All about cursors
PL/SQL中有几种cursor，写代码的时候基本上都会用到，最常用使用起来也是最简单的当属“Implicit Cursor", 对应的就是"Explicit Cursor"， 还有一种是Ref Cursor (i.e. Cursor Variable). 使用哪种游标自然视情况而定，不过一般来说首选的是"implicit cursor", 除了刚开始写PL/SQL时候用过"Explict Cursor"，后来几乎就没有用过，因为用起来相对来说麻烦一点，而且很多时候相对于"implict cursor"也没有什么明显的好处。Ref Cursor一般用来在PL/SQL中返回一个游标（结果集）给客户程序使用（比如Java/.NET).
一般用哪种Cursor，关注点还在于SQL的的Parse次数上面，当然parse越少越好，我们知道parse分为hard parse和soft parse两种，对于同一条SQL而言，第一次执行自然免不了hard parse, 之后parse结果会cache在libary cache中，以后再次执行的时候就只需要soft parse就OK了。虽然soft parse相比hard parse来说代价已经不是太大，但是还是有代价的，那么可不可以减少soft parse的次数呢? PL/SQL提供一种机制，可以缓存cursor, 这样即使你在PL/SQL中显示去close了一个cursor， 这个cursor其实只是"soft" close了，当下次再执行的时候连"soft parse" 都不需要了。这自然是一件相当有吸引力的事情，但是并不是每种cursor都可以被cache从而避免soft parse的，那么很显然在使用pl/sql中的几种cursor的时候就需要注意了，不能随意为之。
下面来测试下几种cursor有什么异同...
SQL> alter session set sql_trace=true;
Session altered.
SQL> edit
Wrote file afiedt.buf
1 begin
2 for i in 1..2000 loop
3 for x in (select /*+implicit cursor query*/ * from dual)
4 loop
5 null;
6 end loop;
7 end loop;
8* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> edit
Wrote file afiedt.buf
1 declare
2 type rc_type is ref cursor;
3 rc rc_type;
4 begin
5 for i in 1..2000 loop
6 open rc for select /*+ ref cursor query*/ * from dual;
7 close rc;
8 end loop;
9* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> edit
Wrote file afiedt.buf
1 declare
2 cursor c is select /*+ explicit cursor query*/ * from dual;
3 begin
4 for i in 1..2000 loop
5 open c;
6 close c;
7 end loop;
8* end;
SQL> /
PL/SQL procedure successfully completed.
然后通过TKPROF格式化一下trace文件，来看下三种游标的异同.....
(1) implict cursor...
SELECT /*+implicit cursor query*/ *
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2000 0.03 0.02 0 0 0 0
Fetch 2000 0.04 0.03 0 6000 0 2000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4001 0.07 0.06 0 6000 0 2000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 335 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2000 TABLE ACCESS FULL DUAL (cr=6000 pr=0 pw=0 time=31247 us)
********************************************************************************
可以看出SQL语句虽然被执行了2000次，但是只进行了一次parse (hard parse).
(2) explict cursor
SELECT /*+ explicit cursor query*/ *
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2000 0.10 0.05 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2001 0.10 0.05 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 335 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL DUAL (cr=0 pr=0 pw=0 time=12649 us)
********************************************************************************
可以看到explict cursor同样也只是parse (hard parse）一次，所以这样用explict cursor没有带来啥好处，相反用起来还麻烦一点。
不过有点要注意，虽然implict cursor中，在loop中我什么都没有做，但是这个SQL 还是被“Fetch”了2000次。相反explict cursor中，因为没有显示去Fetch，所以没有这方面的开销。不过这里只是关注parse，而且在实际情况中一般open了cursor, 不去fetch的话岂不很无聊。
(3) Ref Cursor
SELECT /*+ ref cursor query*/ *
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2000 0.06 0.03 0 0 0 0
Execute 2000 0.01 0.03 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4000 0.07 0.07 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 335 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL DUAL (cr=0 pr=0 pw=0 time=10 us)
用Ref Cursor差别就明显了，这次parse是2000次（一次hard parse, 1999次soft parse)!! 因为ref cursor相当于指针一样，可以指向任意其他的cursor，因此不会被cache，也就享受不到PL/SQL提供的cursor cache的好处了。因此要尽量避免用Ref Cursor.
------
有时候在写代码的时候，可能需要写很长的SQL，这个时候就想把这个SQL单独拿出来复制给一个变量，然后通过EXECUTE IMMEDIATE的方式来执行，如下所示....
declare
l_sql VARCHAR2(4000) :=
q'[ select xxxxxxx from xxxxxxxxxxxxx]';
begin
execute immediate l_sql into xxx;
end;
/
显然这种方式并不好，毕竟通过dynamic sql方式执行失去了compiler检测错误的机会。这里主要是看下这种方式执行从parse上有没有什么坏处....
SQL> declare
2 l_val pls_integer;
3 begin
4 for i in 1..2000
5 loop
6 execute immediate 'select/*+ using dynamic sql*/ 1 from dual' into l_val;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
TKPROF结果....
select/*+ using dynamic sql*/ 1 from dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2000 0.01 0.02 0 0 0 0
Fetch 2000 0.04 0.01 0 0 0 2000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4001 0.05 0.03 0 0 0 2000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 335 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2000 FAST DUAL (cr=0 pr=0 pw=0 time=12508 us)
********************************************************************************
从结果可以看出，动态SQL方式执行的话也只是引入了一次parse (hard parse)， 同implict cursor一样。但是，如前所述，最好还是不要用动态SQL，除非必须得是用。
-----------------
Sum up:
优先使用implict cursor, 最好不要用ref cursor.
--------------------------------------
Regards,
FangwenYu
