游標是構建在PL/SQL中用來查詢數據獲取記錄集的指針它讓開發者 一次訪問結果集中一行記錄 在oracle中提供了兩種游標 靜態游標 ref游標
靜態游標靜態游標是在編譯的時候就被確定然後把結果集復制到內存中 靜態游標又分為兩種隱式游標和顯示游標
ref游標ref游標是在運行的時候加載結果集
先來看看靜態游標中的隱式游標 在PL/SQL中為所有的SQL數據操縱語句（包括返回一行的select）隱式聲明游標 稱為隱式游標主要原因是用戶不能直接命名和控制此類游標當用戶在PL/SQL 中使用數據操縱語句（DML）時oracle預先定義一個名稱為SQL的隱式游標通過 檢查隱式游標的屬性獲取與最近執行的SQL語句相關信息 在執行DML語句之後隱式游標屬性返回信息隱式游標屬性包括 %found %notfound %rowcount %isopen
%found 只有DML語句影響一行或多行時%found屬性才返回true declare num number; begin update emp set empno= where empno=; if sql%found then dbms_outputput_line(存在記錄); else dbms_outputput_line(不存在記錄); end if; end;
%notfound %notfound屬性作用正好跟%found屬性相反如果DML語句沒有影響任何行數 則%notfound屬性返回true declare begin delete from emp where empno=; if sql%notfound then dbms_outputput_line(刪除失敗); end if; end;
%rowcount %rowcount屬性返回DML語句影響的行數如果DML語句沒有影響任何行數 則%rowcount屬性將返回 declare num number; begin update emp set empno= where empno=; if sql%rowcount= then dbms_outputput_line(不存在記錄); else dbms_outputput_line(存在記錄); end if; end;
%isopen %isopen屬性判斷SQL游標是否已經打開在執行SQL語句之後oracle自動關閉SQL 游標所以隱式游標的%isopen屬性始終為false
在PL/SQL中向標准的select語句增加單獨的into子句就可以將從表或視圖中查詢 記錄賦予變量或行變量需要注意的是select into 語句結果必須有且只能有一行 如果查詢沒有返回行PL/SQL將拋出no_data_found異常如果查詢返回多行則拋出 too_many_rows 異常如果拋出異常則停止執行控制權轉移到異常處理部分（沒有 異常處理則程序中斷）在引發異常時將不使用屬性%found%notfound%rowcount來查明DML語句是否 已影響了行數 declare num number; begin select empno into num from emp where empno=; if sql%rowcount= or sql%notfound then dbms_outputput_line(不存在記錄); else dbms_outputput_line(存在記錄); end if; end;
顯示游標 顯示游標是由用戶顯示聲明的游標根據在游標中定義的查詢查詢返回的行集合可以 包含零行或多行這些行稱為活動集游標將指向活動集中的當前行 顯示游標的操作過程使用顯示游標的個步驟 （）聲明游標 （）打開游標 （）從游標中獲取結果集 （）關閉游標 cursor cursor_name [(parameter[parameter])] [return return_type] is select_statement; cursor_name 指游標的名稱 parameter 為游標指定輸入參數 return_type 定義游標提取行的行類型 select_statement 為游標定義查詢語句 open 游標名稱 fetch 從游標中提取行 close 關閉游標
打開游標執行游標中定義的查詢語句綁定輸入參數將游標指針指 向結果集的BOF位置 open cursor_name [parameters]
fetch 在打開游標之後可以從游標中提取記錄 fetch cursor_name into variable_name; fetch 是提取結果集中一行記錄存儲在變量中每次提取之後結果集指針 就向前移動一行
close 在處理游標中的所有行之後必須關閉游標以釋放分配給游標的所有資源 close cursor_name 用戶可以通過檢查游標屬性來確定游標的當前狀態顯示游標的屬性如下 %found如果執行最後一條fetch語句成功返回行則%found屬性為true %notfound如果執行最後一條fetch語句未能提取行則%notfound屬性為true %isopen:如果游標已經打開則返回true否則返回false %rowcount返回到目前為止游標提取的行數%rowcount為數字類型屬性在第一 次獲取之前%rowcount為零當fetch語句返回一行時則該數加 declare info emp%rowtype; cursor my_cur is select * from emp where empno=; begin open my_cur; dbms_outputput_line(my_cur%rowcount); loop if my_cur%isopen then fetch my_cur into info; exit when my_cur%notfound; dbms_outputput_line(infoempno); dbms_outputput_line(my_cur%rowcount); end if; end loop; close my_cur; end;
使用顯示游標刪除或更新 使用游標時如果處理過程中需要刪除或更新在定義游標查詢語句時 必須使用selectfor update語句而在執行delete或update時使用 where current of 子句指定游標當前行 cursor cursor_name is select_statement for update[of column] wait/nowait 在使用for update 子句聲明游標之後可以使用以下語法更新行 update table_name set column_name=column_value where current of cursor_name; update命令中使用的列必須出現在for update of 子句中 select 語句必須只包括一個表而且delete和update語句只有在打開游標並且提取 特定行之後才能使用 declare cursor cur_emp is select * from emp where sal< for update of sal; num emp%rowtype; begin open cur_emp; loop fetch cur_emp into num; exit when cur_emp%notfound; update emp set sal= where current of cur_emp; end loop; close cur_emp; end;
帶參數的顯示游標 PL/SQL中允許顯示游標接受輸入參數用於聲明帶參數的顯示游標語法 cursor cursor_name[<param_name> data_type] [return <return type>] is select_statement declare dept_num empdeptno%type; emp_num empempno%type; emp_nam empename%type; cursor emp_cur(deptparam number) is select empnoename from emp where deptno=deptparam; begin dept_num :=&部門編號; open emp_cur(dept_num); loop fetch emp_cur into emp_numemp_nam; exit when emp_cur%notfound; dbms_outputput_line(emp_num|| ||emp_nam); end loop; close emp_cur; end;
可以使用循環游標來簡化顯示游標循環游標隱式打開顯示游標（不需要open） 自動從結果集提取記錄然後處理完所有記錄自動關閉游標循環游標自動創建 %rowtype類型的變量並將此變量用做記錄的索引 循環游標語法如下 for record_index in cursor_name record_index是PL/SQL自動創建的變量此變量的屬性聲明為%rowtype類型作用 域for循環之內 循環游標的特性有 從游標中提取所有記錄之後自動關閉游標 提取和處理游標中每一條記錄 提取記錄之後%notfound屬性為true則退出循環如果未有結果集則不進入循環 declare cursor emp_cur is select * from emp; begin for temp in emp_cur loop dbms_outputput_line(tempename); end loop; end; 循環游標自動打開提取關閉只適用於靜態游標
ref游標 隱式游標和顯示游標都是靜態定義的它們在編譯的時候結果集就已經被確定 如果想在運行的時候動態確定結果集就要使用ref游標和游標變量
創建ref游標需要兩個步驟 聲明ref cursor類型 聲明 ref cursor類型變量 語法如下 type ref_cursor_name is ref cursor [return record_type] 其中return 用於指定游標提取結果集的返回類型有return表示是強類型ref游標 沒有return表示是弱類型的游標弱類型游標可以提取任何類型的結果集 定義游標變量之後就可以在PL/SQL執行部門打開游標變量 open cursor_name for select_statement; declare type emp_cur is ref cursor; my_cur emp_cur; num number; selection varchar():=&請輸入編號; begin if selection= then dbms_outputput_line(員工信息); open my_cur for select deptno from emp; elsif selection= then dbms_outputput_line(部門信息); open my_cur for select deptno from dept; else dbms_outputput_line(請輸入員工信息（）或門部信息（）); end if; fetch my_cur into num; while my_cur%found loop dbms_outputput_line(num); fetch my_cur into num; end loop; close my_cur; end;
在PL/SQL中可以執行動態SQL語句execute immediate 語句只能語句處理返回單行 或沒有返回的SQL語句ref游標則可以處理返回結果集的動態SQLref游標的聲明 方法與普通ref游標相同只是在open時指定了動態SQL字符串 open cursor_name for dynamic_select_string [using bind_argument_list] declare type sql_cur is ref cursor; my_cur sql_cur; emp_info emp%rowtype; sql_string varchar():=&請輸入查詢字符串; begin open my_cur for sql_string; loop fetch my_cur into emp_info; exit when my_cur%notfound; dbms_outputput_line(emp_infoename); end loop; close my_cur; end;
游標變量的特點 （）游標變量可以從不同的結果集中提取記錄 （）游標變量可以做為存儲過程參數進行傳遞 （）游標變量可以引用游標的所有屬性 （）游標變量可以進行賦值運算 使用游標變量也有一定的限制 （）for update 子句不能與游標變量一起使用 （）不允許在程序包使用游標變量（可以聲明游標類型） （）另一台服務器上的子過程不能接受游標變量參數 （）不能將NULL值賦給游標變量 （）游標變量不能使用比較運算符 （）數據庫中的列不能存儲游標變量 總結 （）游標是使用在PL/SQL中是用來查詢數據獲取結果集的指針 （）游標類型包括隱式游標顯示游標和ref游標 （）游標屬性包括%found%notfound%rowcount%isopen （）PL/SQL自動定義隱式游標以獲取最近執行SQL語句信息 （）循環游標簡化處理游標中所有行的查詢 （）在聲明ref游標時不要將它與select 語句相關聯
From:http://tw.wingwit.com/Article/program/Oracle/201311/18462.html
