Oracle遊標解析

來源:互聯網
上載者:User

標籤: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遊標解析

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.