Creating Procedure

Oracle Application 11.5.9 Bias Technologies

10

Procedures: Procedures are also known as subprograms. Given below is the syntax for

the creation of the procedure.

Syntax: CREATE [OR REPLACE] PROCEDURE procedure_name

[(argument [{IN | OUT | IN OUT}] type,

…

[(argument [{IN | OUT | IN OUT}] type) ] {IS | AS}

BEGIN

procedure_body

END procedure_name;

Ex: SQL> create or replace procedure razia_proc as

v_sal number;

cursor razia_cursor is select * from emp order by deptno;

begin

v_sal := &v_s;

for i in razia_cursor

loop

if(i.sal > v_sal) then

dbms_output.put_line( 'Employee Name: ' || i.ename);

end if;

end loop;

exception

when others then

dbms_output.put_line( 'YOU HAVE PERFORMED AN ILLEGAL OPERATION');

dbms_output.put_line( 'THE PROGRAM MAY TERMINATE NOW');

end razia_proc;

Execution: exec razia_proc;

Passing IN Parameter to the Procedures:

Ex: SQL> create or replace procedure kanthi_proc(p_inpar in number) as

v_name varchar2(30);

begin

select ename into v_name from emp where empno=p_inpar;

dbms_output.put_line( 'Employee Name: ' || v_name);

exception

when others then

dbms_output.put_line( 'YOU HAVE PERFORMED ILLEGAL OPERATION');

end kanthi_proc;

Using IN and OUT parameters to Procedures:

Ex: SQL> create or replace procedure

shabbir_proc(p_outpar out varchar2,p_inpar in number) as

begin

select ename into p_outpar from emp where empno=p_inpar;

end;

--To Catch the Output variable out side the procedure in the SQL

declare

v_name varchar2(20);

v_num number;

begin

mypro(v_name,&v_num);

dbms_output.put_line( 'Employee Name: ' || v_name);

end shabbir_proc;