子程序
一、子程序概述
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;