游标FOR循环
在大多数时候我们在设计pl-sql程序的时候都遵循下面的步骤：
打开游标 ->开始循环 -> 从游标中取值->检查那一行被返回 -> 处理 -> 关闭循环 -> 关闭游标
要在程序中使用游标，必须首先声明游标
Cursor Loop
显示声明游标及记录组
格式:
复制内容到剪贴板
程序代码
程序代码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;
[code]
Demo
[code]
Fetch Demo
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;
注意:%ROWTYPE也可以用游标名来定义，但必须要首先声明游标,所以上面的例子要采用显示的游标声明
Cursor 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;
Curcor For Loop是一个隐式的游标声明.
格式:
复制内容到剪贴板 程序代码
程序代码BEGIN
FOR IN
LOOP
<其它要执行的代码>
END LOOP;
END <存储过程名称>;
/
Demo
复制内容到剪贴板 程序代码
程序代码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 <存储过程名称>;
/
Demo:
复制内容到剪贴板 程序代码
程序代码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
测试样表
复制内容到剪贴板 程序代码
程序代码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;
PL-SQL Example
复制内容到剪贴板 程序代码
程序代码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;
/
[code]
嵌套固定的游标
Nested Loops With Static Cursors
DeMo:
[code]
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;
/
嵌套使用BLUK COLLECT获取数据集的循环
Nested Loops with Bulk Collect
这个demo是为了显示通过BULK COLLECTION来复制嵌套循环中的游标里的数据
Demo
复制内容到剪贴板 程序代码
程序代码Create or REPLACE PROCEDURE bulk_nested IS
CURSOR a_cur IS
Select program_id, line_number, customer_id
FROM airplanes;
TYPE ap_array IS TABLE OF airplanes.program_id%TYPE
INDEX BY BINARY_INTEGER;
ap_rec ap_array;
TYPE al_array IS TABLE OF airplanes.line_number%TYPE
INDEX BY BINARY_INTEGER;
al_rec al_array;
TYPE ac_array IS TABLE OF airplanes.customer_id%TYPE
INDEX BY BINARY_INTEGER;
ac_rec ac_array;
TYPE pp_array IS TABLE OF parts.part_type%TYPE
INDEX BY BINARY_INTEGER;
pp_rec pp_array;
TYPE pq_array IS TABLE OF parts.quantity%TYPE
INDEX BY BINARY_INTEGER;
pq_rec pq_array;
BEGIN
OPEN a_cur;
LOOP
FETCH a_cur BULK COLLECT INTO ap_rec, al_rec, ac_rec LIMIT 500;
FOR i IN 1 .. ap_rec.COUNT LOOP
DECLARE
CURSOR p_cur IS
Select part_type, quantity
FROM parts
Where program_id = ap_rec(i)
AND UPPER(line_type)=DECODE(MOD(al_rec(i),2),0,'EVEN','ODD');
BEGIN
OPEN p_cur;
LOOP
FETCH p_cur BULK COLLECT INTO pp_rec, pq_rec;
FORALL j IN pp_rec.FIRST .. pp_rec.LAST
Insert INTO ap_parts
(customer_id, part_type, quantity)
VALUES
(ap_rec(i), pp_rec(j), pq_rec(j));
EXIT WHEN p_cur%NOTFOUND;
END LOOP;
CLOSE p_cur;
END;
END LOOP;
EXIT WHEN a_cur%NOTFOUND;
END LOOP;
COMMIT;
CLOSE a_cur;
END bulk_nested;
/
