一、数据库分类
一、数据库分类
1、小型数据库：access、foxbase
2、中型数据库：informix、sql server、mysql
3、大型数据库：sybase、db2、oracle
二、项目中如何合理地使用数据库，可以依据如下三个方面入手
1、项目的规模
a、负载量有多大，即用户数有多大
b、成本
c、安全性
eg、
小型数据库
1、负载量小，用户大概100人之内
比如：留言板、信息管理系统
2、成本在千元之内
3、对安全性要求不高
中型数据库
比如负载量 日访问量5000~10000
成本在万元内
比如商务网站
大型数据库
海量负载，可以处理海量数据(sybase<oracle<db2(海量数据处理能力))
安全性高，相对贵
二、oracle sql*plus常用命令
一、sys用户和system用户
Oracle安装会自动的生成sys用户和system用户
(1)、sys用户是超级用户，具有最高权限，具有sysdba角色，有create database的权限，该用户默认的密码是change_on_install
(2)、system用户是管理操作员，权限也很大。具有sysoper角色，没有create database的权限，默认的密码是manager
(3)、一般讲，对数据库维护，使用system用户登录就可以拉
注意：也就是说sys和system这两个用户最大的区别是在于有没有create database的权限。
二、sql*plus工具简介
sql*plus是oracle自带的工具软件，主要用于执行sql语句，pl\sql块。
操作如下：
(1)、在D:\dev\oracle\product\10.2.0\db_1\bin\目录下的sqlplusw.exe。(D:\dev\oracle\为oracle安装目录)
(2)、在运行栏中输入“sqlplusw”即可
三、sqlplus dos工具简介
(1)、概述：sqlplus是在dos下操作oracle的工具，其功能和sql*plus相似。
(2)、操作如下：在运行栏中输入“sqlplus”即可
四、PLSQL Developer工具，需要自己安装，推荐大家使用
五、sql*plus常用命令
1)、连接命令
1.conn[ect]
用法：conn 用户名/密码@网络服务名 [as sysdba/sysoper]
说明：当用特权用户身份连接时，必须带上as sysdba或是as sysoper
eg、
SQL> show user
USER 为 "SCOTT"
SQL> conn system/oracle@orcl
已连接。
SQL> show user
USER 为 "SYSTEM"
SQL>
以上命令实现类似切换用户的效果
2.disc/disconn/disconnect
说明: 该命令用来断开与当前数据库的连接
3.pssw[ord]
说明: 该命令用于修改用户的密码，如果要想修改其它用户的密码，需要用sys/system登录。
eg、
SQL> conn scott/oracle
已连接。
SQL> passw
更改 SCOTT 的口令
旧口令:
新口令:
重新键入新口令:
口令已更改
SQL>
4.show user
说明: 显示当前用户名
5.exit
说明: 该命令会断开与数据库的连接，同时会退出sql*plus
5.clear screen
清空屏幕
2)、文件操作命令
1.start和@
说明: 运行sql脚本
案例: sql>@ d:\a.sql或是sql>start d:\a.sql
2.edit
说明: 该命令可以编辑指定的sql脚本
案例: sql>edit d:\a.sql,这样会把d:\a.sql 这个文件打开
3.spool
说明: 该命令可以将sql*plus屏幕上的内容输出到指定文件中去。
案例: sql>spool d:\b.sql并输入sql>spool off
eg、
sql>spool d:\b.sql;
sql>select * from emp;
sql>spool off;
3)、交互式命令
1.&
说明：可以替代变量，而该变量在执行时，需要用户输入。
select * from emp where job='&job'；
4)、显示和设置环境变量
概述：可以用来控制输出的各种格式，set show 如果希望永久的保存相关的设
置，可以去修改glogin.sql 脚本
1.linesize
说明：设置显示行的宽度，默认是80个字符
show linesize
set linesize 90
2.pagesize说明：设置每页显示的行数目，默认是14
用法和linesize 一样
至于其它环境参数的使用也是大同小异
三、oracle 用户管理一
一、创建用户
概述：在oracle中要创建一个新的用户使用create user语句，一般是具有dba(数据库管理员)的权限才能使用。
create user 用户名 identified by 密码;
注意：oracle有个毛病，密码必须以字母开头，如果以数字开头，它不会创建用户
eg、create user xiaoming identified by oracle;
二、给用户修改密码
概述：如果给自己修改密码可以直接使用
SQL> password 用户名或passw
如果给别人修改密码则需要具有dba的权限，或是拥有alter user的系统权限
SQL> alter user 用户名 identified by 新密码
三、删除用户
概述：一般以dba的身份去删除某个用户，如果用其它用户去删除用户则需要具有drop user的权限。
比如drop user 用户名 【cascade】
注意：在删除用户时，如果要删除的用户，已经创建了表，那么就需要在删除的时候带一个参数cascade，即把该用户及表一同删除;
四、权限
权限分为系统权限和对象权限。
何为系统权限？
用户对数据库的相关权限，connect、resource、dba等系统权限，如建库、建表、建索引、建存储过程、登陆数据库、修改密码等。
何为对象权限？
用户对其他用户的数据对象操作的权限，insert、delete、update、select、all等对象权限，数据对象有很多，比如表，索引，视图，触发器、存储过程、包等。
执行SELECT * FROM Dba_Object_Size;语句可得到oracle数据库对象。
五、角色
角色分为预定义角色和自定义角色。
六、用户管理的综合案例
概述：创建的新用户是没有任何权限的，甚至连登陆的数据库的权限都没有，需要为其指定相应的权限。给一个用户赋权限使用命令grant，回收权限使用命令revoke。
为了讲清楚用户的管理，这里我给大家举一个案例。
SQL> conn xiaoming/oracle
ERROR:
ORA-01045: user XIAOMING lacks CREATE SESSION privilege; logon denied
警告: 您不再连接到 ORACLE。
SQL> show user
USER 为 ""
SQL> conn system/oracle
已连接。
SQL> grant connect to xiaoming;
授权成功。
SQL> conn xiaoming/oracle
已连接。
SQL>
注意：grant connect to xiaoming;在这里，准确的讲，connect不是权限，而是角色。
现在说下对象权限，现在要做这么件事情：
* 希望xiaoming用户可以去查询emp表
* 希望xiaoming用户可以去查询scott的emp表
grant select on scott.emp to xiaoming
* 希望xiaoming用户可以去修改scott的emp表
grant update on scott.emp to xiaoming
* 希望xiaoming 用户可以去修改/删除，查询，添加scott的emp表
grant all on scott.emp to xiaoming
* scott希望收回xiaoming对emp表的查询权限
revoke select on scott.emp from xiaoming
七、权限的传递
//对权限的维护。
* 希望xiaoming用户可以去查询scott的emp表/还希望xiaoming可以把这个权限传递给别人。
--如果是对象权限，就加入with grant option
grant select on emp to xiaoming with grant option
我的操作过程：
SQL> conn scott/oracle;
已连接。
SQL> grant select on scott.emp to xiaoming with grant option;
授权成功。
SQL> conn system/oracle;
已连接。
SQL> create user xiaohong identified by oracle;
用户已创建。
SQL> grant connect to xiaohong;
授权成功。
SQL> conn xiaoming/oracle;
已连接。
SQL> grant select on scott.emp to xiaohong;
授权成功。
--如果是系统权限。
system给xiaoming权限时：grant connect to xiaoming with admin option
问题：如果scott把xiaoming对emp表的查询权限回收，那么xiaohong会怎样？
答案：被回收。
下面是我的操作过程：
SQL> conn scott/oracle;
已连接。
SQL> revoke select on emp from xiaoming;
撤销成功。
SQL> conn xiaohong/oracle;
已连接。
SQL> select * from scott.emp;
select * from scott.emp
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
结果显示：小红受到诛连了。。
八、with admin option与with grant option区别
1、with admin option用于系统权限授权，with grant option用于对象授权。
2、给一个用户授予系统权限带上with admin option时，此用户可把此系统权限授予其他用户或角色，但收回这个用户的系统权限时，这个用户已经授予其他用户或角色的此系统权限不会因传播无效，如授予A系统权限create session with admin option,然后A又把create session权限授予B,但管理员收回A的create session权限时，B依然拥有create session的权限，但管理员可以显式收回B create session的权限，即直接revoke create session from B.
而with grant option用于对象授权时，被授予的用户也可把此对象权限授予其他用户或角色，不同的是但管理员收回用with grant option授权的用户对象权限时，权限会因传播而失效，如grant select on table with grant option to A,A用户把此权限授予B，但管理员收回A的权限时，B的权限也会失效，但管理员不可以直接收回B的SELECT ON TABLE 权限。
四、oracle 用户管理二
一、使用profile管理用户口令
概述：profile是口令限制，资源限制的命令集合，当建立数据库时，oracle会自动建立名称为default的profile。当建立用户没有指定profile选项时，那么oracle就会将default分配给用户。
1.账户锁定
概述：指定该账户(用户)登陆时最多可以输入密码的次数，也可以指定用户锁定的时间(天)一般用dba的身份去执行该命令。
例子：指定scott这个用户最多只能尝试3次登陆，锁定时间为2天，让我们看看怎么实现。
创建profile文件
SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2;
SQL> alter user scott profile lock_account;
2.给账户(用户)解锁
SQL> alter user scott account unlock;
3.终止口令
为了让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要dba的身份来操作。
例子：给前面创建的用户test创建一个profile文件，要求该用户每隔10天要修改自己的登陆密码，宽限期为2天。看看怎么做。
SQL> create profile myprofile limit password_life_time 10 password_grace_time 2;
SQL> alter user test profile myprofile;
二、口令历史
概述：如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史，这样oracle就会将口令修改的信息存放到数据字典中，这样当用户修改密码时，oracle就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。
例子：
1）建立profile
SQL>create profile password_history limit password_life_time 10 password_grace_time 2
password_reuse_time 10 //password_reuse_time指定口令可重用时间即10天后就可以重用
2）分配给某个用户
SQL> alter user test profile password_history;
三、删除profile
概述：当不需要某个profile文件时，可以删除该文件。
SQL> drop profile password_history 【casade】
注意：文件删除后，用这个文件去约束的那些用户通通也都被释放了。。
加了casade，就会把级联的相关东西也给删除掉
五、oracle 表的管理
一、表名和列名的命名规则
1)、必须以字母开头
2)、长度不能超过30个字符
3)、不能使用oracle的保留字
4)、只能使用如下字符 a-z，a-z，0-9，$,#等
二、数据类型
1)、字符类
char 长度固定，最多容纳2000个字符。
例子：char(10) ‘小韩’前四个字符放‘小韩’，后添6个空格补全，如‘小韩 ’
varchar2(20) 长度可变，最多容纳4000个字符。
例子：varchar2（10） ‘小韩’ oracle分配四个字符。这样可以节省空间。
clob(character large object) 字符型大对象，最多容纳4g
char 查询的速度极快浪费空间，适合查询比较频繁的数据字段。
varchar 节省空间
2)、数字型
number范围-10的38次方到10的38次方，可以表示整数，也可以表示小数
number(5,2)表示一位小数有5位有效数，2位小数；范围：-999.99 到999.99
number(5)表示一个5位整数；范围99999到-99999
3)、日期类型
date 包含年月日和时分秒 oracle默认格式1-1月-1999
timestamp 这是oracle9i对date数据类型的扩展。可以精确到毫秒。
4)、图片
blob 二进制数据，可以存放图片/声音4g；一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放，一般存放图片、视频的路径，如果安全需要比较高的话，则放入数据库。
三、怎样创建表
--创建表
--学生表
create table student (
Id number(4) primary key,
xh number(4) , --学号
xm varchar2(20), --姓名
sex char(2), --性别
birthday date, --出生日期
sal number(7,2) --奖学金
);
--班级表
create table class(
classid number(2),
cname varchar2(40)
);
--修改表
--添加一个字段
sql>alter table student add (classid number(2));
--修改一个字段的长度
sql>alter table student modify (xm varchar2(30));
--修改字段的类型或是名字（不能有数据） 不建议做
sql>alter table student modify (xm char(30));
--删除一个字段 不建议做(删了之后，顺序就变了。加就没问题，应该是加在后面)
sql>alter table student drop column sal;
--修改表的名字 很少有这种需求
sql>rename student to stu;
--创建自增列
create sequence student
increment by 1 -- 每次加几个
start with 1 -- 从1开始计数
minvalue 1 --最小值为1
NOMAXvalue -- 不设置最大值 (maxvalue 99999999 等同于maxvalue 99999999 )
cache 10; --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，
nocache; --一直累加 不循环
一旦定义了S_Test，你就可以用currval，nextval
currval=返回 sequence的当前值
nextval=增加sequence的值，然后返回 sequence 值
比如：
S_Test.CURRVAL
S_Test.NEXTVAL
insert into student(id,xh, xm, sex) values (student.Nextval,'a003', 'john', '女');
--删除表
sql>drop table student;
--添加数据
--所有字段都插入数据
insert into student values ('a001', '张三', '男', '01-5 月-05', 10);
--oracle中默认的日期格式‘dd-mon-yy’ dd 天 mon 月份 yy 2位的年 ‘09-6 月-99’ 1999年6月9日
--修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改注册表）
alter session set nls_date_format ='yyyy-mm-dd';
--修改后，可以用我们熟悉的格式添加日期类型：
insert into student values ('a002', 'mike', '男', '1905-05-06', 10);
--插入部分字段
insert into student(xh, xm, sex) values ('a003', 'john', '女');
--插入空值
insert into student(xh, xm, sex, birthday) values ('a004', 'martin', '男', null);
--问题来了，如果你要查询student表里birthday为null的记录，怎么写sql呢？
--错误写法：select * from student where birthday = null;
--正确写法：select * from student where birthday is null;
--如果要查询birthday不为null,则应该这样写：
select * from student where birthday is not null;
--修改数据
--修改一个字段
update student set sex = '女' where xh = 'a001';
--修改多个字段
update student set sex = '男', birthday = '1984-04-01' where xh = 'a001';
--修改含有null值的数据
不要用 = null 而是用 is null；
select * from student where birthday is null;
--删除数据
delete from student; --删除所有记录，表结构还在，写日志，可以恢复的，速度慢。
--delete的数据可以恢复。
savepoint a; --创建保存点
delete from student;
rollback to a; --恢复到保存点
一个有经验的dba，在确保完成无误的情况下要定期创建还原点。
drop table student; --删除表的结构和数据；
delete from student where xh = 'a001'; --删除一条记录；
truncate table student; --删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。
六、表查询一
通过scott用户下的表来演示如何使用select语句，接下来对emp、dept、salgrade表结构进行解说。
emp 雇员表
字段名称 数据类型 是否为空 备注
-------- ----------- -------- --------
EMPNO NUMBER(4) 员工编号
ENAME VARCHAR2(10) Y 员工名称
JOB VARCHAR2(9) Y 职位
MGR NUMBER(4) Y 上级的编号
HIREDATE DATE Y 入职日期
SAL NUMBER(7,2) Y 月工资
COMM NUMBER(7,2) Y 奖金
DEPTNO NUMBER(2) Y 所属部门
-------------------------------------------
job字段：
clerk 普员工
salesman 销售
manager 经理
analyst 分析师
president 总裁
dept 部门表
字段名称 数据类型 是否为空 备注
-------- ----------- -------- --------
DEPTNO NUMBER(2) 部门编号
DNAME VARCHAR2(14) Y 部门名称
LOC VARCHAR2(13) Y 部门所在地点
-------------------------------------------
DNAME字段：
accounting 财务部
research 研发部
operations 业务部
salgrade 工资级别表
字段名称 数据类型 是否为空 备注
-------- --------- -------- --------
GRADE NUMBER Y 级别
LOSAL NUMBER Y 最低工资
HISAL NUMBER Y 最高工资
1、查看表结构
desc emp;
2、查询所有列
select * from dept;
备注:切忌动不动就用select *，使用*效率比较低，特别在大表中要注意。
3、set timing on/off;
打开显示操作时间的开关，在底部显示操作时间。
eg、sql> insert into tb_stu values('0001', 'zhangsan', 24);
1 row inserted
executed in 0.015 seconds
4、insert into...select...表复制语句
语法：insert into table2(field1,field2,...) select value1,value2,... from table1
--创建tb_dept表
create table tb_dept
(
deptno number(4) not null,
dname varchar2(14),
loc varchar2(13)
)
--添加主键约束
alter table tb_dept add constraint tb_dept primary key (deptno);
--insert into...select...用法
insert into tb_dept (deptno, dname, loc) select a.deptno, a.dname, a.loc from dept a;
5、统计
select count (*) from emp;
6、查询指定列
select ename, sal, job, deptno from emp;
7、如何取消重复行distinct
select distinct deptno, job from emp;
8、查询smith所在部门，工作，薪水
select deptno, job, sal from emp where ename = 'smith';
注意：oracle对内容的大小写是敏感的，所以ename='smith'和ename='smith'是不同的
9、nvl函数
格式为：nvl(string1, replace_with)
功能：如果string1为null，则nvl函数返回replace_with的值，否则返回string1的值。
注意事项：string1和replace_with必须为同一数据类型，除非显示的使用to_char函数。
eg、如何显示每个雇员的年工资？
select sal*13+nvl(comm, 0)*13 "年薪" , ename, comm from emp;
10、使用列的别名
select ename "姓名", sal*12 as "年收入" from emp;
11、如何处理null值
使用nvl函数来处理
12、如何连接字符串(||)
select ename || ' is a ' || job from emp;
13、使用where子句
问题：如何显示工资高于3000的员工？
select * from emp where sal > 3000;
问题：如何查找1982.1.1后入职的员工？
select ename,hiredate from emp where hiredate >'1-1 月-1982';
问题：如何显示工资在2000到3000的员工？
select ename,sal from emp where sal>=2000 and sal<=3000;
14、如何使用like操作符
%：表示0到多个字符 _：表示任意单个字符
问题：如何显示首字符为s的员工姓名和工资？
select ename,sal from emp where ename like 's%';
如何显示第三个字符为大写o的所有员工的姓名和工资？
select ename,sal from emp where ename like '__o%';
15、在where条件中使用in
问题：如何显示empno为7844,7839,123,456的雇员情况？
select * from emp where empno in (7844, 7839, 123, 456);
16、使用is null的操作符
问题：如何显示没有上级的雇员的情况？
错误写法：select * from emp where mgr = '';
正确写法：select * from emp where mgr is null;
七、oracle 表查询二
1、使用逻辑操作符号
问题：查询工资高于500或者是岗位为manager的雇员，同时还要满足他们的姓名首字母为大写的J？
select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';
2、使用order by字句 默认asc
问题：如何按照工资从低到高的顺序显示雇员的信息？
select * from emp order by sal;
问题：按照部门号升序而雇员的工资降序排列
select * from emp order by deptno, sal desc;
3、使用列的别名排序
问题：按年薪排序
select ename, (sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc;
备注：别名需要使用“”号圈中,英文不需要“”号
4、聚合函数用法：max，min，avg，sum，count
问题：如何显示所有员工中最高工资和最低工资？
select max(sal),min(sal) from emp e;
最高工资那个人是谁？
错误写法：select ename, sal from emp where sal=max(sal);
正确写法：select ename, sal from emp where sal=(select max(sal) from emp);
注意：select ename, max(sal) from emp;这语句执行的时候会报错，说ora-00937：非单组分组函数。因为max是分组函数，而ename不是分组函数.......
但是select min(sal), max(sal) from emp;这句是可以执行的。因为min和max都是分组函数，就是说：如果列里面有一个分组函数，其它的都必须是分组函数，否则就出错。这是语法规定的
5、问题：如何显示所有员工的平均工资和工资总和？
select sum(e.sal), avg(e.sal) from emp e;
查询最高工资员工的名字，工作岗位
select ename, job, sal from emp e where sal = (select max(sal) from emp);
显示工资高于平均工资的员工信息
select * from emp e where sal > (select avg(sal) from emp);
6、group by 和 having 子句
group by 用于对查询的结果分组统计，
having 子句用于限制分组显示结果。
问题：如何显示每个部门的平均工资和最高工资？
select avg(sal), max(sal), deptno from emp group by deptno;
（注意：这里暗藏了一点，如果你要分组查询的话，分组的字段deptno一定要出现在查询的列表里面，否则会报错。因为分组的字段都不出现的话，就没办法分组了）
问题：显示每个部门的每种岗位的平均工资和最低工资？
select min(sal), avg(sal), deptno, job from emp group by deptno, job;
问题：显示平均工资低于2000的部门号和它的平均工资？
select avg(sal), max(sal), deptno from emp group by deptno having avg(sal)< 2000;
7、对数据分组的总结
1 分组函数只能出现在选择列表、having、order by子句中(不能出现在where中)
2 如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having, order by
3 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by 子句中，否则就会出错。
如select deptno, avg(sal), max(sal) from emp group by deptno having avg(sal) < 2000;这里deptno就一定要出现在group by中
8、多表查询
多表查询是指基于两个和两个以上的表或是视图的查询。在实际应用中，查询单个表可能不能满足你的需求，如显示sales部门位置和其员工的姓名，这种情况下需要使用到dept表和emp表。
1)、问题：显示雇员名，雇员工资及所在部门的名字【笛卡尔集】？
SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
规定：多表查询的条件是至少不能少于表的个数N-1才能排除笛卡尔集（如果有N张表联合查询，必须得有N-1个条件，才能避免笛卡尔集合）
2)、问题：显示部门号为10的部门名、员工名和工资？
SELECT d.dname, e.ename, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno and e.deptno = 10;
3)、问题：显示各个员工的姓名，工资及工资的级别？
SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
4)、问题：显示雇员名，雇员工资及所在部门的名字，并按部门排序？
SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER by e.deptno;
注意：如果用group by，一定要把e.deptno 放到查询列里面
5)、自连接
自连接是指在同一张表的连接查询
问题：显示某个员工的上级领导的姓名？
比如显示员工‘FORD’的上级
SELECT worker.ename, boss.ename FROM emp worker, emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD';
6)、子查询
什么是子查询？
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。
单行子查询？
单行子查询是指只返回一行数据的子查询语句
请思考：显示与SMITH同部门的所有员工？
思路：
1))、查询出SMITH的部门号(返回单行结果集)
select deptno from emp WHERE ename = 'SMITH';
2))、显示
SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH');
数据库在执行sql是从左到右扫描的，如果有括号的话，括号里面的先被优先执行。
多行子查询
多行子查询指返回多行数据的子查询
请思考：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
1))、查询出部门10的所有工作(返回多行结果集)
SELECT DISTINCT job FROM emp WHERE deptno = 10;
2))、显示
SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10);
注意：不能用job=..，因为等号=是一对一的
在多行子查询中使用all操作符
问题：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号？
--方法一
SELECT ename, sal, deptno FROM emp WHERE sal > all(SELECT sal FROM emp WHERE deptno = 30);
--方法二(执行效率最高，使用聚合函数)
SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT max(sal) FROM emp WHERE deptno = 30);
在多行子查询中使用any操作符
问题：如何显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号？
--方法一
SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30);
--方法二(执行效率最高，使用聚合函数)
SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30);
多列子查询
单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询是指查询返回多个列数据的子查询语句。
请思考如何查询与SMITH 的部门和岗位完全相同的所有雇员。
SELECT deptno, job FROM emp WHERE ename = 'SMITH';
SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH');
在from子句中使用子查询
请思考：如何显示高于自己部门平均工资的员工的信息
思路：
1. 查出各个部门的平均工资和部门号
SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno;
2. 把上面的查询结果看做是一张子表
SELECT e.ename, e.deptno, e.sal, ds.mysal
FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds
WHERE e.deptno = ds.deptno AND e.sal > ds.mysal;
小总结：
在这里需要说明的当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在from 子句中使用子查询时，必须给子查询指定别名。
注意：别名不能用as，如：SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) as ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal;
在ds前不能加as，否则会报错（给表取别名的时候，不能加as；但是给列取别名，是可以加as的）
如何衡量一个程序员的水平？
网络处理能力，数据库，程序代码的优化程序的效率要很高
7)、用查询结果创建新表，这个命令是一种快捷的建表方式
CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp;
8)、合并查询
有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，union all，intersect，minus。
多用于数据量比较大的数据局库，运行速度快。
1). union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。
SELECT ename, sal, job FROM emp WHERE sal >2500
UNION
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
2).union all
该操作符与union相似，但是它不会取消重复行，而且不会排序。
SELECT ename, sal, job FROM emp WHERE sal >2500
UNION ALL
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
该操作符用于取得两个结果集的并集。当使用该操作符时，不会自动去掉结果集中重复行。
3). intersect
使用该操作符用于取得两个结果集的交集。
SELECT ename, sal, job FROM emp WHERE sal >2500
INTERSECT
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
4). minus
使用该操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据。
SELECT ename, sal, job FROM emp WHERE sal >2500
MINUS
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
（MINUS就是减法的意思）
八、oracle 分页
oracle的分页一共有三种方式
方法一 根据rowid来分
SELECT *
FROM EMP
WHERE ROWID IN
(SELECT RID
FROM (SELECT ROWNUM RN, RID
FROM (SELECT ROWID RID, EMPNO FROM EMP ORDER BY EMPNO DESC)
WHERE ROWNUM <= ( (currentPage-1) * pageSize + pageSize )) --每页显示几条
WHERE RN > ((currentPage-1) * pageSize) ) --当前页数
ORDER BY EMPNO DESC;
eg、
-- 5 = (currentPage-1) * pageSize + pageSize 每页显示几条
-- 0 = (currentPage-1) * pageSize 当前页数
SELECT *
FROM EMP
WHERE ROWID IN
(SELECT RID
FROM (SELECT ROWNUM RN, RID
FROM (SELECT ROWID RID, EMPNO FROM EMP ORDER BY EMPNO DESC)
WHERE ROWNUM <= ( (1-1) * 5 + 5 )) --每页显示几条
WHERE RN > ((1-1) * 5) ) --当前页数
ORDER BY EMPNO DESC;
方法二 按分析函数来分
SELECT *
FROM (SELECT T.*, ROW_NUMBER() OVER(ORDER BY empno DESC) RK FROM emp T)
WHERE RK <= ( (currentPage-1) * pageSize + pageSize ) --每页显示几条
AND RK > ( (currentPage-1) * pageSize ); --当前页数
eg、
-- 5 = (currentPage-1) * pageSize + pageSize 每页显示几条
-- 0 = (currentPage-1) * pageSize 当前页数
SELECT *
FROM (SELECT T.*, ROW_NUMBER() OVER(ORDER BY empno DESC) RK FROM emp T)
WHERE RK <= 5
AND RK > 0;
方法三 按rownum 来分
SELECT *
FROM (SELECT T.*, ROWNUM RN
FROM (SELECT * FROM EMP ORDER BY EMPNO DESC) T
WHERE ROWNUM <= ( (currentPage-1) * pageSize + pageSize )) --每页显示几条
WHERE RN > ( (currentPage-1) * pageSize ); --当前页数
eg、
-- 5 = (currentPage-1) * pageSize + pageSize 每页显示几条
-- 0 = (currentPage-1) * pageSize 当前页数
SELECT *
FROM (SELECT T.*, ROWNUM RN
FROM (SELECT * FROM EMP ORDER BY EMPNO DESC) T
WHERE ROWNUM <= 5)
WHERE RN > 0;
其中emp为表名称，empno 为表的主键id，获取按empno降序排序后的第1-5条记录，emp表有70000 多条记录。
个人感觉方法一的效率最好，方法三 次之，方法二 最差。
下面通过方法三来分析oracle怎么通过rownum分页的
1、
SELECT * FROM emp;
2、显示rownum，由oracle分配的
SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e; --rn相当于Oracle分配的行的ID号
3、先查出1-10条记录
正确的: SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM<=10;
错误的：SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE rn<=10;
4、然后查出6-10条记录
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM<=10) WHERE rn>=6;
九、oracle 事务
一、什么是事务
事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml(数据操作语言，增删改，没有查询)语句要么全部成功，要么全部失败。
如：网上转账就是典型的要用事务来处理，用于保证数据的一致性。
二、事务和锁
当执行事务操作时(dml语句)，oracle会在被作用的表上加锁，防止其它用户修改表的结构。这里对我们的用户来讲是非常重要的。
三、提交事务
当用commit语句执行时可以提交事务。当执行了commit语句之后，会确认事务的变化、结束事务。删除保存点、释放锁，当使用commit语句结束事务之后，其它会话将可以查看到事务变化后的新数据。保存点就是为回滚做的。保存点的个数没有限制。
四、回滚事务
在介绍回滚事务前，我们先介绍一下保存点(savepoint)的概念和作用。保存点是事务中的一点。用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点。当执行rollback 时，通过指定保存点可以回退到指定的点，这里我们作图说明。
五、事务的几个重要操作
1.设置保存点 savepoint a
2.取消部分事务 rollback to a
3.取消全部事务 rollback
eg、
SQL> savepoint a; --创建保存点a
Savepoint created
SQL> delete from emp where empno=7782;
1 row deleted
SQL> savepoint b; --创建保存到b
Savepoint created
SQL> delete from emp where empno=7934;
1 row deleted
SQL> select * from emp where empno=7934; --无法查询到empno为7934这条记录，因为这条记录已被删除
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
SQL> rollback to b; --通过保持点来恢复这条记录
Rollback complete
SQL> select * from emp where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
SQL> select * from emp where empno=7782; --无法查询到empno为7982这条记录，因为这条记录已被删除
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
SQL> rollback to a; --通过保持点来恢复这条记录
Rollback complete
SQL> select * from emp where empno=7782;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
SQL>
注意：这个回滚事务，必须是没有commit前使用的；如果事务提交了，那么无论你刚才做了多少个保存点，都统统没用。如果没有手动执行commit,而是exit了，那么会自动提交。
eg、
SQL> savepoint a;
Savepoint created
SQL> delete from emp where empno=7782;
1 row deleted
SQL> commit;
Commit complete
SQL> rollback to a;
rollback to a
ORA-01086: 从未创建保存点 'A'
SQL>
六、java程序中如何使用事务
在java操作数据库时，为了保证数据的一致性，比如账户操作(1)从一个账户中减掉10$(2)在另一个账户上加入10$,我们看看如何使用事务？
java代码
package junit.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TransationTest {
public static void main(String[] args) {
Connection conn = null;
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
Statement sm = conn.createStatement();
// 从scott的sal中减去100
sm.executeUpdate("update emp set sal=sal-100 where ename='SCOTT'");
int i = 7 / 0; //报java.lang.ArithmeticException: / by zero异常
// 给smith的sal加上100
sm.executeUpdate("update emp set sal=sal+100 where ename='SMITH'");
// 关闭打开的资源
sm.close();
conn.close();
} catch (Exception e) {
// 如果发生异常，就回滚
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
}
}
运行，会出现异常，查看数据库，SCOTT 的sal 减了100，但是SMITH 的sal 却不变，很可怕。。。
我们怎样才能保证，这两个操作要么同时成功，要么同时失败呢？
Java 代码
package junit.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TransationTest {
public static void main(String[] args) {
Connection conn = null;
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
// 加入事务处理
conn.setAutoCommit(false);// 设置不能默认提交
Statement sm = conn.createStatement();
// 从scott的sal中减去100
sm.executeUpdate("update emp set sal=sal-100 where ename='SCOTT'");
int i = 7 / 0;
// 给smith的sal加上100
sm.executeUpdate("update emp set sal=sal+100 where ename='SMITH'");
// 提交事务
conn.commit();
// 关闭打开的资源
sm.close();
conn.close();
} catch (Exception e) {
// 如果发生异常，就回滚
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
}
}
再运行一下，会出现异常，查看数据库，数据没变化。。
七、只读事务
只读事务是指只允许执行查询的操作，而不允许执行任何其它dml操作的事务，使用只读事务可以确保用户只能取得某时间点的数据。
假定机票代售点每天18点开始统计今天的销售情况，这时可以使用只读事务。在设置了只读事务后，尽管其它会话可能会提交新的事务，但是只读事务将不会取得最新数据的变化，从而可以保证取得特定时间点的数据信息。
设置只读事务: set transaction read only;
比如有两个用户system、scott各自用sqlplus登陆，操作如下：
第一步：用system用户登陆sqlplus，设置只读事务。
SQL> set transaction read only;
事务处理集。
第二步：用scott用户登陆sqlplus，操作如下：
SQL> select count(*) from emp; --查询emp表的总记录数
COUNT(*)
----------
13
SQL> insert into emp values (7777, 'zhangsan', 'MANAGER', 7782, to_date('1988-02-18', 'yyyy-mm-dd'), 38.38, 45.45, 10); --插入一条记录到emp表
1 row inserted
SQL> select count(*) from emp; --查询emp表的总记录数
COUNT(*)
----------
14
SQL> commit; --提交
Commit complete
第三步：用system用户查询scott.emp表
SQL> select count(*) from scott.emp;
COUNT(*)
----------
13
SQL>
十、oracle 常用函数
一、字符函数
字符函数是oracle中最常用的函数，我们来看看有哪些字符函数：
lower(char)：将字符串转化为小写的格式。
upper(char)：将字符串转化为大写的格式。
length(char)：返回字符串的长度。
substr(char, m, n)：截取字符串的子串，n代表取n个字符的意思，不是代表取到第n个
replace(char1, search_string, replace_string)
instr(C1,C2,I,J) -->判断某字符或字符串是否存在，存在返回出现的位置的索引，否则返回小于1;在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
问题：将所有员工的名字按小写的方式显示
SQL> select lower(ename) from emp;
问题：将所有员工的名字按大写的方式显示。
SQL> select upper(ename) from emp;
问题：显示正好为5个字符的员工的姓名。
SQL> select * from emp where length(ename)=5;
问题：显示所有员工姓名的前三个字符。
SQL> select substr(ename, 1, 3) from emp;
问题：以首字母大写,后面小写的方式显示所有员工的姓名。
SQL> select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;
问题：以首字母小写,后面大写的方式显示所有员工的姓名。
SQL> select lower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from emp;
问题：显示所有员工的姓名，用“我是老虎”替换所有“A”
SQL> select replace(ename,'A', '我是老虎') from emp;
问题：instr(char1,char2,[,n[,m]])用法
SQL> select instr('azhangsanbcd', 'zhangsan') from dual; --返回2
SQL> select instr('oracle traning', 'ra', 1, 1) instring from dual; --返回2
SQL> select instr('oracle traning', 'ra', 1, 2) instring from dual; --返回9
SQL> select instr('oracle traning', 'ra', 1, 3) instring from dual; --返回0，根据条件，由于ra只出现二次，第四个参数3，就是说第3次出现ra的位置，显然第3次是没有再出现了，所以结果返回0。注意空格也算一个字符
SQL> select instr('abc','d') from dual; --返回0
二、数学函数
数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos，cosh，exp，ln, log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round等
我们讲最常用的：
round(n,[m]) 该函数用于执行四舍五入，
如果省掉m，则四舍五入到整数。
如果m是正数，则四舍五入到小数点的m位后。
如果m是负数，则四舍五入到小数点的m位前。
eg、SELECT round(23.75123) FROM dual; --返回24
SELECT round(23.75123, -1) FROM dual; --返回20
SELECT round(27.75123, -1) FROM dual; --返回30
SELECT round(23.75123, -3) FROM dual; --返回0
SELECT round(23.75123, 1) FROM dual; --返回23.8
SELECT round(23.75123, 2) FROM dual; --返回23.75
SELECT round(23.75123, 3) FROM dual; --返回23.751
trunc(n,[m]) 该函数用于截取数字。
如果省掉m，就截去小数部分，
如果m是正数就截取到小数点的m位后，
如果m是负数，则截取到小数点的前m位。
eg、SELECT trunc(23.75123) FROM dual; --返回23
SELECT trunc(23.75123, -1) FROM dual; --返回20
SELECT trunc(27.75123, -1) FROM dual; --返回20
SELECT trunc(23.75123, -3) FROM dual; --返回0
SELECT trunc(23.75123, 1) FROM dual; --返回23.7
SELECT trunc(23.75123, 2) FROM dual; --返回23.75
SELECT trunc(23.75123, 3) FROM dual; --返回23.751
mod(m,n)取余函数
eg、select mod(10,2) from dual; --返回0
SELECT MOD(10,3) FROM dual; --返回1
floor(n) 返回小于或是等于n的最大整数
ceil(n) 返回大于或是等于n的最小整数
eg、SELECT ceil(24.56) from dual; --返回25
SELECT floor(24.56) from dual; --返回24
abs(n) 返回数字n的绝对值
对数字的处理，在财务系统或银行系统中用的最多，不同的处理方法，对财务报表有不同的结果
三、日期函数
日期函数用于处理date类型的数据。默认情况下日期格式是dd-mon-yy 即“12-7 月-12”
(1)sysdate 返回系统时间
eg、SQL> select sysdate from dual;
(2)oracle add_months函数
oracle add_months(time,months)函数可以得到某一时间之前或之后n个月的时间
eg、select add_months(sysdate,-6) from dual; --该查询的结果是当前时间半年前的时间
select add_months(sysdate,6) from dual; --该查询的结果是当前时间半年后的时间
(3)last_day(d)：返回指定日期所在月份的最后一天
问题：查找已经入职8个月多的员工
SQL> select * from emp where sysdate>=add_months(hiredate,8);
问题：显示满10年服务年限的员工的姓名和受雇日期。
SQL> select ename, hiredate from emp where sysdate>=add_months(hiredate,12*10);
问题：对于每个员工，显示其加入公司的天数。
SQL> select floor(sysdate-hiredate) "入职天数",ename from emp;
或者
SQL> select trunc(sysdate-hiredate) "入职天数",ename from emp;
问题：找出各月倒数第3天受雇的所有员工。
SQL> select hiredate,ename from emp where last_day(hiredate)-2=hiredate;
四、转换函数
转换函数用于将数据类型从一种转为另外一种。在某些情况下，oracle server允许值的数据类型和实际的不一样，这时oracle server会隐含的转化数据类型
比如：
create table t1(id int);
insert into t1 values('10');--这样oracle会自动的将'10' -->10
create table t2 (id varchar2(10));
insert into t2 values(1); --这样oracle就会自动的将1 -->'1'；
我们要说的是尽管oracle可以进行隐含的数据类型的转换，但是它并不适应所有的情况，为了提高程序的可靠性，我们应该使用转换函数进行转换。
to_char()函数
你可以使用select ename, hiredate, sal from emp where deptno = 10;显示信息，可是，在某些情况下，这个并不能满足你的需求。
问题：日期是否可以显示 时/分/秒
SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') from emp;
问题：薪水是否可以显示指定的货币符号
SQL>
yy：两位数字的年份 2004-->04
yyyy：四位数字的年份 2004年
mm：两位数字的月份 8 月-->08
dd：两位数字的天 30 号-->30
hh24： 8点-->20
hh12：8点-->08
mi、ss-->显示分钟\秒
9：显示数字，并忽略前面0
0：显示数字，如位数不足，则用0补齐
.：在指定位置显示小数点
,：在指定位置显示逗号
$：在数字前加美元
L：在数字前面加本地货币符号
C：在数字前面加国际货币符号
G：在指定位置显示组分隔符、
D：在指定位置显示小数点符号(.)
问题：显示薪水的时候，把本地货币单位加在前面
SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss'), to_char(sal,'L99999.99') from emp;
问题：显示1980年入职的所有员工
SQL> select * from emp where to_char(hiredate, 'yyyy')=1980;
问题：显示所有12月份入职的员工
SQL> select * from emp where to_char(hiredate, 'mm')=12;
to_date()函数
函数to_date用于将字符串转换成date类型的数据。
问题：能否按照中国人习惯的方式年—月—日添加日期。
eg、SELECT to_date('2012-02-18 09:25:30','yyyy-mm-dd hh24:mi:ss') FROM dual;
五、sys_context()系统函数
1)terminal：当前会话客户所对应的终端的标示符，如计算机名
2)language: 语言
3)db_name： 当前数据库名称
4)nls_date_format： 当前会话客户所对应的日期格式
5)session_user： 当前会话客户所对应的数据库用户名
6)current_schema： 当前会话客户所对应的默认方案名
7)host： 返回数据库所在主机的名称
通过该函数，可以查询一些重要信息，比如你正在使用哪个数据库？
select sys_context('USERENV','db_name') from dual;
注意：USERENV是固定的，不能改的，db_name可以换成其它,
eg、select sys_context('USERENV','language') from dual;
select sys_context('USERENV','current_schema') from dual;
十一、oracle 数据库管理员
一、数据库管理员
每个oracle数据库应该至少有一个数据库管理员(dba)，对于一个小的数据库，一个dba就够了，但是对于一个大的数据库可能需要多个dba分担不同的管理职责。那么一个数据库管理员的主要工作是什么呢：
1.安装和升级oracle 数据库
2.建库，表空间，表，视图，索引…
3.制定并实施备份和恢复计划
4.数据库权限管理，调优，故障排除
5.对于高级dba，要求能参与项目开发，会编写sql 语句、存储过程、触发器、规则、约束、包
二、管理数据库的用户主要是sys和system(sys好像是董事长，system好像是总经理，董事长比总经理大，但是通常是总
经理干事)
在前面我们已经提到这两个用户，区别主要是：
1.最重要的区别，存储的数据的重要性不同
sys：所有oracle的数据字典的基表和视图都存放在sys用户中，这些基表和视图对于oracle 的运行是至关重要的，由数据库自己维护，任何用户都不能手动更改。sys用户拥有dba，sysdba，sysoper角色或权限，是oracle权限最高的用户。
system：用于存放次一级的内部数据，如oracle的一些特性或工具的管理信息。system用户拥有dba，sysdba 角色或系统权限。
注意：sysdba可以建数据库，sysoper不能建数据库
2. 其次的区别，权限的不同。
sys用户必须以as sysdba或as sysoper形式登录。不能以normal方式登录数据库。
system如果正常登录，它其实就是一个普通的dba用户，但是如果以as sysdba登录，其结果实际上它是作为sys用户登录的，从登录信息里面我们可以看出来。
sysdba和sysoper权限区别图，看图：
sysdba>sysoper>dba
可以看到：只要是sysoper拥有的权限，sysdba都有；蓝色是它们区别的地方。（它们的最大区别是：sysdba可以创建数据库，sysoper不可以创建数据库）
dba权限的用户
dba用户是指具有dba角色的数据库用户。特权用户可以执行启动实例，关闭实例等特殊操作，而dba用户只有在启动数据库后才能执行各种管理工作。（相当于说dba连startup和shutdown这两个权限都没有）。
两个主要的用户，三个重要权限，他们的区别和联系，大家要弄清楚。
三、管理初始化参数
1)、管理初始化参数(调优的一个重要知识点，凭什么可以对数据库进行调优呢？是因为它可以对数据库的一些参数进行修改修正)
初始化参数用于设置实例或是数据库的特征。oracle9i提供了200多个初始化参数，并且每个初始化参数都有默认值。
2)、显示初始化参数：show parameter
3)、如何修改参数
需要说明的是如果你希望修改这些初始化的参数，可以到文件D:\oracle\admin\myoral\pfile\init.ora文件中去修改。
十二、oracle 数据库(表)的逻辑备份与恢复
一、介绍
逻辑备份是指使用工具export将数据对象的结构和数据导出到文件的过程。
逻辑恢复是指当数据库对象被误操作而损坏后使用工具import利用备份的文件把数据对象导入到数据库的过程。
物理备份即可在数据库open的状态下进行也可在关闭数据库后进行，但是逻辑备份和恢复只能在open的状态下进行。
二、备份(导出)
导出分为导出表、导出方案、导出数据库三种方式。
导出使用exp命令来完成的，该命令常用的选项有：
userid：用于指定执行导出操作的用户名，口令，连接字符串
tables：用于指定执行导出操作的表
owner：用于指定执行导出操作的方案
full=y：用于指定执行导出操作的数据库
inctype：用于指定执行导出操作的增量类型
rows：用于指定执行导出操作是否要导出表中的数据
file：用于指定导出文件名
注意：特别说明-->在导入和导出的时候，要到oracle目录的bin目录下。
1)、导出表
1.导出自己的表
exp userid=scott/oracle@orcl tables=(emp) file=d:\emp.dmp --导出单个表
exp userid=scott/oracle@orcl tables=(emp,dept) file=d:\emp.dmp --导出多个表
eg、
C:\Users\jiqinlin>cd D:\dev\oracle\product\10.2.0\db_1\bin
C:\Users\jiqinlin>d:
D:\dev\oracle\product\10.2.0\db_1\bin>exp userid=scott/oracle@orcl tables=(emp) file=d:\emp.dmp
2.导出其它方案的表
如果用户要导出其它方案的表，则需要dba的权限或是exp_full_database的权限，比如system就可以导出scott的表
D:\dev\oracle\product\10.2.0\db_1\bin>exp userid=system/oracle@orcl tables=(scott.emp) file=d:\emp.emp
D:\dev\oracle\product\10.2.0\db_1\bin>exp userid=system/oracle@orcl tables=(scott.emp,scott.dept) file=d:\emp.emp
3. 导出表的结构
exp userid=scott/oracle@orcl tables=(emp) file=d:\emp.dmp rows=n
4. 使用直接导出方式
exp userid=scott/oracle@orcl tables=(emp) file=d:\emp.dmp direct=y
这种方式比默认的常规方式速度要快，当数据量大时，可以考虑使用这样的方法。
这时需要数据库的字符集要与客户端字符集完全一致，否则会报错...
2)、导出方案
导出方案是指使用export工具导出一个方案或是多个方案中的所有对象(表，索引，约束...)和数据，并存放到文件中。
1. 导出自己的方案
exp userid=scott/oracle@orcl owner=scott file=d:\scott.dmp
2. 导出其它方案
如果用户要导出其它方案，则需要dba的权限或是exp_full_database的权限，
比如system 用户就可以导出任何方案
exp userid=system/oracle@orcl owner=(system,scott) file=d:\system.dmp
3)、导出数据库
导出数据库是指利用export导出所有数据库中的对象及数据，要求该用户具有dba的权限或者是exp_full_database权限
增量备份（好处是第一次备份后，第二次备份就快很多了）
exp userid=system/oracle@orcl full=y inctype=complete file=d:\all.dmp
三、恢复(导入)
导入就是使用工具import将文件中的对象和数据导入到数据库中，但是导入要使用的文件必须是export所导出的文件。与导出相似，导入也分为导入表，导入方案，导入数据库三种方式。
imp常用的选项有
userid：用于指定执行导入操作的用户名，口令，连接字符串
tables：用于指定执行导入操作的表
formuser：用于指定源用户
touser：用于指定目标用户
file 用于指定导入文件名
full=y：用于指定执行导入整个文件
inctype：用于指定执行导入操作的增量类型
rows：指定是否要导入表行（数据）
ignore：如果表存在，则只导入数据
1)导入表
1. 导入自己的表
imp userid=scott/oracle@orcl tables=(emp) file=d:\xx.dmp
2. 导入表到其它用户
要求该用户具有dba的权限，或是imp_full_database
imp userid=system/oracle@orcl tables=(emp) file=d:\xx.dmp touser=scott
3. 导入表的结构
只导入表的结构而不导入数据
imp userid=scott/oracle@orcl tables=(emp) file=d:\xx.dmp rows=n
4. 导入数据
如果对象（如比表）已经存在可以只导入表的数据
imp userid=scott/oracle@orcl tables=(emp) file=d:\xx.dmp ignore=y
2)导入方案
导入方案是指使用import工具将文件中的对象和数据导入到一个或是多个方案中。如果要导入其它方案，要求该用户具有dba 的权限，或者imp_full_database
1．导入自身的方案
imp userid=scott/oracle@orcl file=d:\xxx.dmp
2．导入其它方案
要求该用户具有dba的权限
imp userid=system/oracle@orcl file=d:\xxx.dmp fromuser=system touser=scott
3)导入数据库(相当于数据库迁移)
在默认情况下，当导入数据库时，会导入所有对象结构和数据，案例如下：
imp userid=system/oracle@orcl full=y file=d:\xxx.dmp
十三、oracle 数据字典和动态性能视图
一、概念
数据字典是oracle数据库中最重要的组成部分，它提供了数据库的一些系统信息。
动态性能视图记载了例程启动后的相关信息。
二、数据字典
1)、数据字典记录了数据库的系统信息，它是只读表和视图的集合，数据字典的所有者为sys用户。
2)、用户只能在数据字典上执行查询操作(select语句)，而其维护和修改是由系统自动完成的。
3)、这里我们谈谈数据字典的组成：数据字典包括数据字典基表和数据字典视图，其中基表存储数据库的基本信息，普通用户不能直接访问数据字典的基表。数据字典视图是基于数据字典基表所建立的视图，普通用户可以通过查询数据字典视图取得系统信息。数据字典视图主要包括user_xxx，all_xxx，dba_xxx三种类型。
user_tables: 用于显示当前用户所拥有的所有表，它只返回用户所对应方案的所有表
比如：select table_name from user_tables;
all_tables: 用于显示当前用户可以访问的所有表，它不仅会返回当前用户方案的所有表，还会返回当前用户可以访问的其它方案的表
比如：select table_name from all_tables;
dba_tables: 它会显示所有方案拥有的数据库表。但是查询这种数据库字典视图，要求用户必须是dba角色或是有select any table 系统权限。
例如：当用system用户查询数据字典视图dba_tables时，会返回system，sys，scott...方案所对应的数据库表。
三、用户名，权限，角色
在建立用户时，oracle会把用户的信息存放到数据字典中，当给用户授予权限或是角色时，oracle会将权限和角色的信息存放到数据字典。
通过查询dba_users可以显示所有数据库用户的详细信息；
通过查询数据字典视图dba_sys_privs，可以显示用户所具有的系统权限；
通过查询数据字典视图dba_tab_privs，可以显示用户具有的对象权限；
通过查询数据字典dba_col_privs 可以显示用户具有的列权限；
通过查询数据库字典视图dba_role_privs 可以显示用户所具有的角色。
这里给大家讲讲角色和权限的关系。
1)、要查看scott具有的角色，可查询dba_role_privs；
SQL> select * from dba_role_privs where grantee='SCOTT';
2)、查询orale中所有的系统权限，一般是dba
select * from system_privilege_map order by name;
3)、查询oracle中所有对象权限，一般是dba
select distinct privilege from dba_tab_privs;
4)、查询oracle 中所有的角色，一般是dba
select * from dba_roles;
5)、查询数据库的表空间
select tablespace_name from dba_tablespaces;
问题1：如何查询一个角色包括的权限？
a.一个角色包含的系统权限
select * from dba_sys_privs where grantee='角色名'
另外也可以这样查看：
select * from role_sys_privs where role='角色名'
b.一个角色包含的对象权限
select * from dba_tab_privs where grantee='角色名'
问题2：oracle究竟有多少种角色？
SQL> select * from dba_roles;
问题3：如何查看某个用户，具有什么样的角色？
select * from dba_role_privs where grantee='用户名'
显示当前用户可以访问的所有数据字典视图。
select * from dict where comments like '%grant%';
显示当前数据库的全称
select * from global_name;
其它说明
数据字典记录有oracle数据库的所有系统信息。通过查询数据字典可以取得以下系统信息：比如
1.对象定义情况
2.对象占用空间大小
3.列信息
4.约束信息
...
但是因为这些个信息，可以通过pl/sql developer工具查询得到，所以这里我就飘过。
四、动态性能视图
动态性能视图用于记录当前例程的活动信息，当启动oracle server时，系统会建立动态性能视图；当停止oracle server时，系统会删除动态性能视图。oracle的所有动态性能视图都是以v_$开始的，并且oracle为每个动态性能视图都提供了相应的同义词，并且其同义词是以V$开始的，例如v_$datafile的同义词为v$datafile;动态性能视图的所有者为sys，一般情况下，由dba或是特权用户来查询动态性能视图。
因为这个在实际中用的较少，所以飞过
十四、oracle 数据库管理--管理表空间和数据文件
一、概念
表空间是数据库的逻辑组成部分。
从物理上讲，数据库数据存放在数据文件中；
从逻辑上讲，数据库数据则是存放在表空间中，表空间由一个或多个数据文件组成。
二、数据库的逻辑结构
oracle中逻辑结构包括表空间、段、区和块。
说明一下数据库由表空间构成，而表空间又是由段构成，而段又是由区构成，而区又是由oracle块构成的这样的一种结构，可以提高数据库的效率。
三、表空间
1、概念
表空间用于从逻辑上组织数据库的数据。数据库逻辑上是由一个或是多个表空间组成的。通过表空间可以达到以下作用：
1)、控制数据库占用的磁盘空间
2)、dba可以将不同数据类型部署到不同的位置，这样有利于提高i/o性能，同时利于备份和恢复等管理操作。
2、建立表空间
建立表空间是使用crate tablespace命令完成的，需要注意的是，一般情况下，建立表空间是特权用户或是dba来执行的，如果用其它用户来创建表空间，则用户必须要具有create tablespace的系统权限。
1)、建立数据表空间
在建立数据库后，为便于管理表，最好建立自己的表空间
--路径D:\dev\oracle\product\10.2.0\要存在，否则创建不成功
create tablespace data01 datafile 'D:\dev\oracle\product\10.2.0\dada01.dbf' size 20m uniform size 128k;
说明：执行完上述命令后，会建立名称为data01的表空间，并为该表空间建立名称为data01.dbf的数据文件，区的大小为128k
2)、使用数据表空间
create table mypart(
deptno number(4),
dname varchar2(14),
loc varchar2(13)
) tablespace data01;
3、改变表空间的状态
当建立表空间时，表空间处于联机的(online)状态，此时该表空间是可以访问的，并且该表空间是可以读写的，即可以查询该表空间的数据，而且还可以在表空间执行各种语句。但是在进行系统维护或是数据维护时，可能需要改变表空间的状态。一般情况下，由特权用户或是dba来操作。
1)、使表空间脱机
alter tablespace 表空间名 offline;
eg、alter tablespace data01 offline;--表空间名不能加单引号
2)、使表空间联机
alter tablespace 表空间名 online;
eg、alter tablespace data01 online;
3)、只读表空间
当建立表空间时，表空间可以读写，如果不希望在该表空间上执行update，delete，insert操作，那么可以将表空间修改为只读
alter tablespace 表空间名 read only;
注意：修改为可写是alter tablespace 表空间名 read write;)
我们给大家举一个实例，说明只读特性：
1)、知道表空间名，显示该表空间包括的所有表
select * from all_tables where tablespace_name=’表空间名’;
eg、select * from all_tables where tablespace_name='DATA01'; --DATA01要大写格式
2)、 知道表名，查看该表属于那个表空间
select tablespace_name, table_name from user_tables where table_name='EMP';
通过2我们可以知道scott.emp是在system这个表空间上，现在我们可以将system改为只读的但是我们不会成功，因为system是系统表空间，如果是普通表空间，那么我们就可以将其设为只读的，给大家做一个演示，可以加强理解。
3)、
4)、使表空间可读写
alter tablespace 表空间名 read write;
4、删除表空间
一般情况下，由特权用户或是dba来操作，如果是其它用户操作，那么要求用户具有drop tablespace 系统权限。
drop tablespace ‘表空间’ including contents and datafiles;
eg、drop TABLESPACE DATA01 including contents and datafiles;
说明：including contents表示删除表空间时，删除该空间的所有数据库对象，而datafiles表示将数据库文件也删除。
5、扩展表空间
表空间是由数据文件组成的，表空间的大小实际上就是数据文件相加后的大小。那么我们可以想象，假定表employee存放到data01表空间上，初始大小就是2M，当数据满2M空间后，如果在向employee表插入数据，这样就会显示空间不足的错误。
案例说明：
1. 建立一个表空间sp01
eg、create tablespace sp01 datafile 'D:\dev\oracle\product\10.2.0\dada01.dbf' size 1m uniform size 128k;
2. 在该表空间上建立一个普通表mydment其结构和dept一样
create table mypart(
deptno number(4),
dname varchar2(14),
loc varchar2(13)
) tablespace sp01;
3. 向该表中加入数据insert into mydment select * from dept;
4. 当一定时候就会出现无法扩展的问题，怎么办？
5. 就扩展该表空间，为其增加更多的存储空间。
有三种方法：
1. 增加数据文件
SQL> alter tablespace sp01 add datafile 'D:\dev\oracle\product\10.2.0\dada02.dbf' size 1m;
2. 修改数据文件的大小
SQL> alter tablespace sp01 'D:\dev\oracle\product\10.2.0\dada01.dbf' resize 4m;
这里需要注意的是数据文件的大小不要超过500m。
3. 设置文件的自动增长。
SQL> alter tablespace sp01 'D:\dev\oracle\product\10.2.0\dada01.dbf' autoextend on next 10m maxsize 500m;
6、移动数据文件
有时，如果你的数据文件所在的磁盘损坏时，该数据文件将不能再使用，为了能够重新使用，需要将这些文件的副本移动到其它的磁盘，然后恢复。
下面以移动数据文件sp01.dbf为例来说明：
1. 确定数据文件所在的表空间
select tablespace_name from dba_data_files where file_name=upper('D:\dev\oracle\product\10.2.0\dada01.dbf');
2. 使表空间脱机
--确保数据文件的一致性，将表空间转变为offline的状态。
alter tablespace sp01 offline;
3. 使用命令移动数据文件到指定的目标位置
host move D:\dev\oracle\product\10.2.0\dada01.dbf c:\dada01.dbf;
4. 执行alter tablespace 命令
在物理上移动了数据后，还必须执行alter tablespace命令对数据库文件进行逻辑修改：
alter tablespace sp01 rename datafile 'D:\dev\oracle\product\10.2.0\dada01.dbf' to 'c:\sp01.dbf';
5. 使得表空间联机
在移动了数据文件后，为了使用户可以访问该表空间，必须将其转变为online状态。
alter tablespace sp01 online;
7、显示表空间信息
查询数据字典视图dba_tablespaces，显示表空间的信息：
select tablespace_name from dba_tablespaces;
显示表空间所包含的数据文件
查询数据字典视图dba_data_files,可显示表空间所包含的数据文件，如下：
select file_name, bytes from dba_data_files where tablespace_name='表空间';
四、表空间小结
1. 了解表空间和数据文件的作用
2. 掌握常用表空间，undo表空间和临时表空间的建立方法
3. 了解表空间的各个状态(online, offline, read write, read only)的作用，及如何改变表空间的状态的方法。
4. 了解移动数据文件的原因，及使用alter tablespace 和alter datatable命令移动数据文件的方法。
五、其它表空间
除了最常用的数据表空间外，还有其它类型表空间：
1. 索引表空间
2. undo表空间
3. 临时表空间
4. 非标准块的表空间
这几种表空间，大家伙可以自己参考书籍研究，这里我就不讲。
六、其它说明
关于表空间的组成部分 段/区/块，我们在后面给大家讲解。
十五、oracle 约束
一、维护数据的完整性
数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则，在oracle中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。
二、约束
约束用于确保数据库数据满足特定的商业规则。在oracle中，约束包括：not null、 unique， primary key， foreign key和check 五种。
1)、not null(非空)
如果在列上定义了not null，那么当插入数据时，必须为列提供数据。
2)、unique(唯一)
当定义了唯一约束后，该列值是不能重复的，但是可以为null。
3)、primary key(主键)
用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null。
需要说明的是：一张表最多只能有一个主键，但是可以有多个unqiue约束。
4)、foreign key(外键)
用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必须具有主键约束或是unique 约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。
5)、check
用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间如果不在1000-2000之间就会提示出错。
三、商店售货系统表设计案例一
现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：
商品goods(商品号goodsId，商品名goodsName，单价unitprice，商品类别category，供应商provider)；
客户customer(客户号customerId，姓名name，地址address，电邮email，性别sex，身份证cardId)；
购买purchase(客户号customerId，商品号goodsId，购买数量nums)；
请用SQL语言完成下列功能：
1. 建表，在定义中要求声明：
(1). 每个表的主外键；
(2). 客户的姓名不能为空值；
(3). 单价必须大于0，购买数量必须在1到30之间；
(4). 电邮不能够重复；
(5). 客户的性别必须是男或者女，默认是男；
SQL> create table goods(
goodsId char(8) primary key, --主键
goodsName varchar2(30),
unitprice number(10,2) check(unitprice>0),
category varchar2(8),
provider varchar2(30)
);
SQL> create table customer(
customerId char(8) primary key, --主键
name varchar2(50) not null, --不为空
address varchar2(50),
email varchar2(50) unique, --唯一
sex char(2) default '男' check(sex in ('男','女')), -- 一个char能存半个汉字，两位char能存一个汉字
cardId char(18)
);
SQL> create table purchase(
customerId char(8) references customer(customerId),
goodsId char(8) references goods(goodsId),
nums number(10) check (nums between 1 and 30)
);
表是默认建在SYSTEM表空间的
四、商店售货系统表设计案例二
如果在建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束。但是要注意：增加not null约束时，需要使用modify选项，而增加其它四种约束使用add选项。
1)、增加商品名也不能为空
SQL> alter table goods modify goodsName not null;
2)、增加身份证也不能重复
SQL> alter table customer add constraint xxxxxx unique(cardId);
3)、 增加客户的住址只能是’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’；
SQL> alter table customer add constraint yyyyyy check (address in ('海淀','朝阳','东城','西城','通州','崇文','昌平'));
删除约束
当不再需要某个约束时，可以删除。
alter table 表名 drop constraint 约束名称；
特别说明一下：在删除主键约束的时候，可能有错误，比如：alter table 表名 drop primary key；这是因为如果在两张表存在主从关系，那么在删除主表的主键约束时，必须带上cascade选项 如像：alter table 表名 drop primary key cascade;
显示约束信息
1)、显示约束信息
通过查询数据字典视图user_constraints，可以显示当前用户所有的约束的信息。
select constraint_name, constraint_type, status, validated from user_constraints where table_name = '表名';
2)、显示约束列
通过查询数据字典视图user_cons_columns，可以显示约束所对应的表列信息。
select column_name, position from user_cons_columns where constraint_name = '约束名';
3)、当然也有更容易的方法，直接用pl/sql developer查看即可。简单演示一下下...
五、表级定义、列级定义
1)、列级定义
列级定义是在定义列的同时定义约束。
如果在department表定义主键约束
create table department4(
dept_id number(12) constraint pk_department primary key,
name varchar2(12),
loc varchar2(12)
);
2)、表级定义
表级定义是指在定义了所有列后，再定义约束。这里需要注意：
not null约束只能在列级上定义。
以在建立employee2表时定义主键约束和外键约束为例：
create table employee2(
emp_id number(4),
name varchar2(15),
dept_id number(2),
constraint pk_employee primary key (emp_id),
constraint fk_department foreign key (dept_id) references department4(dept_id)
);
十六、oracle 索引
一、管理索引-原理介绍
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。索引有很多种我们主要介绍常用的几种：
为什么添加了索引后，会加快查询速度呢？
二、创建索引
1)、单列索引
单列索引是基于单个列所建立的索引
语法：create index 索引名 on 表名(列名);
eg、create index nameIndex on custor(name);
2)、复合索引
复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同，比如：
create index emp_idx1 on emp(ename, job);
create index emp_idx1 on emp(job, ename);
以上这两个索引是两个不同的索引。
三、使用原则
1)、在大表上建立索引才有意义
2)、在where子句或是连接条件上经常引用的列上建立索引
3)、索引的层次不要超过4层
这里能不能给学生演示这个效果呢？
如何构建一个大表呢？
四、索引的缺点
索引有一些先天不足：
1)、建立索引，系统要占用大约为表1.2倍的硬盘和内存空间来保存索引。
2)、更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。
实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。
比如在如下字段建立索引应该是不恰当的：
1. 很少或从不引用的字段；
2. 逻辑型的字段，如男或女(是或否)等。
综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，这是考验一个DBA是否优秀的很重要的指标
五、其它索引
按照数据存储方式，可以分为B*树、反向索引、位图索引；
按照索引列的个数分类，可以分为单列索引、复合索引；
按照索引列值的唯一性，可以分为唯一索引和非唯一索引。
此外还有函数索引，全局索引，分区索引...
对于索引我还要说：
在不同的情况，我们会在不同的列上建立索引，甚至建立不同种类的索引，请记住，技术是死的，人是活的。
比如：B*树索引建立在重复值很少的列上，而位图索引则建立在重复值很多、不同值相对固定的列上。
六、显示索引信息
1)、在同一张表上可以有多个索引，通过查询数据字典视图dba_indexs和user_indexs，可以显示索引信息。其中dba_indexs用于显示数据库所有的索引信息，而user_indexs用于显示当前用户的索引信息：select index_name, index_type from user_indexes where table_name = '表名';
2)、显示索引列
通过查询数据字典视图user_ind_columns,可以显示索引对应的列的信息
select table_name, column_name from user_ind_columns where index_name ='IND_ENAME';
你也可以通过pl/sql developer工具查看索引信息
十七、oracle 权限
一、介绍
这一部分我们主要看看oracle中如何管理权限和角色，权限和角色的区别在哪里。
当刚刚建立用户时，用户没有任何权限，也不能执行任何操作。如果要执行某种特定的数据库操作，则必须为其授予系统的权限；如果用户要访问其它方案的对象，则必须为其授予对象的权限。为了简化权限的管理，可以使用角色。这里我们会详细的介绍。
二、权限
权限是指执行特定类型sql命令或是访问其它方案对象的权利，包括系统权限和对象权限两种。
三、系统权限
1)、系统权限是指执行特定类型sql命令的权利。它用于控制用户可以执行的一个或是一组数据库操作。比如当用户具有create table权限时，可以在其方案中建表，当用户具有create any table权限时，可以在任何方案中建表。oracle提供了100多种系统权限。
常用的有：
create session 连接数据库
create table 建表
create view 建视图
create public synonym 建同义词
create procedure 建过程、函数、包
create trigger 建触发器
create cluster 建簇
2)、显示系统权限
oracle提供了100多种系统权限，而且oracle的版本越高，提供的系统权限就越多，我们可以查询数据字典视图system_privilege_map，可以显示所有系统权限。
select * from system_privilege_map order by name;
3)、授予系统权限
一般情况，授予系统权限是由dba完成的，如果用其他用户来授予系统权限，则要求该用户必须具有grant any privilege的系统权限。在授予系统权限时，可以带有with admin option选项，这样，被授予权限的用户或是角色还可以将该系统权限授予其它的用户或是角色。为了让大家快速理解，我们举例说明：
1.创建两个用户ken，tom。初始阶段他们没有任何权限，如果登录就会给出错误的信息。
create user ken identified by ken;
2 给用户ken授权
1). grant create session, create table to ken with admin option;
2). grant create view to ken;
3 给用户tom授权
我们可以通过ken给tom授权，因为with admin option是加上的。当然也可以通过dba给tom授权，我们就用ken给tom授权：
1. grant create session, create table to tom;
2. grant create view to ken; --ok 吗？不ok
4)、回收系统权限
一般情况下，回收系统权限是dba来完成的，如果其它的用户来回收系统权限，要求该用户必须具有相应系统权限及转授系统权限的选项(with admin option)。回收系统权限使用revoke来完成。当回收了系统权限后，用户就不能执行相应的操作了，但是请注意，系统权限级联收回的问题?[不是级联回收！]
system --------->ken ---------->tom
(create session)(create session)( create session)
用system 执行如下操作：
revoke create session from ken; --请思考tom还能登录吗？
答案：能，可以登录
四、对象权限
1)、对象权限介绍
指访问其它方案对象的权利，用户可以直接访问自己方案的对象，但是如果要访问别的方案的对象，则必须具有对象的权限。
比如smith用户要访问scott.emp表(scott：方案，emp：表)
常用的有：
insert 添加
delete 删除
alter 修改
select 查询
index 索引
references 引用
execute 执行
2)、显示对象权限
通过数据字段视图可以显示用户或是角色所具有的对象权限。视图为dba_tab_privs
SQL> conn system/manager;
SQL> select distinct privilege from dba_tab_privs;
SQL> select grantor, owner, table_name, privilege from dba_tab_privs where grantee = 'BLAKE';
3)、授予对象权限
在oracle9i前，授予对象权限是由对象的所有者来完成的，如果用其它的用户来操作，则需要用户具有相应的(with grant option)权限，从oracle9i 开始，dba用户(sys，system)可以将任何对象上的对象权限授予其它用户。授予对象权限是用grant 命令来完成的。对象权限可以授予用户，角色，和public。在授予权限时，如果带有with grantoption 选项，则可以将该权限转授给其它用户。但是要注意with grant option选项不能被授予角色。
1.monkey 用户要操作scott.emp 表，则必须授予相应的对象权限
1). 希望monkey可以查询scott.emp 表的数据，怎样操作？
grant select on emp to monkey;
2). 希望monkey可以修改scott.emp 的表数据，怎样操作？
grant update on emp to monkey;
3). 希望monkey可以删除scott.emp 的表数据，怎样操作？
grant delete on emp to monkey;
4). 有没有更加简单的方法，一次把所有权限赋给monkey？
grant all on emp to monkey;
2.能否对monkey访问权限更加精细控制。(授予列权限)
1). 希望monkey只可以修改scott.emp的表的sal字段，怎样操作？
grant update on emp(sal) to monkey
2).希望monkey 只可以查询scott.emp 的表的ename，sal 数据，怎样操作？
grant select on emp(ename,sal) to monkey
3.授予alter权限
如果black用户要修改scott.emp表的结构，则必须授予alter对象权限
SQL> conn scott/tiger
SQL> grant alter on emp to blake;
当然也可以用system，sys 来完成这件事。
4.授予execute权限
如果用户想要执行其它方案的包/过程/函数，则须有execute权限。
比如为了让ken可以执行包dbms_transaction，可以授予execute 权限。
SQL> conn system/manager
SQL> grant execute on dbms_transaction to ken;
5.授予index权限
如果想在别的方案的表上建立索引，则必须具有index 对象权限。
如果为了让black 可以在scott.emp 表上建立索引，就给其index 的对象权限
SQL> conn scott/tiger
SQL> grant index on scott.emp to blake;
6.使用with grant option 选项
该选项用于转授对象权限。但是该选项只能被授予用户，而不能授予角色
SQL> conn scott/tiger;
SQL> grant select on emp to blake with grant option;
SQL> conn black/shunping
SQL> grant select on scott.emp to jones;
4)、回收对象权限
在oracle9i 中，收回对象的权限可以由对象的所有者来完成，也可以用dba用户(sys，system)来完成。
这里要说明的是：收回对象权限后，用户就不能执行相应的sql命令，但是要注意的是对象的权限是否会被级联收回？【级联回收】
如：scott------------->blake-------------->jones
select on emp select on emp select on emp
SQL> conn scott/tiger@accp
SQL> revoke select on emp from blake
请大家思考，jones能否查询scott.emp表数据。
答案：查不了了(级联回收，和系统权限不一样，刚好1相反)
十八、oracle 角色
一、介绍
角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理。
假定有用户a，b，c为了让他们都拥有如下权限
1. 连接数据库
2. 在scott.emp表上select，insert，update。
如果采用直接授权操作，则需要进行12次授权。
因为要进行12次授权操作，所以比较麻烦喔！怎么办？
如果我们采用角色就可以简化：
首先将creat session，select on scott.emp, insert on scott.emp, update on scott.emp 授予角色，然后将该角色授予a，b，c 用户，这样就可以三次授权搞定。
二、角色分为预定义和自定义角色两类
三、预定义角色
预定义角色是指oracle所提供的角色，每种角色都用于执行一些特定的管理任务，下面我们介绍常用的预定义角色connect、resource、dba。
1)、connect角色
connect角色具有一般应用开发人员需要的大部分权限，当建立了一个用户后，多数情况下，只要给用户授予connect和resource角色就够了，那么connect角色具有哪些系统权限呢？
create cluster
create database link
create session
alter session
create table
create view
create sequence
2)、resource角色
resource角色具有应用开发人员所需要的其它权限，比如建立存储过程，触发器等。这里需要注意的是resource角色隐含unlimited tablespace系统权限。
resource角色包含以下系统权限：
create cluster
create indextype
create table
create sequence
create type
create procedure
create trigger
3)、dba角色
dba角色具有所有的系统权限，及with admin option选项，默认的dba用户为sys和system，它们可以将任何系统权限授予其他用户。但是要注意的是dba角色不具备sysdba和sysoper的特权（启动和关闭数据库）。
四、自定义角色
1、顾名思义就是自己定义的角色，根据自己的需要来定义。一般是dba来建立，如果用别的用户来建立，则需要具有create role的系统权限。在建立角色时可以指定验证方式(不验证，数据库验证等)。
1)、建立角色（不验证）
如果角色是公用的角色，可以采用不验证的方式建立角色。
create role 角色名 not identified;
2)、建立角色（数据库验证）
采用这样的方式时，角色名、口令存放在数据库中。当激活该角色时，必须提供口令。在建立这种角色时，需要为其提供口令。
create role 角色名 identified by 密码;
2、角色授权
1)、给角色授权
给角色授予权限和给用户授权没有太多区别，但是要注意，系统权限的unlimited tablespace和对象权限的with grant option选项是不能授予角色的。
SQL> conn system/oracle;
SQL> grant create session to 角色名 with admin option
SQL> conn scott/oracle@orcl;
SQL> grant select on scott.emp to 角色名;
SQL> grant insert, update, delete on scott.emp to 角色名;
通过上面的步骤，就给角色授权了。
2)、分配角色给某个用户
一般分配角色是由dba来完成的，如果要以其它用户身份分配角色，则要求用户必须具有grant any role的系统权限。
SQL> conn system/oracle;
SQL> grant 角色名 to blake with admin option;
因为我给了with admin option选项，所以，blake可以把system分配给它的角色分配给别的用户。
3、删除角色
使用drop role，一般是dba来执行，如果其它用户则要求该用户具有drop any role系统权限。
SQL> conn system/oracle;
SQL> drop role 角色名;
问题：如果角色被删除，那么被授予角色的用户是否还具有之前角色里的权限？
答案：不具有了
4、显示角色信息
1)、显示所有角色
SQL> select * from dba_roles;
2)、显示角色具有的系统权限
SQL> select privilege, admin_option from role_sys_privs where role='角色名';
3)、显示角色具有的对象权限
通过查询数据字典视图dba_tab_privs可以查看角色具有的对象权限或是列的权限。
4)、显示用户具有的角色，及默认角色
当以用户的身份连接到数据库时，oracle 会自动的激活默认的角色，通过查询数据字典视图dba_role_privs 可以显示某个用户具有的所有角色及当前默认的角色。
SQL> select granted_role, default_role from dba_role_privs where grantee = ‘用户名’;
五、精细访问控制
精细访问控制是指用户可以使用函数，策略实现更加细微的安全访问控制。如果使用精细访问控制，则当在客户端发出sql语句(select，insert，update，delete)时，oracle会自动在sql语句后追加谓词(where子句)，并执行新的sql语句，通过这样的控制，可以使得不同的数据库用户在访问相同表时，返回不同的数据信息，如：
用户 scott blake jones
策略 emp_access
数据库表 emp
如上图所示，通过策略emp_access，用户scott，black，jones在执行相同的sql语句时，可以返回不同的结果。
例如：当执行select ename from emp时，根据实际情况可以返回不同的结果。
十九、oracle pl/sql简介
一、pl/sql 是什么
pl/sql(procedural language/sql)是oracle在标准的sql语言上的扩展。
pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。
二、为什么要学pl/sql
1.提高应用程序的运行性能
2.模块化的设计思想(分页的过程，订单的过程，转账的过程。。)
3.减少网络传输量
4.提高安全性(sql会包括表名，有时还可能有密码，传输的时候会泄露。PL/SQL就不会)
三、Oracle为什么在PL/SQL developer执行很快,用c# oracleclient执行就慢
因为PL/SQL这门语言是专门用于在各种环境下对Oracle数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。
而c#语言是微软的产品，它在连接ORACLE的时候先存到“连接池”中，所以第一次会慢点，但是当你的Web程序没有重起的时候，以后的速度就不会慢了。
四、使用pl/sql的缺点
移植性不好(换数据库就用不了)
五、pl/sql理解
1)、存储过程、函数、触发器是pl/sql编写的
2)、存储过程、函数、触发器是存在oracle中的
3)、pl/sql是非常强大的数据库过程语言
4)、存储过程、函数可以在java中调用
六、编写一个存储过程，该过程可以向某表中添加记录。
1、创建一张简单的表
CREATE TABLE mytest(
username VARCHAR2(30),
pwd VARCHAR2(30)
);
2、创建过程(replace:表示如果有insert_proc，就替换)
CREATE OR REPLACE PROCEDURE insert_proc IS
BEGIN
INSERT INTO mytest VALUES('林计钦', '123456');
END;
/
3、如何查看错误信息：show error;
注意要在命令窗口执行
4、如何调用该过程：exec 过程名(参数值1，参数值2...);
eg、exec insert_proc;
注意要在命令窗口执行
二十、oracle pl/sql基础
一、pl/sql developer开发工具
pl/sql developer是用于开发pl/sql块的集成开发环境(ide)，它是一个独立的产品，而不是oracle的一个附带品。
二、pl/sql介绍
开发人员使用pl/sql编写应用模块时，不仅需要掌握sql语句的编写方法，还要掌握pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑控制语句，从而可以编写非常有用的功能模块。比如：分页存储过程模块、订单处理存储过程模块、转账存储过程模块。而且如果使用pl/sql编程，我们可以轻松地完成非常复杂的查询要求。
三、pl/sql可以做什么
可以用来编写存储过程、函数、触发器、包等
四、编写规范
五、pl/sql块介绍
块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块，要完成相对简单的应用功能，可能只需要编写一个pl/sql块，但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块。
六、块结构示意图
pl/sql块由三个部分构成：定义部分，执行部分，例外处理部分。
如下所示：
declare
/*定义部分——定义常量、变量、游标、例外、复杂数据类型*/
begin
/*执行部分——要执行的pl/sql 语句和sql 语句*/
exception
/*例外处理部分——处理运行的各种错误*/
end;
说明：
定义部分是从declare开始的，该部分是可选的；
执行部分是从begin开始的，该部分是必须的；
例外处理部分是从exception开始的，该部分是可选的。
可以和java编程结构做一个简单的比较。
七、pl/sql块的实例一
实例一 只包括执行部分的pl/sql块
set serveroutput on; --打开输出选项
begin
dbms_output.put_line('hello world');
end;
/ --执行
相关说明：
dbms_output是oracle所提供的包(类似java 的开发包)，该包包含一些过程，put_line就是dbms_output包的一个过程。
八、pl/sql块的实例二
实例二 包含定义部分和执行部分的pl/sql块
set serveroutput on; --打开输出选项
DECLARE
--定义字符串变量
v_ename varchar2(10);
BEGIN
--执行部分
select ename into v_ename from emp where empno=&empno; --& 表示要接收从控制台输入的变量
--在控制台显示雇员名
dbms_output.put_line('雇员名：'||v_ename);
end;
/
九、pl/sql块的实例三
实例三 包含定义部分，执行部分和例外处理部分
为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理，这个很有必要。
1.比如在实例二中，如果输入了不存在的雇员号，应当做例外处理。
2.有时出现异常，希望用另外的逻辑处理，我们看看如何完成1的要求。
相关说明：oracle事先预定义了一些例外，no_data_found就是找不到数据的例外
--打开输出选项
set serveroutput on;
DECLARE
--定义字符串变量
v_ename varchar2(10);
v_sal NUMBER(7,2);
BEGIN
--执行部分
select ename, sal into v_ename, v_sal from emp where empno=&empno;
dbms_output.put_line('雇员名：'||v_ename||'，薪水：'||v_sal);
EXCEPTION
--异常处理
WHEN no_data_found THEN dbms_output.put_line('朋友，您的编号输入有误！');
end;
/
二十一、oracle pl/sql分类一 存储过程
存储过程用于执行特定的操作，当建立存储过程时，既可以指定输入参数(in)，也可以指定输出参数(out)，通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。
实例如下：
1.请考虑编写一个存储过程，可以输入雇员名，新工资，用来修改雇员的工资
--根据雇员名去修改工资
CREATE PROCEDURE sp_update(uname VARCHAR2, newsal NUMBER) IS
BEGIN
update emp set sal=newsal where ename=uname;
END;
/
2.如何调用存储过程有两种方法：exec、call
--使用exec调用存储过程
SQL> exec sp_update('zhangsan', 888);
SQL> commit;
3.如何在java程序中调用一个存储过程
package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
/**
* 演示java程序调用oracle的存储过程案例
*
* @author jiqinlin
*
*/
public class ProcedureTest {
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
// 3.创建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_update(?,?)}");
// 4.给?赋值
cs.setString(1, "SMITH");
cs.setInt(2, 4444);
// 5.执行
cs.execute();
// 关闭
cs.close();
ct.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
问题：如何使用过程返回值？
特别说明：对于存储过程我们会在以后给大家详细具体的介绍，现在请大家先有一个概念。
二十二、oracle pl/sql分类二 函数
函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句。而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数。
1）、接下来通过一个案例来模拟函数的用法
--输入雇员的姓名，返回该雇员的年薪
CREATE FUNCTION annual_incomec(uname VARCHAR2)
RETURN NUMBER IS
annual_salazy NUMBER(7,2);
BEGIN
SELECT a.sal*13 INTO annual_salazy FROM emp a WHERE a.ename=uname;
RETURN annual_salazy;
END;
/
2）、在sqlplus中调用函数
SQL> var income NUMBER;
SQL> call annual_incomec('SCOTT') into:income;
SQL> print income;
3）、在java程序中调用oracle函数：select annual_incomec('SCOTT') income from dual;
package junit.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* 演示java程序调用oracle的函数案例
*
* @author jiqinlin
*
*/
public class ProcedureTest {
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
// 3.创建PreparedStatement
PreparedStatement ps = ct.prepareStatement("select annual_incomec('SCOTT') annual from dual");
// 4.执行
ResultSet rs=ps.executeQuery();
if(rs.next()){
Float annual=rs.getFloat("annual");
System.out.println(annual);
}
//5、关闭
rs.close();
ps.close();
ct.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
二十三、oracle pl/sql分类三 包
包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。
1)、我们可以使用create package命令来创建包，如：
i、创建一个包sp_package
ii、声明该包有一个过程update_sal
iii、声明该包有一个函数annual_income
--声明该包有一个存储过程和一个函数
create package sp_package is
procedure update_sal(name varchar2, newsal number);
function annual_income(name varchar2) return number;
end;
2)、建立包体可以使用create package body命令
给包sp_package实现包体
CREATE OR REPLACE PACKAGE BODY SP_PACKAGE IS
--存储过程
PROCEDURE UPDATE_SAL(NAME VARCHAR2, NEWSAL NUMBER) IS
BEGIN
UPDATE EMP SET SAL = NEWSAL WHERE ENAME = NAME;
COMMIT;
END;
--函数
FUNCTION ANNUAL_INCOME(NAME VARCHAR2) RETURN NUMBER IS
ANNUAL_SALARY NUMBER;
BEGIN
SELECT SAL * 12 + NVL(COMM, 0) INTO ANNUAL_SALARY FROM EMP WHERE ENAME = NAME;
RETURN ANNUAL_SALARY;
END;
END;
/
3)、如何调用包的过程或是函数
当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名。如：
--调用存储过程
SQL> exec sp_package.update_sal('SCOTT', 8888);
--调用函数
var income NUMBER;
CALL sp_package.ANNUAL_INCOME('SCOTT') INTO:income;
print income;
特别说明：包是pl/sql 中非常重要的部分，我们在使用过程分页时，将会再次体验它的威力呵呵。
触发器
触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件insert,update,delete 语句，而触发操作实际就是一个pl/sql 块。可以使用create trigger 来建立触发器。
特别说明：我们会在后面详细为大家介绍触发器的使用，因为触发器是非常有用的，可维护数据库的安全和一致性。
二十四、oracle pl/sql 变量
一、变量介绍
在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有：
1)、标量类型(scalar)
2)、复合类型(composite) --用于操作单条记录
3)、参照类型(reference) --用于操作多条记录
4)、lob(large object)
二、标量(scalar)——常用类型
1)、在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。
pl/sql中定义变量和常量的语法如下：
identifier [constant] datatype [not null] [:=| default expr]
identifier: 名称
constant：指定常量。需要指定它的初始值，且其值是不能改变的
datatype：数据类型
not null：指定变量值不能为null
:= 给变量或是常量指定初始值
default 用于指定初始值
expr ：指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等。
2)、标量定义的案例
1.定义一个变长字符串
v_ename varchar2(10);
2.定义一个小数,范围-9999.99~9999.99
v_sal number(6,2);
3.定义一个小数并给一个初始值为5.4，:=是pl/sql的赋值号
v_sal2 number(6,2):=5.4;
4.定义一个日期类型的数据
v_hiredate date;
5.定义一个布尔变量，不能为空，初始值为false
v_valid boolean not null default false;
三、标量(scalar)——使用标量
在定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前面加冒号(:=)。
下面以输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)为例。说明变量的使用，看看如何编写。
set serveroutput on; --打开输出选项
DECLARE
--税率为0.03
C_TAX_RATE NUMBER(3, 2) :=0.03;
--雇员姓名
V_ENAME VARCHAR2(5);
--工资
V_SAL NUMBER(7, 2);
--个人所得税
V_TAX_SAL NUMBER(7, 2);
BEGIN
--执行
SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO=&empno; --7369
--计算所得税
V_TAX_SAL := V_SAL * C_TAX_RATE;
--输出
DBMS_OUTPUT.PUT_LINE('雇员姓名：' || V_ENAME || '工资：' || V_SAL || ' 交税：' || V_TAX_SAL);
END;
/
四、标量(scalar)——使用%type类型
对于上面的pl/sql块有一个问题：就是如果员工的姓名超过了5个字符的话，就会有“ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小”错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。
我们看看这个怎么使用：标识符名 表名.列名%type;
比如上例的v_ename，这样定义： v_ename emp.ename%type;
set serveroutput on; --打开输出选项
DECLARE
--税率为0.03
C_TAX_RATE NUMBER(3, 2) :=0.03;
--雇员姓名
V_ENAME emp.ename%TYPE;--推荐使用%type类型
--工资
V_SAL NUMBER(7, 2);
--个人所得税
V_TAX_SAL NUMBER(7, 2);
BEGIN
--执行
SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO=&empno; --7777
--计算所得税
V_TAX_SAL := V_SAL * C_TAX_RATE;
--输出
DBMS_OUTPUT.PUT_LINE('雇员姓名：' || V_ENAME || '工资：' || V_SAL || ' 交税：' || V_TAX_SAL);
END;
/
五、复合变量(composite)——介绍
用于存放多个值的变量。主要包括这几种：
1)、pl/sql记录
2)、pl/sql表
3)、嵌套表
4)、varray
六、复合类型——pl/sql记录
类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)如下：
set serveroutput on; --打开输出选项
DECLARE
--定义一个pl/sql记录类型emp_record_type，
--类型包含3个数据NAME, SALARY, TITLE。说白了，就是一个类型可以存放3个数据，主要是为了方便管理
TYPE EMP_RECORD_TYPE IS RECORD(
NAME EMP.ENAME%TYPE,
SALARY EMP.SAL%TYPE,
TITLE EMP.JOB%TYPE);
--定义了一个sp_record变量，这个变量的类型是emp_record_type
SP_RECORD EMP_RECORD_TYPE;
BEGIN
SELECT ENAME, SAL, JOB INTO SP_RECORD FROM EMP WHERE EMPNO = 7788;
DBMS_OUTPUT.PUT_LINE('员工名:' || SP_RECORD.NAME || '工资：' || SP_RECORD.SALARY);
END;
/
七、复合类型——pl/sql表
相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。实例如下：
方法一(推荐)：
set serveroutput on; --打开输出选项
DECLARE
--定义了一个pl/sql表类型sp_table_type，该类型是用于存放EMP.ENAME%TYPE
--INDEX BY VARCHAR2(20)表示下标是字符串
TYPE SP_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE INDEX BY VARCHAR2(20);
--定义了一个sp_table变量，这个变量的类型是sp_table_type
SP_TABLE SP_TABLE_TYPE;
BEGIN
SELECT ENAME, sal INTO SP_TABLE('ename'), SP_TABLE('sal') FROM EMP WHERE EMPNO = 7788;
DBMS_OUTPUT.PUT_LINE('员工名:' || SP_TABLE('ename')||'工资：'||SP_TABLE('sal'));
END;
/
方法二：
set serveroutput on; --打开输出选项
DECLARE
--定义了一个pl/sql 表类型sp_table_type，该类型是用于存放EMP.ENAME%TYPE
--index by binary_integer表示下标是整数
TYPE SP_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER; --注意binary_integer如果换为integer就会报错，知道的朋友欢迎告诉我下
--定义了一个sp_table变量，这个变量的类型是sp_table_type
SP_TABLE SP_TABLE_TYPE;
BEGIN
SELECT ENAME,sal INTO SP_TABLE(-1),SP_TABLE(-2) FROM EMP WHERE EMPNO = 7788;
DBMS_OUTPUT.PUT_LINE('员工名:' || SP_TABLE(-1)||'工资：'||SP_TABLE(-2));
END;
/
说明：
sp_table_type是pl/sql表类型
emp.ename%type 指定了表的元素的类型和长度
sp_table 为pl/sql表变量
sp_table(0) 则表示下标为0的元素
注意：如果把select ename into sp_table(-1) from emp where empno = 7788；变成select ename into sp_table(-1) from emp;则运行时会出现错误，错误如下：ORA-01422:实际返回的行数超出请求的行数
解决方法是：使用参照变量(这里不讲)
八、复合变量——嵌套表(nested table)
复合变量——变长数组(varray)
省略
九、参照变量——介绍
参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。推荐使用游标变量。
十、参照变量——ref cursor游标变量
使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open 时）需要指定select语句，这样一个游标与一个select语句结合了。实例如下：
1.请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。
2.在1的基础上，如果某个员工的工资低于200元，就添加100元。
SET serveroutput ON;
DECLARE
--定义游标
TYPE sp_emp_cursor IS REF CURSOR;
--定义一个游标变量
sp sp_emp_cursor;
--定义变量
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
OPEN sp FOR SELECT e.ename, e.sal FROM emp e WHERE e.deptno=10;
--方法一 loop循环
/*
LOOP
FETCH sp INTO v_ename, v_sal;
EXIT WHEN sp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('名字：' || V_ENAME || ' 工资：' || V_SAL);
END LOOP;*/
--方法二 while循环
/*
WHILE 1=1 LOOP
FETCH sp INTO v_ename, v_sal;
EXIT WHEN sp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('名字：' || V_ENAME || ' 工资：' || V_SAL);
END LOOP;*/
--方法三 for循环
FOR cur IN (SELECT e.ename, e.sal FROM emp e WHERE e.deptno=10) LOOP
DBMS_OUTPUT.PUT_LINE('名字：' || cur.ename || ' 工资：' || cur.sal);
END LOOP;
END;
/
二十五、oracle pl/sql进阶--控制结构(分支，循环，控制)
一、pl/sql的进阶--控制结构
在任何计算机语言(c，java，pascal)都有各种控制语句(条件语句，循环结构，顺序控制结构...)，在pl/sql中也存在这样的控制结构。
在本部分学习完成后，希望大家达到：
1.使用各种if语句
2.使用循环语句
3.使用控制语句——goto和null(goto语句不推荐使用)；
二、条件分支语句
pl/sql中提供了三种条件分支语句if—then，if–then–else，if–then–else if–then。
这里我们可以和java语句进行一个比较。
1)、简单的条件判断if–then
问题：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该员工工资增加10%。
SET serveroutput ON;
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS
--定义
V_SAL EMP.SAL%TYPE;
BEGIN
--执行
SELECT SAL INTO V_SAL FROM EMP WHERE ENAME = SPNAME;
--判断
IF V_SAL < 2000 THEN
UPDATE EMP SET SAL = SAL + SAL * 0.1 WHERE ENAME = SPNAME;
COMMIT;
END IF;
END;
/
--调用存储过程
exec SP_PRO6('ALLEN');
2)、二重条件分支 if–then–else
问题：编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0就在原来的基础上增加100；如果补助为0就把补助设为200；
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS
--定义
V_COMM EMP.COMM%TYPE;
BEGIN
--执行
SELECT COMM INTO V_COMM FROM EMP WHERE ENAME = SPNAME;
--判断
IF V_COMM <> 0 THEN
UPDATE EMP SET COMM = COMM + 100 WHERE ENAME = SPNAME;
ELSE
UPDATE EMP SET COMM = COMM + 200 WHERE ENAME = SPNAME;
END IF;
COMMIT;
END;
/
--调用存储过程
exec SP_PRO6('ALLEN');
3)、多重条件分支 if–then–ELSIF–then
问题：编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就给他的工资增加1000，如果该雇员的职位是MANAGER 就给他的工资增加500，其它职位的雇员工资增加200。
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNO NUMBER) IS
--定义
V_JOB EMP.JOB%TYPE;
BEGIN
--执行
SELECT JOB INTO V_JOB FROM EMP WHERE EMPNO = SPNO;
IF V_JOB = 'PRESIDENT' THEN
UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = SPNO;
ELSIF V_JOB = 'MANAGER' THEN
UPDATE EMP SET SAL = SAL + 500 WHERE EMPNO = SPNO;
ELSE
UPDATE EMP SET SAL = SAL + 200 WHERE EMPNO = SPNO;
END IF;
COMMIT;
END;
/
--调用存储过程
exec SP_PRO6(7499);
三、循环语句–loop
是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop结尾，这种循环至少会被执行一次。
案例：现有一张表users，表结构如下：
用户vid | 用户名 uname
CREATE TABLE USERS(
vid NUMBER(5),
uname VARCHAR2(30)
);
请编写一个过程，可以输入用户名，并循环添加10个用户到users表中，用户编号从1开始增加。
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS
--定义 :=表示赋值
V_NUM NUMBER := 1;
BEGIN
LOOP
INSERT INTO USERS VALUES (V_NUM, SPNAME);
--判断是否要退出循环
EXIT WHEN V_NUM = 10;
--自增
V_NUM := V_NUM + 1;
END LOOP;
COMMIT;
END;
/
--调用存储过程
EXEC SP_PRO6('ALLEN');
四、循环语句–while循环
基本循环至少要执行循环体一次，而对于while循环来说，只有条件为true时，才会执行循环体语句，while循环以while...loop开始，以end loop 结束。
案例：现有一张表users，表结构如下：
用户vid | 用户名 uname
问题：请编写一个过程，可以输入用户名，并循环添加10个用户到users表中，用户编号从11开始增加。
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS
--定义 :=表示赋值
V_NUM NUMBER := 11;
BEGIN
WHILE V_NUM <= 20 LOOP
--执行
INSERT INTO USERS VALUES (V_NUM, SPNAME);
V_NUM := V_NUM + 1;
END LOOP;
COMMIT;
END;
/
--调用存储过程
EXEC SP_PRO6('ALLEN');
五、循环语句–for循环
基本for循环的基本结构如下
CREATE OR REPLACE PROCEDURE SP_PRO6 IS--注意如果无参记得不要加()
BEGIN
FOR I IN REVERSE 1 .. 10 LOOP --REVERSE反转函数，表示I从10到1递减，去掉REVERSE表示I从1到10递增
INSERT INTO USERS VALUES (I, 'shunping');
END LOOP;
END;
/
--调用存储过程
EXEC SP_PRO6;
我们可以看到控制变量i，在隐含中就在不停地增加。
六、顺序控制语句–goto、null
1)、goto语句
goto语句用于跳转到特定符号去执行语句。注意由于使用goto语句会增加程序的复杂性，并使得应用程序可读性变差，所以在做一般应用开发时，建议大家不要使用goto语句。
基本语法如下goto lable，其中lable是已经定义好的标号名
set serveroutput on;
DECLARE
I INT := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('输出i=' || I);
IF I = 1 THEN
GOTO END_LOOP;
END IF;
I := I + 1;
END LOOP;
<<END_LOOP>>
DBMS_OUTPUT.PUT_LINE('循环结束');
END;
/
2)、null语句
null语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用null语句的主要好处是可以提高pl/sql的可读性。
SET serveroutput ON;
DECLARE
V_SAL EMP.SAL%TYPE;
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO = &NO;
IF V_SAL < 3000 THEN
UPDATE EMP SET COMM = SAL * 0.1 WHERE ENAME = V_ENAME;
dbms_output.put_line('1111');
ELSE
NULL;
dbms_output.put_line('2222');--不会被执行
END IF;
END;
/
二十六、oracle pl/sql 分页
一、无返回值的存储过程
古人云：欲速则不达，为了让大家伙比较容易接受分页过程编写，我还是从简单到复杂，循序渐进的给大家讲解。首先是掌握最简单的存储过程，无返回值的存储过程。 案例：现有一张表book，表结构如下：书号、书名、出版社。
CREATE TABLE book(
ID NUMBER(4),
book_name VARCHAR2(30),
publishing VARCHAR2(30)
);
请写一个过程，可以向book表添加书，要求通过java程序调用该过程。
--注意：in->表示这是一个输入参数，默认为in --out->表示一个输出参数
CREATE OR REPLACE PROCEDURE ADD_BOOK(ID IN NUMBER,
NAME IN VARCHAR2,
PUBLISHING IN VARCHAR2) IS
BEGIN
INSERT INTO BOOK VALUES (ID, NAME, PUBLISHING);
COMMIT;
END;
/
java程序调用该存储过程的代码
package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
/**
* 调用一个无返回值的存储过程
*
* @author jiqinlin
*
*/
public class ProcedureTest {
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
// 3.创建CallableStatement
CallableStatement cs = ct.prepareCall("call ADD_BOOK(?,?,?)");
//给?赋值
cs.setInt(1, 1);
cs.setString(2, "java");
cs.setString(3, "java出版社");
// 4.执行
cs.execute();
//5、关闭
cs.close();
ct.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
二、有返回值的存储过程(非列表)
案例：编写一个存储过程，可以输入雇员的编号，返回该雇员的姓名。
--输入和输出的存储过程
CREATE OR REPLACE PROCEDURE SP_PROC(SPNO IN NUMBER, SPNAME OUT VARCHAR2) IS
BEGIN
SELECT ENAME INTO SPNAME FROM EMP WHERE EMPNO = SPNO;
END;
/
java程序调用该存储过程的代码
package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
/**
* 调用一个无返回值的存储过程
*
* @author jiqinlin
*
*/
public class ProcedureTest {
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
// 3.创建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_proc(?,?)}");
//给第一个?赋值
cs.setInt(1,7788);
//给第二个?赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
//4、执行
cs.execute();
//取出返回值,要注意？的顺序
String name=cs.getString(2);
System.out.println("编号7788的名字："+name);
//5、关闭
cs.close();
ct.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。
--输入和输出的存储过程
CREATE OR REPLACE PROCEDURE SP_PROC(SPNO IN NUMBER,
SPNAME OUT VARCHAR2,
SPSAL OUT NUMBER,
SPJOB OUT VARCHAR2) IS
BEGIN
SELECT ENAME, SAL, JOB INTO SPNAME, SPSAL, SPJOB FROM EMP WHERE EMPNO = SPNO;
END;
/
java程序调用该存储过程的代码
package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
/**
* 调用一个无返回值的存储过程
*
* @author jiqinlin
*
*/
public class ProcedureTest {
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
// 3.创建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_proc(?,?,?,?)}");
//给第一个?赋值
cs.setInt(1,7788);
//给第二个?赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
//给第三个？赋值
cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);
//给第四个？赋值
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
//4、执行
cs.execute();
//取出返回值,要注意？的顺序
String name=cs.getString(2);
double sal=cs.getDouble(3);
String job=cs.getString(4);
System.out.println("编号7788的名字："+name+"，职位："+job+"，薪水："+sal+"");
//5、关闭
cs.close();
ct.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
三、有返回值的存储过程(列表[结果集])
案例：编写一个存储过程，输入部门号，返回该部门所有雇员信息。
该题分析如下：由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了。所以要分两部分：
1)、建立一个包，在该包中我们定义类型test_cursor，它是个游标。
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE TEST_CURSOR IS REF CURSOR;
END TESTPACKAGE;
/
2)、建立存储过程。
CREATE OR REPLACE PROCEDURE SP_PROC(SPNO IN NUMBER,
P_CURSOR OUT TESTPACKAGE.TEST_CURSOR) IS
BEGIN
OPEN P_CURSOR FOR
SELECT * FROM EMP WHERE DEPTNO = SPNO;
END SP_PROC;
/
3)、如何在java 程序中调用该过程
package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
/**
* 调用一个无返回值的存储过程
*
* @author jiqinlin
*
*/
public class ProcedureTest {
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
// 3.创建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_proc(?,?)}");
//给第一个?赋值
cs.setInt(1,10);
//给第二个?赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
//4、执行
cs.execute();
//得到结果集
ResultSet rs = (ResultSet) cs.getObject(2);
while (rs.next()) {
System.out.println(rs.getInt(1) + " " + rs.getString(2));
}
//5、关闭
rs.close();
cs.close();
ct.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
四、编写分页过程
有了上面的基础，相信大家可以完成分页存储过程了。
要求，请大家编写一个存储过程，要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数，和返回的结果集。
--ROWNUM用法
SELECT o.*, ROWNUM RN FROM (SELECT * FROM EMP) o WHERE ROWNUM <= 10;
----oracle分页sql语句；在分页时，大家可以把下面的sql语句当做一个模板使用
SELECT *
FROM (SELECT o.*, ROWNUM RN FROM (SELECT * FROM EMP) o WHERE ROWNUM <= 10)
WHERE RN >= 6;
1)、开发一个包
建立一个包，在该包中定义类型为test_cursor的游标。
--建立一个包
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE TEST_CURSOR IS REF CURSOR;
END TESTPACKAGE;
/
--开始编写分页的过程
CREATE OR REPLACE PROCEDURE FENYE(TABLENAME IN VARCHAR2,
PAGESIZE IN NUMBER, --每页显示记录数
PAGENOW IN NUMBER, --页数
MYROWS OUT NUMBER, --总记录数
MYPAGECOUNT OUT NUMBER, --总页数
P_CURSOR OUT TESTPACKAGE.TEST_CURSOR) IS --返回的记录集
--定义部分
--定义sql语句字符串
V_SQL VARCHAR2(1000);
--定义两个整数
V_BEGIN NUMBER := (PAGENOW - 1) * PAGESIZE + 1;
V_END NUMBER := PAGENOW * PAGESIZE;
BEGIN
--执行部分
V_SQL := 'select * from (select t1.*, rownum rn from (select * from ' || TABLENAME || ') t1 where rownum<=' || V_END || ') where rn>=' || V_BEGIN;
--把游标和sql关联
OPEN P_CURSOR FOR V_SQL;
--计算myrows和myPageCount
--组织一个sql语句
V_SQL := 'select count(*) from ' || TABLENAME;
--执行sql,并把返回的值，赋给myrows；
EXECUTE ImMEDIATE V_SQL INTO MYROWS; --它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前，
--EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能，较之以前它相当容易编码.
--尽管DBMS_SQL仍然可用，但是推荐使用EXECUTE IMMEDIATE,因为它获的收益在包之上。
--计算myPageCount
--if myrows%Pagesize=0 then 这样写是错的
IF MOD(MYROWS, PAGESIZE) = 0 THEN
MYPAGECOUNT := MYROWS/PAGESIZE;
ELSE
MYPAGECOUNT := MYROWS/PAGESIZE + 1;
END IF;
--关闭游标
--CLOSE P_CURSOR; --不要关闭，否则java调用该存储过程会报错
END;
/
java调用分页代码
package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
/**
* 调用一个无返回值的存储过程
*
* @author jiqinlin
*
*/
public class ProcedureTest {
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
// 3.创建CallableStatement
CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}");
cs.setString(1, "emp"); //表名
cs.setInt(2, 5); //每页显示记录数
cs.setInt(3, 1);//页数
// 注册总记录数
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER); //总记录数
// 注册总页数
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER); //总页数
// 注册返回的结果集
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR); //返回的记录集
// 4、执行
cs.execute();
// 得到结果集
// 取出总记录数 /这里要注意，getInt(4)中4，是由该参数的位置决定的
int rowNum = cs.getInt(4);
int pageCount = cs.getInt(5);
ResultSet rs = (ResultSet) cs.getObject(6);
// 显示一下，看看对不对
System.out.println("rowNum=" + rowNum);
System.out.println("总页数=" + pageCount);
while (rs.next()) {
System.out.println("编号：" + rs.getInt(1) +
" 名字：" + rs.getString(2) +
" 工资：" + rs.getFloat(6));
}
// 5、关闭
//rs.close();
cs.close();
ct.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
二十七、oracle 例外
一、例外分类
oracle将例外分为预定义例外、非预定义例外和自定义例外三种。
1)、预定义例外用于处理常见的oracle错误。
2)、非预定义例外用于处理预定义例外不能处理的例外。
3)、自定义例外用于处理与oracle错误无关的其它情况。
下面通过一个小案例演示如果不处理例外看会出现什么情况？
编写一个存储过程，可接收雇员的编号，并显示该雇员的姓名。
sql代码如下：
SET SERVEROUTPUT ON;
DECLARE
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = &GNO;
DBMS_OUTPUT.PUT_LINE('名字：' || V_ENAME);
END;
/
随便输入不存在的编号，回车，会抛出如下异常：
ORA-01403: 未找到数据
ORA-06512: 在line 6
例外捕获的sql代码如下：
SET SERVEROUTPUT ON;
DECLARE
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = &GNO;
DBMS_OUTPUT.PUT_LINE('名字：' || V_ENAME);
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('编号未找到！');
END;
/
随便输入不存在的编号，回车，会友情提示：编号未找到！
二、处理预定义例外
预定义例外是由pl/sql所提供的系统例外。当pl/sql应用程序违反了oracle规定的限制时，则会隐含的触发一个内部例外。pl/sql为开发人员提供了二十多个预定义例外。我们给大家介绍常用的例外。
1)、case_not_found预定义例外
在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found例外：
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNO NUMBER) IS
V_SAL EMP.SAL%TYPE;
BEGIN
SELECT SAL INTO V_SAL FROM EMP WHERE EMPNO = SPNO;
CASE
WHEN V_SAL < 1000 THEN
UPDATE EMP SET SAL = SAL + 100 WHERE EMPNO = SPNO;
WHEN V_SAL < 2000 THEN
UPDATE EMP SET SAL = SAL + 200 WHERE EMPNO = SPNO;
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('case语句没有与' || V_SAL || '相匹配的条件');
END;
/
--调用存储过程
SQL> EXEC SP_PRO6(7369);
case语句没有与4444相匹配的条件
2)、cursor_already_open预定义例外
当重新打开已经打开的游标时，会隐含的触发cursor_already_open例外
DECLARE
CURSOR EMP_CURSOR IS
SELECT ENAME, SAL FROM EMP;
BEGIN
OPEN EMP_CURSOR; --声明时游标已打开，所以没必要再次打开
FOR EMP_RECORD1 IN EMP_CURSOR LOOP
DBMS_OUTPUT.PUT_LINE(EMP_RECORD1.ENAME);
END LOOP;
EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
DBMS_OUTPUT.PUT_LINE('游标已经打开');
END;
/
3)、dup_val_on_index预定义例外
在唯一索引所对应的列上插入重复的值时，会隐含的触发例外
BEGIN
INSERT INTO DEPT VALUES (10, '公关部', '北京');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('在deptno列上不能出现重复值');
END;
/
4)、invalid_cursorn预定义例外
当试图在不合法的游标上执行操作时，会触发该例外
例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标。则会触发该例外
DECLARE
CURSOR EMP_CURSOR IS
SELECT ENAME, SAL FROM EMP;
EMP_RECORD EMP_CURSOR%ROWTYPE;
BEGIN
--open emp_cursor; --打开游标
FETCH EMP_CURSOR INTO EMP_RECORD;
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);
CLOSE EMP_CURSOR;
EXCEPTION
WHEN INVALID_CURSOR THEN
DBMS_OUTPUT.PUT_LINE('请检测游标是否打开');
END;
/
5)、invalid_number预定义例外
当输入的数据有误时，会触发该例外
比如：数字100写成了loo就会触发该例外
SET SERVEROUTPUT ON;
BEGIN
UPDATE EMP SET SAL = SAL + 'AAA';
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('输入的数字不正确');
END;
/
6)、no_data_found预定义例外
下面是一个pl/sql 块，当执行select into没有返回行，就会触发该例外
SET serveroutput ON;
DECLARE
V_SAL EMP.SAL%TYPE;
BEGIN
SELECT SAL INTO V_SAL FROM EMP WHERE ENAME = 'ljq';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('不存在该员工');
END;
/
7)、too_many_rows预定义例外
当执行select into语句时，如果返回超过了一行，则会触发该例外。
DECLARE
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('返回了多行');
END;
/
8)、zero_divide预定义例外
当执行2/0语句时，则会触发该例外
9)、value_error预定义例外
当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外value_error
其它预定义例外(这些例外不是在pl/sql里触发的，而是在用oracle时触发的，所以取名叫其它预定义例外)
1、login_denied
当用户非法登录时，会触发该例外
2、not_logged_on
如果用户没有登录就执行dml操作，就会触发该例外
3、storage_error
如果超过了内存空间或是内存被损坏，就触发该例外
4、timeout_on_resource
如果oracle在等待资源时，出现了超时就触发该例外
三、非预定义例外
非预定义例外用于处理与预定义例外无关的oracle错误。使用预定义例外只能处理21个oracle 错误，而当使用pl/sql开发应用程序时，可能会遇到其它的一些oracle错误。比如在pl/sql块中执行dml语句时，违反了约束规定等等。在这样的情况下，也可以处理oracle的各种例外，因为非预定义例外用的不多，这里我就不举例了。
四、处理自定义例外
预定义例外和自定义例外都是与oracle错误相关的，并且出现的oracle 错误会隐含的触发相应的例外；而自定义例外与oracle 错误没有任何关联，它是由开发人员为特定情况所定义的例外.
问题：请编写一个pl/sql 块，接收一个雇员的编号，并给该雇员工资增加1000元，如果该雇员不存在，请提示。
CREATE OR REPLACE PROCEDURE EX_TEST(SPNO NUMBER) IS
BEGIN
UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = SPNO;
END;
/
--调用存储过程，
EXEC EX_TEST(56);
这里，编号为56 是不存在的，刚才的报异常了，为什么现在不报异常呢？
因为刚才的是select语句
怎么解决这个问题呢？ 修改代码，如下：
--自定义例外
CREATE OR REPLACE PROCEDURE EX_TEST(SPNO NUMBER) IS
--定义一个例外
MYEX EXCEPTION;
BEGIN
--更新用户sal
UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = SPNO;
--sql%notfound 这是表示没有update
--raise myex;触发myex
IF SQL%NOTFOUND THEN RAISE MYEX;
END IF;
EXCEPTION
WHEN MYEX THEN DBMS_OUTPUT.PUT_LINE('没有更新任何用户');
END;
/
现在再测试一次：
SQL> exec ex_test(56);
没有更新任何用户
二十八、oracle 视图
一、介绍
视图是一张虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。(视图不是真实存在磁盘上的)
二、视图与表的区别
1、表需要占用磁盘空间，视图不需要
2、视图不能添加索引(所以查询速度略微慢点)
3、使用视图可以简化，复杂查询
4、视图的使用利于提高安全性
比如：不同用户查看不同视图
三、创建/修改视图
1、创建视图
create view 视图名 as select 语句 [with read only]
2、创建或修改视图
create or replace view 视图名 as select 语句 [with read only]
3、删除视图
drop view 视图名
注意：当表结构国语复杂，请使用视图吧！
二十九、oracle 触发器
一、触发器简介
触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。因此触发器不需要人为的去调用，也不能调用。然后，触发器的触发条件其实在你定义的时候就已经设定好了。这里面需要说明一下，触发器可以分为语句级触发器和行级触发器。详细的介绍可以参考网上的资料，简单的说就是语句级的触发器可以在某些语句执行前或执行后被触发。而行级触发器则是在定义的了触发的表中的行数据改变时就会被触发一次。
具体举例：
1、 在一个表中定义的语句级的触发器，当这个表被删除时，程序就会自动执行触发器里面定义的操作过程。这个就是删除表的操作就是触发器执行的条件了。
2、 在一个表中定义了行级的触发器，那当这个表中一行数据发生变化的时候，比如删除了一行记录，那触发器也会被自动执行了。
二、触发器语法
触发器的语法：
create [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
pl/sql语句
end
其中：
触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
触发时间：指明触发器何时执行，该值可取：
before：表示在数据库动作之前触发器执行;
after：表示在数据库动作之后触发器执行。
触发事件：指明哪些数据库动作会触发此触发器：
insert：数据库插入会触发此触发器;
update：数据库修改会触发此触发器;
delete：数据库删除会触发此触发器。
表 名：数据库触发器所在的表。
for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。
触发器能实现如下功能：
功能：
1、 允许/限制对表的修改
2、 自动生成派生列，比如自增字段
3、 强制数据一致性
4、 提供审计和日志记录
5、 防止无效的事务处理
6、 启用复杂的业务逻辑
举例
1)、下面的触发器在更新表tb_emp之前触发，目的是不允许在周末修改表：
create or replace trigger auth_secure before insert or update or DELETE
on tb_emp
begin
IF(to_char(sysdate,'DY')='星期日') THEN
RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表tb_emp');
END IF;
END;
/
2)、使用触发器实现序号自增或者每次都调用方法
创建一个测试表：
create table tab_user(
id number(11) primary key,
username varchar(50),
password varchar(50)
);
Create Sequence
你首先要有create sequence或者create any sequence权限，模板为：
1. CREATE SEQUENCE my_seq
2. increment by 1 -- 每次加几个
3. start with 1 -- 从1开始计数
4. NOMAXvalue -- 不设置最大值 (maxvalue 99999999)
5. nocycle -- 一直累加，不循环
6. cache 10; --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，
7.
8. 也可以设置为---------NOCACHE
创建一个序列：
create sequence my_seq increment by 1 start with 1 nomaxvalue nocycle cache 20;
创建一个触发器：
CREATE OR REPLACE TRIGGER MY_TGR
BEFORE INSERT ON TAB_USER
FOR EACH ROW--对表的每一行触发器执行一次
DECLARE
NEXT_ID NUMBER;
BEGIN
SELECT MY_SEQ.NEXTVAL INTO NEXT_ID FROM DUAL;
:NEW.ID := NEXT_ID; --:NEW表示新插入的那条记录
END;
向表插入数据:
insert into tab_user(username,password) values('admin','admin');
insert into tab_user(username,password) values('fgz','fgz');
insert into tab_user(username,password) values('test','test');
COMMIT;
查询表结果：SELECT * FROM TAB_USER;
--------使用调用方法
一旦定义了my_seq，你就可以用currval，nextval
currval=返回 sequence的当前值
nextval=增加sequence的值，然后返回 sequence 值
比如：
S_Test.CURRVAL
S_Test.NEXTVAL
可以使用sequence的地方：
- 不包含子查询、snapshot、view的 select 语句
- INSERT语句的子查询中
- NSERT语句的values中
- UPDATE 的 set中
insert into tab_user(username,password) values(my_seq.Nextval，'admin','admin');
insert into tab_user(username,password) values(my_seq.Nextval，'fgz','fgz');
insert into tab_user(username,password) values(my_seq.Nextval，'test','test');
COMMIT;
3)、当用户对test表执行DML语句时，将相关信息记录到日志表
--创建测试表
CREATE TABLE test(
t_id NUMBER(4),
t_name VARCHAR2(20),
t_age NUMBER(2),
t_sex CHAR
);
--创建记录测试表
CREATE TABLE test_log(
l_user VARCHAR2(15),
l_type VARCHAR2(15),
l_date VARCHAR2(30)
);
创建触发器：
--创建触发器
CREATE OR REPLACE TRIGGER TEST_TRIGGER
AFTER DELETE OR INSERT OR UPDATE ON TEST
DECLARE
V_TYPE TEST_LOG.L_TYPE%TYPE;
BEGIN
IF INSERTING THEN
--INSERT触发
V_TYPE := 'INSERT';
DBMS_OUTPUT.PUT_LINE('记录已经成功插入，并已记录到日志');
ELSIF UPDATING THEN
--UPDATE触发
V_TYPE := 'UPDATE';
DBMS_OUTPUT.PUT_LINE('记录已经成功更新，并已记录到日志');
ELSIF DELETING THEN
--DELETE触发
V_TYPE := 'DELETE';
DBMS_OUTPUT.PUT_LINE('记录已经成功删除，并已记录到日志');
END IF;
INSERT INTO TEST_LOG
VALUES
(USER, V_TYPE, TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss')); --USER表示当前用户名
END;
/
--下面我们来分别执行DML语句
INSERT INTO test VALUES(101,'zhao',22,'M');
UPDATE test SET t_age = 30 WHERE t_id = 101;
DELETE test WHERE t_id = 101;
--然后查看效果
SELECT * FROM test;
SELECT * FROM test_log;
运行结果如下：
3)、创建触发器，它将映射emp表中每个部门的总人数和总工资
--创建映射表
CREATE TABLE dept_sal AS
SELECT deptno, COUNT(empno) total_emp, SUM(sal) total_sal
FROM scott.emp
GROUP BY deptno;
--创建触发器
CREATE OR REPLACE TRIGGER EMP_INFO
AFTER INSERT OR UPDATE OR DELETE ON scott.EMP
DECLARE
CURSOR CUR_EMP IS
SELECT DEPTNO, COUNT(EMPNO) AS TOTAL_EMP, SUM(SAL) AS TOTAL_SAL FROM scott.EMP GROUP BY DEPTNO;
BEGIN
DELETE DEPT_SAL; --触发时首先删除映射表信息
FOR V_EMP IN CUR_EMP LOOP
--DBMS_OUTPUT.PUT_LINE(v_emp.deptno || v_emp.total_emp || v_emp.total_sal);
--插入数据
INSERT INTO DEPT_SAL
VALUES
(V_EMP.DEPTNO, V_EMP.TOTAL_EMP, V_EMP.TOTAL_SAL);
END LOOP;
END;
--对emp表进行DML操作
INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000);
SELECT * FROM dept_sal;
DELETE EMP WHERE empno=123;
SELECT * FROM dept_sal;
显示结果如下：
4)、创建触发器，用来记录表的删除数据
--创建表
CREATE TABLE employee(
id VARCHAR2(4) NOT NULL,
name VARCHAR2(15) NOT NULL,
age NUMBER(2) NOT NULL,
sex CHAR NOT NULL
);
--插入数据
INSERT INTO employee VALUES('e101','zhao',23,'M');
INSERT INTO employee VALUES('e102','jian',21,'F');
--创建记录表(包含数据记录)
CREATE TABLE old_employee AS SELECT * FROM employee;
--创建触发器
CREATE OR REPLACE TRIGGER TIG_OLD_EMP
AFTER DELETE ON EMPLOYEE
FOR EACH ROW --语句级触发，即每一行触发一次
BEGIN
INSERT INTO OLD_EMPLOYEE VALUES (:OLD.ID, :OLD.NAME, :OLD.AGE, :OLD.SEX); --:old代表旧值
END;
/
--下面进行测试
DELETE employee;
SELECT * FROM old_employee;
5)、创建触发器，利用视图插入数据
--创建表
CREATE TABLE tab1 (tid NUMBER(4) PRIMARY KEY,tname VARCHAR2(20),tage NUMBER(2));
CREATE TABLE tab2 (tid NUMBER(4),ttel VARCHAR2(15),tadr VARCHAR2(30));
--插入数据
INSERT INTO tab1 VALUES(101,'zhao',22);
INSERT INTO tab1 VALUES(102,'yang',20);
INSERT INTO tab2 VALUES(101,'13761512841','AnHuiSuZhou');
INSERT INTO tab2 VALUES(102,'13563258514','AnHuiSuZhou');
--创建视图连接两张表
CREATE OR REPLACE VIEW tab_view AS SELECT tab1.tid,tname,ttel,tadr FROM tab1,tab2 WHERE tab1.tid = tab2.tid;
--创建触发器
CREATE OR REPLACE TRIGGER TAB_TRIGGER
INSTEAD OF INSERT ON TAB_VIEW
BEGIN
INSERT INTO TAB1 (TID, TNAME) VALUES (:NEW.TID, :NEW.TNAME);
INSERT INTO TAB2 (TTEL, TADR) VALUES (:NEW.TTEL, :NEW.TADR);
END;
/
--现在就可以利用视图插入数据
INSERT INTO tab_view VALUES(106,'ljq','13886681288','beijing');
--查询
SELECT * FROM tab_view;
SELECT * FROM tab1;
SELECT * FROM tab2;
6)、创建触发器，比较emp表中更新的工资
--创建触发器
set serveroutput on;
CREATE OR REPLACE TRIGGER SAL_EMP
BEFORE UPDATE ON EMP
FOR EACH ROW
BEGIN
IF :OLD.SAL > :NEW.SAL THEN
DBMS_OUTPUT.PUT_LINE('工资减少');
ELSIF :OLD.SAL < :NEW.SAL THEN
DBMS_OUTPUT.PUT_LINE('工资增加');
ELSE
DBMS_OUTPUT.PUT_LINE('工资未作任何变动');
END IF;
DBMS_OUTPUT.PUT_LINE('更新前工资 ：' || :OLD.SAL);
DBMS_OUTPUT.PUT_LINE('更新后工资 ：' || :NEW.SAL);
END;
/
--执行UPDATE查看效果
UPDATE emp SET sal = 3000 WHERE empno = '7788';
运行结果如下：
7)、创建触发器，将操作CREATE、DROP存储在log_info表
--创建表
CREATE TABLE log_info(
manager_user VARCHAR2(15),
manager_date VARCHAR2(15),
manager_type VARCHAR2(15),
obj_name VARCHAR2(15),
obj_type VARCHAR2(15)
);
--创建触发器
set serveroutput on;
CREATE OR REPLACE TRIGGER TRIG_LOG_INFO
AFTER CREATE OR DROP ON SCHEMA
BEGIN
INSERT INTO LOG_INFO
VALUES
(USER,
SYSDATE,
SYS.DICTIONARY_OBJ_NAME,
SYS.DICTIONARY_OBJ_OWNER,
SYS.DICTIONARY_OBJ_TYPE);
END;
/
--测试语句
CREATE TABLE a(id NUMBER);
CREATE TYPE aa AS OBJECT(id NUMBER);
DROP TABLE a;
DROP TYPE aa;
--查看效果
SELECT * FROM log_info;
--相关数据字典-----------------------------------------------------
SELECT * FROM USER_TRIGGERS;
--必须以DBA身份登陆才能使用此数据字典
SELECT * FROM ALL_TRIGGERS;SELECT * FROM DBA_TRIGGERS;
--启用和禁用
ALTER TRIGGER trigger_name DISABLE;
ALTER TRIGGER trigger_name ENABLE;
扫一扫
4844
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
