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
return
[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;