Oracle的知识点总结
语法部分主要补充与MySQL不同的地方
1. 连接符||
--需求1：查询出员工的名字，要求显示的员工名字前面加上“姓名：”的字符串，显示结果参考：姓名：scott
SELECT '姓名:'||ename FROM emp;
--需求2：将和员工的编号和员工的姓名都放在一个结果字段中显示。
SELECT empno||'>>'||ename FROM emp;
注意：单引号表示字符串,双引号用于别名
2. 伪表-dual
DUAL 是一个‘伪表’（也称之为万能表），可以用来测试函数和表达式
--查询显示当前日期
SELECT SYSDATE FROM dual;
SELECT 1+1 FROM dual;
3.过滤语句查询where
--需求1：查询关于KING这个人的记录。
SELECT * FROM emp WHERE ename = 'KING';
--需求2：查询入职日期是1987/4/19的员工的信息。
SELECT * FROM emp WHERE hiredate = to_date('1987/4/19','yyyy/MM/dd');
4. 转义字符-Escape
--需求1：查询名称是带有”x”字符的员工的记录信息。
SELECT * FROM emp WHERE ename LIKE '%x%';
--需求2：查询员工名称中含有下划线（“_”）的员工.
SELECT * FROM emp WHERE ename LIKE '%|_%' ESCAPE '|';
--需求3：查询姓名是4个字符的员工的信息。
SELECT * FROM emp WHERE ename LIKE '____';
escape用于转义特殊字符.在需要转义的地方前加上escape规定的转义符
5. 单行函数
5.1 字符函数
img01.png
1. 字符函数
1. 大写 upper
2. 小写 lower
3. 首字母大写 initcap
select upper('hello') from dual;
select lower('HELLO') from dual;
select initcap('i love you') from dual;
2. 字符控制函数
1. concate / || 连接
2. substr 截取
3. length 长度
4. instr 第几个
5. trim 去除首位空格/指定字符
6. replace 替换
select concat('hello',' world') from dual;--hello world
select substr('helloworld',3,6) from dual;--llowor,第几个开始截,包头不包尾
select length('hello') from dual;--5
select instr('hello','h') from dual;--1,第几个
select trim('H' from 'Hello WorHldH') from dual;--ello WorHld去掉首尾H
select trim(' h h hhhh ') from dual;--h h hhhh去掉首尾空格
select replace('java','a','e') from dual;--jeve
5.2 数字函数
-
round 四舍五入
-
trunc 截断
-
mod 求余
select round(13.16,1) from dual;--13.2 select trunc(13.16,1) from dual;--13.1 select mod(20,3) from dual;--2
5.3 日期函数
img02.png
需求1：计算员工的工龄（工龄：当前的日期和入职的日期的差），要求分别显示员工入职的天数、多少月、多少年。
select round(sysdate-hiredate)||'天' 入职天数,trunc(months_between(sysdate,hiredate),1)||'月' 入职月数,
trunc(months_between(sysdate,hiredate)/12,1)||'年' 入职年数 from emp;
需求2：查看当月最后一天的日期。
select to_char(last_day(sysdate),'yyyy-MM-dd') from dual;--2017-07-31,sql语句不区分大小写,MM是因为习惯
需求3：查看指定日期的下一个星期天或星期一的日期。（next_day(基础日期,星期几)）
select to_char(next_day(sysdate,1),'yyyy-MM-dd') from dual;--下个星期天的日期,2017-07-23
select to_char(next_day(sysdate,2),'yyyy-MM-dd') from dual--下个星期天的日期,2017-07-24
//超过12点返回第二天的日期,否则返回当天的日期
select round(sysdate) from dual;
5.4 转换函数
to_char : 转换为字符格式
to_date : 转换为日期格式
to_number : 转换为数字格式
需求1：显示今天的完整日期，结果参考：“2015-07-06 11:07:25”。
select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') from dual;--2017-07-17 21:05:21
需求2：显示今天是几号，不包含年月和时间，结果参考：“8日”。
select to_char(sysdate,'dd')||'日' from dual;--8日
需求3：显示当月最后一天是几号，结果参考：”30“。
select to_char(last_day(sysdate),'dd') from dual;--31
需求4：xiaoming的入职日期是2015-03-15，由于其入职日期当时忘记录入，现在请将其插入到emp表中。
update emp set hiredate=to_date('2015-03-15','yyyy-MM-dd') where ename ='xiao_ming';
需求5：查看2015年2月份最后一天是几号，结果参考“28“
select to_char(last_day(to_date('2015-02','yyyy-MM')),'dd') from dual;--28
---------------------------------------------------------
和java不同，Oracle的日期格式对大小写不敏感。
日期格式的常见元素：
img03.png
--需求：查看显示今天是星期几
select to_char(sysdate,'day') from dual;--monday
数字格式的常见元素：
img04.png
9代表任意数字，可以不存在。0代表数字，如果该位置不存在，则用0占位。
需求：查询员工的薪水，格式要求：两位小数，千位数分割，本地货币代码。
select to_char(sal,'L99990.00') from emp;--$1600.00
5.5 滤空函数
1. nvl(a,c)，当a为null的时候，返回c，否则，返回a本身。
2. nvl2(a,b,c),当a为null的时候，返回c，否则返回b
其中，nvl2中的2是增强的意思，类似于varchar2。
3. nullif(a,b),当a=b的时候，返回null，否则返回a
4. coalesce(a,b,c,d)，从左往右查找，当找到第一个不为null的值的时候，就显示这第一个有值的值。
--需求：查询员工的月收入（基本薪资+奖金）
select ename 员工姓名,sal+nvl(comm,0) from emp where sal is not null;
SELECT coalesce(NULL,NULL,1,2) FROM dual;--1,返回第一个不为空的值
5.6 条件表达式
- CASE 表达式
表达式1：
case job
when 条件 then 结果
when 条件 then 结果
...
else 结果
end
表达式2：
case
when job=条件 then 结果
when job=条件 then 结果
......
else 结果
end
- DECODE 函数
decode(job,条件1,结果1,条件2,结果2...，'其他结果')
--需求:要将工种job转换为中文
select ename,case
when job='CLERK' then '办事员'
when job='SALESMAN' then '销售人员'
else '闲杂人等'
end
from emp;
select ename,decode(job,'CLERK','办事员','SALESMAN','销售人员','闲杂人等') from emp;
decode只能用于等于的情况,如果要比较大小进行条件判断,就只能用case表达式。
-------------------------------------------------------
需求：查看公司员工的工资情况，要求显示员工的姓名、职位、工资、以及工资情况。
如果是工资小于1000，则显示“工资过低”，工资大于1000小于5000为“工资适中”，工资大于5000的，则显示“工资过高”：
select ename,case
when sal<1000 then '工资过低'
when sal>=1000 and sal<5000 then '工资适中'
when sal>=5000 then '工资过高'
else '工资未知'
end "薪资等级"
from emp;
6. 多行函数
多行函数也称之为分组函数、聚集函数。
就是把多行的值汇聚计算成一个值。
count,max,min,avg,sum :多行函数会自动滤空。
count（主键）效率很高。主键自动会有索引（提升查询效率的），这个效率最高。
count（*）全表字段扫描，效率低，但现在的数据库都对此做了优化，底层根据主键
count（1）效率高，如果没有索引，这个效率比较高
--需求：查询所属部门号大于等于20的员工信息。（无法使用having子句）
select * from emp where deptno >= 20;
--需求：查询平均工资大于2000的部门信息，要求显示部门号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>=2000;
--需求1：查询显示各个部门的平均薪资情况，并且按照部门号从低到高排列。
select d.deptno,d.dname,t.s from dept d,(select deptno,avg(sal) s from emp group by deptno) t where d.deptno=t.deptno order by deptno;
--需求2：查询显示各个部门的不同工种的平均薪资情况，并且按照部门号从低到高排列。
select deptno,job,avg(sal) from emp group by job,deptno order by deptno;
sql语句优化:加上前缀效率高。
7. 多表查询
7.1 内连接
--需求：查询一下员工信息，并且显示其部门名称
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
显式内连接：
select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;
7.2 左外连接
--查询"所有"员工信息，要求显示员工号，姓名 ,和部门名称--要求使用左外连接
select e.empno,e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno;
--Oracle特有语法：把+号看成扩展补充数据就就容易理解了
select e.empno,e.ename,d.dname from emp e,dept d where e.deptno=d.deptno(+);
7.3 右外连接
--查询“所有”部门及其下属的员工的信息。--右外连接
select e.empno,e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;
--Oracle特有语法(+)
select e.empno,e.ename,d.dname from emp e,dept d where e.deptno(+)=d.deptno;
7.4 练习
--1.查询员工信息，要求同时显示员工和员工的领导的姓名
select t1.empno,t1.ename,t2.ename from emp t1,emp t2 where t1.mgr=t2.empno;
select e.empno,e.ename,e.mgr,t2.ename from emp e,(select t.empno,t.ename from emp t) t2 where e.mgr=t2.empno;
--2.查询“所有”员工信息，要求同时显示员工和员工的领导的姓名(所有--左外连接)
select t1.empno,t1.ename,t2.ename from emp t1 left join emp t2 on t1.mgr=t2.empno;
--需求:查找工作和'SMITH' 'ALLEN' 这两个人的工作一样的员工信息
select * from emp where job in (select job from emp t2 where t2.ename='SMITH' or t2.ename='ALLEN');
--需求:查找工作和'SMITH' 'ALLEN' 这两个人的工作不一样的员工信息
select * from emp where job not in(select job from emp t2 where t2.ename='SMITH' or t2.ename='ALLEN');
--需求:查询工资比30号部门任意一个员工的工资高的员工信息。
select * from emp where sal>(select min(sal) from emp where deptno=30);
--需求:查询工资比30号部门所有员工的工资高的员工信息。
select * from emp where sal>(select max(sal) from emp where deptno=30);
8. 伪列
rownum、rowid
8.1 rownum 行号
查询操作时由ORACLE为每一行记录自动生成的一个编号
行号排序：
--需求：查询出所有员工信息，按部门号正序排列，并且显示默认的行号列信息。
select rownum,t.* from (select * from emp order by deptno) t;
order by排序，不会影响到rownum的顺序。rownum永远按照默认的顺序生成。
所谓的“默认的顺序”，是指系统按照记录插入时的顺序（其实是rowid）。
行号分页：
--需求：根据行号查询出第四条到第六条的员工信息。
--先子查询出小于第六条记录的所有员工信息(尽量让虚表尽量小)
select * from (select rownum r,t.* from emp t where rownum<=6) t2 where r>=4;
--需求：要分页查询，每页3条记录，查询第二页
/*
pageSize=3,pnum=2
beginRownum=(pnum-1)*pageSize+1;--4
endRownum=pageSize*pnum;--6
*/
--写Oracle的分页，从子查询写起，也就是说从小于等于写起，或者说从endRownum写起
--select rownum r,t.* from emp t where rownum<=endRownum;
select rownum,t.* from emp t where rownum<=6;--使虚表尽量小
--将上面查询出来带有行号的表作为虚表查询,这时rownum代表的列就代表真实的列了
select * from (select rownum r,t.* from emp t where rownum<=6) t2 where t2.r>=4;
--按照薪资的高低排序再分页
--1. 先排序
select * from emp order by sal desc;
--2.将排序后的表作为一张虚表,加上虚拟行号,将查询出来的表再作为虚表
select rownum r,t1.* from (select * from emp order by sal desc) t1 where rownum<=6;
--3.最后的分页
select * from (select rownum r,t1.* from (select * from emp order by sal desc) t1 where rownum<=6)
where r>=4;
--通用
SELECT * FROM
(
SELECT ROWNUM r,t.* FROM
(SELECT ename,job,sal FROM emp ORDER BY sal DESC) t
WHERE ROWNUM <=endRownum ORDER BY sal DESC
)
WHERE r >=firstRownum ;
8.2 rowid 记录编号
ROWID（记录编号）：是表的伪列，是用来唯一标识表中的一条记录，并且间接给出了表行的物理位置，定位表行最快的方式。
使用insert语句插入数据时，oracle会自动生成rowid并将其值与表数据一起存放到表行中。
--需求：删除表中的重复数据，要求保留重复记录中最早插入的那条。
--根据姓名分类,找出相同分类中最小的rowid
select min(rowid) from test01 group by name;
--删除除了同一分类中除最小rowid的其他记录
delete from test01 where rowid not in(select min(rowid) from test01 group by name);
删除重复记录一定要小心,如果条件有问题,就会删错数据.删除之前，先用查询查一下，看是否是目标数据。
rowid与rownum不同,它是在数据插入表格中时一起存入到表格中,只是我们平时不可见,因此在查询时,不需要将它跟表格的其他数据查询出来作为虚表,直接使用即可.而rownum只是查询时才生成的,因此利用它分页时必须将rownum与其他查询出来的数据作为虚表进行进一步的查询
9. 增删改
9.1 Insert
单条插入：
insert into table values(...);
--批量插入语法(主要用于将一张表中的数据批量插入到另外一张表中)
insert into test01 select empno,ename from emp;
--拷贝表
create table test02 as select * from emp;
--拷贝表结构
create table test01 as select * from emp where 1=2;
9.2 Delete和truncate区别
- delete逐条删除，truncate先摧毁表，再重建 。
- 最根本的区别是：delete是DML（可以回滚,还能闪回），truncate是DDL（不可以回滚）
- delete不会释放空间，truncate会（当确定一张表的数据不再使用，应该使用truncate）
- delete会产生碎片，truncate不会。
Hwm-高水位
高水位线英文全称为high water mark，简称HWM，那什么是高水位呢 ？
在Oracle数据的存储中，可以把存储空间想象为一个水库，数据想象为水库中的水。
水库中的水的位置有一条线叫做水位线，在Oracle中，这条线被称为高水位线（High-warter mark, HWM）。
在数据库表刚建立的时候，由于没有任何数据，所以这个时候水位线是空的，也就是说HWM为最低值。
当插入了数据以后，高水位线就会上涨，但是这里也有一个特性，就是如果你采用delete语句删除数据的话，数据虽然被删除了，
但是高水位线却没有降低，还是你刚才删除数据以前那么高的水位。也就是说，这条高水位线在日常的增删操作中只会上涨，不会下跌。
高水位对查询有巨大的影响。而且还浪费空间。
如何解决高水位带来的查询效率问题?
- 1．将表数据备份出来，摧毁表再重建（truncate table），然后再将数据导回来。
- 收缩表，整理碎片，可使用变更表的语句：alter table 表名 move
查看、测试、消除高水位—了解
--之前查看rowid
SELECT t.*,ROWID FROM TEST t;
--对表进行分析，收集统计信息（执行了收集信息的动作，user_tables表的块字段才有数据）
analyze table TEST compute statistics;
--查询表数据的块信息，其中blocks是高水位，empty_blocks是预申请的块空间。
select table_name,blocks,empty_blocks from user_tables where table_name='TEST';
--收缩表(整理碎片)，降低高水位，消除行移植和行链接，不释放申请的空间
ALTER TABLE TEST MOVE;
--对表进行分析，收集统计信息（执行了收集信息的动作，user_tables表的块字段才有数据）
analyze table TEST compute statistics;
--查询表数据的块信息，其中blocks是高水位，empty_blocks是预申请的块空间。
select table_name,blocks,empty_blocks from user_tables where table_name='TEST';
--之后查看rowid
SELECT t.*,ROWID FROM TEST t;
- 收缩表之后，高水位线下降了。
- 收缩表之后，rowid发生了变化。
img05.png
注意：
- move最好是在空闲时做，记得move的是会产生锁的(如果你move的时候需要很长事件，那么别人是不能操作这张表的。排他锁)
- move以后记得重建index（索引存放的其实就是数据的地址信息。当数据的地址变动了，索引也会失效。）语法：ALTER INDEX 索引名字 REBUILD;
10. 对表的操作
--增加字段
alter table test01 add address varchar2(60);
--删除字段
alter table test01 drop column address;
--修改字段type
alter table test01 modify name varchar2(100);
--修改字段名
alter table test01 rename column name to ename;
--修改表名(执行RENAME语句改变表, 视图, 序列, 或同义词的名称。)
rename test01 to test1;
--删除表
drop table test02;
11. 序列-sequence
Mysql中主键有自增长的特性.
Oracle中,主键没有自增长这个特性.
使用序列高效的生成主键值。
img06.png
11.1 创建序列
在ORACLE中为序列提供了两个伪列：
1，NEXTVAL 获取序列对象的下一个值（指针向前移动一个，并且获取到当前的值。）
2，CURRVAL 获取序列对象当前的值
--创建一个简单的序列
create sequence seq_test;
--使用序列,取出当前序列,指针跳到下一个
select seq_test.nextval from dual;
--取出当前序列,指针不变动
select seq_test.currval from dual;
11.2 序列的应用
在插入数据的时候插入序列主键.
insert into test1 values(seq_test.nextval,'tomcat'||seq_test.nextval);
11.3 序列的裂缝
- 序列是一个共有对象，多个表都可以调用。
- 当插入记录时报错，序列对象值也被使用，下一次再使用时，序列的值就会+1
也就是说，用序列插入数据库的值不一定是连续的。
序列出现裂缝的条件：
- 事务回滚。
- 系统异常。
- 多个表同时使用同一个序列。
12. 表空间tablespace
常见表空间的分类：
- (永久)数据表空间,主要用来永久存储正式的数据文件。
- 临时数据表空间,主要用来存储临时数据的，比如数据的排序、分组等产生的临时数据，不能存放永久性对象。
- UNDO表空间，保存数据修改前的镜象
临时表空间和UNDO表空间的异同：
相同之处：两者都不会永久保存数据。
不同之处：
UNDO表空间用于存放UNDO数据,当执行DML操作时,oracle会将这些操作的旧数据写入到UNDO段,以保证可以回滚和事务隔离读取等，主要用于数据的修改等；
而临时表空间主要用来做查询和存放一些缓冲区数据。
12.1 创建表空间
表空间的创建一般是由DBA来操作完成的，而且需要管理员权限
三种表空间中，UNDO表空间通常是由Oracle自动化管理的，而另外两种表空间则一般需要手动创建。
永久表空间语法：
create tablespace tablespace_name
[datafile datafile1，[datafile 2]…]
[logging | nologging]
[online|offline]
[extent_management_clause]
参数：
* tablespace_name：表空间名称随意，但最好遵循一定的规范，如tbl_itdream_dat、tbl_itdream_tmp等。
* datafile : 表空间的类型
* datafile1 : 数据文件需要有如下格式：文件名 SIZE 初始文件大小 [autoextend off| on] [maxsize|next size maxSize size]
* 文件名是数据文件的路径名，可以是绝对路径，也可以是相对路径，如“路径\xxx.dbf”，注意路径必须先建立好。
* 初始化文件大小，是数据文件刚建立起来的时候所占物理磁盘空间的大小
* autoextend，是否自动扩展数据文件的大小，OFF表示关闭自动扩展，数据文件只能是初始大小，ON表示开启自动扩展，当数据文件超过初始大小的时候，会自动增大。默认值为OFF。
* 如果设置自动扩展，则需要设置最大值MAXSIZE，如设置2000m，当然也可以设置为UNLIMITED，表示无限表空间。
如果要指定每次扩展的大小，可以使用NEXT SIZE MAXSIZE SIZE语法，表示每次扩展多少尺寸，最大能扩展到多大（大小上限）。
* [logging | nologging]该子句用来声明这个表空间上所有的用户对象的日志属性，默认为logging
* [online|offline]表空间的状态，online表示表空间创建后立即有效，offline表示表空间创建后暂时无效，即不能使用，只有设置为online后才有效，默认值为online。
* extent_management_clause表空间如何管理范围，推荐设置为本地管理，值为extent management local.生产环境推荐本地管理。
临时表空间语法：
--创建临时数据表空间
create temporary tablespace tablespace_name
tempfile datafile1，[datafile 2]…
extent_management_clause
临时数据表空间的数据文件一般不需要指定最大值，Oracle对其采用了贪吃算法策略，因此，该表空间会自动逐渐增大。
其他参数见永久表空间。
临时表空间默认是不记日志的
创建表空间：
最简语法：
--永久数据表空间和临时数据表空间的建立。
create tablespace tbl_test1_dat
datafile
'c:/tbl_test1_dat01.dbf' size 100M
autoextend on next 5M maxsize 2000M
extent management local;
--创建临时数据表空间。
create temporary tablespace tbl_test2_tmp
tempfile
'c:/tbl_test1_tmp01.dbf' size 20M
extent management local;
img07.png
注意：实际开发中，不要用最简化的方式来进行表空间的创建。
参考1:
--创建数据表空间
CREATE TABLESPACE TBS_CSP_BS_DAT
DATAFILE '/dev/rlv_dat001' SIZE 2000M REUSE AUTOEXTEND OFF,
'/dev/rlv_dat002' SIZE 2000M REUSE AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL;
--创建临时数据表空间
CREATE TEMPORARY TABLESPACE TBS_CSP_BS_TMP
TEMPFILE '/dev/rlv_dat009' SIZE 2000M REUSE AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
注：PERMANENT是显式的指定创建的是永久的表空间，用来存放永久对象。默认值。
参考2：
--创建数据表空间
create tablespace tbs_user_data
logging
datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
--创建临时数据表空间
create temporary tbs_user_temp
tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
12.2 删除表空间
drop tablespace 表空间名 [including contents and datafile]
----删除表空间以及下面所有数据和数据文件（全删，寸草不生）
drop tablespace tbl_test2_tmp including contents and datafiles;
如果不加后面的including...，则只是将表空间进行了逻辑删除（Oracle无法管理使用这个表空间了，但数据文件还存在）。
13. 用户与权限
SYS 帐户(数据库拥有者):
拥有 DBA 角色权限
拥有 ADMIN OPTION 的所有权限
拥有 startup, shutdown, 以及若干维护命令
拥有数据字典
system账户：
拥有 DBA 角色权限.
Sys和system账户的区别：
sys用户是数据库的拥有者，是系统内置的、权限最大的超级管理员帐号。
system用户只是拥有DBA角色权限的一个管理员帐号，其实它还是归属于普通用户。
13.1 创建用户(在dba权限下)
＊ 创建用户的语句
create user 用户名
identified by 密码(不要加引号)
default tablespace 默认表空间名 quota 5M on 默认表空间名
[temporary tablespace 临时表空间名]
[profile 配置文件名] //配置文件
[default role 角色名] //默认角色
[password expire] //密码失效
//如果设置失效，那么第一次登录的时候，会提醒你更改密码。
[account lock] //账号锁定（停用）
* 修改用户
alter user 用户名 identified by 密码 quota 10M on 表空间名
alter user 用户名 account lock/unlock
* 删除用户
drop user 用户名 [cascade].如果要删除的用户中有模式对象，必须使用cascade.
img08.png
一般企业开发中，建表要手动指定表空间，可以让不同模块、不同功能的对象存储在不同的数据文件中，可以提高性能。
13.2 删除用户
--删除用户及其下面所有的对象
drop user itcasttest cascade;
每个数据库用户帐户具备：
一个唯一的用户名
一个验证方法
一个默认的表空间
一个临时表空间
权限和角色(dba用户授权)
(每个表空间的配额.)
13.3 配置角色和权限
此时,用户jack虽然创建,但没有任何权限,连登陆都不能成功.因此需要使用dba用户授予jack用户权限。
Oracle内置有大量的权限,其中常见的权限有：
img09.png
但是一个个的添加权限太过于麻烦,通过Oracle的预定义内置角色就可以实现将普通用户的权限赋予给一个用户。
img10.png
普通用户就选择：connect和Resource角色。
管理员用户选择：connect和Resource、dba角色。
--给jack用户授予普通用户的权限
grant connect,resource to jack;
-------------------------------------------------------------------------
建立一个普通用户的过程：
1. create user ...创建用户(指定表空间)
2. 赋予权限(connect,resource)
13.4 操作jack用户
--创建序列sequence成功
create sequence seq_test01; --序列名，其他使用默认值
select seq_test01.nextval from dual;
--创建表空间tablespace失败
create tablespace tbs_jack_dat --表空间名
datafile --表空间类型
'c:/tbs_jack_dat01.dbf' size 50M --指定表空间数据文件的存储位置及大小
autoextend on --开启表空间文件自动增长
next 5M maxsize 1000M --每次增长5M，最大1000M
extent management local; --本地管理表空间
jack用户创建表空间失败,原因是表空间创建的权限不够,表空间默认有dba权限的用户才能创建。
13.5 对象权限
Oracle用户的权限分为两种：
系统权限(System Privilege): 允许用户执行对于数据库的特定行为，例如：创建表、创建用户等
对象权限(Object Privilege): 允许用户访问和操作一个特定的对象，例如：对其他方案下的表的查询
实现跨域访问：
跨域访问也称之为跨用户访问、跨方案访问，访问的方式为：用户名.对象名，
如在itcast用户下访问scott用户下的emp表的数据：
Select * from scott.emp;
--访问失败。原因：没有对象访问权限。
登陆scott用户,授予emp表的查询权限给jack用户
--scott用户授予emp表的查阅权限给jack
grant select on emp to jack;
--跨域访问成功。
select * from scott.emp;
注意：
赋权的时候，只能是自己拥有的权限、或者该权限是可以传递的，才可以将其赋予别人。
14. 视图View
需求：jack用户现在只需要查询10部门的员工数据就行了，scott也不想将所有数据都开放给jack用户。
14.1 视图的概念
概念：
- 视图是一种虚表.
- 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
- 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
- 视图向用户提供基表数据的另一种表现形式
作用：
- 限制数据访问
- 简化复杂查询(将比较复杂并且经常要查询的数据封装成视图保存起来,以后只需要查这张表即可)
- 提供数据的相互独立
- 同样的数据,可以有不同的显示方式
- 视图不能提高性能
14.2 语法
img11.png
视图只能创建、替换和删除，不能修改。
14.3 视图的操作
创建视图：
尝试在jack用户创建视图：(失败)
--复制scott用户的emp表
create or replace table emp as select * from scott.emp;
--在jack用户创建视图View
create view v_jack_emp --视图的名称
as select empno 编号,ename 姓名 from emp --
with read only;
创建jack用户的视图失败,原因：权限不够,说明视图的创建权限也需要dba权限.
登陆system用户,赋予jack用户dba权限.
--赋予jack用户dba权限
grant dba to jack;
--jack用户再次创建视图View
create or replace View v_jack_emp --视图的名称
as select empno 编号,ename 姓名 from emp where deptno=10 --视图显示内容
with read only;
创建成功。通过视图查询。
create or replace 创建或替换
img12.png
删除视图：
--删除视图v_jack_emp
drop view v_jack_emp;
视图默认是可以修改的,但我们一般只用视图来提供查询功能,因此设置它为只读,with read only
14.4 视图的跨域访问
需求：在scott用户下访问jack用户的视图v_jack_emp
视图和表类似,如果直接在scott用户访问该视图肯定是没有权限的,因此jack用户要赋予scott查询该视图的权限。
--因为刚刚删除了视图,这里再次创建
create or replace view v_jack_emp --创建或替换视图v_jack_emp
as select empno 编号,ename 姓名 from emp where deptno=10 --视图的内容
with read only; --视图只读
--jack用户赋予scott用户视图查询权限
grant select on v_jack_emp to scott;
--登陆scott用户执行查询
select * from jack.v_jack_emp;
这样就能控制让其他用户只能访问我想让他看到的内容,不给它查询表的权限,只给他查询视图的权利。
实际上，我们查询的表可能不是真正的表，而是视图，而且还是只读的。
视图还可以屏蔽筛选/修改不同字段、字段名称等(别名)，因此，你看到的时候的字段也未必是真实表中存在的！
14.5 视图小结
视图和表的区别：
视图是实体表的映射，视图和实体表区别就是于视图中没有真实的数据存在。
什么时候使用视图：
1，在开发中，有一些表结构是不希望过多的人去接触，就把实体表映射为一个视图。
2，在项目过程中，程序人员主要关注编码的性能、业务分析这方面。对于一些复杂的SQL语句，提前把这些语句封装到一个视图中，供程序人员去调用
注意：查询的对象（表）他可能不是一张的表，可能是视图；你看到的视图的字段可能也不是真实的字段。
15. 同义词SYNONYM
需求：想伪装一下这个视图的名字不被其他人知道，或者是嫌调用的这个对象名字太长，怎么办？
15.1 同义词的概念
同义词就是（对象的）别名，可以对表、视图等对象起个别名，然后通过别名就可以访问原来的对象了。
作用：
- 方便访问其它用户的对象
- 缩短对象名字的长度
15.2 语法
create [public] synonym for Object; --Object指Oracle对象
15.3 操作同义词
创建同义词：
首先,我创建一个tom用户,检测同义词synonym的创建权限
--创建tom用户
create user tom --用户名称
identified by orcl --密码
default tablespace tbs_itdream_dat --默认表空间
temporary tablespace tbs_itdream_tmp; --临时表空间
--授权
grant connect,resource to tom;
--创建tom01表格
create table tom01(id number);
insert into tom01 values(1);
select * from tom01;
--为tom01表格创建同义词
create synonym t1 for tom01;
创建失败,权限不够.说明同义词synonym的创建不够权限,
下面删除tom用户,继续使用jack用户进行操作.
drop user tom cascade; //必须有dba权限的用户才能删除
------------------------------------------------------------------------
在拥有dba权限的jack用户中为v_jack_emp视图创建同义词synonym：
--为视图v_jack_emp创建同义词
create synonym e for v_jack_emp;
--jack通过同义词查询视图
select * from e;
--scott用户通过同义词跨域访问视图
select * from jack.e;
select * from jack.v_jack_emp;
一个小失误：
不小心,将dba权限给删除了,重装了一次oracle,重建jack用户
--创建默认表空间
create tablespace tbs_jack_dat --创建永久表空间tbs_jack_dat，
datafile --表空间的类型
'c:/tbs_jack_dat01.dbf' size 100M --指定表空间物理文件的存储位置及默认大小
autoextend on --开启自动增容
next 5M maxsize 2000M --自动增容一次5M,最大2G
extent management local;--本地管理表空间
--创建临时表空间
create temporary tablespace tbs_jack_tmp --创建临时表空间
tempfile --表空间类型为临时表空间
'c:/tbs_jack_tmp01.dbf' size 5M --设置临时表空间物理文件的储存位置及默认大小,临时表空间无需设置自动增长,有默认的贪吃模式
extent management local; --本地管理表空间
--创建jack用户
create user jack --用户名
identified by jack --密码
default tablespace tbs_jack_dat --默认表空间
temporary tablespace tbs_jack_tmp --默认临时表空间;
--授予权限给jack用户
grant dba,connect,resource to jack;
--建表
create table emp as select * from scott.emp;
--------------------------------------------------
--创建视图View
create or replace view v_jack_emp --创建视图v_jack_emp
as select empno 编号,ename 姓名 from emp --视图的内容
with read only; --设置视图只读
--给视图创建同义词
create synonym e for v_jack_emp;
--同义词查询测试(成功)
select * from e;
-----------------------------------------------------
--跨域查询同义词,不授权视图,只授权同义词查询,测试是否能够成功在scott用户下查询
--授权同义词给scott
grant select on e to scott;
--切换scott用户跨域查询jack用户的同义词e代表的视图v_jack_emp
select * from jack.e;--成功
select * from jack.v_jack_emp;--成功
结论：
授权视图查询给scott用户,在scott用户下通过视图或同义词查询都可以。
授权同义词查询给scott用户,同样可以。
16. 索引-index
16.1 索引的概念特性和作用
数据库中的索引相当于字典的目录(索引)),它的作用就是提升查询效率。
特性：
- 一种独立于表的模式（数据库）对象, 可以存储在与表不同的磁盘或表空间中。
- 索引被删除或损坏, 不会对表（数据）产生影响, 其影响的只是查询的速度。
- 索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引. 用户不用在查询语句中指定使用哪个索引。
- 在删除一个表时, 所有基于该表的索引会自动被删除。
- 如果建立索引的时候,没有指定表空间,那么默认索引会存储在哪个表空间.会存储在所属用户默认的表空间.
作用：
- 通过指针（地址）加速Oracle 服务器的查询速度。
- 提升服务器的i/o性能（减少了查询的次数)
16.2 索引的工作原理
img13.png
16.3 操作索引
创建索引：
索引有两种创建方式：
- 自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引。
- 手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询。
手动创建索引：
--scott用户在emp表上的deptno上创建索引,
create index idx_scott_deptno on emp(deptno);
建立索引成功,说明索引的创建普通用户的权限既可以完成。
上面索引的创建有缺陷。
--缺点：没有指定表空间，生产环境下一般要将索引单独指定表空间。
create index idx_emp_ename on EMP (ename) tablespace USERS;
删除索引：
--删除idx_scott_deptno索引
drop index idx_scott_deptno;
16.4 索引的创建场景
索引不是万能！
以下情况可以创建索引:
- 列中数据值分布范围很广.即根据这个索引分类,可以分很多类。
- 表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%
以下情况不要创建索引：
- 表比较小
- 列不经常作为连接条件或出现在WHERE子句中
- 查询的数据大于2%到4%
- 表经常频繁更新（看需求，如果表经常不断的再更新，Oracle会频繁的重新改动索引，反而降低了数据库性能。但如系统日志历史表，就必须增加索引，效率超高）
一些关于索引的问题：
1. 索引的作用是什么？
主要是提高查询效率,减少磁盘的读写，从而提高数据库性能。
2. 创建索引一定能提高查询速度么？
未必！得看你创建的索引的合理性和语句的编写
3. 索引创建的越多越好么？
不是！索引也是需要占用存储空间的，过多的索引不但不会加速查询速度，反而还会降低效率。
17. PL/SQL编程
17.1 概念和目的
PLSQL(Procedure Language/SQL)是Oracle对sql语言的过程化扩展。
PLSQL在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。（具有编程的能力）。
17.2 PLSQL的入门Hello World
declare --用于声明变量
begin --业务逻辑
--输出Hello World！
dbms_output.put_line('Hello World!');
end;
----------------------------------------------------------------------------
--面向过程的语言
--declare --声明部分：没有变量，则declare可以省略
--不需要变量声明，则不需要写任何东西
BEGIN--程序体的开始：编写语句逻辑
--在控制台输出一句话：dbms_output相当于system.out类,内置程序包，put_line:相当于println()方法
dbms_output.put_line('Hello World');
--dbms_output.put('Hello World');
end;--程序体的结束
17.3 程序结构
PL/SQL可以分为三个部分：声明部分、可执行部分、异常处理部分。
[delare]
声明部分（变量、游标、例外）
begin
逻辑执行部分（DML语句、赋值、循环、条件等）
[exception]
异常处理部分（when 预定义异常错误 then）
end;
/
注意：在SQLPLUS中，PLSQL执行时，要在最后加上一个 “/”
17.4 变量声明
declare声明部分可以定义变量，定义变量的语法：
变量名 [CONSTANT] 数据类型;
* 普通数据类型（char, varchar2, date, number, boolean, long）：
* id number;
* name varchar(20);
* sex char(1);
* birthday date;
* married boolean := true; 直接赋值
* salary number(7,2); 总共7位数,5位整数,2位小数
* 特殊变量类型(引用型变量,记录型变量)：
* username emp.ename%type; --引用型变量,即username的变量名和emp表的ename类型一样
* emp_rec emp%rowtype; --记录型变量,即一次可以存储一行数据
17.5 赋值
普通变量赋值:
在ORACLE中有两种赋值方式：
1，直接赋值语句 :=
2, 使用select …into … 赋值：（语法;select 值 into 变量）
-----------------------------------------------------------------------------
declare --变量声明
v_id number;
v_name varchar(20) := 'jack'; --直接赋值
v_address varchar(60);
v_salary number;
begin --逻辑代码
--方法一：直接赋值
v_id := 1;
--方法二：语句赋值
select '深圳' into v_address from dual;
select sal into v_salary from emp where ename='KING';
--打印
dbms_output.put_line('v_id:'||v_id||',姓名:'||v_name||',地址:'||v_address||',薪资:'||v_salary);
end;
注意:
语句赋值,格式是select 字段 into 声明变量 from 表 where ...
引用变量：引用表中字段的类型 （推荐使用引用类型）
%type 例： v_ename emp.ename%type;
--查询并打印7839号（老大）员工的姓名和薪水
declare
--变量引用emp表的字段类型
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--语句赋值v_ename
select ename into v_ename from emp where empno=7839;
--语句赋值v_sal
select sal into v_sal from emp where empno=7839;
--打印
dbms_output.put_line('姓名:'||v_ename||',薪资:'||v_sal);
end;
------------------------------------------------------------------------
引用类型的好处：
- 使用普通变量定义方式，需要知道表中列的类型，而使用引用类型，不需要考虑列的类型
- 使用引用类型，当列中的数据类型发生改变，不需要修改变量的类型。而使用普通方式，当列的类型改变时，需要修改变量的类型
使用%TYPE是非常好的编程风格，因为它使得PL/SQL更加灵活，更加适应于对数据库定义的更新。
记录型变量:
记录型变量，代表一行，可以理解为数组，里面元素是每一字段值。
%rowtype 例： v_emp emp%rowtype;直接引用emp表所有的字段及类型
含义：v_emp 变量代表emp表中的一行数据的类型，它可以存储emp表中的任意一行数据。
---------------------------------------------------------------------------
--查询并打印7839号（老大）员工的姓名和薪水
declare
--引用类型变量
v_emp emp%rowtype;--该变量可以存储emp表中一行记录的值
begin
--变量语句赋值
select * into v_emp from emp where empno=7839;
--打印
dbms_output.put_line('姓名:'||v_emp.ename||',薪资:'||v_emp.sal);
end;
17.6 PLSQL编程—流程控制
条件结果 if
语法：
if 条件 then 结果
elsif 条件 then 结果
......
else 其他情况的结果
end if;
----------------------------------------------------------------------------
--判断emp表中记录是否超过20条，，10-20之间，10以下打印一句
declare
--声明一个变量,存储查询出来的记录数
v_count number;
begin
--获取emp表的记录数，给v_count赋值
select count(1) into v_count from emp;
--条件判断
if v_count<=10 then dbms_output.put_line('记录数小于10条');
elsif v_count>10 and v_count<=20 then dbms_output.put_line('记录数在10-20之间');
else dbms_output.put_line('记录数大于20条');
end if;
end;
循环Loop
img14.png
在ORACLE中有三种循环：
Loop 循环 EXIT WHEN...条件 end loop;
While()…loop 条件判断循环
For 变量 in 起始..终止 Loop
其中使用第一种,能完成其它两种循环,因此接下来就是用Loop循环操作。
--打印数字1-10
declare
--声明一个变量
v_num number := 1;
begin
--循环
loop
--输出
dbms_output.put_line(v_num);
--退出循环的条件
exit when v_num >= 10;
v_num := v_num+1;
end loop;
end;
18. 游标-Cursor
18.1 游标的概念
游标从概念上讲基于数据库的表返回结果集，也可以理解为游标就是个结果集，但该结果集是带向前移动的指针的，每次只指向一行数据。类似与JDBC操作时返回的ResultSet结果集。
游标的主要作用：
用于临时存储一个查询返回的多行数据（结果集），通过遍历游标，可以逐行访问处理该结果集的数据。
游标的使用方式：声明--->打开--->读取--->关闭
18.2 语法
1. 游标声明：
CURSOR 游标名 [ (参数名 数据类型[,参数名 数据类型]...)]
IS SELECT 语句;
无参游标：
cursor c_emp is select ename from emp;
有参游标：
cursor c_emp(v_deptno emp.deptno%TYPE) is select ename from emp where deptno=v_deptno;
2. 开启游标. 会执行查询获得结果集。
Open 游标名(参数列表)
例：open c_emp; --开启游标,执行查询
3. 读取游标
fetch 游标名 into 变量列表|记录型变量
例：fetch c_emp into v_ename;--取一行游标的值到变量中。注意：v_ename必须与emp表中的ename列类型一致。(v_ename emp.ename%type;)
4. 关闭游标释放资源
例：close 游标名
close c_emp;--关闭游标释放资源
18.3 游标的属性
img15.png
游标的原理：
游标刚开启时,游标在结果集的第一条记录之前。
当fetch取值时,指针会往前游动,并获取游动后的后处于游标位置的值。(游标是有位置的)
注意:游动不能回头。
因此可以使用,%notfound属性来判断游标是否遍历完毕。
img17.png
18.4 操作无参游标
需求：使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。
引用型变量获取游标的值：
declare
--声明一个游标
cursor c_emp_test1
is select ename,sal from emp;
--声明两个变量用于接收遍历出来的数据
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--开启游标,执行查询
open c_emp_test1;
--使用游标,循环取值
loop
--获取值,存入临时变量时,要保证数量和类型一致.每次取出值后,指针往下移动一次
fetch c_emp_test1 into v_ename,v_sal;
--判断当前游标位置是否有值,如果没有就退出循环
exit when c_emp_test1%notfound;
--输出
dbms_output.put_line('姓名:'||v_ename||',薪资:'||v_sal);
end loop;
--关闭游标,释放资源
close c_emp_test1;
end;
PLSQL程序运行结果：
img16.png
使用记录型变量存值：
declare
--声明游标
cursor c_emp is select * from emp;
--记录型变量
v_emp emp%rowtype;--可用于存储一行记录的值
begin
--开启游标,执行查询
open c_emp;
--使用游标,循环取值
loop
--获取游标的值放入变量的时候，必须要into前后要对应（数量和类型)
fetch c_emp into v_emp;
--推出循环的条件
exit when c_emp%notfound;
--打印
dbms_output.put_line('姓名:'||v_emp.ename||',薪资:'||v_emp.sal);
end loop;
--关闭游标
close c_emp;
end;
18.5 带参游标
需求：使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时手动输入。
使用引用型变量取游标的值：
declare
--声明游标--带参数的游标:需要定一个形式参数
cursor c_emp_dept(v_deptno emp.deptno%type)
is select ename,sal from emp where deptno=v_deptno;
--声明变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--开启游标,执行查询--传入参数:部门编号
open c_emp_dept(10);
--使用游标循环取出游标中的值
Loop
--取值,存入临时变量(保证into后的数量与类型与游标的select一致)
fetch c_emp_dept into v_ename,v_sal;
--退出循环的条件：如果当前游标位置没有值,就退出循环
exit when c_emp_dept%notfound;
--输出
dbms_output.put_line('姓名:'||v_ename||',薪资:'||v_sal);
end Loop;
--关闭游标释放资源
close c_emp_dept;
end;
使用记录型变量取游标中的值：
declare
--声明一个带参的游标
cursor c_emp_dept2(v_deptno emp.deptno%type)
is select * from emp where deptno=v_deptno;
--记录型变量
v_emp emp%rowtype;
begin
--开启游标,执行查询获得结果集
open c_emp_dept2(20);--传入参数：部门编号20
--使用游标，循环取值
Loop
--取值(保证数量和类型一致,因此游标使用的*)
fetch c_emp_dept2 into v_emp;
--跳出循环(指针所处游标位置没有值时跳出循环)
exit when c_emp_dept2%notfound;
--打印
dbms_output.put_line(v_emp.ename||'>>>'||v_emp.sal);
end Loop;
--关闭游标,释放资源
close c_emp_dept2;
end;
注意：
- Found是游标有数据的判断。如果游标刚打开，值false
- Notfound是游标结束的判断，如果游标刚打开，值false，只判断游标是否结束！
19.存储过程
19.1 概念与作用
存储过程：就是一块PLSQL语句包装起来，起个名称
相对而言:单纯plsql可以认为是匿名程序。
- plsql是存储过程的基础。
- java是不能直接调用plsql的，但可以通过存储过程这些对象来调用。
19.2 语法
create or replace procedure 过程名(参数列表)
as/is
--声明
begin
PLSQL子程序体,完成逻辑操作
end 程序名;
-----------------------------------------------------------------------------
As和is是通用的。
根据参数的类型，我们将其分为3类：
1. 不带参数的存储过程
2. 带输入参数的存储过程
3. 带输入参数与输出参数的存储过程
19.3 无参的存储过程
create or replace procedure sayHelloWorld --没有参数的情况下，不要加()
as
--声明变量,不写不可省略
begin
dbms_output.put_line('Hello World!');
end sayHelloWorld;
存储过程的测试,直接在Procedure对象右键选择Test即可。
19.4 存储过程的调用方法
如何调用执行，两种方法：
- 用exec（execute）命令来调用—用来测试存储
- 用其他的程序（plsql和java）来调用
命令调用的方式:
img21.png
程序调用：
img22.png
19.5 带输入参数的存储过程
可以直接在PLSQL工具右键新建Procedure程序,工具帮我们自动写好一些代码。
--查询并打印某个员工（如7839号员工）的姓名和薪水--存储过程：要求，调用的时候传入
--创建一个带输入参数的存储过程，in代表这是个输入参数,out代表是输出参数
create or replace procedure p_queryempsal(i_empno in emp.empno%type)
is
--声明变量, 接收结果
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--逻辑代码
select ename,sal into v_ename,v_sal from emp where empno=i_empno;
--输出打印
dbms_output.put_line('姓名:'||v_ename||',薪资是:'||v_sal);
end p_queryempsal;
命令调用：
img23.png
程序调用:
img24.png
19.6 带输入in和输出参数out—主要是其他程序调用
--输入员工号查询某个员工（7839号员工）信息，要求，将薪水作为返回值输出，给调用的程序使用。
create or replace procedure p_queryempsal_out(i_empno in emp.empno%type,
o_sal out emp.sal%type)
is
begin
--执行查询
select sal into o_sal from emp where empno=i_empno;
end p_queryempsal_out;
--------------------------------------------------------------------------
调用（使用plsql程序调用）：
--使用plsql程序调用p_queryempsal_out存储过程
declare
--输入参数值
v_empno emp.empno%type := 7839;
--声明一个参数来接收输出参数
v_sal emp.sal%type;
begin
--调用Procedure存储过程
p_queryempsal_out(v_empno,v_sal);--第二个参数是输出参数,必须有变量来接受！
--打印v_sal
dbms_output.put_line('员工编号:'||v_empno||',薪资是:'||v_sal);
end;
注意:调用的时候,参数要与定义的参数的顺序和类型一致.
----------------------------------------------------------------------------
也可以直接Test测试。
img25.png
存储过程总结：
存储过程的作用：主要用来执行一段程序。
- 在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭(连接和关闭是很耗费资源)。这种就需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率.
- ORACLE官方给的建议：能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误，在程序中操作可以会存在错误.(如果在数据库中操作数据,可以有一定的日志恢复等功能.)
三种存储基本应用场景：
- 无参参数：只用来做数据处理。存储内部写一些处理数据的逻辑。
- 带输入参数：数据处理时，可以针对输入参数的值来进行判断处理
- 带输入输出参数：一般用来传入一个参数值，我想经过数据库复杂逻辑处理后，得到我想要的值然后输出给我。
20.存储函数
语法：
create [or replace] function 函数名(参数列表)
return 函数值类型
as/is
--声明
begin
--程序体
--必须有return
return ...;
end;
20.1 存储函数案例
--查询某职工的总收入。
create or replace function queryempincome(f_empno in emp.empno%type) --创建一个存储函数,传递参数
return number --返回值类型
as
--声明一个变量查询出来的结果返回
v_income number;
begin
--查询赋值
select sal*12+nvl(comm,0) into v_income from emp where empno=f_empno;
--返回值
return v_income;
end;
PLSQL程序调用：
--测试queryempincome存储函数
declare
--声明一个变量接收返回的值
v_income number;
begin
--调用函数获得结果
v_income := queryempincome(7934);--传入参数
--打印
dbms_output.put_line('编号:'||7934||',总收入:'||v_income);
end;
Test程序测试：
img26.png
如何选择存储过程和存储函数？
原则上，如果只有一个返回值，用存储函数，否则，就用存储过程。
但是，一般我们会直接选择使用存储过程，原因是：
* 函数是必须有返回值，存储过程可以有也可以没有，存储的更灵活！
* 存储过程也可以有输出参数,可以代替存储函数。
* Oracle的新版本中，已经不推荐使用存储函数了。
21. 例外
语法：
declare
--声明部分
begin
--逻辑部分
excepetion
--捕获例外
when 例外名 then ...
when 例外名 then...
when others then ...
end;
--例外
declare
i number;
--声明一个引用型变量
v_ename emp.ename%type;
--声明一个记录型变量
v_emp emp%rowtype;
begin
--i := 1/0; zero_divide
--i := 'abc'; value_error
--select ename into v_ename from emp where empno = 123; no_data_found
select * into v_emp from emp;
exception
when zero_divide then dbms_output.put_line('发生了除零例外');
when value_error then dbms_output.put_line('算术或转换例外');
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('发生了未知例外');
end;
21.1 自定义例外
在declare-begin中声明一个自定义例外：
例外名 exception;
----------------------------------------------------------------------------
--使用自定义例外
declare
--声明自定义例外
no_emp_found exception;
--声明游标
cursor c_emp is select * from emp;
v_emp emp%rowtype;
begin
--开启游标
open c_emp;
--因为要使用自定义游标,直接利用游标的属性抛异常
loop
fetch c_emp into v_emp;
if c_emp%notfound then
raise no_emp_found;
end if;
end loop;
exception
when no_emp_found then
dbms_output.put_line('抛出自定义例外');
when others then
dbms_output.put_line('抛出未知的例外');
--关闭游标
close c_emp;
end;
22. 使用java调用存储过程
写一个存储过程p_queryempsal_out.使用java代码调用它,并获取到返回的值打印出来。
PLSQL代码：
--需求：封装存储过程：传入某个员工的员工编号,输出该员工的年薪
create or replace procedure proc_getYearSal(i_empno in emp.empno%type,o_yearsal out emp.sal%type) --创建存储过程
as
--声明部分
begin
--获取年薪,赋值给输出变量
select sal*12+nvl(comm,0) into o_yearsal from emp where empno=i_empno;
end;
---------------------------------------------------------------------------
java代码调用过程：
使用JDBC连接数据库,传入员工编号,获取参数。
1. 加载数据库驱动
2. 创建Connection连接
3. 获取执行sql的CallableStatement对象
4. 设置参数
5. 执行sql,获取结果集
6. 关闭资源
首先导入Oracle.jdbc的驱动包：ojdbc6.jar
准备JDBCUtils工具类：(变量也可以提取到配置文件中)
public class JDBCUtils {
private static String driver = "oracle.jdbc.oracle.OracleDriver";
private static String url = "jdbc:oracle:thin:@192.168.175.10:1521:orcl";
private static String user = "jack";
private static String password = "jack";
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void release(Connection conn, Statement st, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs = null;// why? ---> Java GC
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
st = null;
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn = null;
}
}
}
}
----------------------------------------------------------------------------
java代码调用存储过程-输出参数返回的值是普通类型：
@Test
public void test() throws Exception {
// 获取连接对象
Connection connection = JDBCUtils.getConnection();
String sql = "{call proc_getYearSal(?,?)}"; // 转义sql,从API中查询格式即可
// 获取执行sql的对象CallableStatement
CallableStatement call = connection.prepareCall(sql);
// 设置参数,输入参数直接set,输出参数需要注册,执行完之后,call对象中调用get方法获取输出值
call.setInt(1, 7369);
// 注册输出参数.参数1：参数的位置,参数2：参数类型
call.registerOutParameter(2, OracleTypes.DOUBLE);
// 执行sql
call.execute();
//获取输出参数的值
double yearSal = call.getDouble(2);
System.out.println("编号7369的员工的年薪为:"+yearSal+"元");
//关闭资源
JDBCUtils.release(connection, call, null);
}
java代码调用存储过程-输出参数返回的值是结果集：
--需求：封装存储过程,获取emp表所有员工的编号,姓名,月薪
create or replace procedure proc_getempinfo(c_empinfo out sys_refcursor) --创建存储过程,输出参数是一个结果集,因此用游标封装(sys_refcursor系统引用游标,使用时才指定查询语句)
as
begin
--系统引用游标,开启时指定查询条件
open c_empinfo for select empno,ename,sal from emp;
end;
使用java代码调用该存储过程proc_getempinfo：
@Test
public void test2() throws Exception {
// 获取连接
Connection connection = JDBCUtils.getConnection();
String sql = "{call proc_getempinfo(?)}";// 转义sql
// 获取执行sql的对象
CallableStatement call = connection.prepareCall(sql);
// 设置参数:注册输出参数
call.registerOutParameter(1, OracleTypes.CURSOR);
// 执行sql
call.execute();
// 获取结果集.
//CallableStatement没有getCursor方法,找它的实现类对象
//System.out.println(call.getClass()); //oracle.jdbc.driver.T4CCallableStatement
//但是它的实现类权限是默认的而不是public的而不能使用,因此使用它的父类OracleCallableStatement中的getCursor方法
OracleCallableStatement call2 = (OracleCallableStatement)call;
ResultSet rs = call2.getCursor(1);
while(rs.next()) {
System.out.println("编号是:"+rs.getObject("empno"));
System.out.println("姓名是:"+rs.getObject("ename"));
System.out.println("月薪是:"+rs.getObject("sal"));
System.out.println("=================================");
}
//关闭资源
JDBCUtils.release(connection, call, rs);
}
- 数据库存储过程的编写,注意输出参数必须加上out
- 谁调用存储过程,得到游标结果集ResultSet由调用者负责关闭游标。
23. 触发器-Trigger
23.1 概念与作用
数据库触发器是一个与表相关联的、存储的PL/SQL程序。
每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的PLSQL语句序列。
换句话说：触发器就是在执行某个操作（增删改）的时候触发一个动作(一段程序)。
23.2 语法
创建触发器语法：
CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN(条件) ] ]
PLSQL 块(即：as begin end)
img27.png
23.3 触发器HelloWorld
测试：普通用户也可以创建触发器。
create or replace trigger tri_sayHello --创建触发器
before --在..操作之前执行触发器
insert --插入数据时触发
on emp --插入emp表时触发
declare
--声明部分
begin
--逻辑部分
dbms_output.put_line('Hello World!!!');
end;
23.3 触发器的类型
- 语句级触发器（表级触发器）
- 在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。
- 行级触发器（FOR EACH ROW）
- 触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量, 识别值的状态。
23.4 语句级触发器与行级触发器的区别
--语句级触发器：
create or replace trigger tri_yuju_test
before update
on emp
declare
begin
dbms_output.put_line('语句级触发器...');
end;
--行级触发器：
create or replace trigger tri_hangji_test
before update
on emp
for each row --定义行级触发器
declare
begin
dbms_output.put_line('行级触发器...');
end;
delete from emp where empno in(9527,9528);
select * from emp;
--测试：修改emp表所有员工的工资：
update emp set sal=10000;
img28.png
语句级触发器和行级触发器区别：
语法上：
1．行级触发器需要定义：for each row
表现上：
2．行级触发器，在每一行的数据进行操作的时候都会触发。但语句级触发器，对表的一个完整操作才会触发一次。
简单的说：行级触发器，是对应行操作的；语句级触发器，是对应表操作的。
上面的区别,是在一条sql语句控制改变表时才会发生。
如果，通过insert一条一条的向表中插入数据,它们就都会触发。
23.5.行级别触发器的伪记录变量
img29.png
行级触发器的强大之处：可以获取修改前后的值。
上面的表格很容易理解,只有update修改前后才都有值。
如果是insert插入,插入前是没有值的（null）.
如果是delete删除,删除后是没有值的(null).
需求：使用触发器,保证涨后的工资不能少于涨前的工资
分析：使用行级触发器,能够获取修改前后的值进行比较,如果涨后工资还低的话,抛出例外(异常)
create or replace trigger tri_addsal --创建触发器
before update on emp --定义触发条件
for each row --定义行级触发器
declare
begin
if :new.sal<=:old.sal then
raise_application_error(-20001,'涨后工资不能低于涨前的工资！！！');
end if;
end;
测试：
update emp set sal=sal-100;
img30.png
23.6. 触发器的应用场景及注意事项
触发器可应用于：
- 数据确认
- 实施复杂的安全性检查
- 做审计,跟踪过表上所做的数据操作等
- 数据的备份与同步
触发器的注意事项：
触发器会引起锁，降低效率！使用时要慎重。如无必要，尽量不要使用触发器
行级触发器会引发行级锁（锁行数据）
语句级触发器可能会引起表级锁（锁表）
存储过程练习：
- 写一个存储过程，输出久久乘法表
首先可以先写出java代码,然后根据java代码写出PLSQL代码
public void test3() {
for(int i=1;i<=9;i++) {
for(int j=1;j<=i;j++) {
System.out.print(j+"*"+i+"="+j*i+" ");
}
System.out.println();
}
}
九九乘法表的存储过程：
create or replace procedure proc_ninemul --创建一个无参的存储过程
is
--声明两个变量
i number := 1;
j number;
begin
--逻辑部分
loop
exit when i > 9;--退出外循环的条件
j := 1; --每次执行内循环之前,将j设为1
loop
exit when j>i;--退出内循环的条件
dbms_output.put(i||'*'||j||'='||i*j||' ');--内循环共输出一行
j := j+1;
end loop;
--每次执行完内循环换行
dbms_output.put_line('');
i := i+1;
end loop;
end proc_ninemul;
--调用
begin
proc_ninemul;
end；
img31.png
网友评论