Oracle遊標解析

標籤：for   oracle   result   來源   精簡   ora   多行   使用   規則   

本節對Oracle中的遊標進行詳細講解。本節所舉執行個體來源Oracle中scott使用者下的emp表dept表：

一、遊標：1、概念：遊標的本質是一個結果集resultset，主要用來臨時儲存從資料庫中提取出來的資料區塊。二、遊標的分類：1、顯式遊標：由使用者定義，需要的操作：定義遊標、開啟遊標、提取資料、關閉遊標，主要用於對查詢語句的處理。屬性：%FOUND        %NOTFOUND        %ISOPEN          %ROWCOUNTExample:列印emp表的員工資訊

DECLARE  CURSOR emp_cursor IS SELECT empno,ename,job FROM emp;  v_empno emp.empno%TYPE;  v_name emp.ename%TYPE;  v_job emp.job%TYPE;BEGIN  OPEN emp_cursor;  LOOP    FETCH emp_cursor INTO v_empno,v_name,v_job;    DBMS_OUTPUT.PUT_LINE(‘員工號為:‘||v_empno||‘姓名是‘||v_name||‘職位：‘||v_job);    EXIT WHEN emp_cursor%NOTFOUND;  END LOOP;  CLOSE emp_cursor;END; 

這裡嚴格按照顯示遊標的書寫規則：DECLARE emp_cursor定義遊標OPEN emp_cursor開啟遊標FETCH emp_cursor INTO...提取資料CLOSE emp_cursor關閉遊標,因為提取出來的資料屬於多行，所以通過loop迴圈列印即可。Example2:檢驗遊標是否開啟,如果開啟顯示提取行數

DECLARE  CURSOR emp_cursor IS SELECT empno,ename,job FROM emp;  v_empno emp.empno%TYPE;  v_name emp.ename%TYPE;  v_job emp.job%TYPE;BEGIN  OPEN emp_cursor;  LOOP       FETCH emp_cursor INTO v_empno,v_name,v_job;       EXIT WHEN emp_cursor%NOTFOUND;  END LOOP;  IF emp_cursor%ISOPEN THEN    DBMS_OUTPUT.PUT_LINE(‘遊標已開啟‘);    DBMS_OUTPUT.PUT_LINE(‘讀取了‘||emp_cursor%ROWCOUNT||‘行‘);  ELSE    DBMS_OUTPUT.PUT_LINE(‘遊標沒有開啟‘);  END IF;    CLOSE emp_cursor;END;

通過%ISOPEN屬性判斷遊標是否開啟，%ROWCOUNT判斷擷取行數。2、隱式遊標：由系統定義並為它建立工作區域，並且隱式的定義開啟提取關閉，隱式遊標的遊標名就是‘SQL‘,屬性和顯示遊標相同，主要用於對單行select語句或dml操作進行處理。Example:又使用者輸入員工號修改員工工資如成功則列印輸出成功標誌。為了盡量不改變原表，建立新表emp_new和原表資料相同：

CREATE TABLE emp_newASSELECT * FROM emp;

BEGIN  UPDATE emp_new SET sal = sal+500 WHERE empno=&empno;  IF SQL%FOUND THEN    DBMS_OUTPUT.PUT_LINE(‘成功修改‘);    COMMIT;  ELSE    DBMS_OUTPUT.PUT_LINE(‘修改失敗‘);    ROLLBACK;  END IF;END;

這裡注意增刪改以後要對做的操作進行commit提交，如果操作失敗則rollback復原剛才的操作。3、參數遊標：在定義遊標時加入參數的遊標，可以配合遊標for迴圈快速找到需要的資料。這裡先講一下遊標for迴圈A、遊標FOR迴圈：隱含的執行了開啟提取關閉資料，代碼精簡很多。Expression:FOR table_record IN table_cursor LOOP　　STATEMENT;END LOOP; Example:使用遊標For迴圈列印輸出員工資訊：

DECLARECURSOR emp_cursor IS SELECT empno,ename,job FROM emp;BEGIN  FOR emp_record IN emp_cursor LOOP    DBMS_OUTPUT.PUT_LINE(‘員工號：‘||emp_record.empno||‘員工姓名‘||emp_record.ename||‘員工職位‘||emp_record.job);  END LOOP;END;

這裡遊標FOR迴圈省去了對於取到的資料的變數的命名和賦值，同時如果全部列印則不用寫迴圈條件，代碼精簡了很多。如果想讓代碼更加精簡，則可以去掉對遊標的聲明引入子查詢即可，操作如下。

BEGIN  FOR emp_record IN (SELECT empno,ename,job FROM emp) LOOP    DBMS_OUTPUT.PUT_LINE(‘員工號：‘||emp_record.empno||‘員工姓名‘||emp_record.ename||‘員工職位‘||emp_record.job);  END LOOP;END;

代碼更加精簡，得到的結果相同。和隱式遊標是不是有點像，但隱式遊標主要用於的是單行select和dml語句的操作，注意2者用法的區別。下面繼續參數遊標的執行個體：Example：輸入部門號列印員工資訊：

DECLARECURSOR emp_cursor(dno NUMBER)IS SELECT empno,ename,job FROM emp WHERE deptno=dno;BEGIN  FOR emp_record IN emp_cursor(&dno) LOOP    DBMS_OUTPUT.PUT_LINE(‘員工號‘||emp_record.empno||‘姓名‘||emp_record.ename||‘職位‘||emp_record.job);  END LOOP;END;

 

 

這裡既然有參數，那麼必然會有對遊標的聲明，在結合遊標FOR迴圈快速超找所需要的資料。三、使用遊標修改資料的注意事項1、使用遊標修改資料時，為防止他人在自己操作資料時對資料進行修改，oracle提供for update子句進行加鎖。同時在你使用update或delete時，必須使用where current of+name_cursor語句，以及在最後記得提交。如果是級聯操作則可以使用for update of 來進行相關表的加鎖。Example1：對職位是PRESIDENT的員工加1000工資，MANAGER的人加500工資

CREATE TABLE emp_newASSELECT * FROM emp;

DECLARECURSOR empnew_cursor IS SELECT ename,job FROM emp_new FOR UPDATE;BEGIN  FOR empnew_record IN empnew_cursor LOOP    DBMS_OUTPUT.PUT_LINE(‘姓名‘||empnew_record.ename||‘職位‘||empnew_record.job);    IF empnew_record.job=‘PRESIDENT‘ THEN      UPDATE emp_new SET sal=sal+1000 WHERE CURRENT OF empnew_cursor;    ELSIF empnew_record.job=‘MANAGER‘ THEN      UPDATE emp_new SET sal=sal+500 WHERE CURRENT OF empnew_cursor;    END IF;  END LOOP;  COMMIT;END;

SELECT * FROM EMP WHERE job in(‘PRESIDENT‘,‘MANAGER‘);SELECT * FROM EMP_NEW WHERE job in(‘PRESIDENT‘,‘MANAGER‘);

可以看到這裡工資有了相應的變化。  至此，Oracle遊標解析完畢，總而言之，遊標只是作為我們從資料庫中提取出來的一部分資料，我們針對這個結果集做一系列的操作。　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　2018-09-07    16:15:34

 

Oracle遊標解析