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;