Oracle游標/異常/過程/函數_ZenDei技術網路在線

1、基礎語法 http://692088846.iteye.com/blog/2017137 （%type、%rowtype、if\if else\if elseif else、while、do..while、游標、異常、函數、過程） 1.1 聲明變數賦值並輸出 set serveroutput o ...

1、基礎語法

http://692088846.iteye.com/blog/2017137 （%type、%rowtype、if\if else\if elseif else、while、do..while、游標、異常、函數、過程）

1.1 聲明變數賦值並輸出

set serveroutput on --設置資料庫輸出，預設為關閉，每次重新打開視窗需要重新設置。 Declare result integer; --聲明變數【變數名變數類型】 begin result:=10+3*4-20+5**2; --給變數賦值【:=】 dbms_output.put_line('運算結果是：'||to_char(result)); end; -------------------------------------------------------------------------------------------------- dbms_output.put_line函數輸出只能是字元串，因此利用to_char函數將數值型結果轉換為字元型。運算的優先次序為NOT、AND和OR。 To_char：將其他類型數據轉換為字元型。 To_date：將其他類型數據轉換為日期型。 To_number：將其他類型數據轉換為數值型。

1.2 %type、%rowtype、select...into

-- %type 變數的類型和數據表中的欄位的數據類型一致 -- %rowtype 變數的類型和數據表中的一行記錄數據類型一致 -- select ... into 變數表中查詢數據並賦值，可以一次性給多個變數賦值
declare v_object CUSTOMER%Rowtype; begin select * into v_object from customer where rownum=1; dbms_output.put_line(v_object.Enterprise_Code ||','||v_object.CreateDate); end;

1.3 只可以增刪改

-- insert，update，delete，select都可以，create table，drop table不行。DPL，DML，和流程式控制制語句可以在pl/sql里用但DDL語句不行，表定義語言不可以在plsql中改變 declare v_name student.name%type:='wang'; --聲明變數並賦值 begin insert into student(id,name,age) values(2,v_name,26); --插入數據 end; -------------------------------------------- declare v_name student.name%type:='hexian'; --更新數據 begin update student set name=v_name where id=1; end; begin update student set name='qinaide' where id=2; end; ------------------------------------------------- --------------

2、 PLSQL流程式控制制

--if判斷---

declare v_b boolean:=true; -- := 是賦值 begin if v_b then dbms_output.put_line('ok'); end if; end;

--if else判斷---

declare v_b boolean:=true; begin if v_b then dbms_output.put_line('ok'); else dbms_output.put_line('false'); end if; end;

--if elsif else判斷--

declare v_name varchar2(20):='cheng'; begin if v_name='0701' then dbms_output.put_line('0701'); elsif v_name='cheng' then dbms_output.put_line('cheng'); else dbms_output.put_line('false'); end if; end;

--loop迴圈，註意推出exit是退出迴圈，而不是推出整個代碼塊

declare v_i binary_integer :=0; begin loop exit when v_i>10; v_i :=v_i+1; dbms_output.put_line('hehe'); end loop; dbms_output.put_line('over'); end;

-- while迴圈

declare v_i binary_integer:=0; begin while v_i<10 loop dbms_output.put_line('hello'||v_i ); v_i:=v_i+1; end loop; dbms_output.put_line('over'); end;

-- for迴圈，註意不需要聲明變數

begin for v_i in 0..10 loop dbms_output.put_line('hello'||v_i); end loop; dbms_output.put_line('over'); end;

3、PLSQL異常處理

1、聲明異常異常名 EXCEPTION； 2、拋出異常 RAISE 異常名 3、處理異常拋出異常後的邏輯代碼不會被繼續執行異常的定義使用 ――――――――――――――――――――――――――――――――――――― begin dbms_output.put_line(1/0); --1/0出現錯誤，拋出異常，輸出error exception when others then dbms_output.put_line('error'); end; ----------------------------------------------------------------------------- declare e_myException exception; --聲明異常 begin dbms_output.put_line('hello'); --raise拋出異常,用此關鍵字，拋出後轉到自定義的e_myException，執行其 --裡面的putline函數後，再跳到end處，結束PL/SQL塊，raise接下麵的2句不會繼續執行。 raise e_myException; 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;

4、 PLSQL游標

4.1 游標定義

備註：下麵提到的游標為靜態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;

4.2 游標的使用

游標的使用，一定別忘了關游標。 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。

4.3 游標實例：

create table BOOK ( ID VARCHAR2(10) not null, BOOKNAME VARCHAR2(10) not null, PRICE VARCHAR2(10) not null, CID VARCHAR2(10) not null ); -- %rowcount是SQL的屬性表示影響了多少條記錄

1、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); --返回完整錯誤信息 o_result_msg := substr(sqlcode, 1, 200); --返回錯誤碼 end;

2、update/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;

3、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;

4、loop方式遍歷游標

declare v_bookname varchar2(100); cursor c_book(i_id number) is select bookname from book where id = i_id; 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 id = i_id; 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;

5、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 id = i_id; 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;

6、 for迴圈遍歷

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

5、Oracle存儲過程

　　存儲過程---就像資料庫中運行方法（函數），和C#方法一樣，由存儲過程名/存儲過程參數組成，可以有返回結果。

　　優點：

　　　　-- 執行速度更快（在資料庫中保存的存儲過程語句都是編譯過的）

　　　　-- 允許模塊化程式設計（類似方法的復用）

　　　　-- 提高系統安全性（防止sql註入）

　　　　-- 減少網路流通量（只要傳輸存儲過程的名稱）

　　一般，以sp_、xp_開頭的都是系統存儲過程,用戶自定義存儲過程usp_

5.1.1 存儲過程案例

存儲過程入參，不論類型，預設情況下值都為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); --定義變數在as和begin之間，需要長度 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值

5.1.2 帶一個參數的存儲過程

輸入參數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;

5.1.3 多個參數的存儲過程

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;

5.1.4 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;

6、 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;