introduction to PL/SQL cursors

3 أبريل 2005

PL/SQL introduces a new dimension of power programming capability for the developer. The goal of reusable code measurely improves quality verse vast amounts of non-reusable SQL. PL/SQL packages describe function and procedure specification and body. The specification describes the package interface to the calling application. The package specification approach is part of good design. The package specification exposes procedures and their parameter properties to external applications. Each specification has a package body. Within the package body various data structures, such as arrays and tables can be referenced, functions called, and procedures executed. Developers new to PL/SQL often understand insert, update, and delete data manipulations; but are confused about selection. This is because they don't know about cursors.

Two steps to creating a PL/SQL Package
1) Create the specifications Contains the declarative descriptions of the function, procedures, and gobal variables.
2) Create the body
Step 1
CREATE OR REPLACE PACKAGE {package_name} AS
/*Procedures*/
PROCEDURE myProcedure (param1 IN NUMBER, param2 in NUMBER);
/*Function*/
FUNCTION myFunction (param1 IN varchar2, param2 IN varchar2) RETURN NUMBER IS retparam NUMBER;
/*variable declaration */
bFlag Number(1);
/*cursor variables*/
TYPE type_C1 IS REF CURSOR RETURN myTable%ROWTYPE;
END {package_name};
Step 2
CREATE OR REPLACE PACKAGE BODY {package_name} AS
/*Procedures*/
PROCEDURE myProcedure ( param1 IN NUMBER, param2 in Number) IS
BEGIN
/*Add your Code*/
END myProcedure;
/*Functions*/
FUNCTION myFunction (param1 IN varchar2, param2 IN varchar2) RETURN NUMBER IS retparam NUMBER;
BEGIN
/*Add your Code*/
END myFunction;
END {package_name};

PL/SQL uses the concept of a cursor to return data records. A cursor defines the records to be returned based on a SQL statement. A cursor returns one record at a time. Cursors are very efficient not requiring large amounts of memory to operate. SQL criteria can be defined by binding field name values to bind variables. Bind variables can be passed into a procedure or function and create cursor selection criteria.

Once the cursor has been declared and bind variable associated with the cursor variable, the cursor variable can be opened. There are two ways to access a cursor fetched row. The first method is through field variables inwhich the cursor row is fetched. The bind variable is type casted to a specific field name and its cast type.

FETCH C1 INTO v_field1,field2;

The second method is through a table row variable, inwhich the cursor is fetched into. Retrieve values from a row variable fetch is easy. Reference the row variable proceded by a "." and annotate the field name.

FETCH C1 INTO v_row;

Once the data row has be fetched various data manipulation can be applied, such as, update, delete, and insert. A cursor can be defined as select SQL for update.

CURSOR C1 IS
SELECT * FROM mytable
where field1 = v_field1
and field2 = v_field2
FOR UPDATE OF field1;
So as the data row is being fetch it can be updated.
update mytable
set field1 = field1 * 1.01;
where current of C1;

Parameters can be passed to a Cursor declaration. The cursor can be open with parameters.

CURSOR C1 (pField1 myTable.field1%TYPE, pField2 myTable.field2.%TYPE) IS
Select * from mytable
where field1=pField1
and field2=pField2;

Defining and using Cursors inside a function or procedure

Using Bind Variables:
v_field1 mytable.field1%TYPE;
v_field2 mytable.field2%TYPE;
v_row mytable%ROWTYPE;
-- DECLARE THE CURSOR
CURSOR C1 IS
SELECT * FROM mytable
where field1 = v_field1
and field2 = v_field2;
BEGIN
-- OPEN A PREVIOUSLY DECLARED CURSOR
OPEN C1;
LOOP
FETCH C1 INTO v_row;
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
-- CLOSE THE CURSOR
END;
Using a While Structure to Fetch Rows
v_field1 mytable.field1%TYPE;
v_field2 mytable.field2%TYPE;
v_row mytable%ROWTYPE;
-- DECLARE THE CURSOR
CURSOR C1 IS
SELECT * FROM mytable
where field1 = v_field1
and field2 = v_field2;
BEGIN
-- OPEN A PREVIOUSLY DECLARED CURSOR
OPEN C1;
FETCH C1 INTO v_row;
WHILE C1%FOUND LOOP
FETCH C1 INTO v_row;
END LOOP;
CLOSE C1;
-- CLOSE THE CURSOR
END;
Using a For Structure to Fetch Rows
v_field1 mytable.field1%TYPE;
v_field2 mytable.field2%TYPE;
v_row mytable%ROWTYPE;
-- DECLARE THE CURSOR
CURSOR C1 IS
SELECT * FROM mytable
where field1 = v_field1
and field2 = v_field2;
BEGIN
-- OPEN A PREVIOUSLY DECLARED CURSOR
OPEN C1;
FOR v_row IN C1
-- AN IMPLICIT FETCH
-- IS EXECUTED
-- AN IMPLICIT CHECK
FOR C1%NOTFOUND is performed
END LOOP
-- AN IMPLICIT CLOSE OF
-- THE CURSOR IS PERFORMED
-- CLOSE THE CURSOR
END;

WHERE CURRENT OF
v_field1 mytable.field1%TYPE;
v_field2 mytable.field2%TYPE;
v_row mytable%ROWTYPE;
-- DECLARE THE CURSOR
CURSOR C1 IS
SELECT * FROM mytable
where field1 = v_field1
and field2 = v_field2
FOR UPDATE OF field1;
BEGIN
-- OPEN A PREVIOUSLY DECLARED CURSOR
OPEN C1;
FOR v_row IN C1
-- Update on the column list
--in the FOR UPDATE clause
update mytable
set field1 = field1 * 1.01;
where current of C1;
END LOOP
-- AN IMPLICIT CLOSE OF THE
-- CURSOR IS PERFORMED
-- CLOSE THE CURSOR
END;
Cursor Variables
TYPE type_C1 IS REF CURSOR
RETURN myTable%ROWTYPE;
v_C1 type_C1;
v_row mytable%ROWTYPE;
BEGIN
OPEN v_C1 FOR
SELECT * FROM mytable
where field1 = v_field1
and field2 = v_field2
FOR UPDATE OF field1;
FETCH C1 INTO v_row;
WHILE C1%FOUND LOOP
FETCH C1 INTO v_row;
END LOOP;
CLOSE C1;
END