Cursor Loop-CSDN博客

Cursor Loop

Explicitly declared cursor and record

CREATE OR REPLACE PROCEDURE IS

CURSOR IS

%ROWTYPE;

BEGIN
OPEN
LOOP
    FETCH INTO ;
    EXIT WHEN %NOTFOUND;


END LOOP;
CLOSE ;
END ;
/

TRUNCATE TABLE loop_test;

DECLARE

CURSOR ao_cur IS
SELECT SUBSTR(object_name,1,5) FIRSTFIVE
FROM all_objs
WHERE SUBSTR(object_name,1,5) BETWEEN \'A\' AND \'M\';

ao_rec ao_cur%ROWTYPE;

BEGIN
OPEN ao_cur;
LOOP
    FETCH ao_cur INTO ao_rec;
    EXIT WHEN ao_cur%NOTFOUND;

    INSERT INTO loop_test VALUES (ao_rec.firstfive);
END LOOP;
COMMIT;
CLOSE ao_cur;
END;
/

SELECT COUNT(*) FROM loop_test;

Fetch Demo

-- Demo courtesy of Andy Hassall

CREATE TABLE t1 (tcol NUMBER);

CREATE TABLE t2 (c NUMBER);

BEGIN
FOR i IN 1..500 LOOP
INSERT INTO t1 VALUES (i);
END LOOP;
END;
/

SELECT COUNT(*) FROM t1;

COMMIT;

CREATE OR REPLACE FUNCTION p (c_in NUMBER) RETURN NUMBER IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
-- Every time this is called, write a row to table t2
INSERT INTO t2 VALUES (c_in);
COMMIT;
RETURN c_in;
END p;
/

DECLARE
BEGIN
/* Open up a cursor for loop, also selecting
* the "p" function which will write rows to
* t2 for every row fetched from t1. */

FOR crec IN (SELECT tcol, p(tcol) FROM t1) LOOP
-- Break out of the loop immediately
EXIT;
END LOOP;
END;
/

SELECT COUNT(*) FROM t2;

Cursor FOR Loop

Explicitly declared cursor and implicit record declared by the FOR loop

CREATE OR REPLACE PROCEDURE IS

CURSOR IS

BEGIN
FOR IN
LOOP

END LOOP;
END ;
/

TRUNCATE TABLE loop_test;

DECLARE
CURSOR ao_cur IS
   SELECT SUBSTR(object_name,1,5) FIRSTFIVE
   FROM all_objs
   WHERE SUBSTR(object_name,1,5) BETWEEN \'N\' AND \'W\';
BEGIN
FOR ao_rec IN ao_cur LOOP
    INSERT INTO loop_test VALUES (ao_rec.firstfive);
END LOOP;
COMMIT;
END;
/

SELECT COUNT(*) FROM loop_test;

A FOR loop with an intrinsic cursor. A record is implicitly declared by the variable in the FOR statement.

BEGIN
FOR IN
LOOP

END LOOP;
END ;
/

TRUNCATE TABLE loop_test;

BEGIN
FOR ao_rec IN (
    SELECT SUBSTR(object_name,1,5) FIRSTFIVE
    FROM all_objs
    WHERE SUBSTR(object_name,1,5) BETWEEN \'N\' AND \'Z\')
LOOP
    INSERT INTO loop_test VALUES (ao_rec.firstfive);
END LOOP;
COMMIT;
END;
/

SELECT COUNT(*) FROM loop_test;

Cursor Loop With
WHERE CURRENT OF Clause

CREATE OR REPLACE PROCEDURE IS

BEGIN
FOR IN ()
LOOP

    UPDATE
    SET =
    WHERE CURRENT OF
END LOOP;
END ;
/

CREATE TABLE test (
pid NUMBER(3),
cash NUMBER(10,2));

INSERT INTO test VALUES (100, 10000.73);
INSERT INTO test VALUES (200 25000.26);
INSERT INTO test VALUES (300, 30000.11);
INSERT INTO test VALUES (400, 45000.99);
INSERT INTO test VALUES (500, 50000.08);
COMMIT;

CREATE OR REPLACE PROCEDURE wco IS

CURSOR x_cur IS
SELECT pid, cash
FROM test
WHERE cash < 35000
FOR UPDATE;

BEGIN
FOR x_rec IN x_cur LOOP
    UPDATE test
    SET cash = FLOOR(cash)
    WHERE CURRENT OF x_cur;
END LOOP;
COMMIT;
END wco;
/

exec wco;

SELECT * FROM test;

Nested Cursor Loops

Demo Tables

CREATE TABLE airplanes (
program_id VARCHAR2(3),
line_number NUMBER(10),
customer_id VARCHAR2(4),
order_date DATE,
delivered_date DATE)
PCTFREE 0;

CREATE INDEX programid_idx
ON airplanes (program_id)
PCTFREE 0;

CREATE TABLE parts (
program_id VARCHAR2(3),
line_type VARCHAR2(4),
part_type VARCHAR2(10),
quantity NUMBER(3));

CREATE TABLE ap_parts AS
SELECT a.customer_id, p.part_type, p.quantity
FROM airplanes a, parts p
WHERE a.program_id = p.program_id
AND 1=2;

Load Airplanes

DECLARE
progid airplanes.program_id%TYPE;
lineno airplanes.line_number%TYPE;
custid airplanes.customer_id%TYPE := \'AAL\';
orddate airplanes.order_date%TYPE;
deldate airplanes.delivered_date%TYPE;
BEGIN
FOR i IN 1 .. 5 LOOP
    SELECT DECODE(i, 1, \'737\', 2, \'747\', 3, \'757\', 4, \'767\', 5, \'777\')
    INTO progid
    FROM DUAL;

    FOR lineno IN 1..250 LOOP
    SELECT DECODE(custid, \'AAL\',\'DAL\',\'DAL\',\'SAL\',\'SAL\',\'ILC\',
     \'ILC\',\'SWA\', \'SWA\',\'NWO\',\'NWO\',\'AAL\')
      INTO custid
      FROM DUAL;

    IF progid = \'737\' THEN
    OrdDate := SYSDATE + lineno;
    DelDate := OrdDate + lineno + 100;
    ELSIF progid = \'747\' THEN
    OrdDate := SYSDATE + lineno+17;
    DelDate := OrdDate + lineno + 302;
    ELSIF progid = \'757\' THEN
    OrdDate := SYSDATE + lineno+22;
    DelDate := OrdDate + lineno + 202;
    ELSIF progid = \'767\' THEN
    OrdDate := SYSDATE + lineno+43;
    DelDate := OrdDate + lineno + 189;
    ELSIF progid = \'777\' THEN
    OrdDate := SYSDATE + lineno-69;
    DelDate := OrdDate + lineno + 299;
    END IF;

    INSERT INTO airplanes
    (program_id, line_number, customer_id, order_date,
       delivered_date)
    VALUES
      (progid, lineno, custid, orddate, deldate);
END LOOP;
END LOOP;
COMMIT;
END load_airplanes;
/

Load Airplane Parts

BEGIN
INSERT INTO parts VALUES (\'737\', \'Even\', \'Wing\', 2);
INSERT INTO parts VALUES (\'747\', \'Even\', \'Wing\', 2);
INSERT INTO parts VALUES (\'757\', \'Even\', \'Wing\', 2);
INSERT INTO parts VALUES (\'767\', \'EVen\', \'Wing\', 2);
INSERT INTO parts VALUES (\'777\', \'even\', \'Wing\', 2);
INSERT INTO parts VALUES (\'737\', \'ODD\', \'Wing\', 2);
INSERT INTO parts VALUES (\'747\', \'odd\', \'Wing\', 2);
INSERT INTO parts VALUES (\'757\', \'Odd\', \'Wing\', 2);
INSERT INTO parts VALUES (\'767\', \'Odd\', \'Wing\', 2);
INSERT INTO parts VALUES (\'777\', \'Odd\', \'Wing\', 2);
INSERT INTO parts VALUES (\'737\', \'Even\', \'Galley\', 1);
INSERT INTO parts VALUES (\'747\', \'EVen\', \'Galley\', 3);
INSERT INTO parts VALUES (\'757\', \'EVEN\', \'Galley\', 3);
INSERT INTO parts VALUES (\'767\', \'EVeN\', \'Galley\', 2);
INSERT INTO parts VALUES (\'777\', \'even\', \'Galley\', 3);
INSERT INTO parts VALUES (\'737\', \'ODD\', \'Galley\', 2);
INSERT INTO parts VALUES (\'747\', \'odd\', \'Galley\', 4);
INSERT INTO parts VALUES (\'757\', \'Odd\', \'Galley\', 3);
INSERT INTO parts VALUES (\'767\', \'ODd\', \'Galley\', 4);
INSERT INTO parts VALUES (\'777\', \'odD\', \'Galley\', 4);
INSERT INTO parts VALUES (\'737\', \'Even\', \'Tire\', 10);
INSERT INTO parts VALUES (\'747\', \'Even\', \'Tire\', 18);
INSERT INTO parts VALUES (\'757\', \'Even\', \'Tire\', 12);
INSERT INTO parts VALUES (\'767\', \'Even\', \'Tire\', 14);
INSERT INTO parts VALUES (\'777\', \'EveN\', \'Tire\', 16);
INSERT INTO parts VALUES (\'737\', \'ODD\', \'Tire\', 14);
INSERT INTO parts VALUES (\'747\', \'Odd\', \'Tire\', 20);
INSERT INTO parts VALUES (\'757\', \'Odd\', \'Tire\', 14);
INSERT INTO parts VALUES (\'767\', \'Odd\', \'Tire\', 16);
INSERT INTO parts VALUES (\'777\', \'Odd\', \'Tire\', 18);
INSERT INTO parts VALUES (\'737\', \'Even\', \'Seats\', 105);
INSERT INTO parts VALUES (\'747\', \'Even\', \'Seats\', 255);
INSERT INTO parts VALUES (\'757\', \'Even\', \'Seats\', 140);
INSERT INTO parts VALUES (\'767\', \'Even\', \'Seats\', 200);
INSERT INTO parts VALUES (\'777\', \'EveN\', \'Seats\', 210);
INSERT INTO parts VALUES (\'737\', \'ODD\', \'Seats\', 137);
INSERT INTO parts VALUES (\'747\', \'Odd\', \'Seats\', 20);
INSERT INTO parts VALUES (\'757\', \'Odd\', \'Seats\', 166);
INSERT INTO parts VALUES (\'767\', \'Odd\', \'Seats\', 345);
INSERT INTO parts VALUES (\'777\', \'Odd\', \'Seats\', 267);
COMMIT;
END;
/

Nested Loops With Static Cursors

CREATE OR REPLACE PROCEDURE nested_loop IS

CURSOR a_cur IS
SELECT program_id, line_number, customer_id
FROM airplanes;

a_rec a_cur%ROWTYPE;

CURSOR p_cur IS
SELECT part_type, quantity
FROM parts
WHERE program_id = a_rec.program_id
AND UPPER(line_type)=DECODE(MOD(a_rec.line_number,2),0,\'EVEN\',\'ODD\');

p_rec p_cur%ROWTYPE;

BEGIN
OPEN a_cur;
LOOP
    FETCH a_cur INTO a_rec;
    EXIT WHEN a_cur%NOTFOUND;

    OPEN p_cur;
    LOOP
      FETCH p_cur INTO p_rec;
      EXIT WHEN p_cur%NOTFOUND;
      INSERT INTO ap_parts
      (customer_id, part_type, quantity)
      VALUES
      (a_rec.customer_id, p_rec.part_type, p_rec.quantity);
    END LOOP;
    CLOSE p_cur;

END LOOP;
COMMIT;
CLOSE a_cur;
END nested_loop;
/

转载于:http://blog.itpub.net/26687597/viewspace-1203660/