Oracle 自定义函数语法与实例

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接： https://blog.csdn.net/libertine1993/article/details/47264211

 

Oracle自定义函数的语法如下：

 

 
 

   
   

    
    

   
   
   
   

    
    

     
     create 
     
     or 
     
     replace 
     
     function 函数名(参数
     
     1 模式 参数类型)
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     return 返回值类型
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     as
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     变量
     
     1 变量类型;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     变量2 变量类型;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     begin
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
         函数体;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     end 函数名;
    
    
   
   
 
 

 

 

参数的模式有3种:(如果没有注明, 参数默认的类型为 in.)

in: 为只读模式, 在函数中, 参数的值只能被引用, 不能被改变;

out: 为只写模式, 只能被赋值, 不能被引用;

in out:  可读可写.

提醒:
1.在Oracle自定义函数中, else if 的正确写法是 elsif 而不是 else if
2.使用 if 需要加 then  "if 条件 then 操作"

 

简单的例子: 读入两个值, 返回比较大的值

 

 
 

   
   

    
    

   
   
   
   

    
    

     
     create 
     
     or 
     
     replace 
     
     function function1(para1 
     
     in 
     
     number, para2 
     
     in 
     
     number) 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     return 
     
     number 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     as 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     begin
    
    
   
   

   
   

    
    

   
   
   
   

    
    
  
     
     if para1 > para2 
     
     then
    
    
   
   

   
   

    
    

   
   
   
   

    
    
      
     
     return para1;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       else
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
           return para2; 
    
    
   
   

   
   

    
    

   
   
   
   

    
    
  
     
     end 
     
     if;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     end function1;
    
    
   
   
 
 

使用:

 

 

select function1(666, 333) from dual;
 
 

结果:

 

 

另一个例子可能在实际中会用到, 有时候我们需要按非自然月进行数据统计, 这种时候我们只需要写一个自定义的函数处理日期就行了.

函数说明: 读入date型日期, 大于15号的归入下一个月, 小于等于15号归入本月

 

 
 

   
   

    
    

   
   
   
   

    
    

     
     create 
     
     or 
     
     replace 
     
     function fn_mymonth(oridate 
     
     in 
     
     date)
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     return varchar2
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     as
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       oriday 
     
     number;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       orimonth number;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       oriyear number; 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     begin
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       oriday := to_number(to_char(oridate, 
     
     'dd'));
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       orimonth := to_number(to_char(oridate, 'mm'));
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       oriyear := to_number(to_char(oridate, 'yyyy'));
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       if oriday <= 15 then                              
     
     --少于等于15号属于本月
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
         return to_char(oridate, 'yyyymm');
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       else
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
         if orimonth <= 8 then                           
     
     -- 日 <=8 则+1后转char还要补0,单独出来作为一种情况
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
           return to_char(oriyear)||'0'||to_char(orimonth + 1);
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
         elsif( orimonth <= 11 ) then                    
     
     -- 日 <= 11则+1后不会跨年,转char不需要补零, 单独出来作为一种情况
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
           return to_char(oriyear)||to_char(orimonth + 1);
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
         else                                            
     
     -- 最后一种情况就是跨年, 改变年份, 月份补零就行
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
           return to_char(oriyear + 1)||'0'||to_char(orimonth - 11);
    
    
   
   

   
   

    
    

   
   
   
   

    
    
    
     
     end 
     
     if;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
  
     
     end 
     
     if;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     end fn_mymonth;
    
    
   
   
 
 

使用与结果:

 

 

select fn_mymonth(to_date('2015-12-14', 'yyyy-mm-dd')) from dual;
 
 

 

select fn_mymonth(to_date('2015-12-15', 'yyyy-mm-dd')) from dual;
 
 

 

 

select fn_mymonth(to_date('2015-12-16', 'yyyy-mm-dd')) from dual;