--==================
-- PL/SQL --> 函數
--==================
?
??? 函數通常用于返回特定的數據。其實質是一個有名字的 PL / SQL 塊，作為一個 schema 對象存儲于數據庫，可以被反復執行。函數通常被作為
一個表達式來調用或存儲過程的一個參數，具有返回值。
?
一、建立函數的語法
??? 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] ;
???
??? 建立函數的幾點注意事項
??????? 1. 指定參數數據類型時 (argument) ，不能指定其長度
??????? 2. 函數頭部必須指定 return 子句，函數體內至少要包含一條 return 語句
??????? 3. 可以指定 in 參數，也可以指定 out 參數，以及 in out 參數
??????? 4. 可以為參數指定缺省值。指定缺省值時使用 default 關鍵字。如 arg1 varchar2 default 'SCOTT'
?
二、使用函數的優點
??? 1. 增加了代碼的靈活性，可以完成一些較為復雜的任務，以及僅僅通過 SQL 無法完成的任務
??? 2. 可以直接將函數使用到 where 子句中來過濾數據
??? 3. 可以作為存儲過程的參數使用，是存儲過程的一種補充
???
三、建立函數
??? 1. 建立不帶參數的函數
??????? 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 .
?
??????? -- 使用全局變量接收函數的返回值
??????????? scott@ORCL > var v1 varchar2 ( 20 ) ?
??????????? scott@ORCL > exec : v1 := get_user
?
??????????? PL / SQL procedure successfully completed .
?
??????????? scott@ORCL > print v1 ;
?
??????????? V1
??????????? --------------------------------
??????????? SCOTT
???????
??????? -- 使用本地變量接收函數的返回值
??????????? 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 語句中直接調用函數 ??
??????????? scott@ORCL > select get_user from dual ;
?
??????????? GET_USER
??????????? ---------------------
??????????? SCOTT ??
?
??????? -- 使用 dbms_output 調用函數 ( 此調用作為存儲過程的一個參數來進行調用 ) ??
??????????? scott@ORCL > set serveroutput on ;
??????????? scott@ORCL > exec dbms_output . put_line ( 'Current user: ' || get_user );
??????????? Current user : SCOTT ????
???????????
??? 2. 建立帶有 in 參數的函數
??????? scott@ORCL > create or replace function raise_sal ( name in varchar2 ) ??? -- 注意此處定義時參數并為指定類型的長度
??????? ? 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 參數的函數
??????? 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 .
?
??????? 注意對于使用 out 參數的函數，不能使用 SQL 語句來調用。而必須定義變量接收 out 參數和函數的返回值。
??????? 調用如下
??????? 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 參數的函數
??????? 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
?
四、函數的調用及限制
??? 1. 函數的調用 ( 其具體調用方法參照上面的演示 )
??????? a . 使用全局變量接收函數的返回值
??????? b . 使用本地變量接受函數的返回值
??????? c . 在 SQL 語句中直接調用函數
??????? d . 使用 dbms_output 調用函數
??????? 注：函數在調用的時候需要按位置指定參數，沒有存儲過程參數傳遞靈活
??????????? 必須具有 execute 函數的權限
???
??? 2. 函數在 SQL 中調用的主要場合
??????? 由于函數必須要返回數據，因此只能作為表達式的一部分調用。此外函數可以在 SQL 語句的以下部分調用
??????? a. select 命令的選擇列表或子查詢中
??????? b. 條件表達式 where, having 子句中
??????? c. connect by , start with ,order by 以及 group by 子句中
??????? d. insert 命令的 values 子句中
??????? f. update 命令的 set 子句中
???????
??? 3. 函數在 SQL 中調用的限制
??????? a . SQL 語句中只能調用存儲在服務器端的函數，而不能調用存儲于客戶端的函數
??????? b. SQL 語句中調用的函數只能帶有輸入參數 IN ，而不能帶有輸出參數 OUT 以及輸入輸出參數 IN OUT
??????? c. SQL 語句中調用的函數只能使用 SQL 支持的標準數據類型，不能使用 PL/SQL 特有的類型，如 boolean,table,record 等
??????? d. SQL 語句中調用的函數不能包含 insert ,update 和 delete 語句
???????
??????? 下面演示 SQL 調用時不能完整 DML 操作示例
?
??????? -- 創建一張表 tb_emp
??????????? scott@ORCL > create table tb_emp as select * from emp ;
?
??????? -- 創建一個函數，用于刪除 tb_emp 表中指定的 empno 號的雇員信息，并返回其薪資
??????????? 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 語句調用時，收到了錯誤信息，在內部查詢內不能完成 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 執行時函數被成功執行
??????? ??? 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 語句來調用函數
??????????? 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 ????
???????????
五、函數的管理 ?
??? 函數使用了與存儲過程相關的視圖，可以從系統視圖中獲得函數的相關信息
??????? DBA_OBJECTS
??????? DBA_SOURCE
??????? USER_OBJECTS
??????? USER_SOURCE
?
??? -- 查看函數的源碼
??????? 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 ;
?
??? -- 查看函數的參數信息
??????? scott@ORCL > desc delete_oper ;
??????? FUNCTION delete_oper RETURNS NUMBER
??????? ? Argument Name ????????????????? Type ??????????????????? In/ Out Default ?
??????? ? ------------------------------ ----------------------- ------ --------
??????? ? NO ???????????????????????????? NUMBER ????????????????? IN ?
?
???????
六、函數與存儲過程的差異
??? 存儲過程 ??? ????????????????????????? ? ???????????? 函數
??? ---------------------------------- ?????????????? ? -------------------------------
??? 不能被作為表達式調用 ??? ??????????????????????????? 只能作為表達式被調用
??? 聲明頭部關鍵字為 procedure ??????????????????????? 聲明頭部關鍵字為 function
??? 聲明頭部不包含 return 關鍵字來描述返回類型 ???????? 頭部必須包含 return 關鍵字 , 且 PL/SQL 塊中至少包含一個有效的 return 語句
??? 可以通過 out,in out 返回零個或多個值 ?????????????? 通過 return 語句返回一個與頭部聲明中類型一致的值 , 也可使用 in,in out 返回值
??? SQL 語句中不可調用存儲過程 ?????????????????????? ? SQL 語句可以調用函數
??? 多用于數據庫中完成特定的操作 , 如刪除 , 更新 , 插入等 DML 操作 ???? 多用于特定的數據如選擇等
???
七、更多參考
? ??? ?
有關 SQL 請參考
??????? SQL 基礎--> 子查詢
??????? SQL 基礎--> 多表查詢
SQL 基礎--> ROLLUP 與CUBE 運算符實現數據匯總
SQL 基礎--> 層次化查詢(START BY ... CONNECT BY PRIOR)
?
??? 有關 PL/SQL 請參考
??????? PL/SQL --> 語言基礎
?
???
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
