function & procedure
packages
function --> arguments or parameters
with arguments,
IN, read only pass values inside the function
eg : select * from emp where empno = IN
OUT : write only, get value from inside the function
eg : select ename into OUT from emp where empno = IN
IN OUT
return varchar2
name of all functions/cursors/procedures/packages/triggers ?
SCOTT USER(or any other user or created by Oracle)
ALL_OBJECTS --> OBJECTS CREATED BY ANY USER
USER_OBJECTS --> OBJECTS CREATED BY THE USER
DBA_OBJECTS --> OBJECTS CREATED BY ORACLE
source or text of function/cursor/procedure/package/trigger?
ALL_SOURCE --> SOURCES OF F/C/P/P/T CREATED BY ALL USER
USER_SOURCE --> SOURCES OF F/C/P/P/T CREATED BY THE USER
DBA_SOURCE --> SOURCES OF F/C/P/P/T CREATED BY ORACLE
procedure --> create or replace procedure p_name is variables
begin
exception
end;
run the procedure :
exec pname()
PL/SQL Block
p_name();
eg : 1. write a procedure to draw a line
for loop
DBMS_OUTPUT.put('-');
NEW_LINE
---------------------
create or replace procedure draw_line
is
begin
for i in 1..40
loop
DBMS_OUTPUT.put('-');
end loop;
DBMS.OUTPUT.NEW_LINE;
end;
/
@draw_line.sql
set serveroutput on
EXEC draw_line;
2. create a procedure to work similar to DBMS_OUTPUT.put_line
eg : exec print('ABC');
create or replace procedure put_line(arg varchar2)
is
begin
DBMS_OUTPUT.put_line(arg);
end;
.
/
PL/SQL program to use Procedure
Declare
a number:= 20;
begin
put_line(a);
put_line('TEST');
end;
or
exec put_line('test');
3. write a procedure to input empno and output emp name and emp job(IN OUT)
input --> empno
output display --> ename, job
emp_no IN number, emp_name OUT varchar2, emp_job OUT varchar2
create or replace function p3(N IN varchar2)
is
name1 varchar2(10);
job1 varchar2(10);
begin
select ename, job into name1, job1
from emp where empno=N;
DBMS_OUTPUT.put_line(name1 || ' ' || job1);
end;
/
exec p3(1234);
method 2 :
create or replace procedure p4(N IN number, name1 OUT varchar2, job1 OUT varchar2)
is
begin
select ename, job into name1, job1 from emp
where empno=N;
end;
/
declare
name12 varchar2(10);
job12 varchar2(10);
begin
p4(1234, name12, job12);
DBMS_OUTPUT.put_line(name12 || ' ' || job12);
end;
4. create a procedure to input empno and output ename for all emp(using cursor and loop)
create or replace procedure p4(N IN number, emp_name OUT varchar2)
is
begin
select ename into emp_name from emp
where empno = N;
end;
/
declare
emp_name1 varchar2(10);
cursor c1 is select empno from emp;
begin
for emp_rec in c1
--no open, fetch, close, exit when
loop
p4(emp_rec.empno, emp_name1);
DBMS_OUTPUT.put_line(emp_rec.empno || ' ' || empname1);
end loop;
end;
note : OUT --> return something from procedure.
PL/SQL program need one variable.
select function_name(parameter) from dual;
eg : select f1(123) from dual;
select p1 from dual;(wrong, procedure may not return something)
note : in PL/SQL program, you can use function and procedure. in SQL*PLUS, you can only use only function.
Package : library(many functions, many procedures)
package_name.function_name(parameter);
package_name.procedure_name(parameter);
eg :
package_name.f1(111);
package_name.p1(111);
eg :
select pakg.f1(111) from dual;(right)
select pakg.p1(111) from dual;(wrong)
exec pakg.p1(111);(right)
to create a package :
1. specification (declare variables, declare functions, declare procedures --> give the name)
2. body (create function, create procedures --> write the full (PL/SQL) function)
eg :
ed pack1.sql -- to store the declaration of package
create or replace package pack1
is
procedure p1; --declare procedure
function f1 return varchar2; --declare function
A number; --declare variable
end pack1; --or end;
ed pakg2.sql -- to store the body of package
create or replace package body pakg1
is
procedure p1 is
begin
DBMS_OUTPUT.put_line('PROCEDURE 1');
end p1; --or end;
function f1 return number is
A number(10);
begin
A:=1234;
return A;
end f1; --or end;
end pakg1; --or end;
to run the package :
select pakg1.f1 from dual;
exec pakg1.p1;
or
declare
N number(10);
begin
N:=pakg1.f1;
DBMS_OUTPUT.put_line(N);
pakg1.p1;
end;
recompile the package :
alter package pack1 compile;
alter package pack2 body compile;
create or replace package pack1
is
declare functions, procedures, variables
end pack1;
create or replace package body pack1
is
function f1() return number
is
...
begin
end f1;
procedure p1(IN OUT)
is
begin
end;
end pack1;
eg : create a package wieh two functions, first fuction to display the name, second function to display the salary.
in both functions input empno.
f1(empno IN, ename OUT)
f2(empno IN, salary OUT)
a) create package spacification
b) create package body
c) execute the package using pL/SQL program
ed p1.sql
create or replace package p1
is
function f1(emp_no IN, emp_name OUT);
function f2(emp_no IN, emp_salary OUT);
end p1;
ed p2.sql
create or replace package p1
is
function f1(emp_no IN, emp_name OUT) return varchar is
emp_name varchar;
begin
select ename into emp_name from emp
where empno=emp_no;
return emp_name;
end f1;
function f2(emp_no IN, emp_salary OUT) return varchar is
emp_salary varchar2;
begin
select salary into emp_salary from emp
where empno=emp_no;
end f2;
end p1;
select p1.f1(1234, emp_name);
select p1.f2(1234, emp_salary);
correction :
ed pack1.sql
create or replace package pack1
is
function get_name(emp_id number)
return varchar2;
function get_salary(emp_id number)
return number;
end pack1;
ed pack2.sql
create or replace package body pack1
is
function get_name(emp_id number)
return varchar2
is
emp_name emp.ename%type;
begin
select ename into emp_name from emp
where empno = emp_id;
return emp_name;
end get_name;
function get_salary(emp_id number)
return number
is
emp_salary emp.sal%type;
begin
select sal into emp_salary from emp
where empno = emp_id;
return emp_salary;
end get_salary;
end pack1;
select pack1.get_name(1234) from dual;
select pack1.get_salary(1234) from dual;
or
declare
v_name emp.ename%type;
v_salary emp.sal%type;
v_emp_id emp.empno%type := &v_emp_id;
begin
v_name:=pack1.get_name(v_emp_id);
v_salary:=pack1.get_salary(v_emp_id);
DBMS_OUTPUT.put_line(v_name||' '|| v_salary);
end;
final exam
1. fill in the blanks
2. true or false
3. what is the result of the following pL/SQL programs
4. explain the following PL/SQL programs
5. match the following
6. short notes
7. write the answer for the following
eg : differences between procedure and function
8. write the PL/SQL programs for the following