首先准备基础数据吧 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);--
END LOOP;
END;
BEGIN
FOR i IN
( SELECT descr FROM hardware WHERE aisle = 1 AND item BETWEEN 1 AND 500
)
LOOP
--
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('
10
20
');
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');
