你的位置:首页 > 数据库

[数据库]PL/SQL学习礼记一 Bulk_Collect 调用方式集锦


事先申明,本文所有示例都皆源于《Expert PL SQL Practices》这本电子书的第六章。小陈觉得在学习PLSQL的过程中,将来或许会用到,在此笔记一番。

正文如下:

  首先准备基础数据吧 HARDWARE 表。

  表结构如图所示:

  

 

  然后插入1,000,000条数据吧。这里不得不说,PLSQL里面,dual表的确帮助很大,当然T-SQL里面你也可以建一张辅助表的。这里用的Oracle SQL Developer自带的格式化功能,相当弱。

  

INSERT INTO HARDWARESELECT TRUNC(rownum/1000)+1 aisle, rownum item, 'Description ' ||rownum descrFROM ( SELECT 1 FROM dual CONNECT BY level <= 1000 ), ( SELECT 1 FROM dual CONNECT BY level <= 1000 );

这里先说最古老的用法吧:

SET serveroutput ON;cl scr;DECLARE l_cursor INT := dbms_sql.open_cursor; l_num_row dbms_sql.number_table; l_exec     INT; l_fetched_rows INT;BEGIN dbms_sql.parse( l_cursor, 'select item from hardware where item <= 1200', dbms_sql.native); dbms_sql.define_array(l_cursor,1,l_num_row,500,1); l_exec := dbms_sql.execute(l_cursor); LOOP  l_fetched_rows := dbms_sql.fetch_rows(l_cursor);  dbms_sql.column_value(l_cursor, 1, l_num_row);  dbms_output.put_line('Fetched '||l_fetched_rows||' rows');  EXIT WHEN l_fetched_rows < 500; END LOOP; dbms_sql.close_cursor(l_cursor);END; 

 

上面的调用极不推荐,进入主题吧。

1.     Implicit Cursor

DECLARE l_descr hardware.descr%type;BEGIN SELECT descr INTO  l_descr FROM  hardware WHERE  aisle = 1     AND item = 1;END;


2.    
Explicit Fetch Calls 

DECLARE CURSOR c_tool_list IS  SELECT descr FROM hardware WHERE aisle = 1 AND item BETWEEN 1 AND 500; l_descr hardware.descr%type;BEGIN OPEN c_tool_list; LOOP  FETCH c_tool_list INTO l_descr;  EXIT WHEN c_tool_list%notfound; END LOOP; CLOSE c_tool_list;END;


3.    
Implicit Fetch Calls 

BEGIN FOR i IN ( SELECT descr FROM hardware WHERE aisle = 1 AND item BETWEEN 1 AND 500 ) LOOP  pl(i.descr);--<processing code FOR EACH row> END LOOP;END;BEGIN FOR i IN ( SELECT descr FROM hardware WHERE aisle = 1 AND item BETWEEN 1 AND 500 ) LOOP  --<processing code for each row> END LOOP;END;


4.    
Implicit Cursor BULK Mode 

DECLAREtype t_descr_listIS TABLE OF hardware.descr%type; l_descr_list t_descr_list;BEGIN SELECT descr bulk collect INTO l_descr_list FROM hardware WHERE aisle = 1 AND item BETWEEN 1 AND 100;END;

5.     Explicit Fetch Calls BULK Mode 

DECLARE CURSOR c_tool_list IS  SELECT descr FROM hardware WHERE aisle = 1 AND item BETWEEN 1 AND 500;type t_descr_listIS TABLE OF c_tool_list%rowtype; l_descr_list t_descr_list;BEGIN OPEN c_tool_list; FETCH c_tool_list bulk collect INTO l_descr_list; CLOSE c_tool_list;END;

 

最后给点彩蛋吧,还是书中的内容。比如T-SQL处理 

CREATE OR REPLACE type COMING_FROM_AS object (  COL1 INT,  COL2 INT)

DECLARE source_BEGIN source_= '<DEMO><COL1>10</COL1><COL2>20</COL2></DEMO>'); source_|| ',' || target_obj.COL2);END;

DECLARE l_refcursor SYS_REFCURSOR; l_BEGIN OPEN l_refcursor FOR SELECT aisle , item,descr FROM HARDWARE WHERE aisle = 1 AND item BETWEEN 1 AND 10; l_= END;DECLARE l_BEGIN l_= dbms_'SELECT aisle , item,descr FROM HARDWARE WHERE aisle = 1 AND item BETWEEN 1 AND 10' ); dbms_output.put_line(l_END;DECLARE  l_BEGIN  l_ctx := dbms_'SELECT aisle , item,descr FROM HARDWARE WHERE aisle = 1 AND item BETWEEN 1 AND 10'                 );  dbms_'HARDWARE');  dbms_'Store');  l_= dbms_End;

结语:小陈学习ORCALE时间不久,有的都是T-SQL功底。只从简洁上来说,还是觉得T-SQL好多了。如果以慈母严父来形容MS与ORACLE。那么前者绝对好妈妈,后者是坏爸爸。