Cursor, Procedure & Function in Oracle

CURSOR

A cursor basically is a pointer to a query result and is used to read attribute values of selected tuples

into variables. A cursor typically is used in combination with a loop construct such that each
tuple read by the cursor can be processed individually.

1.     In summary, the major goals of PL/SQL are to

2.     increase the expressiveness of SQL,
3.     process query results in a tuple-oriented way,
4.     optimize combined SQL statements,
5.     develop modular database application programs,
6.     reuse program code, and
7.     reduce the cost for maintaining and changing applications.

Processing Cursors: Before a cursor can be used, it must be opened using the open statement

open <cursor name> [(<list of parameters>)] ;

The example below illustrates how a cursor is used together with a continuous loop:

declare

cursor emp_cur is select _ from EMP;

emp_rec EMP%ROWTYPE;

emp_sal EMP.SAL%TYPE;

begin

open emp_cur;

loop

fetch emp_cur into emp_rec;

exit when emp_cur%NOTFOUND;

emp_sal := emp_rec.sal;

<sequence of statements>

end loop;

close emp_cur;

. . .

end;

 

Each loop can be completed unconditionally using the exit clause:

exit [<block label>] [when <condition>]

 

Cursor for loops can be used to simplify the usage of a cursor:
[<< <label name> >>]

for <record name> in <cursor name>[(<list of parameters>)] loop

<sequence of statements>

end loop [<label name>];

A record suitable to store a tuple fetched by the cursor is implicitly declared. Furthermore,
this loop implicitly performs a fetch at each iteration as well as an open before the loop is
entered and a close after the loop is left. If at an iteration no tuple has been fetched, the loop
is automatically terminated without an exit.

It is even possible to specify a query instead of <cursor name> in a for loop:

for <record name> in (<select statement>) loop

<sequence of statements>

end loop;

Example:

for sal_rec in (select SAL + COMM total from EMP) loop

. . . ;

end loop;

Except data definition language commands such as create table, all types of SQL statements

can be used in PL/SQL blocks, in particular delete, insert, update, and commit. Note
that in PL/SQL only select statements of the type select <column(s)> into are allowed, i.e.,
selected attribute values can only be assigned to variables (unless the select statement is used
in a subquery). The usage of select statements as in SQL leads to a syntax error. If update or
delete statements are used in combination with a cursor, these commands can be restricted to
currently fetched touple. In these cases the clause where current of<cursor name> is added
as shown in the following example.

 Example: The following PL/SQL block performs the following modifications: All employees

having 'KING' as their manager get a 5% salary increase.

declare

manager EMP.MGR%TYPE;

cursor emp_cur (mgr_no number) is

select SAL from EMP

where MGR = mgr_no

for update of SAL;

begin

select EMPNO into manager from EMP

where ENAME = 'KING';

for emp_rec in emp_cur(manager) loop

update EMP set SAL = emp_rec.sal * 1.05

where current of emp_cur;

end loop;

commit;

end;

Procedures and Functions

PL/SQL provides sophisticated language constructs to program procedures and functions as

stand-alone PL/SQL blocks. They can be called from other PL/SQL blocks, other procedures
and functions. The syntax for a procedure definition is

create [or replace] procedure <procedure name> [(<list of parameters>)] is

<declarations>

begin

<sequence of statements>

[exception<exception handling routines>]

end [<procedure name>];

A function can be specified in an analogous way

create [or replace] function <function name> [(<list of parameters>)]

return <data type> is

The optional clause or replace re-creates the procedure/function. A procedure can be deleted
using the command drop procedure <procedure name> (drop function <function name>).
In contrast to anonymous PL/SQL blocks, the clause declare may not be used in procedure/
function definitions.

Valid parameters include all data types. However, for char, varchar2, and number no length

and scale, respectively, can be specified. For example, the parameter number(6) results in a
compile error and must be replaced by number. Instead of explicit data types, implicit types
of the form %TYPE and %ROWTYPE can be used even if constrained declarations are referenced.

Example: The subsequent procedure is used to increase the salary of all employees who work
in the department given by the procedure's parameter. The percentage of the salary increase
is given by a parameter, too.

create procedure raise_salary(dno number, percentage number DEFAULT 0.5) is

cursor emp_cur (dept no number) is

select SAL from EMP where DEPTNO = dept no

for update of SAL;

empsal number(8);

begin

open emp_cur(dno); - - Here dno is assigned to dept no

loop

fetch emp_cur into empsal;

exit when emp cur%NOTFOUND;

update EMP set SAL = empsal * ((100 + percentage)/100)

where current of emp_cur;

end loop;

close emp_cur;

commit;

end raise_salary;

 

This procedure can be called from the SQL*Plus shell using the command

execute raise salary(10, 3);

If the procedure is called only with the parameter 10, the default value 0.5 is assumed as

specified in the list of parameters in the procedure definition. If a procedure is called from a
PL/SQL block, the keyword execute is omitted.

Functions have the same structure as procedures. The only difference is that a function returns

a value whose data type (unconstrained) must be specified.

Example:

create function get_dept_salary(dno number) return number is

all_sal number;

begin

all sal := 0;

for emp_sal in (select SAL from EMP where DEPTNO = dno and SAL is not null) loop

all_sal := all_sal + emp sal.sal;

end loop;

return all_sal;

end get_dept_salary;

In order to call a function from the SQL*Plus shell, it is necessary to first define a variable

to which the return value can be assigned. In SQL*Plus a variable can be defined using
the command variable <variable name> <data type>;, for example, variable salary
number. The above function then can be called using the command execute

:salary :=get dept salary(20);

Note that the colon \:" must be put in front of the variable.

Note :- please let me know for any additional points about above topic... :):)