SQL笔记
结构化和非结构化两大类
结构化数据库
mysql，oracle，sqlserver，db2，hive
结构化的数据：表格，日期，电话号码，地址，方便机器进行分析的
非结构化数据库
mongodb
非结构化数据：图片，视频，以二进制的形式存在
[!人为生成的非结构化数据]
文本文件：演示文稿，日志
电子邮件：半结构化，有元数据有内部结构
网站：bilibili
通讯：聊天，录音
媒体：mp3，mp4，照片
[!机器生成的非结构化数据]
卫星图像，天气数据，地形，大气数据，地震图像，交通传感器
结构化数据和非结构化数据有什么区别？
市面上结构化的数据存在很多非常成熟的分析工具，结构化数据机器分析起来非常方便，非结构化
数据目前来说分析起来还是比较复杂的，并且非结构化数据要比结构化数据复杂的多，数量也更
庞大，非结构化的数据占企业数据的80%以上，如果没有工具来分析这些数据的话，会对企业造成
非常大的损失
数据库还细分为两种：关系型数据库，非关系型数据库
关系型数据库：
表跟表之间，数据和数据之间可以联系起来做更复杂的关联操作的数据库，相当于
把一个大的数据聚合拆分开，需要什么就把什么拼一起用
mysql，oracle，sqlserver，db2
非关系型数据库：
就不是数据库，就是一个数据的集合，存储的数据可以是文档，表格，图片，视频
hbase，mongobd
[!目前市面上主流的数据库]
oracle：亿级别数据量 甲骨文
mysql：百万级数据量 甲骨文
sqlserver：千万级别数据量 微软
hive：百亿级别 facebook
[[sql]]语句
sqlplus
oracle数据库自带的界面，通过它来对数据库发出指令，书写sql语句
第三方工具
[[navicat]] 用来远程连接数据库的第三方工具
1 表中的数据类型
数值，字符串，日期
2 显示表结构，通过它来查看表中的数据类型
desc 用户名.表名;
desc hr.employees;
只能在sqlplus里敲
3 dual，伪表，做测试用的
dql 数据查询语言
select 显示哪些列，显示什么内容
from 从哪里，从哪个表取数据
查询hr下的employees表的所有内容
select *
from hr.employees;
查询hr下的employees表里的employee_id列，email列，salary列
select employee_id,email,salary
from hr.employees;
注意事项
1 sql语句大小写不敏感
2 sql可以写在一行或者多行
3 关键字不能被分行也不能被缩写
4 *代表显示所有内容
5 查询多列数据用逗号隔开
6 数据里所有的符号必须是英文状态下的符号
4 [[算术运算符]]
数字和日期使用的算术运算符
- [[加]]，- [[减]]，* [[乘]]，/ [[除]]
[!注意事项]
1 乘除的优先级高于加减
2 同一优先级运算符从左向右执行
3 括号内的运算优先执行
从员工表里提取名字，姓，年薪，每个人的年薪是多少
select first_name,last_name,salary*12
from hr.employees;
从员工表里提取名字，姓，日新，每个人的日薪是多少，一个月按30天算日
select first_name,last_name,salary/30
from hr.employees;
5 定义空值 null
空值是无效的，未指定的，未知的值，空值不是空格或者0
空值运算结果测试
select commission_pct+salary
from hr.employees
where employee_id=144
create table ceshi(id int,
num number);
insert into ceshi(id)values(1);
insert into ceshi(id,num)values(1,'15');
insert into ceshi(id,num)values(2,'10');
insert into ceshi(id,num)values(3,'');
insert into ceshi(id,num)values('','10');
select sum(num)
from ceshi
group by id
having id=1
6 空值在数学运算中的使用
包含空值的数学运算结果都是空值
从员工表中提取员工号，年薪乘提成，得到的是每个人年提成是多少
7 列/表的别名
重命名一个列/表，方便计算或者使用，加一个空格然后紧跟列名，还可以在列名和别名之间加一个
as，但是as不是必须的
8 重复行
默认状态下，查询会返回所有行，包括重复的行
9去除重复行
在select语句中使用关键字distinct去除重复行
distinct关键字必须紧跟select
如果distinct后面有多个列的话，那么就会去除3列都完全一样的行
10 在查询中过滤行
使用where子句，将不满足条件的行进行过滤，where代表要什么，要哪些
where子句紧随from子句
[!查询部门号是80的员工]
select * from hr.employees where department_id = 80
[[比较运算]]
= 等于
>大于
>= 大于等于
< 小于
<= 小于等于
<> != 不等于(两种写法)
工资小于等于3000的人的信息，显示员工号和工资
[!字符和日期]
1 字符和日期要包含在单引号中
2 字符大小写敏感，日期格式敏感
3 默认日期格式是DD-mon月-RR 24-4月-2024
4 日期跟日期只能相减，结果是两个日期相差的天数
5 日期跟数值之间可以加减，+1的话就是加一天，-1就是减一天
6 select后面可以用逗号，where后面不能用逗号，要用连接符 or and
7 sysdate显示当前系统时间
逻辑运算连接符
and 表示且的关系，两个条件需要同时满足
or 或的关系，两个条件满足其中一个就可以
[!找工资大于10000并且部门号是90的人]
select *
from hr.employees
where salary > 10000
and department_id = 90
[!找工资大于5000或者部门号是90的人]
select *
from hr.employees
where salary > 5000
or department_id = 90
其它比较运算
between … and … 在两个值之间(包含边界)
[!查询工资在5000到10000之间的人，包含边界]
select * FRom hr.employees where salary between 5000 and 10000;
in () 等于()里的值的，或者字符的
select *
FRom hr.employees
where job_id in ('SA_REP','IT_PROG','FI_ACCOUNT')
is null 是空值的
[!所有没有提成的人]
select *
FRom hr.employees
where commission_pct is null;
like 模糊查询
使用like寻找类似的值，模糊查找
1 查找条件可以包含字符或数字
2 %代表0个或多个字符(任意个字符)
3 _代表一个字符
4 %和_可以一起使用
escape转意符(只用来配合like使用)
可以设定一个转意符号，将like里这个符号后面的一位只表达为原意，针对_%的
select *
FRom hr.employees
where first_name like '%!_v%' escape '!'
找first_name里第二是小写s的人
SELect *
FRom hr.employees
where first_name like '_s%'
not 表示否，条件不满足什么的时候将其余结果全部显示
找first_name里第二不是小写s的人
SELect *
FRom hr.employees
where first_name not like '_s%'
找工资不在5000-10000的的人
SELect *
FRom hr.employees
where salary not between 5000 and 10000;
找工种不是AD_VP和FI_MGR的人
select *
FRom hr.employees
where job_id not in ('AD_VP','FI_MGR');
order by 子句
使用order by进行排序
asc 升序
desc 降序
[!注意事项]
1 order by 子句永远在sql语句的结尾
2 order by 子句如果不写升序还是降序的话默认是升序
3 order by 子句可以写，1，2 按结果的第几列进行排序
排序大小顺序：数字<大写字母<小写字母<中文
ascii码值
每个字符都有对应的ascii码值，在对比大小的时候，比的是ascii码值的大小
多个列排序
1 按照order by从左至右的顺序进行排序
2 可以使用不在select列表里的列进行排序
查询除90，60，100号部门外，job_id不是IT_PROG或AD_VP，
且月薪大于5000，年薪大于5万，有提成的人的全部信息，结果按部门号降序，工资降序，进行排序
函数
单行函数：
作用在每一行，返回一个值，分为五种类型：字符函数，数值函数，日期函数，转化函数、通用函数
*多行函数：*作用在多行，返回一个结果，聚合函数，组函数
单行函数
字符函数
-大小写控制函数
initcap() 结果首字母，空格或特殊符号的后一位变成大写，其余小写
lower() 结果全小写
upper() 结果全大写
–initcap 结果首字母，空格或特殊符号的后一位变成大写，其余小写
select initcap('AB CBD')
from dual;
–lower 结果全小写
select lower('ABCabc')
from dual;
–upper 结果全大写
select upper('ABCabc')
from dual;
GREATEST（聚合函数）返回一组值中的最大值。
这个函数可以应用于数值列或日期列，并且可以同时处理多个参数。
使用greatest函数的基本语法如下：
greatest(value1, value2, ..., valueN)
这里，value1, value2, …, valueN 是你想要比较的列或表达式。greatest函数将返回这些值中的最大值。
例如，如果你有一个名为employees的表，其中包含员工的salary和commission列，你可以使用greatest函数来找出每位员工的最大收入（基本工资或佣金）：
select employee_id, greatest(salary, commission) as max_income
from employees;
这将返回每名员工的ID和他们的最大收入。如果salary和commission都是数值类型，GREATEST将返回两者之间的较大值。如果它们是日期类型，GREATEST将返回日期较晚的值。
-字符控制函数
1 concat(a,b) 拼接函数
有两个参数，把两个参数拼在一起，可以通过嵌套函数拼接多个
select concat(concat('a','b'),'c')
from dual;
select concat(concat(concat(concat(concat(concat(first_name,last_name),'在公司工作了'),sysdate-hire_date),'天,赚了'),salary/30*(sysdate-hire_date)),'元')
from hr.employees;
2 length(a) 得到一个字符串里有多个字符
只有一个参数，字符串，结果是这个字符串有多少个字符
length(0.2) 结果是2，不是3，原因是length函数会把括号里的内容先转换为字符串再求长度，
oracle在存储小数的时候，如果是0.1，0.2这种，那么就会省略掉前面的0
3 lengthb()得到一个字符串里有多少个字节
根据字符集的不同，汉字可能是两个字节，可能是3个字节，符号字母数据都是1位
[!找出列里有汉字的数据]
select * from cs20240416_1 where length(a) != lengthb(a)
instr(a,b) 用来查一个字符串里的某个字符在这个参数从左向右数的第几位
两个参数，a参数是一个字符串，b参数是要查询的字符，结果返回b参数在a参数的第几位，
只返回从左往右数第一个遇到的指定字符的位置
4 instr(a,b,c,d)
四个参数，第一个参数还是字符串，第二个参数是要查的字符，第三个参数是以这个字符串从左向右
第几位开始找，可以写负数，代表从-1位开始找，从右往左数，第四个参数，是要找第几个出现的
第二个参数
select instr(‘eabcdefegee’,‘e’,1,2)
from dual;
补充函数
5 lpad(‘a’,b,‘c’) 左填充
6 rpad(‘a’,b,‘c’) 右填充
有三个参数，第一个参数是字符串，第二个参数是将这个字符串补充到多少个字节，第三个参数是
用什么补充
| 函数 | 语法及解释 | 示例 | 结果 |
|---|---|---|---|
TRIM | `TRIM([leading | trailing | both] [remstr] FROM str)` 去除字符串两端的空格或指定字符 |
SUBSTR | SUBSTR(str, pos [,len])从字符串的指定位置开始提取子字符串 | SUBSTR('Hello World', 7, 5)SUBSTR('Hello World', -5) | 'World''World' |
REPLACE | REPLACE(str, from_str, to_str)将字符串中的指定部分替换为另一个字符串 | REPLACE('Hello World', 'World', 'Everyone') | 'Hello Everyone' |
TRANSLATE | TRANSLATE(str, from_str, to_str)将字符串中的字符替换为另一个字符（与 REPLACE类似，但替换的是字符集） | TRANSLATE('12345', '123', 'abc') | 'abc45' |
| 去除一段字符串最左边或最右边的一个字符，不能去除中间的 | |||
| trim(‘H’ from ‘Helloworld’) |
[!去空格函数]
去除字符串里的左右两边的空格
trim(’ abc ')去除字符串里的左边空格
ltrim(’ abc ')去除字符串里的右边空格
rtrim(’ abc ')
[!截取函数]
substr(a,b,c) 截取函数
三个参数，第一个参数是要截取的字符串，第二个参数是从第几位开始截取，第三个参数是截取多少
位，第二个参数可以是负数，从倒数第几位开始，第三个参数可以不写，默认截取到最后一位
select substr('abcdefg',1,3)
from dual;
select substr('abcdefg',5)
from dual;
select substr('abcdefg',-5,2)
from dual;
[!替换函数replace]
replace(‘a’,‘b’,‘c’)
三个参数，第一个参数是字符串，第二个参数是要替换的字符，第三个参数是要替换成什么，
结果输出替换之后的字符串
select replace(' a be a b b ',' ','')
from dual;
select replace('abeabb''ab','!')
from dual;
---结果是 !e!b
[!替换函数translate]
translate(‘a’,‘b’,‘c’)
三个参数，第一个参数是字符串，第二个参数是要替换的字符，第三个参数是依次替换成什么，
逐一对应替换，如果第三个参数的位数比第二个参数多，多出来的字符是无效的，如果第二个参数
位数比第三个参数多，多出来的字符就被删除了
|| 拼接符号
select ‘你好’||‘今天’||‘星期三’
from dual;
ascii码
第一个字母对应的ascii值，汉字也是一样
ascii(‘字符串’)
将ascii码值转变为字符
chr(‘100’)
数字函数
[!-round(a,b) 四舍五入]
两个参数，第一个参数是数值，第二个参数是保留到小数点后几位，第二个参数可以是负数，
也可以不写默认保留到整数位
[!-trunc(a,b) 截断]
两个参数，第一个参数是数值，第二个参数是截断到小数点后几位，第二个参数可以是负数，
也可以不写默认截断到整数位
[!-mod(a,b) 求余]
两个参数，第一个参数是数值，第二个参数是除数，结果是余多少
日期函数
[!-months_between(a,b) 查两个日期相差几个月]
两个参数，都是日期，结果是两个日期相差几个月，大日期在前，小日期在后
[!-add_months(a,b) 向指定日期加上多少个月]
两个参数，第一个参数是日期，第二个参数是要加多少个月，也可以是负数，就是减多少个月
[!-next_day(a,b) 指定日期的下一个西方国家的周几是几月几号]
两个参数，第一个参数是日期，第二个参数是下一个西方国家的星期几，返回日期几月几号
[!-last_day]
last_day(a) 指定日期的当前月份的最后一天是几月几号
[!-extract]
extract(year|month|day from 日期) 取日期中的年、月、日
select extract(day from sysdate)
from dual;
[!时间日期加减]
numtodsinterval 在日期上加上或减去天day，时hour，分minute，秒second
numtoyminterval 在日期上加上或减去年year，月monthsysdate+numtodsinterval(3,‘day’) 在今天的基础上加3天
sysdate+numtoyminterval(3,‘year’) 在今天的基础上加3年
[!round(a,b) 日期的四舍五入]
两个参数，第一个参数是指定的日期，第二个参数可以是年，月，周，季度，天
yyyy年，以7月为基准，大于等于7月的找指定年下一年的第一天，小于7月的找指定年的第一天mm月，以16号为基准，大于等于16号的找下个月的第一天，小于16号的找当月第一天
day周 不考虑时分秒的话，以周四为基准，周四前的找上一个周日，周四以后包括周四找
下一个周日
考虑时分秒的话，以周三中午12点为基准，周三中午12点前的找上一个周日，周三中午12点以后包括周三中午12点找下一个周日iw周 国内的周一到周日
不考虑时分秒，是以周五为基准，周五以后包括周五的找下周一，周五之前的找上周一
考虑时分秒，是以周四中午12点为基准，周四中午12点之后包括中午12点找下周一，周四中午12点之前找上周一q季度，是以一个季度中间月的15号为基准，小于等于15号找本季度的第一天，大于15号找下一个季度的第一天 123 456 789 101112
dd天，以一天的12点为基准，中午12点之前找今天的0点，12点之后找明天的0点
[!trunc(a,b) 日期的截断]
两个参数，第一个参数是指定的日期，第二个参数可以是年，月，周，季度，天
yyyy年，找指定年的第一天
mm月，找指定月的第一天
iw周 找上一个周一
day周 找上一个周日
q季度，找当前季度的第一天 123 456 789 101112
trunc(入职日期,‘q’) = trunc(入职日期+2个月,‘q’)
dd天，找今天0点
去掉日期中的时分秒
select trunc(sysdate)
from dual
转换函数 转换数据类型
[!to_char(a,b) ]
将一个日期转换为字符串两个参数，第一个参数是日期，第二个参数是要转换成什么格式，结果数据类型是字符串
可以把日期转换成想要的格式，to_char(日期,‘day’) 得到这个日期是星期几,to_char(‘日期’,‘mmdd’)去
取日期中的月和日，0711就是7月11号,或者取年to_char(日期,‘yyyy’)
to_char(日期,‘w’)返回一个日期在一个月的第几个7天
to_char(日期,‘ww’)返回一个日期在一年的第几个7天 **to_char(日期,'iw')**一个日期在当前年的第几个自然周，从当前年的第一个星期一开始算
可以在结果上加上想要的字符或汉字，用双引号包起来然后同时包含在单引号里
select to_char(sysdate,'yyyy"年"mm"月"dd"日"')
from dual;
``to_char(a,b) 将一个数值转换为字符串
两个参数，第一个参数是数值，第二个参数是想要的字符串
9 表示对数字的占位，在小数点位，表示转换为对应的字符，如果没有则以0表示，在整数位,正常
进行填充，如果没有的话不填充字符
0 在对应位置返回对应的字符，如果没有则以0填充
select to_char(526.75645,‘0000.0000000000’)
from dual
$ 在结果上加一个美元符，最左或最后，不能加中间
L 本地货币符号
. 表示小数点
, 放在整数位，表示千分位符，作为分组符号使用，如果需要十分位，百分位都可以
注意事项
对于0和9而言，如果格式的位数没有数值的位数多，就会返回to_char(12345,‘9999’) 返回 #####
[!to_date(a,b) ]
将字符串转换成日期两个参数，第一个参数是字符串，第二个参数是要转换成什么格式的日期，结果是日期
yyyy-mm-dd hh:mi:ss 分别代表年,月,日,天,时,分,秒
可以在hh后见加24，就是改成24小时格式
select to_date(‘2021-01-01 15:33:00’,‘yyyy-mm-dd hh24:mi:ss’)
from dual
to_date(‘01-01’,‘mm-dd’) 取到的是月和日，然后自动填充当前年的年份
select to_date(‘01-01’,‘mm-dd’)
from dual
to_date(‘15’,‘dd’) 取到的日，然后自动填充当前年的年份和当前月
select to_date(‘01’,‘dd’)
from dual
to_date(‘7:30:00’,‘hh:mi:ss’) 取到时间，自动填充当前年当前月一号的指定时间
select to_date(‘7:30:00’,‘hh:mi:ss’)
from dual
[!to_number(a,b) ]
将字符串变成数值
select to_number(to_char(526.75645,'$999.99999'),'$999.99999')
from dual
–to_char(expr,fmt)返回值是字符
根据转换的对像，把to_char()归纳为以下几个用法
1)日期时间的转换
fmt可用的基础格式如下(针对日期的)：
yyyy-mm-dd hh24:mi:ss
to_char(timestamp'2024-3-12 14:29:21','yyyy-mm-dd hh24:mi:ss')
常用的fmt参数：
HH/hh12/hh24：返回一天的小时数，例to_char(sysdate,’hh’)
select to_char(sysdate,'hh24')
from dual;
MI:返回分钟数00-59，例to_char(sysdate,’mi’)
select to_char(sysdate,'mi')
from dual;
SS: 返回秒 (00-59)，例to_char(sysdate,’ss’)
SSSSS:返回午夜后的秒 (0-86399)，列to_char(sysdate,‘sssss’)
select to_char(sysdate,'sssss')
from dual;
AM or A.M. or PM or P.M. :返回正午标识(大写)，例to_char(sysdate,’AM’)
am or a.m. or pm or p.m. :返回正午标识(小写)，例to_char(sysdate,’am’)
Y,YYY:返回带逗号的年份数，例to_char(sysdate,’y,yyy’)
YYYY/YYY/YY/Y:返回年份数的后几位，例to_char(sysdate,’yyy’)
BC or B.C. or AD or A.D. :返回年标识，例to_char(sysdate,’BC’)
select to_char(sysdate,'BC'),
to_char(sysdate,'AD')
from dual;
Month:返回月份数，例to_char(sysdate,’month’) 8月 like ‘8月’
select to_char(sysdate,'month')
from dual;--3月
MM:返回月份，例to_char(sysdate,’mm’)
select to_char(sysdate,'mm')
from dual;--03
DAY:返回星期几，例to_char(sysdate,’DAY’)
select to_char(sysdate,'day')
from dual;--星期三
DDD: 一年里的日子(001-366)
DD: 一个月里的日子(01-31)
D: 一周里的日子(1-7；SUN=1)
select to_char(date'2024-3-13','d')
from dual;--4(3月13号这一天是星期三)
W:返回一个月里的周数(1-号是第1周，714号是第2周，1522号是第3周，2230号第4周)
select to_char(date'2024-3-13','w'),
to_char(date'2024-3-15','w')
from dual;
WW:返回一年里的周数
CC:返回世纪
select to_char(date'2024-03-13','cc')
from dual;--21，表示是21世纪
Q:返回季度
RM:返回罗马数字的月份（I-XII；I=JAN）－大写
{-
/
,
.
;
:
“text”}:大括号里面的这些符号也可用于fmt参数
[!这些格式可以组合在一起使用]
，例to_char(sysdate,'FMDay, HH12:MI:SS'), to_char(sysdate,'fmDdspth "of" Month YYYY fmHH:MI:SS AM') select to_char(sysdate,'FMDay, HH12:MI:SS'), to_char(sysdate,'fmDdspth "啦乾" Month YYYY fmHH:MI:SS AM') from dual `不管怎样组，所有的fmt参数都包含在一个''单引号里面' to_char(sysdate,'fmdy' 'hh24')--这样就不对 select to_char(sysdate,'fmdy' 'hh24') from dual; select to_char(systimestamp,'FMDay, HH12:MI:SS'), TO_CHAR(sysdate,'fmDdspth "啦乾" Month YYYY fmHH:MI:SS AM') from dual;
2)对数字的转换
用于数字转换的fmt格式化参数有：
fm:去除前后空格的
9:返回指定位数的值，例to_char(45.6782,’999.99’)
9表示的位数大于等于数字的位数
select to_char(45.6782,'999.99'),--' 45.68'
to_char(45.6782,'fm999.99'),--'45.68'
to_char(45.6782,'9'),--#####
to_char(-45.6782,'999.99'),--' -45.68'
to_char(0,'99.99'),--' .00'
to_char(0)--'0'
from dual;
0:返回前导0的值，例to_char(-0.314,’0.99’)
select to_char(-0.314,'0.99'),--'-0.314'
to_char(-0.314,'9.99'),--'-.31'
to_char(-0.314)--'-.314'
from dual;
,逗号:千位分隔符，例to_char(45327.45,’9,999.99’)
select to_char(45327.45,'9,999,999.99'),--' 45,327.45'
to_char(45327.4567,'FM9,999,999.99')--'45,327.46'
from dual;
S:返回带符号的值，例to_char(45327.45,’s9,999.99’)
如果是正数，那么在数字的前面加上+
如果是负数，那么在数字的前面加上-
select to_char(45327.45,'fms9,999,999.99'),--'+45,327.45'
to_char(-1234.56,'fms9,99,999.99')--'-1,234.56'
from dual;
L:返回本地货币符号，例to_char(246.77,’L999’)
select to_char(246.7734,'fml9,99,999')
from dual;
$:返回美元货币符号，例to_char(246.77,’$999’)
select to_char(246.7834,'fm$99,999,999.99')
from dual;
保留三年
[!保留3年年末+近13个月末+近31天]
delete from baoliu
where hire_date < trunc(sysdate) - 31 and hire_date <> last_day(hire_date)
or hire_date < add_months(trunc(sysdate),-13) and to_char(hire_date,‘mm-dd’) <> ‘12-31’
or hire_date < add_months(trunc(sysdate),-36)
通用函数
用来处理空值
nvl (a,b)
两个参数，如果第一个参数不是空值就返回第一个参数，如果是空值返回第二个参数
nvl2 (a,b,c)
==三个参数，第一个参数不为null，返回第二个参数，如果为null，返回第三个参数
coalesce(a,b,c,…)
==可以有多个参数，第一个参数为空，返回第二个参数，第二个参数也为空，返回第三个参数,…
nullif(a,b)
==两个参数，两个参数相同返回null，不相同返回第一个参数
case 表达式(条件表达式)
第一种写法
case … when … then … 如果…里有…那么…
when … then … 里有…那么…
when … then … 里有…那么…
else … end 其它的… 结束
第二种写法
case when 条件 then …
when 条件 then …
when 条件 then …
else … end
[!注意事项]
1 case when的then后面必须数据类型一致
2 case when有执行顺序，从上到下执行，满足第一个when then的，在第二个when then里
不被该变
decode
跟cese 列 when类似，只是把case when这些关键字用逗号替代
select decode(department_id,50,salary+500
,80,salary+1000
,90,salary+5000
,salary-10000),employee_id,salary,department_id
from hr.employees
select case department_id when 50 then salary+500
when 80 then salary+1000
when 90 then salary+5000
else salary-10000 end,employee_id,salary,department_id
from hr.employees
多表关联
–笛卡尔积，避免笛卡尔积
–多张表关联，n张表需要n-1个条件
我要100号员工的员工号,部门号，部门名，所在城市叫什么
表跟表的关联方式
92写法
1 等值连接
拿相等作为连接条件使两张表连接起来
where是连接条件，两张表满足什么条件的连接起来，where里用=的就是等值连接
2 不等值连接
不使用等于作为连接条件的查询
3 自连接
自己和自己连接
4 左外连接
(+)符号在右表
–左外连接
select *
from hr.employees t1,hr.departments t2
where t1.department_id = t2.department_id(+)
5 右外连接
(+)符号在左表
–右外连接
select *
from hr.employees t1,hr.departments t2
where t1.department_id(+) = t2.department_id
6 笛卡尔积
select *
from hr.employees t1,hr.departments
99写法
内连接 inner join
满足连接条件的保留，不满足的直接不要
select *
from hr.employees t1
inner join hr.departments t2
on t1.department_id = t2.department_id
左外连接 left join
满足连接条件的数据正常连接，保留左表所有不满足连接条件的数据，并且在右表里对应的位置
显示为空值
select *
from hr.employees t1
left join hr.departments t2
on t1.department_id = t2.department_id
右外连接
满足连接条件的数据正常连接，保留右表所有不满足连接条件的数据，并且在左表里对应的位置
显示为空值
select *
from hr.employees t1
right join hr.departments t2
on t1.department_id = t2.department_id
全外连接 full join
满足连接条件的正常连接，不满足连接条件的左表和右表都保留下来，对应的位置显示为空值
select *
from hr.employees t1
full join hr.departments t2
on t1.department_id = t2.department_id
叉集 cross join
直接产生笛卡尔积
select *
from hr.employees t1
cross join hr.departments t2
分组和分组函数(多行函数)
分组函数作用于一组数据，对多行数据进行操作，返回一个值
找出公司里工资最高是多少
select max(salary)
from hr.employees
组函数
max 求一组数据的最大值(最大)
min 求一组数据的最小值(最小)
max和min可以对任何数据类型进行操作，数值，日期，字符串
sum 求一组数据的和 (求和)
找出公司每个月要开多少支
select sum(salary)
from hr.employees
avg 求一组数据的平均值(求平均)
select avg(salary)
from hr.employees
sum和avg只能作用在数值上，日期sum没意义，字符串也sum不了，avg也是一样
count 计数，统计一列数据的数量
select count(employee_id)
from hr.employees
count什么数据类型都行，它只在乎有多少条数据
组函数忽略空值
所有组函数都会把你给它的列里的空值忽略掉，只对非空数据有效
统计有多少个工种，count如果想统计不重复数据的数量，先加distinct
select count(distinct job_id)
from hr.employees
主键外键
主键是唯一且非空的，外键列不唯一
但可以出现空值，主键和外键是一对多的关系，一个主键会对应多个外键
怎么判断一张表哪列是主键哪列是外键
通过desc查看表结构，看一下有没有注释，primary key是主键，foregin key是外键，要么就用数据字典通过语句
看，表里有几百万条数据，没有设置过主键外键，什么都没有怎么找主键，先count一下列，然后再count(distinct 列)
看结果是不是一致的，如果是一致的就可以确定它就是主键
count(主键) 表里有多少条数据，对主键count就是统计表里有多少条非空且唯一的数据
count(某一列) 统计该列里有多少条非空数据
count(1)和count(*) 统计表里有多少条数据，跟count(主键)是一样的
group by 子句
可以把表中指定的列进行分组，group by子句在where之后，如果没有where的话，不对数据进行筛选，那就在
from之后，直接对原表进行分组
使用group by之后，表结构就被破坏了，select里只能出现group by分组之后的列，或者被组函数包裹的列
select department_id,max(salary)
from hr.employees
group by department_id
分组函数可以嵌套，但是如果嵌套了组函数，select之后就不能出现任何其他列了，group by分组的列也不行
平均工资最高的部门的平均工资是多少
select max(avg(salary))
from hr.employees
group by department_id
对多列进行分组
having子句
having子句在group by之后，必须有group by才能用，对分组之后的数据进行过滤
平均工资高于8000的部门
语法
select 只能出现group by分组之后的列，或者被分组函数包裹的列
from 表
where 对原表数据进行筛选，分组前进行数据筛选
group by 以哪列进行分组
having 对group by之后的列进行筛选，只能出现group by里的列，或者组函数包裹的列
注意事项
1 where分组前，having分组后
2 分组时，select列里的数据行数是否一致，如果不一致就会报错
3 用函数操作之后的列也可以用来分组
select length(job_id)
from hr.employees
group by length(job_id)
having max(salary) > 5000
4 where里不能出现组函数
子查询
子查询是嵌入到另一个select语句当中的select语句，查询里还有一个查询
子查询都可以被放在哪
select,from,关联,where,and,having,case when,order by，基本上哪都能用，常用的是放在where里和having里
当作查询条件去使用，from里也比较常用
子查询分为两大类
单行子查询：只返回一个结果的子查询
多行子查询：返回多个结果的子查询
单行子查询
子查询会产生于一个结果，供主查询使用，一般用来解决需求里存在未知的值，比较符和之前用的都一样,>,<,=,!=
=,<=
谁的工资比Lex高
select *
from hr.employees
where salary > (select salary
from hr.employees
where first_name = 'Lex')
子查询要包含在括号内，要注意书写格式
--如果子查询返回的就是空值的话，那结果返回给主查询出来的就还是空值
select *
from hr.employees
where salary > (select salary
from hr.employees
where department_id > 500);
多行子查询
子查询查出来的结果是多行数据，会返回给主查询好多个值，比较符就不能是大于等于，会有一些多行子查询的
比较符
in 在多行子查询的结果里就可以
–查询在有15个员工以上的部门工作的人的信息
select *
from hr.employees
where department_id in (select department_id
from hr.employees
group by department_id
having count(*)>15)
not in 不在子查询的结果里的
注意事项
1 in 等于子查询结果集里的任意一个就可以，里面有空值的话不影响结果
2 not in 如果子查询的结果里有空值，那么主查询的结果就是空值
any 在< > = 运算符之后加一个any，小于大于等于子查询里的任意一个就行
比工种为IT_PROG任意工资高的员工
–比工种为IT_PROG任意工资高的员工
select *
from hr.employees
where salary > any (select salary
from hr.employees
where job_id = ‘IT_PROG’)
空值情况和in类似，满足子查询里的其中一个值就行，比最低的高
all 在< > !=运算符之后加一个any，小于大于子查询里所有的值
–比工种为IT_PROG所有人工资都高的员工
select *
from hr.employees
where salary > all (select salary
from hr.employees
where job_id = ‘IT_PROG’)
all要跟子查询集合里每个数据都要对比一次，都要满足条件才行，如果子查询的结果集里有空值的话，
那么结果也都是空值
大于all，大于最大的就行
大于any，大于最小的就行
子查询的多种用法
多列对比子查询
主查询和子查询列的位置要一一对应，数量也要一致,主查询的where后面要用括号包裹起来
查询跟 141号员工是同一个部门的并且是同一个经理的人
select *
from hr.employees
where (department_id,manager_id) in(select department_id,manager_id
from hr.employees
where employee_id = 141)
在from里面使用子查询
可以from一个子查询
select t1.department_id
from (select department_id,manager_id
from hr.employees) t1
在关联里用子查询
拿一张表关联一个子查询
select *
from hr.employees t1
inner join (select department_id,manager_id
from hr.employees) t2
on t2.department_id = t1.department_id
相关子查询
查询工资大于本部门平均工资的人
select *
from hr.employees t1
where salary > (select avg(salary)
from hr.employees t2
where t2.department_id = t1.department_id)
在select里使用子查询
只能返回一列结果，不能是多列
`要每个人的员工号，名字，部门号，部门名称
select employee_id,
last_name,
department_id,
(select department_name
from hr.departments t2
where t1.department_id = t2.department_id) 部门名
from hr.employees t1
子查询放在order by里
`员工表，但是要用部门名排序
select *
from hr.employees t1
order by (select department_name
from hr.departments t2
where t1.department_id = t2.department_id)
`子查询放在case when里
如果一个员工在公司平均工资最高的部门工作，那就输出降薪，如果一个员工在公司平均工资最低的部门工作
的话，那就输出涨薪，其余不变
`找出每个人通过的考试是哪次
select t1.tname,count(*) as 考试次数,count(t2.tname) as 通过次数,round(count(t2.tname)/count(*),2)*100||'%' as 考核通过率
from hr.sqlfenshu t1
left join hr.sqlfenshu t2
on t1.tname = t2.tname
and t1.inserttime = t2.inserttime
and t1.fr_grade > 60
and t1.sql_grade > 60
and t1.tomcat_grade > 60
and t1.fr_grade+t1.sql_grade+t1.tomcat_grade+t1.kettle_grade > 240
group by t1.tname
select tname,count(*) 考核次数,sum(case when fr_grade>60 and sql_grade>60 and tomcat_grade>60 and fr_grade+sql_grade+tomcat_grade+kettle_grade>240 then 1
else 0 end) 考核通过的次数,
round(sum(case when fr_grade>60 and sql_grade>60 and tomcat_grade>60 and fr_grade+sql_grade+tomcat_grade+kettle_grade>240 then 1
else 0 end) /count(*) ,3)*100||'%' 考核通过率
from hr.sqlfenshu
group by tname;
select tname,count(*) 考核次数,(select count(distinct t3.inserttime)
from (select t1.tname,t2.inserttime
from hr.sqlfenshu t1
left join (select *
from hr.sqlfenshu
where fr_grade > 60
and sql_grade > 60
and tomcat_grade > 60
and fr_grade+sql_grade+tomcat_grade+kettle_grade > 240) t2
on t1.tname=t2.tname) t3
where t3.tname=t4.tname) 考核通过次数,round((select count(distinct t3.inserttime)
from (select t1.tname,t2.inserttime where t3.tname=t4.tname)/count(*),2) 考核通过率
from hr.sqlfenshu t4
group by tname
[!数据库对象]
数据库的组成部分，数据库由哪些东西组成，表，视图，序列，索引，同义词，触发器
[!数据字典]
数据字典是由数据库自己创建的，在数据库当你进行操作的时候，数据字典会自动进行
记录和修改，储存一些系统相关的信息，它是数据库自行维护的一些表，包含数据库的
各种信息，数据字典也是数据库重要的组成部分，对用户来说是只读的
1 当前登录的用户，名下所有的表
select *
from user_tables
2 sys用户下所有的表
(sys是最高权限的用户，他能看库里所有的表，所以sys.all_tables等同于查看库里所有的表，只不过会告诉你，他们属于哪些用户)
select *
from sys.all_tables
3 查看system下所有的表
select *
from sys.all_tables
where owner = 'SYSTEM'
[!注意事项]
1 在数据字典里的数据都是大写的，如果查不到某个东西，先确定自己写的东西是不是对的
4 当前用户所有定义的数据库对象
select *
from user_objects
5 sys用户下所有的数据库对象
select *
from sys.all_objects
6 当前用户定义的表，视图，同义词和序列
select *
from user_catalog
7 sys用户所有定义的表，视图，同义词和序列
select *
from sys.all_catalog
[!数据字典]
DBA_：包含整个数据库范围内的对象的信息，例如DBA_TABLES 包含了数据库范围内
的所有表，用户需要具有一定权限才能查看ALL_: 当前登录用户有权限查看的对象的信息，例如all_tables包括了当前用户有
权限查看的所有表的信息user_；当前登录用户所拥有的对象的信息，例如user_tables包括了当前用户所拥有的表的信息
ddl语言
DDL 数据定义语言，是不可回滚的，用于定义和管理数据库结构或对象，如表、视图、索引等
create：用于创建新的数据库对象，如表、视图、索引等。
alter：用于修改现有的数据库对象结构。
drop：用于删除现有的数据库对象。
truncate：用于删除表中的所有数据，但保留表结构。
rename：用于重命名数据库对象。
comment：用于添加描述性注释到数据库对象。
[!建表的规则]
表名和列名的要求
1 必须以字母开头
2 必须在1-30个字符之间
3 必须只能包含A-Z,a-z,0-9,_,$,#
4 不能和其它数据库对象重名
5 不能是oracle的关键字
$abc 2abc _abc a-b a#b
建表语句的语法
create table 用户.表名 (列名 数据类型
,列名 数据类型
,列名 数据类型
,列名 数据类
,列名 数据类型);
表中的数据类型主要分为三大类，数值，日期，字符串
数值
int 只能储存整数，不能设置长度，最大长度不能超过22位
number(6) 可以存整数或小数，number(6)代表最多可以存6位的整数
number(5,3) 表示最多可以放5位数,小数点后3位，小数点前2位,加起来不超过5位，如果小数点后位数大于3位，就对多出来的位数进行四舍五入，小数点后小于3位的话用0补充到3位
字符串
char(20) 定长字符串，当存入abc3个字符的时候，abc占3位，不够20位的话，用17
个空格去填充，效率高，占空间
varchar2(20) 变长字符串 表示该字段最多能存入20个字节，假设数据是15个字节， 那就只占15个字节的空间，实际存储的是数据本身的长度，存汉字最多10个，英文符号数据这些可以存20个，效率低，但省空间,oracle特有的，并且官方推荐使用
nvarchar2(20) 变长字符串，跟varchar2类似，区别在于nvarchar2(20)可以存20个汉字或者20个字母，可以理解为汉字在nvarchar2里面只占一个字节
日期
date 可以放任意有效格式的日期型数据
char，varchar2默认储存的单位都是字节，nvarchar2储存的是字符
子查询建表
[! 通过子查询的结果建一张新的表]
语法create table 用户名.表名 as select employee_id,salary from hr.employees;这种方式不需要指定列数据类型，列的数据类型和之前的表保持一致
[!复制一张表]
语法create table 用户名.表名 as select * from hr.employees;
[!复制一张表，但是不包括里面的数据,复制一张表的表结构]
语法create table 用户名.表名 as select * from hr.employees where 1=2;
在select里加一个不可能成立的条件，建一张和原表字段一致的空表，复制原表的
表结构，数据类型，非空约束都会被复制过来，其它约束会消失。
[!子查询建表的同时修改列名]
create table 用户名.表名 as select employee_id as 员工号,salary as 工资 from hr.employees where 1=2;或者
create table 用户名.表名(新列名,新列名,新列名) as (子查询)create table hr.cs20240511_5(员工号,工资,部门号)as (select employee_id,salary,department_id from hr.employees); 前后列的数量和位置要一一对应，不能多也不能少
alter table 语句
[!加一个新列]
语法
alter table 表名
add (新列列名 数据类型);
alter table hr.cs20240511_1
add (余额 int);
[!加一个带默认值的列]
语法
alter table 表名
add (列名 数据类型 default 默认值是什么);默认值也可以在建表的时候添加
(列名 数据类型 default 默认值是什么)
[!修改一个列的数据类型]
语法
alter table 表名
modify (要改哪列 要改的数据类型)
要改变一个列的数据类型，如果列里有数据的话那就只能改同样的数据类型，
比方说char改成varchar2，如果要彻底改变数据类型，比方说number转date，那需要
列里没数据才可以
varchar2有数据的时候可以转成nvarchar2，反过来则不行，因为一个是按字节，一个
是按字符修改一个列的数据长度
[!修改数据类型长度]
语法
alter table 表名
modify (要改哪列 要改的数据类型(修改的长度));
[!给一个列加上默认值或者修改一个列的默认值]
语法
alter table 表名
modify (要改的列 数据类型 default 默认值)
[!删除默认值]
语法
alter table 表名
modify 列名 default null;alter table hr.cs20240511_1 modify id default null
[!删除一个列]
alter table 表名
drop column 要删除的列;
[!删除一个列]
alter table 表名
rename column 旧列名 to 新列名
[!该变数据库对象的名称]
alter table 表名 rename to 新表名;
alter table 视图名 rename to 新视图名;
删表
[!删表]
语法
drop table 用户名.表名;
清空表数据，只保留表结构
truncate table 用户名.表名;
TRUNCATE(trunctle)
是一种快速清空表数据的方法，它会删除表中的所有记录，但保留表结构和其依赖的对象（如索引）。TRUNCATE 操作不能回滚，执行后不能恢复数据。
trunctle table table_name;
[!注意] 注意
TRUNCATE 不能添加 where 条件，它会删除表中的所有数据
删除表中的指定记录（dml语句）
delete from table_name;
[!注意]
可以回滚：在事务提交前，可以回滚
删除操作触发触发器：会触发定义在表上的 DELETE 触发器。
较慢：由于逐行删除，且需要记录回滚信息，速度比 TRUNCATE 慢。
DELETE 语句可以添加 WHERE 条件，以删除符合条件的部分数据。
[!-delete和truncate的区别]
delete 可以回滚会产生回滚数据，长期操作会积攒水位线
truncate 不能回滚没有回滚数据，不会积攒水位线，效率更高
dml语句
DML 数据操纵语言，可以回滚，用于操作数据库中的数据，如插入、更新、删除和查询数据。常见的DML命令包括：
select：用于查询数据库中的数据
insert：用于插入新的数据记录到表中。
update：用于更新表中的现有数据记录。
delete：用于删除表中的数据记录。
merge：用于合并两个表的数据。
[!在表中插入一行数据]
insert into 表名(要往哪些列里插数据) values (跟前面要一一对应插入哪些列的
数据);
insert into hr.cs20240511_1(编号,id,学生姓名,入学时间) values (100,'Qa00001','king',to_date('2000-01-01','yyyy-mm-dd'));
[!向所有列插入数据]
insert into 表名 values (所有列都写清楚);
insert into hr.cs20240511_1 values (101,'Qa00002','明','男',13,'123456789w78945612',to_date('2000-01-02','yyyy-mm-dd'),700.5);
[!从其他表中复制数据]
insert into 表名
select 列名或者*
from 哪张表;
insert into hr.cs20240511_1
select department_id,job_id,job_id,'男',15,first_name,hire_date,500
from hr.employees;
语法
insert into 表名(列名,列名)
select 列名,列名
from 哪张表;
update语句 更新(修改)语句
[!-update]
语法
update 表名
set 列名=要改的值,
列名=要改的值
where 设定一个或多个条件如果不加where的话，那么全表数据都会被更新，一般情况下要写where
[!用子查询update语句更新数据]
把114号员工的job_id换成205号的job_id,再把salary也换成205号的工资
–把114号员工的job_id换成205号的job_id,再把salary也换成205号的工资
> update cs20240511_10
> set job_id = (select job_id
> from hr.employees
> where employee_id = 205),
> salary = (select salary
> from hr.employees
> where employee_id = 205)
> where employee_id = 114
[!相关更新，逐一多行更新]
将50，80，90号部门的员工的工资上涨到比本部门最高工资高1000，但是这三个部门
里当前工资最高的人不变，保持原薪资update cs20240511_10 t1 set salary = (select max(salary)+1000 from hr.employees t2 where t1.department_id = t2.department_id) where department_id in (50,80,90) and employee_id not in (select employee_id from hr.employees where (department_id,salary) in (select department_id,max(salary) from hr.employees group by department_id having department_id in (50,80,90)))
数据库事务
事务：一组操作，会使数据从一种状态变为另一种状态
一个或多个dml语句组成的一组
事务的四大特性
原子性：一个事务中的所有操作，要么全部提交，要么全部回滚
一致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏
隔离性：数据库允许多个并发事务同时对其数据进行操作和修改，隔离性可以防止多个
事务由于交叉执行导致的数据不一致
持久性：事务处理结束后，对数据的修改就是永久的
数据库事务由哪些东西组成
由一个dml语句开始
以一个ddl或者commit,rollback，关闭数据库，作为一个事务的结束
[!-tcl语言]
`事务处理语言，用来回滚事务，提交当前事务，存档当前dml操作
回到上一个存档点的状态或者回到事务开始之前
rollback to 存档点;
rollback;当前事务设置存档点
savepoint 存档点名;存档的位置只跟当前事务的状态有关，和写的位置没关，存档点只对当前事务有效，
如果事务结束了，那么存档点也就没有意义了提交本次事务的所有操作
commit;
提交和回滚前的数据状态
1 改变前的数据状态是可以恢复的
2 执行dml操作的用户，可以通过select语句查看目前的修改
3 其它用户不能看到当前用户所作的修改，直到当前用户结束事务
4 dml语句所涉及的数据行会被锁定，其他用户不能操作
提交后的数据状态
1 数据的更改已经被保存在数据库中
2 更改前的数据已经丢失
3 所有用户可以看到结果
4 锁会被释放，其他用户可以操作涉及到的数据
5 所有保存点被释放
[!NOTE]
根据现有的表更新另一张表，满足条件的进行更新，不满足条件的进行插入
merge into语句语法
> merge into 表1
> using 表2
> on (表1的列=表2的列)
> when matched then update set 表1的列=
> 表2的列
> when not matched then insert (要往表1的哪些列里插数据) values (表2的列)
注意事项
1 不能更新on条件里的列
2 可以只更新或者只插入将成绩的数据更新到成绩2里
> create table chengji2(name varchar2(20),
> 数学 int,
> 语文 int,
> 英语 int);
> insert into chengji2 values('小明',0,0,0);
> insert into chengji2 values('小黑',0,0,0);
> insert into chengji2 values('小白',0,0,0);
> create table chengji (name varchar2(20),
> 数学 int,
> 语文 int,
> 英语 int);
> insert into chengji values('小明',100,80,70);
> insert into chengji values('小黑',44,66,70);
> insert into chengji values('小白',50,80,55);
> insert into chengji values('小红',88,80,70);
> insert into chengji values('小张',77,33,70);
> select *
> from chengji2
>
> select *
> from hr.chengji
>
> merge into chengji2 t1
> using chengji t2
> on (t1.name=t2.name)
> when matched then update set t1.语文=t2.语文,t1.数学=t2.数学,t1.英语=t2.英语
> when not matched then insert (t1.name,t1.数学,t1.语文,t1.英语) values (t2.name,t2.数学,t2.语文,t2.英语);
数据恢复
查看delete删除的日志
日志文件路径
select *
from v$logfile;
查看delete的操作记录
select *
from v$sql
where sql_next like ‘delete%’
order by first_load_time desc;
查看最近的操作记录
select *
from v$sqlarea
order by first_load_time desc;
查看一张表某个时间的状态，看自己想恢复到几点时的状态
select *
from 表名 as of timestamp to_timestap(‘2024-05-14 9:55:00’,‘yyyy-mm-dd hh24:mi:ss’);
开启行移动功能
alter table 表名
enable row movement;
数据恢复，把一张表的数据状态恢复到几点
flashback table 表名 to timestamp to_timestap(‘2024-05-14 9:55:00’,‘yyyy-mm-dd hh24:mi:ss’);
恢复刚刚被drop删除的表
数据库执行删除或删表的时候会自动镜像保存到undo表空间,flashback table 去undo表空间把镜像的被删除的数据拉回来
flashback table 表名 to before drop;
表分区
建表分区
[[#表分区]]，是一种优化方式，当表中数据不断增大，查询数据的速度就会变慢，程序的性能就会下降，这时候就要考虑表分区 表进行分区之后，逻辑上表仍然是一张完整的表，只是将表的数据在物理上分到多个表空间里(物理文件)，这样在查询数据的时候，不至于每次都扫描整张表`
特性
1 数据量大的时候可以把表分的小一点，提高效率，便于管理
2 便于维护，某个分区的数据出现问题，只需要修复该分区就可以
3 提升查询效率，只针对某个分区进行查询
4 分区使用同一个表名，但是各个分区中的数据独立
建分区表
将一个表进行分区，各个区的表名还是原来的表名，可以直接对表的某个或某些分区进行操作
建一个分区表，表里至少有一个分区，如果要建多个分区用逗号隔开
[!建分区表]
语法
create table 表名 (列名 数据类型,…)
partition by range(以哪列进行分区)
(partition 分区名 values less than (小于多少是一个分区),
partition 分区名 values less than (小于多少是一个分区)…);举例
小于指的是小于具体的数值，比如小于5是第一个分区指定的这列里1 2 3 4就是第一个分区的数据，第二个分区小于10，5 6 7 8 9就是第二个分区的数据
[!注意事项]
1 插入分区列里的数据不能大于或等于当前分区的值，因为是小于多少是一个分区，不是小于等于
2 partition后面的分区名可以省略，分区名就由自己分配create table 表名 (列名 数据类型,…)
partition by range(以哪列进行分区)
(partition values less than (小于多少是一个分区),
partitionvalues less than (小于多少是一个分区)…);
[!查看某个分区的数据]
语法
select *
from 表名
partition(分区名);1 使用
USER_TAB_PARTITIONS可以查看分区名
这个视图显示当前用户拥有的分区表的分区信息2 使用
ALL_TAB_PARTITIONS
这个视图显示当前用户可以访问的所有分区表的分区信息，包括其他用户的表。3 使用
DBA_TAB_PARTITIONS
这个视图显示数据库中所有分区表的分区信息，通常需要具有管理员权限。
查看一个表有多少个分区(数据字典)
select *
from sys.all_tab_partitions
where table_owner=‘大写的表的拥有者’
and table_name = ‘大写的表名’;
[!每间隔多少自动分区]
语法
有数据才建立对应分区，没有数据就不建立对应分区
create table 表名 (列名 数据类型,… …)
partition by range(以哪列分区) interval(每间隔多少自动分成一个区)
(partition 分区名 values less than (小于多少是第一个分区),
…);
举例
create table cs20240514_2
(id int,
name varchar2(20))
partition by range(id) interval(5)
(partition p1 values less than (5));
> [!解释]
> 每间隔多少分一个区填5，小于多少是第一个分区填成5，第一个分区能容纳1234，目前就只有一个分区，当插入5的时候，
> 自动创建一个新分区，能容纳56789五个数,如果插入一个21，那就会立即创建一个新分区，该分区能容纳20,21,22,23,24
> 1234 56789 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
[!每隔多长时间自动分区]
语法
–numtoyminterval(3,‘month’) 在日期上加上或减去年,月
numtodsinterval(3,‘hour’) 在日期上加上或减去天时分秒create table 表名(列名 数据类型,… …)
partition by range(以哪列进行分区) interval(每隔多长时间自动分成一个区)
(partition 分区名 values less than(小于几月几号是第一个分区),
…)
举例
create table cs20240514_3
(id int,
rq date)
partition by range(rq) interval(numtoyminterval(1,'month'))
(partition p1 values less than(to_date('2024-02-01','yyyy-mm-dd')),
partition p2 values less than(to_date('2024-03-01','yyyy-mm-dd')));
列表分区（字符列）
语法
==create table 表名
(列名 数据类型,
… …)
partition by list(以哪列进行分区)
(partition 分区名 values (指定的值),
partition 分区名 values (指定的值),
…) ==
create table cs20240514_4
(id int,
name varchar2(20),
xueke varchar2(20))
partition by list(xueke)
(partition p1 values ('数学'),
partition p2 values ('英语'),
partition p3 values ('语文'));
插入的数据如果是数学会被分到一起，英语一起，语文一起，如果插入一个物理，直接报错，分区列里只能
插入和各个分区相同的数据
哈希分区(分桶)
分区的个数尽量是2的n次方个，为了数据能更均匀分布，未来列里插入的数据是未知的，没法提前做好分区规划，
那就分桶，没有规律的分区针对没有规律的数据，随机的
将数据插入到不同分区里，不受人为干预
==create table 表名
(列名 数据类型,
… …)
partition by hash(以哪列进行分区)
(partition 分区名,partition 分区名
…) ==
对分区表加分区
不能对建表时没有分区的表或自动分区的分区表加分区
alter table 表名
add partition 分区名 values less than(小于多少是一个分区);
alter table 表名
add partition 分区名 values(值);
加一个无限大的分区
alter table 表名
add partition 分区名 values less than(maxvalue);
从另外一张表里拉取数据并建立分区表
create table shuiguo
partition by list(shuiguo)
(partition fq1 values(‘苹果’),
partition fq1 values(‘橘子’),
partition fq1 values(‘牛奶’),
partition fq1 values(‘哈密瓜’),
partition fq1 values(‘香蕉’))
as select * from hr.shuiguo9
删分区
分区跟分区内的数据都删除
alter table 表名
drop partition(分区名)
删除数据保留分区
alter table 表名
truncate partition(分区名)
改分区名
alter table 表名
rename partition 旧分区名 to 新分区名
如何将普通表变为分区表
第一步：建一个新的分区表，表结构与原表相同
第二步：将原表中的数据导入到新表中
第三步：删除原表
第四步：将分区表表名改成原表表名
函数补充，开窗函数，列转行，行转列函数
| 函数 | 描述 | 示例 | 结果 |
|---|---|---|---|
| ceil(n) | 取大于等于数值n的最小整数，向上取整 | ceil(2.1) | 3 |
| floor(n) | 取小于等于数值n的最大整数，向下取整 | floor(2.9) | 2 |
| median(n) | 求中位数 (多行函数) | median([1, 2, 3]) | 2 |
| abs(n) | 绝对值 | abs(-2.1) | 2.1 |
| sign(n) | 数字n大于0返回1，小于0返回-1，等于0返回0 | sign(-2) | -1 |
| rowid | 每行数据的物理地址，类似于每行数据的身份证 | ||
| rownum | 标序号，where筛选条件里要用<或者<=，不能大于或者= | ||
| 举例 |
select *
from hr.shuiguo9 t1
inner join hr.shuiguo9 t2
on t1.rowid = t2.rowid
[!-pivot 行转列]
pivot 行转列函数，转成之后的表叫数据透视表，case when也可以做到语法
select *
from (select 列名,列名…
from 表名)
pivot(组函数(参数1) for 参数2 in (参数3));
注意事项
1 第一个参数要把原表哪列转换成一行一行的数据，把什么数据放在中间，第一个参数需要被组函 数包裹
2 第二个参数是转换之后的数据，按什么分列，用原表的哪一列进行分列
3 第三个参数是用第二个参数列里的哪些数据变成新列，如果按学科就是 in (‘语文’,‘数学’,‘英语’)
4 子查询里的select后面只放需要用到的列，多余的不要
5 参数1和参数2填完之后，如果还有剩余的列，就相当于按照剩余的列进行分组，如果剩的是名 字，那就是对名字分组
6 第三个参数的in里面的大小写需要注意
将每个人的成绩转成数据透视表
select s_name,nvl(语文,0) as 语文,nvl(数学,0) as 数学,nvl(英语,0) as 英语
from (select s_name,c_id,s_or
from hr.stu t1
left join hr.score t2
on t1.stu_id = t2.stu_id)
pivot(max(s_or) for c_id in (1 语文,2 数学,3 英语))
开窗函数
开窗函数都是用在select里的，想使用它的结果就要再套一层子查询
[!组内不考虑并列依次排序]
row_number()over(partition by 以哪列或者哪些列进行分组 order by 按哪些列
进行排序 desc|asc)
举例一：求每个部门工资前10的员工
select *
from (select employee_id,department_id,
salary,
row_number()over(partition by department_id order by salary desc) rw
from hr.employees)
where rw <= 10
举例二：可以不写partition by不分组，整表排序,order by是必须的
select employee_id,department_id,salary,row_number()over(order by salary desc)
from hr.employees
[!组内考虑并列跳跃性排序]
rank()over(partition by 以哪列或者哪些列进行分组 order by 按哪些列
进行排序 desc|asc)比方说排第二的有两个人并列。那就是1224 没有3直接跳到4 ，也可以不分组
[!组内考虑并列按顺序排序]
dense_rank()over(partition by 以哪列或者哪些列进行分组 rder by 按哪些列
进行排序 desc|asc)比方说排第二的有两个人并列。那就是1223 也可以不分组,order by是必须的
偏移函数
[!向下偏移]
lead(偏移哪列,向下偏移几位向下找几位)over(partition by 以哪列或者哪些列进行分组
order by 用什么排序)lead后面的()里，还可以有第三个参数，如果有空值或者取不到值了的，填什么，
但是数据类型要保持一致
也可以不分组,order by是必须的
把第一个参数的第一行数据向下的第几位找出来，找出来之后，再把第二行数据向下的
几位找出,以此类推
select employee_id,department_id,salary,first_name,lead(first_name,5)over(order by salary desc)
from hr.employees
[!向上偏移]
lag(偏移哪列,向上偏移几位向上找几位)over(partition by 以哪列或者哪些列进行分组
order by 用什么排序)
聚合开窗函数
组内一行一行累加，然后显示组内之和，下一组再重新加
sum(把什么东西加起来)over(partition by 用什么分组 order by 用什么排序)
[!整表求和]
跟sum差不多，但是数据对等了，每条数据之后会有一个总和
sum(把什么东西加起来)over()
[!组内求和，在每条数据后面显示组内之和]
sum(把什么东西加起来)over(partition by 用什么分组)
[!组内累加计数]
count(对什么计数)over(partition by 用什么分组 order by 用什么排序)
[!组内计数，在每条数据之后显示整组总数]
count(对什么计数)over(partition by 用什么分组)
[!考虑并列整表跳跃性累加计数]
count(对什么计数)over(order by 对什么排序)
[!组内递增求平均值]
avg(以什么求平均值)over(partition by 以什么分组 order by 用什么排序)
[!求组内平均值，在每条数据之后显示整组的平均值]
avg(以什么求平均值)over(partition by 以什么分组)
[!整表递增求平均工资]
avg(以什么求平均值)over(order by 用什么排序)
[!组内求最大值，在每行数据之后加上组内的最大值是多少]
max(以什么找最大)over(partition by 以什么分组)
[!整表求最大值，]
在每行数据之后加上最大值在
max(以什么找最大)over()
[!组内求最小值，在每行数据之后加上组内的最小值是多少]
min(以什么找最大)over(partition by 以什么分组)
[!整表求最小值，在每行数据之后加上最小值]
min(以什么找最大)over()
约束
用来防止无效的垃圾数据进入表里，给表或者列强制定义一些规则，不满足这些规则
的数据不能被插入到表里，可不可以插入重复数据或者空值，定义主键列外键列，
约束可以在建表的时候添加，也可以后添加，但是建好表之后就不能对原有的约束
进行修改了，只能添加或者删除，约束分为表级约束和列级约束，列级约束只能作用
在列上，表级约束是将一个约束同时作用在多个列上，表级约束是单独声明的，但是
效果是一样的只是写的位置不一样，一般情况下一个列只加一个约束
分为6种约束
非空约束 not null
唯一约束 unique
主键约束 primary key
外键约束 foreign key
检查约束 check
默认值 default
建表时进行列级约束或者表级约束的写法
create table 表名
(列名 数据类型 constraint 约束名 什么约束,
列名 数据类型 constraint 约束名 什么约束,
列名 数据类型,
constratint 约束名 什么约束(字段名),
constratint 约束名 什么约束(字段名));
constratint可以给约束命名，不是必须的，最好写上，一般情况下用表名_列名_约束
缩写，employees_employee_id_pk
not null 非空约束
对一列进行约束，表示该列不能出现空值，只能出现非空数据
非空约束只能定义在列级，不能定义在表级
create table 表名
(列名 数据类型 constraint 约束名 not null,
列名 数据类型);
如果查数据字典或者查看表结构，里面关于表的状态，是否有非空约束里，没有y
代表有非空约束，有y就是没有非空约束，允许出现空值
关于约束的数据字典
p:主键 r:外键 u:唯一约束 c检查约束 非空也标为c
查看数据库里所有的约束
select *
from sys.all_constraints
查看当前用户下的所有表的约束
select *
from user_cons_columns
数据库里所有列的约束
select *
from all_cons_columns
unique 唯一约束
作用在某列上，该列里不能出现重复的数据，但是允许出现空值，多个空值也是允许的
create table 表名
(列名 数据类型 constraint 约束名 unique,
列名 数据类型,
constraint 约束名 unique(列名),unique(列名));
primary key 主键约束
指定一个列为主键，主键是非空且唯一的列，并且跟表里的内容相关，一个表只能
有一个主键，一个主键可以拥有多个外键
create table 表名
(列名 数据类型 constraint 约束名 primary key,
列名 数据类型,
constraint 约束名 primary key(列名)–指定哪列为主键,
constraint 约束名 primary key(列名,列名)–指定哪些列为联合
主键);
联合主键只能建立在表级，联合主键一般用于当一列作为主键不能确保数据的唯一性
时，增加更多的列一起作为主键，确保唯一性，如果是联合主键，那么外键也必须是
联合外键，主键约束和外键约束可以建立在列级，但是可能会出问题，尽量写在表级
foreign key 外键约束
用于定义主表和从表之间的关系的，有主键才有外键，没有主键那就外键这个说法，
外键一定是依赖于另一张表的主键的，而且外键列里的数据只能出现跟主键列一样的
数据或者空值，不能出现其它数据，外键必须有另一张主键表并且里面有主键列才可以
建立，先有主键后有外键
create table 表名
(列名 数据类型 constraint 约束名 references 表名(主键列列名),
列名 数据类型,
constraint 约束名 foreign key(指定哪列为外键) references 另一张
表的表名(主键列列名));
级联删除
外键列的数据可以随便删，但是主键列里某一条数据目前还在其它外键列里存在的，
就不能删除，如果主键里的数据目前就只有主键有，其它外键里没有这条数据，就可以
删除
on delete cascade
加载建立外键的语句后面，让外键的列跟主键列进行连接，当主键的某条数据被删除
之后，外键里的这条数据也被删除，
但是单独删除外键不影响主键
级联置空
on delete set null
加载建立外键的语句后面，让外键的列跟主键列进行连接，当主键的某条数据被删除
之后，外键里的数据不会被删除，但是会被填上空值
check 检查约束
给一列加一个条件约束，满足某个条件的数据才可以被加进来。类似于加一个where
条件，可以设定任何条件> < between，函数处理的结果，但是要用括号包起来
在列级约束的时候，不能声明其他列，
在表级可以给任意列加约束，并且可以拿同表的其它列作为条件进行对比
create table 表名
(列名 数据类型 constraint 约束名,
name 数据类型 constraint 约束名 check(length(name)<2),
constraint 约束名 primary key(列名)–指定哪列为主键,
constraint 约束名 primary key(列名,列名)–指定哪些列为联合
主键);
create table cs20240516_4
(id int,
name varchar2(20) constraint cs20240516_4_name_c check(substr(name,2,1)=‘e’));
添加约束
如果表的某字段已经出现了违反约束的数据，就不能建立约束，比如列里已经有
重复数据了，就不能建立唯一约束
alter table 表名
add constraint 约束名 约束类型(哪列);
alter table 表名
add constraint 约束名 check(sex in(‘男’,‘女’));
添加not null约束需要用alter table modify语句，add constraint不行
alter table 表名
modify (列名 数据类型 not null)
modift语句也可以用来加其它的约束，加主外键都可以
alter table 表名
modify 约束名 (sex check(sex in(‘男’,‘女’)));
alter table 表名
modify 约束名 (sex unique);
给多个列加约束
alter table 表名
modify 约束名 (sex unique,
constraint 约束名 列名 not null,
…);
默认值跟其它约束同时添加的话，默认值的约束要写在前面
alter table 表名
modify 约束名 (sex default ‘男’ not null);
alter table cs20240516_4
modify (constraint ys1 sex default ‘男’ not null);
删除约束
alter table 表名
drop constraint 约束名;
不能删除有外键列的主键约束
删除表的主键约束
alter table 表名
drop primary key cascade
只删除主键约束，不删除主键列里的数据
删除主键约束的同时，所有依赖于主键约束的外键约束也同时被删除
强制删除表（有主见不能被删除的表也能被删除）
正常删除一张表的时候，该表的所有约束也都会消失，但是如果里面的主键被
某个外键使用了，那就不能删除这张表，这个时候可以用强制删除语句，删除主键
表的同时所有依赖于这张表的外键约束也都被删除
drop table 表名 cascade constraint
无效化约束
alter table 表名
disable constraint 约束名
激活约束
激活约束之前必须保证列里没有不满足约束条件的数据，如果有不能激活
alter table 表名
enable constraint 约束名
表，视图，序列，索引，同义词，函数，存储过程
视图
视图是一个虚表，就是不存在的表，存储在数据库数据字典里的select语句，建立视图
需要依赖于一张表，被依赖的这张表叫做基表
视图和表的区别
[!视图和表的区别]
1 视图不占物理空间
表需要占物理空间，一张表，有10个字段，有500万条数据，大概会有2个g，视图只是
一个select语句2 视图不可以加索引
索引增加查询效率的，视图不能加索引3 视图可以简化查询
可以对很多张表的关联状态做一个视图，以后再查询的时候直接查询这个视图就可以
了，不用每次写很多代码4 使用视图利于数据安全性
对数据进行脱敏，将敏感数据隐藏起来，外人想查看表里的数据的时候，只给他看视图
不给原表
视图分为两种
简单视图和复杂视图
简单视图：只有一张表，只对一张表建立视图，而且里面没有函数，没有分组，
可以对这个视图进行dml操作
复杂视图：可能由
一个或多个表组成，里面有函数，分组，这种情况下视图比较复杂，
dml一般情况下是操作不了的，但是有的情况可以
1 对视图进行修改就是对基表进行修改
2 基表如果被修改所有对其依赖的视图都会被修改
3 视图不能加索引，不能加约束
4 对基表加列不会影响已有视图，已有的视图更新不了
5 只能对视图进行dml操作，不能进行ddl
6 视图是为了简化开发，物化视图是为了提高性能
[!创建视图]
create view 视图名
as
select 列名，列名…
from 表名;
建立视图的时候变更列名
位置和数量要一一对应
create view(新列名,新列名)
as
select 列名，列名
from 表名;
create view v_employees2(员工号,名字)
as
select employee_id,first_name
from hr.employees;
[!查看视图]
select *
from 视图名;
关于视图的数据字典
数据库里所有的视图
select *
from sys.all_views
当前用户下的所有视图
select *
from user_views
删除视图
drop view 视图名;
视图有重名的则替换已有视图，如果没有则直接建立新视图
create or replace view 视图名
as
select *
from 表名;
对一个不存在的表建立视图，未来有这个表的时候，直接把数据拉过来
create force view 视图名
as
select *
from 暂时不存在的表名
建立一个只读视图
可以查看但是不能修改的视图
create view 视图名
as
select *
from 表名
with read only
检查视图
建立视图的时候加一个where子句，对视图进行dml操作时，必须要满足建立视图
时的where条件(不影响delete)
create view 视图名
as
select *
from 表名
where 条件
with check option
问题
1 验证视图能否作为基表
2 视图能否关联
3 视图能否关联后取其中部分字段再建立新的视图
4 表与视图关联的结果能否作为一个新视图的基表
对视图进行dml
正常是可以对视图进行dml，insert,update,delete，如果是简单视图的话都可以，
如果是复杂视图的话，不一定可不可以
在视图上进行dml有以下限制
1 如果修改违反了基表的约束条件，则无法更新视图
2 如果视图包含distinct关键字，聚合函数，rownum，set运算符，group by子句
则无法进行dml操作
3 如果视图里用连接符号||对列进行连接了，可以删除，不能更新，插入
键值保留表和非键值保留表
先建立两张表，stu_1 id 是主键 dep_1 depid 是主键
create table stu_1
(id int,
name varchar2(20),
depid varchar2(20),
constraint zj primary key(id));
create table dep_1
(depid varchar2(20),
depname varchar2(20),
constraint zj2 primary key(depid));
insert into stu_1 values (1,‘张三’,‘001’);
insert into stu_1 values (2,‘李四’,‘002’);
insert into stu_1 values (3,‘王五’,‘003’);
insert into dep_1 values (‘001’,‘中文系’);
insert into dep_1 values (‘002’,‘数学系’);
insert into dep_1 values (‘003’,‘英语系’);
select *
from stu_1
select *
from dep_1
create view v_dep
as
select id,name,t1.depid did,t2.depid,depname
from stu_1 t1
inner join dep_1 t2
on t1.depid = t2.depid
id所在的表叫做键值保留表
表2叫做非键值保留表
对多表关联的视图进行dml有一个规则
只能对键值保留表进行更新操作，不能对非键值保留表进行更新操作
完全不可以插入，但是可以删除，删除只影响键值保留表的基表数据，非键值
保留表的基表数据不受影响
查看视图里的列能不能更新删除插入，查看键值保留表的状态
select *
from dba_updatable_columns
where owner = ‘大写的用户名’
and table_name = ‘大写的视图名’
关于建立视图时使用了函数或者表达式的视图
要取好别名，表达式，函数，分组组函数，都要取好别名才能建立
create view st_2
as
select id,upper(name) newname
from st_1
物化视图
实体化视图，物化视图
视图上本质上是一个查询语句，查询视图的时候就是查询基表，
如果视图在创建的时候
有很多关联关系，很多条件，那么性能就不行了，视图是为了简化开发，但是效率低下
因为视图只是一个sql语句，常规的优化手段，加索引，分区表都用不了，物化视图
可以避免效率低下的问题，并且保留视图的降低sql复杂性，提高安全性的功能
把视图物理化，变成一张真正的表，视图是虚拟的，物化视图是一张实实在在的表
视图只是一个sql语句，不存在数据更新的问题。物化视图是不一样的，它已经落地
成表了，如果不更新的话，数据永远保持原样，物化视图是基于视图的思路，进一步
把视图的查询结果物理化成为一张实表，且拥有表的一切特性，可以加索引，可以分区
也会占用物理空间，物化视图是一个用空间换时间，牺牲实时性，换取效率的方案
物化视图的缺点
1 实时性欠佳
2 只能刷新，无法增删改数据
3 需要管理好，避免数据爆炸
物化视图相当于对一个表建立物化视图时，保留当下表的状态，快照，不手动刷新
就永远都不会变
什么时候建立物化视图
在基表没有很多dml操作的时候，并且查询基表需要消耗大量的资源可以建立物化视图
如果天天改的话，没事就要变动，就不要建立物化视图
建立物化视图
create materialized view 视图名
as
select *
from 视图名
手动刷新物化视图
原表有修改时像刷新物化视图就用这个，但是要在命令窗口敲
原表的修改要commit之后再刷新
exec dbms_mview.refresh(‘视图名’);
当基表进行dml操作，commit之后自动刷新的物化视图
表要有主键，先建立一个有主键的表，然后对主键建立物化视图日志，再建立一个
即时刷新的物化视图
1 对一个主键表的主键建立物化视图日志
create materialiazed view log on 表名 with primary key
2 建立一个commit之后及时刷新的物化视图，提交dml操作之后立即刷新，但是依赖于
主键
create materialized view 视图名
refresh fast
as
select *
from表名
create materialized view v_stu_1
refresh fast
on commit
as
select *
from stu_1
如果没有主键的表可以用rowid来实现
create materialized view log on 表名 with rowid;
语法
create materialized view v_stu_4
refresh fast
on commit
with rowid
as
select *
from stu_1
每隔多长时间自动刷新的物化视图
create materialized view 视图名
build immediate
refresh force
on demand
start with 设置开始时间
next 下次刷新时间
as
select *
from 表名
create materialized view vv_stu_1
build immediate
refresh force
on demand
start with sysdate
next sysdate+2/1440
as
select *
from stu_1
定时刷新，每天凌晨3点自己刷新
开始时间sysdate
next to_date(concat(to_char(sysdate+1,‘yyyy-mm-dd’),’ 03:00:00’),‘yyyy-mm-dd hh24:mi:ss’)
create materialized view vv_stu_1
build immediate
refresh force
on demand
start with sysdate
next to_date(concat(to_char(sysdate+1,‘yyyy-mm-dd’),’ 03:00:00’),‘yyyy-mm-dd hh24:mi:ss’)
as
select *
from stu_1
删除物化视图
drop materialized view 视图名
删除表的主键物化视图日志
drop materialized view log on 表名
PL/SQL语言
sql：结构化查询语言，基本上每个数据库都通用，普通的sql没法处理比较复杂的逻辑
plsql：oracle数据库从sql语言上扩展出来的一种编程语言，过程化查询语言，功能更强大，并且可以嵌入sql
语句，在plsql里之前学的sql语言都可以使用，在其基础上可以定义一些变量，循环，常量变量，错误处理
plsql developer ：过程化查询语言开发工具
[!PLSQL程序块的结构]
declare –
非必须
声明部分: 在此声明变量，常量，游标，数据类型等
begin –必须
执行部分：写逻辑的地方，要执行的sql语句和plsql语句
exception –非必须
异常处理：处理异常的地方，语句运行中报错的怎么办
end; –必须
begin
dbms_output.put_line(‘来昆明没你好果子吃’);
end;
–输出
在括号里填你想要输出的东西，输出之后换行
dbms_output.put_line()
函数的处理结果
begin
dbms_output.put_line(length(‘昆明’));
end;
不能输出多个值，括号里只能出现一个参数
begin
dbms_output.put_line(length(‘昆明’),‘你好’);
end;
[!注意事项]
1 在程序块里每写完一个语句，最后都要以分号结束
2 如果不需要声明任何东西，那么declare可以不写，没有异常处理就可以不写exception，只有begin和end是
必须的
3 一个begin里可以写多个语句
4 最后的end后面的分号千万不要忘
5 一般情况下输出日期的时候都要加个to_char(日期,‘yyyy-mm-dd’)，不加的话会显示默认日期格式
–输出今天星期几
begin
dbms_output.put_line(‘今天’||to_char(sysdate,‘day’));
end;
不换行将内容存放在内存中但不输出
dbms_output.put()
begin
dbms_output.put(‘今天’);
dbms_output.put(‘星期一’);
end;
将内存中的内容全部输出之后换行
dbms_output.new_line
begin
dbms_output.put(‘今天’);
dbms_output.put(‘星期一’);
dbms_output.new_line;
end;
输出之后换行，会将储存在内存中的内容和本次输出的内容一起输出
begin
dbms_output.put(‘今天’);
dbms_output.put(‘星期一’);
dbms_output.new_line;
dbms_output.put_line(‘小明不上学’);
end;
–声明变量
在declare里先写一个变量名 空格 变量的数据类型，可以声明一个变量，数据类型是什么
declare
v_a int;
注意事项
1 declare里面的变量命名，不能跟表里的列名或者oracle的关键字相同，不然可能会得到不正确的结果
2 变量一般以v_开头
3 如果想在程序块里使用一个变量，必须有三步，声明，赋值，使用，缺一不可
4 declare里声明的变量的个数，可以比实际用到的多，声明之后可以不用
5 如果一个变量没有被赋值的话，那么默认就是空值，如果输出会看到一个空值
declare
v_a varchar2(20);
begin
dbms_output.put_line(v_a);
end;
[!:=为变量赋值]
变量在刚声明好之后，变量里存的是空值，可以对其:=赋值，将一个内容存储在这个变量里
把今天的日期赋值给变量
declare
v_a date;
begin
v_a:=sysdate;
dbms_output.put_line(to_char(v_a,‘yyyy-mm-dd’));
end;
declare
v_a varchar2(20);
begin
v_a:=sysdate;
dbms_output.put_line(to_char(v_a,‘yyyy-mm-dd’));
end;
:=赋值的后面加&
手动设定一个变量，&跟后面的参数不能有空格，如果是字符串的话
要加单引号，也要把&包含在内
declare
v_ming varchar2(20);
begin
v_ming:=‘&名字’;
dbms_output.put_line(v_ming);
end;
declare
v_ming int;
begin
v_ming:=&数值;
dbms_output.put_line(v_ming);
end;
如果有两个手动设定变量，&后面不能写一样的名字，要区分开
[!手动设定变量]
举例
declare
v_ming varchar2(20);
v_xing varchar2(20);
begin
v_ming:=‘&名字’;
v_xing:=‘&姓’;
dbms_output.put_line(v_ming);
dbms_output.put_line(v_xing);
end;
注意事项
1 赋给变量的值，长度不能超过设定变量数据类型时的长度
declare
v_ming varchar2(2);
begin
v_ming:=‘&名字’;
dbms_output.put_line(v_ming);
end;
变量可以被再赋值，对已有值的变量再进行赋值，会覆盖掉之前的值
declare
v_ming varchar2(10);
begin
v_ming:=‘小明’;
v_ming:=‘小李’;
dbms_output.put_line(v_ming);
dbms_output.put_line(v_ming);
end;
–声明常量
不能被再赋值，里面的值是固定的，常量必须在declare里赋值
在declare里写，常量名 constant 数据类型:=赋值
[!申明常量举例]
declare
v_a constant int:=10;
begin
dbms_output.put_line(v_a);
end;
用常量已知半径5米求面积，输出面积=**平米
declare
v_p constant number(10,2):=5;
v_r constant number(10,2):=5;
begin
dbms_output.put_line(‘面积=’||v_pv_rv_r||‘平米’);
end;
[!into赋值]
在begin里将select查询的结果赋值给一个变量，在select语句的后面使用
查看100号员工的工资是多少
declare
v_a int;
v_b int;
begin
v_a:=100;
select salary into v_b
from hr.employees
where employee_id = v_a;
dbms_output.put_line(v_b);
end;
也可以没有v_b
declare
v_a int;
begin
v_a:=100;
select salary into v_a
from hr.employees
where employee_id = v_a;
dbms_output.put_line(v_a);
end;
[!注意事项]
1 select不能单独出现在begin里，有select就必须有into赋值，将select的结果赋值给一个变量，否则
会报错select缺少into子句2 into将select的结果赋值给一个变量的时候，select的结果必须是单行的，不能给变量赋值多行数据
3 一个select里只可以写一个into，但是可以将多个列的结果赋值到多个变量上，要赋值的变量放在into后面跟
select里面列的位置和数量一一对应
declare
v_a int;
v_b varchar2(20);
begin
v_a:=100;
select salary,first_name into v_a,v_b
from hr.employees
where employee_id = v_a;
dbms_output.put_line(v_b||‘的工资是’||v_a);
end;
4 变量可以进行变量传递，常量不能用into再赋值
–120号员工的薪资是***
–隶属于***部门
declare
v_a int;
v_b int;
v_c int;
begin
v_a:=120;
select salary,department_id into v_b,v_c
from hr.employees
where employee_id = v_a;
dbms_output.put_line(v_a||‘号员工的薪资是’||v_b);
dbms_output.put_line(‘隶属于’||v_c||‘部门’);
end;
–输入一个员工号，输出***号员工在***(哪个城市)工作，每个月赚***元
declare
a int;
b varchar2(20);
c number(10,2);
begin
a:=‘&员工号’;
select t1.employee_id,t3.city,t1.salary into a,b,c
from hr.employees t1
inner join hr.departments t2
on t1.department_id = t2.department_id
inner join hr.locations t3
on t3.location_id = t2.location_id
where employee_id = a;
dbms_output.put_line(‘’||a||‘号员工在’||b||‘工作，每个月赚’||c||‘元’);
end;
–新建一张员工表，将employee_id为奇数的工资涨百分之10% ，偶数的扣百分之8（直接更新数据）
create table emp_1 as select * from hr.employees;
select *
from emp_1
declare
v_a number(10,2);
v_b number(10,2);
begin
v_a:=0.1;
v_b:=0.08;
update emp_1
set salary = salary+salary\*v_a
where mod(employee_id,2)=1;
update emp_1
set salary = salary+salary\*v_b
where mod(employee_id,2)=0;
commit;
end;
/*输入学号如果该学生考的3门科目都大于等于60则输出***(名字)的学习成绩优秀
该学生考的2门科目大于等于60则输出***的学习成绩一般
该学生考的1或者0门科目大于等于60则输出***的学习成绩有点差 没考的科目算0分*/
declare
a int;
b varchar2(20);
begin
a:=&学号;
select case when jg = 3 then t1.s_name||'的学习成绩优秀'
when jg = 2 then t1.s_name||'的学习成绩一般'
else t1.s_name||'的学习成绩有点差' end into b
from hr.stu t1
left join (select stu_id,count(\*) jg
from hr.score
where s_or >= 60
group by stu_id) t2
on t1.stu_id = t2.stu_id
where t1.stu_id = a;
dbms_output.put_line(b);
end;
/*上月电表数:103.75个字
这月电表数:137.2个字
电字单价:10个字以内0.5,10个字以上0.85
需求：这月电费是多少
只用一个变量进行变量传递*/
declare
a number(10,2);
begin
a:=137.2;
a:=a-103.75;
a:=(a-10)0.85;
a:=a+100.5;
dbms_output.put_line(a);
end;
/*商业险合计优惠34.5%，驾乘人员补充意外伤害优惠60元，显示出：优惠后商业险要花费***，共计花费****/
DECLARE
v_a NUMBER; --商业险折扣
v_b NUMBER; --商业险合计
v_ab NUMBER; --商业险总共花费
v_c NUMBER; --驾乘人员补充意外伤害优惠
v_d NUMBER; --驾乘意外保险费用
v_e NUMBER; --保费合计
BEGIN
v_a:= 0.345;
v_b:=1089.98;
v_c:= 60;
v_d:= 200;
v_ab:= V_b*(1-v_a);
v_d:= v_ab+v_d-v_c;
dbms_output.put_line(‘优惠后商业险要花费’||v_ab||‘，共计花费’||v_d);
END;
boolean 布尔值
声明一个布尔类型的变量，变量里只有true和false，它只判断结果真假
1是否大于2
declare
a boolean;
begin
a:=1<2;
–用if输出boolean值的结果
if a then dbms_output.put_line(‘对’);
else dbms_output.put_line(‘错’);
end if;
end;
–输入一个员工号，如果工资大于20000则降薪，低于20000则涨薪
declare
a int;
b int;
c boolean;
begin
a:=&员工号;
select salary into b
from hr.employees
where employee_id = a;
c:=b>20000;
if c then dbms_output.put_line(‘降薪’);
else dbms_output.put_line(‘涨薪’);
end if;
end;
默认设定变量，用一张表的列来当变量
变量所引用的数据类型可以根据表里的列的数据类型变化而实时该变
declare
变量名 表名.列名%type;
输入一个人的员工号返回他的工资
declare
–v_id int;
–v_salary int;
v_id hr.employees.employee_id%type;
v_salary hr.employees.employee_id%type;
begin
v_id:=&员工号;
select salary into v_salary
from hr.employees
where employee_id = v_id;
dbms_output.put_line(v_salary);
end;
declare
v_id hr.employees.employee_id%type;
v_salary hr.employees.employee_id%type;
v_job_id hr.employees.job_id%type;
begin
v_id:=&员工号;
select employee_id,salary,job_id into v_id,v_salary,v_job_id
from hr.employees
where employee_id = v_id;
dbms_output.put_line(v_id);
dbms_output.put_line(v_salary);
dbms_output.put_line(v_job_id);
end;
DECLARE
v_emid hr.employees.employee_id%TYPE;
v_salary hr.employees.salary%TYPE;
v_job_id hr.employees.job_id%TYPE;
BEGIN
v_emid:=&员工号;
SELECT salary, job_id INTO v_salary, v_job_id
FROM hr.employees
WHERE employee_id = V_emid;
dbms_output.put_line(‘员工号:’||v_emid||‘, 工资:’||v_salary||‘, 工种:’||v_job_id);
END;
默认设定变量，用一张表的所有列来当变量，针对一张表，相当于可以拿这张表里的任意列来当变量，数据类型和列一致，用什么变量，就写变量名 .表里哪一列
declare
变量名 表名%rowtype;
–输入一个人的员工号返回他的工资
declare
v_e hr.employees%rowtype;
begin
v_e.employee_id:=&员工号;
select salary into v_e.salary
from hr.employees
where employee_id = v_e.employee_id;
dbms_output.put_line(v_e.salary);
end;
–其它变量类型
–设定一个记录类型(变量集合)
declare
type 数据类型集合名 is record(变量a 数据类型,表量b 数据类型…);
变量名 数据类型集合名;
输入一个员工号，返回员工号，工资，名字
declare
type e_r is record(v_a int,
v_b int,
v_c varchar2(20)); --先声明一个变量集合在里面声明好变量和类型
v_e e_r; --给变量集合一个变量名
begin
v_e.v_a:=&员工号;
select salary,first_name into v_e.v_b,v_e.v_c
from hr.employees
where employee_id = v_e.v_a;
dbms_output.put_line(v_e.v_a||’ ‘||v_e.v_b||’ '||v_e.v_c);
end;
注意事项
1 如果有多个列要赋值到变量集合的时候，列的数量要和变量集合里要一致，数据类型也要一致，位置也要一致
2 使用变量的时候要用 变量集合名.变量集合里的某个变量名
3 变量集合里的变量用逗号隔开
4 可以和默认设定变量一起用
输入一个员工号，返回员工号，工资，名字
declare
type e_r is record(v_a hr.employees.employee_id%type,
v_b hr.employees.salary%type,
v_c hr.employees.first_name%type); --先声明一个变量集合在里面声明好变量和类型
v_e e_r; --给变量集合一个变量名
begin
v_e.v_a:=&员工号;
select salary,first_name into v_e.v_b,v_e.v_c
from hr.employees
where employee_id = v_e.v_a;
dbms_output.put_line(v_e.v_a||’ ‘||v_e.v_b||’ '||v_e.v_c);
end;
declare
type e_r is record(v_a hr.employees%rowtype); --先声明一个变量集合在里面声明好变量和类型
v_e e_r; --给变量集合一个变量名
begin
v_e.v_a.employee_id:=&员工号;
select * into v_e.v_a
from hr.employees
where employee_id = v_e.v_a.employee_id;
dbms_output.put_line(v_e.v_a.employee_id||’ ‘||v_e.v_a.salary||’ '||v_e.v_a.first_name);
end;
集合类型
集合是由多个元素组成的数据结构，可以包含任意数量的元素
概念：
集合：是一种用于存储多个相同类型数据的数据类型
下标：用来标识集合中每个元素的位置，由下标的大小值决定先后顺序
元素：集合中的每个数据，可以通过下标来访问
索引表类型(也叫关联数组)，储存单行多列数据
注意事项
1 可以使用整数(pls_integer,binary_integer)或字符串当作下标，下标是可以不连续的
2 索引表的元素个数是不限的
3 索引表只能用在plsql里，不能存在数据库里
[!定义索引表类型]
语法:
type 类型名称 is table of 集合中数据的数据类型 index by 下标的数据类型
集合的变量名字 集合的类型 --声明一个变量集合
集合中数据的存储
变量集合名(下标)
declare
–定义索引表类型
type indextable is table of varchar2(20) index by pls_integer;
–给索引表类型一个变量名
v_indextable indextable;
begin
–使用: 变量集合名(下标) 存储值或者赋值
v_indextable(1):=‘a’;
v_indextable(2):=‘b’;
v_indextable(3):=‘c’;
v_indextable(6):=‘d’;
v_indextable(-8):=‘e’;
–取值: 变量集合名(下标)
dbms_output.put_line(v_indextable(1));
dbms_output.put_line(v_indextable(3));
dbms_output.put_line(v_indextable(6));
dbms_output.put_line(v_indextable(-8));
end;
集合里面的存储元素也可以是记录类型
declare
–声明记录类型
type type_stu is record(s_name hr.stu.s_name%type,
ssex hr.stu.s_sex%type);
v_stu type_stu;
–声明一个索引表类型
type mytable is table of type_stu index by pls_integer;
–给索引表类型一个变量名
v_mytable mytable;
begin
v_stu.s_name:=‘张三’;
v_stu.ssex:=‘男’;
–存放记录类型数据
v_mytable(1):=v_stu;
–取记录类型数据
dbms_output.put_line(v_mytable(1).s_name||’ '||v_mytable(1).ssex);
end;
集合的属性
1.first 取集合的第一个元素的下标
2.last 取集合的最后一个元素的下标
3 count 取集合的总长度
4 limit 取集合元素索引的最大值(索引表和嵌套表是不限个数的，所以返回null，变长数组返回定义时的最大索引)
5 delete([n]) 删除集合中的元素,加n表示下标，删除对应下标的值
6 extend(n[,ind]) 扩展集合元素，n是一个数字，表示扩充的元素个数，ind是集合中一个元素的下标，加上它
表示扩展元素的时候，给扩展的元素加上值，值是ind下标对应的元素
7 next(下标) 取当前元素下一个元素的下标
8 prior(下标) 取当前元素上一个元素的下标
使用
集合名字.属性名
declare
–定义一个索引表类型
type indextable is table of varchar2(20) index by pls_integer;
v_indextable indextable;
begin
–使用索引表
v_indextable(1):=‘小明’;
v_indextable(3):=‘小张’;
v_indextable(5):=‘张三’;
v_indextable(-8):=‘a’;
–取值：集合变量名(下标)
dbms_output.put_line(v_indextable.first);
dbms_output.put_line(v_indextable.last);
dbms_output.put_line(v_indextable.count);
dbms_output.put_line(v_indextable.limit);
dbms_output.put_line(v_indextable.next(-8)); --返回下一个元素的下标
dbms_output.put_line(v_indextable(1));
dbms_output.put_line(v_indextable.prior(-8)); --返回上一个元素的下标
–v_indextable.extend(2); --表示扩展两个元素，索引表的下标是无限的，所以不能添加
v_indextable.delete(); --括号里不加n表示删除全部，如果写填下标了，表示删除那个下标的值，如果删掉一个
–下标的值，那个下标就不能再被访问
end;
–嵌套表类型
注意事项
1 只能使用正整数作为下标,并且下标是连续的
2 元素的个数是无限的，可以在plsql程序块中使用，也可以存储到数据库当中
3 数据的存取和索引表类型类似
[!定义嵌套表类型]
语法type 类型名称 is table of 数据类型
变量名 类型名;
嵌套表在赋值之前需要初始化
变量名:=类型名(任意值);
declare
–定义嵌套表类型
type mytable is table of varchar2(20);
v mytable;
begin
–为嵌套表初始化
v:=mytable(‘初始化’);
v(1):=‘a’;
dbms_output.put_line(v(1));
end;
declare
–定义嵌套表类型
type mytable is table of varchar2(20);
v mytable;
begin
–为嵌套表初始化
v:=mytable(‘初始化’);
v(1):=‘aa’;
dbms_output.put_line(v(1));
v.extend(2); --扩展两个元素,，默认值是null
v.extend(2,1);–扩展两个元素，值是下标1的值
v(2):=‘bb’;
v(3):=‘cc’;
dbms_output.put_line(v(2));
dbms_output.put_line(v(3));
dbms_output.put_line(v(4));
dbms_output.put_line(v(5));
end;
exception部分
错误处理，异常处理，报错捕捉
内置异常
exception when no_data_found /*检测有没有数据*/ then dbms_output.put_line(‘无数据’);
/*如果没有输入的数据就打印无数据*/
when too_many_rows /*监测数据是不是多行*/ then dbms_output.put_line(‘多行数据’);
/*返回的数据是多行就输出多行数据*/
when others /*其它错误*/ then dbms_output.put_line(sqlerrm) /*如果有其它报错，输出报错的原因*/
when value_error /*赋值时，变量长度不足以容纳实际数据*/ then dbms_output.put_line(‘放不下了’);
输出300号员工的工资
declare
v_a int;
v_b number(10,2);
begin
v_a:=300;
select salary into v_b
from hr.employees
where employee_id = v_a;
dbms_output.put_line(v_b);
dbms_output.put_line(‘运行成功’);
exception
when no_data_found then dbms_output.put_line(‘没有这个员工’);
end;
输出50号部门的所有人的工资
declare
v_a int;
v_b number(1,1);
begin
v_a:=100;
select salary into v_b
from hr.employees
where employee_id = v_a;
dbms_output.put_line(v_b);
dbms_output.put_line(‘运行成功’);
exception
when no_data_found then dbms_output.put_line(‘没有这个员工’);
when too_many_rows then dbms_output.put_line(‘返回多行数据’);
when value_error then dbms_output.put_line(‘放不下啦’);
when others then dbms_output.put_line(sqlerrm);
end;
注意事项
1 捕捉其它错误others，要放在exception部分的最后
/*输入老师姓名，输出这个老师教的学生中谁的分数最高，输出**的分数最高，为**分，
找不到数据显示：非本班教师，多行显示：这个老师很优秀，其他错误显示错误代码和原因*/
declare
a varchar2(20);
b varchar2(20);
c varchar2(20);
begin
a:=‘&老师姓名’;
select t1.s_name,t2.s_or into b,c
from hr.stu t1
inner join hr.score t2
on t1.stu_id = t2.stu_id
inner join hr.cour t3
on t3.c_id = t2.c_id
where (t3.t_name,t2.s_or) in (select t3.t_name,max(t2.s_or)
from hr.stu t1
inner join hr.score t2
on t1.stu_id = t2.stu_id
inner join hr.cour t3
on t3.c_id = t2.c_id
where t3.t_name = a
group by t3.t_name);
dbms_output.put_line(b||‘的分数最高，为’||c||‘分’);
exception
when no_data_found then dbms_output.put_line(‘非本班教师’);
when too_many_rows then dbms_output.put_line(‘这个老师很优秀’);
when others then dbms_output.put_line(sqlerrm);
end;
/*输入客户姓名 输出**买了******（水果名）已知客户最多买了4种
找不到数据:非本店客户*/
/*输入客户号,输出这个客户消费最高的订单号和订单金额（含运费),如果无数据 显示“这人0元购” 其他错误
显示错误代码*/
declare
a int;
b number(10,2);
begin
a:=‘&客户号’;
select max(a) into b
from (select t2.ddid,sum(price*amount)+avg(yunfei) a
from hr.khb t1
inner join hr.ddb t2
on t1.cust_id = t2.cust_id
inner join hr.mxb t3
on t3.ddid = t2.ddid
inner join hr.spb t4
on t4.productid = t3.productid
where t1.cust_id = a
group by t2.ddid);
dbms_output.put_line(a||‘号客户消费最高的订单金额是’||b);
exception
when no_data_found then dbms_output.put_line(‘这人0元购’);
when others then dbms_output.put_line(sqlerrm);
end;
/*输入客户号,输出这个客户消费最高的订单号和订单金额（含运费),如果无数据 显示“这人0元购” 其他错误
显示错误代码*/
declare
a int;
b varchar2(20);
c int;
begin
a:='&客户号';
select t2.ddid,sum(price\*amount)+avg(yunfei) a into c,b
from hr.khb t1
inner join hr.ddb t2
on t1.cust_id = t2.cust_id
inner join hr.mxb t3
on t2.ddid = t3.ddid
inner join hr.spb t4
on t3.productid = t4.productid
where t1.cust_id = a
group by t2.ddid
having sum(price\*amount)+avg(yunfei) =
(select max(a)
from (
select t2.ddid,
sum(price\*amount)+avg(yunfei) a
from hr.khb t1
inner join hr.ddb t2
on t1.cust_id = t2.cust_id
inner join hr.mxb t3
on t2.ddid = t3.ddid
inner join hr.spb t4
on t3.productid = t4.productid
where t1.cust_id = a
group by t2.ddid
order by a desc
)
);
dbms_output.put_line(a||'客户消费最高的订单号是'||c||',订单金额为:'||b);
exception
when no_data_found then dbms_output.put_line('这人0元购');
when others then dbms_output.put_line(sqlerrm);
end;
/输入学生学号 显示 姓名， 每科分数(没考的显示0)，任课老师(没成绩的科目显示无)
异常捕捉 非本校学生，请输入正确学号！
其他异常 显示异常代码/
declare
a varchar2(20); --学生学号
b varchar2(20); --学生姓名
c varchar2(20); --分数
d varchar2(20); --老师
e varchar2(20); --分数
f varchar2(20); --老师
g varchar2(20); --分数
h varchar2(20); --老师
begin
a:='&学生学号';
select t4.stu_id,t3.s_name,语文,任课老师1,数学,任课老师2,英语,任课老师3
into a,b,c,d,e,f,g,h
from (select s_name,nvl(语文,0) as 语文,nvl2(语文,'吴京','无') as 任课老师1,
nvl(数学,0) as 数学,nvl2(数学,'张译','无') as 任课老师2,
nvl(英语,0) as 英语,nvl2(英语,'黄渤','无') as 任课老师3
from (select s_name,c_id,s_or
from hr.stu t1
left join hr.score t2
on t1.stu_id = t2.stu_id)
pivot(max(s_or) for c_id in (1 语文,2 数学,3 英语))) t3
inner join hr.stu t4
on t3.s_name=t4.s_name
where t4.stu_id = a;
--显示 姓名， 每科分数(没考的显示0)，任课老师(没成绩的科目显示无)
dbms_output.put_line(b||'语文考了'||c||'老师是'||d||',数学考了'||e||'老师是'||f||',英语考了'||g||'老师是'||h);
--异常捕捉 非本校学生，请输入正确学号！ 其他异常 显示异常代码
exception
when no_data_found /\*检测有没有数据*/ then dbms_output.put_line('非本校学生，请输入正确学号！ ');
when others /\*其它错误*/ then dbms_output.put_line(sqlerrm);
end;
select *
from hr.stu t1
left join hr.score t2
on t1.stu_id = t2.stu_id;
/*输入学生学号,显示姓名， 每科分数(没考的显示0)，任课老师(没成绩的科目显示无)
异常捕捉 非本校学生，请输入正确学号！
其他异常 显示异常代码*/
declare
xuehao int;
name varchar2(20);
yuwen varchar2(20);
shuxue varchar2(20);
yingyu varchar2(20);
aa varchar2(20);
bb varchar2(20);
cc varchar2(20);
begin
xuehao:=&学号;
select t1.s_name,t1.语文,t1.数学,t1.英语,t2.a,t2.b,t2.c into name,yuwen,shuxue,yingyu,aa,bb,cc
from(
select stu_id,s_name,nvl(语文,0) 语文,nvl(数学,0) 数学,nvl(英语,0) 英语
from (select t1.stu_id,t1.s_name,t2.s_or,t2.c_id
from hr.stu t1
left join hr.score t2
on t1.stu_id = t2.stu_id
left join hr.cour t3
on t3.c_id = t2.c_id)
pivot(max(s_or) for c_id in (1 语文,2 数学,3 英语))) t1
inner join
(select stu_id,nvl(a,'无') a,nvl(b,'无') b,nvl(c,'无') c
from (select t1.stu_id,t3.t_name
from hr.stu t1
left join hr.score t2
on t1.stu_id = t2.stu_id
left join hr.cour t3
on t3.c_id = t2.c_id)
pivot(max(t_name) for t_name in ('吴京' a,'张译' b,'黄渤' c))) t2
on t1.stu_id = t2.stu_id
where t1.stu_id = xuehao;
dbms_output.put_line(name||' 语文成绩:'||yuwen||' 任课教师:'||aa||' 数学成绩:'||shuxue||' 任课教师:'||bb||' 英语成绩:'||yingyu||' 任课教师:'||cc);
end;
zero_divide 当除数为0的时候会抛出异常
dup_val_on_index 在插入数据时候，违反了唯一约束
invalid_number 将非数字的值强制转换成number时触发
[[Raise自定义异常]]
[!raise 自定义异常]
自动抛出异常，满足什么条件就报错
有的时候数据会违反我们的需求，并且还不在oracle的内置异常里，这个时候可以自定义异常，如果出现
什么什么情况，就抛出异常
–输入一个员工号，如果工资大于20000那就报错，工资太高了
/*用布尔值和自定义异常，ab两字符串，a长度>b长度，输出前字符串比后字符串长**，
a<b输出后字符串比前字符串长**，长度相等返回：长度相等*/
declare
a varchar2(20);
b varchar2(20);
c boolean;
d exception;
begin
a:='&字符串a';
b:='&字符串b';
c:=length(a)>length(b);
if length(a) = length(b) then raise d;
end if;
if c then dbms_output.put_line('前字符串比后字符串长'||(length(a)-length(b)));
else dbms_output.put_line('后字符串比前字符串长'||(length(b)-length(a)));
end if;
exception
when d then dbms_output.put_line('长度相等');
end;
直接弹窗报错以及报错原因
raise_application_error(报错代码,‘报什么错’);
–输入一个员工号，如果工资大于20000那就报错，工资太高了
declare
v_a int;
begin
v_a:=&员工号;
select salary into v_a
from hr.employees
where employee_id = v_a;
if v_a > 20000 then raise_application_error(-20000,‘工资太高了’);
end if;
dbms_output.put_line(v_a);
end;
注意事项
1 报错代码要在-20000到-21000之间
变量的作用域和可见性
plsql程序块出现了嵌套的话
内部的plsql块可以访问外部的变量
外部的plsql块不能访问内部的变量
declare
a int;
b int;
begin
a:=1;
b:=2;
declare
c int;
begin
c:=3;
dbms_output.put_line(a||b||c);
end;
dbms_output.put_line(a||b);
end;
流程控制语句
if
跟case when 类似
语法：
if … then …
elsif … then …
elsif … then …
elsif … then …
else …
end if;
注意事项
1 then后面可以执行多个操作，用分号隔开，dml语句或者输出
declare
v_salary number(10,2);
begin
select salary into v_salary
from hr.employees
where employee_id = 185;
if v_salary > 20000 then dbms_output.put_line(‘好’);
dbms_output.put_line(‘很好’);
insert into cs20240523_1 values (1);
elsif v_salary < 20000 then insert into cs20240523_1 values(2);
end if;
end;
2 流程控制语句里不能出现子查询
–如果工资大于20000，输出降薪
declare
v_salary number(10,2);
begin
if (select salary
from hr.employees
where employee_id = 100) > 20000 then dbms_output.put_line(‘降薪’);
end if;
end;
declare
v_a int;
e_a exception; --在declare声明一个异常
begin
v_a:=&员工号;
select salary into v_a
from hr.employees
where employee_id = v_a;
if v_a > 20000 then raise e_a;
end if;
dbms_output.put_line(v_a);
exception
when e_a then dbms_output.put_line(‘工资太高了’);
end;
在两个参数之间随机生成小数
dbms_random.value(参数,参数2)
select round(dbms_random.value(1,10))
from dual;
/*如果一个员工的工资大于5000并且是60号部门的员工，则工资加500
是80号部门的员工 ，工资减500
如果不是60也不是80号部门的员工，工资不变
如果工资小于5000则工资加1000*/
declare
a int;
b number(10,2);
c int;
begin
a:=&员工号;
select salary,department_id into b,c
from hr.employees
where employee_id = a;
if b<5000 then dbms_output.put_line(‘工资加5000’); --找出薪资小于5000的
else if c = 60 then dbms_output.put_line(‘工资加500’); --薪资大于5000里的，是60号部门的
elsif c=80 then dbms_output.put_line(‘工资减500’);
else dbms_output.put_line(‘薪资不变’); --不是60也不是80号部门的员工，工资不变
end if;
end if;
end;
if嵌套
有几个if就要写几个end if，可以在then里套if，或者else里
在then里嵌套if
列举出来多种情况，分情况决定怎么输出
if … then if … then …
elsif … then …
else …
end if;
end if;
在else里嵌套if
除了…的怎么办，可以实现其它里的其它
if … then …
else if … then …
else …
end if;
end if;
/如果一个员工的工资大于5000并且是60号部门的员工，则工资加500
是80号部门的员工 ，工资减500
如果不是60也不是80号部门的员工，工资不变
如果工资小于5000则工资加1000/
declare
a int;
b number(10,2);
c int;
begin
a:=&员工号;
select salary,department_id into b,c
from hr.employees
where employee_id = a;
if b<5000 then dbms_output.put_line(‘工资加5000’); --找出薪资小于5000的
else if c = 60 then dbms_output.put_line(‘工资加500’); --薪资大于5000里的，是60号部门的
elsif c=80 then dbms_output.put_line(‘工资减500’);
else dbms_output.put_line(‘薪资不变’); --不是60也不是80号部门的员工，工资不变
end if;
end if;
end;
/*
输入这个月的电表数 上月电表数是1603 返回 本月电费金额：** 用电量：** 本月电表数：**
10度（含） 0.5元/度
10-20度（含） 0.85元/度
20以上 1元/度
异常处理 (输入的电表数小于上月电表数 ) 电表数输入错误请重新输入*/
declare
a number(10,2);
b number(10,2);
begin
a:=&本月电表数;
if a-1603 < 0 then dbms_output.put_line(‘电表数输入错误请重新输入’);
elsif a-1603 <= 10 then if substr((0.5*(a-1603)),1,1) = ‘.’ then dbms_output.put_line(‘本月电费金额：’||‘0’||(0.5*(a-1603))|| ’ 用电量：‘||(a-1603)||’ 本月电表数：‘||a);
else dbms_output.put_line(‘本月电费金额：’||(0.5*(a-1603))|| ’ 用电量：’||(a-1603)||’ 本月电表数：‘||a);
end if;
elsif a-1603 between 11 and 20 then dbms_output.put_line(‘本月电费金额：’||(0.85*(a-1603-10)+100.5)|| ’ 用电量：‘||(a-1603)||’ 本月电表数：'||a);
elsif a-1603 > 20 then dbms_output.put_line(‘本月电费金额：’||(1(a-1603-20)+100.5+100.85)|| ’ 用电量：’||(a-1603)||’ 本月电表数：'||a);
end if;
end;
–case when
–跟if完全一样，只是把关键字换了一下，也可以嵌套
case when … then …
when … then …
else …
end case;
注意事项
1 case when 必须把所有可能的形况全都描述清楚，不然会报错，执行case时找不到case语句
两数交换
a和b变量，分别有个初始值，让他俩的值进行交换
declare
a int := 10;
b int := 15;
c int;
begin
c:=a; --先把a的值给c，a现在是10，c也是10
a:=b; --把b的值15给a，a现在是15
b:=c; --把c的值给b，b现在是10
dbms_output.put_line(a);
dbms_output.put_line(b);
end;
循环
循环
反复的执行一些语句，反复的去做一些事，直到满足什么条件为止
每个循环都需要4个条件，初始值，循环条件或退出循环条件，循环体，迭代
初始值：一般情况下一个变量在进入循环之前都要有一个初始值
循环条件或退出循环条件：根据循环类型的不同，有的是满足什么条件才进行循环，有的是满足什么条件退出
循环
循环体：循环的开始和结束，中间的部分就叫做循环体，循环的内容是什么
迭代：每次循环都在发生变化的东西
循环分为3种：loop，while，for
loop
无条件循环，需要在适当的地方加exit when 当怎么怎么样的时候停止循环，不然就成了死循环
语法
loop
语句;
exit when 条件;
end loop;
–输出1-10
declare
a int; --声明一个变量a
begin
a:=0; --给变量一个初始值为0
loop
a:=a+1; --每次循环都把a+1的结果给a 9
dbms_output.put_line(a); --每次循环都输出一次a
exit when a=10; --当a等于10的时候退出循环
end loop; --循环结束标志
end ;
declare
a int; --声明一个变量a
begin
a:=1; --给变量一个初始值为1
loop
dbms_output.put_line(a);
a:=a+1; --每次循环都把a+1的结果给a 9
–每次循环都输出一次a
exit when a>10; --当a等于10的时候退出循环
end loop; --循环结束标志
end ;
while
设定一个条件进行循环，每次循环开始之前判断满不满足设定的条件，满足就继续循环，不满足就结束循环
语法
while 条件 loop
语句;
end loop;
用while输出1-10
declare
a int; --声明一个变量a
begin
a:=0;
while a<10 loop --设定一个条件，每次循环之前判断a是否小于10，如果小于10继续循环
a:=a+1;
dbms_output.put_line(a);
end loop;
end;
for 循环
适合用于需要循环的次数是已知时候使用
每循环一次，循环变量自动加1，变量每次被加1，就会执行一次循环体，把变量从下限循环到上限然后结束
,1…10 相当于循环 循环体 10次
语法
for 变量名 in 下限…上限 loop
语句;
end loop;
用for输出1-10
begin
for a in 1…10 loop
dbms_output.put_line(a);
end loop;
end;
for的反循环
放在for循环的in后面，将for循环变成从上限循环到下限，但是上限和下限还得写成从小到大
用for的反循环输出10-1
begin
for a in reverse 1…10 loop
dbms_output.put_line(a);
end loop;
end;
exit 直接退出循环
语句从上到下执行，遇到exit的时候立即退出循环
一般情况下配合if使用，每当到if的时候判断，满不满足if的条件，满足then exit; 不满足继续循环
满足什么条件就退出循环
exit when 条件
注意事项
1 退出循环放在不同的位置，循环的结果是不一样的，放置退出循环条件的时候考虑一下位置的位置
满足什么条件，提前结束本次循环，跳到下一次循环
continue when
–输入a，输出a到20的所有数 loop
declare
a int;
b int;
begin
a:=&值;
loop
a:=a+1;
exit when a>20; --当a大于20的时候退出循环
continue when a>15; --a大于15提前结束本次循环，跳到下一次
dbms_output.put_line(a);
end loop;
end;
–输入a，输出a到20的所有数 loop
declare
a int;
b int;
begin
a:=&值;
loop
dbms_output.put_line(a);
a:=a+1;
exit when a>20; --当a大于20的时候退出循环
end loop;
end;
–输入a，输出a到20的所有数 loop
declare
a int;
b int;
begin
a:=&值;
b:=20;
while a<=b loop
dbms_output.put_line(a);
a:=a+1;
end loop;
end;
–输入a，输出a到20的所有数 for
declare
a int;
begin
a:=&值;
for i in a…20 loop
dbms_output.put_line(i);
end loop;
end;
—输出字母A-K
declare
v varchar2(20);
begin
v:=ascii(‘A’); --先求出大写A的ascii值
v:=chr(v); --把ascii值转成对应的字母，转成大写A
dbms_output.put_line(v);
for i in 1…10 loop --for循环开始，设定一个变量i，将变量i从1循环到10
v:=ascii(v)+1;
v:=chr(v);
dbms_output.put_line(v); --每次循环都输出一下v
end loop;
end;
declare
a varchar2(10);
begin
a:=ascii(‘A’);
loop
dbms_output.put_line(chr(a));
a:=a+1;
exit when a>ascii(‘K’);
end loop;
end;
declare
a varchar2(10);
begin
a:=ascii(‘A’);
while a<ascii(‘L’) loop
dbms_output.put_line(chr(a));
a:=a+1;
end loop;
end;
declare
a varchar2(5);
begin
a:=ascii(‘A’);
for b in ascii(‘A’)… ascii(‘K’) loop
dbms_output.put_line(chr(b));
end loop;
end;
/用循环输出多行数据
输入部门号返回该部门的员工信息(员工号，姓名，工资)/
[!举例]
declare
v_id int;
v_name varchar2(20);
v_salary int;
v_dep int;
v_rw int;
v_max int;
begin
v_dep:=&部门号;
v_rw:=1;
select max(rownum) into v_max
from hr.employees
where department_id = v_dep;
loop
select employee_id,last_name,salary into v_id,v_name,v_salary
from (select employee_id,last_name,salary,rownum a
from hr.employees)
where a = v_rw;
v_rw:=v_rw+1;
dbms_output.put_line(v_id||’ ‘||v_name||’ '||v_salary);
exit when v_rw > v_max;
end loop;
end;
1*1=?
1*2=?
1*3=?
.
.
1*9=？
2*1=?
2*2=?
…
9*9=?
declare
a number(4);
b number(4);
c number(10);
begin
a:=1;
b:=1;
loop
loop
c:=a\*b;
dbms_output.put_line(a||'\*'||b||'='||c);
b:=b+1;
exit when b>9;
end loop;
b:=1;
a:=a+1;
exit when a>9;
end loop;
end;
输出1-100之间的质数
begin
dbms_output.put(‘今天’);
dbms_output.put(‘星期一’);
dbms_output.new_line;
end;
[!输出A-K]
declare
v varchar2(20);
begin
v:=ascii(‘A’); --先求出大写A的ascii值
v:=chr(v); --把ascii值转成对应的字母，转成大写A
dbms_output.put_line(v);
for i in 1…10 loop --for循环开始，设定一个变量i，将变量i从1循环到10
v:=ascii(v)+1;
v:=chr(v);
dbms_output.put_line(v); --每次循环都输出一下v
end loop;
end;
DECLARE
v_a INT;
BEGIN
v_a:= 0;
FOR i IN 1…100 LOOP
v_a:=v_a+i;
END LOOP;
dbms_output.put_line(‘1+2+3+…+100=’||v_a);
END;
declare
a int; --声明一个变量a
b int; --声明一个变量b，用来存储累积和
begin
a := 0; --给变量a一个初始值为0
b := 0; --给变量b一个初始值为0
loop
a := a + 1; --每次循环都把a+1的结果赋给a
b := b + a; --将a累加到b
exit when a = 100; --当a等于100的时候退出循环
end loop; --循环结束标志
dbms_output.put_line(b || ’ '); --输出当前累积和
end;
DECLARE
v_a INT;
i INT;
BEGIN
v_a:=0;
i:=1;
LOOP
v_a := v_a+i;
i:= i+1;
EXIT WHEN i>100;
END LOOP;
dbms_output.put_line(‘1+2+3+…+100=’||v_a);
END;
[!NOTE]
DECLARE
a INT;
b INT;
c INT;
BEGIN
a:= 1;
b:= 1;
c:=1;
WHILE b <100 LOOP
b:=b+1;
a := a+1;
c:=c+a;
END LOOP;
DBMS_OUTPUT.PUT_LINE©;
END;
DECLARE
a INT;
b INT;
c INT;
BEGIN
a:= 1;
b:= 1;
c:=1;
loop
b:=b+1;
a :=a+1;
c:=c+a;
exit when b=100;
end loop;
dbms_output.put_line(c);
end ;
declare
a int;
b int;
begin
a:=0;
b:=1;
loop
a:=a+b;
b:=b+1;
exit when b > 100;
end loop;
dbms_output.put_line(a);
end;
declare
a int; --声明一个变量a
b int;
begin
a:=0;
b:=0;
while a<100 loop --设定一个条件，每次循环之前判断a是否小于10，如果小于10继续循环
a:=a+1;
dbms_output.put_line(a||'+'||b||'='||(b+a));
b:=b+a;
end loop;
dbms_output.put_line(b||' ');
end;
declare
a int; --声明一个变量a
b int;
begin
a:=1;
b:=1;
while a<100 loop --设定一个条件，每次循环之前判断a是否小于10，如果小于10继续循环
a:=a+1;
dbms_output.put_line(b||'+'||a||'='||(b+a));
b:=b+a;
end loop;
dbms_output.put_line(b||' ');
end;
declare
v_a number;
v_b number;
begin
v_a:=0;
v_b:=1;
while v_b<100 loop
v_a:=v_a+v_b;
v_b:=v_b+1;
dbms_output.put_line(v_a||'+'||v_b||'='||(v_a+v_b));
end loop;
end;
DECLARE
a INT;
b INT;
c INT;
BEGIN
a:= 1;
b:= 1;
c:=1;
WHILE b <100 LOOP
b:=b+1;
a:=a+1;
DBMS_OUTPUT.PUT_LINE(c||'+'||a||'='||(c+a));
c:=c+a;
END LOOP;
END;
DECLARE
v_a INT;
v_b INT;
i INT;
BEGIN
v_a:= 1;
v_b:=0;
i:=2;
WHILE i<=100 LOOP
v_b:=v_a+i;
dbms_output.put_line(v_a||'+'||i||'='||v_b);
v_a:=v_b;
i:=i+1;
END LOOP;
END;
–显示过程
declare
n int;
a int;
v_sum int;
begin
n:=&n;
a:=1;
v_sum:=n;
loop
v_sum:=v_sum*(n+a);
a:=a+1;
dbms_output.put_line(v_sum);
exit when a > 20;
end loop;
end;
–输入一个n 输出 n*(n+1)*(n+2)…*(n+20) 的结果
declare
n int;
a int;
v_sum int;
begin
n:=&值;
a:=1;
v_sum:=n;
loop
v_sum:=v_sum*(n+a);
a:=a+1;
dbms_output.put_line(v_sum);
exit when a > 20;
end loop;
end;
用循环输出多行数据
–loop
–输入部门号返回该部门的员工信息(员工号，姓名，工资)
declare
v_id int;
v_name varchar2(20);
v_salary int;
v_dep int;
v_rw int;
v_max int;
begin
v_dep:=&部门号;
v_rw:=1;
select max(rownum) into v_max
from hr.employees
where department_id = v_dep;
loop
select employee_id,last_name,salary into v_id,v_name,v_salary
from (select employee_id,last_name,salary,rownum a
from hr.employees)
where a = v_rw;
v_rw:=v_rw+1;
dbms_output.put_line(v_id||’ ‘||v_name||’ '||v_salary);
exit when v_rw > v_max;
end loop;
end;
–while
–输入部门号返回该部门的员工信息(员工号，姓名，工资)
declare
v_id int;
v_name varchar2(20);
v_salary int;
v_dep int;
v_rw int;
v_max int;
begin
v_dep:=&部门号;
v_rw:=1;
select max(rownum) into v_max
from hr.employees
where department_id = v_dep;
while v_rw <= v_max loop
select employee_id,last_name,salary into v_id,v_name,v_salary
from (select employee_id,last_name,salary,rownum a
from hr.employees)
where a = v_rw;
v_rw:=v_rw+1;
dbms_output.put_line(v_id||’ ‘||v_name||’ '||v_salary);
end loop;
end;
–for
–for的情况比较特殊，in后面不仅仅可以定义循环次数，还可以放一个结果集
–for
–输入部门号返回该部门的员工信息(员工号，姓名，工资)
declare
v_dep int;
begin
v_dep:=&部门号;
for i in(select *
from hr.employees
where department_id = v_dep)
dbms_output.put_line(i.employee_id||i.last_name||i.salary);
end loop;
end;
—输入一个水果，输出买了这个水果的人
declare
v_name varchar2(20);
v_shuiguo varchar2(20);
v_max int;
a int;
begin
v_shuiguo:=‘&水果’;
a:=1;
select max(rownum) into v_max --求出表里有多少条数据，得知要循环多少次，或者什么时候退出循环
from hr.shuiguo9
where shuiguo = v_shuiguo;
loop
exit when a>v_max;
select name,shuiguo into v_name,v_shuiguo
from (select t1.*,rownum w
from hr.shuiguo9 t1
where shuiguo = v_shuiguo)
where w = a;
a:=a+1;
dbms_output.put_line(v_name);
end loop;
end;
DECLARE
n INT;
b INT;
BEGIN
n:=1;
WHILE n <= 9 LOOP
b := 1;
WHILE b <= 9 LOOP
DBMS_OUTPUT.PUT_LINE(n || ‘*’ || b || ‘=’ || (n*b));
b := b + 1;
END LOOP;
n := n + 1;
END LOOP;
END;
declare
a int;
b int;
c int;
begin
a:=1;
b:=1;
loop
loop
c:=a*b;
dbms_output.put_line(a||‘*’||b||‘=’||c);
b:=b+1;
exit when b>9;
end loop;
a:=a+1;
b:=1;
exit when a>9;
end loop;
end;
declare
a int;
b int;
begin
a:= 100;
b:=1;
for i in 2…a loop
b:=1;
for j in 2…i-1 loop
if mod(i, j) = 0 then b:=2;
exit;—退出内循环
end if;
end loop;
if b=1 then – 如果
dbms_output.put_line(i); – 输出i
end if;
end loop;
end;
declare
a int;
b int;
begin
a:= 100;
b:=1;
for i in 2…a loop
b:=1;
for j in 2…i-1 loop
if mod(i, j) = 0 then b:=2;
exit;—退出内循环
end if;
end loop;
if b=1 then – 如果
dbms_output.put_line(i); – 输出i
end if;
end loop;
end;
declare
a int;
b boolean;
begin
a:=2;
while a < 100 loop
b := TRUE;
for i in 2…a loop
if i!=a and mod(a,i) = 0
then b := FALSE;
exit; —判断是否能被除了其他1和他本身的其他数整数，如果可以 标记false 并退出内循环
end if;
end loop;
if b then dbms_output.put_line(a);
end if;
a:=a+1;
end loop;
end;
–输出2-100之间的质数 while
declare
a int; --当前判断的数字
b int; --求余除数
begin
a:=1;
b:=2;
while a <= 99 loop
a:=a+1;
while mod(a,b)!=0 loop --a除以b如果被整除了就跳出循环
b:=b+1; --b从2开始一直在+1，知道a除b可以整除
end loop;
if b = a then dbms_output.put_line(a);
/*判断跳出小循环的a跟当时的b是否相等*/
end if;
b:=2;
end loop;
end;
–输出99乘法表 for
begin
for a in 1…9 loop
for b in 1…a loop
dbms_output.put(b||‘*’||a||‘=’||(b*a)||’ ');
end loop;
dbms_output.put_line(null);
end loop;
end;
–用for，输出每个人都买了什么水果 pivot
begin
for i in (select *
from(select *
from hr.shuiguo9)
pivot(max(shuiguo) for shuiguo in (‘苹果’ 苹果,‘橘子’ 橘子,‘香蕉’ 香蕉,‘哈密瓜’ 哈密瓜,‘牛奶’ 牛奶))) loop
dbms_output.put_line(i.name||‘:’||i.苹果||i.橘子||i.香蕉||i.哈密瓜||i.牛奶);
end loop;
end;
–新建一张表numbers，只有一个字段nums，数据类型int，往表里插入100到200的所有数字
create table hr.numbers (nums int);
begin
for i in 100…200 loop
insert into hr.numbers values (i);
commit;
end loop;
end;
select *
from hr.numbers
–将numbers表里，两两相加等于288的数字输出出来，143+145=288 145+134=288
begin
for i in (select *
from hr.numbers) loop
for j in (select *
from hr.numbers) loop
if i.nums+j.nums=288 and i.nums<j.nums then dbms_output.put_line(i.nums||‘+’||j.nums||‘=’||(i.nums+j.nums));
end if;
end loop;
end loop;
end;
–输入a打印a至20的所有数，考虑a比20大的情况
declare
a int;
b int;
begin
a:=&值;
b:=20;
if a<b then for i in 1…20 loop
dbms_output.put_line(i);
end loop;
elsif a>b then for j in reverse b…a loop
dbms_output.put_line(j);
end loop;
else dbms_output.put_line(a);
end if;
end;
–新建一个员工表，去掉工资的最小值 当平均工资大于8000之后输出：公司裁员前平均工资：裁员后
create table cs20240528 as select * from hr.employees;
–drop table cs20240528;
declare
v_salary int;
b int;
begin
select avg(salary) into b
from cs20240528;
v_salary:=b;
while v_salary <= 8000 loop
delete from cs20240528
where salary = (select min(salary)
from cs20240528)
and rownum = 1;
select avg(salary) into v_salary
from cs20240528;
end loop;
commit;
dbms_output.put_line(‘公司裁员前平均工资：’||b||‘，裁员后：’||v_salary);
end ;
–输入一组顺序随机的数字，将其从小到大排序 7 6 4 8 9
declare
v_num varchar2(20);
v_length int;
a int;
v_jg int;
begin
v_num:=‘&值’;
v_length:=length(v_num);
for i in 1…v_length loop
a:=substr(v_num,1,1);
for j in 1…v_length-i loop
if a<substr(v_num,j+1,1) then a:=a;
else a:=substr(v_num,j+1,1);
end if;
end loop;
v_jg:=v_jg||a;
v_num:=replace(v_num,a,‘’);
end loop;
dbms_output.put_line(‘排序后：’||v_jg);
end;
补充一个函数，可以算作开窗函数也可以算作聚合函数，需要考虑数据对等的问题
[!列拼接]
listagg(把哪列里的数据拼起来,‘以什么作为分隔符’)within group(order by salary)
第二个参数不写的话，拼接的时候不放任何东西
把所有人的工资拼一起
select listagg(salary,‘*’)within group(order by salary)
from hr.employees;
[!组内列拼接]
listagg(把哪列里的数据拼起来,‘以什么作为分隔符’)within group(order by salary)over(partition by department_id)
把同一部门的所有人的工资拼一起
select listagg(salary,‘*’)within group(order by salary)over(partition by department_id)
from hr.employees;
默认设定变量
declare
变量名 表名.列名%type;
默认设定变量(表)
declare
变量名 表名%rowtype;
记录类型(变量集合)
declare
type … is record(变量a 数据类型 …)
索引表
declare
type … is table of 集合中的数据类型 index by 下标的数据类型
嵌套表
declare
type … is table of 集合中的数据类型 index by
子程序
命了名的plsql块，将一段逻辑或者功能，比如sql，plsql封装起来，在以后使用的时候可以调用
子程序的分类
存储过程–执行某一些操作
由sql，plsql组合在一起，为了执行某些特定任务的一个可执行单位，在使用的时候可以进行调用，
将一段逻辑封装好，一次编译多次使用
函数–执行某一些操作并返回值
为了实现某个特定功能，自己定义一些实现需求的功能，可以封装起来，提高效率，只要定义一次多次使用
子程序的各个部分
子程序的各个部分就是plsql的部分，它俩是一样的，声明部分，可执行部分，异常处理部分
存储过程和函数必须有声明部分，异常处理部分可以没有
子程序的优点
1 模块化
一个程序可以由多个逻辑组成，一个一个逻辑拼起来，构成一个程序
2 可重复性
一次编译多次使用，以后用的时候，直接调用
3 安全性
可以通过设置权限来使数据更安全，代码谁都能运行，但是存储过程不是
4 可以降低网络的通信量
客户端调用存储过程的时候只需要传入存储过程名和参数就可以，比传输sql语句要快的多
–创建存储过程
[!创建存储过程]
create /or replace/ procedure 存储过程名–(参数名 输入in/即输入又输出in out/输出 out 参数类型)
is/as
–声明变量;
begin
逻辑部分,执行部分;
–exception
错误处理;
end;
可有可无的：参数，变量，错误处理
创建的时候可以用 or replace 关键字
存储过程参数的三种模式
in 输入
定义参数是in类型参数，可以理解为存储过程就是in类型，在调用存储过程的时候，就要传进去一个参数，
必须接受一个参数，in模式也是最常见的，如果存储过程没有参数要输入可以不写，参数如果不声明
是什么类型，默认就是in类型
-创建存储过程，输出n行*
*
**
create procedure gc_1(i int)
is
xinghao varchar2(50);
begin
for j in 1…i loop
xinghao:=xinghao||‘*’;
dbms_output.put_line(xinghao);
end loop;
end;
–调用存储过程
调用in类型的存储过程
begin
存储过程名(参数);
end;
begin
gc_1();
end;
–输入一个日期，输出：早上好，今天是***
create or replace procedure gc_2(i in date)
is
a varchar2(20);
b varchar2(20);
begin
a:=‘早上好，今天是’;
b:=to_char(i,‘yyyy-mm-dd’);
dbms_output.put_line(a||b);
end;
begin
gc_2(sysdate);
end;
在sqlplus里调用
exec 存储过程名(参数);
在sqlplus里显示输出内容
set serverout on;
–out 输出
代表当前的存储过程最终要返回一个值，向调用它的地方返回值
create or replace procedure gc_3(j out int)
is
begin
j:=100;
end;
–调用out类型的存储过程
declare
a int;
begin
gc_3(a);
end;
–两个参数，一个in，一个out，执行存储过程之后打印in参数减1之后的结果，例如in参数是10，那就打印9
create or replace procedure gc_1(i in int,j out int)
is
begin
j:=i-1;
end;
declare
a int;
b int;
begin
a:=10;
gc_1(a,b);
dbms_output.put_line(b);
end;
–in out 即输入又输出
in和out合二为一
–两束交换
create or replace procedure gc_2(i in out int,j in out int)
is
zjs int;
begin
zjs:=i;
i:=j;
j:=zjs;
end;
declare
a int;
b int;
begin
a:=1;
b:=2;
gc_2(a,b);
dbms_output.put_line(‘a’||‘=’||a);
dbms_output.put_line(‘b’||‘=’||b);
end;
–删除存储过程
drop procedure 存储过程名;
drop procedure gc_2;
自定义函数
[!自定义函数]
语法
create function 函数名(参数名称 数据类型,…)不用写数据类型的长度，没有参数可以不写括号，数据类 型可以不一样
return 返回的结果的数据类型 --不写数据类型的长度，只能写一个
is
变量声明部分;
begin
逻辑部分;
dbms_output.put_line() --输出，一定要在return语句之前，语句到return就结束了
return 返回什么;
exception
异常处理部分，一般情况下不会用，但是有异常处理
end;
定义函数的限制
1 函数一般只能接受in类型的参数，不能接受out，in out类型的参数，它是通过return来返回值的，如果建了一个
out类型的函数，可以建，但是不能调用
2 函数的参数数据类型以及return返回结果的数据类型必须是数据库里自带的数据类型，不能plsql类型的，
int，varchar2，date
调用函数的方式
1 使用plsql块调用
2 使用sql语句调用，跟使用系统自带的函数一样
create function hello
return varchar2
is
begin
return ‘你好’;
end;
declare
a varchar2(20);
begin
a:=hello;
dbms_output.put_line(a);
end;
–创建一个函数，用户输入一个学号，返回该学生的名次（总分），返回：第*名
create or replace function pm(i int)
return varchar2
is
aa int;
begin
select b into aa
from (
select t1.stu_id,nvl(sum(t2.s_or),0) a,row_number()over(order by nvl(sum(t2.s_or),0) desc) b
from hr.stu t1
left join hr.score t2
on t1.stu_id = t2.stu_id
group by t1.stu_id)
where stu_id = i;
return '第'||aa||'名';
end;
select pm(9)
from dual
drop function pm;
–遍历索引表
loop
–从前往后
declare
–定义索引表
type indextable is table of varchar2(20) index by pls_integer;
–声明成变量
v_indextable indextable;
–声明变量存储下标
v_index int;
begin
v_indextable(1):=‘a’;
v_indextable(2):=‘b’;
v_indextable(3):=‘c’;
v_indextable(-8):=‘d’;
v_indextable(0):=‘e’;
v_index:=v_indextable.first; --获取第一个元素的下标是多少 -8
loop
dbms_output.put_line(v_indextable(v_index)); --遍历值
exit when v_index=v_indextable.last;
v_index:=v_indextable.next(v_index);
end loop;
end;
–从后往前
declare
–定义索引表
type indextable is table of varchar2(20) index by pls_integer;
–声明成变量
v_indextable indextable;
–声明变量存储下标
v_index int;
begin
v_indextable(1):=‘a’;
v_indextable(2):=‘b’;
v_indextable(3):=‘c’;
v_indextable(-8):=‘d’;
v_indextable(0):=‘e’;
v_index:=v_indextable.last; --获取最后一个元素的下标是多少 -8
loop
dbms_output.put_line(v_indextable(v_index)); --遍历值
exit when v_index=v_indextable.first;
v_index:=v_indextable.prior(v_index);
end loop;
end;
–for
使用for循环遍历，下标一定是连续的
declare
type indextable is table of varchar2(20) index by pls_integer;
--声明成变量
v_indextable indextable;
begin
v_indextable(1):='a';
v_indextable(2):='b';
v_indextable(3):='c';
v_indextable(4):='d';
v_indextable(5):='e';
for j in v_indextable.first..v_indextable.last loop
dbms_output.put_line(v_indextable(j));
end loop;
end;
–创建判断质数函数 输入一个数 如果是质数返回是质数 不是质数返回不是质数
/*
1000以内的质数有168个：
2 3 5 7 11 13 17 19 23 29 31 37 41 43 47 53 59 61 67 71 73 79 83 89 97
101 103 107 109 113 127 131 137 139 149 151 157 163 167 173 179 181 191 193 197 199
211 223 227 229 233 239 241 251 257 263 269 271 277 281 283 293
307 311 313 317 331 337 347 349 353 359 367 373 379 383 389 397
401 409 419 421 431 433 439 443 449 457 461 463 467 479 487 491 499
503 509 521 523 541 547 557 563 569 571 577 587 593 599
601 607 613 617 619 631 641 643 647 653 659 661 673 677 683 691
701 709 719 727 733 739 743 751 757 761 769 773 787 797
809 811 821 823 827 829 839 853 857 859 863 877 881 883 887
907 911 919 929 937 941 947 953 967 971 977 983 991 997 */
create or replace function hs_1(a int)
return varchar2
is
begin
for i in 2..a loop
if a=i then return a||'是质数';
else if mod(a,i)=0 then return a||'不是质数';
exit;
end if;
end if;
end loop;
end;
用case when 嵌套做
新建一个员工表 输出和修改数据
以员工号和部门号作为参数，修改员工所在的部门为所输入的部门号
如果输入的部门号和该员工号本来的部门相同显示’部门号不变’
如果修改成功，则显示“员工由……号部门调入……号部门
如果不存在该员工，则显示“员工号不存在，请输入正确的员工号
如果不存在该部门，则显示“该部门不存在，请输入正确的部门号
如果员工号和部门号都不存在，则显示:’请重新输入 */
create table cs20240531_a as select * from hr.employees
create or replace procedure p_1(a int,b int)
is
v_empid int;
v_depid int;
v_count_depid int;
begin
select count(employee_id),max(department_id) into v_empid,v_depid
from cs20240531_a
where employee_id = a;
select count(department_id) into v_count_depid
from cs20240531_a
where department_id = b;
case when v_empid = 0 and v_count_depid = 0 then dbms_output.put_line('请重新输入');
when v_empid = 0 then dbms_output.put_line('员工号不存在，请输入正确的员工号');
when v_count_depid = 0 then dbms_output.put_line('该部门不存在，请输入正确的部门号');
else case when v_depid!=b then update cs20240531_a
set department_id = b
where employee_id= a;
dbms_output.put_line('员工由'||v_depid||'号部门调入'||b||'号部门');
else dbms_output.put_line('部门号不变');
end case;
end case;
commit;
end;
begin
p_1(100,50);
end;
标号,goto,null
标号：<<标号名>> 标记一个位置
goto: 将执行直接跳转到某一个位置上
null: 什么都不做，置空操作
–不用循环，用goto 标号 null实现输出a-20
declare
a int;
b int;
begin
a:=&变量;
b:=20;
<<aa>> --标记这个位置名叫aa
dbms_output.put_line(a);
a:=a+1;
if a<=b then goto aa; --如果a小于等于b那就跳到aa标号所在的位置
else goto bb;
end if;
<<bb>> null; --跳到b之后什么都不做
dbms_output.put_line('结束');
end;
–不用循环，用goto,标号,null，考虑a比20大的情况，输出a-20
declare
a int;
b int;
begin
a:=&变量;
b:=20;
<<aa>>
if a<b then dbms_output.put_line(a);
a:=a+1;
goto aa;
elsif a>b then dbms_output.put_line(a);
a:=a-1;
goto aa;
else dbms_output.put_line(a);
end if;
end;
–遍历索引表
while
[!遍历索引表]
declare
type index_table is table of number(20) index by pls_integer;
t index_table;
n number;
begin
t(-1):=21;
t(-2):=23;
t(3):=40;
n:=t.first;
while n<=t.last loop
dbms_output.put_line(t(n));
n:=t.next(n);
end loop;
end;
–遍历嵌套表
–loop
declare
–定义嵌套表类型
type mytable is table of varchar2(20);
–声明成变量
v1 mytable;
–声明变量储存下标
n number;
–声明变量储存值
v varchar2(20);
begin
v1:=mytable(‘a’,‘b’,‘c’); --为嵌套表类型赋值
n:=v1.first; --取v1里第一个下标的值给n
v:=v1(n); --将下标n的值给v
loop
dbms_output.put_line(v);
exit when n=v1.last;
n:=v1.next(n);
v:=v1(n);
end loop;
end;
–while
declare
–定义嵌套表类型
type mytable is table of varchar2(20);
–声明成变量
v1 mytable;
–声明变量储存下标
n number;
–声明变量储存值
v varchar2(20);
begin
v1:=mytable(‘a’,‘b’,‘c’); --为嵌套表类型赋值
n:=v1.first; --取v1里第一个下标的值给n
while n<=v1.last loop
dbms_output.put_line(v1(n));
n:=v1.next(n);
end loop;
end;
–for
declare
–定义嵌套表类型
type mytable is table of varchar2(20);
–声明成变量
v1 mytable;
begin
v1:=mytable(‘a’,‘b’,‘c’); --为嵌套表类型赋值
for n in v1.first…v1.last loop
dbms_output.put_line(v1(n));
end loop;
end;
–嵌套表在数据库中的使用
–创建嵌套表
create type mytable is table of varchar2(20);
declare
–声明变量 mytable是一个嵌套表类型
v1 mytable;
begin
v1:=mytable(‘a’,‘b’,‘c’); --为嵌套表类型赋值
for n in v1.first…v1.last loop
dbms_output.put_line(v1(n));
end loop;
end;
–创建表的时候也可以使用嵌套表类型
create table cs5(id number,
name mytable)nested table name store as myname;
--存值
insert into cs5(id,name) values (1,mytable('小明','小张','小李'));
insert into cs5(id,name) values (2,mytable('豆豆','点点','花花'));
select *
from cs5
commit;
--查询嵌套表中的数据
select *
from table(select name
from cs5
where id = 1);
--删除时要注意，先删除表再删除类型
drop table cs5;
drop type mytable;
–变长数组
注意事项
1 使用正整数作为下标，必须是连续的
2 元素的个数有限制，可以使用在plsql中也可以使用在数据库当中
3 和嵌套表一样，使用之前先初始化
4 使用v.extend扩展数组的时候不能超过声明时的长度
语法格式:
type 类型名 is varray(5) of 保存的数据的数据类型;
declare
--声明变长数组类型
type myvar is varray(5) of varchar2(20);
--声明成变量
v myvar;
begin
v:=myvar('a','b','c','d','e');
dbms_output.put_line(v(1));
dbms_output.put_line(v(2));
dbms_output.put_line(v(3));
dbms_output.put_line(v(4));
dbms_output.put_line(v(5));
end;
–遍历变长数组
–loop
declare
--声明变长数组类型
type myvar is varray(5) of varchar2(20);
--声明成变量
v myvar;
--存储下标
n number;
begin
v:=myvar('a','b','c','d','e');
n:=v.first;
loop
dbms_output.put_line(v(n));
exit when n=v.last;
n:=v.next(n);
end loop;
end;
–for
declare
--声明变长数组类型
type myvar is varray(5) of varchar2(20);
--声明成变量
v myvar;
begin
v:=myvar('a','b','c','d','e');
for n in v.first..v.last loop
dbms_output.put_line(v(n));
end loop;
end;
–while
declare
–声明变长数组类型
type myvar is varray(5) of varchar2(20);
–声明成变量
v myvar;
–声明变量储存下标
n number;
begin
v:=myvar(‘a’,‘b’,‘c’,‘d’,‘e’);
n:=v.first;
while n<=v.last loop
dbms_output.put_line(v(n));
n:=v.next(n);
end loop;
end;
–变长数组在数据库中的使用
create type 类型名称 is varray(长度) of 数据类型;
create type myvar is varray(5) of varchar2(20);
declare
v myvar;
begin
v:=myvar('a','b','c','d','e');
for n in v.first..v.last loop
dbms_output.put_line(v(n));
end loop;
end;
–建表的时候也可以使用
–语法格式
create table cs6(id int,
name myvar);
-- 存值
insert into cs6(id,name) values (1,myvar('小明','小张','小李','小白','小黑'));
insert into cs6(id,name) values (2,myvar('明','张','李'));
insert into cs6(id,name) values (1,myvar('小明','小张','小李','小白','小黑','a'));
–查询
select *
from table(select name
from cs6
where id = 2);
–bulk collect into 语句
可以把一组数据取出来存入一个集合类型当中，之前select… into 变量，只能查出一条数据保存到一个
变量当中 ，但是select … bulk collect into 集合类型的变量，可以取多条数据存储在集合中
declare
v_sname hr.stu.s_name%type;
--定义嵌套表
type mytable is table of hr.stu.s_name%type;
--定义成变量
v mytable;
begin
select s_name into v_sname
from hr.stu
where stu_id = 1;
dbms_output.put_line(v_sname);
dbms_output.put_line('------------------------');
select s_name bulk collect into v
from stu;
--遍历集合中的所有元素
for n in v.first..v.last loop
dbms_output.put_line(v(n));
end loop;
end;
–批量绑定
语法格式:
forall 变量 in 集合
–复制一张表
create table cs8 as select * from hr.employees
–删除所有有部门编号的员工信息
declare
–定义嵌套表
type mytable is table of hr.departments.department_id%type;
–定义成变量
v mytable;
begin
select department_id bulk collect into v
from departments;
forall n in v.first…v.last
delete from cs8 where department_id = v(n);
end;
动态执行sql
之前在plsql块里写的都是dml,增删改，或者就是查询，commit，没写过ddl，ddl在plsql里比较特殊
–在plsql里建一张员工表
declare
begin
create table cs20240603 as select * from hr.employees;
end;-----报错
正常来说plsql里不允许出现ddl语句，但是ddl如果一定要被放在plsql里执行，需要进行一些处理，不能直接
写在里面，处理的方式就叫做动态sql
execute immediate ‘ddl语句’;
execute immediate然后把ddl语句放在后面用单引号围起来，代表把这个语句动态封装起来
–在plsql里建一张员工表
declare
begin
execute immediate ‘create table cs20240603 as select * from hr.employees’;
end;
用动态sql建了一张表，如果在同一个plsql里直接对它进行dml
declare
begin
execute immediate ‘create table cs20240603 as select * from hr.employees’;
delete from cs20240603 where salary < 10000;
end;
如果在一个plsql里动态封装了一个ddl，不能直接和它交互，如果需要直接进行交互的话，需要把dml也进行
动态封装
declare
begin
execute immediate 'create table cs20240603 as select * from hr.employees';
execute immediate 'delete from cs20240603 where hire_date = to_date(''2003-06-17'',''yyyy-mm-dd'')';
commit;
end;
select *
from cs20240603
注意事项
1 如果动态封装的语句里有日期或者有被单引号引上的内容，那么需要在内容外再加一层单引号
execute immediate ‘delete from cs20240603 where hire_date = to_date(’‘2003-06-17’‘,’‘yyyy-mm-dd’‘)’;
2 如果在plsql里动态封装了一个ddl，如果想直接和它进行交互需要把dml也进行动态封装
execute immediate 的其它用法
第一种用法
将需要执行那个的sql语句存入一个字符串类型的变量里，然后用execute immediate 变量名 来执行这个变量
的语句
create or replace procedure c_1 (b1 in varchar2)
is
yj varchar2(1000);
begin
yj:='create table '||b1||'(id int,name varchar2(22))';
execute immediate yj;
end;
begin
c_1('cs20240803_2');
end;
第二种用法
将需要执行的sql语句存入一个字符串类型的变量里，然后用execute immediate 变量名 来执行这个变量
的语句，并将结果赋值给一个变量
–找last_name是King的工资，然后把结果存入a
declare
yj varchar2(2000);
a int;
b varchar2(20);
begin
yj:=‘select salary,last_name from hr.employees where last_name = ‘‘Kochhar’’’;
execute immediate yj into a,b;
dbms_output.put_line(a);
dbms_output.put_line(b);
end;
列的位置数量，类型要一一对应，语句的结果也必须是单行数据
第三种用法
带参数的动态sql，将sql里需要当成变量的内容，使用标记:1，:2代表第一个和第二个参数，然后存入到
一个字符串类型的变量里，然后用execute immediate 变量名来执行，并且在执行的时候传入变量值
–找出部门号是30，并且工资大于3000的人
declare
yj varchar2(2000);
a int;
begin
yj:='select salary
from hr.employees
where department_id = :1
and salary > :2
and rownum = :1';
execute immediate yj into a using 30,3000,1;
dbms_output.put_line(a);
end;
select *
from hr.employees
where salary>3000
and department_id=30
and rownum<3
第四种用法
执行多行sql语句
chr(10)表示把ascii码值为10的转换为原本的字符，10原本的字符是换行的意思，回车是13
–两个sql语句放在一起执行，删除表里是30号部门的，删除表里员工号小于110的
create table cs20240603_3 as select * from hr.employees;
declare
v_sql1 varchar2(2000);
v_sql2 varchar2(2000);
v_sql varchar2(2000);
begin
v_sql1:= ‘delete from cs20240603_3
where department_id = 30;’;
v_sql2:=‘delete from cs20240603_3
where employee_id < 110;’;
v_sql:=‘begin’||chr(10)||v_sql1||chr(10)||v_sql2||chr(10)||‘end;’;
execute immediate v_sql;
end;
—select * from cs20240603_3
—drop table cs20240603_3
create table cs20240603_3 as select * from hr.employees;
–存储过程里直接查询当天的表
sql1:=‘select from emp’||to_char(sysdate,yyyymmdd);
execute immediate sql1;
–returning into 子句，返回值
一般和delete，update，insert一起使用，它的功能类似于delete/update/insert + select语句，相比来说
性能会有一些提高，少一次查询
–returning into配合insert的用法
–告诉你新插入的数据是什么
create table t1 (id int,name varchar2(20));
declare
v_id int;
v_name varchar2(20);
begin
insert into t1 values (1,‘小明’) returning id,name into v_id,v_name;
dbms_output.put_line(v_id||’ '||v_name);
end;
–效果等同于
declare
v_id int;
v_name varchar2(20);
begin
insert into t1 values (1,‘小明’);
select id,name into v_id,v_name
from t1
where id =1;
dbms_output.put_line(v_id||’ '||v_name);
end;
–delete的用法
–告诉你把什么数据给删除了
truncate table t1
select *
from t1;
insert into t1 values (2,‘小黑’)
declare
v_id int;
v_name varchar2(20);
begin
delete from t1
where id = 1 returning id,name into v_id,v_name;
dbms_output.put_line(v_id||v_name);
end;
update的用法
–哪条数据被更新了，显示这条数据更新之后的样子
declare
v_id int;
v_name varchar2(20);
begin
update t1
set id = 3
where id = 2 returning id,name into v_id,v_name;
dbms_output.put_line(v_id||v_name);
end;
注意事项
1 正常来说returning into只能返回单行数据，如果要返回多行需要配合table类型，索引表
2 insert 返回插入之后的数据，delete返回删除之前的数据，update返回更新后的值
–returning into返回多行的情况 update
declare
type t1_record is record --先声明一个变量集合，变量集合里有两种类型，int和varchar2，跟t1的列保持一致
(v_id t1.id%type,v_name t1.name%type);
type t1_table is table of t1_record index by binary_integer;
v_t1_table t1_table;
begin
update t1
set name = ‘换好了’
where id = 1
or id = 2
returning t1.id,t1.name bulk collect into v_t1_table;
–更新了多行数据，如果返回这些数据的话要用bulk collect into给一个table类型的变量
–列的个数要和table类型保持一致
if v_t1_table.count >=1 then
for i in v_t1_table.first…v_t1_table.last loop
dbms_output.put_line(v_t1_table(i).v_id||’ '||v_t1_table(i).v_name);
end loop;
end if;
end;
–复制一张分数表，将小于80分的成绩删掉，返回被删掉的数据
create table fs as select * from hr.score;
declare
type fs_record is record (v_stuid fs.stu_id%type,
v_cid fs.c_id%type,
v_sor fs.s_or%type);
type fs_table is table of fs_record index by binary_integer;
v_fs_table fs_table;
begin
delete from fs
where s_or <80
returning fs.stu_id,fs.c_id,fs.s_or bulk collect into v_fs_table;
for i in v_fs_table.first..v_fs_table.last loop
dbms_output.put_line(v_fs_table(i).v_stuid||' '||v_fs_table(i).v_cid||' '||v_fs_table(i).v_sor);
end loop;
end;
–returning into 配合动态sql返回值
–返回单行的情况
select *
from t1
declare
v_id t1.id%type;
v_name t1.name%type;
v_update varchar2(200);
begin
v_update:=‘update t1
set name =’‘好了’’
where id = :1
returning id,name into :2,:3’;
execute immediate v_update using 3 returning into v_id,v_name;
commit;
dbms_output.put_line(v_id||v_name);
end;
–返回多行的情况
–配合动态sql返回多行的时候，不能和record一起使用，只能单独声明table类型
declare
type t1_id_table is table of t1.id%type;
type t1_name_table is table of t1.name%type;
v_update varchar2(200);
v_id t1_id_table;
v_name t1_name_table;
begin
v_update:= ‘update t1
set name = ‘‘小白’’
returning id,name into :1,:2’;
execute immediate v_update returning bulk collect into v_id ,v_name;
for i in v_id.first … v_id.last loop
dbms_output.put_line(v_id(i)||v_name(i));
end loop;
end;
select *
from t1
----------------------------------------------------------=
–游标
什么是游标
select *
from hr.employees
检索每行数据是否满足条件，然后一行一行取数据，放入内存当中，最后再返回给用户,
游标会把每条数据都提出来暂时存在游标中，依次向下选择，一直走到最后一条，全部取完之后结束，
而且游标只能向下走，不能回头，逐行处理查询结果，可以通过游标的特性来访问数据，自己指定游标来处理数据
，每次游标提取出来的行可以进行一些处理，进行处理了之后游标再向下移动，以编程的方式访问数据
游标分为3种类型
1 隐式游标
在plsql程序中执行一个dml语句的时候自动创建隐式游标，隐式游标不需要管，正常的游标需要你声明，
打开，关闭，但是隐式游标不需要是自动创建的，隐式游标的名字是默认的，叫sql，不区分大小写，一般用于
只返回一行的sql语句
2 显示游标
用的最多的，我们可以指定一个游标对应着什么结果集，对应哪一个selece结果，显式游标需要定义，声明，
使用的时候需要打开和获取数据，使用完需要关闭，多用于处理返回多行数据的查询
3 ref游标
也叫参照游标，也是处理select查询的结果集的，但它只有在运行的时候猜能确定这个游标对应的结果集是什么
隐式游标
系统自动创建的，执行dml语句的时候，又或者select … into 的时候会自动创建，由oracle自己来声明，打开，关闭
，它的名字叫sql，使用隐式游标能得到游标属性，刚刚执行的dml语句的信息，主要用来处理dml语句，实际开发
中一般使用隐式游标来判断更新数据行或删除数据行的情况
游标的属性
[!游标属性]
sql%rowcount
%found
%notfound
%rowcount
%isopen
显式游标和隐式游标都具有4个属性，通过这些属性可以得知sql语句执行结果以及游标的状态信息，使用时
游标名接上属性，例如sql%rowcount，中间没有空格
%found 布尔型属性
刚刚执行的dml语句，产生了作用没，它发没发现数据，如果刚刚的dml影响了一行或者多行数据的话，%found的
结果就是true，它不管你影响了多少条，只返回真或假，影响了还是没影响
create table ygb as select * from hr.employees;
删掉100号员工的信息
declare
a boolean;
begin
delete from ygb
where employee_id = 100;
a:=sql%found;
if a then dbms_output.put_line(‘任务完成’);
else dbms_output.put_line(‘未删除任何数据’);
end if;
end;
%notfound 布尔型属性
刚刚执行过的dml语句，如果没有任何影响，返回true，否则返回false
declare
a boolean;
begin
delete from ygb
where employee_id = 100;
a:=sql%notfound;
if a then dbms_output.put_line(‘未删除任何数据’);
else dbms_output.put_line(‘任务完成’);
end if;
end;
%rowcount 数值型属性 记录上一个dml语句具体处理了多少行数据
删除掉90号部门的所有人
declare
a int;
begin
delete from ygb
where department_id = 90;
a:=sql%rowcount;
dbms_output.put_line(‘删除了’||a||‘行数据’);
end;
注意事项
1 如果将%rowcount放在游标里的话，那%rowcount就记录抽取过的记录行数，也可以理解为当前游标所在的行号
%isopen 布尔型属性
判断游标是否打开，隐式游标永远那都是false
declare
a boolean;
begin
delete from ygb
where department_id = 90;
a:=sql%isopen;
if a then dbms_output.put_line(‘打开’);
else dbms_output.put_line(‘关闭’);
end if;
end;
[!显示游标]
显示游标在plsql块的声明部分定义查询，该查询可以返回多行
先定义好一个游标，然后打开游标，此时游标就对应着某个结果集，之后一行一行的提取数据，再放到一个
声明好的变量里，进行相应的处理，一直循环提取数据，直到取到结果集的最后一条数据，游标就被使用完了
最后关闭游标就可以了，不然会一直占着内容使用显式游标
4步，声明游标，打开游标，提取数据，关闭游标
loop和游标
使用游标将employees的所有数据都提取出来
declare
emp hr.employees%rowtype;先声明一个记录类型用来存表里的数据 cursor c_1 is select * from hr.employees;声明游标，游标名叫c_1，游标里存储的结果集是is后面的结果集begin open c_1; --打开游标，想要使用之前必须打开 loop --开始循环 fetch c_1 into emp; --从游标里取一条数据放到emp这个变量里，每次只取一行，取出来一行游标向下走一格并且只能向下不能回头`
exit when c_1%notfound; 当c_1这个游标取不到数据的时候，就退出循环
dbms_output.put_line(emp.employee_id||’ '||emp.salary);
–取出来一行，输出一行
end loop;
close c_1; --关闭游标
end ;
while和游标
要在进入循环之前先取一条数据，让%found得到true，正常进入第一次循环
declare
emp hr.employees%rowtype; --先声明一个记录类型用来存表里的数据
cursor c_1 is select *
from hr.employees; --声明游标，游标名叫c_1，游标里存储的结果集是is后面的结果集
begin
open c_1; --打开游标
fetch c_1 into emp; --让%found获取一条数据，为了正常进入循环
while c_1%found loop --c_1游标%found为真就循环，否则不循环
dbms_output.put_line(emp.employee_id||emp.salary);
fetch c_1 into emp; --每次取一条，取完游标向下走
end loop;
close c_1;
end;
for和游标
for循环自动打开游标，自动获取数据，自动关闭游标，比loop和while都要方便
declare
cursor c_1 is select *
from hr.employees; --声明游标，游标名叫c_1，游标里存储的结果集是is后面的结果集
begin
for i in c_1 loop
dbms_output.put_line(i.employee_id||i.salary);
end loop;
end;–用游标配合while获取所有只买了苹果的人(关联)
declare
a varchar2(20);
cursor c_1 is select t1.name
from hr.shuiguo9 t1
left join hr.shuiguo9 t2
on t1.name = t2.name
and t1.shuiguo = ‘苹果’
and t2.shuiguo != ‘苹果’
where t1.shuiguo = ‘苹果’
and t2.shuiguo is null;
begin
open c_1;
fetch c_1 into a;
while c_1 %found loop
dbms_output.put_line(a);
fetch c_1 into a;
end loop;
close c_1;
end;
新建一个员工表，将50号部门的人工资增加1000，并且将在2023年入职的人从员工表里删除，
返回本次处理的结果（更新成没成功，如果成功了返回公司开支成本会增加多少，
删除2023年入职的人员工表里一定没有，所以返回没有2023年入职的人)
create table cs20240604 as select * from hr.employees;
declare
数量 int;
是否成功 boolean;
begin
update cs20240604
set salary=salary+1000
where department_id = 50;
数量:=sql%rowcount;
是否成功:=sql%found;
if 是否成功 then dbms_output.put_line(‘更新成功,公司开支成本增加’||(数量1000)||‘元’);
else dbms_output.put_line(‘更新失败’);
end if;
delete from cs20240604
where to_char(hire_date,‘yyyy’) = ‘2023’;
数量:=sql%rowcount;
是否成功:=sql%found;
if 是否成功 then dbms_output.put_line(‘删除了’||数量||‘个人’);
else dbms_output.put_line(‘删除失败,没有2023年入职的人’);
end if;
end;
–带参数显示游标
声明游标的时候可以指定参数
–用游标获取输入的部门所有员工的id和salary
create or replace procedure c_1(a int)
is
cursor yb1(b int) is select *
from hr.employees
where department_id = b;
c hr.employees%rowtype;
begin
open yb1(a);
fetch yb1 into c;
while yb1%found loop
dbms_output.put_line(c.employee_id||' '||c.salary);
fetch yb1 into c;
end loop;
close yb1;
end;
begin
c_1(90);
end;
[!用游标，输入指定的人,输出他买了什么水果(人 产生 水果)]
create or replace procedure c_2
is
c hr.shuiguo9%rowtype;
cursor yb(b varchar2) is select * from hr.shuiguo9 where name = b;
begin
open yb(‘大乔’);
fetch yb into c;
while yb%found loop
dbms_output.put_line(c.shuiguo);
fetch yb into c;
end loop;
close yb;
end;
begin
c_2;
end;
–使用显式游标删除或更新结果集中的行
之前显式游标用来获取结果集中的数据，通过带参游标获取指定范围的数据，还可以通过它删除或更新数据
–需要加一些关键字
cursor 游标名 is select … from 表名 where 条件 把要更新的数据找出来 for update;
update 表名
set 将数据更新成什么
where current of 游标名;
行级锁
[!行级锁]
1 for update其实是一种行级锁，也叫排它锁，只用于plsql程序块，一旦用户对某些行施加了行级锁，那么该用户可以查询也可以更新被加锁的数据行，其它用户只能查询不能更新，如果其它用户也想对其更新的话，需要也对它施加行级锁，但是即使很多用户都对这些数据施加了行级锁，但也不允许两个事务同时对其更新，有一方正在进行更新的时候，直到提交或者回滚之前，行锁永远是独占锁
只有当出现3种情况的时候，才会释放行级锁
1 执行提交commit语句
2 退出数据库
3 程序停止运行
行级锁是在事务级别上生效的。在当前事务中锁定的行将在事务提交或回滚时释放。
如果其他会话尝试选择锁定的行，它们将会阻塞，直到持有锁的事务完成或超时。
锁相关（共享锁、排他锁、表级锁）
共享锁（Shared Lock）
共享锁允许多个事务同时读取同一行数据，但是阻止其他事务获取排他锁（写锁）。
– 事务 A
BEGIN TRANSACTION;
SELECT * FROM your_table WHERE id = 123 FOR SHARE;
– 在事务 A 持有共享锁期间，其他事务可以读取但不能更新该行数据
COMMIT;
– 其他事务可以在事务 A 提交后继续读取该行数据
排它锁（Exclusive Lock）
排它锁用于防止其他事务修改被锁定的行，直到持有锁的事务释放锁。也称为行级锁
– 事务 A
BEGIN TRANSACTION;
SELECT * FROM your_table WHERE id = 123 FOR UPDATE;
– 在事务 A 持有排它锁期间，其他事务不能读取或更新该行数据
UPDATE your_table SET column1 = ‘new value’ WHERE id = 123;
COMMIT;
– 其他事务可以在事务 A 提交后继续读取或更新该行数据
表级锁（Table-level Lock）
表级锁是锁定整个表的锁类型
– 事务 A
BEGIN TRANSACTION;
LOCK TABLE your_table IN EXCLUSIVE MODE;
– 在事务 A 持有表级锁期间，其他事务不能读取或修改整个表的数据
UPDATE your_table SET column1 = ‘new value’ WHERE id = 123;
COMMIT;
– 其他事务可以在事务 A 提交后继续读取或更新该表的数据
–新建一张学生，把一号学生和二号学生的成绩都更新成0
create table sc as select * from hr.score;
declare
v_score hr.score%rowtype;
cursor c_1 is select *
from sc
where stu_id in (1,2)
for update;
begin
open c_1;
fetch c_1 into v_score;
while c_1%found loop
update sc
set s_or =0
where current of c_1;
fetch c_1 into v_score;
end loop;
close c_1;
end;
select *
from sc
–新建一张学生，把一号学生和二号学生的成绩都删除
declare
v_score hr.score%rowtype;
cursor c_1 is select *
from sc
where stu_id in (1,2)
for update;
begin
open c_1;
fetch c_1 into v_score;
while c_1%found loop
delete from sc
where current of c_1;
fetch c_1 into v_score;
end loop;
close c_1;
end;
--新建一个水果表，用游标将所有苹果换成火龙果
create table shuiguo1 as select * from hr.shuiguo9
declare
a varchar2(20);
cursor c_1 is select shuiguo
from shuiguo1
where shuiguo = '苹果'
for update;
begin
open c_1;
fetch c_1 into a;
while c_1%found loop
update shuiguo1
set shuiguo='火龙果'
where current of c_1;
fetch c_1 into a;
end loop;
close c_1;
end;
create table 学生信息(学号 int,
姓名 varchar2(6),
性别 varchar2(3),
班级 varchar2(4));
insert into 学生信息(学号,姓名,性别,班级)values(1,'陈婉茹','女','11班');
insert into 学生信息(学号,姓名,性别,班级)values(2,'木沐','女','11班');
insert into 学生信息(学号,姓名,性别,班级)values(3,'君乔','男','11班');
insert into 学生信息(学号,姓名,性别,班级)values(4,'谢婉婉','女','11班');
insert into 学生信息(学号,姓名,性别,班级)values(5,'小蓝','女','10班');
insert into 学生信息(学号,姓名,性别,班级)values(6,'江青','男','10班');
insert into 学生信息(学号,姓名,性别,班级)values(7,'长歌','男','10班');
insert into 学生信息(学号,姓名,性别,班级)values(8,'冬雨','女','10班');
create table 分数信息(学号 int,
科目 varchar2(6),
分数 number(10));
insert into 分数信息(学号,科目,分数)values(1,'语文','93');
insert into 分数信息(学号,科目,分数)values(1,'数学','101');
insert into 分数信息(学号,科目,分数)values(1,'英语','71');
insert into 分数信息(学号,科目,分数)values(2,'数学','120');
insert into 分数信息(学号,科目,分数)values(2,'英语','80');
insert into 分数信息(学号,科目,分数)values(3,'语文','103');
insert into 分数信息(学号,科目,分数)values(4,'语文','82');
insert into 分数信息(学号,科目,分数)values(4,'数学','73');
insert into 分数信息(学号,科目,分数)values(4,'英语','90');
insert into 分数信息(学号,科目,分数)values(5,'语文','103');
insert into 分数信息(学号,科目,分数)values(5,'数学','61');
insert into 分数信息(学号,科目,分数)values(6,'数学','100');
insert into 分数信息(学号,科目,分数)values(7,'语文','94');
insert into 分数信息(学号,科目,分数)values(7,'数学','108');
insert into 分数信息(学号,科目,分数)values(8,'数学','67');
insert into 分数信息(学号,科目,分数)values(8,'英语','59');
/*1，输入科目，返回科目最高的分数，以及最高分对应的人，如果最高分相同，
取学号最小的，展示学号，姓名，分数，科目*/
create or replace procedure p1(i varchar2) --输入一个科目
is
zgf int; --指定科目最高分
xh int; --学号
xm varchar2(20); --姓名
fs int; --分数
km varchar2(20); --科目
begin
select max(分数) into zgf
from 分数信息
where 科目 = i;
select * into xh,xm,fs,km
from (
select t1.学号,姓名,分数,科目
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
where 分数 = zgf
and 科目 = i
order by 1)
where rownum = 1;
dbms_output.put_line(xh||' '||xm||' '||fs||' '||km);
end;
begin
p1('语文');
end;
/*2，输入班级，返回每个科目的平均分*/
create or replace procedure p_2(i varchar2)
is
begin
for j in(select 科目 a,trunc(avg(分数),1) b
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
where 班级 = i
group by 科目) loop
dbms_output.put_line(j.a||' '||j.b);
end loop;
end;
begin
p_2('11班');
end;
/*3，数学课程满分120，语文课程满分150，英语课程满分100
现有评分规则:
男生单科成绩达到满分80%优秀，70%中等，60%及格
女生单科成绩达到满分85%优秀，75%中等，65%及格
输入学生姓名，返回结果显示学生成绩的评级*/
create or replace procedure p_3(i varchar2)
is
a varchar2(20);
b varchar2(20);
c varchar2(20);
begin
select nvl(max(case when 性别='男' and 科目='数学' and 分数>120*0.8 then '优秀'
when 性别='男' and 科目='数学' and 分数>120*0.7 then '中等'
when 性别='男' and 科目='数学' and 分数>120*0.6 then '及格'
when 性别='女' and 科目='数学' and 分数>120*0.85 then '优秀'
when 性别='女' and 科目='数学' and 分数>120*0.75 then '中等'
when 性别='女' and 科目='数学' and 分数>120*0.65 then '及格' end),'不及格') as 数学,
nvl(max(case when 性别='男' and 科目='语文' and 分数>150*0.8 then '优秀'
when 性别='男' and 科目='语文' and 分数>150*0.7 then '中等'
when 性别='男' and 科目='语文' and 分数>150*0.6 then '及格'
when 性别='女' and 科目='语文' and 分数>150*0.85 then '优秀'
when 性别='女' and 科目='语文' and 分数>150*0.75 then '中等'
when 性别='女' and 科目='语文' and 分数>150*0.65 then '及格' end),'不及格') as 语文,
nvl(max(case when 性别='男' and 科目='英语' and 分数>100*0.8 then '优秀'
when 性别='男' and 科目='英语' and 分数>100*0.7 then '中等'
when 性别='男' and 科目='英语' and 分数>100*0.6 then '及格'
when 性别='女' and 科目='英语' and 分数>100*0.85 then '优秀'
when 性别='女' and 科目='英语' and 分数>100*0.75 then '中等'
when 性别='女' and 科目='英语' and 分数>100*0.65 then '及格' end),'不及格') as 英语 into a,b,c
from 学生信息 t1
left join 分数信息 t2
on t1.学号 = t2.学号
where 姓名 = i;
dbms_output.put_line(i||' '||'数学:'||a||'语文:'||b||'英语:'||c);
end ;
begin
p_3(‘陈婉茹’);
end;
4，输入学生姓名，返回和输入名字考过科目完全相同的人，他们的总分，如果输入了错误的学生名字，输出
没有该学生，没有和输入名字考试科目完全相同的，输出‘没有和输入名字考试科目完全相同的’;
–关联
create or replace procedure p4(i varchar2)
is
a int;--计数
xm varchar2(20);
zf int;
begin
select count(*) into a
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
where 姓名 = i;
if a=0 then dbms_output.put_line('没有该学生');
end if;
if a=3 then select * into xm,zf
from
(select 姓名,sum(分数) 总分
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
where 姓名 != '陈婉茹'
group by 姓名
having count(*) = 3)
where rownum = 1;
for j in(select 姓名,sum(分数) 总分
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
where 姓名 != '陈婉茹'
group by 姓名
having count(*) = 3)loop
dbms_output.put_line(j.姓名||' '||j.总分);
end loop;
elsif a=2 then select * into xm,zf
from
(select 姓名,sum(分数) 总分
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
where 姓名 in (select t7.姓名
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
and t1.姓名 = i
inner join 分数信息 t3
on t3.学号 = t2.学号
and t2.科目 > t3.科目
inner join 分数信息 t4
on t4.科目 = t2.科目
inner join 分数信息 t5
on t5.科目 = t3.科目
and t5.学号 = t4.学号
left join 分数信息 t6
on t6.学号 =t5.学号
and t6.科目 != t2.科目
and t6.科目 != t3.科目
left join 学生信息 t7
on t7.学号 = t4.学号
where t6.学号 is null
and t7.姓名 != i)
group by 姓名)
where rownum = 1;
for j in (select 姓名,sum(分数) 总分
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
where 姓名 in (select t7.姓名
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
and t1.姓名 = i
inner join 分数信息 t3
on t3.学号 = t2.学号
and t2.科目 > t3.科目
inner join 分数信息 t4
on t4.科目 = t2.科目
inner join 分数信息 t5
on t5.科目 = t3.科目
and t5.学号 = t4.学号
left join 分数信息 t6
on t6.学号 =t5.学号
and t6.科目 != t2.科目
and t6.科目 != t3.科目
left join 学生信息 t7
on t7.学号 = t4.学号
where t6.学号 is null
and t7.姓名 != i)
group by 姓名) loop
dbms_output.put_line(j.姓名||' '||j.总分);
end loop;
elsif a=1 then select * into xm,zf
from
(select 姓名,sum(分数) 总分
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
where 姓名 in (select t5.姓名
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
and t1.姓名 = i
left join 分数信息 t3
on t3.科目 = t2.科目
left join 分数信息 t4
on t4.学号 = t3.学号
and t4.科目 != t3.科目
left join 学生信息 t5
on t5.学号 = t3.学号
where t4.学号 is null
and t5.姓名 != i)
group by 姓名)
where rownum = 1;
for j in (select 姓名,sum(分数) 总分
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
where 姓名 in (select t5.姓名
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
and t1.姓名 = i
left join 分数信息 t3
on t3.科目 = t2.科目
left join 分数信息 t4
on t4.学号 = t3.学号
and t4.科目 != t3.科目
left join 学生信息 t5
on t5.学号 = t3.学号
where t4.学号 is null
and t5.姓名 != i)
group by 姓名) loop
dbms_output.put_line(j.姓名||' '||j.总分);
end loop;
end if;
exception
when no_data_found then dbms_output.put_line('没有和'||i||'考试科目相同的学生');
end;
begin
p4('君乔');
end;
--5，输入学生姓名，如果该学生考过不止2门科目，输出他的平均分，否则输出不符合条件
create or replace procedure p5(i varchar2)
is
数量 int;
平均分 number;
begin
select count(*) into 数量
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
where 姓名 = i;
if 数量>2 then select trunc(avg(分数),1) into 平均分
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
where 姓名 = i;
dbms_output.put_line(i||'的平均分是'||平均分);
else dbms_output.put_line('不满足条件');
end if;
end;
begin
p5('木沐');
end;
/输入学生姓名，如果所有考的科目最低分不低于80分，返回该学生的最高分，展示姓名，科目，分数，
如果不满足条件 返回输入条件不符合/
create or replace procedure p6(i varchar2)
is
a int;–最低分
b int;–最高分
begin
select min(分数),max(分数) into a,b
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
where 姓名 = i;
if a >= 80 then for j in(select 姓名,科目,分数
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
where 姓名 = i
and 分数 = b) loop
dbms_output.put_line(j.姓名||j.科目||j.分数);
end loop;
else dbms_output.put_line(‘输入条件不符合’);
end if;
end;
/7，输入学生姓名，如果存在考过80分以下，则展示该学生的姓名，总分，如果不满足条件
返回输入条件不符合/
create or replace procedure p7(i varchar2)
is
a int;--最低分
b int;--总分
begin
select min(分数),sum(分数) into a,b
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
where 姓名 = i;
if a < 80 then dbms_output.put_line(i||b);
else dbms_output.put_line('输入条件不符合');
end if;
end;
begin
p7('木沐');
end;
/8，输入科目，如果该科目最低分不低于80分，则返回有多少人考过，如果不满足，
返回该科目不符合/
create or replace procedure p6(i varchar2)
is
a int;–最低分
b int;–计数
begin
select min(分数),count(*) into a,b
from 学生信息 t1
inner join 分数信息 t2
on t1.学号 = t2.学号
where 科目 = i;
if a >= 80 then dbms_output.put_line(i||b);
else dbms_output.put_line(‘该科目不符合’);
end if;
end;
9，输入姓名，如果该学生至少2门科目在90以上，并且考过语文的人，
如果是男生，让他的语文分数+5，如果是女生，语文分数+3
展示姓名，语文科目，新分数，性别，
判断因为什么不满足条件，如果是科目数量问题返回该学生科目不达标，如果是没考过语文返回没考过语文
–创建存储
CREATE OR REPLACE PROCEDURE pro_9 (p_name VARCHAR2)
IS
v_subject_count int; – 科目数量
v_high_score_count int; – 至少90分科目数量
v_gender varchar2(10); – 性别
v_chinese_score int; – 语文分数
v_new_chinese_score int; – 新的语文分数
v_student_id int; – 学生ID
BEGIN
– 获取学生ID和性别
SELECT student_id, gender INTO v_student_id, v_gender
FROM students
WHERE name = p_name;
– 判断学生是否考过语文
SELECT count(*) INTO v_subject_count – 科目数量
FROM scores
WHERE student_id = v_student_id
AND subject = ‘语文’;
IF v_subject_count = 0 THEN
dbms_output.put_line(‘没考过语文’); – 没考过语文
ELSE
SELECT score INTO v_chinese_score
FROM scores
WHERE student_id = v_student_id
AND subject = ‘语文’;
END IF;
– 获取至少90分的科目数量
SELECT count(*) INTO v_high_score_count
FROM scores
WHERE student_id = v_student_id
AND score >= 90;
– 判断是否至少有2门科目在90以上
IF v_high_score_count < 2 THEN
dbms_output.put_line(‘该学生科目不达标’);
END IF;
– 根据性别判断语文分数
IF v_gender = ‘男’ THEN
v_new_chinese_score := v_chinese_score + 5;
ELSE
v_new_chinese_score := v_chinese_score + 3;
END IF;
– 更新语文分数
UPDATE scores
SET score = v_new_chinese_score
WHERE student_id = v_student_id
AND subject = ‘语文’;
dbms_output.put_line(‘姓名:’|| p_name || ’ 科目: 语文 新分数: ’ || v_new_chinese_score || ’ 性别: ’ || v_gender);
END;
–调用存储
BEGIN
pro_9(‘陈婉茹’);
END;
/输入班级，返回每月，每科的及格，良，一般，优秀，不及格，缺考的人数占比 (总人数，包括没考的也算进去)/
–创建存储
–外层 确定当前月份
–中层 确定当前科目
–内层 处理结果
create or replace procedure p_1(i varchar2)
is
总人数 int;
月考次数 int;
良人数 int;
及格人数 int;
一般人数 int;
优秀人数 int;
不及格人数 int;
缺考人数 int;
begin
select count(distinct 姓名) into 总人数 --指定班级有多少个人
from yuekao
where 班级=i;
select count(distinct 时间) into 月考次数 --总共考几次试
from yuekao;
for a in 1…月考次数 loop --2
for b in(select distinct(科目)
from yuekao) loop --数学
良人数:=0;
及格人数:=0;
一般人数:=0;
优秀人数:=0;
不及格人数:=0;
缺考人数:=0;
/及格>=60，良>=70 一般>=80 优秀>=90/
for c in(select case when 分数>=90 then ‘优秀’
when 分数>=80 then ‘一般’
when 分数>=70 then ‘良’
when 分数>=60 then ‘及格’
else ‘不及格’ end 考核情况
from yuekao
where 科目=b.科目 --数学
and replace(substr(时间,-2),‘0’,‘’)=a --1
and 班级=i) loop
if c.考核情况 = ‘良’ then 良人数:=良人数+1;
elsif c.考核情况 = ‘及格’ then 及格人数:=及格人数+1;
elsif c.考核情况 = ‘不及格’ then 不及格人数:=不及格人数+1;
elsif c.考核情况 = ‘优秀’ then 优秀人数:=优秀人数+1;
elsif c.考核情况 = ‘一般’ then 一般人数:=一般人数+1;
end if;
end loop;
缺考人数:=总人数-良人数-及格人数-优秀人数-一般人数-不及格人数;
dbms_output.put_line(i||a||‘月’||b.科目||‘考核情况:’);
dbms_output.put_line(’ 及格人数占比:‘||round((及格人数/总人数100),2)||‘%’);
dbms_output.put_line(’ 不及格人数占比:'||round((不及格人数/总人数100),2)||’%‘);
dbms_output.put_line(’ 优秀人数占比:‘||round((优秀人数/总人数100),2)||‘%’);
dbms_output.put_line(’ 一般人数占比:'||round((一般人数/总人数100),2)||’%‘);
dbms_output.put_line(’ 良人数占比:‘||round((良人数/总人数100),2)||‘%’);
dbms_output.put_line(’ 缺考人数占比:'||round((缺考人数/总人数100),2)||’%‘);
dbms_output.put_line(’ ');
end loop;
end loop;
end;
begin
p_1(‘一班’);
end;
/7），输入班级，输入月份，返回每个科目的最高分，以及最低分的学生姓名以及分数，如果有相同分数，
一起展示/
create or replace procedure p_7(i varchar2,j int)
is
begin
for a in(
select *
from(
select *
from (select 姓名,分数,科目,dense_rank()over(partition by 科目 order by 分数 desc) 排名a
from yuekao
where 班级 = i
and replace(substr(时间,-2),‘0’,‘’)=j)
where 排名a=1) t1
inner join (
select *
from (select 姓名 姓名b,分数 分数b,科目 科目b,dense_rank()over(partition by 科目 order by 分数) 排名b
from yuekao
where 班级 = i
and replace(substr(时间,-2),‘0’,‘’)=j)
where 排名b=1) t2
on t1.科目 = t2.科目b) loop
dbms_output.put_line(i||j||‘月’||a.科目||‘最高分:’||a.分数||‘学生姓名:’||a.姓名||‘最低分:’||a.分数b||‘学生姓名:’||a.姓名b);
end loop;
end;
begin
p_7(‘一班’,4);
end;
/4.输入科目，返回该科目缺考的所有学生姓名，输出缺考的人都是谁以及他们哪个月缺考，
该科目没有人缺考输出：没有人缺考/
–1-6 数学 名字不在考过数学的人里 not in
create or replace procedure p_4(i varchar2)
is
月考次数 int;
判断有没有人缺考 int;
begin
判断有没有人缺考:=0;
select count(distinct 时间) into 月考次数
from yuekao;
for j in 1…月考次数 loop
for k in (select distinct 姓名
from yuekao
where 姓名 not in(select 姓名
from yuekao
where replace(substr(时间,-2),‘0’,‘’)=j
and 科目 = i)) loop
dbms_output.put_line(j||‘月’||k.姓名||‘缺考’||i);
判断有没有人缺考:=判断有没有人缺考+1;
end loop;
end loop;
if 判断有没有人缺考=0 then dbms_output.put_line(‘没有人缺考’);
end if;
end;
begin
p_4(‘数学’);
end;
–游标和
bulk collect into
fetch 游标 bulk collect into table类型，比bulk collect into效率要高，面对大数据量的时候效率提升很大，
但是必要和table类型一起使用
declare
–声明游标
cursor c_1 is select *
from hr.stu;
type mytable is table of hr.stu%rowtype;
v mytable;
begin
open c_1;
fetch c_1 bulk collect into v;
for i in v.first…v.last loop
dbms_output.put_line(v(i).stu_id||v(i).s_name);
end loop;
close c_1;
end;
–ref游标
也叫做参照游标，在打开的时候才动态执行sql查询，ref游标对应的结果集在运行的时候才确定并执行，显式可以
叫做静态游标，ref叫做动态游标
创建ref游标的步骤
比显式游标要麻烦一些
声明ref游标类型
1
type ref游标类型名 is ref cursor;
2
ref游标变量名 ref游标类型名;
打开ref游标的语法
open 游标变量名 for 一个select查询
–当用户输入employees时，输出表里所有员工的id和工资，如果输入其它表名输出’不是正确的表名’
declare
type ref_c is ref cursor; --声明ref游标类型
c_1 ref_c; --声明一个变量，它是ref游标
table_name varchar2(20); --让用户输入表名
emp_id int; --储存员工号
emp_salary int; --储存工资
begin
table_name:=‘&表名’; --输入表名
if table_name = ‘employees’ or table_name = ‘EMPLOYEES’ then --判断，如果用户输入的是employees那就执行什么操作
open c_1 for select employee_id,salary --打开c_1游标，结果集同时声明
from hr.employees;
–下面跟显式游标一致
fetch c_1 into emp_id,emp_salary;
while c_1%found loop
dbms_output.put_line(c_1%rowcount||’ ‘||‘员工号:’||emp_id||’ '||‘工资:’||emp_salary);
fetch c_1 into emp_id,emp_salary;
end loop;
close c_1;
else dbms_output.put_line(‘不是正确的表名’);
end if;
end;
/创建存储过程 输入部门号 并用ref游标涨薪
如果是60号部门涨百分之3 如果80号部门涨百分之5
如果是50号部门涨百分之10 其他涨百分之15
显示员工号 和涨薪后的工资，如果在游标中抓不到数据的话抛出异常，‘游标数据提取完成’
提示：如果输入的是60号部门，则打开对应60号部门的ref游标，并更新/
create table cs_20240611 as select * from hr.employees;
create table employ_611
as select * from hr.employees
select * from employ_611
drop table employ_611
create or replace procedure cch_21(a int) --输入部门号
is
zx number(10,2); --声明涨薪
type ref_bm is ref cursor; --声明ref游标类型
v_bm ref_bm; --声明一个变量，它是ref游标
emp_id int; --储存员工号
emp_salary int; --储存工资
xin_salary int; --储存新工资
b boolean; --声明一个布尔值
begin
if a = 60 then zx := 0.03;
elsif a = 80 then zx := 0.05;
elsif a = 50 then zx := 0.1;
else zx := 0.15;
end if;
open v_bm for select employee_id,salary
from employ_611
where department_id = a;
fetch v_bm into emp_id, emp_salary;
while v_bm%found loop
update employ_611
set salary = salary + salary * zx
where employee_id = emp_id
and salary = emp_salary
returning salary into xin_salary;
dbms_output.put_line(v_bm%rowcount||' '||'员工号:'||emp_id||' '||'工资:'||xin_salary);
fetch v_bm into emp_id, emp_salary;
b := v_bm%notfound; --判断游标里是否有数据
if b then dbms_output.put_line('游标数据提取完成');
end if;
end loop;
close v_bm;
end;
begin
cch_21(60);
end;
–触发器
特殊类型的存储过程，分为几种，有在表上的触发器，有在视图上的触发器，有在数据库上的触发器，有在模式
上的触发器，通过触发器可以实现更复杂的约束，一般用来做数据类型或者数据监控
模式是什么
在创建用户的时候，oracle会自动创建一个与这个用户同名的数据库模式，然后该用户下的所有数据库对象（
存储过程，表，序列，同义词，视图等等)都归属于这个模式
模式是某个用户下数据库对象的集合
什么是触发器
设定一个特定事件，如果这个事件出现了，就自动执行某一段代码，这就是触发器，主要的点就是当特定事件
出现的时候，它是自动执行的，我们没办法主动去触发触发器，触发器只能被特定事件触发，它的启动是由oracle自己
判断的，当某个事件触发，它就马上调用你写的代码(触发器里的内容)
特定事件都有什么
增删改dml语句，这三个动作，还有创建，删除，修改，ddl语句，都可以触发某个写好的触发器，但是select不行
select不能触发触发器
触发器的功能
1 自动生成数据
触发器是当某一个事件出现时自动执行，所以它可以被设计成自动生成一些事件
2 自定义复杂的约束
3 提供日志功能
如果某个事件触发了，就马上使用触发器这信息记录到日志表当中
4 复杂的业务逻辑
当向表里进行增删改的时候，先用触发器来判断一下
创建触发器语法
create trigger 触发器名
after|before|instead of
/常见的触发器就是建立在表上的触发器和建立在视图上的触发器，建立在视图上的触发器就是instead of触发器，
建立在表上的有after，before两种，before是前置触发器，after是后置触发器 ，在触发事件之前/在触发事件
之后，before先执行begin里的操作再执行触发了触发器的语句，after先执行触发了触发器的语句然后再执行触发器
里的内容/
insert|update|delete on 表名/视图名
/当什么事件发生了就触发触发器，如果写的是insert on 表名的话，当对某张表执行插入操作的时候，触发当前
触发器，也可以有多个事件加or就可以了, insert or delete or update on 表名/
for each row
/行级触发器还是表级触发器(语句级触发器)，写for each row就是行级触发器，不写就是表级触发器，行级触发器是指
触发了触发器的dml语句影响了多少行，触发器就执行多少次，表级是指不管触发了触发器的语句影响了多少行，
当前触发器都指执行一次/
when 条件
/* 增加触发触发器的条件，不写的话，只要有对应的dml动作触发器才会运行，如果写的话不仅要满足dml动作，
还要满足when里的条件，才会触发触发器，进一步增加条件，满足什么条件才触发触发器*/
plsql块;
[!注意事项]
1 在触发器的执行部分只能使用dml语句，不能使用ddl语句
2 触发器中不能存在commit或者rollback，触发器自动提交
3 作用在表上的触发器，如果该表被删除，触发器也自动删除
4 如果plsql块的begin部分报错了的话，触发了触发器的dml语句就执行不了
5 begin里不要写会循环触发当前 触发器的事件，比方说，update on 表a 然后在begin写 update 表a set
这样写会成死循环
–新建一个成绩表，当用户插入，更新表中的数据时，就输出‘触发器触发了’
create table cs20240611_1 (id int,name varchar2(20),s_or int);
insert into cs20240611_1 values (1,‘小明’,80);
insert into cs20240611_1 values (2,‘小黑’,66);
insert into cs20240611_1 values (3,‘小李’,57);
insert into cs20240611_1 values (4,‘小白’,99);
select *
from cs20240611_1
create or replace trigger t_1 --创建一个触发器名字叫t_!
after --对于这个需求来说，前置触发器 还是后置触发器无所谓，随便写一个
insert or update on cs20240611_1 --当对cs20240611_1表进行插入和更新操作的时候就会触发触发器
–for each row --不需要写，语句级就行,不写代表对这张表有insert或者update操作的时候只触发一次触发器
begin
–触发了触发器的话做什么
dbms_output.put_line(‘触发器t_1触发了’);
end;
delete from cs20240611_1
–当用户插入数据到表里的时候，id列不能为负数
insert into cs20240611_1 values (-8,‘小丽’,60);
create or replace trigger t_2
before --前置触发器
insert on cs20240611_1
for each row
begin
if :new.id<0 then raise_application_error(-20001,‘学号输入有误，请重新输入’);
–如果新值里的id列小于0，那么就直接弹窗报错
end if;
end;
–伪记录变量，新值，旧值
:old 旧值 :new 新值
新值就是进行dml操作的时候打算给的值，旧值就是表里原来的值
新值和旧值根据不同的dml操作，里面所储存的东西也是不一样的
–不同的dml语句下，新值旧值所储存的内容
语句 :old :new
insert 所有字段都是空值 准备插入的数据
update 更新以前该行的数据 更新后该行的数据
delete 删除前该行的数据 所有字段都是空值
–delete :new全都是空值
–insert :old全都是空值
–update 都有值
insert into cs20240611_1
select *
from stu
注意事项
1 insert 的后置触发器不能对新值进行修改，delete的后置触发器不能对旧值进行修改，违反的话会报错
–新建一张学生表，当用户插入记录到学生表时，如果插入的id是负数，那么就把id变成正数，再进行插入
create or replace trigger t_3
before
insert on cs20240611_1
for each row
begin
if :new.id <0 then :new.id:=abs(:new.id);
end if;
end;
insert into cs20240611_1 values (-15,‘小丽’,60);
–当删除表中的数据的时候，将删除的数据备份到另外一个表里，备份数据必须有备份的时间
create table cs20240611_2 as select * from hr.stu;
create table cs20240611_3 as select * from hr.stu where 1=2;
select *
from cs20240611_2
select *
from cs20240611_3
alter table cs20240611_3
add(时间 date);
create or replace trigger t_4
before
delete on cs20240611_2
for each row
begin
insert into cs20240611_3 values (:old.stu_id,:old.s_name,:old.s_birth,:old.s_sex,sysdate);
end ;
delete from cs20240611_2
where stu_id = 1;
/周六周日休息， 休息日不能对你的表进行dml操作，如果操作就报错， ‘周末休息，操作剁手/
create or replace trigger t_5
before
insert or update or delete on cs20240611_2
begin
if to_char(sysdate,'day') in ('星期六','星期日') then raise_application_error(-20001,'周末休息,操作剁手');
end if;
end;
–学生信息表
create table stuInfo
(stuNo varchar2(8) not null primary key,
stuName varchar2(10) not null,
stuSex varchar2(2) not null,
stuAge number(6) not null,
stuSeat number(6) not null,
strAddress varchar2(255) default(‘地址不详’));
–学生成绩表
create table stuMarks
(ExamNo varchar2(7) not null primary key,
stuNo varchar2(6) not null references stuInfo(stuNo),
writtenExam number(6) null,
LabExam number(6) null);
insert into stuInfo(stuNo, stuName, stuSex, stuAge,stuSeat,strAddress)
select ‘s25301’, ‘张秋丽’, ‘男’, 18,1, ‘北京海淀’ from dual union
select ‘s25303’, ‘李斯文’, ‘女’, 22,2, ‘河阳洛阳’ from dual union
select ‘s25302’, ‘李文才’, ‘男’, 85, 3,‘地址不详’ from dual union
select ‘s25304’, ‘欧阳俊雄’, ‘男’, 28, 4,‘新疆’ from dual union
select ‘s25318’, ‘梅超风’, ‘女’, 23, 5,‘地址不详’ from dual;
insert into stuMarks(ExamNo, stuNo, writtenExam, LabExam)
select ‘s271811’, ‘s25303’, 93, 59 from dual union
select ‘s271813’, ‘s25302’, 63, 91 from dual union
select ‘s271816’, ‘s25301’, 90, 83 from dual union
select ‘s271817’, ‘s25318’, 63, 53 from dual;
select *
from stuInfo
select *
from stuMarks
–要求李斯文不能删除
create or replace trigger t_6
before
delete on stuInfo
for each row
begin
if :old.stuname=‘李斯文’ then raise_application_error(-20003,‘李思文不能被删除’);
end if;
end;
delete from stuInfo
where stuname = ‘李斯文’
–张三不能增加
create or replace trigger t_7
before
insert on stuInfo
for each row
begin
if :new.stuname=‘张三’ then raise_application_error(-20004,‘张三不能被增加’);
end if;
end;
insert into stuInfo values (‘s25308’, ‘张三’, ‘男’, 18,1, ‘北京海淀’)
–李斯文不能修改
create or replace trigger t_8
before
update on stuInfo
for each row
begin
if :old.stuname=‘李斯文’ then raise_application_error(-20005,‘李思文不能被修改’);
end if;
end;
update stuInfo
set stuname = ‘李’
where stuname = ‘李斯文’;
三个谓词
inserting, deleting, updating
用于判断在dml操作中触发的具体时间
如果事件是insert，则inserting的值为true，否则为false
当进行insert操作的时候，那么inserting是true，其它的deleting, updating里面都是false
把三个触发器合成一个
create or replace trigger td
before
insert or delete or update on stuInfo
for each row
begin
if inserting then --当正在执行的dml操作是增加的时候
if :new.stuname=‘张三’ then raise_application_error(-20004,‘张三不能被增加’);
end if;
elsif deleting then --当正在执行的dml操作是删除的时候
if :old.stuname=‘李斯文’ then raise_application_error(-20003,‘李斯文不能被删除’);
end if;
elsif updating then --当正在执行的dml操作是修改的时候
if :old.stuname=‘李斯文’ then raise_application_error(-20005,‘李斯文不能被修改’);
end if;
end if;
end;
–删除李斯文的同时她的成绩也自动删除
create or replace trigger t_a
before
delete on stuInfo
for each row
declare
sno varchar2(6);
begin
if :old.stuname = '李斯文' then
sno:=:old.stuno;
delete from stuMarks
where stuno = sno;
end if;
end;
delete from stuInfo
where stuname = ‘李斯文’
触发器的分类
1 模式触发器(也叫ddl触发器)
2 数据库触发器
3 dml触发器：行级触发器，语句触发器，instead of 触发器
平时用的最多的就是dml触发器，dml触发器就是由增删改触发的触发器
–instead of 触发器
也叫做替代触发器，当用户不能直接使用dml语句修改视图的时候，可以使用instead of触发器
来达到修改视图里面数据的目的，一个视图如果进行关联的就有可能不能修改，要看键值保留表和非键值保留表
如果是多表关联的话，只能对键值保留表进行更新，不能对非键值保留表进行更新操作，替代触发器和dml触发器
是不同的，在定义好替代触发器之后，用户对视图的dml操作将不再被执行，而是执行替代触发器里的内容
作用
1 解决复杂视图的更新问题
2 具体含义：执行一个替代触发器来代替触发事件的操作，oracle只运行替代触发器里的内容
3 替代触发器只能基于视图建立，并且主要用在不可修改的视图上
4 单表视图没有建立替代触发器的必要
5 所有的替代触发器都是建立在视图上的，所有的替代触发器都是行级触发器
不可更新的视图
1 使用了集合操作运算符，set运算符 (union,union all,minus,intersect)
2 使用了分组函数(max,min,avg,sum,count)
3 使用group by子句,start with 根节点子句,connect by
4 使用 distinct 关键字
5 使用了关联
对上述情况创建的视图，不能对其直接执行dml，但是可以在该视图上创建 instead of 触发器来间接的执行dml
create table ba
(序号 int,
姓名 varchar2(20));
create table bb
(序号 int,
地址 varchar2(20));
insert into ba values (1,‘小明’);
insert into ba values (2,‘小黑’);
insert into ba values (3,‘小李’);
insert into ba values (4,‘小白’);
insert into bb values (1,‘深圳’);
insert into bb values (2,‘广州’);
insert into bb values (3,‘上海’);
insert into bb values (4,‘北京’);
select *
from bb
–对两张表的关联状态做一个视图
create or replace view v_1
as
select t1.序号 序号1,t1.姓名,t2.序号 序号2,t2.地址
from ba t1
inner join bb t2
on t1.序号 = t2.序号
select *
from v_1
–把一号学生的地址变成昆明
update v_1
set 地址=‘昆明’
where 序号1 = 1
–查看视图里的列能不能更新删除插入，查看键值保留表的状态
select *
from dba_updatable_columns
where owner = ‘SYSTEM’
and table_name = ‘V_1’
create or replace trigger tr_1
instead of --触发器是替代触发器
update on v_1 --当有更新操作更新v_1视图的时候触发此触发器
for each row --instead of必须是行级触发器
begin
update bb
set 地址=:new.地址
where 序号=:old.序号1;
end;
update v_1
set 地址 = ‘重庆’
where 序号1 = 3;
drop trigger tr_1
–向v_1里插入一条数据
insert into v_1 values (5,‘小张’,5,‘长春’);
create or replace trigger tr_1
instead of --触发器是替代触发器
insert on v_1 --当有插入v_1视图的时候触发此触发器
for each row --instead of必须是行级触发器
begin
insert into ba values (:new.序号1,:new.姓名);
insert into bb values (:new.序号2,:new.地址);
end;
–视图的本质就是一条查询语句，所以视图是动态的
select employee_id，last_name,salary,t1.department_id,department_name
from hr.employees t1
inner join hr.departments t2
on t1.department_id = t2.department_id
/新建一个员工表和部门表，将两张表关联之后的状态做一个视图，视图里有employee_id，
last_name,salary,department_id,department_name分别写出对视图删除，插入，更新的触发器
插入触发器规定：插入部门时，必须同时插入部门名和部门号，不能只插其中一列数据。
如果准备插入的部门已经存在则必须插入原部门号和部门名/
–delete
t1.department_id = t2.department_id
–insert into
–update
create table emp as select * from hr.employees;
create table dep as select * from hr.departments;
–检查非空约束
select *
from sys.all_constraints
where table_name = ‘EMP’
select *
from sys.all_constraints
where table_name = ‘DEP’
–删除所有约束
alter table emp
drop constraint PK_EMP
–对两张表的关联状态做视图
create view emp_dep
as
select employee_id，
last_name,salary,t1.department_id,department_name
from emp t1
inner join dep t2
on t1.department_id = t2.department_id
select *
from emp_dep
update emp_dep
set department_name = ‘aaa’
where employee_id = 1000
create or replace trigger emp_update
instead of
update on emp_dep
for each row
declare
a int;
begin
select count(*) into a
from dep
where department_id = :new.department_id; --判断更新的部门号表里有没有
delete from emp
where employee_id = :old.employee_id; --删掉即将更新的员工信息
insert into emp(employee_id,last_name,salary,department_id) values
(:new.employee_id,:new.last_name,:new.salary,:new.department_id);
--重新插入一次这条数据，全部插新值
--动没动部门号，如果没动的话，那就把dep表里的对应的数据全部换成新的，如果没动过部门名
--那就没影响，更新前和更新后都是一样的
if :new.department_id = :old.department_id then update dep
set department_id = :new.department_id,
department_name = :new.department_name
where department_id = :old.department_id;
--部门号如果动了，会有不同的情况，如果a=0，那就说明部门表里没有你要更新的部门号，是一个新部门号，
--所以向里插入一条新部门数据，如果a!=0就说明部门表里已经有你要更新的部门号了，所以直接更新员工表
--对应的人的部门号让视图自己连接就行了
else if a=0 then insert into dep(department_id,department_name)
values (:new.department_id,:new.department_name);
elsif a!=0 then update emp
set department_id = :new.department_id
where department_id = :old.department_id;
end if;
end if;
end;
–模式触发器
又叫做系统触发器，是一种特殊类型的触发器，主要用来记录数据库操作的信息，比方说谁删了一张表
那就记录下来，几点删的，删的哪张表，或者数据库登录信息，几点谁登录了，几点谁断开了，对这些
系统级别的信息进行记录
–系统级触发器的语法
create trigger 触发器名字
before|after on
一个或多个ddl事件，多个事件用or隔开
schema|database
–系统级别触发器既可以建立在一个模式上，又可以建立在整个数据库上，当建立在模式(schema)上的时候
–只有当前用户模式的ddl操作和他们所导致的错误才能激活触发器，当建立在database数据库级别上的时候
–该数据库所有用户的ddl操作和他们所导致的错误都会激活触发器
administer database trigger 权限
plsql块;
–系统触发器的种类和事件出现的先后
事件 允许的时机 说明
startup after 启动数据库之后触发
shutdown before 关闭数据库之前触发
servererror after 数据库服务器发生错误之后触发
logon after 成功登录连接到数据库后触发
logoff before 开始断开数据库连接之前触发
create before,after 在执行create语句创建数据库对象之前或之后触发
drop before,after 在执行drop语句删除数据库对象之前或之后触发
alter before,after 在执行alter语句删除数据库对象之前或之后触发
ddl before,after 在执行大多数ddl语句之前或之后触发
grant before,after 在执行赋权语句之前或之后触发
revoke before,after 在执行收回权限语句之前或之后触发
rename before,after 在执行rename 语句之前或之后触发
事件属性函数
写系统级别触发器涉及到系统预定义的包，包里面有一些事件属性函数，事件属性函数会返回一些有用的信息
ora_client_ip_address 取得客户端ip地址，如果是本地连接返回null
ora_database_name 取得数据库名称
ora_login_user 登录或注销的用户名称
ora_dict_obj_name 取得ddl操作所对应的数据库对象名
ora_dict_obj_type 取得ddl操作所对应的对象类型
ora_dict_obj_owner 取得对象的所有者名称
ora_sysevent 激活触发器的事件名称
ora_is_alter_column(‘列名’) 检查特定列是否被修改
ora_is_drop_column(‘列名’) 检查特定列是否被删除
create table ba(序号 int,
姓名 varchar2(20));
create table bb(序号 int,
地址 varchar2(20));
insert into ba values (1,‘小明’);
insert into ba values (2,‘小黑’);
insert into ba values (3,‘小李’);
insert into ba values (4,‘小白’);
insert into bb values (1,‘深圳’);
insert into bb values (2,‘广州’);
insert into bb values (3,‘上海’);
insert into bb values (4,‘北京’);
create or replace trigger cs_1
before --前置触发器
alter on schema --范围为当前用户
begin
if ora_is_alter_column(‘姓名’) then dbms_output.put_line(‘修改了姓名列’);
end if;
end;
alter table ba
modify (姓名 varchar2(500))
/创建用于记录数据库操作的日志表，记录内容：执行的操作，对象的名称，对象的类型，对象所有者，执行时间/
create table rz (执行的操作 varchar2(20),
对象的名称 varchar2(20),
对象的类型 varchar2(20),
对象所有者 varchar2(20),
执行时间 date);
create or replace trigger cs_1
before --前置触发器
ddl on schema --范围为当前用户
begin
insert into rz values (ora_sysevent,ora_dict_obj_name,ora_dict_obj_type,ora_dict_obj_owner,sysdate);
end;
create table cs_20230612(id int,name varchar2(20));
drop table cs_20230612
select *
from rz
–删除触发器
drop trigger 触发器名;
–删掉其它用户模式中的触发器，需要有drop any trigger 系统权限，当删除建立在数据库上的触发器的时候
–用户需要有administer database trigger系统权限
–禁用或启用触发器
触发器的状态
有效状态:enable
无效状态:disable
触发器的两种状态可以互换
语法
alter trigger 触发器名 disable/enable
alter trigger 表名 disable/enable all triggers;
第一种写法只能生效或禁用一个触发器，第二种可以一次改变与指定表相关的所有触发器的状态
–关于触发器的数据字典
查看当前用户拥有的触发器信息
select *
from user_triggers
查看当前用户能够访问的触发器信息
select *
from all_triggers
查看oracle的所有触发器的信息
select *
from dba_triggers
备注
在开发的时候会涉及到给表加备注，或者给列加备注，来告诉自己和别人，某张表是干嘛的，某个字段是干嘛的
添加表备注
comment
comment on table 表名 is ‘含义’;
添加列备注
comment on column 表名.列名 is ‘含义’;
comment on table cs_20230612 is ‘测试用的’;
comment on column cs_20230612.id is ‘没什么用不用管’;
查看表注释的数据字典
select *
from user_tab_comments
where table_name = ‘大写的表名’;
select *
from user_tab_comments
where table_name = ‘CS_20230612’;
查看列注释的数据字典
select *
from user_col_comments
where table_name = ‘CS_20230612’;
–程序包
1 oracle内置程序包
2 创建程序包
3 在程序包中使用游标
4 常用的程序包使用
–程序包是什么
程序包是一个程序的集合，一个整体，对一些相关的存储过程，函数，变量，游标，异常等等进行封装
–程序包由什么组成
程序包由包规范和包主体两部分组成，一般叫做包头和包体
包头：公共类型对象，包括类型，变量，常量，异常，游标，子程序的一些声明
包体：私有类型对象，包括类型，变量，常量，异常，游标，子程序等等的一些声明，以及包头内的过程和
函数的实现
[!-创建程序包]
创建包头语法
create package 包头名
is
声明部分;
procedure 过程名(参数和参数类型); --过程的名字和参数信息
function 函数名(参数和参数类型) return 数据类型; --函数的名字和参数信息以及返回值的数据类型
…
end 包头名;–创建包体语法
create package body 包体名
is
声明部分;
procedure 过程名(参数和参数类型) --过程的具体内容
is
plsql块;
end 过程名;
function 函数名(参数和参数类型) --函数的具体内容
return 数据类型
is
plsql块;
end 函数名;
end 包体名;
注意事项
1 包头和包体的名字要完全相同
2 包头要先于包体建立
3 包头和包体中只有建立包的关键词，没有建立存储过程函数的关键词,没有create 语句
4 包中的过程和一般的过程可以重名
创建并使用程序包
要求：写一个包，使用包可以直接向学生表插入数据以及更新数据
--创建包头
create or replace package pack_1
is
公共 int :=1;
procedure insert_stu(a in hr.stu%rowtype);
procedure update_stu(b in hr.stu%rowtype);
end pack_1;
--创建包体
create or replace package body pack_1
is
私有 int :=5;
procedure insert_stu(a in hr.stu%rowtype)
is
begin
insert into stu_2 values (a.stu_id,a.s_name,a.s_birth,a.s_sex);
commit;
dbms_output.put_line(私有);
end insert_stu;
procedure update_stu(b in hr.stu%rowtype)
is
begin
update stu_2
set s_name = b.s_name
where stu_id = b.stu_id;
commit;
end update_stu;
end pack_1;
–调用包里的存储过程1
declare
c hr.stu%rowtype;
begin
c.stu_id:= 50;
c.s_name:= ‘老师’;
c.s_birth:= sysdate;
c.s_sex:= ‘男’;
pack_1.insert_stu©;
end;
select *
from stu_2
–调用包里的存储过程2
declare
c hr.stu%rowtype;
begin
c.stu_id:=2;
c.s_name:=‘校长’;
pack_1.update_stu©;
end;
begin
dbms_output.put_line(pack_1.私有);
end;
–创建一个包，调用包根据当前时间如果是上午则输出早上好，如果是下午则输出晚上好
create or replace package pack_2
is
procedure p_1;
end pack_2;
create or replace package body pack_2
is
procedure p_1
is
begin
if trunc(sysdate,‘dd’) = round(sysdate,‘dd’) then dbms_output.put_line(‘早上好’);
else dbms_output.put_line(‘晚上好’);
end if;
end p_1;
end pack_2;
–在程序包中使用游标
游标使用包含两部分，声明部分，使用部分(打开游标,提取数据,关闭游标)，在包中声明游标需要有return子句指定
游标的返回类型，游标结果集的类型是什么，但是return返回的类型是有规定的
包头声明游标的时候return只能返回两种数据类型
1 %rowtype
2 record
不可以是int,number,varchar2,%type等类型
–显式游标和ref游标
–显式游标
create or replace package pack_3
is
cursor c_1 return hr.employees%rowtype;
procedure pack_3_p;
end pack_3;
create or replace package body pack_3
is
cursor c_1 return hr.employees%rowtype is select *
from hr.employees;
procedure pack_3_p
is
v_emp hr.employees%rowtype;
begin
open c_1;
fetch c_1 into v_emp;
while c_1%found loop
dbms_output.put_line(v_emp.employee_id||’ '||v_emp.salary);
fetch c_1 into v_emp;
end loop;
close c_1;
end pack_3_p;
end pack_3;
begin
pack_3.pack_3_p;
end;
ref游标
–包头
create or replace package pack_4
is
type ref_type is ref cursor; --声明一个ref游标类型，类型名叫ref_type
procedure pack_4_p;
end pack_4;
–包体
create or replace package body pack_4
is
procedure pack_4_p
is
v_ref ref_type;
v_emp hr.employees%rowtype;
begin
open v_ref for select * from hr.employees;
fetch v_ref into v_emp;
while v_ref%found loop
dbms_output.put_line(v_emp.employee_id||’ '||v_emp.salary);
fetch v_ref into v_emp;
end loop;
close v_ref;
end pack_4_p;
end pack_4;
begin
pack_4.pack_4_p;
end;
–怎么查询当前用户定义的包和包体
select *
from user_objects
where object_type = ‘PACKAGE’
or object_type = ‘PACKAGE BODY’
–当前用户定义的子程序或程序包的源代码
select *
from user_source;
–内置程序包
内置程序包用来扩展数据库功能，所有的内置程序包需要都由sys用户保管，所有的内置程序包都在sys模式下，
并且内置程序包所有用户都可以访问，最常用的就是dbms_output包
–dbms_job包实现定时任务
可以制订任务，定时的来执行任务，结束任务
创建定时任务
dbms_job.submit(填任意一个数值类型的变量用来让系统自动生成一个任务编号,‘要执行的过程或语句;’,开始时间,‘间隔时间’);
--创建一个定时任务，每隔一分钟向表里插入数据
create table ds_1 (id int); ---建表
--创建一个存储过程，过程被执行的时候就会向ds_1里插入一个1
create or replace procedure insert_ds_1
is
begin
insert into ds_1 values (1);
commit;
end ;
--创建定时任务
declare
a int;
begin
dbms_job.submit(a,'insert_ds_1;',sysdate,'sysdate+1/1440');
commit;
end;
select *
from ds_1
容器出错的地方
1 第二个参数的最后必须有分号
2 第二个参数和第四个参数需要加单引号
注意事项
1 凡是调用dbms_job包里过程的时候都要有commit，不然会不生效
2 如果想使用dbms_job包必须保证初始化相关参数job_queue_processes 不能为0，这代表了oracle能够
并发执行的job任务数量，当它为0的时候，表示oracle的定时任务全部停止
查看job_queue_processes参数
select *
from v$parameter
where name = ‘job_queue_processes’
–这里是小写的
修改job_queue_processes参数
alter system
set job_queue_processes=并发数;
alter system
set job_queue_processes=1000;
–查看当前用户定义的定时任务的数据字典
select *
from user_jobs;
job 唯一任务号
last_date 最后一次成功执行任务的时间
next_date 下一次执行任务的时间
删除定时任务
dbms_job.remove(job号);
begin
dbms_job.remove(44);
end;
–修改一个任务里调用的过程或语句
dbms_job.what(job号,‘过程或语句;’);
–修改下次执行时间
dbms_job.next_date(job号,‘间隔时间’);
–启动任务或停止任务
dbms_job.broken(job号,true/false);
true是任务中止，false任务运行
查数据字典，里面的broken字段是y表示停止状态，为n表示运行状态
begin
dbms_job.broken(45,false);
commit;
end;
–立即执行一次指定任务
dbms_job.run(job号)
begin
dbms_job.run(45);
commit;
end;
表空间
[!表空间概念]
表空间，所有表，数据库对象，都要存放在表空间里，通俗来讲，数据库就是房子的外壳，表空间就是各个房间数据库文件就是房间中的箱子，表就是箱子里的东西，表空间储存的大部分都是表，所以叫表空间，用户指定表空间就是希望把这个用户的表放在哪个表空间里，表和数据库对象不能单独存在，一个隶属于某个用户，而某一个用户的所有数据必定存放在一个表空间下，oracle数据库必须有一个表空间，就是system的表空间，一个数据可以有多个表空间，但是一个表空间只能属于一个数据库
数据库的存储结构
数据库由多个表空间组成，一个表空间由多个段组成，一个段由多个区组成，一个区由多个数据块组成
表空间：给用户分配的空间用来存放表或数据库对象
段：可以理解为表
区：可以理解为表分区
块: 由多行数据组成，块是数据库的最小单元，默认是8k
[!表空间和段，区，块的关系]
一个表空间下有多张表，每张表都会有多个分区，每个分区里面有多个数据块，一个块里由多行数据组成
–查询员工号是100的人的信息
hr.employees;
表空间的分类
永久表空间：
一般保存表，视图，存储过程和索引等数据，储存数据库对象的，每个用户给个空间，给每个人发个柜子
临时表空间：
只用于存放系统中短期活动的数据，比方说排序运算，创建索引，order by,group by ,distinct ，关联访问视图以及一些临时操作进行运算的，当你运算完毕或者关闭数据库之后，数据就会从临时表空间迁回到永久表空间，同时系统会清理临时表空间，临时表空间里不能存放永久性的数据，oracle默认用户使用的临时表空间是temp
undo表空间：
系统自带的表空间，当执行dml操作的时候，oracle会自动将这些操作的旧数据存放在undo表空间里，帮助用户回退未提交的事务，或者找回被你删除的数据，提供闪回功能，理解为电脑的回收站
[!注意]
如果工作中出现表空间内存不足的报错，检查自己代码有没有问题，一般情况下给你分配的空间只会多不会少
每张表都会属于某个用户，每个用户都会有自己的表空间，如果创建用户的时候没有指定他使用哪个表空间，他的表空间会默认为系统的表空间
创建永久表空间
[!创建永久表空间语法]
create tablespace 表空间名
logging 创建表空间的时候建立日志
datafile 新建一个表空间文件的路径’C:\app\a.dbf’ --(路径里必须都是中文)
size 表空间设定为多大
autoextend on next 空间不够的时候自动加多少 maxsize 最大加到多大
extent management local 本地管理
也可以关闭自动扩展，autoextend off，不写后面的了，只写一个autoextend off，相当于给多大就是多大，一般情况下表空间都是自动扩展的
–maxsize unlimited 最大扩展到无限大
–reuse选项，表空间已经删除，但是数据文件还在，再创建表空间的时候指定这个文件就会报错，提示文件已经
–存在。此时可以使用reuse选项重新使用这个文件，如果建表空间的时候指定的文件不存在，那么就没影响
–nologging 创建表空间的时候不建立日志，唯一的好处是创建表空间很快，但是数据丢失之后找补回来了，一般
–情况下不会关
关于表空间的数据字典
select *
from sys.all_tables --数据库里全部的表
–tablespace_name表示 该表在哪个表空间下
数据库里的所有表空间
select *
from sys.v_$tablespace (由数据文件组成，一个表空间可以有多个数据文件)
数据库里所有表空间的详细信息
select *
from dba_tablespaces;
–contents显示的是表空间是什么类型，TEMPORARY临时表空间，PERMANENT永久表空间，UNDO表空间
–所有表空间的数据文件的路径
select *
from dba_data_files
–所有临时表空间的数据文件路径
select *
from dba_temp_files
–所有临时表空间文件的详细信息
select *
from v$tempfile
–用户的信息，里面有用户使用的是什么表空间
select *
from dba_users;
–查表在哪个表空间里
select *
from dba_tables
where table_name = ‘大写的表名’
select *
from dba_tables
where table_name = ‘EMPLOYEES’
–查看系统默认的临时表空间
select *
from database_properties
where property_name = ‘DEFAULT_TEMP_TABLESPACE’;
–查看当前用户的用户状态，里面有用户目前默认使用的永久，临时表空间都是什么
select *
from user_users
–查询表空间名, 和表空间大小:
SELECT a.tablespace_name "表空间名",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
/*创建一个永久表空间，初始大小，500m，空间不够自动加10m，最大加到1g*/
create tablespace cs_yjbkj
logging
datafile ‘B:\cs_1.dbf’ --(路径里必须都是中文)
size 500m
autoextend on next 10m maxsize 1g
extent management local
创建临时表空间
[!创建临时表空间]
create temporary tablespace 表空间名
tempfile ‘D:\AA\A.dbf’
size 表空间设置多大
autoextend on next 空间不够自动增加多少 maxsize 最大能加到多少
extent management local;
create temporary tablespace cs_lsbkj
tempfile ‘B:\cs_2.dbf’
size 1g
autoextend on next 10m maxsize 2g
extent management local;
–建表的时候指定表去哪个表空间
create table cs20240613_1 tablespace aa as select * from hr.employees;
select *
from dba_tables
where table_name = ‘CS20240613_1’
–建分区表的同时指定分区去哪个表空间
–表的分区也可以被放在不同的分区里
create table cs20240613_2
(id int,
name varchar2(20))
partition by hash(id)
(partition fenqu1 tablespace aa,partition fenqu2 tablespace system);
drop table cs20240613_2
–建索引的时候指定索引的表空间
create index 索引名
on 表名(作用在哪列上)
tablespace 表空间名
–修改永久表空间的大小
alter database datafile ‘B:\a.dbf’ resize 5g;
–修改临时表空间的大小
alter database tempfile ‘B:\b.dbf’ resize 3g;
–删除表空间和表空间文件
drop tablespace 表空间名 including contents and datafiles --删除非空表空间，包含物理文件
drop tablespace aa including contents and datafiles
–如果其它表空间中的表有外键等约束关联到了本表空间中表的字段，或者有和其它表空间有关，就要加上
–cascade constraints
drop tablespace aa including contents and datafiles cascade constraints;
–删除非空表空间但是不包含物理文件
drop tablespace bb including contents
–删除空表空间，不包含数据文件
drop tablespace 表空间名
权限
权限就是对用户而言的，如果只给一个用户登录权限，那他就没法建表，删表，查表
权限分为两大类，系统权限和对象权限，系统权限里有连接数据库，创建表空间，创建用户，建表，建视图
基本上和建立或者删除数据库对象相关的权限都是系统权限，关乎到系统安全的，对于数据库的一些权限，
系统权限都是针对用户设置的，用户必须被授予相应的权限，才能连接到数据库进行相应的操作
对象权限指用户对数据库对象的操作权限，比方说查一张表，update更新一张表,delete删除里面的数据，alter
加列修改，类似这些对数据库对象进行操作的权限叫对象权限，系统权限几百个，对象权限也有100-200个
–关于权限的一些数据字典
查看有多少个系统权限(全部)
select *
from system_privilege_map
–查询某个用户/角色对象权限的数据字典
select *
from dba_tab_privs
where grantee=‘大写的用户名/角色名’
select *
from dba_tab_privs
where grantee=‘HR’
–查询某个用户/角色系统权限的数据字典
select *
from dba_sys_privs
where grantee = ‘大写的用户名/角色名’
–查询一个用户下的角色
select *
from dba_role_privs
where grantee = ‘大写的用户名/角色名’
–创建用户
–正常只有的dba权限的用户才能创建用户
create user a identified by 123456;
–建用户的时候把表空间分配好
–建用户的时候如果不指定表空间就会使用系统的表空间，建用户的时候要分配表空间
create user 用户名 identified by 用户密码 default tablespace 永久表空间 temporary tablespace 临时表空间
create user a identified by 123456 default tablespace cs_yjbkj temporary tablespace cs_lsbkj
–一个用户必须有永久表空间和临时表空间，如果指定其中一个了，另一个没指定，另一个自动使用系统的表空间
create user b identified by 123456 default tablespace cs_yjbkj
–删除用户
drop user 用户名 --如果用户里没有任何东西就会被删除
drop user 用户名 cascade --强制删除用户和其创建的所有东西
–dcl 数据控制语言 包括grant,revoke
授权:grant
–建用户的时候必须给需要的权限，不然登录都登录不了
–赋予系统权限
grant 权限,权限,…
to 用户名;
–常用的系统权限
create session 创建会话的权限，连接到数据库的权限
create table 创建表
create any table 在任何模式下创建表
create view 创建视图
create any view 在任何模式下创建视图
create materialized view 创建物化视图的权限
create sequence 创建序列
create synonym 创建同义词
drop any table 删除任何模式下的表
create procedure 创建存储过程
execute any procedure 执行任何模式下的存储过程
create user 创建用户
drop user 删除用户
–给一个用户所有的系统权限
grant all privileges
to 用户名;
grant create table,create session
to cc;
–解锁用户使用表空间的权限，让用户可以使用哪个表空间，不解锁是不能用表空间的
–需要指定好永久表空间，临时表空间不需要指定
–用户除了有create table权限之外，还需要分配相应的表空间使用权限才能开辟存储空间正常使用
–解锁用户使用表空间的权限
alter user 用户名 quota unlimited
on 表空间名
alter user dd quota unlimited
on cs_yjbkj
–解锁用户使用表空间的权限，并分配使用大小
alter user 用户名 quota 给表空间限制多大几m
on 表空间
alter user dd quota 1000m
on cs_yjbkj
–更改用户使用的表空间
–更改使用的永久表空间
alter user 用户名 default
tablespace 永久表空间名
alter user a default
tablespace system
–更改使用的临时表空间
alter user 用户名 temporary
tablespace 临时表空间名
alter user a temporary
tablespace cs_lsbkj
–修改数据库默认用户使用的表空间
alter database default [temporary] tablespace 表空间名
–使用temporary关键字表示设置临时表空间，不使用设置永久表空间
–对象权限
分配某个对象的权限给哪个用户
grant 对象权限名,对象权限名…
on 对象名
to 用户
–不同的对象具有不同的对象权限
修改: 表，序列
删除: 表，视图
执行: 存储过程
索引: 表
插入: 表，视图
关联: 表，视图
查询: 表，视图，序列
更新: 表，视图
–对象的拥有者拥有所有权限
–对象的拥有者可以向外分配权限
–分配表的权限给哪个用户
grant 对象权限,对象权限…
on 表名
to 哪个用户
–新建两个用户
create user aa identified by 123456 default tablespace cs_yjbkj temporary tablespace cs_lsbkj
create user bb identified by 123456 default tablespace cs_yjbkj temporary tablespace cs_lsbkj
create table cs1 (id int);
insert into cs1 values (1);
select *
from aa.cs1
grant select
on cs1
to bb;
–给一个用户对于某张表的所有对象权限，11种
grant all
on 表名
to 用户名
–冻结一个用户(死锁)
alter user 用户名 account lock;
–关于死锁的数据字典
select *
from sys.v_$lock
–解锁一个用户
alter user 用户名 account unlock;
–重置密码
alter user 用户名 identified by 新密码;
–使用户同样具有分配对象权限的权力(只能分配对象权限，系统权限不行)
grant 权限名
on 表名
to 哪个用户
with grant option
grant select
on aa.cs1
to cc
with grant option
create user dd identified by 123456 default tablespace cs_yjbkj temporary tablespace cs_lsbkj
select *
from aa.cs1
–使用户同样具有分配系统权限的权力(只能分配系统权限，不能分配对象权限)
grant create table
to 用户名
with admin option
grant create table,create session
to dd
–可以使用 with admin option 对角色进行再赋权
–不可以使用 with grant option 对角色进行再赋权
–向数据库中的所有用户分配对象权限
grant 权限名
on 表名
to public
–再赋权可以传递
a-b-c-d
–收回权限
revoke 收回权限
–收回系统权限
revoke 权限
from 用户
revoke create table
from bb
–收回对象权限
revoke 权限
on 表名
from 用户名
select *
from hr.departments
grant select
on hr.departments
to ee
create user ee identified by 123456 default tablespace cs_yjbkj temporary tablespace cs_lsbkj
revoke select
on hr.departments
from ee
–A给b权限，b再给c的话
–系统权限
如果收回b的系统权限c的系统权限还在
a也可以直接收回c的系统权限
–对象权限
如果收回b的对象权限那么c的对象权限也没了
a不能直接收回c的对象权限，收回对象权限，只能收回当前用户直接给过的权限
create user b identified by 123456 default tablespace cs_yjbkj temporary tablespace cs_lsbkj;
create user c identified by 123456 default tablespace cs_yjbkj temporary tablespace cs_lsbkj;
alter user b quota unlimited
on cs_yjbkj;
alter user c quota unlimited
on cs_yjbkj;
grant create session
to b
with admin option
revoke create session
from c
create table cs20240613 (id int);
insert into cs20240613 values (1);
select *
from system.cs20240613
grant select
on system.cs20240613
to c
with grant option
revoke select
on system.cs20240613
from c
角色
相当于对一些系统权限或对象权限进行打包，然后定义成一个角色，直接赋予一个用户一个角色，该用户
就有了该角色的是所有权限
老板
管理人员 create session,create table,select hr.employees…
新员工 create session …
–oracle系统预定义的角色
–不常用
1 connect 只有连接数据库的权限
2 resource 授权一般开发人员的权限
3 dba 拥有全部的权限，系统最高权限
4 public 公共（特殊角色） 如果授予一个权限给public的话，那么数据库下的所有用户都有了这个权限
授予给用户角色之后，并不是将权限直接给用户，相当于将该角色的所有权限共享给了用户，所以直接查用户
的数据字典是查不到的，需要查这个用户下有没有角色，如果有再通过查角色权限的方式，得知该用户有什么
权限
创建角色
create role 角色名
–为角色赋予系统权限
grant 权限名，权限名…
to 角色名
–为角色赋予对象权限
grant 权限名，权限名…
on 表名
to 角色名
–将角色赋予给用户
grant 角色名 to 用户名,用户名…
–删除角色
drop role 角色名
–将角色赋予给角色
grant 角色名 to 角色名
–收回角色
revoke 角色名 from 用户名
–切换用户
conn 用户名;
–显示当前用户
show user;
–查询某个用户/角色对象权限的数据字典
select *
from dba_tab_privs
where grantee=‘大写的用户名/角色名’
–查询某个用户/角色系统权限的数据字典
select *
from dba_sys_privs
where grantee = ‘大写的用户名/角色名’
–查询一个用户下的角色
select *
from dba_role_privs
where grantee = ‘大写的用户名/角色名’
create role cs;
grant create session
to cs
grant select
on cs20240613
to cs
select *
from dba_sys_privs
where grantee = ‘CS’;
select *
from dba_tab_privs
where grantee= ‘CS’;
grant cs to b
select *
from dba_role_privs
where grantee = ‘B’
–其它数据库对象
–序列，同义词，索引
序列
–提供给你一组有规律有顺序的数值，序列提供一组唯一的值，并且主要就是用来提供主键的，不用人为插入主键
–数据
–创建序列语法
[!创建序列语法]
create sequence 序列名
increment by 每次增长的数值是多少，每次加多少，步长
start with 从哪个值开始(默认从1开始)
maxvalue 或 nomaxvalue 最大值是多少或者没有最大值
minvalue 或 nominvalue 最小值或者没有最小值
cycle 或 nocycle 循环或者不循环
cache 或 no cache 缓存或不缓存
increment by
用于定义序列的步长，每次增长多少，如果填2的话那就是 1 3 5 7 9 去生成数字，如果省略的话默认为1，也可以是
负数，说明递减序列，每次减少多少个值
start with
定义序列的初始值(产生的第一个值是什么)，不写默认为1，开始值不能设置比minvalue小
maxvalue 或 nomaxvalue
定义序列能产生的最大值，nomaxvalue是默认选项，代表没有最大值的定义，这个时候能产生的最大值10的27
次方
minvalue 或 nominvalue
定义序列能产生的最小值，nominvalue是默认选项
–cycle 或 nocycle
表示序列的值打到限制值的时候，是否继续循环，cycle表示循环，nocycle表示不循环，如果循环的话表示当
序列递增到最大值时，回到最小值再次循环，对于递减序列来说，达到最小值时，重新回到最大值，如果是
递增序列，达到最大值的时候，回到最小值，在不循环的时候，尽量不设置最大值，如果不循环达到最大值的时候
再获取值会报错，一般不会循环在插入主键的时候
–cache 或 no cache
定义存放序列的内存块的大小，默认为20，nocache表示不缓存，对序列进行内存缓冲，可以改善序列的性能
缓存几个数到内存里去，定义的时候必须要比序列里可能出现的数值的个数要少，最少要缓冲2个值，不然会报错
create sequence sq_1
increment by 1
start with 1
maxvalue 10
minvalue 1
cycle
cache 2
–使用序列，查询序列
有两个功能健,nextval 下一个值,currval 当前值
–返回序列中的下一个有效值，任何用户都可以引用
select 序列名.nextval
from dual
–返回序列中存放当前的值
select 序列名.currval
from dual
select sq_1.nextval
from dual
select sq_1.currval
from dual
–在查序列的时候必须先运行一次nextvalue，否则会报错
–关于序列的数据字典
–查询当前用户定义的所有序列
select *
from user_sequences
–如果定义序列的时候nocache不缓存的话，则last_value显示当前序列的下一个值
–实际开发中最常用的序列写法
create sequence a_1;
–添加序列数据
insert into 表名 values(序列名.nextval,数据,数据);
create table cs20240614 (id int,name varchar2(20))
insert into cs20240614 values(a_1.nextval,‘小张’);
–修改序列
alter sequence 序列名
increment by 修改步长
maxvalue 最大值
minvalue 最小值
nocycle 打开或关闭
cache 装入内存
–删除
drop sequence 序列名;
同义词
–给现有的数据库对象取一个别名，将来能够简化访问数据库对象，提高数据库的安全性
–创建同义词
[!创建同义词]
create synonym 同义词名
for 数据库对象名
create synonym emp_a
for hr.employees
–没办法drop掉同义词的原表，但是delete可以
–删除同义词
drop synonym 同义词名;
索引
–索引的定义索引是什么，什么时候需要建索引，什么时候不要建索引，以及索引优缺点，索引失效，
–索引的底层逻辑，索引碎片率（索引修复）
索引的概念
[!索引的概念]
1 索引是与表相关的一个可选项
可以在表上建立索引，也可以不建索引，一个表可以有多个索引，或者一个索引都没有2 提高sql语句执行的性能
索引最重要的就是提高sql语句的执行效率，索引对表的作用，相当于目录对于书的作用，使我们可以快速定位
到想要的内容上，如果表使用索引的话，索引就能快速定位到数据的物理位置上，表里每行数据的物理地址rowid
所以一般情况下要建立索引，提高查询效率3 减少磁盘i/o
磁盘的输入和输出，input 和 output的缩写，提高磁盘吞吐量，未雨绸缪，把数据提前放在数据缓冲区里4索引是完全独立的
索引在逻辑上和物理上都是完全独立于表的，索引和表里的数据可以被存放在不同的表空间下，意味着可以放在
不同的磁盘里5索引会自动维护
如果表上面已经建立了索引，当你对这张表进行增删改的时候，insert,update,delete，索引是不需要管的，系统
会自己维护，使索引仍然能提供目录的功能
–索引的分类
主要分为两大类，b树索引和位图索引
b树索引是用的最多的也是最常见的最普通的，还有一种位图索引，这两种索引在存储上和逻辑结构上是完全不一样的，
b树索引又分为：唯一索引，组合索引，反向索引，函数索引
–b树索引的结构
balance 平衡树
–创建索引
创建普通索引(b树索引)
create index 索引名
on 表名(列名)
–b树索引可以建立在任何数据上，没有要求必须应用在哪些数据上
create table cs20240614_1(id int,name varchar2(20));
create index index_1
on cs20240614_1(id)
索引数据字典
[!索引数据字典]
–关于索引的一些数据字典
–当前用户创建的所有索引
select *
from user_indexes;–当前用户创建的所有索引的索引名，索引位于哪个表，作用在哪个列上
select *
from user_ind_columns;–数据库中所有的索引
select *
from sys.all_indexes;
分析索引碎片率
分析索引里存不存在碎片，有多少碎片，是多还是少，然后在index_stats数据字典里查看索引碎片率，如果碎片存在过多的话，重建索引，重建索引有两种方式，删了重新建或者重建索引
分析索引
analyze index 索引名 validate structure;
显示索引碎片率
select name,(del_lf_rows_len/lf_rows_len)*100 as 索引碎片率
from index_stats
where name = ‘大写的索引名’
分析索引实验
create table cs_1 (数值 int);
begin
for a in 1..1300000 loop
insert into cs_1 values (a);
if mod(a,100) = 0 then commit;
end if;
end loop;
end;
select *
from cs_1;
create index sy_1
on cs_1(数值);
analyze index sy_1 validate structure;
select name,(del_lf_rows_len/lf_rows_len)*100||'%' as 索引碎片率
from index_stats
where name = 'SY_1'
delete from cs_1
where rownum < 1000000
–开发中索引碎片率达到30%以上，建议重建索引
–重建索引
alter index 索引名 rebuild;
alter index sy_1 rebuild;
–创建大表(测试)
select *
from hr.employees t1
cross join hr.employees t2
cross join hr.employees t3
cross join hr.employees t4
–删除数据库中所有的表
select ‘drop table ‘||owner||’.’||table_name||‘;’
from sys.all_tables;
–唯一索引
系统自动创建的索引
主键约束或者唯一约束在定义这两个约束的时候，系统就会自动在列上创建唯一索引，如果索引是定义约束的
时候自动建立的，在禁用约束的时候对应索引也会被删除
–手动创建唯一索引
如果某一列里全都是唯一的数据的话可以建立一个唯一索引
create unique index 索引名
on 表名(列名)
–组合索引
在多个列上创建的索引
create index 索引名
on 表名(列名,列名…)
–员工表
where 部门号 = ?
and 工种 = ?
–人员信息表
where 省份 = ？
and 城市 = ？
and 区 = ？
–反向索引
create index 索引名
on 表名(列名) reverse;
–工号
数据在各个子节点分配的不均匀的时候，可以使用反向索引，让索引反着看数据，为了让这种数据更均匀，
让数据不全都集中在某一子节点上
1001，1002，1004…
1001，2001，4001
–函数索引
基于一个或多个列上的函数或表达式创建的索引，表达式中不能出现组函数，必须是单行函数
如果某一列经常要使用函数进行查询的话，那就要建立函数索引
create index 索引名
on 表名(函数(列名))
–经常要找tom，名字列里有tom，TOM，Tom
select *
from 表
where lower(名字)=‘tom’
–位图索引 bitmap
b树索引是通过指针指向数据，像一根根绳子一样连着数据，但是位图索引完全不一样，它是用0101去判断的
create bitmap index 索引名
on 表名(列名)
–什么时候适合建位图索引
位图索引适合建立在低基数列上，列里面数据的重复性较高，有张表100万条数据，性别列里只有男女，位图
索引比较省空间，因为它只用0和1来指向数据的位置，如果索引列经常被更新的话也不建议建位图索引
–位图索引的逻辑结构
–什么时候建索引
1 oracle数据库会为表的主键和包含唯一约束的列自动创建索引，所以创建唯一约束的时候，考虑这一列是否
会经常作为where条件进行查询
2 如果某个表的数据量比较大(十几二十万以上)，某一列经常要作为where条件筛选，并且检索出来的行
经常小于总表数据的5%，那可以考虑建索引
3 对于两表连接的字段，考虑建立索引。如果经常在某个表的字段上order by 那也建议建立索引
–什么时候不要建索引
1 列不经常作为连接条件也不经常出现在where里
2 查询的数据大于2%-4%
3 不应该在小表上建立索引，但是如果有低基数列可以考虑建个位图索引
4 经常进行dml操作的表，创建索引的时候就要权衡一下，建索引会导致表的dml速度变慢，因为索引会自行维护
不能盲目乱建
–索引的优点
1 加快检索数据的速度，提高查询效率
–索引的缺点
1 当对表进行dml操作的时候，索引自动维护，降低dml操作的速度
2 索引需要占实际的物理空间，相当于拿空间换时间
3 创建索引和系统自动维护索引需要耗费时间，这种时间会根据数据量的增加而增加
–索引的失效
–系统会自行决定什么时候使用索引，用什么索引，用不用索引，如果系统觉得全表扫描跟走索引的速度差不多
–就不会走索引了，或者比走索引还快，那就不走索引，避免系统选择全表扫描
–导致索引失效的几种情况
1 没有where子句
添加索引的列必须要在where条件后适当的使用才会生效，如果连where条件都没有，肯定不会走索引
2 使用 is null 或者 is not null
select *
from 表
where 列 is null
3 where子句中对索引列使用函数
如果对已经存在非函数索引的列使用函数的话，系统会忽略掉已有的索引
–hire_date上建立了一个b树索引，执行下面的语句索引就会失效
select *
from 表
where round(hire_date) = ‘2002-01-01’
–把函数放在条件上，索引是可以生效的
select *
from 表
where hire_date = to_date(‘2002-01-01’,‘yyyy-mm-dd’)
4 使用 like ‘%T’ 进行模糊查询
like 以 %开头，会导致索引失效
尽量不要用like或者把%放在后面
select *
from 表
where first_name like ‘%e%’
5 where 条件中使用!=操作
不等于操作都有什么：!=,in,not in,not exists
–换成or替代
失效: 某列 !=0
有效: 某列>0 or 某列<0
6 条件是等于和一个范围，索引不会被合并使用
select *
from employees
where job_id = ‘it’
and department_id > 10
id和department_id都是索引列。这种情况下oracle不会合并索引，它只会使用第一个索引
7 隐性转换导致索引失效
假设员工号是varchar2类型的字段，在这个字段上有索引
select *
from employees
where employee_id = 100;
–不会失效
select *
from employees
where employee_id = ‘100’;
8 or语句中其中一个条件没有使用索引列
select *
from student
where name = ‘张三’
or age = 18
9 联合索引，但是不适用左边的列，或者左边的列没有找到数据
–name列是左边的列，age是右边的列
–会用到索引
select *
from student
where name = ‘aaa’
select *
from student
where name= ‘aaa’
and age = 18
–不会走索引
select *
from student
where age = 18
–假设name列里没有小明这条数据
select *
from student
where name= ‘小明’
and age = 18
如果是组合索引的话，左边的列是负责找大方向的，如果最左侧的列没有被选择上，或者最左边的没有where条件
里要找的数据，那么索引就会失效，系统会选择别的索引或者直接全表扫描
查看执行计划
方法一：explain plan for
第一步：
EXPLAIN PLAN FOR
select * from hr.employees;
第二步（查看动态性能视图，显示最后一次执行的SQL语句的执行计划）
DBMS_XPLAN.DISPLAY
dbms_xplan.dlsplay
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
[!1]
由数据库自己生成的，里面有关于sql语句执行的详细信息，包括sql语句的执行顺序，使用的索引是什么，
表之间的关联方式，查看执行计划是为了查看sql语句的性能表现怎么样，可以通过它来得知语句有没有性能
问题或者有没有优化空间
–sqlplus 中查看执行计划的语句
set autotrace trace
–plsql
选中语句，按f5查看执行计划
select *
from hr.employees
where employee_id = 150
-执行计划界面
1 description 执行计划描述
2 基数(rows) oracle估计当前步骤返回的结果集行数
3 字节(bytes) 执行sql对应步骤返回的字节数
4 耗费(cost) cpu的耗费，oracle估计该步骤的执行耗费或cpu耗费
5 时间(time) oracle估计该步骤需要多长时间
–怎么看执行顺序
缩进最多的优先执行，缩进如果相同，上面的优先执行
–按f5显示的是简易执行计划，信息不完全，不是真实的执行计划，如果要看真实的执行计划，要写程序块
declare
a date;
begin
execute immediate ‘alter session set statistics_level=ALL’;
a:=sysdate-1;
for i in (select * from hr.employees where employee_id = 100) loop
null;
end loop;
for x in (select p.plan_table_output
from table(dbms_xplan.display_cursor(null,null,‘advanced -bytes PROJECTION allstats last’)) p) loop
dbms_output.put_line(x.plan_table_output);
end loop;
rollback;
end;
–普通用户想访问真实的执行计划，需要sys用户给普通用户4个权限
alter user sys account unlock;
alter user sys identified by 123456;
–4个权限
grant select on v_KaTeX parse error: Expected group after '_' at position 30: …ant select on v_̲sql_plan_statistics_all to 用户名;
grant select on v_KaTeX parse error: Expected group after '_' at position 34: …ant select on v_̲sql_plan to 用户名;
grant select on v_KaTeX parse error: Expected group after '_' at position 29: …ant select on v_̲sql_plan_statistics_all to hr;
grant select on v_KaTeX parse error: Expected group after '_' at position 33: …ant select on v_̲sql_plan to hr;
starts sql执行的次数
E-rows 执行计划预计返回的行数
a-rows 实际返回的行数，E-rows和a-rows对比，就可以就可以知道具体哪一步执行计划出问题了
a-time 每一步实际执行的时间，可以看出耗时的sql
–表的扫描方式
1 table access full 全表扫描
如果全表扫描的话，oracle会读取表中所有的行，并检查每一行是否满足where条件，数据量太大的表
不能走全表扫描，除非要提取的数据太多了，占到表总数据量的5%-10%或者以上了
2 table access by rowid 通过rowid扫描
行的rowid指出了数据的物理地址，所以通过rowid可以快速定位到数据上
3 table access by index scan 索引扫描
index unique scan 索引唯一扫描
结果集只返回一行数据
当查询字段有唯一约束，主键约束时，触发索引唯一扫描
index range scan 索引范围扫描
至少返回一条记录
有3种情况会触发索引范围扫描
1 在唯一约束列上使用了范围操作符(小于（<），大于(>)，小于等于(<=)，
大于等于(>=)， between)
2 在组合索引上，只使用了部分列进行查询(必须包含最左侧的列的，否则会走全表扫描)
3 对非唯一索引列上进行查询
index full scan 索引全扫描
order by唯一索引时触发，结果集会被排好序
index skip scan 索引跳跃扫描
必须是组合索引
拿除了索引第一列以外的其他列作为条件
–表的连接方式和类型
表连接类型：写sql的时候，表之间的关联关系
表连接方式：执行计划里表连接的方式
表连接类型
inner join
left join
right join
full join
cross join
–什么是驱动表和被驱动表
driving table (驱动表)
probed table (被驱动表)
从驱动表中拿出一条数据，去与被驱动表的所有记录进行匹配，能匹配上的合并连接，驱动表相当于
外层循环，被驱动表类似于内层循环，它的运行方式类似于循环
for a in (select * from hr.departments) loop
for b in (select * from hr.employees) loop
end loop;
end loop;
select *
from hr.employees t1 --驱动表 20000条
inner join hr.departments t02 --被驱动表 200
on t1.department_id = t2.department_id
–区分驱动表和被驱动表
left join的时候：左表是驱动表
right join: 右表是驱动表
inner join: 自动选择数据量小的表作为驱动表
–选择驱动表和被驱动表的时候，遵循小表驱动大表
对于驱动表来说必须走全表扫描，假设大表20000条数据，小表200条数据，
驱动表是一定不会走索引的，而被驱动表可以建立索引
加快查询速度，这个时候如果拿小表去驱动大表，小表是驱动表，大表是被驱动表，只需要匹配200次
大表是驱动表，小表是被驱动表，要匹配20000次
–关联的优化一句话
拿小表作为驱动表，在大表上建立索引
表连接方式
–嵌套循环 nested loops
工作方式是从一张表中读取数据(驱动表)，然年访问另一张表(被查找表，被驱动表，通常有索引)，
驱动表中的每一行与被驱动表中的满足条件的记录进行join，类似一个嵌套循环
访问表的次数：驱动表返回几条，被驱动表被访问多少次
适用场景：小量数据的连接或者小表连接大表
使用限制：没有
索引列要建在哪：驱动表的条件列和被驱动表的条件列
–哈希连接 hash join
工作方式，取出驱动表的数据集，然后将其构建成内存中的一个hash函数，形成一个hash key连接条件列，
创建hash位图，然后取出被驱动表的数据集，对其中每一条数据的连接条件列使用相同的hash函数
并找到对应的数据在hash key中的位置，在该位置上能否找到匹配的数据
访问表的次数：表最多就被访问1次
适用场景：大数据量的连接或者小表连接大表
使用限制：只能使用=作为连接条件，不能用其它的连接条件
索引列要建在哪：没有任何要求
–排序合并连接 sort merge join
先将关联表的关联列各自做排序，然后从各自的排序列中抽取数据，到另一个排序表中做匹配
访问表的次数：表最多就被访问1次
适用场景：大数据量且有排序操作
使用限制：没有任何限制
索引列要建在哪：两表关联的字段上
–笛卡尔积 cartesian produck
如果两个表没有连接条件就会走笛卡尔积，要避免
select *
from hr.employees t1
cross join hr.departments t2
–驱动表和被驱动表的概念，只存在于嵌套循环和哈希连接两种连接方式中
–强制走索引
select/*+index(表名 索引名)/
from 表
where 条件
select/*+index(employees EMP_EMP_ID_PK)/
from employees
where employee_id !=150
–删除索引
drop index 索引名
–索引被删除了或损坏了，不会对表产生任何影响，影响的只有查询速度
–表如果被删了，那么相关索引全部被删除
–只有索引的拥有者或者拥有drop any index权限的用户才能删除索引
set操作符
把两个查询的结果进行合并之后再进行一些处理，并集交集差集
create table cs20240617_1 (id int,name varchar2(20));
create table cs20240617_2 (id int,name varchar2(20));
insert into cs20240617_1 values (1,‘小明’);
insert into cs20240617_1 values (2,‘小黑’);
insert into cs20240617_1 values (5,‘小张’);
insert into cs20240617_1 values (3,‘小白’);
insert into cs20240617_1 values (4,‘小李’);
drop table cs20240617_1
insert into cs20240617_2 values (1,‘小明’);
insert into cs20240617_2 values (2,‘小黑’);
insert into cs20240617_2 values (5,‘小青’);
insert into cs20240617_2 values (3,‘小白’);
insert into cs20240617_2 values (4,‘小李’);
insert into cs20240617_2 values (6,null);
drop table cs20240617_2
select *
from cs20240617_1
select *
from cs20240617_2
UNION：用于合并两个或多个查询的结果集，并消除重复行。UNION ALL：与UNION类似，但不会消除重复行。INTERSECT：返回两个或多个查询结果集中都存在的行。MINUS：返回第一个查询结果集中存在，但不在第二个查询结果集中的行。
例如：
sql复制
SELECT column_name FROM table1 UNION SELECT column_name FROM table2;
–union
将两个查询结果合并，进行去重和升序排序，完全一样的数据只保留一个
select *
from cs20240617_1
union
select *
from cs20240617_2;
–union all
将两个查询结果合并，不去重，不排序，相当于一个查询在上，一个查询在下，直接拼在一起，没有任何其它操作
select *
from cs20240617_1
union all
select *
from cs20240617_2;
–intersect
将两个查询结果合并，只保留两个表中完全一样的数据，去重，升序排序
select *
from cs20240617_1
intersect
select *
from cs20240617_2;
–minus
将两个查询结果合并，以表1为主，把查询1里跟查询2完全相同的数据去掉，只保留两个表不一样的数据
select id as 编号,name as 名字
from cs20240617_2 t1
union all
select *
from cs20240617_2
order by 1
–注意事项
1 列的数量要一致
2 列的顺序也要一致
3 列的数据类型要一致
4 括号可以改变set操作符的执行顺序
5 set操作符默认以第一个查询为主，给第一个查询的列取别名，那么整个结果集的列名就是这个别名
6 除了union all以外，其余的默认以第一个查询的第一列进行排序
7 order by 子句只能在所有查询的最后出现，整个语句的最后，可以使用第一个查询的列名或别名以及相对位置
进行排序
8 当两个表或者两个查询想使用set操作符的时候，但是列的数据类型不一致，或者列的数量不一致，可以使用
to_char(列名),to_number(列名),to_date(列名),或者to_number(null),to_char(null),to_date(null)，使两个查询
的列保持一致，数据类型也一致
select id,to_char(null)
from cs20240617_2 t1
union all
select id,name
from cs20240617_2
–数据逆透视表
–pivot
–把数据透视表转回正常的表
–pivot
CREATE TABLE TEST
( ID NUMBER(10),
DDID NUMBER(10),
ZL varCHAR(15),
AM NUMBER(10)
);
INSERT INTO TEST (ID, DDID, ZL, AM) VALUES (1, 11, ‘苹果’, 10);
INSERT INTO TEST (ID, DDID, ZL, AM) VALUES (1, 12, ‘橘子’, 5);
INSERT INTO TEST (ID, DDID, ZL, AM) VALUES (2, 21, ‘香蕉’, 10);
INSERT INTO TEST (ID, DDID, ZL, AM) VALUES (3, 31, ‘香蕉’, 5);
INSERT INTO TEST (ID, DDID, ZL, AM) VALUES (4, 41, ‘苹果’, 10);
INSERT INTO TEST (ID, DDID, ZL, AM) VALUES (4, 42, ‘香蕉’, 5);
INSERT INTO TEST (ID, DDID, ZL, AM) VALUES (4, 43, ‘香蕉’, 10);
INSERT INTO TEST (ID, DDID, ZL, AM) VALUES (5, 51, ‘苹果’, 5);
select *
from TEST;
select *
from(select id,zl,am
from test)
pivot(sum(am) for zl in(‘苹果’ 苹果,‘橘子’ 橘子,‘香蕉’ 香蕉))
where 苹果 is not null
union
select id, ‘香蕉’ zl,香蕉 am
from(select id,zl,am
from test)
pivot(sum(am) for zl in(‘苹果’ 苹果,‘橘子’ 橘子,‘香蕉’ 香蕉))
where 香蕉 is not null
union
select id, ‘橘子’ zl,橘子 am
from(select id,zl,am
from test)
pivot(sum(am) for zl in(‘苹果’ 苹果,‘橘子’ 橘子,‘香蕉’ 香蕉))
where 橘子 is not null
列转行
unpivot
select 原表中的列变成数据之后的新列列名
from 原表表名或者子查询
unpivot(原表中间的数据放在哪个新列里 for 把原来的列名放到哪个新列里当数据用 in (要用原表的哪些列当数据))
select id,zl,am
from (select *
from(select id,zl,am
from test)
pivot(sum(am) for zl in(‘苹果’ 苹果,‘橘子’ 橘子,‘香蕉’ 香蕉)))
unpivot(am for zl in (苹果,橘子,香蕉))
高级子查询
–exists和not exists --in not in
–exists存在，not exists不存在
–分为两种情况
（一）子查询和主查询没有进行相关子查询
exists 存在
一般放在where里，用来判断子查询中是否有记录，它只返回一个布尔值，只储存true或false，如果子查询里有值，子查询的结果不为空，exists返回ture，如果子查询的结果集为空，子查询没找到数据，返回false，如果exists返回的是true，则主查询执行，返回主查询里的所有数据，如果exists返回的是false，主查询则不执行，不返回任何值，exists不返回值，只判断真或假
–判断水果表里有没有人买过牛奶，如果有则返回买过牛奶的人的名字
select name
from hr.shuiguo9
where shuiguo = ‘牛奶’
and exists (select *
from hr.shuiguo9
where shuiguo = ‘牛奶’)
–查询employees表里是否有工资大于20000的人，如果有则返回工资大于20000的人的信息
select *
from hr.employees
where salary > 20000
and exists (select 1
from hr.employees
where salary > 20000)
not exists
不存在，跟exists相反，如果子查询没有数据返回true，有数据返回false，相当于变成子查询结果为空返回true
，不为空返回false
select *
from hr.employees
where salary > 20000
and not exists (select 1
from hr.employees
where salary > 30000)
–exists跟not exists都是在子查询结果为true的情况下返回值，只不过在判断true上有区别
（二）第二种情况，子查询与主查询进行了相关子查询
–exists和相关子查询
判断主查询的数据是否存在于子查询的结果集里，先跑出主查询的整张表，从主查询里拿出一条数据，放到子查询里进行匹配，如果在子查询中匹配不上满足条件的数据则返回false，并继续在子查询中查找满足条件的数据，直到遍历了子查询的所有数据之后还是没有能匹配上的，这条数据就不会返回给主查询，直接丢掉，如果在子查询中匹配上了满足条件的数据那就直接返回true，则将这条数据返回给主查询，且不再继续寻找，判断为true的保留，其余全不要
–not exists 和相关子查询
不满足条件的保留，满足条件的丢掉
–查询部门表里暂时还没有员工的部门
select *
from hr.departments t1
where not exists(select *
from hr.employees t2
where t1.department_id = t2.department_id)
–
–查询公司里所有经理的员工号，工资，部门号
select *
from hr.employees t1
where exists (select 1
from hr.employees t2
where t1.employee_id = t2.manager_id)
–查询1号课程比2号课程成绩高的所有学生的学号
select *
from hr.score t1
where c_id =1
and exists (select 1
/*true false*/ from hr.score t2
where t2.c_id = 2
and t1.stu_id = t2.stu_id
and t1.s_or > t2.s_or)
–谁买过小乔买的水果
select *
from hr.shuiguo9 t1
where shuiguo in (select shuiguo
from hr.shuiguo9 t2
where t2.name = ‘小乔’)
–没买过小乔买的水果的人
select *
from hr.shuiguo9 t3
where not exists (select *
from hr.shuiguo9 t1
where exists (select *
from hr.shuiguo9 t2
where name = ‘小乔’
and t1.shuiguo = t2.shuiguo)
and t3.name = t1.name)
–查询选修了全部课程的学生姓名
–不存在有一课该学生没考
select *
from hr.stu t1
where not exists(select 1
from hr.cour t2
where not exists(select 1
from hr.score t3
where t1.stu_id = t3.stu_id
and t2.c_id = t3.c_id))
exists和in的区别
–两张表，在t1里找想要的数据，并且要用到t2表来找指定的数据
–exists
select *
from t1
where exists(select 1
from t2
where t1.a = t2.a); --100万条
–in
select *
from t1
where a in (select t2.a
from t2) --100万条
[!总结]
in: t1一条数据要与子查询中的所有数据进行对比
exists ：t1一条数据与子查询中的数据进行对比，遇到匹配数据返回true，并且不再继续对比当t1 t2 两个表数据量差不多的时候，用in或exists没有太大区别，exists也略占优势
当t1数据量小，而t2的数据量非常大的时候，使用exists的查询效率高
当t1数据量大，而t2的数据量非常小的时候，使用in的效率更高–not exists和not in
not exists 和not in都要遍历一遍主查询和子查询，但是not in走不了索引，not exists可以走索引，所以not exists
和not in对比，无论什么情况not exists都要比not in高
–with as
通过定义一个子查询，降低扫描表的次数，减少代码重跑，相当于建立一个临时表，把一些会重复用到的sql语句放在with as里，取一个别名，后面的查询就可以直接对这个别名进行查询
with 别名1 as (子查询或者结果集),
别名2 as (子查询或者结果集),
别名3 as (子查询或者结果集)
select *
from 别名1;
–注意事项
1 with as语句最后不能加;
2 with as 语句不能单独跑，声明好直接就得用
3 with as 在定义好第一个查询之后，定义第二个查询时可以直接使用已经定义好的查询
–一般用来配合set操作符
with 科技部 as (select *
from hr.employees
where department_id = 50),
人事部 as (select *
from hr.employees
where department_id = 90),
数据部 as (select *
from hr.employees
where department_id = 20)
select *
from 科技部
union all
select *
from 人事部
union all
select *
from 数据部
–rownum
–员工表，查询rownum10-20的人
select *
from(select employee_id,salary,rownum a
from hr.employees)
where a between 10 and 20
–用with as
with emp as (select employee_id,salary,rownum a
from hr.employees)
select *
from emp
where a between 10 and 20
CREATE TABLE DGUI (
CODE VARCHAR2(20),
NAME VARCHAR2(20),
PARENTCODE VARCHAR2(20));
INSERT INTO DGUI VALUES (‘01’, ‘北京市’, ‘0’);
INSERT INTO DGUI VALUES (‘02’, ‘河南省’, ‘0’);
INSERT INTO DGUI VALUES (‘0101’, ‘海淀区’, ‘01’);
INSERT INTO DGUI VALUES (‘010101’, ‘上地’, ‘0101’);
INSERT INTO DGUI VALUES (‘010102’, ‘清河’, ‘0101’);
INSERT INTO DGUI VALUES (‘0102’, ‘西城区’, ‘01’);
INSERT INTO DGUI VALUES (‘0103’, ‘东城区’, ‘01’);
INSERT INTO DGUI VALUES (‘0201’, ‘安阳市’, ‘02’);
INSERT INTO DGUI VALUES (‘020101’, ‘林州’, ‘0201’);
INSERT INTO DGUI VALUES (‘020102’, ‘滑县’, ‘0201’);
INSERT INTO DGUI VALUES (‘0202’, ‘落阳市’, ‘02’);
树查询，递归查询
–用来找具有父子关系的数据，上下级关系，
递归查询就相当于：国下面有各个省，省下面有各个市，市下面有各个区县，这就是递归
语法
select *
from 表名
start with 列名 = 值
connect by prior 列名 = 列名
一、start with 是用来定义根节点的，以某条数据或某些数据为基础来查找第二层数据，然后再找第三层，第四层，定义父辈，以父辈为基础向下找子辈，孙辈，一个查询可以定义多个父辈，根据条件来决定有几个父辈，然后将所有的子辈，孙辈都找出来
二、prior 关键字
是可选项，prior在等号的哪一边，决定了递归是怎么样的父子关系或者递归是向上查询还是向下查询，prior
在哪边，代表哪边是"我的"
–员工表找出120员工和他的所有下属
select *
from hr.employees
start with employee_id = 120 --根节点设置为120号员工的员工号
connect by prior /*我的*/employee_id = /*别人的*/manager_id /*我的员工号是别人的经理号，找谁的经理号是我的员工号*/
–找150号员工的上司，和上司的上司
select *
from hr.employees
start with employee_id = 150 --根节点设置为150号员工的员工号
connect by /*别人的*/employee_id = prior /*我的*/manager_id --别人的员工号是我的经理号
–找出北京市为根节点向下查询所有子节点
select *
from dgui
start with name = ‘北京市’
connect by prior code = parentcode
三、level关键字
可以放在select里显示当前查询结果每条数据的分级或者放在where里查找指定的分级数据
select t1.*
from dgui t1
where level = 2
start with name = ‘北京市’
connect by prior code = parentcode
分组汇总：rollup
group by rollup(字段1，字段2，子段3…字段n)
group by的子函数，只能在group by里用，rollup主要用于分组汇总，如果rollup里有n个字段，
那就分别按照 [字段1，字段2，字段3…字段n] ，[字段1，字段2，字段3],[字段1，字段2,[字段1] 配合
分组函数按照颗粒度由细到粗进行分组统计，有几个字段就统计n+1次
–如果只在rollup里填一个学科
select coalesce(to_char(t2.c_id),‘总计’),sum(s_or)
from hr.stu t1
inner join hr.score t2
on t1.stu_id = t2.stu_id
inner join hr.cour t3
on t2.c_id = t3.c_id
group by rollup(t2.c_id)
–如果填学号和学科的话
select t1.stu_id,t2.c_id,sum(s_or)
from hr.stu t1
inner join hr.score t2
on t1.stu_id = t2.stu_id
inner join hr.cour t3
on t2.c_id = t3.c_id
group by rollup(t1.stu_id,t2.c_id)
分组聚合:grouping sets
grouping sets((字段1,字段2),(字段2),())
根据不同的维度进行分组聚合，等价于将不同维度的group by结果集进行union all，可以理解为
将从右向左group by之后的结果用分组函数显示结果再进行union all，()等同于对整表使用分组函数
select t1.stu_id,t2.c_id,sum(s_or)
from hr.stu t1
inner join hr.score t2
on t1.stu_id = t2.stu_id
inner join hr.cour t3
on t2.c_id = t3.c_id
group by grouping sets((t1.stu_id,t2.c_id),(t1.stu_id),())
–等价于
select t1.stu_id,t2.c_id,sum(s_or)
from hr.stu t1
inner join hr.score t2
on t1.stu_id = t2.stu_id
inner join hr.cour t3
on t2.c_id = t3.c_id
group by t1.stu_id,t2.c_id
union all
select t1.stu_id,null,sum(s_or)
from hr.stu t1
inner join hr.score t2
on t1.stu_id = t2.stu_id
inner join hr.cour t3
on t2.c_id = t3.c_id
group by t1.stu_id
union all
select null,null,sum(s_or)
from hr.stu t1
inner join hr.score t2
on t1.stu_id = t2.stu_id
inner join hr.cour t3
on t2.c_id = t3.c_id
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
