oracle自訂函數《轉》

函數用於返回特定資料。執行時得找一個變數接收函數的傳回值;
文法如下:     create or replace function function_name
(
argu1 [mode1] datatype1,
argu2 [mode2] datatype2, ........
)
return datatype
is
begin

end;
執行 var v1 varchar2(100)
      exec :v1:=function_name
===============================================================================
不帶任何參數
===============================================================================
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;