一. 存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)的定義
定義：存儲(chǔ)在數(shù)據(jù)庫(kù)中匣屡，供所有用戶程序調(diào)用的子程序叫做存儲(chǔ)過(guò)程/存儲(chǔ)函數(shù)屯仗。復(fù)雜點(diǎn)的解釋：存儲(chǔ)過(guò)程(Stored Procedure)佛南，就是一組用于完成特定數(shù)據(jù)庫(kù)功能的SQL 語(yǔ)句集晃酒，該SQL語(yǔ)句集經(jīng)過(guò)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)系統(tǒng)中故源。在使用時(shí)候，用戶通過(guò)指定已經(jīng)定義的存儲(chǔ)過(guò)程名字并給出相應(yīng)的存儲(chǔ)過(guò)程參數(shù)來(lái)調(diào)用并執(zhí)行它账千，從而完成一個(gè)或一系列的數(shù)據(jù)庫(kù)操作侥蒙。
區(qū)別：是否可以通過(guò)return返回函數(shù)值。
存儲(chǔ)函數(shù)可以通過(guò)return返回函數(shù)值匀奏；而存儲(chǔ)過(guò)程不可以鞭衩。注意：由于通過(guò)out參數(shù)，存儲(chǔ)過(guò)程也可以返回函數(shù)值娃善，所以存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)已經(jīng)沒(méi)有太大的區(qū)別了论衍。而存儲(chǔ)函數(shù)仍然存在，是由于oracle不斷升級(jí)聚磺，需要實(shí)現(xiàn)向下兼容坯台，所以存儲(chǔ)函數(shù)就一直存留著。
二. 存儲(chǔ)過(guò)程
1. 創(chuàng)建和使用存儲(chǔ)過(guò)程
用create procedure命令建立存儲(chǔ)過(guò)程瘫寝，語(yǔ)法如下：
create [or replace] procedure 過(guò)程名(參數(shù)列表)
as -- as不可以省略
PLSQL子程序體;
注意事項(xiàng)：
1> 存儲(chǔ)過(guò)程或者存儲(chǔ)函數(shù)蜒蕾，只能創(chuàng)建或者替換。
2> 參數(shù)可以帶也可以不帶焕阿。
3> as相當(dāng)于PLSQL語(yǔ)句中的declare咪啡，用來(lái)聲明變量、游標(biāo)等暮屡，但是不可以省略撤摸。
2. 入門案例:
（1）不帶參數(shù)的存儲(chǔ)過(guò)程:不用帶括號(hào)
create or replace procedure sayHello
as
begin
dbms_output.put_line('HelloWorld');
end;
/
調(diào)用方式：
- <1> 使用execute：
exec是sqlplus命令，只能在sqlplus中使用栽惶，使用時(shí)愁溜，exec可以直接跟過(guò)程名（可以省略括號(hào)）；
控制臺(tái)執(zhí)行示例:
SQL> set serveroutput on;
SQL> exec sayHello;
- <2> 使用call：
使用call時(shí)外厂，要帶上括號(hào);call為SQL命令使用時(shí)，對(duì)場(chǎng)景沒(méi)有限制代承。
控制臺(tái)執(zhí)行示例:
SQL> set serveroutput on;
SQL> call sayHello();
- <3> 使用PLSQL語(yǔ)句調(diào)用：
控制臺(tái)執(zhí)行示例:
SQL> set serveroutput on;
SQL> begin
sayHello;
end;
/
（2）帶參數(shù)的存儲(chǔ)過(guò)程:
- 給指定的員工漲100元工資,并且打印漲前和漲后的薪水:
create or replace procedure addSal(pempno in emp.empno%type)
as
pename emp.ename%type;
beforesal emp.sal%type;
aftersal emp.sal%type;
begin
select ename,sal into pename,beforesal from emp where empno=pempno;
aftersal:=beforesal+100;
update emp set sal=aftersal where empno=pempno;
dbms_output.put_line('姓名: '||pename||' 漲前工資:'||beforesal||'漲后工資:'||aftersal);
end;
/
** 注意事項(xiàng)：**
- <1> 要說(shuō)明汁蝶，參數(shù)是輸入?yún)?shù)(in)還是輸出參數(shù)(out)；
- <2> 為保證調(diào)用多個(gè)存儲(chǔ)過(guò)程中處在同一個(gè)事務(wù)中论悴，所以一般不在存儲(chǔ)過(guò)程或者存儲(chǔ)函數(shù)中掖棉，commit或rollback；
3. 調(diào)試存儲(chǔ)過(guò)程:
** 注意事項(xiàng)：**
(1) oracle數(shù)據(jù)庫(kù)和PLSQL工具都是放在同一個(gè)機(jī)器上膀估；
(2) 在開(kāi)啟調(diào)試時(shí)幔亥，可能會(huì)報(bào)出權(quán)限不夠的錯(cuò)誤信息,通過(guò)下面方式進(jìn)行授權(quán)：
三. 存儲(chǔ)函數(shù)
1. 存儲(chǔ)函數(shù):
- 函數(shù)(Function)為一命名的存儲(chǔ)程序,可帶參數(shù),并返回一計(jì)算值.
- 函數(shù)和過(guò)程的結(jié)構(gòu)類似,但必須有一個(gè)return子句,用于返回函數(shù)值.
2. 創(chuàng)建存儲(chǔ)函數(shù)的語(yǔ)法:
create [or replace] function 函數(shù)名(參數(shù)列表)
return 函數(shù)值類型
as
PLSQL子程序體;
- 注意事項(xiàng):
- (1) 與存儲(chǔ)過(guò)程注意事項(xiàng)類似,不同的是,必須有個(gè)返回值;
- (2) 參數(shù)列表可以有,也可以沒(méi)有.當(dāng)沒(méi)有時(shí),函數(shù)名后面不要帶括號(hào).
create or replace function queryempannal(pempno in number)
return number
as
psal emp.sal%type;
pcomm emp.comm%type;
begin
select sal,comm into psal,pcomm from emp where empno=pempno;
return psal*12+nvl(pcomm,0);
end;
四. in和out參數(shù)
1.概述
(1) 一般來(lái)講,存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)的區(qū)別在于存儲(chǔ)函數(shù)可以有一個(gè)返回值;而存儲(chǔ)過(guò)程沒(méi)有返回值.
-
(2) 過(guò)程和函數(shù)都可以通過(guò)out指定一個(gè)或多個(gè)輸出參數(shù).我們可以利用out參數(shù),在過(guò)程和函數(shù)中實(shí)現(xiàn)返回多個(gè)值.
- a. 存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)都可以有out參數(shù);
- b. 存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)都可以有多個(gè)out參數(shù);
- c. 存儲(chǔ)過(guò)程可以通過(guò)out參數(shù)來(lái)實(shí)現(xiàn)返回值;
-
(3) 什么時(shí)候用存儲(chǔ)過(guò)程/存儲(chǔ)函數(shù)?
- 原則:如果只有一個(gè)返回值,用存儲(chǔ)函數(shù);否則,就用存儲(chǔ)過(guò)程.
create or replace procedure queryempinform(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2 )
as
begin
select ename,sal,job into pename,psal,pjob from emp where empno=eno;
end;
/
