程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle基礎游標，oracle基礎游標

Oracle基礎游標，oracle基礎游標

編輯：Oracle教程

Oracle基礎游標，oracle基礎游標

一、游標　　

　　游標用來處理從數據庫中檢索的多行記錄（使用SELECT語句）。利用游標，程序可以逐個地處理和遍歷一次檢索返回的整個記錄集。

為了處理SQL語句，Oracle將在內存中分配一個區域，這就是上下文區。這個區包含了已經處理完的行數、指向被分析語句的指針，整個區是查詢語句返回的數據行集。游標就是指向上下文區句柄或指針。

二、游標的分類：

　　1、靜態游標：靜態游標是在編譯時知道其SELECT語句的游標。靜態游標又分為兩種類型，隱式游標和顯示游標。

　　2、動態游標：用戶為游標使用的查詢直到運行的時候才能確定，可以使用REF游標和游標變量滿足這個要求。為了使用引用游標，必須聲明游標變量。有兩種類型的REF游標：強類型REF游標和弱類型REF游標。

三、顯示游標的用法：

　　顯示游標被用於處理返回多行數據的SELECT 語句，游標名通過CURSOR….IS 語句顯示地賦給SELECT 語句。

（一）使用步驟；

1）聲明游標：CURSOR cursor_name IS select_statement

2）為查詢打開游標：OPEN cursor_name

3）取得結果放入PL/SQL變量中；

FETCH cursor_name INTO list_of_variables;

　FETCH cursor_name INTO PL/SQL_record;

4）關閉游標。CLOSE cursor_name

　　注意：在聲明游標時，select_statement不能包含INTO子句。當使用顯示游標時，INTO子句是FETCH語句的一部分。

　　例：顯示雇員的名稱和薪水

--使用LOOP遍歷游標
DECLARE
  v_name emp.ename%TYPE;
  v_sal emp.sal%TYPE;
  CURSOR cus_emp IS
    SELECT ename,sal FROM emp;                         --聲明游標
BEGIN
   OPEN cus_emp;                                       --打開游標
   LOOP 
      FETCH cus_emp INTO v_name,v_sal;                 --提取游標
      EXIT WHEN cus_emp%NOTFOUND;
      dbms_output.put_line('第'||cus_emp%ROWCOUNT||'個用戶:  name:'||v_name||'     sal:'||v_sal);
   END LOOP;
   CLOSE cus_emp;                                      --關閉游標
END;

　　顯示游標的屬性：

　　%FOUND:只有在DML語句影響一行或者多行是，則返回TRUE；

　　%NOTFOUND：沒有影響任何行，則返回TRUE。

　　%ROWCOUNT：返回DML語句影響的行數，沒有影響則返回0；

　　%ISOPEN：返回游標是否打開，在執行SQL之後，Oracle自動關閉SQL游標，所以隱式游標的%isopen屬性始終未false；

　　另一種方式：

--使用for來簡化游標遍歷
DECLARE
  CURSOR cus_emp IS
    SELECT ename,sal FROM emp;
BEGIN
   FOR record_emp IN cus_emp
   LOOP 
      dbms_output.put_line('第'||cus_emp%ROWCOUNT||'個用戶:  name:'||record_emp.ename||'     sal:'||record_emp.sal);
   END LOOP;
END;

　　record_emp是plsql聲明的的記錄變量，此變量的屬性為聲明為%ROWTYPE類型，作用域在FOR循環之內，即在FOR循環外就不能訪問了。

　　循環游標的特性：
　　（1）從游標中提取了所有記錄之後自動終止。
　　（2）提取和處理游標中的每一條記錄。
　　（3）如果在提取記錄之後%NOTFOUND屬性返回TRUE，則終止循環。如果未返回任何行，則不進入循環。

游標案例：　　

--給員工加薪，按員工入職時間進行加薪，每年加100，1000封頂
DECLARE
  v_date emp.hiredate%TYPE;
  v_empno emp.empno%TYPE;
  v_money NUMBER;
  CURSOR cur_emp IS
    SELECT empno,hiredate FROM emp;
BEGIN
  OPEN  cur_emp;
  LOOP
     FETCH cur_emp INTO v_empno,v_date;
     EXIT WHEN cur_emp%NOTFOUND;
     v_money := 100*(1990-to_char(v_date,'yyyy'));
     IF v_money<1000 THEN
        UPDATE emp SET sal=sal+v_money WHERE empno=v_empno;
     ELSE
        UPDATE emp SET sal=sal+1000 WHERE empno=v_empno;
     END IF;   
  END LOOP;
END;

四、隱式游標：

　　所有的隱式游標都被假設為只返回一條記錄。
　　使用隱式游標時，用戶無需進行聲明、打開及關閉。PL/SQL隱含地打開、處理，然後關掉游標。多條sql語句隱式游標SQL永遠指的是最後一條sql語句的結果，主要使用在update 和 delete語句上。

　　隱式游標的四個屬性：

屬性說明 SQL%rowcount 影響的記錄的行數整數（用來判斷插入，更新修改是否成功，必須在comit之前，否則提交後結果為0.） SQL%found 影響到了記錄 true() SQL%notfound 沒有影響到記錄 true SQL%isopen 是否打開布爾值永遠是false

例如：

DECLARE
  row_emp emp%ROWTYPE;
BEGIN
  SELECT ename,sal INTO row_emp.ename,row_emp.sal
  FROM emp WHERE emp.empno = 7369;
  --判斷是否查到數據
  if(SQL%ROWCOUNT=1) THEN
    dbms_output.put_line('找到了');
  END IF;
  --另一種方式判斷
  IF(SQL%Found) THEN
    dbms_output.put_line('找到了');
  END IF;

  dbms_output.put_line('ename:'||row_emp.ename||'    sal:'||row_emp.sal);
END;

上述游標自動打開，並把相關值賦給對應變量，然後關閉。執行完後，PL/SQL變量rowemp.ename,rowemp.sal中已經有了值。

五：動態游標

　　靜態游標是在聲明就已經確定查詢語句，如果用戶需要在運行時動態決定游標執行的查詢，就需要使用動態游標（REF游標）。

　　動態游標分為兩類：強類型游標和弱類型游標。

　　動態游標使用步驟：

　　1、聲明動態游標類型；

　　2、打開游標，指定游標查詢；

　　3、提取游標。

　　4、關閉游標。

　　例：

　　強類型游標，使用return聲明的游標為強類型游標。在對游標進行綁定查詢只能綁定游標返回的類型rowtype。

--強類型的動態游標,查詢emp表中的數據
DECLARE
  TYPE ref_cur IS REF CURSOR    --聲明游標類型
  RETURN emp%ROWTYPE;       --帶返回值的為強類型動態游標
  refcur_emp ref_cur;         --游標類型對象
  v_emp emp%ROWTYPE;
BEGIN
  OPEN refcur_emp FOR        --將游標綁定到一個查詢語句,因為聲明的是強類型，所以只能綁定emp;
    SELECT * FROM emp;    
  LOOP
    FETCH refcur_emp INTO v_emp;     --提取游標內容
    EXIT WHEN refcur_emp%NOTFOUND;
    dbms_output.put_line(refcur_emp%Rowcount||'、name:'||v_emp.ename||'    sal:'||v_emp.sal);
  END LOOP;
  CLOSE refcur_emp;
END;

　　弱類型游標：可以用來綁定多個查詢結果。

　　例：

--弱類型游標
DECLARE
  TYPE refcur IS REF CURSOR;   --未定義返回類型為弱類型游標
  rc refcur;
  v_name emp.ename%TYPE;
  v_deptname dept.dname%TYPE;
BEGIN
  OPEN rc FOR SELECT ename,dname FROM emp e,dept d WHERE e.deptno = d.deptno;  --綁定查詢
  LOOP
    FETCH rc INTO v_name,v_deptname;    
    EXIT WHEN rc%NOTFOUND;
    dbms_output.put_line('name:'||v_name||'               deptname:'||v_deptname);
  END LOOP;
  CLOSE rc;
END;

　　例：

--根據輸入的內容綁定游標
DECLARE 
  TYPE refcur IS REF CURSOR;
  rc refcur;
  v_tablename VARCHAR2(10) := '&tab';
  v_id NUMBER;
  v_name VARCHAR2(20);
BEGIN
  IF(v_tablename = 'e') THEN
    OPEN rc FOR 
      SELECT e.empno,e.ename INTO v_id,v_name FROM emp e;
      dbms_output.put_line('=========員工信息=============');
  Elsif(v_tablename = 'd') THEN
    OPEN rc FOR 
      SELECT d.deptno,d.dname INTO v_id,v_name FROM dept d;
      dbms_output.put_line('=========部門信息=============');
  ELSE
      dbms_output.put_line('輸入錯誤，請輸入e或者d!');
      RETURN;
  END IF;  
  
  LOOP 
    FETCH rc INTO v_id,v_name;
    dbms_output.put_line('#'||rc%Rowcount||'   id:'||v_id||'   name:'||v_name);
    EXIT WHEN rc%NOTFOUND;
  END LOOP;
END;