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