Oracle自訂函數1

標籤：

使用者定義函數是儲存在資料庫中的代碼塊，可以把值返回到調用程式。調用時如同系統函數一樣，如max(value)函數，其中，value被稱為參數。函數參數有3種類型。

IN 參數類型：表示輸入給函數的參數。

OUT 參數類型：表示參數在函數中被賦值，可以傳給函數調用程式。

IN OUT參數類型：表示參數既可以傳值也可以被賦值。

１、文法格式：

SQL文法方式建立的文法格式為：

CREATE OR REPLACE FUNCTION function_name         /*函數名稱*/
(
Parameter_name1,mode1 datatype1,　　　　　　　　　　　 /*參數定義部分*/
Parameter_name2,mode2 datatype2,
Parameter_name3,mode3 datatype3
…
)
RETURN return_datatype　　　　　　　　　　　　　　　　/*定義傳回值類型*/
IS/AS
BEGIN
       Function_body　　　　　　　　　　　　　　　　　 /*函數體部分*/
      RETURN scalar_expression                        /*返回語句*/
END function_name;

  

說明：

function_name:：使用者定義的函數名。函數名必須符合標示符的定義規則，對其所有者來說，該名在資料庫中是唯一的。

parameter：使用者定義的參數。使用者可以定義一個或多個參數。

mode：參數類型。

datatype：使用者定義參數的資料類型。

return_type:：使用者傳回值的資料類型。

函數返回scalar_expression運算式的值，function_body函數體由pl/sql語句構成。

２、樣本

函數代碼：

create or replace function T01001_count
return number
is
count_T01001 number;
begin
select count(*) into count_T01001 from T01001;
return(count_T01001);
end T01001_count;                  --記得一定要打分號

調用：
declare
i number;
begin
i:=T01001_count();
dbms_output.put_line(to_char(i));
end;　　　　　　　　　　　　　　　　 --記得一定要打分號

 

注意：

(1)    如果函數沒有參數，那麼函數名後不應該要括弧；

(2)    建立函數的時候end後面一定要記得寫函數名

--沒有參數的函數    
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;    
end get_user;    
   
--測試    
方法一    
select get_user from dual;    
   
方法二    
SQL> var v_name varchar2(50)    
SQL> exec :v_name:=get_user;    
   
PL/SQL 過程已成功完成。    
   
SQL> print v_name    
   
V_NAME    
------------------------------    
TEST    
   
方法三    
SQL> exec dbms_output.put_line(‘當前資料庫使用者是：‘||get_user);    
當前資料庫使用者是：TEST    
   
PL/SQL 過程已成功完成。   
--沒有參數的函數
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;
end get_user;
--測試
方法一
select get_user from dual;
方法二
SQL> var v_name varchar2(50)
SQL> exec :v_name:=get_user;
PL/SQL 過程已成功完成。
SQL> print v_name
V_NAME
------------------------------
TEST
方法三
SQL> exec dbms_output.put_line(‘當前資料庫使用者是：‘||get_user);
當前資料庫使用者是：TEST
PL/SQL 過程已成功完成。
Sql代碼 
--帶有IN參數的函數    
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無效！‘);    
end get_empname;   
--帶有IN參數的函數
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無效！‘);
end get_empname;
 

附：

函數調用限制
1、SQL語句中只能調用儲存函數(伺服器端),而不能調用用戶端的函數
2、SQL只能調用帶有輸入參數,不能帶有輸出,輸入輸出函數
3、SQL不能使用PL/SQL的特有資料類型(boolean,table,record等)
4、SQL語句中調用的函數不能包含INSERT,UPDATE和DELETE語句

 

查看函數院原始碼
oracle會將函數名及其原始碼資訊存放到資料字典中user_source 
select text from user_source where name=‘GET_EMPNAME‘;

刪除函數
drop function get_empname

 

 

　　不帶任何參數

　　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;

　　執行:

　　帶in參數的

　　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;

　　執行: SQL> var sal number

　　SQL> exec :sal:=get_sal(‘scott‘);

　　帶out參數的函數

　　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;

　　執行: SQL> var job varchar2(20)

　　SQL> var dname varchar2(20)

　　SQL> exec :dname:=get_info(‘SCOTT‘,:job)

　　帶in out參數的函數

　　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;

　　執行: var result1 number;

　　var result2 number;

　　exec :result2:=30

　　exec :result1:=result(100,:result2)

　　eg:

　　1　、一個最簡單的自訂函數Fun_test1的定義。

　　create or replace function Fun_test1(p_1 number)--Fun_test1是函數名，有一個輸入參數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;

　　--這個函數只是可以知道自訂函數的定義和格式。其實沒什麼用途。

　　2、Fun_test1自訂函數的調用的預存程序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;

　　--一個輸入參數，一個輸出參數

　　3、Fun_test1自訂函數的調用的預存程序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;

　　--自訂函數的調用方法和Oracle的其它內建函式是一樣的。

　　二、包的定義和使用入門

　　包一般是過程和函數的集合,對過程和函數進行更好的封裝,一般不針對欄位。

　　包的構成包括包頭和包體。

　　1、包頭的定義：

　　包頭僅僅只是對包中的方法進行說明,而沒有實現

　　文法：

　　create or replace package myPackage_1

　　is

　　procedure syaHello(vname varchar2);--申明了該包中的一個過程

　　end;

　　2、包體的定義：

　　包體是對包頭中已定義流程、函數的具體實現。

　　create or replace package body myPackage_1

　　is

　　procedure syaHello(vname varchar2)--對包中已定義流程的實現

　　is

　　begin

　　dbms_output.put_line(‘Hello ‘||vname);

　　end;

　　end;

　　要注意的是：

　　create or replace package後面的名稱必須和create or replace package body後面的名稱一致，

　　如果將create or replace package body後面的名稱改為，‘MYPACKAGE‘

　　否則將會出現諸如下面的錯誤：

　　必須說明標識符 ‘MYPACKAGE‘

　　3、調用包用的自訂方法：

　　create or replace procedure Pro_test_package(

　　p1_in string

　　)

　　AS

　　begin

　　myPackage_1.syaHello(p1_in);

　　end Pro_test_package;

　　eg2:

　　--沒有參數的函數

　　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;

　　--測試

　　方法一

　　select get_user from dual;

　　方法二

　　SQL> var v_name varchar2(50)

　　SQL> exec :v_name:=get_user;

　　--帶有IN參數的函數

　　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無效!‘);

　　end get_empname;

　　附：

　　函數調用限制

　　1、SQL語句中只能調用儲存函數(伺服器端),而不能調用用戶端的函數

　　2、SQL只能調用帶有輸入參數,不能帶有輸出,輸入輸出函數

　　3、SQL不能使用PL/SQL的特有資料類型(boolean,table,record等)

　　4、SQL語句中調用的函數不能包含INSERT,UPDATE和DELETE語句

　　查看函數院原始碼

　　oracle會將函數名及其原始碼資訊存放到資料字典中user_source

　　select text from user_source where name=‘GET_EMPNAME‘;

　　刪除函數

　　drop function get_empname;

　　判斷任務到期時間：

　　create or replace function GetUrgentState(m_TaskID varchar2,

　　m_SendTime date,

　　m_flag varchar2)

　　return varchar2 IS

　　myDate date;

　　ExpireTime 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;

　　--沒有到期時間 就是正常狀態

　　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;

　　--未發送任務，就是判斷目前時間

　　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

　　return ‘cb_limit.gif‘;

　　end if;

　　end if;

　　--小於3天的任務預警

　　if ExpireTime - myDate < 3 then

　　if m_flag = ‘String‘ then

　　return ‘預警‘;

　　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;

　　查詢其它表資料：

　　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;

　　格式化標題輸出：

　　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;

Oracle自訂函數1