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
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
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;
