Bulk_Collect 调用方式集锦
事先申明，本文所有示例都皆源于《Expert PL SQL Practices》这本电子书的第六章。小陈觉得在学习PLSQL的过程中，将来或许会用到，在此笔记一番。
正文如下：
首先准备基础数据吧 HARDWARE 表。
表结构如图所示:
然后插入1,000,000条数据吧。这里不得不说，PLSQL里面，dual表的确帮助很大，当然T-SQL里面你也可以建一张辅助表的。这里用的Oracle SQL Developer自带的格式化功能，相当弱。
INSERT INTO HARDWARE SELECT TRUNC(rownum/1000)+1 aisle, rownum item, 'Description ' ||rownum descr FROM ( 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
DECLARE type t_descr_list IS 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_list IS 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处理XML，ORACLE不知道甩了它几条街呢。
CREATE OR REPLACE type COMING_FROM_XML AS object ( COL1 INT, COL2 INT)
DECLARE source_xml xmltype; target_obj coming_from_xml; BEGIN source_xml := xmltype('<DEMO> <COL1>10</COL1> <COL2>20</COL2> </DEMO>'); source_xml.toObject(target_obj); dbms_output.put_line( target_obj.COL1 || ',' || target_obj.COL2); END;
DECLARE l_refcursor SYS_REFCURSOR; l_xmltype XMLTYPE; BEGIN OPEN l_refcursor FOR SELECT aisle , item,descr FROM HARDWARE WHERE aisle = 1 AND item BETWEEN 1 AND 10; l_xmltype := XMLTYPE(l_refcursor); dbms_output.put_line(l_xmltype.getClobVal); END; DECLARE l_xmltype XMLTYPE; BEGIN l_xmltype := dbms_xmlgen.getxmltype('SELECT aisle , item,descr FROM HARDWARE WHERE aisle = 1 AND item BETWEEN 1 AND 10' ); dbms_output.put_line(l_xmltype.getClobVal); END; DECLARE l_xmltype XMLTYPE; l_ctx dbms_xmlgen.ctxhandle; BEGIN l_ctx := dbms_xmlgen.newcontext('SELECT aisle , item,descr FROM HARDWARE WHERE aisle = 1 AND item BETWEEN 1 AND 10' ); dbms_xmlgen.setrowsettag(l_ctx, 'HARDWARE'); dbms_xmlgen.setrowtag(l_ctx, 'Store'); l_xmltype := dbms_xmlgen.getXmlType(l_ctx) ; dbms_xmlgen.closeContext(l_ctx); dbms_output.put_line(l_xmltype.getClobVal); End;
结语：小陈学习ORCALE时间不久，有的都是T-SQL功底。只从简洁上来说，还是觉得T-SQL好多了。如果以慈母严父来形容MS与ORACLE。那么前者绝对好妈妈，后者是坏爸爸。
