### Oracle Cursor

> 節選自《基於Oracle的SQL最佳化》第三章

Library cache：主要作用是快取剛剛執行過的sql語句和PL/SQL語句(如儲存過程、函式、包、觸發器)鎖對應的執行計劃、解析樹、Pcode/Mcode等物件，當同樣的SQL語句和PL/SQL語句再次被執行時，就可以例如已快取再Library Cache中的那些相關物件而無需再次從頭開始解析，這樣就提高了這些SQL、PL/SQL語句重複執行的效率.

v$sqlarea檢視父遊標、v$sql檢視子游標

任意一個目標SQL一定會同時對應兩個Shared Cursor,其中一個Parent Cursor,另一個是Child Cursor,父遊標儲存sql文字，sql中真正可以被重用的解析樹和執行計劃則儲存在子游標中。

```
--測試,同一物件SQL大小寫不同，父遊標不同；不同使用者同一物件語句一樣，父遊標相同，子游標不同(使用者不同，解析不一樣)

SQL_TEXT				 SQL_ID        VERSION_COUNT
---------------------------------------- ------------- -------------
select empno,ename from emp		 78bd3uh4a08av		   1

SQL> 1
  1* select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'select emp%'
SQL> select plan_hash_value,child_number from v$sql where sql_id='78bd3uh4a08av';

PLAN_HASH_VALUE CHILD_NUMBER
--------------- ------------
     3956160932 	   0

SQL> /

PLAN_HASH_VALUE CHILD_NUMBER
--------------- ------------
     3956160932 	   0

SQL> select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'select emp%';

SQL_TEXT				 SQL_ID        VERSION_COUNT
---------------------------------------- ------------- -------------
select empno,ename from emp		 78bd3uh4a08av		   1
select empno,ename from  EMP		 7k63aycdqsfr1		   1

SQL> select plan_hash_value,child_number from v$sql where sql_id='7k63aycdqsfr1';

PLAN_HASH_VALUE CHILD_NUMBER
--------------- ------------
     3956160932 	   0

SQL> select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'select emp%';

SQL_TEXT				 SQL_ID        VERSION_COUNT
---------------------------------------- ------------- -------------
select empno,ename from emp		 78bd3uh4a08av		   2
select empno,ename from  EMP		 7k63aycdqsfr1		   1

SQL> select plan_hash_value,child_number from v$sql where sql_id='78bd3uh4a08av';

PLAN_HASH_VALUE CHILD_NUMBER
--------------- ------------
     3956160932 	   0
     3956160932 	   1

```
> 硬解析是指Oracle在執行SQL時，在庫快取中找不到可以重用的解析樹和執行計劃，而不得不重新開始解析並生成父遊標和子游標.

硬解析可能會導致Shared Pool
Latch爭用，導致庫快取相關latch.
理想情況下，oltp硬解析每秒控制在20以下

> 軟解析是Library cache找到了匹配的父遊標和子游標，並將子游標中的解析樹和執行計劃之家拿過來重用。

#### Oracle裡的session Cursor

主要用於當前Session中解析和執行sql。快取在PGA中。注意事項：
1. Session Cursor與Session是一一對應，不同的session之間沒法共享，這是與Shared Cursor本質的區別。
2. SC是有生命週期的，每個SC在使用過程中都至少會經歷一次Open Parse Bing Execute Fetch 和Close中的一個或多個階段。用過的SC不一定會快取在對於的Session 的PGA中，這取決於引數SESSION_CACHED_CURSORS的值是否大於0
3. SC也是以雜湊表方式快取再PGA中，訪問機制與Shared Cursor一樣。

Oracle11g 之前，在快取Session Cursor的雜湊表的對應Hash Bucket中，Oracle會儲存目標SQL對應的Parent Cursor的庫快取物件控制程式碼地址，這意味著Oracle已經建立了目標SQL的Session Cursor與其Parent Cursor之間的聯絡，即Oracle可以透過Session Cursor找到對應的Parent Cursor，金額日可以找到對應的子游標中目標sql的解析樹和執行計劃。

```
--dump session Cursor
alter session set events 'immediate trace name ERRORSTACK level 3';
--Bucket 的Hash Bucket中hdl控制程式碼的值就是 目標sql父遊標的庫快取物件控制程式碼地址.
select sql_text,sql_id,version_count,executions,address from v$sqlarea where sql_text like 'select count(*) %';
```

> 一個Session Cursor只能對應一個Shared Cursor，而一個Shared Cursor卻可以同時對應多個Session Cursor。

如果引數SESSION_CACHED_CURSORS等於0，那麼session Cursor正常關閉，相同語句再次執行時，可以找到父遊標，但無法找到Session Cursor，Oracle必須重新生成一個SC，sc會重新經歷“硬解析”。

如果引數大於0，SC不會執行關閉操作，會將其標記為soft closed，同時將其快取在當前session 的PGA中。這樣當再次執行時，不用重新解析。這個過程叫“軟軟解析”。

> 不管是硬解析還是軟解析，Oracle在解析和執行目標sql時，都會先去當前session的pga中查詢匹配的SC,如果沒有，就去庫快取找匹配的父遊標，如果找不到，生成全新的，如果找到，生成SC並去匹配父遊標.

##### Session Cursor相關引數說明

- OPEN_CURSORS :單個session可以同時open狀態並存SC的總數

```
show parameter open_cursors
--檢視sid
select sid from v$mystat where rownum<=2;
--檢視已經被快取的SC總數
SELECT COUNT(*) FROM v$open_cursor where sid=;
--當前所有以open狀態存在的SC總數
select name,value from v$sysstat where name='opened cursors current';
```

- SESSION_CACHED_CURSORS :用於設定單個session中能夠以Soft Closed狀態並存的SC總數。即設定單個session能夠快取在PGA的SC總數

```
show parameter session_cached_cursors
```

> 注意：被快取有一定條件，例如oracle11gR2,該SC所對應的SQL解析和執行的次數要超過3次。

```
--可以透過以下語句檢視
select sql_text,cursor_type from v$open_cursor where user_name='' and sid= and sql_text like '';
--當執行3次時，遊標型別是"DICTIONARY LOOKUP CURSOR CACHED",當執行4次後顯示"SESSION CURSOR CACHED"
```

> CURSOR_SPACE_FOR_TIME：11g之後已過時

當SC狀態為執行時，Oracle會把該SQL所對應的Child Cursor給pin住，保證這個正在執行的sql的解析樹和執行計劃保留在庫快取中而不被age out出快取。 當上述引數為FALSE時，sql執行完畢，此時子游標可以不pin在庫快取中，釋放了，所對應的解析樹和執行計劃被age out出快取。 如果有大量併發，可能會造成與庫快取相關的Latch爭用。 可以將上述引數設定為TRUE，但會造成Shred Pool空間緊張。

##### Session Cursor的種類和寫法

###### 隱式遊標

常用的四個屬性
1. SQL%FOUND屬性

表示一條SQL語句被執行成功後受其影響而改變的記錄數是否大於或等於1.適用於DML操作。  在DML執行前，SQL%FOUND值是NULL,當這條DML語句被執行並且成功改變了一條或者一條以上記錄時，又或者SELECT INFO 語句成功返回一條或多條記錄時，SQL%FOUND值時TRUE,否則是FALSE。

```
declare
  dept_no number(4) := 50;
begin
  delete from dept_temp where deptno = dept_no;
  if sql%found then
   insert into dept_temp values (50,'Database','BEIJING');
  end if;
  commit;
 end;
/
```

> 在PL/SQL中使用SELECT INFO，當且僅當對於的select返回結果只有一條時Oracle才不會報錯，返回0，報錯"NO_DATA_FOUND",返回多行報錯"TOO_MANY_ROWS"

```
--eg
declare
  dept_name varchar2(14);
  vc_message varchar2(4000);
begin
  select dname info dept_name from dept_temp where deptno=50;
exception
  when no_data_found then
    dbms_output.put_line('No data found!');
    return;
  when too_many_rows then
    dbms_output.out_line('Too many rows!');
    return;
  when others then
    vc_message := 'E' ||'_'||sqlcode ||'_' || sqlerrm;
    dbms_output.put_line(vc_message);
    return;
end;
/
-- SQLCODE返回Oracle錯誤的序列號，SQLERRM返回相應的錯誤資訊
```

2. SQL%NOTFOUND

表示一條SQL語句被執行成功後受其影響而改變的記錄數是否為0.

在一條DML語句被執行前，該值時NULL，沒有任何記錄時是TRUE,否則為FALSE

3. SQL%ISOPEN

表示隱士遊標是否處於open狀態。 對於隱式遊標而言，永遠是FALSE.

4. SQL%ROWCOUNT

表示一條SQL語句成功執行後受其影響而改變的記錄的數量。

當DML語句聯用時，表示該DML語句被執行後受其影響而改變的記錄數。當一條語句執行後沒有改變任何記錄或者SELECT INFO沒有任何返回記錄時，值為0.

> 注意：當SELECT INFO返回超過1行時，報"TOO_MANY_ROWS"，在這種情況下，SQL%ROWCOUNT返回值是1，而不是實際查詢到的記錄數。

```
--表示被成功刪除的記錄數，再次執行會覆蓋，不是疊加
declare 
  dept_no number(4) := 50;
begin
  delete from dept_temp where deptno = deptno=dept_no;
  dbms_output.put_line('Number of departments deleted: '|| to_char(sql%rowcount));
  commit;
 end;
/
```

###### 顯示遊標

常用於PL/SQL中。四種屬性如下，CURSORNAME 遊標名

1. CURSORNAME%FOUND
 
當遊標被open後，如果沒有被fetch，值是NULL,被Fetch後，值是TRUE,直到全部Fetch。然後再執行fetch，該值變成FALSE.

```
--如果沒被open，試圖使用該屬性，報錯"INVALID_CURSOR"
declare
  cursor c1 select ename,sal from emp where rownum<=10;
  my_ename emp.ename%type;
  my_salary emp.sal%type; --繼承emp.sal型別
begin
  open c1; --不開啟，使用C1%FOUND報錯
  loop
    fetch c1 into my_ename,my_salary;
    if c1%found then
      dbms_output.put_line('name= ' ||my_ename || ', salary = ' || my_salary);
    else
      exit;
    end if;
  end loop;
 close c1;
end;
/
```

2. CURSORNAME%NOTFOUND

表示指定的顯示遊標是否已經Fetch完畢了。 當一個顯示遊標被open後，如果還一次都沒被fetch，該值為null，被fetch後，該值是false，全部fetch後，為true。 當一個顯示遊標未被open，使用該屬性，會報錯"INVALID_CURSOR"

```
--eg
declare
  cursor c1 is select ename,sal from emp where rownum<=11;
  my_ename emp.ename%type;
  my_salary emp.sal%type;
begin
  open c1;
  loop
  fetch c1 into my_ename,my_salary;
  if c1%notfound then
    exit;
  else
    dbms_output.put_line('name= ' || my_ename || ',salary=' ||my_salary);
   end if;
  end loop;
    close c1;
 end;
/
```

3. CURSORNAME%ISOPEN

表示指定的顯示遊標是否被open。主要用於close那些由於發生了Exception而導致有顯示遊標沒有被正常關閉的情形。

```
--eg
declare
  cursor c1 is select ename,sal from emp where rownum<=11;
  my_ename emp.ename%type;
  my_salary emp.sal%type;
begin
  open c1;
  fetch c1 into my_ename,my_salary;
  dbms_output.put_line('name= ' || my_ename || ',salary=' ||my_salary);
  close c1;
 exception
   when others then
    if c1%open = true then
      close c1;
    end if;
    return;
end;
/
```

4. CURSORNAME%ROWCOUNT

表示指定顯示遊標到目前一共fetch了多少行記錄.當沒fetch，為0，當返回空，也是0，隨著每一次fetch，值會遞增。

```
--eg
declare
  cursor c1 is select ename from emp where rownum<=11;
  my_ename emp.ename%type;
  begin
  open c1;
  fetch c1 into my_ename;
  if c1%found then
    dbms_output.put_line(c1%rowcount ||' :' || 'name = '|| my_ename);
  else
    exit;
  end if;
 end loop;
 close c1;
end;
/

--顯示遊標標準用法是先open，在Fetch，然後用一個while迴圈處理資料，最後close。while內部迴圈，記得執行fetch操作跳到下一條記錄，否則就是死迴圈.
create or replace procedure P_DEMO_EXPLICIT_CURSOR_STD is
  cursor c1 is select * from emp where rownum <= 11;
  emp_rec emp%rowtype; --跟所有表型別一致
begin
  open c1;
  fetch c1 into emp_rec;
  while (c1%found) loop
    dbms_output.put_line('name =' || emp_rec.ename || ', salary = ' || emp_rec.sal);
    
    fetch c1 into emp_rec;
   end loop;
   close c1;
exception
  when others then
    --o_parm:='E'||sqlcode || sqlerrm;
    rollback;
    returen;
end P_DEMO_EXPLICIT_CURSOR_STD;
```

###### 參考遊標

是Oracle資料庫中最靈活的一種Session Cursor，它的靈活表現在如下三個方面。
 
 - 參考遊標的定義方式非常靈活，它可以有多種定義方式。
 - 參考遊標的open方式也非常靈活，它可以不喝某個固定的sql繫結在一起，並且每次open鎖對應的sql語句都可以是不一樣的（在Oracle11gR2,中，即便是同一條SQL語句，它所對應的參考遊標也是可以隨時重複open)
 - 參考遊標可以作為儲存過程的輸入引數和函式的輸出引數

```
--參考遊標的定義方式
type typ_cur_emp is ref cursor return emp%rowtype;
cur_emp typ_cur_emp;

--第二種
type typ_result is record(ename emp.ename%type,sal emp_sal%type);
type typ_cur strong is ref cursor return type_result;
cur_emp typ_cur_stong;

--第三種
type typ_cur_weak is ref cursor;
cur_emp typ_cur_weak;

--第四種
cur_emp SYS_REFCURSOR;
```

> 參考遊標可以作為儲存過程引數

```
declare
  type typ_cur_emp is ref cursor return emp%rowtype;
  cur_emp typ_cur_emp;
  
  procedure process_emp_cv(emp_cv in typ_cur_emp) is 
    person emp%rowtype;
 begin
    dbms_output.put_line('----');
    loop
    fetch emp_cv into person;
    exit when emp_cv%notfound;
    dbms_output.put_line('name = '||person.ename);
 end loop;
end;
begin
  open cur_emp for select * from emp where rownum <11;
  process_emp_cv(cur_emp);
  close cur_emp;
  
  open cur_emp for select * from emp where ename like 'C%';
  process_emp_cv(cur_emp);
  close cur_emp;
 end;
 /
--引數遊標被作為內嵌儲存過程的輸入 引數，遊標兩次open，和顯示遊標不同，這裡多了一個"for"

--根據輸入選擇不同，執行不同sql
create package pck_refcursor_open_demo as
   type gencurtyp is ref cursor;
   procedure open_cv (generic_cv in out gencurtyp,choice int);
end pck_refcursor_open_demo;
/

create package body pck_refcursor_open_demo as
  procedure open_cv (generic_cv in out gencurtyp,choice int) is
begin
   if choice = 1 then
     open generic_cv for select * from emp;
   elsif choice = 2 then
     open generic_cv for select * from dept;
   elsif choice =3 then
     open generic_cv for select * from dept_tmp;
   end if;
 end;
end pck_refcursor_open_demo;
/

```

> 不能直接在一個包或包體的定義部分定義一個參考遊標型別的Cursor變數，它只能在包或包體的儲存過程中定義,或者作為該儲存過程的輸入、輸出引數。

```
--eg:1/2 錯誤，3/4可以
create package pck_name as
   type gencurtyp is ref cursor;
   generic_cv gencurtyp;--1
   procedure open_cv(generic_cv in out gencurtyp --3,choice int);
end pck_name;
/

create package body pck_name as 
   generic_cv gencurtyp;--2
   procedure open_cv (generic_cv in out gencurtyp,choice int) is
   generic_cv gencurtyp; --4
begin
   ...
end;
/

--oracle11g 開始參考遊標可以多次open，不用open一次關閉一次。後一次會覆蓋前一次open 的參考遊標。

--參考遊標除了常規的一次fetch一條資料之外，哈可以次Fetch一批資料，批次獲取，eg
declare
   type empcurtyp is ref cursor;
   type namelist is table of emp.ename%type;
   emp_cv empcurtyp;
   names namelist;
 begin
   open emp_cv for select ename from emp where rownum<=11;
   fetch emp_cv bulk collect into names;  --批次獲取
   close emp_cv;
   
   for i in names.first .. names.last
   loop
      dbms_output.line('name = ' || names(i));
    end loop;
end;
/

```

> 參考遊標和顯示遊標是可巢狀的

```
--eg:按照部門巢狀列印，參考遊標emp_cur巢狀在顯示遊標c1中。
--顯示輸出結果
set serveroutput on
declare
    type emp_cur_typ is ref cursor;
    emp_cur emp_cur_typ;
    dept_name dept.dname%type;
    emp_name emp.ename%type;
    
    cursor c1 is select dname,cursor(select e.ename from emp e where e.deptno=d.deptno) employees
    from dept d;
begin
   open c1;
   loop
     fetch c1 into dept_name,emp_cur;
     exit when c1%notfound;
     dbms_output.put_line('department: '|| dept_name);
     loop
       fetch emp_cur into emp_name;
       exit when emp_cur%notfound;
       dbms_output.put_line('-- employee: '|| emp_name);
     end loop;
    end loop;
  close c1;
end;
/
```

【CURSOR】Oracle 遊標（cursor）知識梳理

相關文章

【CURSOR】Oracle 遊標 （cursor）知識梳理

相關文章

【CURSOR】Oracle 遊標（cursor）知識梳理