PL/SQL

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