PROCEDURES in ORACLE

PROCEDURES in ORACLE

A Procedure is type of subprogram that performs an action.
- A procedure can be stored in the database, as a schema object, for repeated execution.

Syntax for Creating Procedure

Create (or Replace) procedure (procedure name)
(Parameter1 (mode1) datatype1,
Parameter2 (mode2) datatype2)
AS|IS
PL/SQL BLOCK

Procedural Parameter Modes

Type of Parameter Description
----------------- -------------
IN Parameter Passes a constant value from the calling environment into the procedure.

OUT Parameter Passes a value from the procedure to the calling environment

IN OUT Parameter Passes a value from the calling environment into the procedure and a
Possibly different value from the procedure back to the calling environment
using the same parameter.


IN Parameter

1)
create or replace procedure Add_proc
(a in number, b in number)
is
c number;
begin
c:= a+b ;
dbms_output.put_line('The values of c is' ||c);
end ;
2)
create or replace procedure emp_proc
(mempno in number)
is
begin
update emp set sal=sal*2 where empno=mempno;
end ;

OUT Parameter

create or replace procedure query_emp
(vempid in emp.empno%type,
vname out emp.ename%type,
vsal out emp.sal%type,
vcomm out emp.comm%type)
IS
begin
select ename,sal,comm into vname,vsal,vcomm from emp
where empno=vempid;
end ;
-- steps for the printing the out parameter
VARIABLE A VARCHAR2(30)
VARIABLE B NUMBER
VARIABLE C NUMBER
EXEC QUERY_EMP (7900,:A,:B,:C)

print A
print B
print C

IN OUT Parameter

create or replace procedure format_phone
(p_phone_no in out varchar2)
is
begin
p_phone_no := '(' || substr(p_phone_no,1,3) ||
')' || substr(p_phone_no,4,3) ||
'-' || substr(p_phone_no,7) ;
end;

Steps for printing the IN OUT result

variable g_phone_no varchar2(15)
begin
:g_phone_no := '8006330575' ;
end;
/
-------
execute format_phone(:g_phone_no)
-------
print g_phone_no

Invoking Procedure from another procedure

1) create or replace procedure view_proc_dept
(mdeptno in number)
is
vdname dept.dname%type;
begin
select dname into vdname from dept where deptno=mdeptno ;
dbms_output.put_line('The department name is ' ||vdname) ;
end ;

2)
create or replace procedure view_proc_emp
(mdeptno in number)
is
vename emp.ename%type;
vsal emp.sal%type ;
cursor emp_cursor is select ename,sal from emp where deptno=mdeptno ;
begin
open emp_cursor ;
loop
fetch emp_cursor into vename,vsal;
dbms_output.put_line('The employee name is' ||vename||' Salary is ' || vsal) ;
exit when emp_cursor%notfound;
end loop;
close emp_cursor ;
view_proc_dept(mdeptno);
end ;

Using Exception in Procedure

1)
create or replace procedure view_exec_emp
(mempno in number)
is
vename emp.ename%type;
vsal emp.sal%type ;
begin
select ename,sal into vename,vsal from emp where empno=mempno;
dbms_output.put_line('The employee name is' ||vename||' Salary is ' || vsal) ;
EXCEPTION
WHEN no_data_found then
dbms_output.put_line('No such Employee number exist');
end ;

2)
create or replace procedure emp_proc
(mdeptno emp.deptno%type )
as
cursor c1 is select ename,sal,job from emp where deptno=mdeptno;
mename emp.ename%type;
msal emp.sal%type ;
mjob emp.job%type;
check_emp exception ;
begin
open c1;
loop
fetch c1 into mename,msal,mjob ;
if c1%rowcount <>
raise check_emp ;
else
dbms_output.put_line('the employee name is '||mename||' salary is '||msal||' '||mjob) ;
end if ;
exit when c1%notfound ;
end loop ;
exception
when check_emp then
dbms_output.put_line(' the deptno not found') ;
end;

Default Option for Parameter in Procedure

create or replace procedure add_location
(vlocation_id in locations.location_id%type default 800,
vlocname IN locations.loc_name%type default 'unknown')
IS
begin
insert into locations values (vlocation_id,vloc_name);
End ;

Dropping Procedure

drop procedure view_proc_emp;
drop procedure view_exec_emp ;
drop procedure view_proc_dept ;