Oracle存儲過程與函數的詳細使用教程
目錄
- 一、存儲過程
- 1、存儲過程的創建
- 2、存儲過程的調用及刪除
- 3、存儲過程的使用
- 4、存儲過程的查詢
- 二、函數
- 1、函數的創建
- 2、函數的調用與刪除
- 3、函數的使用
- 4、函數的查詢
- 補充：存儲過程與存儲函數的區別和聯系
- 總結
一、存儲過程
存儲過程是一種命名的PL/SQL數據塊，存儲在Oracle數據庫中，可以被用戶調用。存儲過程可以包含參數，也可以沒有參數，它一般沒有返回值。存儲過程是事先編譯好的代碼，再次調用的時候不需再次編譯，因此程序的運行效率非常高。
1、存儲過程的創建
語法如下
create [or replace] 過程名
[<參數1> inioutin out <參數類型>[默認值|:=初始值]]
[,<參數2> inioutin out <參數類型>[默認值|:=初始值],...]
isias
[局部變量聲明]
begin
程序語句序列
[exception]
異常處理語句序列
end 過程名
參數說明如下：
1、or replace 可選參數，表示如果數據庫中已經存在要創建的過程，則先把原先過程刪除，再重新建立過程，或者說覆蓋原先的過程。
2、如果過程中存在參數，則需要在參數后面用“inioutin out”關鍵字。如果是輸入參數，則參數后面用“in”關鍵字，表示接受外部過程傳遞來的值；如果是輸出參數，則參數后面用“out”關鍵字，表示此參數將在過程中被復制，并傳遞給過程體外；如果是“in out” 關鍵字則表示該參數既具有輸入參數特性，又具有輸出參數的特性。默認是in參數，即如果不寫就默認為in參數。
3、參數類型不能指定長度，只需要給出類型即可。
4、局部變量聲明中所定義的變量只在該過程中有效。
5、局部變量聲明，程序語句序列和異常處理語句序列定義和使用同上一章PL/SQL塊。
2、存儲過程的調用及刪除
存儲過程創建后，以編譯的形式存在于oracle數據庫中，可以在sql plus中或者pl/sql塊中調用。
1、在sql plus中調用存儲過程
語法如下：
execute 過程名 [參數序列]
其中execute可以簡寫成exec。
2、在pl/sql塊中調用存儲過程
直接把過程名寫到其他pl/sql塊中即可調用，此時不需使用execute命令。
3、存儲過程的刪除
存儲過程的刪除和表的刪除類似，基本語法如下所示。
drop procdure 過程名
3、存儲過程的使用
1、不帶參數的存儲過程
1、創建一個存儲過程，向student表中插入一條記錄
create or replace procedure pro_stu is begin insert into student<id,name,class> values<10,"張三","五班">; commit; dbms_output.put_line<"插入一條新紀錄！！！">; end pro_stu;
上面存儲過程已經成功創建，但是并沒有執行，執行語句如下。
exec pro_stu;
上面是exec命令執行，我們也可以在PL/SQL塊中直接調用，語法如下。
begin pro_stu; end;
2、帶in參數的存儲過程
使用in參數可以向存儲過程中的程序單元輸入數據，在調用的時候提供參數值，被存儲過程讀取。這種模式是默認的參數模式。下面看一個范例。
2、創建一個存儲過程，接收來自外部的數值，在存儲過程中判斷該數值是否大于零并顯示。
create or replace procedure pro_decide< var_num in number > is begin if var_num>=0 then dbms_output.put_line<"傳遞進來的參數大于等于0">; else dbms_output.put_line<"傳遞進來的參數小于0">; end if; end pro_decide;
執行存儲過程
exec pro_decide<3>;
結果顯示：
傳遞進來的參數大于等于0
3、輸入一個編號，查詢student表中是否有這個編號，如果有則顯示對應學生的姓名，如果沒有則提示沒有對應的學生。
create or replace procedure pro_show< var_stuid in student.id%type --定義in參數 > is var_name student.name%type; --定義存儲過程內部變量 no_result exception; begin select name into var_name from student where id = var_stuid; --取值 if sql%found then dbms_output.put_line<"所查詢的學生姓名是：" || var_name>; --顯示 end if; when no_data_found then dbms_output.put_line<"沒有對應此編號的學生">; --錯誤處理 end pro_show;
執行存儲過程。
exec pro_show<10>
4、創建一個存儲過程，向數據表student中插入一條記錄。
create or replace procedure pro_add< var_id in number, var_name in varchar2, var_class in varchar2> is begin insert into student values<var_id,var_name,var_class>; --插入記錄 commit; dbms_output.put_line<"插入一條新紀錄！！！">; end pro_add;
執行存儲過程
exec pro_add<10,"張三","五班">;
5、輸入一個編號，查詢student表中是否有這個編號，如果有則返回對應學生的姓名，如果沒有則提示沒有對應的學生。
上面我們使用in是顯示學生的姓名，現在我們要返回學生的姓名就要使用out，語法如下
create or replace procedure pro_show1< var_id in student.id%type, --定義in參數 var_name out student.name%type --定義out參數 > is no_result exception; begin select name init var_name from student where id = var_id; --取值 exception when no_data_found then dbms_output.put_line<"沒有對應此編號的學生">; --錯誤處理 end pro_show1;
調用含有out參數的存儲過程需要提前聲明一個相應類型的變量，然后用來接收。
variable var_name varchar2<10>; exec pro_show1<10,:var_name>;
在調用的時候，使用“：”后面緊跟變量名。
4、存儲過程的查詢
存儲過程的查詢需要使用到數據字典user_source,語法如下
select distinct name from user_source where type=upper("procedure");
上面這個語句查詢當前用戶下所有的存儲過程的名字。
此外，我們還可以查詢存儲過程的內容，查詢語句如下所示。
select text from user_source where name = upper("pro_aa");
二、函數
上面的存儲過程有輸入參數和輸出參數，但是沒有返回值，函數和存儲過程非常類似，也是可以存儲在oracle數據庫中的PL/SQL代碼塊，但是有返回值，可以把經常使用的功能定義為一個函數，就像系統自帶的函數（例如大小寫轉換，求絕對值等函數）一樣使用。
1、函數的創建
函數的創建的基本語法格式如下所示。
create or replace function 函數名
[<參數1> inioutin out <參數類型>[默認值|:=初始值]]
return 返回數據類型
isias
[局部變量聲明]
begin
程序語句序列
[exception]
異常處理語句序列
end 過程名
其中的參數說明如下。
1、or replace 可選參數，表示如果數據庫中已經存在要創建的函數，則先把原先函數刪除，再重新建立函數，或者說覆蓋原先的函數。
2、如果過程中存在參數，則需要在參數后面用“inioutin out”關鍵字。如果是輸入參數，則參數后面用“in”關鍵字，表示接受外部過程傳遞來的值；如果是輸出參數，則參數后面用“out”關鍵字，表示此參數將在過程中被復制，并傳遞給過程體外；如果是“in out” 關鍵字則表示該參數既具有輸入參數特性，又具有輸出參數的特性。默認是in參數，即如果不寫就默認為in參數。
3、參數類型不能指定長度，只需要給出類型即可。
4、函數的返回值類型是必選項。
5、局部變量聲明中所定義的變量只在該函數中有效。
6、局部變量聲明、程序語句序列和異常處理語句序列定義以及使用PL/SQL塊。
在函數的主程序中，必須使用return語句返回最終的函數值，并且返回值的數據類型要和聲明的時候說明的類型一樣。
## 2、隱式游標的創建與使用 >和顯示游標不同，隱式游標是系統自動創建的，用于處理DML語句（例如insert、update、delete等指令）的執行結果或者select查詢返回的單行數據，這時隱式游標是指向緩沖區的指針。使用時不需要進行聲明、打開和關閉，因此不需要open、fetch、close這樣的操作指令。隱式游標也有前述介紹的4種屬性，使用時需要在屬性前面加上隱式游標的默認名稱SQL，因此隱式游標也叫SQL游標。 ### 1、將student表中張三的學生年齡增加10歲，然后使用隱式游標的%rowcount屬性輸出涉及的員工數量 ```go begin update student set age=age+10 --年齡增加10 where name = "張三"; if sql%notfound then --是否有符合條件的記錄 dbms_output.put_line<"沒有符合條件的學生">; else dbms_output.put_line<"符合條件的學生數量為：" || sql%rowcount>; end if; end;
2、函數的調用與刪除
函數的調用基本上與系統內置函數的調用方法相同。可以直接在SQL plus中使用，也可以在存儲過程中使用。
函數的刪除與存儲過程的刪除類似，語法如下：
drop function 函數名
3、函數的使用
1、創建一個函數，如果是偶數則計算其平方，如果是奇數則計算其平方根
create or replace function fun_cal <var_num number> --聲明函數參數 return number --聲明函數返回類型 is i int:=2; begin if mod<var_num,2>=0 then --判斷奇偶性 return power<var_num,i>; --返回平方 flse return round<sqrt<var_num>,2>; --返回平方根 end if; end fun_cal;
4、函數的查詢
在實際使用中經常會需要查詢數據庫中已有的函數或者某一個函數的內容，使用的方法和存儲過程類似，也需要使用到數據字典user_source，使用的查詢語句如下所示。
select distinct name from user_source where type=upper("function");
上面這個語句查詢當前用戶下所有的用戶定義的函數名字。
此外，我們還可以查詢函數的內容，查詢語句如下所示。
select text from user_source where name=upper("fun_cal") and type=upper("function")
補充：存儲過程與存儲函數的區別和聯系
相同點：1.創建語法結構相似，都可以攜帶多個傳入參數和傳出參數。
2.都是一次編譯，多次執行。
不同點：1.存儲過程定義關鍵字用procedure，函數定義用function。
2.存儲過程中不能用return返回值，但函數中可以，而且函數中必須有return子句。
3.執行方式略有不同，存儲過程的執行方式有兩種（1.使用execute2.使用begin和end），函數除了存儲過程的兩種方式外，還可以當做表達式使用，例如放在select中（select f1() form dual;）。
總結：如果只有一個返回值，用存儲函數，否則，一般用存儲過程。
總結
這里的相關內容還沒有整理完畢，文章后面持續更新，建議收藏。
文章中涉及到的命令大家一定要像我一樣每個都敲幾遍，只有在敲的過程中才能發現自己對命令是否真正的掌握了。
到此這篇關于Oracle存儲過程與函數的詳細使用的文章就介紹到這了,更多相關Oracle存儲過程與函數內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！
網公網安備