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），然后再将数据导回来。
• 2. 收缩表，整理碎片，可使用变更表的语句：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. 序列是一个共有对象，多个表都可以调用。
2. 当插入记录时报错，序列对象值也被使用，下一次再使用时，序列的值就会+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

存储过程总结：

存储过程的作用：主要用来执行一段程序。

1. 在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭(连接和关闭是很耗费资源)。这种就需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率.
2. 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. 触发器的应用场景及注意事项

触发器可应用于：

• 数据确认
• 实施复杂的安全性检查
• 做审计,跟踪过表上所做的数据操作等
• 数据的备份与同步

触发器的注意事项：

触发器会引起锁，降低效率！使用时要慎重。如无必要，尽量不要使用触发器

行级触发器会引发行级锁（锁行数据）
语句级触发器可能会引起表级锁（锁表）

存储过程练习：

1. 写一个存储过程，输出久久乘法表

    首先可以先写出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