ITPub博客

首页 > Linux操作系统 > Linux操作系统 > BULK COLLECT

BULK COLLECT

原创 Linux操作系统 作者:hky87 时间:2013-08-22 14:50:02 0 删除 编辑

BULK COLLECT的使用

先建立測試用的表及數據

CREATE TABLE plch_orders

(

   order_id       INTEGER PRIMARY KEY,

   order_amount   NUMBER

)

/

 

BEGIN

   INSERT INTO plch_orders VALUES (100, 5000);

   INSERT INTO plch_orders VALUES (200, 10000);

   COMMIT;

END;

/

1. SELECT …..

BULK COLLECT INTO……

 

DECLARE

 TYPE orders_t IS TABLE OF plch_orders%ROWTYPE;

 l_orders orders_t;

begin

SELECT  *

   BULK COLLECT INTO  l_orders

   FROM plch_orders

order by order_id;

for i in 1..l_orders.count

loop

  DBMS_OUTPUT.put_line(l_orders(i).order_id||':'||l_orders(i).order_amount);

end loop;

end;

/

100:5000

200:500

 

2. FETCH …  BULK INTO …

DECLARE

 TYPE orders_t IS TABLE OF plch_orders%ROWTYPE;

 l_orders orders_t;

 CURSOR orders_cur

 IS

      SELECT *

        FROM plch_orders

    ORDER BY order_id;

  BEGIN

     OPEN orders_cur;

     FETCH orders_cur BULK COLLECT INTO l_orders;

  for i in 1..l_orders.count

 loop

   DBMS_OUTPUT.put_line(l_orders(i).order_id||':'||l_orders(i).order_amount);

 end loop;

 CLOSE orders_cur;

 END;

 /

 

3. EXECUTE IMMEDIATE … BULK COLLECT INTO …

DECLARE

  TYPE orders_t IS TABLE OF plch_orders%ROWTYPE;

  l_orders orders_t;

BEGIN

   EXECUTE IMMEDIATE

       'SELECT * FROM plch_orders

      ORDER BY order_id'

      BULK COLLECT INTO l_orders;

  for i in 1..l_orders.count

 loop

   DBMS_OUTPUT.put_line(l_orders(i).order_id||':'||l_orders(i).order_amount);

 end loop;

end;

/

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10314474/viewspace-769050/,如需转载,请注明出处,否则将追究法律责任。

上一篇: TO_CHAR()
请登录后发表评论 登录
全部评论

注册时间:2009-10-09

  • 博文量
    87
  • 访问量
    255485