柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) oracle-創(chuàng)建函數(shù)

http://yzkb.51969.com/

oracle自定義函數(shù)

核心提示：函數(shù)用于返回特定數(shù)據(jù)。執(zhí)行時(shí)得找一個(gè)變量接收函數(shù)的返回值; 語(yǔ)法如下: create or replace function function_name ( argu1 [mode1]datatype1, argu2 [mode2] datatype2, … ) return datatype is begin end;

執(zhí)行 var v1 varchar2(100) exec :v1:=function_na 函數(shù)用于返回特定數(shù)據(jù)。執(zhí)行時(shí)得找一個(gè)變量接收函數(shù)的返回值;

/=========================================================================================================== oracle函數(shù)的語(yǔ)法結(jié)構(gòu) ===============================================================================================================/ 語(yǔ)法如下: create or replace function function_name( 　　argu1 [mode1] datatype1, 　　argu2 [mode2] datatype2, …) return datatype --返回值類(lèi)型，自定義的函數(shù)必然有一個(gè)返回值 is –這里可以定義變量 begin –業(yè)務(wù)邏輯區(qū) end;

執(zhí)行 var v1 varchar2(100) 　　exec :v1:=function_name /=========================================================================================================== oracle函數(shù)不帶任何參數(shù) ===============================================================================================================/ create or replace function get_user return varchar2 is Result varchar2(50);

begin select username into Result from user_users; return(Result); end get_user; /

/*------------------------------------------------------------------------------------------------------------- 函數(shù)的調(diào)用1：直接調(diào)用，類(lèi)似于oracle預(yù)定義的函數(shù)那樣 --------------------------------------------------------------------------------------------------------------**/

SQL> select get_user from dual; GET_USER

SCOTT

/=========================================================================================================== oracle函數(shù)帶in參數(shù)的 ===============================================================================================================/ create or replace function get_sal(empname in varchar2) return number is Result number; begin select sal into Result from emp where ename=empname; return(Result); end get_sal; /

執(zhí)行: SQL> var sal number SQL> exec :sal:=get_sal(‘SCOTT’); 【說(shuō)明：調(diào)用函數(shù)一定要有一個(gè)變量來(lái)接收函數(shù)的返回值】 /=========================================================================================================== oracle函數(shù)帶out參數(shù)的函數(shù) ===============================================================================================================/ create or replace function get_info(e_name varchar2,job out varchar2) return number is Result number; begin select sal,job into Result,job from emp where ename=e_name; return(Result); end get_info; /

執(zhí)行: SQL> var job varchar2(20) SQL> var dname varchar2(20) SQL> exec :dname:=get_info(‘SCOTT’,:job) PL/SQL procedure successfully completed dname

3000 job

ANALYST /=========================================================================================================== oracle函數(shù)帶in out參數(shù)的函數(shù) ===============================================================================================================/ create or replace function result(num1 number,num2 in out number) return number is v_result number(6); v_remainder number; begin v_result:=num1/num2; v_remainder:=mod(num1,num2); num2:=v_remainder; return(v_result); Exception when zero_divide then raise_application_error(-20000,‘不能除0’); end result; /

執(zhí)行: SQL> var result1 number; SQL> var result2 number; SQL> exec :result2:=30 PL/SQL procedure successfully completed result2

30

SQL> exec :result1:=result(100,:result2) PL/SQL procedure successfully completed result1

3 result2

10 /=========================================================================================================== eg: 1　、一個(gè)最簡(jiǎn)單的自定義函數(shù)Fun_test1的定義。 ===============================================================================================================/ create or replace function Fun_test1(p_1 number)–Fun_test1是函數(shù)名，有一個(gè)輸入?yún)?shù)p_1，是number型的。返回值也是number型的 return number IS begin if p_1>0 then return 1; elsif p_1=0 then return 0; else return -1; end if; end; / –這個(gè)函數(shù)只是可以知道自定義函數(shù)的定義和格式。其實(shí)沒(méi)什么用途。 /=========================================================================================================== eg: 2、Fun_test1自定義函數(shù)的調(diào)用的存儲(chǔ)過(guò)程Pro_Fun_test1_1示例： ===============================================================================================================/ create or replace procedure Pro_Fun_test1_1(p1_in in number,p2_out out number) AS begin p2_out:=Fun_test1(p1_in); end Pro_Fun_test1_1; / /=========================================================================================================== eg: 一個(gè)輸入?yún)?shù)，一個(gè)輸出參數(shù) 3、Fun_test1自定義函數(shù)的調(diào)用的存儲(chǔ)過(guò)程Pro_Fun_test1_2示例： ===============================================================================================================/ create or replace procedure Pro_Fun_test1_2(p1_in in number,p2_out out number) AS t_1 number; begin select Fun_test1(p1_in)+100 INTO p2_out from bill_org where org_ID=1; end Pro_Fun_test1_2; / /=========================================================================================================== eg: 包的定義和使用入門(mén) ===============================================================================================================/ 自定義函數(shù)的調(diào)用方法和Oracle的其它內(nèi)部函數(shù)是一樣的。 包一般是過(guò)程和函數(shù)的集合,對(duì)過(guò)程和函數(shù)進(jìn)行更好的封裝,一般不針對(duì)字段。 包的構(gòu)成包括包頭和包體。

1、包頭的定義：

包頭僅僅只是對(duì)包中的方法進(jìn)行說(shuō)明,而沒(méi)有實(shí)現(xiàn)

語(yǔ)法：

create or replace package myPackage_1 is procedure syaHello(vname varchar2);–申明了該包中的一個(gè)過(guò)程 end; /

2、包體的定義： 　　包體是對(duì)包頭中定義的過(guò)程、函數(shù)的具體實(shí)現(xiàn)。

create or replace package body myPackage_1 is 　　 procedure syaHello(vname varchar2)–對(duì)包中定義的過(guò)程的實(shí)現(xiàn) begin dbms_output.put_line('Hello '||vname); end; end; /

要注意的是： create or replace package 后面的名稱必須和 create or replace package body 后面的名稱一致， 　如果將 create or replace package body后面的名稱改為，‘MYPACKAGE’ 否則將會(huì)出現(xiàn)諸如下面的錯(cuò)誤：必須說(shuō)明標(biāo)識(shí)符 ‘MYPACKAGE’

3、調(diào)用包用的自定義方法：

create or replace procedure Pro_test_package(

p1_in string ) AS begin myPackage_1.syaHello(p1_in); end Pro_test_package; /

eg2:

–沒(méi)有參數(shù)的函數(shù)

create or replace function get_user return varchar2 is v_user varchar2(50);

begin

select username into v_user from user_users;

return v_user;

return v_user; /=========================================================================================================== eg: 調(diào)用：方法一 ===============================================================================================================/ select get_user from dual; /=========================================================================================================== eg: 調(diào)用：方法二 ===============================================================================================================/ SQL> var v_name varchar2(50) SQL> exec :v_name:=get_user; /=========================================================================================================== eg: 帶有IN參數(shù)的函數(shù) ===============================================================================================================/ create or replace function get_empname(v_id in number) return varchar2 as v_name varchar2(50); begin select name into v_name from employee where id = v_id; return v_name; exception when no_data_found then raise_application_error(-20001, ‘你輸入的ID無(wú)效!’); end get_empname; / /=========================================================================================================== eg: 　　函數(shù)調(diào)用限制 ===============================================================================================================/ 　　1、SQL語(yǔ)句中只能調(diào)用存儲(chǔ)函數(shù)(服務(wù)器端),而不能調(diào)用客戶端的函數(shù)

2、SQL只能調(diào)用帶有輸入?yún)?shù),不能帶有輸出,輸入輸出函數(shù)

3、SQL不能使用PL/SQL的特有數(shù)據(jù)類(lèi)型(boolean,table,record等)

4、SQL語(yǔ)句中調(diào)用的函數(shù)不能包含INSERT,UPDATE和DELETE語(yǔ)句 /=========================================================================================================== eg: 　　查看函數(shù)院源代碼 ===============================================================================================================/ 　　oracle會(huì)將函數(shù)名及其源代碼信息存放到數(shù)據(jù)字典中user_source

select text from user_source where name=‘GET_EMPNAME’; /=========================================================================================================== eg: 　　刪除函數(shù) ===============================================================================================================/ drop function get_empname; /=========================================================================================================== eg: 　　判斷任務(wù)過(guò)期時(shí)間： ===============================================================================================================/

create or replace function GetUrgentState(m_TaskID varchar2,m_SendTime date,m_flag varchar2)return varchar2 IS myDate date; strsql varchar2(200); begin myDate := m_SendTime; strsql := ‘select max(EXPIRETIME) from t_wf_supervise where TASKID =’‘’ ||m_TaskID || ‘’‘’; execute immediate strsql into ExpireTime; –沒(méi)有到期時(shí)間 就是正常狀態(tài) if ExpireTime is null then if m_flag = ‘String’ then return ‘正常’; end if; if m_flag = ‘Img’ then return ‘cb_execute.gif’; end if; end if; –未發(fā)送任務(wù)，就是判斷當(dāng)前時(shí)間 if m_SendTime is null then myDate := sysdate; end if; if ExpireTime < myDate then if m_flag = ‘String’ then return ‘超期’; end if; if m_flag = ‘Img’ then end if; end if; –小于3天的任務(wù)預(yù)警 if ExpireTime - myDate < 3 then if m_flag = ‘String’ then return ‘預(yù)警’; end if;

if m_flag = ‘Img’ then

return ‘cb_warning.gif’;

end if;

else

if m_flag = ‘String’ then

return ‘正常’;

end if;

if m_flag = ‘Img’ then

return ‘cb_execute.gif’;

end if;

end if;

end;

查詢其它表數(shù)據(jù)：

create or replace function GetPreNode(m_PreTaskID varchar2) return varchar2 IS

nodename varchar2(50);

strsql varchar2(200);

begin

if m_PreTaskID is null then

return ‘’;

end if;

strsql := ‘select max(nodename) from t_Wf_Tasklist where TaskID =’‘’ ||

m_PreTaskID|| ‘’‘’;

execute immediate strsql

into nodename;

return nodename;

end;

格式化標(biāo)題輸出：

create or replace function FormatTitle(m_title varchar2,m_length number,m_FillChar varchar2) return varchar2 IS begin if lengthb(m_title) > m_length*2 then return substr(m_title, 0,m_length) || m_FillChar; else return m_title; end if; end; /

CREATE OR REPLACE FUNCTION f (n INTEGER) RETURN INTEGER IS BEGIN IF n = 0 THEN RETURN 1; ELSIF n = 1 THEN RETURN n; END IF; END; /

創(chuàng)建函數(shù)輸出員工號(hào)為7788 的員工的名稱：

create or replace function fun_show return varchar2 as v_ename emp.ename%type; – 這種定義變量的方式一定后面要加上對(duì)應(yīng)的字段的名稱 ，不要 v_ename emp%type 這樣 我這樣的玩過(guò)，報(bào)錯(cuò) begin select ename into v_ename from emp where empno = 7788; return v_ename; exception when others then dbms_output.put_line(‘SQLCODE:’ || SQLCODE || ‘–SQLERRM:’ || SQLERRM); end fun_show;

刪除函數(shù)： SQL> SQL> drop function fun_show; Function dropped

調(diào)用函數(shù)的方式很多： 1、select fun_show() from dual; 這種方式就是在代碼塊中調(diào)用，我們也可以單獨(dú)調(diào)用； 2、單獨(dú)調(diào)用 declare str varchar2(2000); begin str := fun_show(); dbms_output.put_line(str); end; / SCOTT PL/SQL procedure successfully completed

這樣調(diào)用比較常見(jiàn)

建立帶有參數(shù)的函數(shù)：

柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) oracle-創(chuàng)建函數(shù)

http://yzkb.51969.com/

推薦文章