REF CURSOR

create or replace procedure proc3(p_grade number)
is
type emp_ref_cur is REF CURSOR return employees%ROWTYPE;
emp_cur emp_ref_cur;
emp_rec emp_cur%ROWTYPE;
emp_lname employees.last_name%TYPE;
begin
if p_grade = 1 then
dbms_output.put_line('employees earning more than 15000 are: ');
open emp_cur for select * from employees where salary/1000 > 15;
elsif p_grade = 2 then
dbms_output.put_line('employees earning less than 15000 are: ');
open emp_cur for select * from employees where salary/1000 < 15;
end if;
loop
fetch emp_cur into emp_rec;
dbms_output.put_line(emp_rec.last_name || ' earns: '||emp_rec.salary);
exit when emp_cur%NOTFOUND;
end loop;
close emp_cur;
end;
/

Exception example

Simple System Defined Exception Example
========================================

create or replace procedure proc1(p_eno employees.employee_id%TYPE)
is
v_sal employees.salary%TYPE;
begin
select salary into v_sal from employees where employee_id=p_eno;
dbms_output.put_line('Salary is: '||v_sal);
end;
/


SQL> exec proc1(100);
Salary is: 24000

PL/SQL procedure successfully completed.

SQL> exec proc1(999);
BEGIN proc1(999); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "HR.PROC1", line 5
ORA-06512: at line 1


create or replace procedure proc1(p_eno employees.employee_id%TYPE)
is
v_sal employees.salary%TYPE;
begin
select salary into v_sal from employees where employee_id=p_eno;
dbms_output.put_line('Salary is: '||v_sal);
exception
when no_data_found then
dbms_output.put_line('Employee id does not exists');
end;
/

SQL> exec proc1(999);
Employee id does not exists

PL/SQL procedure successfully comple





=============================
Simple user defined exception
=============================

create or replace procedure proc1(p_eno employees.employee_id%TYPE)
is
v_max_sal employees.salary%TYPE;
v_sal employees.salary%TYPE;
e_sal exception;
pragma exception_init(e_sal,-100);
begin
select MAX(salary) into v_max_sal from test1;
select salary into v_sal from test1 where employee_id=p_eno;
if v_sal >= v_max_sal then
raise e_sal;
else
dbms_output.put_line('Eligible for increment');
end if;
exception
when e_sal then
dbms_output.put_line('Not eligible for increment');
end;
/

SQL> select employee_id from test1 where salary=(select max(salary) from test1);

EMPLOYEE_ID
-----------
        100

SQL> exec proc1(101);
Eligible for increment

PL/SQL procedure successfully completed.

SQL> exec proc1(100);
Not eligible for increment

PL/SQL procedure successfully completed.

Cursor Example

Simple Cursor with for loop
============================

create or replace procedure proc1(p_dno employees.department_id%TYPE)
is
cursor cur_emp is
select * from employees where department_id = p_dno;
begin
for emp_rec in cur_emp
loop
dbms_output.put_line('Emp id '||emp_rec.employee_id||' salary is '||emp_rec.salary);
end loop;
end;
/


==================================
Open Cursor-Close Cursor example
==================================

create or replace procedure proc2 as
cursor cur_emp(p_dno employees.department_id%type) is
select * from employees where department_id = p_dno;
rec_emp cur_emp%rowtype;
begin
open cur_emp(100);
dbms_output.put_line('Employees in department 100 are:');
loop
fetch cur_emp into rec_emp;
dbms_output.put_line('Employee Name: '|| rec_emp.last_name);
exit when cur_emp%NOTFOUND;
end loop;
close cur_emp;
open cur_emp(30);
dbms_output.put_line('Employees in department 30 are:');
loop
fetch cur_emp into rec_emp;
dbms_output.put_line('Employee Name: '|| rec_emp.last_name);
exit when cur_emp%NOTFOUND;
end loop;
close cur_emp;
end;
/

SQL> exec proc2();
Employees in department 100 are:
Employee Name: Greenberg
Employee Name: Faviet
Employee Name: Chen
Employee Name: Sciarra
Employee Name: Urman
Employee Name: Popp
Employee Name: Popp
Employees in department 30 are:
Employee Name: Raphaely
Employee Name: Khoo
Employee Name: Baida
Employee Name: Tobias
Employee Name: Himuro
Employee Name: Colmenares
Employee Name: Colmenares

PL/SQL procedure successfully completed.
 

Package Example

Simple Package and Package Body
================================

create or replace package pack1 is
v_ann_sal employees.salary%TYPE;
function f_inn_sal(p_eno employees.employee_id%TYPE) return boolean;
procedure p_ann_sal(p_eno employees.employee_id%TYPE);
end;
/

create or replace package body pack1 is
function f_inn_sal(p_eno employees.employee_id%TYPE)
return boolean
is
begin
update test1 set salary=salary+100 where employee_id=p_eno;
return true;
end f_inn_sal;
procedure p_ann_sal(p_eno employees.employee_id%TYPE)
is
begin
select salary*12 into v_ann_sal from test1 where employee_id=p_eno;
dbms_output.put_line('New salary is: '||v_ann_sal);
end p_ann_sal;
end;
/

SQL> exec pack1.p_ann_sal(100);
New salary is: 289200

PL/SQL procedure successfully completed.

Procedure Example

Simple Procedure
=================

create or replace procedure proc1(par1 number, par2 number)
is
v_res number;
begin
v_res := par1 + par2;
dbms_output.put_line(v_res);
end;
/


=================================
Simple procedure with table data
=================================

create or replace procedure proc1(par1 number)
is
v_sal employees.salary%type;
v_ann_sal employees.salary%type;
begin
select salary into v_sal from employees where employee_id=par1;
v_ann_sal := 12*v_sal;
dbms_output.put_line(' Annual Salary is: ' || v_ann_sal);
end;
/

Function example

Simple Function
================

create or replace function f1(par1 number,par2 number)
return number is
v_res number;
begin
v_res := par1 + par2;
return(v_res);
end;
/

select f1(10,20) from dual;


=======================================
Simple example with employees datatype
=======================================

create or replace function f1(p_eno employees.employee_id%type)
return employees.salary%type
is
v_ann_sal employees.salary%type;
begin
select (salary*12) into v_ann_sal from employees where employee_id=p_eno;
return(v_ann_sal);
end;
/


Simple example of function with DML
===================================

create or replace function func1(p_eno employees.employee_id%type)
return boolean
is
begin
update test1 set salary=salary+100 where employee_id=100;
commit;
return true;
end;
/

SQL> select func1(100) from employees;
select func1(100) from employees
       *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

SQL> declare
    v_ret boolean;
    begin
    v_ret := func1(100);
    if v_ret then
    dbms_output.put_line('Salary updated');
    else
    dbms_output.put_line('Salary not updated');
    end if;
   end;
   /
Salary updated

PL/SQL procedure successfully completed.

SQL> select salary from test1 where employee_id=100;

    SALARY
----------
     24100