SRAVAN ORACLE APPS HRMS: Cursors And Bulk Collect

CURSORS

Cursor is a pointer to memory location which is called as context area which contains the information necessary for processing, including the number of rows processed by the statement, a pointer to the parsed representation of the statement, and the active set which is the set of rows returned by the query.
Cursor contains two parts

Header

Body

Header includes cursor name, any parameters and the type of data being loaded.
Body includes the select statement.
Ex:
Cursor c(dno in number) return dept%rowtype is select *from dept;
In the above
Header – cursor c(dno in number) return dept%rowtype
Body – select *from dept

CURSOR TYPES

Implicit (SQL)

Explicit

Parameterized cursors

REF cursors

Explicit Cursor: The set of rows returned by a query can consist of zero, one, or multiple rows, depending on how many rows meet your search criteria. When a query returns multiple rows, you can explicitly define a cursor to process the rows. You use three commands to control the cursor:
CURSOR STAGES

Open

Fetch

Implicit Cursors: ORACLE implicitly opens a cursor to process each SQL statement not associated with an explicitly declared cursor.PL/SQL lets you refer to the most recent implicit cursor as the SQL” cursor. So, although you cannot use the OPEN,
FETCH, and CLOSE statements to control an implicit cursor, you can still use cursor attributes to access information about the most recently executed SQL statement.

CURSOR ATTRIBUTES

%found

%notfound

%rowcount

%isopen

%bulk_rowcount

%bulk_exceptions

CURSOR DECLERATION
Syntax:
Cursor is select statement;
Ex:
Cursor c is select *from dept;

CURSOR LOOPS

Simple loop

While loop

For loop

SIMPLE LOOP
Syntax:
Loop
Fetch into ;
Exit when % notfound;
;
End loop;
Ex:

PgSQL

DECLARE

cursor c is select * from student;

v_stud student%rowtype;

BEGIN

open c;

loop

fetch c into v_stud;

exit when c%notfound;

dbms_output.put_line('Name = ' || v_stud.name);

end loop;

close c;

END;

Output:
Name = saketh
Name = srinu
Name = satish
Name = sudha

WHILE LOOP
Syntax:
While % found loop
Fetch nto ;
;
End loop;
Ex:

PgSQL

DECLARE

cursor c is select * from student;

v_stud student%rowtype;

BEGIN

open c;

fetch c into v_stud;

while c%found loop

fetch c into v_stud;

dbms_output.put_line('Name = ' || v_stud.name);

end loop;

close c;

END;

Output:
Name = saketh
Name = srinu
Name = satish
Name = sudha

FOR LOOP
Syntax:
for in loop
;
End loop;
Ex:

PgSQL

DECLARE

cursor c is select * from student;

BEGIN

for v_stud in c loop

dbms_output.put_line('Name = ' || v_stud.name);

end loop;

END;

Output:
Name = saketh
Name = srinu
Name = satish
Name = sudha

PARAMETARIZED CURSORS

This was used when you are going to use the cursor in more than one place with different values for the same where clause.

Cursor parameters must be in mode.

Cursor parameters may have default values.

The scope of cursor parameter is within the select statement.

Ex:

PgSQL

DECLARE

cursor c(dno in number) is select * from dept where deptno = dno;

v_dept dept%rowtype;

BEGIN

open c(20);

loop

fetch c into v_dept;

exit when c%notfound;

dbms_output.put_line('Dname = ' || v_dept.dname || ' Loc = ' || v_dept.loc);

end loop;

close c;

END;

Output:

Dname = RESEARCH Loc = DALLAS

PACKAGED CURSORS WITH HEADER IN SPEC AND BODY IN PACKAGE BODY

Cursors declared in packages will not close automatically.

In packaged cursors you can modify the select statement without making any changes to the cursor header in the package specification.

Packaged cursors with must be defined in the package body itself, and then use it as global for the package.

You can not define the packaged cursor in any subprograms.

Cursor declaration in package with out body needs the return clause.

PgSQL

Ex1:

CREATE OR REPLACE PACKAGE PKG IS

cursor c return dept%rowtype is select * from dept;

procedure proc is

END PKG;

CREATE OR REPLACE PAKCAGE BODY PKG IS

cursor c return dept%rowtype is select * from dept;

PROCEDURE PROC IS

BEGIN

for v in c loop

dbms_output.put_line('Deptno = ' || v.deptno || ' Dname = ' ||

v.dname || ' Loc = ' || v.loc);

end loop;

END PROC;

END PKG;

Output:

SQL> exec pkg.proc

Deptno = 10 Dname = ACCOUNTING Loc = NEW YORK

Deptno = 20 Dname = RESEARCH Loc = DALLAS

Deptno = 30 Dname = SALES Loc = CHICAGO

Deptno = 40 Dname = OPERATIONS Loc = BOSTON

Ex2:

CREATE OR REPLACE PAKCAGE BODY PKG IS

cursor c return dept%rowtype is select * from dept where deptno > 20;

PROCEDURE PROC IS

BEGIN

for v in c loop

dbms_output.put_line('Deptno = ' || v.deptno || ' Dname = ' ||

v.dname || ' Loc = ' || v.loc);

end loop;

END PROC;

END PKG;

Output:

SQL> exec pkg.proc

Deptno = 30 Dname = SALES Loc = CHICAGO

Deptno = 40 Dname = OPERATIONS Loc = BOSTON

REF CURSORS AND CURSOR VARIABLES

This is unconstrained cursor which will return different types depends upon the user input.

Ref cursors cannot be closed implicitly.

Ref cursor with return type is called strong cursor.

Ref cursor without return type is called weak cursor.

You can declare ref cursor type in package spec as well as body.

You can declare ref cursor types in local subprograms or anonymous blocks.

Cursor variables can be assigned from one to another.

You can declare a cursor variable in one scope and assign another cursor variable with different scope, then you can use the cursor variable even though the assigned cursor variable goes out of scope.

Cursor variables can be passed as parameters to the subprograms.

Cursor variables modes are in or out or in out.

Cursor variables cannot be declared in package spec and package body (excluding subprograms).

You cannot user remote procedure calls to pass cursor variables from one server to another.

Cursor variables cannot use for update clause.

You can not assign nulls to cursor variables.

You cannot compare cursor variables for equality, inequality and nullity.

PgSQL

CREATE OR REPLACE PROCEDURE REF_CURSOR(TABLE_NAME IN VARCHAR) IS

type t is ref cursor;

c t;

v_dept dept%rowtype;

type r is record(ename emp.ename%type,job emp.job%type,sal emp.sal%type);

v_emp r;

v_stud student.name%type;

BEGIN

if table_name = 'DEPT' then

open c for select * from dept;

elsif table_name = 'EMP' then

open c for select ename,job,sal from emp;

elsif table_name = 'STUDENT' then

open c for select name from student;

end if;

loop

if table_name = 'DEPT' then

fetch c into v_dept;

exit when c%notfound;

dbms_output.put_line('Deptno = ' || v_dept.deptno || ' Dname = ' ||

v_dept.dname || ' Loc = ' || v_dept.loc);

elsif table_name = 'EMP' then

fetch c into v_emp;

exit when c%notfound;

dbms_output.put_line('Ename = ' || v_emp.ename || ' Job = ' || v_emp.job

|| ' Sal = ' || v_emp.sal);

elsif table_name = 'STUDENT' then

fetch c into v_stud;

exit when c%notfound;

dbms_output.put_line('Name = ' || v_stud);

end if;

end loop;

close c;

END;

Output:

SQL> exec ref_cursor('DEPT')

Deptno = 10 Dname = ACCOUNTING Loc = NEW YORK

Deptno = 20 Dname = RESEARCH Loc = DALLAS

Deptno = 30 Dname = SALES Loc = CHICAGO

Deptno = 40 Dname = OPERATIONS Loc = BOSTON

SQL> exec ref_cursor('EMP')

Ename = SMITH Job = CLERK Sal = 800

Ename = ALLEN Job = SALESMAN Sal = 1600

Ename = WARD Job = SALESMAN Sal = 1250

Ename = JONES Job = MANAGER Sal = 2975

Ename = MARTIN Job = SALESMAN Sal = 1250

Ename = BLAKE Job = MANAGER Sal = 2850

Ename = CLARK Job = MANAGER Sal = 2450

Ename = SCOTT Job = ANALYST Sal = 3000

Ename = KING Job = PRESIDENT Sal = 5000

Ename = TURNER Job = SALESMAN Sal = 1500

Ename = ADAMS Job = CLERK Sal = 1100

Ename = JAMES Job = CLERK Sal = 950

Ename = FORD Job = ANALYST Sal = 3000

Ename = MILLER Job = CLERK Sal = 1300

SQL> exec ref_cursor('STUDENT')

Name = saketh

Name = srinu

Name = satish

Name = sudha

CURSOR EXPRESSIONS

You can use cursor expressions in explicit cursors.

You can use cursor expressions in dynamic SQL.

You can use cursor expressions in REF cursor declarations and variables.

You cannot use cursor expressions in implicit cursors.

Oracle opens the nested cursor defined by a cursor expression implicitly as soon as it fetches the data containing the cursor expression from the parent or outer cursor.

Nested cursor closes if you close explicitly.

Nested cursor closes whenever the outer or parent cursor is executed again or closed or canceled.

Nested cursor closes whenever an exception is raised while fetching data from a parent cursor.

Cursor expressions cannot be used when declaring a view.

Cursor expressions can be used as an argument to table function.

You cannot perform bind and execute operations on cursor expressions when using the cursor expressions in dynamic SQL.

USING NESTED CURSORS OR CURSOR EXPRESSIONS

PgSQL

Ex:

DECLARE

cursor c is select ename,cursor(select dname from dept d where e.empno = d.deptno) from emp e;

type t is ref cursor;

c1 t;

c2 t;

v1 emp.ename%type;

v2 dept.dname%type;

BEGIN

open c;

loop

fetch c1 into v1;

exit when c1%notfound;

fetch c2 into v2;

exit when c2%notfound;

dbms_output.put_line('Ename = ' || v1 || ' Dname = ' || v2);

end loop;

close c;

END;

CURSOR CLAUSES

Return

For update

Where current of

Bulk collect

RETURN
Cursor c return dept%rowtype is select *from dept;
Or
Cursor c1 is select *from dept;
Cursor c return c1%rowtype is select *from dept;
Or
Type t is record(deptno dept.deptno%type, dname dept.dname%type);
Cursor c return t is select deptno, dname from dept;

FOR UPDATE AND WHERE CURRENT OF
Normally, a select operation will not take any locks on the rows being accessed. This will allow other sessions connected to the database to change the data being selected. The result set is still consistent. At open time, when the active set is determined, oracle takes a snapshot of the table. Any changes that have been committed prior to this point are reflected in the active set. Any changes made after this point, even if they are committed, are not reflected unless the cursor is reopened, which will evaluate the active set again.

However, if the FOR UPDATE caluse is pesent, exclusive row locks are taken on the rows in the active set before the open returns. These locks prevent other sessions from changing the rows in the active set until the transaction is committed or rolled back. If another session already has locks on the rows in the active set, then SELECT … FOR UPDATE operation will wait for these locks to be released by the other session. There is no time-out for this waiting period. The SELECT…FOR UPDATE will hang until the other session releases the lock. To handle this situation, the NOWAIT clause is available.
Syntax:
Select …from … for update of column_name [wait n];

If the cursor is declared with the FOR UPDATE clause, the WHERE CURRENT OF clause can be used in an update or delete statement.
Syntax:
Where current of cursor;

PgSQL

Ex:

DECLARE

cursor c is select * from dept for update of dname;

BEGIN

for v in c loop

update dept set dname = 'aa' where current of c;

commit;

end loop;

END;

BULK COLLECT

This is used for array fetches

With this you can retrieve multiple rows of data with a single roundtrip.

This reduces the number of context switches between the pl/sql and sql engines.

Reduces the overhead of retrieving data.

You can use bulk collect in both dynamic and static sql.

You can use bulk collect in select, fetch into and returning into clauses.

SQL engine automatically initializes and extends the collections you reference in the bulk collect clause.

Bulk collect operation empties the collection referenced in the into clause before executing the query.

You can use the limit clause of bulk collect to restrict the no of rows retrieved.

You can fetch into multible collections with one column each.

Using the returning clause we can return data to the another collection.

BULK COLLECT IN FETCH

PgSQL

Ex:

DECLARE

Type t is table of dept%rowtype;

nt t;

Cursor c is select *from dept;

BEGIN

Open c;

Fetch c bulk collect into nt;

Close c;

For i in nt.first..nt.last loop

dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' ||

nt(i).loc);

end loop;

END;

Output:

Dname = ACCOUNTING Loc = NEW YORK

Dname = RESEARCH Loc = DALLAS

Dname = SALES Loc = CHICAGO

Dname = OPERATIONS Loc = BOSTON

BULK COLLECT IN SELECT

PgSQL

Ex:

DECLARE

Type t is table of dept%rowtype;

Nt t;

BEGIN

Select * bulk collect into nt from dept;

for i in nt.first..nt.last loop

dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' ||

nt(i).loc);

end loop;

END;

Output:

Dname = ACCOUNTING Loc = NEW YORK

Dname = RESEARCH Loc = DALLAS

Dname = SALES Loc = CHICAGO

Dname = OPERATIONS Loc = BOSTON

LIMIT IN BULK COLLECT
You can use this to limit the number of rows to be fetched.

PgSQL

Ex:

DECLARE

Type t is table of dept%rowtype;

nt t;

Cursor c is select *from dept;

BEGIN

Open c;

Fetch c bulk collect into nt limit 2;

Close c;

For i in nt.first..nt.last loop

dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' || nt(i).loc);

end loop;

END;

Output:

Dname = ACCOUNTING Loc = NEW YORK

Dname = RESEARCH Loc = DALLAS

MULTIPLE FETCHES IN INTO CLAUSE

PgSQL

Ex1:

DECLARE

Type t is table of dept.dname%type;

nt t;

Type t1 is table of dept.loc%type;

nt1 t;

Cursor c is select dname,loc from dept;

BEGIN

Open c;

Fetch c bulk collect into nt,nt1;

Close c;

For i in nt.first..nt.last loop

dbms_output.put_line('Dname = ' || nt(i));

end loop;

For i in nt1.first..nt1.last loop

dbms_output.put_line('Loc = ' || nt1(i));

end loop;

END;

Output:

Dname = ACCOUNTING

Dname = RESEARCH

Dname = SALES

Dname = OPERATIONS

Loc = NEW YORK

Loc = DALLAS

Loc = CHICAGO

Loc = BOSTON

Ex2:

DECLARE

type t is table of dept.dname%type;

type t1 is table of dept.loc%type;

nt t;

nt1 t1;

BEGIN

Select dname,loc bulk collect into nt,nt1 from dept;

for i in nt.first..nt.last loop

dbms_output.put_line('Dname = ' || nt(i));

end loop;

for i in nt1.first..nt1.last loop

dbms_output.put_line('Loc = ' || nt1(i));

end loop;

END;

Output:

Dname = ACCOUNTING

Dname = RESEARCH

Dname = SALES

Dname = OPERATIONS

Loc = NEW YORK

Loc = DALLAS

Loc = CHICAGO

Loc = BOSTON

RETURNING CLAUSE IN BULK COLLECT

You can use this to return the processed data to the ouput variables or typed variables.

PgSQL

Ex:

DECLARE

type t is table of number(2);

nt t := t(1,2,3,4);

type t1 is table of varchar(2);

nt1 t1;

type t2 is table of student%rowtype;

nt2 t2;

BEGIN

select name bulk collect into nt1 from student;

forall v in nt1.first..nt1.last

update student set no = nt(v) where name = nt1(v) returning

no,name,marks bulk collect into nt2;

for v in nt2.first..nt2.last loop

dbms_output.put_line('Marks = ' || nt2(v));

end loop;

END;

Output:

Marks = 100

Marks = 200

Marks = 300

Marks = 400

POINTS TO REMEMBER

Cursor name can be up to 30 characters in length.

Cursors declared in anonymous blocks or subprograms closes automatically when that block terminates execution.

%bulk_rowcount and %bulk_exceptions can be used only with forall construct.

Cursor declarations may have expressions with column aliases.

These expressions are called virtual columns or calculated columns.

- See more at: http://alloracleapps.com/oracle/cursors-and-bulk-collect/#sthash.P3XKFEKv.dpuf

SRAVAN ORACLE APPS HRMS

Saturday, February 14, 2015

Cursors And Bulk Collect

No comments:

Post a Comment