自定义函数

*自定义函数

 

1：实现一个输出系统时间的函数

createorreplacefunctionmyfunc

returnnvarchar2

is     à函数里面使用as和is都是一样

begin

 return (to_char(sysdate,'yyyy-mm-ddhh:mi:ss'));

end;

 

查询：selectmyfunc from dual

 

输出：2017-12-07 09:43:35

 

 

 

2：实现为空处理nvl,与nvl2功能函数

 

If语句

 

处理nvl:

createorreplacefunctionmynvl(p1 nvarchar2,p2 nvarchar2)

returnnvarchar2

is

begin

 if (p1 isnullor p1='') then

    return p2;

 else

   return p1;

 endif;

end;

 

查询：selectmynvl('','zx') from dual

 

输出：zx

 

 

处理nvl:

createorreplacefunctionmynvl2(p1 nvarchar2,p2 nvarchar2,p3 nvarchar2)

returnnvarchar2

is

begin

 if (p1 isnullor p1='') then

    return p3;

 else

   return p2;

 endif;

end;

 

查询：selectmynvl2('','zx','lmy') from dual

 

输出：lmy

 

 

 

3：函数 实现   1 ，1， 2， 3，5， 8

 

For循环的使用

方式一：

createorreplacefunctionmyloop

returnint

as

p int:=0;     à定义一个变量的时候不需要给参数

begin

 loop

   p:=p+1;

   return p;     à在函数里面必须要返回值

   exitwhen p=11;       à此处为循环结束的条件

 endloop;

end;

 

方式一：

createorreplacefunctionmyloop

returnint

as

p int:=0;

begin

 loop

   p:=p+1;

   return p;

   exitwhen p=11;

   dbms_output.put_line(p);

   commit;

 endloop;

end;

 

 

递归：

1(位数)—>1(结果)

2—>1       +1

3—>2       +1+1

4—>3       +2+1

5—>5       +3+2

6—>8       +5+3

7—>13    +8+5

 

loop循环实现递归

createorreplacefunctionmydigui(num int)

returnint

as

begin

 if num=1or num=2then

   return1;

 else

   return mydigui(num-2)+mydigui(num-1);

 endif;

end;

 

输出：selectmydigui(4) from dual

 

结果：3

 

 

 

4：查询指定用户名工资的函数

 

Case语句

 

Case查询语句

select

 (

    case

      when sal=800then'土鳖'

      when sal=5000then'土豪'

      else'平民'

    end

 )as nick,

ename,sal fromscott.emp

 

 

 

Case使用函数查询

createorreplacefunctionmyfunsel(sname nvarchar2)

returnint

as

salary int;

begin

 select sal intosalary from scott.emp whereename=sname;

 return salary;

 commit;

end;

 

输出：selectmyfunsel('SCOTT')from dual

 

结果：3000

 

 

 

5：实现的四舍五入函数

 

selectround(1.235,1) from dual用函数实现round的功能。

 

selectsubstr(153.15485,5,1) from dual截取需要的数字

 

selectfloor(153.15485) from dual————>153向下取整

 

selectceil(153.15485) from dual————>154向上取整

 

selecttrunc(153.15485,1) from dual ————>153.1取小数点后几位

 

selectlength(153.123) from dual ————>7截取字符串长度

 

 

 

取出整数时的函数：

 

selecttrunc(153.56) from dual--153

selectlength(trunc(153.56)) from dual--3

selectsubstr(153.56,length(trunc(153.56))+2,1) from dual--5

 

createorreplacefunctionmyfunc(p number)

returnnumber

is

num int;

begin

 --先取出需要比较的

 num:=substr(p,length(trunc(p))+2,1);

 if num>=5then

   return ceil(p);

   else

   return floor(p);

 endif;

end;

 

输出结果：

select myfunc(153.123) from dual—>153

select myfunc(153.56) from dual—>154

 

 

实现四舍五入时的函数：

 

分析：

保留一位： +0.1     1/10       1/10^1

保留一位： +0.01    1/100      1/10^2

保留一位： +0.001   1/1000     1/10^3

 

取出小数点后一位：

selectsubstr(153.76,length(trunc(153.56))+2,1) from dual—7

 

实现代码：

createorreplacefunctionmyfunc(p number,poi int)

returnnumber

is

num int;

begin

 --先取出需要比较的

 num:=substr(p,length(trunc(p))+2+poi,1);

 if num>=5then

     return trunc(p,poi)+1/(poi*10);或trunc(p,poi)+ 1/power(10,poi)

或trunc(p+ 1/power(10,poi),poi)

 else

     return trunc(p,poi);

 endif;

end;