How to Create Simple Procedures

Below are examples of few procedures which will give and insight about Procedures

a)Simple Procedure to display “HELLO”

create or replace procedure proc

as

begin

dbms_output.put_line('Hello From Procedure');

end;

b)Simple Procedure to find SUM of two input values and output the Total

create or replace procedure proc

(x  in number, y in number, total out number)

is

begin

total := x+y;

end;

c)Simple Procedure using a CURSOR

create or replace procedure proc(dept_id number)

is

empl_record employee%rowtype;

cursor c1 is select * from employee where deptno =dept_id;

begin

open c1;

loop

fetch c1 into empl_record;

exit when c1%notfound;

dbms_output.put_line(empl_record.name);

end loop;

if c1%rowcount<1 then

dbms_output.put_line('Dept No not present);

end if;

close c1;

end;

d)Simple Procedure to Insert records into Table

create or replace procedure Insert_PROC(emp_no number ,dept_no number,emp_name varchar2)

is

begin

insert into EMP_RECORDS(EMP_ID,DEPT_ID,EMP_NAME) values(emp_no,dept_no,emp_name);

end Insert_PROC;

e)Simple Procedure to Update a Table

create or replace procedure Update_PROC(emp_id)

is

begin             

update EMP_RECORDS set SAL=SAL*1.5 where EMP_ID=emp_id;

end;