隱式遊標(implicit cursor)_sql%found_rowcount小操作

wisdomone1發表於2010-06-21
SQL
#sql%found用於dml(delete,update,insert)及select into是否真正的操作了表
SQL> desc dept;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(20)
SQL> declare
  2  v_name varchar2(20):='zxy';
  3  begin
  4   delete from dept where name=v_name;
  5    if sql%found then
  6      dbms_output.put_line('the record deleted');
  7      insert into dept values(10,'other');
  8 
  9    end if;
 10  end;
 11  /
the record deleted
PL/SQL procedure successfully completed.
SQL> select * from dept;
        ID NAME
---------- --------------------
        10 other
 
#sql%rowcount用於dml及select into影響表的多少行記錄
SQL> select * from dept;
        ID NAME
---------- --------------------
        10 other
SQL> declare
  2   v_name varchar2(20):='zxy';
  3  begin
  4     delete from dept where name=v_name;
  5     dbms_output.put_line(to_char(sql%rowcount)||' number of dept deleted');
  6  end;
  7  /
0 number of dept deleted
PL/SQL procedure successfully completed.
SQL>
 

#sql%notfound

SQL> declare
  2    v_name varchar2(20):='zxy';
  3  begin
  4    delete from dept where name=v_name;
  5     if sql%notfound then
  6          dbms_output.put_line('not found the record');
  7          insert into dept values(88,v_name);
  8     else
  9         dbms_output.put_line('table dept total records '||to_char(sql%rowcount));
 10     end if;
 11  end;
 12  /
not found the record
PL/SQL procedure successfully completed.
SQL> select * from dept;
        ID NAME
---------- --------------------
        10 other
        88 zxy
SQL>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-665812/，如需轉載，請註明出處，否則將追究法律責任。

相關文章