Cursors provide a handle to the SQL context area and allow ...



CURSORS

Every SQL DML statement, INSERT, UPDATE, DELETE, SELECT…INTO, COMMIT, ROLLBACK used in a PL/SQL block has an associated cursor. There are basically two types of curosors: Explicit (dealing with multiple row SELECT statements) and Implicit (All INSERT, UPDATE, DELETE and Single row SELECT statements). Explicit cursors provide a handle to the SQL context area and allow row by row processing of a table. When a SELECT statement returns multiple rows an explicit cursor allows us to process beyond the first row returned by a query by keeping track of the current row being processed.

Four Steps in defining and using a cursor:

1. Declare the cursor.

2. Open the cursor for a query.

3. Fetch the results into PL/SQL variables

4. Close the cursor

Let us look at the following example of a cursor:

DECLARE

Inv_Limit number(4) := 400

v_part_no inventory.part_no%type;

CURSOR c_partsorder IS

SELECT part_no FROM inventory where qty_on_hand > inv_limit;

BEGIN

OPEN c_partsorder;

LOOP

FETCH c_partsorder INTO v_part_no;

EXIT WHEN c_partsorder%NOTFOUND;

INSERT INTO TEST VALUES(v_part_no);

END LOOP;

CLOSE c_partsorder;

COMMIT;

END;

/

Declaring the Cursor:

DECLARE

CURSOR cursorname IS selectstatement

Note that the SELECT statement contains no INTO clause. INTO clause is part of the FETCH statement. A cursor declaration can reference PL/SQL variables in the SELECT clause. These are refered to as the BIND variables. Declared cursors have the same scope as variables within a PL/SQL block.

Example of a CURSOR declaration:

DECLARE

Inv_Limit number(4) := 400

v_part_no inventory.part_no%type;

CURSOR c_partsorder IS SELECT part_no FROM inventory where qty_on_hand > inv_limit;

Opening The Cursor

OPEN cursor_name

cursor_name should have been already been declared.

When a cursor is opened

1. The values of the bind variables are examined.

2. Based on the values of the bind variables, the active set is determined.

3. The active set pointer is set to the first row.

We could use a bind variable in the cursor as in the following example:

DECLARE

v_part_no inventory.part_no%type;

v_qtyhand inventory.qty_on_hand%type;

CURSOR c_partsorder IS

SELECT part_no FROM inventory where qty_on_hand > v_qtyhand;

BEGIN

v_qtyhand := 400;

OPEN c_partsorder;

LOOP

FETCH c_partsorder INTO v_part_no;

EXIT WHEN c_partsorder%NOTFOUND;

INSERT INTO TEST VALUES(v_part_no);

END LOOP;

CLOSE c_partsorder;

COMMIT;

END;

/

Note that the bind variable has a value before the cursor is opened.

FETCHing the Cursor

FETCH cusorname INTO list_of_variables;

Or

FETCH cursorname INTO PL/SQL record;

FETCH statement must have exactly one INTO variable for each column used in the SELECT statement. The variable get the values assigned in the order in which they appear in the SELECT statement.

In the declaration section we could have declared

v_inventoryrecord c_partsorder%rowtype;

then, while Fetching we could use

FETCH c_partsorder INTO v_inventoryrecord

Notice EXIT WHEN immediately after fetch.

CLOSING a cursor

CLOSE cursorname

The CLOSE for a cursor frees the resources held by the OPEN cursor. After CLOSE(ing), no more new rows can be fetched from the cursor.

%FOUND evaluates to true if prior FETCH returned a row

Example %FOUND

FETCH c_partsorder INTO v_part_no;

WHILE c_parsorder%FOUND LOOP

INSERT INTO TEST VALUES(v_part_no);

FETCH c_partsorder INTO v_part_no;

END LOOP;

%NOTFOUND evaluates to TRUE if prior FETCH returned no rows.

Example of %NOTFOUND

LOOP

FETCH c_partsorder INTO v_part_no;

EXIT WHEN c_partsorder%NOTFOUND;

INSERT INTO TEST VALUES(v_part_no);

END LOOP;

%ROWCOUNT evaluates to the total number of rows FETCHED thus far.

Example of %rowcount

LOOP

FETCH c_partsorder INTO v_part_no;

EXIT WHEN c_partsorder%NOTFOUND OR c_partsorder%rowcount > 10;

INSERT INTO TEST VALUES(v_part_no);

END LOOP;

%ISOPEN evaluates to TRUE if the associated cursor is open.

Example of %ISOPEN

IF c_partsorder%ISOPEN THEN

FETCH c_partsorder INTO v_part_no;

ELSE

OPEN c_partsorder;

END IF;

Another cursor application

CAREY Inc. has decided not more than $15000 will be paid as sales commission to its sales force. Table sales contains the following data:

|salesper_num |Salesper_name |Sales_amount |

|100 |Thompson Carey |45600 |

|101 |Steve Levin |36000 |

|102 |Cathy Reynolds |59000 |

|103 |Sandra Slater |48000 |

|104 |Rob Mittal |39000 |

|105 |Patricia Corbin |47000 |

10% commision is being paid to the sales force. We need to determine the cutoff amount for sales above which the 10% commission would be paid. A cursor can test different cutoff amounts and calculate whether the total commission to be paid exceeds the allocated amount of $15000.

DECLARE

Total_amount NUMBER(8,2) := 0;

Min_salesamt NUMBER(8) := 10000;

Comm_amt sales.sales_amount%type;

CURSOR salescommission IS

SELECT sales_amount * .10 FROM sales WHERE

sales_amount > Min_salesamt;

BEGIN

OPEN salescommission;

LOOP

FETCH salescommission INTO comm_amt;

EXIT WHEN salescommission%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(comm_amt);

Total_amount := total_amount + Comm_amt;

IF total_amount > 15000 THEN

Min_salesamt := Min_salesamt + 1000;

Total_amount := 0;

CLOSE salescommission;

OPEN salescommission;

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE(Min_salesamt);

END;

/

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download