Oracle函数、子程序实例讲解

子程序

一、子程序概述

pl/sql命名程序块的定义在pl/sql程序块的声明部分，用户称之为子程序。

通俗讲：命名的 pl/sql 块，编译并存储在中。

子程序构成：

l 声明部分

l 可执行部分

l 异常处理部分（可选）

子程序的优点：

模块性：子程序可以将程序划分成易于管理，定义明确的逻辑模块，它支持自顶向下的设计方法。

可维护性：简化维护操作。

可重性：子程序一旦被定义可以用于许多个应用程序。

安全性：通过设置权限，使数据更安全。

二、子程分类

oracle提供了4种类型的子程序：存储过程、函数、包、触发器。

过程 － 执行某些操作

函数 － 执行操作并返回值

三、子程序之：函数

函数是可以返回值的命名的 pl/sql 子程序。

函数分为：内置函数 和 自定义函数。

1. 内置函数

由oracle提供的一系列用于执行特定操作的函数。

sql内置函数带有一个或多个参数并返回一个值。

sql内置函数分为：单行函数、分组函数、分析函数

l 单行函数：从表中查询的每一行只返回一个值。

日期函数，数字函数，字符函数，转换函数，其他函数

l 分组函数：基于一组行来返回结果。为每一组行返回一个值。

count()、sum()、avg()、max()、min()

l 分析函数：根据一组行来计算聚合值。用于计算完成聚集的累计排名、移动平均数等。

分析函数为每组记录返回多个行。

row_number、rank、dense_rank

1.1 数值函数

round() 四舍五入

select round( 10054.345 ) from dual;

--返回：10054

select round( 10054.345 , 2 ) from dual;

--返回：10054.35 精确到小数点后2位

select round( 10054.345 , -2 ) from dual;

--返回：10100

trunc() 取整（无四舍五入）

select trunc( 45.67 ) from dual;

--返回：45

select trunc( -45.22 , 0 ) from dual;

--返回：-45

floor() 取整（最小值，无四舍五入）

select floor( 45.67 ) from dual;

--返回：45 最小的整数

select floor(-45.17) from dual;

--返回：-46 最小的负整数

mod() 求模（与java中的%一样）

select mod( 11 , 7) from dual;

--返回：4

power() 开方

select power( 5 , 2 ) from dual;

--返回：25 求5的2次方

sqrt() 开根号

select sqrt( 4 ) from dual;

--返回：2

sign()判断值的正负数

--正数返回1，0返回0，负数返回-1（以后常用）

select sign( 10 ) from dual;

--返回：1

1.2 字符函数

substr() 字符串截取

select substr( 'abcdefg' , 2 , 4 ) from dual;

--返回：bcde 从第2位开始连续截取4个

--注意：sql和oracle中字符串角标是从1开始；而java中字符串角标是从0开始。

length() 求字符串长度（字符个数，与中英文所占字节无关）

select length( 'abc' ) from dual;

--返回：3

instr() 查找字符位置（类似于java中的charindex，sql server中的indexof）

select instr( 'abcadefag' , 'a' ) from dual;

--返回：1 默认从字符串角标1开始查找

select instr( 'abcsadefag' , 'a' , 4 ) from dual;

--返回：5 从字符串角标4开始查找

concat() 连接 与||相同

select concat('a','b') from dual;

--结果：ab

replace() 替换

select replace('abcdefg','a',9) from dual;

--结果：9bcdefg

select translate('adbecf','abc','123') from dual;

lpad() 左填充

select lpad('abc',10,'6') from dual; --10是字符串总长度

--结果：6666666abc

rpad() 右填充

select rpad('abc',10,'6') from dual;

--结果：abc6666666

trim()

--有两个意思：去除字符串两边的空格

-- 去除字符串两边指定的字符

--语法：trim('字符1' from '字符串2')

select trim('a' from 'aabbbccdda') from dual;

--结果：bbbccdd

chr() ascii码对应的字符

select chr(97) from dual;

--结果：a

ascii() 字符对应的ascii码

select ascii('a') from dual;

--结果：97

decode()

--decode(value,if1,then1,if2,then2,if3,then3,...,else)

表示如果value 等于if1时，decode函数的结果返回then1,...,如果不等于任何一个if值，则返回else。初看一下，decode()只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使decode()函数具备大于、小于或等于功能。

select decode('a','a',1,'b',2,'c',3) from dual;

--1

--工资大于3500提示交税，等于3500刚刚好，小于3500要努力

--分析：三种情况

select decode(sign(sal-3500),1,'交税',0,'刚好',-1,'努力') from emp;

1.3 日期函数

sysdate 取当前时间

select sysdate from dual;

--显示时间类型（date），但要转成字符串呢？

systimestamp 获取系统当前时间

select systimestamp from dual;

--结果比较长，格式是格林乔治时间

add_months() 取月份实现加月份

select add_months(sysdate,3) from dual;

--在当前系统时间月份上加3

select add_months(to_date('2015-12-12','yyyy-mm-dd'),3) from dual;

--在2015-12-12的月份上加3

extract() 取(年月日)

select extract(day from sysdate) from dual;

--取当前系统的日

select extract(month from sysdate) from dual;

--取当前系统的月

--案例：求每个月的入职人数

按月分组统计

select extract(month from hiredate),count(*) from emp

group by extract(month from hiredate)

--求每年每个月的入职人数

select extract(year from hiredate),extract(month from hiredate),count(*) from emp

group by extract(year from hiredate),extract(month from hiredate)

months_between() 时间差

select months_between(sysdate,to_date('2015-12-12','yyyy-mm-dd')) from dual;

--前面的日期减去后面的日期，返回是月份，会出现负数，也会出现小数（最精确的）

last_day() 取本月的最后一天

select last_day(sysdate) from dual;

--2016/2/29 16:59:13 本月最后一天的当前时间

next_day() 下一个日期

select next_day(sysdate,'星期四') from dual;

--结果：下一个“星期四”的日期

问：下一周的星期三和下一个星期三是一样的吗？

round() 取最近的一天

--取整，超过12点算第二天，有四舍五入功能

select round(to_date('2009-09-08 12:12:12','yyyy-mm-dd hh:mi:ss')) from dual;

--只取整数部分 2009/9/9 还有超过12点就算9号了

select round(to_date('2009-09-08 12:12:12','yyyy-mm-dd hh24:mi:ss')) from dual;

--只取整数部分 2009/9/9

trunc() 截取日期函数

--取整，当天

select trunc(to_date('2009-09-08 12:12:12','yyyy-mm-dd hh:mi:ss')) from dual;

--只取整数部分 2009/9/8

select trunc(to_date('2009-09-08 23:12:12','yyyy-mm-dd hh24:mi:ss')) from dual;

--只取整数部分 2009/9/8

1.4 转换函数

转换函数将值从一种数据类型转换为另一种数据类型。

to_char

将非char类型转成char类型

select to_char(sysdate,'yyyy-mm-dd') from dual;

select to_char(sysdate,'yyyymmdd') from dual;

select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;

--这是oracle中唯一用到双引号的地方

to_date

将非date类型转成date类型

select to_date('2016-09-09','yyyy-mm-dd') from dual;

select to_date('2016-09-09 18:20:30','yyyy-mm-dd hh24:mi:ss') from dual;

to_number

将非数值型转成数值型

select to_number('345') from dual;

select to_number('34a5') from dual;

1.5 集合函数

count()

sum()

avg()

max()

min()

1.6 其他函数

转换空值函数

nvl(exp1,exp2)

--如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

select ename,nvl(comm, -1) from emp;

nvl2(exp1,exp2,exp3)

--如果该函数的第一个参数为空那么显示第三个参数的值，如果第一个参数的值不为空，则显示第二个参数的值。

select ename,nvl2(comm,-1,1) from emp;

nullif(exp1,exp2)

--如果exp1和exp2相等则返回空(null)，否则返回第一个值。

select ename,nullif(comm,-1) from emp;

1.7 分析函数

7. 分析函数

主查询结果的基础上进行一定的分析，如分部门汇总，分部门求均值等等。

（1）oracle 分析函数建立在所谓的数据窗口之上，数据窗口可以理解为一个数据集合。

主查询的数据可以按照不同的标准分割成不同的数据集。

比如partition by manager_id，按照manager_id将主查询的数据分成n(n代表有多少个不同的manager_id)个不同的数据窗口。

（2）其次，数据窗口内部还应该与一定的顺序通过 order by 实现

row_number() over()

rank() over()

dense_rank() over()

分析函数和group by的区别和联系：

（1）分析函数的功能大部分都可以通过group by 来聚合完成

（2）分析函数查询出来的行数是由主查询决定的，group by 的行数结果是由group by 后面的集合构成的唯一性组合决定的，通常比主查询的结果行数少。

sum(emp.salary) over(partition by emp.manager_id) sum_salary_department, --该部门薪水总额

dense_rank() over(partition by emp.manager_id order by emp.salary desc) rank_salary_dept, --该人员的部门薪水排行

min(emp.salary) keep(dense_rank first order by emp.salary) over(partition by emp.manager_id) min_salary_dept_first, --部门的最低薪水

first_value(emp.salary) over(partition by emp.manager_id order by emp.salary) min_salary_dept_firstv, --部门的最低薪水

lag(emp.full_name, 1, '00') over (order by emp.salary desc) last_persion, --薪水在自己前一位的人

2. 自定义函数

2.1 创建函数（in、out、in out）

创建函数的语法：

create [or replace] function

[(param1,param2)]

return is|as

[local declarations]

begin

executable statements;

return result;

exception

exception handlers;

end;

--案例：给编号，返回工资'交税'还是'刚好'，还是'努力'

create or replace function f_n126(sid number)

return varchar2

is

ssal number(8,2);

str varchar2(22); --注意，该处不用declare定义

begin

select sal into ssal from emp where empno=sid;

if ssal>3500 then

str:='交税';

elsif ssal=3500 then

str:='刚好';

else

str:='努力';

end if;

return str;

end;

create or replace function my_sum(n_a in number)

return number

is

n_sum number(5):=0;

begin

for int_s in 1..n_a loop

n_sum:=n_sum+int_s;

end loop;

return n_sum;

end;

定义函数的限制：

　　函数只能接受 in 参数，而不能接受 in out 或 out 参数

　　形参不能是 pl/sql 类型

　　函数的返回类型也必须是数据库类型

访问函数的两种方式：

　　使用 pl/sql 块

　　使用 sql 语句

创建函数

create or replace function fun_hello

return varchar2

is

begin

return '朋友，您好';

end;

从 sql 语句调用函数：

select fun_hello from dual;

例

create or replace function

item_price_range (price number)

return varchar2 as

min_price number;

max_price number;

begin

select max(itemrate), min(itemrate)

into max_price, min_price

from itemfile;

if price >= min_price and price <= max_price

then

return '输入的单价介于最低价与最高价之间';

else

return '超出范围';

end if;

end;

declare

p number := 300;

msg varchar2(200);

begin

msg := item_price_range(300);

dbms_output.put_line(msg);

end;

2.2 调用函数

--oracle调用方式：

select f_n126(7369) from dual;

--pl/sql调用方式：

declare str varchar2(22);

begin

str:=f_n126(7369);

dbms_output.put_line(str);

end;

2.3 删除函数

drop function f_name;