Blog is developed for free online learning of Oracle Applications, SQL, PLSQL

Procedures in PL/SQL


Procedures:

In this post post we will be learning PL/SQL procedure,

Tutorial for Procedures in PL/SQL :

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;