orcale 之遊標的屬性

風靈使發表於2018-11-14

無論是顯式遊標還是隱式遊標都會有四個屬性分別是:%ISOPEN、%FOUND、%NOTFOUND、%ROWCOUNT 下面我們分別對著幾種屬性進行分析。

%FOUND

該屬性表示當前遊標是否指向有效的一行,結果是一個Boolean 型別的。這個屬性一般用來判斷是否結束當前遊標的使用。

OPEN emp_cur; --開啟遊標
  --將第一行的資料放入變數中,之後遊標後移
  FETCH emp_cur INTO emp_no, emp_nanme;
  LOOP
    EXIT WHEN NOT emp_cur%FOUND; --如果遊標已經到末尾結束
    IF emp_no = '7839' THEN
      INSERT INTO t_emp values(emp_no,emp_nanme);
    else
      INSERT INTO t_emp2 values(emp_no,emp_nanme);
    END IF;
    FETCH emp_cur INTO emp_no, emp_nanme;
  END LOOP;
CLOSE emp_cur; --關閉遊標

在隱式遊標中引用方法為 SQL%FOUND

delete from student where sid = 1;
if SQL%FOUND THEN
    INSERT INTO states values(1,1);
else
    INSERT INTO states values(1,0);
end if;

%NOTFOUND

這個屬性和上面的 %FOUND 很類似,只不過結果相反。這裡就不舉例子了,大家自己練習下。

%ROWCOUNT

這個屬性記錄了遊標取過記錄的行數,也可以理解為當前遊標所在的行數。這個屬性在迴圈的時候十分有效。

LOOP
    FETCH emp_cur INTO emp_no, emp_nanme;
    EXIT WHEN emp_cur%ROWCOUNT=10;
    ...
END LOOP;

在隱式遊標中使用 %ROWCOUNT

%ISOPEN

顧名思義是表示遊標是否處於開啟的狀態。在我們的實際使用前最好使用該屬性進行檢查。

IF emp_cur%ISOPEN THEN
    FETCH emp_cur INTO emp_no, emp_nanme;
ELSE
    OPEN emp_cur
END IF;

在隱式遊標中可以使用 SQL%ISOPEN 進行引用,但是需要說的是隱式遊標中 SQL%ISOPEN 總是為 TRUE 因此隱式遊標中不需要開啟遊標關閉遊標,也不需要對遊標的狀態進行檢查。


oracle中的sql%foundsql%notfoundsql%rowcountsql%isopen

Oracle 儲存過程 刪除表記錄時刪除不存在的記錄也是顯示刪除成功

create or replace procedure delDept(p_deptno in dept.deptno%type) is
begin
    delete from dept where deptno=p_deptno;
    dbms_output.put_line('部門刪除成功...');
    exception when others then
    dbms_output.put_line('部門刪除失敗...');
end;

刪除不存在的資料並不會促發Oracle的異常
對於delete from dept where deptno=p_deptno;
判斷是否有資料被刪除可以用sql%rowcountsql%notfound 來判斷

if sql%rowcount = 0 then
    dbms_output.put_line('部門刪除失敗...');
    end if;
    或者
    if sql%notfound then
    dbms_output.put_line('部門刪除失敗...');
    end if;

create or replace procedure delDept(p_deptno in dept.deptno%type) is
EmpCount    NUMBER; --影響的記錄數
begin
    delete from dept where deptno=p_deptno;
     EmpCount := SQL%ROWCOUNT;
     if(EmpCount <>0)
     then
    dbms_output.put_line('部門刪除成功...');
    exception when others then
    dbms_output.put_line('部門刪除失敗...');
end;

sql%rowcount用於記錄修改的條數,就如你在sqlplus下執行delete from之後提示已刪除xx行一樣,這個引數必須要在一個修改語句和commit之間放置,否則你就得不到正確的修改行數。

例如:

SQL> declare n number;
  2  begin
  3  insert into test_a select level lv from dual connect by level<500;
  4  n:=sql%rowcount;
  5  commit;
  6  dbms_output.put_line(n);
  7  end;
  8  /

如果我連續執行了3個select語句
然後呼叫sql%rowcount,得到的結果是最後一個select的條數。 要統計所有的,可以使用三個變數接收sql%rowcount,然後相加即可,不過貌似直接select的寫法無法使用sql%rowcount

在執行DMLinsert,update,delete)語句時,可以用到以下三個隱式遊標(遊標是維護查詢結果的記憶體中的一個區域,執行DML時開啟,完成時關閉,用sql%isopen檢查是否開啟):

sql%found (布林型別,預設值為null
sql%notfound(布林型別,預設值為null
sql%rowcount(數值型別預設值為0)
sql%isopen(布林型別)

當執行一條DML語句後,DML語句的結果儲存在四個遊標屬性中,這些屬性用於控制程式流程或者瞭解程式的狀態。當執行DML語句時,PL/SQL開啟一個內建遊標並處理結果,遊標是維護查詢結果的記憶體中的一個區域,遊標在執行DML語句時開啟,完成後關閉。隱式遊標只使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT三個屬性.SQL%FOUND,SQL%NOTFOUND是布林值,SQL%ROWCOUNT是整數值。

SQL%FOUNDSQL%NOTFOUND在執行任何DML語句前SQL%FOUNDSQL%NOTFOUND的值都是NULL,在執行DML語句後,SQL%FOUND的屬性值將是:

  • TRUE :INSERT
  • TRUE :DELETEUPDATE,至少有一行被DELETEUPDATE.
  • TRUE :SELECT INTO至少返回一行

SQL%FOUNDTRUE時,SQL%NOTFOUNDFALSE

SQL%ROWCOUNT

在執行任何DML語句之前,SQL%ROWCOUNT的值都是NULL,對於SELECT INTO語句,如果執行成功,SQL%ROWCOUNT的值為1,如果沒有成功或者沒有操作(如update、insert、delete為0條),SQL%ROWCOUNT的值為0.

SQL%ISOPEN

SQL%ISOPEN是一個布林值,如果遊標開啟,則為TRUE, 如果遊標關閉,則為FALSE.對於隱式遊標而言SQL%ISOPEN總是FALSE,這是因為隱式遊標在DML語句執行時開啟,結束時就立即關閉。

no_data_foundsql%notfound 以及sql%rowcount 的區別:

NO_DATA_FOUND:該異常可以在兩種不同的情況下出現:第一種:當SELECT。。。。INTO語的 WHERE子句 沒匹配任何資料行時;第二種:試圖引用尚未賦值的PL/SQL index-by表元素時。

SQL%NOTFOUND:是隱匿遊標的屬性,當沒有可檢索的資料時,該屬性為:TRUE;常作為檢索迴圈退出的條件。若某UPDATEDELETE語句的WHERE子句不匹配任何資料行,該屬性為:TRUE,但不併不出現NO_DATA_FOUND異常.

SQL%ROWCOUNT:該數字屬性返回了到目前為止,遊標所檢索資料庫行的個數。

相關文章