BULK COLLECT

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10314474/viewspace-769050/