PL/SQL 遊標的使用，plsql遊標使用

PL/SQL 遊標的使用，plsql遊標使用


遊標的使用

①遊標概念
為了處理SQL 陳述式，ORACLE 必須分配一片叫上下文( context area )的地區來處理所必需的資訊，
其中包括要處理的行的數目，一個指向語句被分析以後的表示形式的指標以及查詢的活動集(active set)。
遊標是一個指向內容相關的控制代碼( handle)或指標。通過遊標，PL/SQL可以控制上下文區和處理語句時上下文區會發生些什麼事情

②顯式遊標處理

1.顯式遊標處理需四個PL/SQL步驟:

定義遊標：就是定義一個遊標名，以及與其相對應的SELECT 語句。
格式：
CURSOR cursor_name[(parameter[, parameter]…)] IS select_statement;
遊標參數只能為輸入參數，其格式為：
parameter_name [IN] datatype [{:= | DEFAULT} expression]
在指定資料類型時，不能使用長度約束。如NUMBER(4)、CHAR(10) 等都是錯誤的。

開啟遊標：就是執行遊標所對應的SELECT 語句，將其查詢結果放入工作區，並且指標指向工作區的首部，標識遊標結果集合。
如果遊標查詢語句中帶有FOR UPDATE選項，OPEN 語句還將鎖定資料庫表中遊標結果集合對應的資料行。
格式：
OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];
在向遊標傳遞參數時，可以使用與函數參數相同的傳值方法，即位置標記法和名稱標記法。PL/SQL 程式不能用OPEN 語句重複開啟一個遊標。

提取遊標資料：就是檢索結果集合中的資料行，放入指定的輸出變數中。
格式：
FETCH cursor_name INTO {variable_list | record_variable };
對該記錄進行處理；
繼續處理，直到活動集合中沒有記錄；

關閉遊標：當提取和處理完遊標結果集合資料後，應及時關閉遊標，以釋放該遊標所佔用的系統資源，並使該遊標的工作區變成無效，
不能再使用FETCH 語句取其中資料。關閉後的遊標可以使用OPEN 語句重新開啟。
格式：
CLOSE cursor_name;
註：定義的遊標不能有INTO 子句。

2.遊標屬性
%FOUND       布爾型屬性，當最近一次讀記錄時成功返回,則值為TRUE；
%NOTFOUND   布爾型屬性，與%FOUND相反；
%ISOPEN       布爾型屬性，當遊標已開啟時返回TRUE；
%ROWCOUNT   數字型屬性，返回已從遊標中讀取的記錄數。

3.遊標的FOR迴圈
PL/SQL語言提供了遊標FOR迴圈語句，自動執行遊標的OPEN、FETCH、CLOSE語句和迴圈語句的功能；當進入迴圈時，遊標FOR迴圈語句自動開啟遊標，並提取第一行遊標資料，當程式處理完當前所提取的資料而進入下一次迴圈時，遊標FOR迴圈語句自動提取下一行資料供程式處理，當提取完結果集合中的所有資料行後結束迴圈，並自動關閉遊標。
格式：
FOR index_variable  IN cursor_name[value[, value]…]  LOOP
    --遊標資料處理代碼
END LOOP;
其中：
index_variable為遊標FOR 迴圈語句隱含聲明的索引變數，該變數為記錄變數，其結構與遊標查詢語句返回的結構集合的結構相同。在程式中可以通過引用該索引記錄變數元素來讀取所提取的遊標資料，
index_variable中各元素的名稱與遊標查詢語句挑選清單中所制定的列名相同。
如果在遊標查詢語句的挑選清單中存在計算資料行，則必須為這些計算資料行指定別名後才能通過遊標FOR 迴圈語句中的索引變數來訪問這些列資料。
註：不要在程式中對遊標進行人工操作；不要在程式中定義用於控制FOR 迴圈的記錄。

③處理隱式遊標
顯式遊標主要是用於對查詢語句的處理，尤其是在查詢結果為多條記錄的情況下；
而對於非查詢語句，如修改、刪除操作，則由ORACLE 系統自動地為這些操作設定遊標並建立其工作區，
這些由系統隱含建立的遊標稱為隱式遊標，隱式遊標的名字為SQL，這是由ORACLE 系統定義的。對於隱式遊標的操作，
如定義、開啟、取值及關閉操作，都由ORACLE 系統自動地完成，無需使用者進行處理。使用者只能通過隱式遊標的相關屬性，來完成相應的操作。
在隱式遊標的工作區中，所存放的資料是與使用者自訂的顯示遊標無關的、最新處理的一條SQL 陳述式所包含的資料。

格式調用為：SQL%
隱式遊標屬性
SQL%FOUND      布爾型屬性,當最近一次讀記錄時成功返回，則值為TRUE；
SQL%NOTFOUND   布爾型屬性,與%FOUND相反；
SQL %ROWCOUNT  數字型屬性, 返回已從遊標中讀取得記錄數；
SQL %ISOPEN    布爾型屬性, 取值總是FALSE。SQL命令執行完畢立即關閉隱式遊標。

④關於NO_DATA_FOUND 和%NOTFOUND的區別
SELECT … INTO 語句觸發NO_DATA_FOUND；（EXCEPTION  when  NO_DATA_FOUND then ......）
當一個顯式遊標的WHERE子句未找到時觸發%NOTFOUND；
當UPDATE或DELETE 語句的WHERE 子句未找到時觸發SQL%NOTFOUND；
在提取迴圈中要用%NOTFOUND 或%FOUND 來確定迴圈的允出準則，不要用NO_DATA_FOUND.

⑤遊標修改和刪除操作
遊標修改和刪除操作是指在遊標定位下，修改或刪除表中指定的資料行。
這時，要求遊標查詢語句中必須使用FOR UPDATE選項，以便在開啟遊標時鎖定遊標結果集合在表中對應資料行的所有列和部分列。
為了對正在處理(查詢)的行不被另外的使用者改動，ORACLE 提供一個FOR UPDATE 子句來對所選擇的行進行鎖住。
該需求迫使ORACLE鎖定遊標結果集合的行，可以防止其他交易處理更新或刪除相同的行，直到您的交易處理提交或回退為止。
文法：
SELECT . . . FROM … FOR UPDATE [OF column[, column]…] [NOWAIT]
如果另一個會話已對活動集中的行加了鎖，那麼SELECT FOR UPDATE操作一直等待到其它的會話釋放這些鎖後才繼續自己的操作，
對於這種情況，當加上NOWAIT子句時，如果這些行真的被另一個會話鎖定，則OPEN立即返回並給出：
ORA-0054 ：resource busy  and  acquire with nowait specified.
如果使用FOR UPDATE 聲明遊標，則可在DELETE和UPDATE 語句中使用WHERE CURRENT OF cursor_name子句，
修改或刪除遊標結果集合當前行對應的資料庫表中的資料行

⑥例：使用遊標

1.要求: 列印出 80 部門的所有的員工的工資:salary: xxx
 
declare
  --1. 定義遊標
  cursor salary_cursor is select salary from employees where department_id = 80;
  v_salary employees.salary%type;
begin
 --2. 開啟遊標
 open salary_cursor;

 --3. 提取遊標
 fetch salary_cursor into v_salary;
 
 --4. 對遊標進行迴圈操作: 判斷遊標中是否有下一條記錄
while salary_cursor%found loop
      dbms_output.put_line('salary: ' || v_salary);
      fetch salary_cursor into v_salary;
end loop; 
 
 --5. 關閉遊標
 close  salary_cursor;
end;

2.要求: 列印出 80 部門的所有的員工的工資: Xxx 's salary is: xxx
 
declare
  cursor sal_cursor is select salary ,last_name from employees where department_id = 80;
  v_sal number(10);
  v_name varchar2(20);
begin
  open sal_cursor;
 
  fetch sal_cursor into v_sal,v_name;
 
  while sal_cursor%found loop
        dbms_output.put_line(v_name||'`s salary is '||v_sal);
        fetch sal_cursor into v_sal,v_name;
  end loop;
 
  close sal_cursor;
 
end;

3.列印出 manager_id 為 100 的員工的 last_name, email, salary 資訊(使用遊標, 記錄類型)

declare 
   --聲明遊標   
   cursor emp_cursor is select last_name, email, salary from employees where manager_id = 100;
  
   --聲明記錄類型
   type emp_record is record(
 name employees.last_name%type,
 email employees.email%type,
 salary employees.salary%type
   );
  
   -- 聲明記錄類型的變數
   v_emp_record emp_record;
begin
   --開啟遊標
   open emp_cursor;
  
   --提取遊標
   fetch emp_cursor into v_emp_record;
  
   --對遊標進行迴圈操作
   while emp_cursor%found loop
   dbms_output.put_line(v_emp_record.name || ', ' || v_emp_record.email || ', ' || v_emp_record.salary );               
   fetch emp_cursor into v_emp_record;
   end loop;
  
   --關閉遊標
   close emp_cursor;
end;
(法二：使用for迴圈)
declare 
      cursor emp_cursor is select last_name,email,salary from employees where manager_id = 100;
begin
      for v_emp_record in emp_cursor loop
          dbms_output.put_line(v_emp_record.last_name||','||v_emp_record.email||','||v_emp_record.salary);
      end loop;
end;

4. 利用遊標, 調整公司中員工的工資:
   
    工資範圍       調整基數
    0 - 5000       5%
    5000 - 10000   3%
    10000 - 15000  2%
    15000 -        1%

declare
    --定義遊標
    cursor emp_sal_cursor is select salary, employee_id from employees;
   
    --定義基數變數
    temp number(4, 2);
   
    --定義存放遊標值的變數
    v_sal employees.salary%type;
    v_id employees.employee_id%type;
begin
    --開啟遊標
    open emp_sal_cursor;
   
    --提取遊標
    fetch emp_sal_cursor into v_sal, v_id;
   
    --處理遊標的迴圈操作
    while emp_sal_cursor%found loop
          --判斷員工的工資, 執行 update 操作
          --dbms_output.put_line(v_id || ': ' || v_sal);
           
          if v_sal <= 5000 then
             temp := 0.05;
          elsif v_sal<= 10000 then
             temp := 0.03;  
          elsif v_sal <= 15000 then
             temp := 0.02;
          else
             temp := 0.01;
          end if;
         
          --dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);
          update employees set salary = salary * (1 + temp) where employee_id = v_id;
                 
          fetch emp_sal_cursor into v_sal, v_id;
    end loop;
    --關閉遊標
    close emp_sal_cursor;
end;

使用SQL中的 decode 函數

update employees set salary = salary * (1 + (decode(trunc(salary/5000), 0, 0.05,
                                                                        1, 0.03,
                                                                        2, 0.02,
                                                                        0.01)))

5. 利用遊標 for 迴圈完成 4.

declare
    --定義遊標
    cursor emp_sal_cursor is select salary, employee_id id from employees;
   
    --定義基數變數
    temp number(4, 2);
begin
    --處理遊標的迴圈操作
    for c in emp_sal_cursor loop
          --判斷員工的工資, 執行 update 操作
          --dbms_output.put_line(v_id || ': ' || v_sal);
           
          if c.salary <= 5000 then
             temp := 0.05;
          elsif c.salary <= 10000 then
             temp := 0.03;  
          elsif c.salary <= 15000 then
             temp := 0.02;
          else
             temp := 0.01;
          end if;
         
          --dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);
          update employees set salary = salary * (1 + temp) where employee_id = c.id;
    end loop;
end;

6*. 帶參數的遊標

declare
    --定義遊標
    cursor emp_sal_cursor(dept_id number, sal number) is
           select salary + 1000 sal, employee_id id
           from employees
           where department_id = dept_id and salary > sal;
   
    --定義基數變數
    temp number(4, 2);
begin
    --處理遊標的迴圈操作
    for c in emp_sal_cursor(sal => 4000, dept_id => 80) loop
          --判斷員工的工資, 執行 update 操作
          --dbms_output.put_line(c.id || ': ' || c.sal);
         
          if c.sal <= 5000 then
             temp := 0.05;
          elsif c.sal <= 10000 then
             temp := 0.03;  
          elsif c.sal <= 15000 then
             temp := 0.02;
          else
             temp := 0.01;
          end if;
         
          dbms_output.put_line(c.sal || ': ' || c.id || ', ' || temp);
          --update employees set salary = salary * (1 + temp) where employee_id = c.id;
    end loop;
end;

7. 隱式遊標: 更新指定員工 salary(漲工資 10)，如果該員工沒有找到，則列印”查無此人” 資訊

begin
         update employees set salary = salary + 10 where employee_id = 1005;
        
         if sql%notfound then
            dbms_output.put_line('查無此人!');
         end if;
end;