程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 游標(學習筆記)，游標學習筆記

游標(學習筆記)，游標學習筆記

編輯：Oracle教程

游標(學習筆記)，游標學習筆記

--游標分2種類型：
--靜態游標：結果集已經存在（靜態定義）的游標,分為隱式和顯示游標
--隱式游標：所有DML語句為隱式游標，通過隱式游標屬性可以獲取SQL語句信息
--顯示游標：用戶顯示聲明的游標，即指定結果集，當查詢返回結果超過一定行時，就需要一個顯示游標
--REF 游標：動態關聯結果集的臨時對象
-強類型：帶return類型
-弱類型: 不帶return類型

--隱式游標
--在PL/SQL中編寫的每條SQL 語句實際上都是隱匿游標。通過在DML操作後使用SQL%ROWCOUNT屬性，可以
--知道語句所改變的行數(INSERT ,UPDATE,DELETE)返回理新行數，SELECT 返回查詢行數.

--顯示游標
--語法：CURSOR 游標名稱 ([參數列表，]) [RETURN 返回值類型]
IS 子查詢（SELECT _statement）
--第一步：聲明游標：
CURSOR 游標名 IS SELECT 。。使用CURSOR定義
--第二步:打開游標
使用OPEN
OPEN 游標名
--第三步：提取游標
使用FETCH 游標 INTO 變量
--第四步:關閉游標
CLOSE 游標名
--顯式游標屬性:
%FOUND 找到是否找到數據，有數據TRUE，沒有則FALSE
%ISOPEN 判斷游標是否打開，打開則返回TRUE，沒有打開則返回FALSE
%NOTFOUND 返回FETCH ...INTO...是否有數據如果沒有返回TRUN，有則為FALSE
%ROWCOUNT 返回執行FETCH 語句所返回的行數，初始為0,每執行一行則%ROWCOUNT增加1
--隱式游標屬性:
SQL%FOUND 找到是否找到數據，有數據TRUE，沒有則FALSE
SQL%ISOPEN 判斷游標是否打開，打開則返回TRUE，沒有打開則返回FALSE
SQL%NOTFOUND 返回FETCH ...INTO...是否有數據如果沒有返回TRUN，有則為FALSE
SQL%ROWCOUNT 返回執行FETCH 語句所返回的行數，初始為0,每執行一行則%ROWCOUNT增加1

隱式游標:

--驗證SQL%ROWCOUNT

DECLARE
  v_count         NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM dept; --只返回一行結果
  dbms_output.put_line('SQL%ROWCOUNT= '|| SQL%ROWCOUNT);
END;

結果:SQL%ROWCOUNT= 1

--驗證SLQ%ROWCOUNT並返回行數

DECLARE
BEGIN
   INSERT INTO dept(deptno,dname,loc)VALUES(90,'qqqq','北京');
     dbms_output.put_line('SQL%ROWCOUNT= '|| SQL%ROWCOUNT);
END;

結果:SQL%ROWCOUNT= 1

--單行隱式游標

DECLARE
   v_empRow         emp%ROWTYPE;
BEGIN
  SELECT * INTO v_empRow FROM emp WHERE empno=7369;
  IF SQL%FOUND THEN   --發現數據
    dbms_output.put_line('員工姓名: '|| v_empRow.ename||'職位: '||v_empRow.job);
    END IF;
END;

結果：員工姓名: SMITH職位: CLERK

--多行隱式游標

DECLARE
BEGIN
  UPDATE EMP SET SAL = SAL * 1.2;
  IF SQL%FOUND THEN
    --發現數據
    DBMS_OUTPUT.PUT_LINE('更新行數' || SQL%ROWCOUNT);
  ELSE
    DBMS_OUTPUT.PUT_LINE('更新行數' || SQL%ROWCOUNT);
  END IF;
END;

結果:更新行數14

--顯示游標
--定義游標例1：

DECLARE
 CURSOR emp_cur IS
 SELECT empno,ename FROM emp;                    --定義游標
 v_id     emp.empno%TYPE;              --定義變量ID
 v_name    emp.ename%TYPE;
BEGIN
  OPEN emp_cur ;                          --打開游標
  FETCH emp_cur INTO v_id,v_name;    ---提示取游標
  LOOP
    EXIT WHEN emp_cur%notFOUND;                     --判斷是否還有數據
    dbms_output.put_line('員工編號'||v_id||',員工姓名:'||v_name);
    FETCH emp_cur INTO v_id,v_name;    ---提示取游標
  END LOOP;
  CLOSE emp_cur;                  --關閉游標
END;

結果:

員工編號7369,員工姓名:SMITH
員工編號7499,員工姓名:ALLEN
員工編號7521,員工姓名:WARD
員工編號7566,員工姓名:JONES
員工編號7654,員工姓名:MARTIN
員工編號7698,員工姓名:BLAKE
員工編號7782,員工姓名:CLARK
員工編號7788,員工姓名:SCOTT
員工編號7839,員工姓名:KING
員工編號7844,員工姓名:TURNER
員工編號7876,員工姓名:ADAMS
員工編號7900,員工姓名:JAMES
員工編號7902,員工姓名:FORD
員工編號7934,員工姓名:MILLER

--定義游標例2：

DECLARE
  V_NAME  VARCHAR2(50); --定義變量姓名
  V_DNAME VARCHAR2(50); --定義變量部門名稱
  CURSOR CUR_E IS --定義游標
    SELECT ENAME, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
BEGIN
  OPEN CUR_E; --打開游標
  LOOP
    --使用循環來讀取游標
    FETCH CUR_E
      INTO V_NAME, V_DNAME; --提取游標
    EXIT WHEN CUR_E%NOTFOUND; --判斷游標是否還有內容
    DBMS_OUTPUT.PUT_LINE(CUR_E%ROWCOUNT || '  員工姓名:' || V_NAME || ' 部門名稱:' ||
                         V_DNAME);                            --輸出內容
  
  END LOOP;
  CLOSE CUR_E; --關閉游標
END;

結果:

程序結果:
1  員工姓名:SMITH 部門名稱:RESEARCH
2  員工姓名:ALLEN 部門名稱:SALES
3  員工姓名:WARD 部門名稱:SALES
4  員工姓名:JONES 部門名稱:RESEARCH
5  員工姓名:MARTIN 部門名稱:SALES
6  員工姓名:BLAKE 部門名稱:SALES
7  員工姓名:CLARK 部門名稱:ACCOUNTING
8  員工姓名:SCOTT 部門名稱:RESEARCH
9  員工姓名:KING 部門名稱:ACCOUNTING
10  員工姓名:TURNER 部門名稱:SALES
11  員工姓名:ADAMS 部門名稱:RESEARCH
12  員工姓名:JAMES 部門名稱:SALES
13  員工姓名:FORD 部門名稱:RESEARCH
14  員工姓名:MILLER 部門名稱:ACCOUNTING

另一種指定變量類型:

DECLARE
  V_EMPNAME EMP.ENAME%TYPE;
  V_DNAME   DEPT.DNAME%TYPE;
  CURSOR EMP_CUR IS
    SELECT E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;
BEGIN
  OPEN EMP_CUR;
  LOOP
    FETCH EMP_CUR
      INTO V_EMPNAME, V_DNAME;
    EXIT WHEN EMP_CUR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('員工姓名 :' || V_EMPNAME || '，部門名稱 :' || V_DNAME);
  END LOOP;
  CLOSE EMP_CUR;
END;

結果同上

--定義游標例3：

DECLARE
  CURSOR CUR_EMP IS
    SELECT * FROM EMP;
  V_EMPROW EMP%ROWTYPE;
BEGIN
  IF CUR_EMP%ISOPEN THEN
    NULL;
  ELSE
    OPEN CUR_EMP;
  END IF;
  FETCH CUR_EMP
    INTO V_EMPROW;
  WHILE CUR_EMP%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('員工姓名: ' || V_EMPROW.ENAME || '，職位: ' ||
                         V_EMPROW.JOB || ' ,工資' || V_EMPROW.SAL);
    FETCH CUR_EMP
      INTO V_EMPROW;
  END LOOP;
  CLOSE CUR_EMP;
END;

結果:

員工姓名: SMITH，職位: CLERK ,工資800
員工姓名: ALLEN，職位: SALESMAN ,工資1600
員工姓名: WARD，職位: SALESMAN ,工資1250
員工姓名: JONES，職位: MANAGER ,工資2975
員工姓名: MARTIN，職位: SALESMAN ,工資1250
員工姓名: BLAKE，職位: MANAGER ,工資2850
員工姓名: CLARK，職位: MANAGER ,工資2450
員工姓名: SCOTT，職位: ANALYST ,工資3000
員工姓名: KING，職位: PRESIDENT ,工資5000
員工姓名: TURNER，職位: SALESMAN ,工資1500
員工姓名: ADAMS，職位: CLERK ,工資1100
員工姓名: JAMES，職位: CLERK ,工資950
員工姓名: FORD，職位: ANALYST ,工資3000
員工姓名: MILLER，職位: CLERK ,工資1300

--使用FOR循環

DECLARE 
CURSOR cur_emp IS SELECT * FROM emp;
BEGIN
  FOR emp_row IN cur_emp LOOP
    DBMS_OUTPUT.PUT_LINE('員工姓名: ' || emp_row.ENAME || '，職位: ' ||
                         emp_row.JOB || ' ,工資' || emp_row.SAL);
     END LOOP;
END;

結果同上

--使用FOR循環操作游標不僅代碼簡單，而且可以將游標的狀態交給系統去完成，盡量使用FOR循環為主

--定義游標例4：使用游標UPDATE數據
--公司上市，決定給員工漲工資，入職年限超過1年加100，1000元封頂
--第一種直接將計算的結果進行判斷

DECLARE
  V_ID       EMP.EMPNO%TYPE;
  V_HIREDATE EMP.HIREDATE%TYPE;
  CURSOR EMP_CUR IS
    SELECT EMPNO, HIREDATE FROM EMP;
BEGIN
  OPEN EMP_CUR;
  LOOP
    FETCH EMP_CUR
      INTO V_ID, V_HIREDATE;
    EXIT WHEN EMP_CUR%NOTFOUND;
    IF (TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(V_HIREDATE, 'yyyy')) * 100 < 1000 THEN
      UPDATE EMP
         SET SAL = SAL +
                   (TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(V_HIREDATE, 'yyyy')) * 100
       WHERE EMPNO = V_ID;
      DBMS_OUTPUT.PUT_LINE('工資增加成功');
      COMMIT;
    ELSE
      UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = V_ID;
      DBMS_OUTPUT.PUT_LINE('工資增加成功');
      COMMIT;
    END IF;
  END LOOP;
  CLOSE EMP_CUR;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('數據異常');
    ROLLBACK;
END;

--第二種通過一個變量判斷

DECLARE
  V_ID       EMP.EMPNO%TYPE; --定義員工編號ID
  V_HIREDATE EMP.HIREDATE%TYPE; --定義員工入職日期變量
  V_SAL      EMP.SAL%TYPE; --定義計算每個員工要漲工資的總數變量
  CURSOR CUR_EMP IS
    SELECT EMPNO, HIREDATE --定義游標查詢員工ID和入職日期
      FROM EMP;
BEGIN
  IF CUR_EMP%ISOPEN THEN
    --判斷游標是否打開
    NULL; --打開了就什麼也不做
  ELSE
    OPEN CUR_EMP; --沒有打開就打開游標
  END IF;
  LOOP
    FETCH CUR_EMP
      INTO V_ID, V_HIREDATE;
    EXIT WHEN CUR_EMP%NOTFOUND;
    V_SAL := (TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(V_HIREDATE, 'yyyy')) * 100;
    IF V_SAL < 1000 THEN
      --判斷是否小於1000
      UPDATE EMP SET SAL = SAL + V_SAL WHERE EMPNO = V_ID;
      COMMIT;
    ELSE
      --大於1000
      UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = V_ID;
      COMMIT;
    END IF;
  END LOOP;
  CLOSE CUR_EMP; --關閉游標
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('數據異常');
    ROLLBACK; --出現異常 就回滾
END;

--定義游標例5
--在動態SELECT中使用游標

DECLARE
  V_LOWSAL EMP.SAL%TYPE := &LOWSAL;
  V_HISAL  EMP.SAL%TYPE := &HISSAL;
  CURSOR CUR_EMP IS
    SELECT * FROM EMP WHERE SAL BETWEEN V_LOWSAL AND V_HISAL;
BEGIN
  FOR EMP_ROW IN CUR_EMP LOOP
    DBMS_OUTPUT.PUT_LINE('員工姓名: ' || EMP_ROW.ENAME || '，職位: ' ||
                         EMP_ROW.JOB || ' ,工資' || EMP_ROW.SAL);
  END LOOP;
END;

--REF動態游標
TYPE 類型名 IS REF CURSOR [RETURN]數據類型
游標名類型名
OPEN 游標名 FOR 查詢語句
--強類型:帶RETURN

DECLARE
  TYPE REF_EMP IS REF CURSOR RETURN EMP%ROWTYPE; --定義一個REF動態游標，並返回類型
  CUR_EMP REF_EMP; --定義一個變量類型是上面的REF動態游標也稱游標變量
  V_EMP   EMP%ROWTYPE; --定義一個變量，類型和REF游標返回類型相同，行類型
BEGIN
  OPEN CUR_EMP FOR
    SELECT * FROM EMP; --打開游標，並關聯查詢語句
  LOOP
    FETCH CUR_EMP
      INTO V_EMP; --提取游標數據
    EXIT WHEN CUR_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT||'  員工編號:' || V_EMP.EMPNO || ' 員工姓名:' || V_EMP.ENAME);
  END LOOP;
  CLOSE CUR_EMP;
END;

--弱類型:不帶RETURN

DECLARE
  TYPE REF_EMP IS REF CURSOR; --定義一個REF動態游標，並返回類型
  CUR_EMP REF_EMP; --定義一個變量類型是上面的REF動態游標也稱游標變量
  V_EMP   EMP%ROWTYPE; --定義一個變量，類型和REF游標返回類型相同，行類型
  V_DEPT  DEPT%ROWTYPE; --定義一個變量，類型和REF游標返回類型相同，行類型
BEGIN
  --員工表
  OPEN CUR_EMP FOR
    SELECT * FROM EMP; --打開游標，並關聯查詢語句
  LOOP
    FETCH CUR_EMP
      INTO V_EMP; --提取游標數據
    EXIT WHEN CUR_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT || '  員工編號:' || V_EMP.EMPNO ||
                         ' 員工姓名:' || V_EMP.ENAME);
  END LOOP;
  CLOSE CUR_EMP;
  ------------下面是部門表  
  OPEN CUR_EMP FOR
    SELECT * FROM DEPT; --打開游標，並關聯查詢語句
  LOOP
    FETCH CUR_EMP
      INTO V_DEPT; --提取游標數據
    EXIT WHEN CUR_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT || '  部門編號:' || V_DEPT.DEPTNO ||
                         ' 部門名稱:' || V_DEPT.DNAME);
  END LOOP;
  CLOSE CUR_EMP;
END;

在Oracle9i之後為了方便用戶使用弱類型游標變量，可以使用  
SYS_REFCURSOR 來替代  TYPE REF_EMP IS REF CURSOR
上面的聲明可以換為:
  CUR_EMP  SYS_REFCURSOR; --定義一個變量類型是上面的REF動態游標也稱游標變量
  V_EMP   EMP%ROWTYPE; --定義一個變量，類型和REF游標返回類型相同，行類型
  V_DEPT  DEPT%ROWTYPE; --定義一個變量，類型和REF游標返回類型相同，行類型

--根據用戶輸入，來輸出內容

DECLARE
  --TYPE REFC_T IS REF CURSOR;
  REFC    SYS_REFCURSOR;
  V_ID    NUMBER;
  V_NAME  VARCHAR2(50);
  V_INPUT VARCHAR(1) := UPPER(SUBSTR('&input', 1, 1));
BEGIN
  IF V_INPUT = 'E' THEN
    OPEN REFC FOR
      SELECT EMPNO, ENAME FROM EMP;
    DBMS_OUTPUT.PUT_LINE('=====員工表信息======');
  ELSIF V_INPUT = 'D' THEN
    OPEN REFC FOR
      SELECT DEPTNO, DNAME FROM DEPT;
    DBMS_OUTPUT.PUT_LINE('=====部門表信息======');
  ELSE
    DBMS_OUTPUT.PUT_LINE('=====員工表信息(E)或者部門表信息(D)=======');
    RETURN;
  END IF;
  FETCH REFC
    INTO V_ID, V_NAME;
  WHILE REFC%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(REFC%ROWCOUNT || '# ' || V_ID || '  ' || V_NAME);
    FETCH REFC
      INTO V_ID, V_NAME;
  END LOOP;
  CLOSE REFC;
END;