--==================
-- PL/SQL --> 函數(shù)
--==================
?
??? 函數(shù)通常用于返回特定的數(shù)據(jù)。其實(shí)質(zhì)是一個(gè)有名字的 PL / SQL 塊，作為一個(gè) schema 對(duì)象存儲(chǔ)于數(shù)據(jù)庫(kù)，可以被反復(fù)執(zhí)行。函數(shù)通常被作為
一個(gè)表達(dá)式來(lái)調(diào)用或存儲(chǔ)過(guò)程的一個(gè)參數(shù)，具有返回值。
?
一、建立函數(shù)的語(yǔ)法
??? CREATE [ OR REPLACE ] FUNCTION function_name
??????? ( argument1 [mode1] datatype1 ,
??????? ? argument2 [mode2] datetype2 ,
??????? ? ...)
??? RETURN datatype
??? IS | AS
??????? [local_variable_declarations;...]
??? BEGIN
??????? --actions;
??????? RETURN expression ;
??? END [function_name] ;
???
??? 建立函數(shù)的幾點(diǎn)注意事項(xiàng)
??????? 1. 指定參數(shù)數(shù)據(jù)類(lèi)型時(shí) (argument) ，不能指定其長(zhǎng)度
??????? 2. 函數(shù)頭部必須指定 return 子句，函數(shù)體內(nèi)至少要包含一條 return 語(yǔ)句
??????? 3. 可以指定 in 參數(shù)，也可以指定 out 參數(shù)，以及 in out 參數(shù)
??????? 4. 可以為參數(shù)指定缺省值。指定缺省值時(shí)使用 default 關(guān)鍵字。如 arg1 varchar2 default 'SCOTT'
?
二、使用函數(shù)的優(yōu)點(diǎn)
??? 1. 增加了代碼的靈活性，可以完成一些較為復(fù)雜的任務(wù)，以及僅僅通過(guò) SQL 無(wú)法完成的任務(wù)
??? 2. 可以直接將函數(shù)使用到 where 子句中來(lái)過(guò)濾數(shù)據(jù)
??? 3. 可以作為存儲(chǔ)過(guò)程的參數(shù)使用，是存儲(chǔ)過(guò)程的一種補(bǔ)充
???
三、建立函數(shù)
??? 1. 建立不帶參數(shù)的函數(shù)
??????? scott@ORCL > create or replace function get_user
??????? ? 2 ? return varchar2
??????? ? 3 ? is
??????? ? 4 ??? v_user varchar2 ( 20 );
??????? ? 5 ? begin
??????? ? 6 ??? select username into v_user from user_users ;
??????? ? 7 ??? return v_user ;
??????? ? 8 ? end ;
??????? ? 9 ? /
?
??????? Function created .
?
??????? -- 使用全局變量接收函數(shù)的返回值
??????????? scott@ORCL > var v1 varchar2 ( 20 ) ?
??????????? scott@ORCL > exec : v1 := get_user
?
??????????? PL / SQL procedure successfully completed .
?
??????????? scott@ORCL > print v1 ;
?
??????????? V1
??????????? --------------------------------
??????????? SCOTT
???????
??????? -- 使用本地變量接收函數(shù)的返回值
??????????? scott@ORCL > set serveroutput on ;
??????????? scott@ORCL > declare user_name varchar2 ( 20 );
??????????? ? 2 ? begin
??????????? ? 3 ? user_name := get_user ();
??????????? ? 4 ? dbms_output . put_line ( 'Current user: ' || user_name );
??????????? ? 5 ? end ;
??????????? ? 6 ? /
??????????? Current user : SCOTT
?
??????????? PL / SQL procedure successfully completed . ???
???????????
??????? -- 在 SQL 語(yǔ)句中直接調(diào)用函數(shù) ??
??????????? scott@ORCL > select get_user from dual ;
?
??????????? GET_USER
??????????? ---------------------
??????????? SCOTT ??
?
??????? -- 使用 dbms_output 調(diào)用函數(shù) ( 此調(diào)用作為存儲(chǔ)過(guò)程的一個(gè)參數(shù)來(lái)進(jìn)行調(diào)用 ) ??
??????????? scott@ORCL > set serveroutput on ;
??????????? scott@ORCL > exec dbms_output . put_line ( 'Current user: ' || get_user );
??????????? Current user : SCOTT ????
???????????
??? 2. 建立帶有 in 參數(shù)的函數(shù)
??????? scott@ORCL > create or replace function raise_sal ( name in varchar2 ) ??? -- 注意此處定義時(shí)參數(shù)并為指定類(lèi)型的長(zhǎng)度
??????? ? 2 ? return number
??????? ? 3 ? as
??????? ? 4 ??? v_sal emp . sal % type ;
??????? ? 5 ? begin
??????? ? 6 ??? select sal * 1.2 into v_sal from emp
??????? ? 7 ??? where upper ( ename )= upper ( name );
??????? ? 8 ??? return v_sal ;
??????? ? 9 ? exception
??????? ? 10 ??? when no_data_found then
??????? ? 11 ??? raise_application_error (- 20000 , 'Current Employee is not exists' );
??????? ? 12 ? end ;
??????? ? 13 ? /
?
??????? Function created . ??
?
??????? scott@ORCL > select sal , raise_sal ( 'SCOTT' ) from emp where ename = 'SCOTT' ;
?
??????????? ?? SAL RAISE_SAL ( 'SCOTT' )
??????? ---------- ------------------
??????????? ? 3100 ?????????????? 3720 ??
?
??????? scott@ORCL > select raise_sal ( 'Robinson' ) from dual ;
??????? select raise_sal ( 'Robinson' ) from dual
??????????? ?? *
??????? ERROR at line 1 :
??????? ORA - 20000 : Current Employee is not exists
??????? ORA - 06512 : at "SCOTT.GET_SAL" , line 11 ?????
?
??? 3. 建立帶有 out 參數(shù)的函數(shù)
??????? scott@ORCL > create or replace function get_info
??????? ? 2 ? ( name varchar2 , title out varchar2 )
??????? ? 3 ? return varchar2
??????? ? 4 ? as
??????? ? 5 ??? deptname dept . dname % type ;
??????? ? 6 ? begin
??????? ? 7 ??? select e . job , d . dname into title , deptname
??????? ? 8 ??? from emp e inner join dept d
??????? ? 9 ????? on e . deptno = d . deptno
??????? ? 10 ??? where upper ( e . ename )= upper ( name );
??????? ? 11 ??? return deptname ;
??????? ? 12 ? exception
??????? ? 13 ??? when no_data_found then
??????? ? 14 ????? raise_application_error (- 20000 , 'Current Employee is not exists' );
??????? ? 15 ? end ;
??????? ? 16 ? /
?
??????? Function created .
?
??????? 注意對(duì)于使用 out 參數(shù)的函數(shù)，不能使用 SQL 語(yǔ)句來(lái)調(diào)用。而必須定義變量接收 out 參數(shù)和函數(shù)的返回值。
??????? 調(diào)用如下
??????? scott@ORCL > var job varchar2 ( 20 );
??????? scott@ORCL > var dname varchar2 ( 20 );
??????? scott@ORCL > exec : dname := get_info ( 'scott' ,: job );
?
??????? PL / SQL procedure successfully completed .
?
??????? scott@ORCL > print dname job ;
?
??????? DNAME
??????? --------------------------------
??????? RESEARCH
?
?
??????? JOB
??????? --------------------------------
??????? ANALYST
?
??? 3. 建立帶有 in out 參數(shù)的函數(shù)
??????? scott@ORCL > create or replace function comp
??????? ? 2 ? ( num1 number , num2 in out number )
??????? ? 3 ? return number
??????? ? 4 ? as
??????? ? 5 ??? v_result number ( 6 );
??????? ? 6 ??? v_remainder number ;
??????? ? 7 ? begin
??????? ? 8 ??? v_result := num1 * num2 ;
??????? ? 9 ??? v_remainder := mod ( num1 , num2 );
??????? ? 10 ??? num2 := v_remainder ;
??????? ? 11 ??? return v_result ;
??????? ? 12 ? exception
??????? ? 13 ??? when zero_divide then
??????? ? 14 ????? raise_application_error (- 20000 , 'Divison by zero' );
??????? ? 15 ? end ;
??????? ? 16 ? /
?
??????? Function created .
?
??????? scott@ORCL > var result1 number ;
??????? scott@ORCL > var result2 number ;
??????? scott@ORCL > exec :result2 := 10
?
??????? PL / SQL procedure successfully completed .
?
??????? scott@ORCL > exec :result1 := comp ( 16 , :result2 );
?
??????? PL / SQL procedure successfully completed .
?
??????? scott@ORCL > print result1 result2 ;
?
??????? ?? RESULT1
??????? ----------
??????????? ?? 160
?
?
??????? ?? RESULT2
??????? ----------
??????????????? ? 6
?
四、函數(shù)的調(diào)用及限制
??? 1. 函數(shù)的調(diào)用 ( 其具體調(diào)用方法參照上面的演示 )
??????? a . 使用全局變量接收函數(shù)的返回值
??????? b . 使用本地變量接受函數(shù)的返回值
??????? c . 在 SQL 語(yǔ)句中直接調(diào)用函數(shù)
??????? d . 使用 dbms_output 調(diào)用函數(shù)
??????? 注：函數(shù)在調(diào)用的時(shí)候需要按位置指定參數(shù)，沒(méi)有存儲(chǔ)過(guò)程參數(shù)傳遞靈活
??????????? 必須具有 execute 函數(shù)的權(quán)限
???
??? 2. 函數(shù)在 SQL 中調(diào)用的主要場(chǎng)合
??????? 由于函數(shù)必須要返回?cái)?shù)據(jù)，因此只能作為表達(dá)式的一部分調(diào)用。此外函數(shù)可以在 SQL 語(yǔ)句的以下部分調(diào)用
??????? a. select 命令的選擇列表或子查詢(xún)中
??????? b. 條件表達(dá)式 where, having 子句中
??????? c. connect by , start with ,order by 以及 group by 子句中
??????? d. insert 命令的 values 子句中
??????? f. update 命令的 set 子句中
???????
??? 3. 函數(shù)在 SQL 中調(diào)用的限制
??????? a . SQL 語(yǔ)句中只能調(diào)用存儲(chǔ)在服務(wù)器端的函數(shù)，而不能調(diào)用存儲(chǔ)于客戶(hù)端的函數(shù)
??????? b. SQL 語(yǔ)句中調(diào)用的函數(shù)只能帶有輸入?yún)?shù) IN ，而不能帶有輸出參數(shù) OUT 以及輸入輸出參數(shù) IN OUT
??????? c. SQL 語(yǔ)句中調(diào)用的函數(shù)只能使用 SQL 支持的標(biāo)準(zhǔn)數(shù)據(jù)類(lèi)型，不能使用 PL/SQL 特有的類(lèi)型，如 boolean,table,record 等
??????? d. SQL 語(yǔ)句中調(diào)用的函數(shù)不能包含 insert ,update 和 delete 語(yǔ)句
???????
??????? 下面演示 SQL 調(diào)用時(shí)不能完整 DML 操作示例
?
??????? -- 創(chuàng)建一張表 tb_emp
??????????? scott@ORCL > create table tb_emp as select * from emp ;
?
??????? -- 創(chuàng)建一個(gè)函數(shù)，用于刪除 tb_emp 表中指定的 empno 號(hào)的雇員信息，并返回其薪資
??????????? scott@ORCL > create or replace function delete_oper ( no number )
??????????? ? 2 ? return number
??????????? ? 3 ? as
??????????? ? 4 ??? v_sal emp . sal % type ;
??????????? ? 5 ??? begin
??????????? ? 6 ??? select sal into v_sal from tb_emp where empno = no ;
??????????? ? 7 ??? delete from tb_emp where empno = no ;
??????????? ? 8 ??? return v_sal ;
??????????? ? 9 ??? end ;
??????????? ? 10 ? /
?
??????????? Function created .
???????
??????? -- 使用 SQL 語(yǔ)句調(diào)用時(shí)，收到了錯(cuò)誤信息，在內(nèi)部查詢(xún)內(nèi)不能完成 DML 操作
??????????? scott@ORCL > select delete_oper ( 7788 ) from dual ;
??????????? select delete_oper ( 7788 ) from dual
??????????????? ?? *
??????????? ERROR at line 1 :
??????????? ORA - 14551 : cannot perform a DML operation inside a query
??????????? ORA - 06512 : at "SCOTT.DELETE_OPER" , line 7 ??????
???????
??????? -- 使用 exec 執(zhí)行時(shí)函數(shù)被成功執(zhí)行
??????? ??? scott@ORCL > var v_no number ;
??????????? scott@ORCL > exec : v_no := delete_oper ( 7788 );
?
??????????? PL / SQL procedure successfully completed .
?
??????????? scott@ORCL > print v_no ;
?
??????????????? ? V_NO
??????????? ----------
??????????????? ? 3100
?
??????????? scott@ORCL > select * from tb_emp where empno = 7788 ;
?
??????????? no rows selected ???
???????????
??????? -- 下面的演示表明，不能使用 DML 語(yǔ)句來(lái)調(diào)用函數(shù)
??????????? scott@ORCL > update emp set sal = raise_sal ( 'SCOTT' ) where ename = 'SCOTT' ;
??????????? update emp set sal = raise_sal ( 'SCOTT' ) where ename = 'SCOTT'
??????????????????????????? ?? *
??????????? ERROR at line 1 :
??????????? ORA - 04091 : table SCOTT . EMP is mutating , trigger / function may not see it
??????????? ORA - 06512 : at "SCOTT.RAISE_SAL" , line 6 ????
???????????
五、函數(shù)的管理 ?
??? 函數(shù)使用了與存儲(chǔ)過(guò)程相關(guān)的視圖，可以從系統(tǒng)視圖中獲得函數(shù)的相關(guān)信息
??????? DBA_OBJECTS
??????? DBA_SOURCE
??????? USER_OBJECTS
??????? USER_SOURCE
?
??? -- 查看函數(shù)的源碼
??????? scott@ORCL > select text from user_source where name = 'DELETE_OPER' order by line ;
?
??????? TEXT
??????? ------------------------------------------------------------
??????? function delete_oper ( no number )
??????? ? return number
??????? ? as
??????? ? v_sal emp . sal % type ;
??????? ? begin
??????? ? select sal into v_sal from tb_emp where empno = no ;
??????? ? delete from tb_emp where empno = no ;
??????? ? commit ;
??????? ?? return v_sal ;
??????? ? end ;
?
??? -- 查看函數(shù)的參數(shù)信息
??????? scott@ORCL > desc delete_oper ;
??????? FUNCTION delete_oper RETURNS NUMBER
??????? ? Argument Name ????????????????? Type ??????????????????? In/ Out Default ?
??????? ? ------------------------------ ----------------------- ------ --------
??????? ? NO ???????????????????????????? NUMBER ????????????????? IN ?
?
???????
六、函數(shù)與存儲(chǔ)過(guò)程的差異
??? 存儲(chǔ)過(guò)程 ??? ????????????????????????? ? ???????????? 函數(shù)
??? ---------------------------------- ?????????????? ? -------------------------------
??? 不能被作為表達(dá)式調(diào)用 ??? ??????????????????????????? 只能作為表達(dá)式被調(diào)用
??? 聲明頭部關(guān)鍵字為 procedure ??????????????????????? 聲明頭部關(guān)鍵字為 function
??? 聲明頭部不包含 return 關(guān)鍵字來(lái)描述返回類(lèi)型 ???????? 頭部必須包含 return 關(guān)鍵字 , 且 PL/SQL 塊中至少包含一個(gè)有效的 return 語(yǔ)句
??? 可以通過(guò) out,in out 返回零個(gè)或多個(gè)值 ?????????????? 通過(guò) return 語(yǔ)句返回一個(gè)與頭部聲明中類(lèi)型一致的值 , 也可使用 in,in out 返回值
??? SQL 語(yǔ)句中不可調(diào)用存儲(chǔ)過(guò)程 ?????????????????????? ? SQL 語(yǔ)句可以調(diào)用函數(shù)
??? 多用于數(shù)據(jù)庫(kù)中完成特定的操作 , 如刪除 , 更新 , 插入等 DML 操作 ???? 多用于特定的數(shù)據(jù)如選擇等
???
七、更多參考
? ??? ?
有關(guān) SQL 請(qǐng)參考
??????? SQL 基礎(chǔ)--> 子查詢(xún)
??????? SQL 基礎(chǔ)--> 多表查詢(xún)
SQL 基礎(chǔ)--> ROLLUP 與CUBE 運(yùn)算符實(shí)現(xiàn)數(shù)據(jù)匯總
SQL 基礎(chǔ)--> 層次化查詢(xún)(START BY ... CONNECT BY PRIOR)
?
??? 有關(guān) PL/SQL 請(qǐng)參考
??????? PL/SQL --> 語(yǔ)言基礎(chǔ)
PL/SQL --> 隱式游標(biāo)(SQL%FOUND)
?
???
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
