函數(shù)與存儲過程相似，是數(shù)據(jù)庫中存儲的已命名PL-SQL程序塊。函數(shù)的主要特征是它必須有一個返回值。通過return來指定函數(shù)的返回類型。在函數(shù)的任何地方可以通過return expression語句從函數(shù)返回，返回類型必須和聲明的返回類型一致。

Oracle自定義函數(shù)的語法如下：

create or replace function 函數(shù)名(參數(shù)1 模式 參數(shù)類型)
return 返回值類型
as
變量1 變量類型;
變量2 變量類型;
begin
   函數(shù)體;
end 函數(shù)名;

參數(shù)的模式有3種:(如果沒有注明, 參數(shù)默認(rèn)的類型為 in.)
in: 為只讀模式, 在函數(shù)中, 參數(shù)的值只能被引用, 不能被改變;
out: 為只寫模式, 只能被賦值, 不能被引用;
in out: 可讀可寫。

實例1，沒有參數(shù)的函數(shù)

create or replace function get_accidentno return t_claim.accident_no%TYPE
  is 
     v_accident_no t_claim.accident_no%TYPE;
  begin
        select accident_no into v_accident_no from t_claim where claim_id=521;
        return v_accident_no;
  end get_accidentno;

測試：

  select get_accidentno from dual;

輸出結(jié)果：

image.png

實例2，帶有IN參數(shù)的函數(shù)

create or replace function get_accidentno1(claimid in t_claim.claim_id%TYPE) return  t_claim.accident_no%TYPE is 
   v_accident_no t_claim.accident_no%TYPE;
   begin
     select accident_no into v_accident_no from t_claim where claim_id=claimid;
     return v_accident_no;
     
     exception 
      when no_data_found then
         raise_application_error(-20001,'你輸入的ID無效！');
   end get_accidentno1;

測試：

select get_accidentno1(521) from dual;

輸出結(jié)果：

image.png

查看函數(shù)院源代碼
oracle會將函數(shù)名及其源代碼信息存放到數(shù)據(jù)字典中user_source

select * from user_source where name='GET_ACCIDENTNO';

image.png

刪除函數(shù)

drop function get_accidentno;

函數(shù)調(diào)用限制
1)、SQL語句中只能調(diào)用存儲函數(shù)(服務(wù)器端),而不能調(diào)用客戶端的函數(shù)
2)、SQL只能調(diào)用帶有輸入?yún)?shù),不能帶有輸出,輸入輸出函數(shù)
3)、SQL不能使用PL/SQL的特有數(shù)據(jù)類型(boolean,table,record等)
4)、SQL語句中調(diào)用的函數(shù)不能包含INSERT,UPDATE和DELETE語句
5)、可執(zhí)行部分至少有一條return語句
6)、調(diào)用函數(shù)時，不能將函數(shù)作為單獨的語句存在，可以作為表達(dá)式的一部分

實例3: 讀入兩個值, 返回比較大的值

create or replace function bijiao(para1 in number, para2 in number) 
return number 
as 
begin
 if para1 > para2 then
     return para1;
 else
     return para2; 
 end if;
end bijiao;

測試：

select bijiao(100, 200) from dual;

輸出結(jié)果：

image.png

實例4: 讀入date型日期, 大于15號的歸入下一個月, 小于等于15號歸入本月

create or replace function fn_mymonth(oridate  in date)
return varchar2
as
 nowday   number;
 nowmonth number;
 nowyear  number; 
begin
 nowday := to_number(to_char(oridate, 'dd'));
 nowmonth := to_number(to_char(oridate, 'mm'));
 nowyear := to_number(to_char(oridate, 'yyyy'));

 if nowday <= 15 then                              --少于等于15號屬于本月
   return to_char(oridate, 'yyyymm');
 else
   if nowmonth <= 8 then                           -- 日 <=8 則+1后轉(zhuǎn)char還要補(bǔ)0,單獨出來作為一種情況
     return to_char(nowyear)||'0'||to_char(nowmonth + 1);
   elsif( nowmonth <= 11 ) then                    -- 日 <= 11則+1后不會跨年,轉(zhuǎn)char不需要補(bǔ)零, 單獨出來作為一種情況
     return to_char(nowyear)||to_char(nowmonth + 1);
   else                                            -- 最后一種情況就是跨年, 改變年份, 月份補(bǔ)零就行
     return to_char(nowyear + 1)||'0'||to_char(nowmonth - 11);
   end if;
 end if;
end fn_mymonth;

測試：

select fn_mymonth(to_date('2020-04-23', 'yyyy-mm-dd')) from dual; --輸出：202005
select fn_mymonth(to_date('2019-12-15', 'yyyy-mm-dd')) from dual; --輸出：201912
select fn_mymonth(to_date('2019-12-16', 'yyyy-mm-dd')) from dual; --輸出：202001