学无止境

KNOWLEDGE HAS NO LIMIT

0%

oracle笔记

发表于 分类于 Valine：
本文字数： 15k 阅读时长 ≈ 13 分钟
oracle

1、dual表

此表为一个没有实际意义的表，主要用来补全语法

1
select 1+1 from dual

2、双引号和单引号的区别

双引号用于别名中，当别名中有空格时会报错，使用””就能解决

单引号用于把字符串值包含起来

1
select ename "姓名" from emp
1
select from emp where ename in ('张三','李四')

3、字符串拼接

oracle特有的字符串拼接

1
select '姓名：' || ename from emp

oracle和mysql都有的concat()函数

1
select concat('姓名',ename) from emp

4、模糊查询以及转义字符

% 匹配多个字符

_匹配单个字符

使用escape关键字,escape后指定的是把前面的这个符号当做转义符，’\‘是自定义的

1
select from emp where ename like '%\%%' escape '\'

5、排序问题

空值放在上面还是下面

1
2
select from emp order by sal desc --此种是直接倒序排列，空值在上面 默认nulls first
select from emp order by sal desc nulls last  --此种是空值在下面

多列排序

1
2
--查询部门编号和工资，部门编号按升序，工资按降序
selece deptno,sal from emp order by deptno asc,sal desc

6、日期相关

查询当前时间

1
select sysdate from dual             --2019-10-10 08:13:12

查询三个月以后的这天时间

1
select add_months(sysdate,3) from dual       --2020-01-10 08:15:38

查询三天后的时间

1
select sysdate + 3 from dual              -- 2019-10-13 08:16:36

查询员工的入职天数

1
2
select sysdate - hiredate from emp       --得出的时间是小数，需要取整
select ceil(sysdate - hiredate) from emp

查询员工入职的周数

1
select ceil((sysdate - hiredate)/7) from emp

查询员工入职的月数

1
select ceil(months_between(sysdate,hiredate)) from emp

查询员工入职的年数

1
select ceil(months_between(sysdate,hiredate)/12) from emp

7、数值字符相互转换

字符串转数值

1
2
select 100 + '100' from dual                 --默认转换200
select 100 + to_number('100') from dual     --一般用上面那个不用这个

数值转字符串 to_char(数值,’格式’)

1
select to_char(100) || '测试' from dual      --100哈哈

8、to_char()函数

TO_CHAR(x[[,c2],C3])

x为数据型时

序号 格式 简例 说明
1 ,(逗号) ‘9999,999’ 逗号,
一般以千分位出现,作为分组符号使用.
如果需要您也可以当作是十分位,百分位出现,可以出现N次,视乎数字的大小而定.
变态的例子是 to_char(1234,’9,9,9,9’).
注意事项:只能出现在整数部分.
2 .(点号) ‘99.99’ 点号.
不要念为”句号”,句号是个圆圈,
点号只能出现在小数点对应的地方.只能出现一次.
to_char(1234.34,’9,9,9,9.99’)
注意事项:只能出现在一个地方,就是原来数据小数点位置
3 $(美元符号) ‘$999.99’ 美元.
其实你可以放在任意地方(在10G下)
to_char(1234.34,’9,9,9,9.$99’)
注意事项:只能出现一次.
4 0(零) ‘0999.99’ 零.
在对应位置返回对应的字符,如果没有则以’0’填充.
to_char(0.34,’9,9,9,0.$99’)=’$0.34’;
to_char(1234,’9999.00’)=’1234.00’;
注意事项:这是一个强制的符号,对应位没有,则以’o’填充,这是9很大不同地方
5 9 ‘999.99’ 9.
在小数位,则表示转换为对应字符,如果没有则以0表示;
在整数位,没有对应则不填充字符.
to_char(123,’999.99’)=123.00;
TO_CHAR(123,’99999.9’)=123.0;
注意事项:对于0和9而言,如果格式的位数不如数字的位数多,会返回’#’.
譬如to_char(12345,’9999’)=’#####’
譬如to_char(12345,’9999’)=’#####’
6 B(空格符) ‘B999’ 没有其它特别作用,在整数部分最前面加一个空格,可以出现在任意位置.
‘S’||TO_CHAR(1234,’99B99’)=’S 1234’;
注意事项:只能出现在整数部位.
7 C(国际货币符号) ‘C9999’ 在特定的位置返回一个ISO货币符号(就是NLS_ISO_CURRENCY参数所代表的值)
TO_CHAR(1233,’C9999’)=’CNY1234’ ,这是新的国际标准RMB,关于这个可查询”国际货币符号”
注意事项:只能出现在整数部位第一位.
可以通过alter session set NLS_ISO_CURRENCY=’JAPAN’;来修改当前会话的设置.
8 D(ISO 小数位符号) ‘999D99’ 这是”点号”的国际版本(ISO),作用等同于点号,也是只能出现一次.所不同的是,数据库会根据NLS_NUMERIC_CHARACTER的参数值来设置内容.默认的这个值是点号.
注意事项:没有特别需要一般不要用这个格式符号.也不要轻易修改参数值.
也可用alter sesssion set 来修改.
alter session set nls_numeric_characters=’!,’; to_char(1234.34,’9999d99’)=1234!34
9 EEEE(科学计算符) 9.9EEEE 科学计算符号
TO_CHAR(2008032001,’9.9EEEE’)=’2.01E+09’,由于是科学计算方法,所以小数位前面加一个9或者0即可,多个是没有意义的.
10 G(分组符号) 999G999 是逗号(,)的的ISO标准,作为分组符号使用,可以放在多个地方使用.
TO_CHAR(123456,’999G9G99’)=123,4,56
注意事项:同第八项 -D, 此外如果要转换出小数点,则要和D配合使用,不能和点号配合.
11 L(本地货币符号) ‘L999’ 是C的本地版本.可以放在整个格式的最前面和最后面.
TO_CHAR(123456,’999G9G99D00L’)=123,4,56.00￥
注意事项:同第七项 C
12 MI(负号) ‘9999MI’ 如果是负数,在尾部加上负号(-),如果是正数,则尾巴加上空格
to_char(1234,’9999mi’)||’S’||TO_CHAR(-5678,’9999MI’) =1234 S5678-
注意事项:只能放在格式尾巴
13 PR(符号) 9999PR 是表达负数的另外一种方式.如果是正数,则头部加上空格;如果是负数,则用小简括号<>把数字包起来.
TO_CHAR(-1234.89,’9G999D00PR’)=<1,234.89>
注意事项:同12
14 RN(rn) RN(rn) 把整数(1-3999)转换为罗马字符.RN表示转为大写,rn表示小写的.
select to_char(23,’RN’) from dual
注意事项:只能自己使用,不能和其它符号组合使用.
15 S ‘9999S’ 是12,13的综合改进版本.为整数加一个正号+,为负数加一个符号-.S在前则加在前,在后则在后.
TO_CHAR(-1234,’S9999’)=-1234;TO_CHAR(1234,’S9999’)=+1234
16 TM TM9/TMe 使用这个参数等于没有用参数to_char(number)一样,应为’tm9’是默认的格式参数.
to_char(1234,’tme’)=1234
注意事项:格式要么是TM9,要么是TME.
当数字长度超过64位时候,TM9的输出等同于TME的输出.
17 U U999 双币符号,例如欧元.作用同11的L
TO_CHAR(999,’U999’)=￥999
注意事项：通过NLS_DUAL_CURRENCY 控制
18 V 999V9 这是个比较古怪，又不是很常使用的符号。它的作用在于做一个计算。
例如TO_CHAR(N,’999V9’),以p表示V的位置,则该表达式＝to_char(N×(10的P-1次方)).但是9个数又必须保证大于等于乘积之后表示的位数.
TO_CHAR(5,’9V’)=51=5;
TO_CHAR(5,’9V9’)=510=50
TO_CHAR(5,’9V99’)=500
TO_CHAR(50,’9V99’)=’######’9的个数不够
注意事项：格式中不能和小数表达写在一起，但是可以混合货币等。
19 X xxxx 转换为16进制。
TO_CHAR(100,’XX’)= 64
注意事项：数值必须是大于等于0的整数。前面只能和0或者FM组合使用.
20 通过以上的例子，我们了解了各种数字的格式。可以说格式太多样，难于记在脑子，最好是作为一个参考存在着.
归类：
数值类： 0,9,
分组类：(.),(,),D,G ,其中点好和逗号因为表示不明显，所以用小括号凸显。
货币类：　$,C,L,U
计算转换类：EEEE,RN,V,X
正负符号：MI,PR,S
其它类：B
正统类：TM

【示例】
to_char(1210.73, ‘9999.9’) 返回 ‘1210.7’
to_char(1210.73, ‘9,999.99’) 返回 ‘1,210.73’
to_char(1210.73, ‘$9,999.00’) 返回 ‘$1,210.73’
to_char(21, ‘000099’) 返回 ‘000021’
to_char(852,’xxxx’) 返回’ 354’

x为日期型,c2可用参数

序号 格式 简例 说明
1 - / , . ; :
“text”		 略 时间分隔符号，除了标准的几个，还允许用文字作为分割符号。
例如 to_char(sysdate,’YYYY”年”mm”月”dd”日”‘)=2008年04月24日
2 AD
A.D.		 即拉丁文Anno Domini的简写，表示公元.会根据nls的不同转换为公元或者ad等
无特殊注意事项
3 AM
A.M.		 上午的简写 ,同pm, p.m. （下午) , 中文环境输出为上午（如果是上午）
4 BC
B.C.		 虽然标准的写法是B.c. (c小写） 或者BC,好在Oracle不讲究这个。表示公元前
5 CC
SCC		 返回世纪，以阿拉伯数字表示
如果年的后两位介于01-99那么，返回前两位+1，否则返回前两位
6 D 一周之中的某天，返回的是序号1-7
7 DAY 一周之中的某天，不过返回的是星期几而已，这和语言设置有关系，在中国环境 NLS_DATE_LANGUAGE=SIMPLIFIED CHINESE ，用星期一到星期天表示
8 DD 月份中的某天(1-31)
9 DDD 年份中的某天（1-366）
10 DL ‘DL’ 返回长的日期格式。受到NLS_TERRITORY,NLS_LANGUAGE参数控制。例 2008年4月28日 星期一
限制：除了DL，其它什么的都不能设置。
11 DS 返回短的日期格式。受到NLS_TERRITORY,NLS_LANGUAGE参数控制。 例如 2008-04-28
限制：除了DL，其它什么的都不能设置。
12 DY 日期的简称，就是星期几（当然这指的是中国环境下)
13 E 纪元简称，但是只适合以下集中日历：日本皇室，中华民国,太过佛历
14 EE 纪元全程，适合情况同E
15 FF [1..9] 就是毫秒,如果不更上数字就是用默认的精度。
只能用于timestamp类型的。
16 FM 值得注意的一个函数：不返回任何内容。
17 FX 同上
18 HH 表示小时，为12小时制，同hh12(1-12)
19 HH12 表示小时，为12小时制(1-12)
20 HH24 表示小时，为24小时制（0-23）
21 IW ISO标准的星期序号(1-52,或者1-53）
22 IYYY
IYY
IY
I		 IYY,IY,I, ISO年(4位）的4,3，2，1位数字(倒数）
to_char(to_date(21120401,’yyyymmdd’),’iyyy, iyy,iy,i’)=2112, 112,12,2
23 J 儒略日(多用于天文的一种日历）,从公元前4712年一月一日算起，得出的结果是个整数，算法大体为 （公元日期+4712）*儒略日历年平均天数
24 MI 秒（0-59）
25 MM 2位月（1-12）
26 MON 月的简称，和国家有关系NLS_DATE_LANGUAGE，例如04在中文环境下用4月表示.
27 MONTH 月的名称，国家有关系NLS_DATE_LANGUAGE,目前在中文下04表示为4月。
28 PM 同am,a.m.表示下午
P.M.
29 Q 季度（1-4）
30 RM 用罗马数字表示的月份，I ,II ,III ,IV ,V ,VI ,VII ,VIII,IX ,X ,XI ,XII
31 RR 有点四舍五入表示年的意思，具体的用法有那么一点点复杂。
以s表示输入的年份最后两位，c表示当前的年份最后两位,其输出结果(新的年份前两位）可以用函数r=f(s,c)来表示,s2,c2分别表示s,c的前两位。
1）s=[0,49],c=[0,49],则r=c2
2) s=[0,49],c=[50,99],则 r=c2+1
3) s=[50,99],c=[0,49],则r=c2-1
4) s=[50,99],c=[50,99],则 r=c2
简而言之就是靠近当前年份原则，如果和当前年份同区域那么就一样，如果比当前区域大，那么就是当作是当前世纪前一世纪，否则就是下一个世纪。
举例来说，以to_date为例子
SQL> select to_date(‘89-01-01’,’rr-mm-dd’) ,to_date(‘12-01-01’,’rr-mm-dd’) FROM DUAL;
TO_DATE(‘89-01-01’,’RR-MM-DD’) TO_DATE(‘12-01-01’,’RR-MM-DD’)
—————————— ——————————
1989-01-01 2012-01-01
我想oracle会搞这个东东出来，估计有两个考虑一个是为了方便，一个是为了对付百年或者千年问题。
32 RRRR 如果输入参数只有两位，则同rr，否则就同yyyy作用.
33 SS 秒（0-59），一分钟内
34 SSSSS 一天从午夜开始的累积秒数.(0-86399)
35 TS 返回短日期格式内容，包括时分秒等,只能和dl,ds组合使用，格式是:
dl ts或者dl ts ,中间以空格间隔开。TO_CHAR(SYSDATE,’TS’)=下午 4:50:04
表现形式受NLS_TERRITORY 和NLS_LANGUAGE影响。
36 TZD 夏令时制信息，时区简写加上夏令时信息，必须和格式tzr设置的时区对应。
包括下面三个TZ开头的，都是和时区相关，并不是直接用在to_char
37 TZH 时区中的小时，例如hh:mi:ss.fftzh:tzm’
38 TZM 时区中的分钟.
39 TZR 时区中的区域信息，必须是数据库支持的时区，例如US/Pacific
40 WW 和iw类似，也是表示星期的序号，从年的第一天算起到年的最后一个第七天。二者取值基本相同。（1-53） ，例如2008-01-01 到2008-01-07 算1，2008-01-09~2008-01-13 算2
41 W 一个月中的星期序号，其算法同ww,不过是局限在一月之内而已，和iso的不同。
42 X 代表本地根符号，没有特别用处，只能和timestamp类型一起使用.
43 Y,YYY 四位年，用都好分隔 例如2,008
44 YEAR
SYEAR		 发音表达的年，例如 2008=two thousand eight
S前缀表示公元前BC
45 YYYY
SYYYY		 四位年，S前缀表示公元前BC
46 YYY
YY
Y		 一次表示后面3，2，1位的年，例如2008 可以分别取值为008,08,8

【示例】
to_char(sysdate,’d’) 每周第几天
to_char(sysdate,’dd’) 每月第几天
to_char(sysdate,’ddd’) 每年第几天
to_char(sysdate,’ww’) 每年第几周
to_char(sysdate,’mm’) 每年第几月
to_char(sysdate,’q’) 每年第几季
to_char(sysdate,’yyyy’) 年

9、to_date()

查询入职日期在1981到1985之间的员工信息

1
select from emp where HIREDATE between to_date('1981','yyyy') and to_date('1985','yyyy')

10、通用函数nvl()

nvl(x,y) 如果x = null则返回y

1
select nvl(null,23) from dual              --23

nvl2(x,y,z) 如果x = null 则返回z，否则返回y

1
2
select nvl2(null,2,3) from dual            --3
select nvl2(1,2,3) from dual               --2

nullif(x,y) 如果x = y 则返回null，否则返回x

1
2
select nullif(1,1) from dual             --null
select nullif(1,2) from dual   			--1

coalesce(x,y,z,a,b) 返回第一个不为null的值

1
select coalesce(null,null,4,2,1) from dual  --4

11、条件表达式

oracle和mysql共有的 字段值区分大小写

case 字段

​ when 值1 then 值

​ when 值2 then 值

​ else

​ 默认值

​ end

给表中姓名取中文名

1
2
3
4
5
6
7
select 
	case ename
		when 'smith'	then  '史密斯'
		when 'blake'	then  '布莱克'
		else '路人甲'
	end
from emp;

oracle独有的 decode() 字段值区分大小写

decode(字段,if1,then1,if2,then2,’默认值’)

1
select decode(ename,'SMITH','史密斯','BLAKE','布莱克','路人甲') from emp

12、伪列rownum 行号，rowid 物理地址(oracle特有)

1
2
select  rownum, emp.from emp 
select rownum , emp.from emp where rownum > 2 -- 没有数据，因为rownum默认值为1，查出一条之后自身+1,常用做小于查询

13、创建（system账号）

创建表空间

1
2
3
4
5
6
7
8
--创建
create tablespace syl 
datafile 'c:\syl.dbf'  --指定文件路径
size 100m		--指定文件大小
autoextend on		--on 开启自增长
next 10m      --自增长每次10m
--删除
drop tablespace syl

创建用户

1
2
3
4
5
6
7
8
--此用户没有创建会话权限
create user syl   --用户名
identified by 12345  -- 密码
default tablespace syl  -- 表空间

--授予登录权限
grant connect to syl

Oracle中已存在三个重要的角色: connect 角色，resource 角色，dba 角色。

CONNECT角色: –是授 予最终用户的典型权利，最基本的
ALTER SESSION -修改会话
CREATE CLUSTER -建立聚簇
CREATE DATABASE LINK -建立数据库链接I
CREATE SEQUENCE -建立序列
CREATE SESSION–建立会话
CREATE SYNONYM -建立同义词
CREAIE VIEW -建立视图
RESOURCE角色: -是授 子开发人员的
CREATE CLUSTER–建立聚簇
CREATE PROCEDURE -建立过程
CREATE SEQUENCE -建立序列
CREATE TABLE -建表
CREATE TRIGGER -建立触发器
CREATE TYPE –建立类型
DBA角色:拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构，并且系统
权限也需要DBA授出，且DBA用户可以操作全体用户的任意基表，包括删除

14、plsql

输出菱形

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--坐标系思路
declare
	m number := 5;
begin
	for x in -m..m loop
		for y in -m..m loop
			if abs(y) + abs(x) <= m then
				dbms_output.put('*');
			else
				dbms_output.put(' ');
			end if;
		end loop;
		dbms_output.new_line();
	end loop;
end;

15、游标

相当于jdbc中的结果集resultset

0、声明游标

1、打开游标 open 游标名

2、从游标中循环读取数据

​ fetch 游标名 into 变量

​ 游标名%found : 找到数据 游标名%notfound : 没有找到数据

3、关闭游标 close 游标名

不带参数的游标

输出员工表中的所有员工的姓名和工资

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--游标：所有员工
--声明一个变量，用来记录一行数据   %rowtype
declare
	cursor vrows is select from emp;  --声明游标中的数据是怎么来的   
	vrow emp%rowtype;    --相当于声明一个对象用来存游标中的每一行数据，对象的属性为emp表中的所有列类型
begin
	open vrows; --打开游标
	loop  -- 开启循环
		fetch vrows into vrow;  -- 从vrows中取出一行数据并塞给vrow
		exit when vrows%notfound;  --退出循环的条件：游标中没有数据
		dbms_output.put_line('姓名：' || vrow.ename || '工资：' || vrow.sal);   --输出
	end loop;    --结束循环
	close vrows;  --关闭游标
end;
--分号不能少
带参数的游标

输出指定部门下的员工姓名和工资

cursor 游标名(参数名 参数类型) is 查询语句

相当于定义一个方法，这个方法需要一个参数用来作为后面查询语句的条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--游标：所有员工
--声明一个变量，用来记录一行数据   %rowtype
declare
	cursor vrows(dno number) is select from emp where deptno = dno;  --声明游标中的数据是怎么来的
	vrow emp%rowtype;    --相当于声明一个对象用来存游标中的每一行数据，对象的属性为emp表中的所有列类型
begin
	open vrows(10); --打开游标
	loop  -- 开启循环
		fetch vrows into vrow;  -- 从vrows中取出一行数据并塞给vrow
		exit when vrows%notfound;  --退出循环的条件：游标中没有数据
		dbms_output.put_line('姓名：' || vrow.ename || '工资：' || vrow.sal);   --输出
	end loop;    --结束循环
	close vrows;  --关闭游标
end;
--分号不能少
系统引用游标

输出员工表中的所有员工的姓名和工资

1、声明系统引用游标 – 游标名 sys_refcursor;

2、打开游标 – open 游标名 for 查询语句

3、循环取数据，和上面一样

4、关闭游标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--游标：所有员工
--声明一个变量，用来记录一行数据   %rowtype
declare
	vrows sys_refcursor;  --声明系统游标
	vrow emp%rowtype;    --相当于声明一个对象用来存游标中的每一行数据，对象的属性为emp表中的所有列类型
begin
	open vrows  for select from emp; --打开游标，指定游标数据怎么来的
	loop  -- 开启循环
		fetch vrows into vrow;  -- 从vrows中取出一行数据并塞给vrow
		exit when vrows%notfound;  --退出循环的条件：游标中没有数据
		dbms_output.put_line('姓名：' || vrow.ename || '工资：' || vrow.sal);   --输出
	end loop;    --结束循环
	close vrows;  --关闭游标
end;
--分号不能少
for循环取数据
1
2
3
4
5
6
7
declare
	cursor vrows is select from emp;  
begin
	for vrow in vrows loop
		dbms_output.put_line('姓名：' || vrow.ename || '工资：' || vrow.sal);   --输出
	end loop;
end;

比上面简单多了

扩展

根据不同的职位涨工资，PRESIDENT涨1000，MANAGER涨800，其他人涨400

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
declare
	cursor vrows is select from emp;
	vrow emp%rowtype;
begin
	open vrows;
	loop
		fetch vrows into vrow;
		exit when vrows%notfound;
		if  vrow.job='PRESIDENT'  then
			update emp set sal = sal + 1000 where empno = vrow.empno;
		elsif vrow.job='MANAGER'  then
			update emp set sal = sal + 800 where empno = vrow.empno;
		else
			update emp set sal = sal + 400 where empno = vrow.empno;
		end if;
	end loop;
	close vrows;
	commit;   --将更新提交
end;

16、异常Exception

异常类型 异常描述
CURSOR_ALREADY_OPEN cursor_alerady_open 试图”OPEN”一个已经打开的游标
DUP_VAL_ON_INDEX dup_val_on_index 试图向有”UNIQUE”中插入重复的值
INVALID_CURSOR invalid_cursor 试图对以关闭的游标进行操作
INVALID_NUMBER invalid_number 在SQL语句中将字符转换成数字失败
LOGIN_DENIED login_denied 使用无效用户登陆
NO_DATA_FOUND no_date_found 没有找到数据时
NOT_LOGIN_ON not_login_on 没有登陆Oracle就发出命令时
PROGRAM_ERROR program_error PL/SQL存在诸如某个函数没有”RETURN”语句等内部问题
STORAGE_ERROR storage_error PL/SQL耗尽内存或内存严重不足
TIMEOUT_ON_RESOURCE timeout_on_resource Oracle等待资源期间发生超时
TOO_MANY_ROWS too_many_rows “SELECT INTO”返回多行时
VALUE_ERROR value_error 当出现赋值错误
ZERO_DIVIDE zero_divide 除数为零 
1
2
3
4
5
6
7
8
9
10
declare 
	n number;
begin
	n:=1/0;
exception 
	when zero_divide then
		dbms_output.put_line('除数为0异常' || sqlerrm);  --sqlerrm sql错误信息
	when others  then
		dbms_output.put_line('发生了其他异常' || sqlerrm);
end;

自定义异常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--自定义异常
declare 
  cursor vrows  is select from emp where empno = 88888;
	vrow emp%rowtype;
	no_emp exception;
begin 
	open vrows;
	fetch vrows into vrow;
	if vrows%notfound then
		--raise_application_error(-20001,'自定义异常');   --发生其他异常ORA-20001: 自定义异常
		--raise no_emp;                 -- 没有找到数据，抛出自定义异常User-Defined Exception
	end if;
	
	for vrow in vrows loop
			dbms_output.put_line('姓名：' || vrow.ename || '工资：' || vrow.sal); 
	end loop;
exception 
	when no_emp then 
		dbms_output.put_line('没有找到数据，抛出自定义异常' || sqlerrm);
	when others then 
		dbms_output.put_line('发生其他异常'|| sqlerrm);
end;

17、存储过程

语法

create [ or replace ] procedure 存储过程名 （参数名 in | out 参数类型，参数名 in | out 参数类型）

is | as

​ – 变量声明

begin

​ – 业务逻辑

end;

将员工id作为参数传入存储过程，给参数id指定的员工涨工资

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create or replace procedure  proc_updatesal(vempno in number,vmoney in number)
is 
	vsal number;  --声明变量记录当前工资数
begin
	select sal into vsal from emp where empno = vempno;
	dbms_output.put_line('涨薪前工资：'|| vsal);
	update emp set sal = vsal + vmoney  where empno = vempno;
	dbms_output.put_line('涨薪后工资：'|| (vsal+vmoney) );
	commit;
end;
--调用   方式一
call proc_updatesal(7369,1);
--方式二   最常用
declare

begin
	proc_updatesal(7369,1);
end;

18、存储函数

和存储过程没什么太大的区别，存储函数有返回值

语法

create [ or replace ] function 存储函数名 （参数名 in | out 参数类型，参数名 in | out 参数类型） return 参数类型

is | as

​ – 变量声明

begin

​ – 业务逻辑

end;

查询指定员工的年薪

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create or replace function func_getsal(vempno in number) return number
is
	vsal number
begin
	select sal*12 + nav(comm,0) into vsal from emp where empno = vempno;
	return vsal;  --返回值不能少
end;	

--调用
declare
	vsal number;
begin 
	vsal := func_getsal(7369);  --接收返回值
	dbms_output.put_line(vsal);
end;

--查询员工姓名以及年薪
select ename,func_getsal(empno) from emp;

使用存储过程实现 查询指定员工的年薪

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create or replace procedure proc_getsal(vempno in number,vsal out number)  --此处指定年薪为 out
is
begin
	select sal*12 + nvl(comm,0) into vsal from emp where empno = vempno;
end;	

--调用
declare
	vsal number;
begin 
	proc_getsal(7369,vsal);  --接收返回值
	dbms_output.put_line(vsal);
end;

19、触发器

语法

create [ or replace ] trigger

before | after

insert | update | delete

on 表名

– for each row 创建行级触发器时添加 当一条sql影响多行数据时，这句话表名这个触发器执行一次还是执行多次

declare

begin

end;

：old –表示旧的数据 更新前 ：old.sal 更新前工资

：new –表示新的数据 更新后 ：new.sal 更新后工资

例：插入数据之后输出一句话欢迎

1
2
3
4
5
6
create or replace trigger  tri_welcome
after insert on emp 
declare 
begin
	dbms_output.put_line('欢迎');
end;

不符合条件的不能插入

1
2
3
4
5
6
7
8
9
10
11
--周日不办理业务
create or replace trigger  tri_insert
before insert on emp 
declare
	date varchar2(10)
begin
	select trim(to_char(sysdate,'day')) into date from dual
	if date='星期日' then
		raise_application_error(-20001,'周日不办理业务，请明天再来');
	end if
end;
主键自增长
1
2
3
4
5
6
7
8
9
10
11
12
--1、创建序列   使用默认的设置就行
create sequence seq_primary_key_auto_increment;
--2、创建触发器
create or replace trigger tri_primary_key_auto_increment
before insert on person
for each row
declare

begin
	select seq_primary_key_auto_increment.nextval into :new.pid from dual;  --:new.pid是此次插入操作之后的pid的值
end;
--执行插入操作
相关文章
赏口饭吃吧！