文档章节

oracle:IF语句 Loop循环 Cursor的用法(一)

ZHXIA
 ZHXIA
发布于 2015/01/03 17:46
字数 1660
阅读 5081
收藏 4

1 概述

在大多数时候我们在设计pl-sql程序的时候都遵循下面的步骤:  
打开游标 ->开始循环 -> 从游标中取值->检查那一行被返回 -> 处理 -> 关闭循环 -> 关闭游标

2 格式:复制内容到剪贴板/ 程序代码
CURSOR <游标名称> IS
<游标名称>%ROWTYPE;
BEGIN
  OPEN <游标名称>
  LOOP
    FETCH <游标名称> INTO ;
    EXIT WHEN <游标名称>%NOTFOUND;

    <其它要执行的代码>
  END LOOP;
  CLOSE <游标名称>;
END <存储过程名称>;
/

3 代码

代码例子:复制内容到剪贴板程序代码 程序代码
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;
/





© 著作权归作者所有

ZHXIA
粉丝 1
博文 87
码字总数 40896
作品 0
石景山
程序员
私信 提问
Oracle存储过程和游标基本语法

1 CREATE OR REPLACE PROCEDURE 存储过程名   2 IS   3 BEGIN   4 NULL;   5 END; 行1:   CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储...

沧海飞蝶
2014/06/19
2.5K
1
Oracle 参数 游标[游标更新删除数据]

一、参数游标 参数游标是带有参数的游标,在定义参数游标之后,当使用不同参数值多次打开游标时,可以产生不同的结果集,语法如下: cursor cursorname(parametername datatype) is select_...

技术小胖子
2017/11/16
0
0
Oracle --存储过程基本语法

一、存储过程的理解 create or replace procedure 存储过程名 as begin null end 行1:create or replace procedure 是一个SQL语句,通知oracle数据库去创建一个叫做skeleton储存过程,如果存...

求是科技
2015/04/09
215
0
Oracle存储过程基本语法介绍(转)

转自该地址:http://www.jb51.net/article/31805.htm 正文如下: Oracle存储过程基本语法介绍,有需求的朋友可以参考下 <SCRIPT type=text/javascript>dy("artup")<SCRIPT type=text/javasc......

功夫panda
2015/12/13
171
0
ORACLE中DBMS_SQL的用法

对于一般的select操作,如果使用动态的sql语句则需要进行以下几个步骤: open cursor---> parse---> define column---> excute---> fetch rows---> close cursor; 而对于dml操作(insert,updat......

紫地瓜
2012/12/06
69
0

没有更多内容

加载失败,请刷新页面

加载更多

规则引擎

解决问题 版本迭代速度更不上业务变化,但是若多个业务同时变化,除了为每个业务设计专属配置项也不利于操作。就想服务接口单纯化,将复杂多变的业务逻辑交给规则引擎,让用户在web端或cs端自...

无极之岚
33分钟前
4
0
OSChina 周三乱弹 —— 欢迎你来做产品经理

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @巴拉迪维 :10多次劲歌金曲获奖,更多叱咤歌坛排名,黎明才应该是四大天王之首,只可惜拍的电影太少。单曲循环一个多月的歌,力荐 《无名份的...

小小编辑
今天
249
9
500行代码,教你用python写个微信飞机大战

这几天在重温微信小游戏的飞机大战,玩着玩着就在思考人生了,这飞机大战怎么就可以做的那么好,操作简单,简单上手。 帮助蹲厕族、YP族、饭圈女孩在无聊之余可以有一样东西让他们振作起来!...

上海小胖
今天
10
0
关于AsyncTask的onPostExcute方法是否会在Activity重建过程中调用的问题

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 本文链接:https://blog.csdn.net/XG1057415595/article/details/86774575 假设下面一种情况...

shzwork
今天
7
0
object 类中有哪些方法?

getClass(): 获取运行时类的对象 equals():判断其他对象是否与此对象相等 hashcode():返回该对象的哈希码值 toString():返回该对象的字符串表示 clone(): 创建并返此对象的一个副本 wait...

happywe
今天
7
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部