Oracle函数、子程序实例讲解，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;