Tuesday 10 May 2016

create procedure to add 2 number

SUM OF 2 NOS
 
CREATE or replace procedure p1 is
Declare
 a number;
 b number;
 c number;
Begin
 a:=50;
 b:=89;
 c:=a+b;
 dbms_output.put_line('Sum of '||a||' and '||b||' is '||c);
End;

if u enter emp_name in emp table in ename field's in any case it will be inserted in capital letters's only

 Create or replace trigger cap before insert on emp 
for each row 
begin 
:New.ename = upper(:New.ename); 
end;

NO CHANGES CAN BE DONE ON A PARTICULAR TABLE ON SUNDAY AND SATURDAY

Create or replace trigger change before on emp
for each row when (to_char(sysdate,’dy’) in (’SAT’,'SUN’)) 
begin
raise_application_error(-200001, ‘u cannot enter data in saturnday and sunday’);

end;

YOU HAVE 2 TABLES WITH THE SAME STRUCTURE. IF U DELETE A RECORD FROM ONE TABLE , IT WILL BE INSERTED IN 2ND TABLE ED TRIGGERNAME

Create or replace trigger backup 
after delete on emp fro each row begin 
insert into emp values (:old.ename,:old.job,:old.sal); 
end; 

A database trigger that allows changes to employee table only during the business hours(i.e. from 8 a.m to 5.00 p.m.) from monday to saturday. There is no restriction on viewing data from the table –



CREATE OR REPLACE TRIGGER Time_Check BEFORE 
INSERT OR UPDATE OR DELETE 
ON EMP 
BEGIN 
IF TO_NUMBER(TO_CHAR(SYSDATE,’hh24′)) < 10 OR TO_NUMBER(TO_CHAR(SYSDATE,’hh24′)) >= 17 OR TO_CHAR(SYSDATE,’DAY’) = ‘SAT’ OR TO_CHAR(SYSDATE,’DAY’) = ‘SAT’ THEN RAISE_APPLICATION_ERROR (-20004,’YOU CAN ACCESS ONLY BETWEEN 10 AM TO 5 PM ON MONDAY TO FRIDAY ONLY.’); 
END IF; 
END;

Display details of Highest 10 salary paid employee

DECLARE
          CURSOR c1 IS SELECT  * FROM emp ORDER BY sal DESC;
          e_rec  emp%rowtype;
BEGIN
          FOR e_rec IN c1
          LOOP
DBMS_OUTPUT.PUT_LINE('Number: ' || ' ' || e_rec.empno);
DBMS_OUTPUT.PUT_LINE('Name  : ' || ' ' || e_rec.ename);
DBMS_OUTPUT.PUT_LINE('Salary: ' || ' ' || e_rec.sal);
          EXIT WHEN c1%ROWCOUNT >= 10;
          END LOOP;
END;

EXPLICIT CURSOR EG

DECLARE
          CURSOR c1 is SELECT * FROM emp;
          str_empno emp.empno%type;
          str_ename emp.ename%type;
          str_job emp.job%type;
          str_mgr emp.mgr%type;
          str_hiredate emp.hiredate%type;
          str_sal emp.sal%type
          str_comm emp.comm%type;
          str_deptno emp.deptno%type;
          rno number;
BEGIN
          rno := &rno;
          FOR e_rec IN c1
          LOOP
                    IF c1%rowcount = rno THEN
 DBMS_OUTPUT.PUT_LINE (str_empno || ' ' || str_ename || ' ' 
|| str_job || ' ' || str_mgr || ' ' || str_hiredate || ' ' || str_sal || ' ' 
|| str_comm || ' ' || str_deptno);
                    END IF;
END LOOP;
END;