不錯的plsql基礎知識

scu2005發表於2010-01-23
SQL
 PLSQL異常處理
*****************************************
1、宣告異常
 異常名 EXCEPTION；
2、丟擲異常
 RAISE 異常名
3、處理異常
 丟擲異常後的邏輯程式碼不會被繼續執行

異常的定義使用
    ―――――――――――――――――――――――――――――――――――――
    begin
          dbms_output.put_line(1/0);
    exception
            when others then
                dbms_output.put_line('error');
    end;

    declare
            e_myException exception;
    begin
            dbms_output.put_line('hello');
            raise e_myException; --raise丟擲異常,用此關鍵字，丟擲後轉到自定義的e_myException ，執行其裡面的putline函式後，再跳到end處，結束PL/SQL塊，raise接下面的2句不會繼續執行。
            dbms_output.put_line('world');
            dbms_output.put_line(1/0);
    exception
            when e_myException then
                dbms_output.put_line(sqlcode); --當前會話執行狀態，錯誤編碼
                dbms_output.put_line(sqlerrm); --當前錯誤資訊
                dbms_output.put_line('my error');
            when others then
                dbms_output.put_line('error');
    end;
    ―――――――――――――――――――――――――――――――――――――
*****************************************
  PLSQL遊標和goto語句
*****************************************
備註：下面提到的遊晡?STRONG>靜態cursor，包括顯示和隱式。
遊標，從declare、open、fetch、close是一個完整的生命旅程。當然了一個這樣的遊標是可以被多次open進行使用的，顯式cursor是靜態cursor，她的作用域是全域性的，但也必須明白，靜態cursor也只有pl/sql程式碼才可以使用它。靜態遊標變數是在定義時就必須指定SQL語句。

cursor 遊標(結果集)用於提取多行資料，定義後不會有資料，使用後才有。一旦遊標被開啟，就無法再次開啟(可以先關閉，再開啟)。
    declare
          cursor c_student is  select * from book;
    begin
          open c_student;
          close c_student;
    end;

第二種遊標的定義方式，用變數控制結果集的數量。
    declare
          v_id binary_integer;
          cursor c_student is select * from book where id>v_id;
    begin
          v_id:=10;
          open c_student;
          close c_student;
    end;

第三種遊標的定義方式，帶引數的遊標，用的最多。
    declare
          cursor c_student(v_id binary_integer) is select * from book where id>v_id;
    begin
          open c_student(10);
          close c_student;
    end;

遊標的使用，一定別忘了關遊標。
    declare
          v_student book%rowtype;
          cursor c_student(v_id binary_integer) is select * from book where id>v_id;
    begin
          open c_student(10);
          fetch c_student into v_student;
          close c_student;
          dbms_output.put_line(v_student.name);
    end;


如何遍歷遊標fetch
  遊標的屬性 %found，%notfound,%isopen,%rowcount。
  %found:若前面的fetch語句返回一行資料，則%found返回true，如果對未開啟的遊標使用則報ORA-1001異常。
  %notfound，與%found行為相反。
  %isopen，判斷遊標是否開啟。
  %rowcount:當前遊標的指標位移量，到目前位置遊標所檢索的資料行的個數，若未開啟就引用，返回ORA-1001。

注：
no_data_found和%notfound的用法是有區別的，小結如下
1）SELECT . . . INTO 語句觸發 no_data_found；
2）當一個顯式游標(靜態和動態)的 where 子句未找到時觸發 %notfound；
3）當UPDATE或DELETE 語句的where 子句未找到時觸發 sql%notfound；
4）在游標的提取(Fetch)迴圈中要用 %notfound 或%found 來確定迴圈的退出條件，不要用no_data_found。

下面是幾個例項：
create table BOOK
(
  ID       VARCHAR2(10) not null,
  BOOKNAME VARCHAR2(10) not null,
  PRICE    VARCHAR2(10) not null,
  CID      VARCHAR2(10) not null
);

--insert
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
     as
     v_price varchar2(100);
     e_myException exception;

     begin
        insert into book(id,bookname,price) values (1,2,3);
        o_result_msg := 'success';
     exception
        when others then
             rollback;
             o_result_msg := substr(sqlerrm, 1, 200);
     end;

--update or delete
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
     as
     v_price varchar2(100);
     e_myException exception;

     begin
        update book set price = '55' where bookname = i_name;
        delete from book where bookname = i_name;
        if sql%notfound then
           raise e_myException;
        end if;

        o_result_msg := 'success';
     exception
        when e_myException then
             rollback;
             o_result_msg := 'update or delete dail';
     end;

--select
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
     as
     v_price varchar2(100);
     e_myException exception;

     begin
        select price into v_price from book where bookname = i_name;
        o_result_msg := 'success';
     exception
        when no_data_found then
             rollback;
             o_result_msg := 'select into dail';
     end;

loop方式遍歷遊標
    declare
          v_bookname  varchar2(100);
          cursor c_book(i_id number) is select bookname from book where >    begin
        Open  c_book(i_id);
        Loop
            Fetch c_book into v_bookname;
            exit when c_student%notfound;
              update book set price = '33' where bookname = v_bookname;
        End Loop;
        Close c_book;
    end;
    或
    declare
          v_bookname  varchar2(100);
          cursor c_book(i_id number) is select bookname from book where >    begin
        Open  c_book(i_id);
          Fetch c_book into v_bookname;
          While c_book%Found
          Loop
              update book set price = '33' where bookname = v_bookname;
          Fetch  c_book into v_bookname;
          End Loop;
        Close c_book;
    end;
       

while迴圈遍歷遊標，注意，第一次遊標剛開啟就fetch，%found為null，進不去迴圈
解決方法：while nvl(c_student%found,true) loop
    declare
         v_bookname  varchar2(100);
         cursor c_book(i_id number) is select bookname from book where >    begin
         Open  c_book(i_id);
         while nvl(c_book%found,true) --或這種寫法：while c_book%found is null or c_book%found loop   
             Fetch c_book into v_bookname;
             update book set price = '33' where bookname = v_bookname;
         End Loop;
         Close c_book;
    end;

for迴圈遍歷，最簡單，用的最多，不需要宣告v_student,Open和Close遊標和fetch操作(不用開啟遊標和關閉遊標，實現遍歷遊標最高效方式)
    declare
         cursor c_book(i_id number) is select bookname from book where >    begin
         for cur in c_book(i_id) --直接將入參i_id傳入cursor即可
         loop
             update book set price = '53' where bookname = cur.bookname;
         end loop;
    end;

goto例子，一般不推薦使用goto，會使程式結構變亂
    declare
         i number:=0;
    begin
         if i=0 then
             goto hello;
         end if;
         <>
         begin
             dbms_output.put_line('hello');
             goto over;
         end;
         <>
         begin
             dbms_output.put_line('world');
             goto over;
         end;
         <>
             dbms_output.put_line('over');
     end;
*****************************************
   Oracle儲存過程
*****************************************
在談儲存過程書寫中的一些規則時，先看一下執行它的規則，在命令視窗執行儲存過程sp_get_product_prompt
     set serveroutput on
     var ret1 varchar2(200);
     var ret2 varchar2(200);
     exec sp_get_product_prompt(83,:ret1,:ret2); --或execute
     print ret1;
     print ret2;
     或
     set serveroutput on
     declare
        ret1 varchar2(200);
        ret2 varchar2(200);
     begin
        sp_get_product_prompt(83,ret1,ret2);
        dbms_output.put_line(ret1);
        dbms_output.put_line(ret2);
     end;

儲存過程入參，不論型別，預設情況下值都為null,入參和出參不能有長度,其中關鍵字as可以替換成is,儲存過程中變數宣告在as和begin之間，同時，儲存過程中可以再呼叫其它的儲存過程，如果要保證儲存過程之間的事務處理不受影響，可以定義為自治事務。
     create or replace procedure say_hello(
       v_name in varchar2,
       v_flag number,
       o_ret out number
     )
     as
     begin
       if v_name is null and v_flag is null then --v_name和v_flag都等於null
           o_ret := 10;
       else
           o_ret := 100;
       end if;
     end;

對於入參為null情況下給予預設值
     create or replace procedure say_hello(
       i_name in varchar2,
       i_flag number,
       o_ret out number
     )
     as
       v_name  varchar2(100);
     begin
       if i_name is null then
          v_name := '0';
       else
          v_name := i_name;
       end if;
       insert into phone(..,wname..,) values(..,v_name,..); 
     
     end;
或直接在insert語句中呼叫nvl函式賦預設值
     insert into phone(..,wname..,) values(..,nvl(v_name,' '),..); ----如果將' '寫成'',則insert進來的v_name值還是為''等價於null值

帶一個引數的儲存過程
   輸入引數in，輸入引數不能進行:=賦值，但可以將它賦給as後面定義的變數；
   輸入引數in，可以作為變數進行條件判斷；
   預設不寫就是in；
   儲存過程沒有過載，這個有參的say_hello會替代已經存在的無參say_hello。

     create or replace procedure say_hello(v_name in varchar2)
     as
     begin
        --v_name:='a'; --儲存過程入參v_name不能做為賦值目標
        dbms_output.put_line('hello '||v_name); 
     end;

儲存過程輸入引數作為變數進行條件判斷
     create or replace procedure say_hello(
        i_opFlag in number
     )
     as
        v_name varchar2(100);
     begin
        if i_opFlag = 1 then
    v_name :='0'; 
        else
    v_name :='haha';
        end if; 
        dbms_output.put_line('hello '||v_name); 
     end;


利用儲存過程中定義的變數對入參的空值處理：
     create or replace procedure say_hello(
        i_name in varchar2
     )
     as
        v_name varchar2(100);
     begin
        if i_name is null then
    v_name :='0'; 
        else
    v_name :=i_name;--將入賦值給定義變數
        end if; 
        dbms_output.put_line('hello '||v_name); 
     end;

多個引數的儲存過程
     create or replace procedure say_hello(
        v_first_name in varchar2,
        v_last_name in varchar2)
     as
     begin
        dbms_output.put_line('hello '||v_first_name||'.'||v_last_name);
     end;

out輸出引數，用於利用儲存過程給一個或多個變數賦值，類似於返回值
     create or replace procedure say_hello(
        v_name in varchar2,
        v_content out varchar2
     )
     begin
        v_content:='hello'||v_name;
     end;

     呼叫：
     declare
        v_con varchar2(200);
        v_in varchar2(20):='wang';
     begin
        say_hello(v_in,v_con);
        dbms_output.put_line(v_con);
     end;

in out引數，既賦值又取值
     create or replace procedure say_hello(v_name in out varchar2)
     as
     begin
        v_name:='hi '||v_name;
     end;

     呼叫：
     declare
        v_inout varchar2(20):='wangsu';
     begin
        say_hello(v_inout);
        dbms_output.put_line(v_inout);
     end;

對儲存過程入參賦預設值
     create or replace procedure say_hello(
        v_name varchar2 default 'susu',
        v_content varchar2 default 'hello'
     )
     as
     begin
        dbms_output.put_line(v_name||' '||v_content);
     end;

     呼叫：(用指明形參名的方式呼叫更好)
     begin
        say_hello();
     end;
     或
     begin
        say_hello('cheng');
     end;
     或
     begin
     say_hello(v_name=>'cheng');
    end;

*****************************************
 PLSQL中的function
*****************************************
FUNCTION和PROCEDURE的區別
1、函式有返回值，過程沒有
2、函式呼叫在一個表示式中，過程則是作為pl/sql程式的一個語句
  過程和函式都以編譯後的形式存放在資料庫中，函式可以沒有引數也可以有多個引數並有一個返回值。過程
  有零個或多個引數，沒有返回值。函式和過程都可以通過引數列表接收或返回零個或多個值，函式和過程的
  主要區別不在於返回值，而在於他們的呼叫方式，過程是作為一個獨立執行語句呼叫的，函式以合法的表達
  式的方式呼叫
    create or replace function func(v_name in varchar2)
    return varchar2
    is
    begin
       return(v_name||' hello');
    end;

    呼叫：
    declare
       v_name varchar2(20);
    begin
       v_name:=func('cheng');
       dbms_output.put_line(v_name);
    end;

帶out引數的函式
    create or replace function func(
       v_name in varchar2,
       v_content out varchar2
    )
    return varchar2
    is
    begin
       v_content:=v_name||' hello';
       return v_content;
    end;

    呼叫：
    declare
       v_name varchar2(20);
       v_name1 varchar2(20);
    begin
       v_name1:=func('susu',v_name);--返回v_name值
       dbms_output.put_line(v_name1);--列印func結果
       dbms_output.put_line(v_name);--列印v_name結果
    end;

帶in out 引數的函式
    create or replace function func(
       v_name in out varchar2)
    return varchar2
    is
    begin
       v_name:=v_name||' hello';
       return 'cheng';
    end;

    呼叫：
    declare
       v_inout varchar2(20):='world';
       v_ret varchar2(20);
    begin
       v_ret:=func(v_inout);--返回撥用v_inout值(作為出參)
       dbms_output.put_line(v_ret);--列印func結果    
       dbms_output.put_line(v_inout);--返回v_name結果
    end;

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

相關文章