PACKAGES
A package
is a container for related objects. It has specification and body. Each of them
is stored separately in data dictionary.
PACKAGE SYNTAX
Create or replace package <package_name> is
--
package specification includes subprograms signatures, cursors and global
or
public variables.
End <package_name>;
Create or replace package body <package_name> is
-- package body
includes body for all the subprograms declared in the spec, private
Variables and
cursors.
Begin
--
initialization section
Exception
-- Exception
handling seciton
End <package_name>;
IMPORTANT POINGS ABOUT PACKAGES
Ø The first time a packaged subprogram is
called or any reference to a packaged variable or type is made, the package is
instantiated.
Ø Each session will have its own copy of
packaged variables, ensuring that two sessions executing subprograms in the
same package use different memory locations.
Ø In many cases initialization needs to be
run the first time the package is instantiated within a session. This can be
done by adding initialization section to the package body after all the
objects.
Ø Packages are stored in the data dictionary
and can not be local.
Ø Packaged subprograms has an advantage
over stand alone subprogram.
Ø When ever any reference to package, the
whole package p-code was stored in shared pool of SGA.
Ø Package may have local subprograms.
Ø You can include authid clause inside the
package spec not in the body.
Ø The execution section of a package is
know as initialization section.
Ø You can have an exception section at the
bottom of a package body.
Ø Packages subprograms are not invalidated.
COMPILING PACKAGES
Ø SQL> Alter package PKG compile;
Ø SQL> Alter package PKG compile specification;
Ø SQL> Alter package PKG compile body;
PACKAGE DEPENDENCIES
Ø The package body depends on the some
objects and the package header.
Ø The package header does not depend on the
package body, which is an advantage of packages.
Ø We can change the package body with out
changing the header.
PACKAGE RUNTIME STATE
Package runtime state is differ for the following packages.
Ø Serially reusable packages
Ø Non serially reusable packages
SERIALLY REUSABLE PACKAGES
To force the oracle to use serially reusable version then
include PRAGMA
SERIALLY_REUSABLE in
both package spec and body, Examine the following package.
CREATE OR REPLACE PACKAGE PKG IS
pragma serially_reusable;
procedure emp_proc;
END PKG;
CREATE OR REPLACE PACKAGE BODY PKG IS
pragma serially_reusable;
cursor c is select ename from
emp;
PROCEDURE EMP_PROC IS
v_ename emp.ename%type;
v_flag boolean := true;
v_numrows number := 0;
BEGIN
if not c%isopen then
open c;
end if;
while v_flag loop
fetch c into v_ename;
v_numrows := v_numrows + 1;
if v_numrows = 5 then
v_flag := false;
end if;
dbms_output.put_line('Ename = '
|| v_ename);
end loop;
END EMP_PROC;
END PKG;
SQL> exec pkg.emp_proc
Ename = SMITH
Ename = ALLEN
Ename = WARD
Ename = JONES
Ename = MARTIN
SQL> exec pkg.emp_proc
Ename = SMITH
Ename = ALLEN
Ename = WARD
Ename = JONES
Ename = MARTIN
Ø The above package displays the same
output for each execution even though the cursor is not closed.
Ø Because the serially reusable version
resets the state of the cursor each time it was called.
NON SERIALL Y REUSABLE PACKAGES
This is the default version used by the oracle, examine the
following package.
CREATE OR REPLACE PACKAGE PKG IS
procedure emp_proc;
END PKG;
CREATE OR REPLACE PACKAGE BODY PKG IS
cursor c is select ename from
emp;
PROCEDURE EMP_PROC IS
v_ename emp.ename%type;
v_flag boolean := true;
v_numrows number := 0;
BEGIN
if not c%isopen then
open c;
end if;
while v_flag loop
fetch c into v_ename;
v_numrows := v_numrows + 1;
if v_numrows = 5 then
v_flag := false;
end if;
dbms_output.put_line('Ename = '
|| v_ename);
end loop;
END EMP_PROC;
END PKG;
SQL> exec pkg.emp_proc
Ename = SMITH
Ename = ALLEN
Ename = WARD
Ename = JONES
Ename = MARTIN
SQL> exec pkg.emp_proc
Ename = BLAKE
Ename = CLARK
Ename = SCOTT
Ename = KING
Ename = TURNER
Ø The above package displays the different
output for each execution even though the cursor is not closed.
Ø Because the non-serially reusable version
remains the state of the cursor over database calls.
DEPENDENCIES OF PACKAGE RUNTIME STATE
Dependencies can exists between package state and anonymous
blocks.
Examine the following program
Create this package in first session
CREATE OR REPLACE PACKAGE PKG IS
v number := 5;
procedure p;
END PKG;
CREATE OR REPLACE PACKAGE BODY PKG IS
PROCEDURE P IS
BEGIN
dbms_output.put_line('v = ' || v);
v := 10;
dbms_output.put_line('v = ' || v);
END P;
END PKG;
Connect to second session, run the
following code.
BEGIN
pkg.p;
END;
The above code wil work.
Go back to first session and recreate the
package using create.
Then connect to second session and run
the following code again.
BEGIN
pkg.p;
END;
This above code will not work because of the following.
Ø The anonymous block depends on pkg. This
is compile time dependency.
Ø There is also a runtime dependency on the
packaged variables, since each session has its own copy of packaged variables.
Ø Thus when pkg is recompiled the runtime
dependency is followed, which invalidates the block and raises the oracle
error.
Ø Runtime dependencies exist only on
package state. This includes variables and cursors declared in a package.
Ø If the package had no global variables,
the second execution of the anonymous block would have succeeded.
PURITY LEVELS
In general, calls to subprograms are procedural, they cannot be
called from SQL statements. However, if a stand-alone or
packaged function meets certain restrictions, it can be called during execution
of a SQL statement.
User-defined functions are called the same way as built-in
functions but it must meet different restrictions. These restrictions are
defined in terms of purity levels.
There are four types of purity levels.
WNDS -- Writes No Database State
RNDS -- Reads No Database State
WNPS -- Writes No Package State
WNPS -- Writes No Package State
RNPS -- Reads No Package State
In addition to the preceding restrictions, a user-defined
function must also meet the following requirements to be called from a SQL statement.
Ø The function has to be stored in the
database, either stand-alone or as part of a
package.
Ø The function can take only in parametes.
Ø The formal parameters must use only
database types, not PL/SQL types such as
boolean or record.
Ø The return type of the function must also
be a database type.
Ø The function must not end the current
transaction with commit or rollback, or
rollback to a savepoint prior to the function execution.
Ø It also must not issue any alter session
or alter system commands.
RESTRICT_REFERENCES
For packaged functions, however, the RESTRICT_REFERENCES pragma is required to specify the purity
level of a given function.
Syntax:
PRAGMA RESTRICT_REFERENCES(subprogram_name
or package_name, WNDS
[,WNPS]
[,RNDS] [,RNPS]);
Ex:
CREATE OR REPLACE PACKAGE PKG IS
function fun1 return varchar;
pragma
restrict_references(fun1,wnds);
function fun2 return varchar;
pragma
restrict_references(fun2,wnds);
END PKG;
CREATE OR REPLACE PACKAGE BODY PKG IS
FUNCTION FUN1 return varchar IS
BEGIN
update dept set deptno = 11;
return 'hello';
END FUN1;
FUNCTION
FUN2 return varchar IS
BEGIN
update dept set dname ='aa';
return 'hello';
END FUN2;
END PKG;
The above package body will not created, it will give the
following erros.
PLS-00452:
Subprogram 'FUN1' violates its associated pragma
PLS-00452:
Subprogram 'FUN2' violates its associated pragma
CREATE OR REPLACE PACKAGE BODY PKG IS
FUNCTION FUN1 return varchar IS
BEGIN
return 'hello';
END FUN1;
FUNCTION
FUN2 return varchar IS
BEGIN
return 'hello';
END FUN2;
END PKG;
Now the package body will be created.
DEFAULT
If there is no RESTRICT_REFERENCES pragma associated with a given packaged
function, it will not have any purity level asserted. However, you can change
the default purity level for a package. The DEFAULT
keyword is used instead of the subprogram name in the pragma.
Ex:
CREATE OR REPLACE PACKAGE PKG IS
pragma
restrict_references(default,wnds);
function fun1 return varchar;
function fun2 return varchar;
END PKG;
CREATE OR REPLACE PACKAGE BODY PKG IS
FUNCTION FUN1 return varchar IS
BEGIN
update dept set deptno = 11;
return 'hello';
END FUN1;
FUNCTION
FUN2 return varchar IS
BEGIN
update dept set dname ='aa';
return 'hello';
END FUN2;
END PKG;
The above package body will not created, it will give the
following erros because the pragma will apply to all the functions.
PLS-00452:
Subprogram 'FUN1' violates its associated pragma
PLS-00452:
Subprogram 'FUN2' violates its associated pragma
CREATE OR REPLACE PACKAGE BODY PKG IS
FUNCTION FUN1 return varchar IS
BEGIN
return 'hello';
END FUN1;
FUNCTION
FUN2 return varchar IS
BEGIN
return 'hello';
END FUN2;
END PKG;
Now the package body will be created.
TRUST
If the TRUST keyword is present, the restrictions
listed in the pragma are not enforced. Rather, they are trusted to be true.
Ex:
CREATE OR REPLACE PACKAGE PKG IS
function fun1 return varchar;
pragma
restrict_references(fun1,wnds,trust);
function fun2 return varchar;
pragma
restrict_references(fun2,wnds,trust);
END PKG;
CREATE OR REPLACE PACKAGE BODY PKG IS
FUNCTION FUN1 return varchar IS
BEGIN
update dept set deptno = 11;
return 'hello';
END FUN1;
FUNCTION
FUN2 return varchar IS
BEGIN
update dept set dname ='aa';
return 'hello';
END FUN2;
END PKG;
The above package will be created successfully.
IMPORTANT POINTS ABOUT RESTRICT_REFERENCES
Ø This pragma can appear anywhere in the
package specification, after the function
declaration.
Ø It can apply to only one function
definition.
Ø For overload functions, the pragma
applies to the nearest definition prior to the
Pragma.
Ø This pragma is required only for packages
functions not for stand-alone functions.
Ø The Pragma can be declared only inside
the package specification.
Ø The pragma is checked at compile time,
not runtime.
Ø
It is
possible to specify without any purity levels when trust or combination of
default and trust
keywords are present.
PINNING IN THE SHARED POOL
The shared pool is the
portion of the SGS that contains, among other things, the p-code of compiled
subprograms as they are run. The first time a stored a store subprogram is
called, the p-code is loaded from disk into the shared pool. Once the object is
no longer referenced, it is free to be aged out. Objects are aged out of the
shared pool using an LRU(Least Recently Used) algorithm.
The DBMS_SHARED_POOL package allows you to pin objects in the
shared pool. When an object is pinned, it will never be aged out until you
request it, no matter how full the pool gets or how often the object is
accessed. This can improve performance, as it takes time to reload a package
from disk.
DBMS_SHARED_POOL has
four procedures
Ø KEEP
Ø UNKEEP
Ø SIZES
Ø ABORTED_REQUEST_THRESHOLD
KEEP
The DBMS_SHARED_POOL.KEEP procedure is used to pin objects in the
pool.
Syntax:
PROCEDURE KEEP(object_name
varchar2,flag char default ‘P’);
Here the flag represents different types of flag values for
different types of objects.
P -- Package, function or procedure
Q -- Sequence
R -- Trigger
C -- SQL Cursor
T -- Object type
JS -- Java source
JC -- Java class
JR -- Java resource
JD -- Java shared data
UNKEEP
UNKEEP is
the only way to remove a kept object from the shared pool, without restarting
the database. Kept objects are never aged out automatically.
Syntax:
PROCEDURE UNKEEP(object_name
varchar2, flag char default ‘P’);
SIZES
SIZES will
echo the contents of the shared pool to the screen.
Syntax:
PROCEDURE SIZES(minsize
number);
Objects with greater than the minsize will be returned. SIZES uses
DBMS_OUTPUT to return the data.
ABORTED_REQUEST_THRESHOLD
When the database determines that there is not enough memory in
the shared pool to satisfy a given request, it will begin aging objects out
until there is enough memory. It enough objects are aged out, this can have a
performance impact on other database sessions. The ABORTED_REQUEST_THRESHOLD can be used to remedy this.
Syntax:
PROCEDURE ABORTED_REQUEST_THRESHOLD(threshold_size
number);
Once this procedure is called, oracle will not start aging
objects from the pool unless at least threshold_size
bytes is needed.
DATA MODEL FOR SUBPROGRAMS AND PACKAGES
Ø USER_OBJECTS
Ø USER_SOURCE
Ø USER_ERRORS
Ø DBA_OBJECTS
Ø DBA_SOURCE
Ø DBA_ERRORS
Ø ALL_OBJECTS
Ø ALL_SOURCE
Ø ALL_ERRORS
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
CURSOR STAGES
Ø Open
Ø Fetch
Ø Close
CURSOR ATTRIBUTES
Ø %found
Ø %notfound
Ø %rowcount
Ø %isopen
Ø %bulk_rowcount
Ø %bulk_exceptions
CURSOR DECLERATION
Syntax:
Cursor <cursor_name> is select statement;
Ex:
Cursor c is select
*from dept;
CURSOR LOOPS
Ø Simple loop
Ø While loop
Ø For loop
SIMPLE LOOP
Syntax:
Loop
Fetch <cursor_name> into <record_variable>;
Exit when <cursor_name> % notfound;
<statements>;
End loop;
Ex:
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 <cursor_name> % found loop
Fetch <cursor_name> nto <record_variable>;
<statements>;
End loop;
Ex:
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 <record_variable> in <cursor_name>
loop
<statements>;
End loop;
Ex:
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:
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.
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 can not be closed implicitly.
Ø Ref cursor with return type is called strong cursor.
Ø Ref cursor with out 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 a
parameters to the subprograms.
Ø Cursor variables modes are in or out or
in out.
Ø Cursor variables can not be declared in
package spec and package body (excluding subprograms).
Ø You can not user remote procedure calls
to pass cursor variables from one server to another.
Ø Cursor variables can not use for update
clause.
Ø You can not assign nulls to cursor
variables.
Ø You can not compare cursor variables for
equality, inequality and nullity.
Ex:
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 can not 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 can not be used when
declaring a view.
Ø Cursor expressions can be used as an
argument to table function.
Ø You can not perform bind and execute
operations on cursor expressions when using the cursor expressions in dynamic
SQL.
USING NESTED CURSORS OR CURSOR EXPRESSIONS
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;
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;
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
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
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.
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
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.
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.
No comments:
Post a Comment