（O管理）O数据库学习日记实
用性最强的 O学习总结
Oracle数据库学习
Oracle数据库学习 1
1 基本使用 5
常用命令 5
数据库连接命令 5
5
配置 Oracle数据库监听 Assistant6
[ord] 修改密码命令 6
显示登录用户 6
清屏 6
退出命令 6
文件操作命令 6
& 交互式命令 6
显示和设置环境的变量 7
2 用户管理 7
用户管理 7
user 创建用户 7
修改密码 7
user删除用户 7
赋予和收回权限 8
管理用户口令 8
给账户解锁 9
终止口令 9
口令历史 9
删除 profile文件 9
3 数据类型 10
字符型 10
定长字符 10
变长字符 10
字符型大对象 10
数值型 10
数值型 10
日期 10
一般日期（年、月、日，时、分、秒）10
精确时间 10
图片类型 11
可存储图片、视频、声音 11
4 表格管理 11
创建表(注意字母大小写，一般为大写)11
添加一个字段 11
修改字段的长度 11
删除一个字段 11
修改表的名字 12
删除表 12
所有字段都插入数据 12
插入部分字段 12
插入空值 12
查询空值 12
修改字段 12
删除数据 13
5 简单表查询 13
查询表的结构 13
查询表的指定列和所有列 13
取消重复行 13
疯狂复制 13
中使用列的别名 14
中使用算术表达式 14
使用 NVL函数处理 NULL值 14
“||”字符串连接符 14
查询条件子句 14
操作符 14
条件中使用 IN15
使用 is null的操作符 15
使用逻辑操作符号 15
by排序语句 15
使用列的别名排序 15
6复杂表查询 16
数据分组 max，min，avg，sum，count16
by和 having子句 16
数据分组总结 16
7 多表查询 17
多表查询，between and语句 17
自连接 17
8 子查询 17
单行子查询 17
多行子查询 18
使用 any操作符查询 18
多列子查询 18
子句中使用子查询 18
分页查询 18
用查询结果创建新表 19
合并查询 19
9Java操作 Oracle20
桥连接,不能远程连接 20
引 sql包 20
加载驱动 20
得到连接 20
连接，允许远程连接 20
引 sql包 20
加载驱动 20
得到连接 20
10事物 21
使用子查询插入数据 21
使用子查询插入数据 21
中的事物 21
11函数 22
字符函数 22
(char):将字符串转化为小写的格式 22
(char):将字符串转化为大写的格式 22
(char):返回字符串的长度 22
(char,m,n):取字符串的子串 22
(char1,serch_string,replace_string)替换字符串 22
(char1,char2,[,n[,m]])取子串在字符串的位置 22
数学函数 23
(n,[m]) 四舍五入 23
(n,[m]) 截取数字 23
(m,n) 取摩 23
(n) 向下取最大整数 23
(n) 向上取最小整数 23
(n) 返回数字 n的绝对值 24
(n) 返回数字的反余弦值 24
(n) 返回数字的反正弦值 24
(n) 返回数字的反正切 24
(n) 返回数字的余弦值 24
(n) 返回 e的 n次幂 24
(m,n) 返回对数值 24
(m,n) 返回 m的 n 次幂 24
日期函数 24
_date函数 24
该函数返回系统时间；24
_months(d,n)；24
_day(d) 返回指定日期所在月份的最后一天 25
转换函数 25
_char转换函数 25
_date函数 26
系统函数 26
_context函数 26
12数据库管理 27
数据库管理员 27
管理数据库的用户主要有：sys和 system27
、Sysdba、Sysoper权限的用户 27
管理初始化参数 28
数据库（表）的逻辑备份和恢复 28
导出 28
导入 30
数据字典和动态性能视图 31
数据字典 31
数据字典-用户名、权限、角色 31
动态性能视图 32
13约束 32
创建约束 33
删除约束 34
显示约束信息 34
表级定义和列级定义 34
14索引、权限 35
索引 35
创建索引 35
索引使用原则 35
索引缺点分析 35
其他索引 35
显示表的所有索引 35
权限 36
系统权限 36
对象权限 36
15角色 37
预定义角色 38
角色 38
角色 38
角色 38
自定义角色 39
建立角色 39
角色授权 39
授予用户角色权限 39
删除角色 40
显示角色信息 40
精细访问控制 40
16plsql编程 41
41
41
42
42
42
43
44
45
46
46
50
53
60
63
基本使用
Oracle安装成功后，会默认生成三个用户
Sys用户：超级管理员权限最高它的角色 dba密码 change_on_install
System用户：是系统管理员权限也很高他的角色是 dbaoper密码 manager
Scott用户：普通用户密码是 tiger
Sys与 system区别：
Sys有 createdatabase的权限，而 system没有，其他相似，日常对 Oracle管理过程中使用 system
就够了。
常用命令
Connect/Disconnect数据库连接命令
Conn[ect]用户名/密码@网络服务名[assysdba/sysoper],当用特权用户身份连接时，必须带上
assysdba或是 assysoper;
Disc[onnect]该命令用来断开与当前数据库的连接。
PL/SQL连接数据库配置，Oracle客户端的配置文件
Oracle客户端的配制文件，默认会安装在“C:\Oracle\ora90\network\admin”目录下，名为
“”参考格式如下：
=即数据库名_数据库 IP地址
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))--HOST数据库 IP地址,PORT端口
)
(CONNECT_DATA=
(SID=YY)--YY数据库名
(SERVER=DEDICATED)
)
)
配置 Oracle数据库监听 Assistant
Passw[ord]修改密码命令
该命令用于修改用户的密码，如果想修改其他用户的密码，需要用 sys/system登录
Show显示登录用户
Showuser;显示当前登录用户名
Clear清屏
Clear;清屏
Exit退出命令
该命令会断开与数据库的连接，同时会退出 sql*plus。
文件操作命令
1) Start和@运行 sql脚本
如：sql>@d:\或者 sql>Startd:\
2) Edit 该命令可以编辑指定的 SQL脚本
如：Sql>editd:\
3) Spool该命令可以将 sql*plus屏幕上的内容输出到指定的文件中去(假脱机)
如：Sql>spoold:\并输入 sql>spooloff；
&交互式命令
&可以替代变量在执行时，需要用户输入。
如：SQL>selsct*fromempwherejob=’&工作’；
显示和设置环境的变量
可以用来控制输出的各种格式，setshow如果希望永久的保存相关的设置，可以去修改
脚本
1) Linsesize设置显示行的宽度，默认是 80个字符
Sql>showlinesize
Sql>setlinesize90
2) Pagesize 设置每页显示的行数目，默认是 14，用法和 linesize一样
用户管理
用户管理
Createuser创建用户
创建用户必须用 DBA 权限，或者拥有 alteruser系统权限，密码只能以字母开头,新创建的用户没有
任何权限，不能登录数据库，需要授权。
Createuserabcidentifiedbym123；--创建用户名为 abc，密码为 m123的用户
Password修改密码
给自己修改密码可以直接使用
Sql>password用户名
如果给别人修改密码则需要具有 dba的权限，或是拥有 alteruser的系统权限
Sql>alteruser用户名 identitiedby新密码
Dropuser删除用户
在删除用户时，如果要删除的用户，已经创建了表，那么就需要在删除时带一个参数 cascade，指删
除该用户下面所有的表，一般以 dba的身份去删除某个用户，如果用其他用户去删除用户则需要具有
dropuser的权限
Sql>Dropuser用户名[caseade]
赋予和收回权限
1) 授予权限命令 grant，常用的角色有 connect、dba、resources三种；
Resources角色可以在任何一个表空间建表；
Sql>grantconnecttoxiaoming;--给 xiaoming授予 connect角色的权限
SQL>revokesysdbafromxiaoming;--回收 xiaoming的 sysdba权限
2) 如何使用户可以管理其他用户的表，Select查询、insert插入、update修改、delete删除、all
全部、createindex；只有 sys、system和表的创建者才可以给其他用户授予表的管理权限(假设
emp为 Scott用户的表)；
Grantselectonemptoabc；--Scott用户将 emp表的查询权限授予给 abc用户
Grantupdateonemptoabc；--Scott用户将 emp表的修改权限授予给 abc用户
Grantallonemptoabc；--Scott用户将 emp表的所有权限授予给 abc用户
3) 回收权限命令 revoke，谁授予的权限谁收回
Revokeselectonemptoabc；--Scott用户将 emp表的查询权限从 abc用户收回
Revokeupdateonemptoabc；--Scott用户将 emp表的修改权限从 abc用户收回
Revokeallonemptoabc；--Scott用户将 emp表的所有权限从 abc用户收回
4) 被授予权限用户继续授予该权限给其他用户，
如果是对象权限，后面就加入 withgrantoption；
如果是系统权限，后面就加入 withadminoption；
如果收回某用户的权限则该用户授予给其他下级用户该权限将全部收回
Grantselectonemptoabcwithgrantoption；--Scott 用户将 emp表的查询权限授予给 abc用户，
并且让 abc用户继续给其他用户授权
；abc用户将 表的查询权限授予给 abc1用户
Profile管理用户口令
Profile 是口令限制，资源限制的命令集合，当建立数据库时，Oracle会自动建立名为 default
的 profile，当建立用户没有指定 profile选项，那 Oracle就会将 default分配给用户，
账户锁定:指定该账户登录时最多可以输入错误密码的次数，也可以指定用户锁定的时间(天)，
一般用 dba的身份去执行该命令。
案例：指定 abc账户登录时最多可以输入错误密码 3次，锁定的时间 2天
Sql>createprofilelock_alimitfailed_login_attempts3password_lock_time2;
Sql>alteruserabcprofilelock_a;--给用户 abc指定 profile选项
给账户解锁
Sql>alteruserabcaccountunlock;--给用户 abc解锁
终止口令
为了让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要 dba身份来操作。
案例:创建一个 profile文件，要求该用户每隔 10天要修改自家的登录密码，宽限期为 2天。
Sql>createprofilelock_a1limitpassword_life_time10password_grace_time2;
Sql>alteruserabcprofilelock_a1;
口令历史
如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史，这样 Oracle就会
将口令修改的信息存放到数据字典中，这样当用户修改密码时，Oracle就会对新旧密码进行比较，
当发现新旧密码一样时，就提示用户重新输入密码。
案例：
建立 profile文件,password_reuse_time//指定口令可重用时间，超过该时间可再次使用：
Sql>createprofilepassword_hlimitpassword_life_time10password_grace_time2password_re
use_time10;
分配给用户：
Sql>alteruserabcprofilepassword_h;
删除 profile文件
删除 profile文件，如果该profile已经分配给客户需在后面加参数 cascade，所有受到该 profile
文件限制的用户全部解除该限制。
Sql>dropprofilepassword_h[cascade];
数据类型
字符型
Char定长字符
char(10)定长字符最大 2000字符，字符数不足用空格不足，查询速度快，适合定长数据，如身
份证、手机号码等；
Varchar2变长字符
varchar(20)变长字符最大 4000字符；
Clob字符型大对象
clob(30000)字符型大对象，最大 4G，可以存图片、视频等；
数值型
Number数值型
number(5,2)可以表示-10的 38次方到 10的 38次方，如 number(5)表示 5位整数，number(5,2)表示
3 位整数 2位小数；
日期
Date一般日期（年、月、日，时、分、秒）
包含年月日和时分秒；
timestamp精确时间
时间可以精确到更小的单位
图片类型
Blob可存储图片、视频、声音
blob二进制数据，最大 4G，可以存图片、视频、声音等；
表格管理
创建表(注意字母大小写，一般为大写)
学生表：
createtablestudent(，--学生
xhnumber(4),--学号
xmvarchar2(20),--姓名
xbchar(2),--性别
birthdaydate,--出生日期
valnumber(7,2)--奖学金
);
班级表：
createtableclass(--班级
classid(4),--班级编号
name(20),--班级名称
);
添加一个字段
altertablestudentadd(classidnumber(2));
修改字段的长度
altertablestudentmodify(xmvarchar2(30));
删除一个字段
altertablestudentdropcolumnsal；
实际过程中不要轻易删除字段。
修改表的名字
renamestudenttostu;--将’STUDENT’修改为’STU’
删除表
droptablestudent;--删除’STUDENT’表
所有字段都插入数据
insertintostudentvalues('1003','张三','01-5月-05',10);
日期格式默认为：'DD-MON-YY'
该日期格式：altersessionsetnls_dateformat='YYYY-MM-DD';
日期格式可以任意修改，Y年份，M月份，D日；
插入部分字段
insertintostudent(xh,xm,sex)values('1004','李四','女');
插入空值
insertintostudent(xh,xm,sex,birthday)values('1005','杨慧','女'，null);
查询空值
select*fromstudentwherebirthdayisnull；--查询空值
select*fromstudentwherebirthdayisnotnull；--查询非空值
修改字段
updatestudentsetsex='男'wherexh='1004';--修改一个字段
updatestudentsetsex='男'，birthday='1980-04-01'wherexh='1004';--修改多个字段
例如：updatestudentsetsal=sal*='男';--所有男性员工薪水加 倍
updatestudentsetbirthdayisnullwherexh='1004';--修改为空值
删除数据
deletefromstudent；--删除所有记录，表结构还在，写日志，可以恢复的(回滚)，速度慢
deletefromstudentwherexh='1005';--删除一条记录
droptablestudent；--删除表结构和数据
truncatetablestudent;--删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速
度快；
savepointAA；--创建回滚点 AA
rollbacktoAA;--回滚到 AA点，需先创建回滚点
简单表查询
查询表的结构
Sql>descdept；--查看 dept表的结构
查询表的指定列和所有列
Select*fromemp;--查询全部列
Selectename，sal，jobfromemp；--查询指定列
注意：查询过程中尽量少用查询全部列，以节省查询时间，提高查询效率
Distinct取消重复行
Selectdistinctdeptno，jobfromemp；
疯狂复制
Insertintousers(userid,username,userpass)select*fromusers;--向 users表中添加 users自己表
中的数据
案例 1：查询 SMITH的薪水，工作，所在部门
Selectdeptno，job，salfromempwhereename=’SMITH’；
注意：Select语句中的大小写不区分，但是单引号中的内区分大小写
Select中使用列的别名
Selectename“姓名”，sal*12as“年收入”fromemp;--从 emp表中查询 ename别名“姓名”、sal*12
别名“年收入”
Select中使用算术表达式
注意：在 select运算过程中如果其中有一个值为 NULL则整个值为 NULL
Selectename，sal*12fromemp;--查询年工资
Selectsal*12+comm*12“年总工资”,ename，commfromemp；--年总工资等于年工资加年奖金
可以使用+，-，*，/进行运算
使用 NVL函数处理 NULL值
Nvl(comm,0);
如果 comm 值为空（NULL）则返回值为后面的 0，如果 comm值不为空则返回 comm 的值
Selectsal*12+nvl(comm,0)*12“年总工资”,ename，commfromemp；--年总工资等于年工资加年奖
金
“||”字符串连接符
Selectename||‘是’||jobfromemp；--将查询出的值和字符串连接在一起
Where查询条件 +120子句
案例 1：如何显示工资高于 3000的员工
Selectename,salfromempwheresal>3000;
案例 2：如何查找 后入职的员工
Selectename,hiredatefromempwherehiredate>’1-1月-1981’；
案例 3：如何显示工资在 2000到 2500的员工情况
Selectename,salfromempwheresal>=2000andsal<=2500;
Like操作符
%：表示任意 0到多个字符；
_:表示任意单个字符。
案例 1：如何显示首字符为 S的员工姓名和工资
Selectename，salfromempwhereenamelike‘%S’;
案例 2：如何显示第三个字符为大写 O的所有员工的姓名和工资
Selectename，salfromempwhereenamelike‘__S%’;
Where条件中使用 IN
案例 1：如何显示 empno为 1561，5645，1546，2155...的员工情况
Select*fromempwhereempnoin(1561,5645,1546,2155);
注意：这种查询速度极快，在多个值中查询建议使用 IN而不要使用 OR.
使用 isnull的操作符
案例 1：如何显示没有上级雇员的情况
Select*fromempwheremgrisnull；
使用逻辑操作符号
查询工资高于 500或是岗位为 MANAGER的雇员，同时还要满足他们的姓名首写字母为大写的 J
Select*fromempwhere(sal>500orjob=’MANAGER’)andenamelike‘J%’;
Orderby排序语句
Orderby默认升序(asc)，降序(desc)。
如何按照工资的从低到高的顺序显示雇员的信息
Select*fromemporderbysal;
按照部门号升序而雇员的工资降序排列
Select*fromemporderbydeptno，saldesc;
使用列的别名排序
注意：别名需要加双引号””，英文不用加双引号，给列加别名时中间可以加 as。
Selectename,sal*12“年薪”fromemporderby“年薪”asc;
复杂表查询
数据分组 max，min，avg，sum，count
显示所有员工中最高工资和最低工资
Selectmax(sal),min(sal)fromemp;
显示工资最高员工的名字，工作岗位
Selectename,salfromempwheresal=(Selectmax(sal)fromemp)
显示所有员工的平均工资和工资总和
Selectavg(sal),sum(sal)fromemp;
计算共有多少员工
Selectcount(*)fromemp；
显示工资高于平均工资的员工信息
Select*fromempwheresal>(Selectavg(sal)fromemp);
Groupby和 having子句
Groupby用于对查询的结果分组统计,分组查询中分组字段必须出现在查询结果中
Having子句用于限制分组显示结果
显示每个部门的平均工资和最高工资
Selectavg(sal),max(sal),deptnofromempgroupbydeptno;
显示每个部门的每种岗位的平均工资和最低工资
Selectavg(sal),min(sal),deptno，jobfromempgroupbydeptno，job;
显示平均工资低于 2000的部门号和它的平均工资
Selectavg(sal),max(sal),deptnofromempgroupbydeptnohavingavg(sal)>2000;
数据分组总结
1、分组函数只能出现在选择列表、having、orderby子句中；
2、如果在 select语句中同时包含 groupby，having，orderby那么他们的顺序是 groupby，having，
orderby；
3、在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在 groupby子句
中，否则就会出错
案例 1:显示平均工资低于 2000的部门号和它的平均工资并按平均工资排序
Selectavg(sal),max(sal),deptnofromempgroupbydeptnohavingavg(sal)>2000orderbyavg(sal);
多表查询
多表查询，betweenand语句
多表查询是指基于两个和两个以上的表或是视图的查询,多表查询查询条件不能少于表的个数减 1。
案例 1：显示雇员名，雇员工资及所在部门的名字；【笛卡尔集】
,,=;
案例 2：显示部门号为 10的部门名、员工名和工资
,,==10;
案例 3：显示部门号为 10的部门名、员工名和工资
,,,;
案例 4：显示雇员名、雇员工资及所在部门的名字，并按部门排序
,,=;
自连接
自连接是指在同一张表的链接查询
显示‘FORD’上级的姓名
，，==’FORD’;
左连接和右连接
左连接和右连接以如下方式来实现：
查看如下语句：
SELECTemp_name,dept_nameFORMEmployee,_deptid(+)=
eptid
此 SQL使用了右连接，即“(+)”所在位置的另一侧为连接的方向，右连接说明等号右侧的所有记录
均会被显示，无论其在左侧是否得到匹配，也就是说上例中无论会不会出现某个部门没有一个员工的
情况，这个部门的名字都会在查询结果中出现。
反之：
SELECTemp_name,dept_nameFORMEmployee,_deptid=
id(+)
则是左连接，无论这个员工有没有一个能在 Department表中得到匹配的部门号，这个员工的记录都
会被显示
一般的相等连接
select*froma,=;
这个是内连接
Commented [n1]: Savepoint
子查询
子查询是指嵌入其他 sql语句中的 select语句，也叫嵌套查询
注意：数据库在执行 sql时是从后往前执行，有括号先执行括号里面的语句，包含多个条件时，尽量
将能过滤数据量大的条件放在后面，以提高执行效率
单行子查询
单行子查询是指子语句只返回一行数据的子查询语句
如：显示与 SMITH同一部门的所有员工
SELECT*fromempwheredeptno=(Selectdeptnofromempwhereename=’SMITH’);
多行子查询
多行子查询指子语句返回多行数据的子查询
如：查询和部门 10的工作相同雇员的名字、岗位、工资、部门号
SELECT*fromempwherejobin(Selectdistinctjobfromempwheredeptno=10)
显示工资比部门 30的所有员工的工资高的员工的姓名、工资和部门号
Selectename，sal，deptnofromempwheresal>all(selectsalfromempwheredeptno=3);
Selectename，sal，deptnofromempwheresal>(selectmax(sal)fromempwheredeptno=3);--效率
比第一种高
使用 any操作符查询
显示工资比部门 30的任意一个员工的工资高的员工的姓名、工资和部门号
Selectename，sal，deptnofromempwheresal>any(selectsalfromempwheredeptno=30);
Selectename，sal，deptnofromempwheresal>(selectmin(sal)fromempwheredeptno=30);
多列子查询
多列子查询是指查询返回多个列数据的子查询语句
查询与 SMITH的部门和岗位完全相同的所有雇员
Select*fromEMPwhere(deptno,job)=(selectdeptno,jobfromempwhereename=’SMITH’);--注意
列名前后顺序对应
From子句中使用子查询
当在 from 子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌图，当在
from子句中使用子查询时，必须给予查询指定别名。
案例 1：显示高于自己部门平均工资员工的信息
,,,,(Selectavg(sal)mysal,deptnofromemp
groupbydeptno)=>;
分页查询
Oracle分页方式有三种：
1、rownum
第一步：
Selecta1.*，rownumrnfrom(select*fromemp)a1whererownum<=10;--找出前 10行记录;
第二步：
Select*from（Selecta1.*，rownumrnfrom(select*fromemp)a1whererownum<=10）wherern>=6;--
找出 6-10行记录;
案例：
A．指定查询列，只需修改最里层的子查询
Select*from（Selecta1.*，rownumrnfrom(selectename，salfromemp)a1whererownum<=10）
wherern>=6;
B．按顺序查询，只需修改最里层的子查询
Select*from（Selecta1.*，rownumrnfrom(select*fromemporderbysal)a1whererownum<=10）
wherern>=6;
C．所有查询修改，只需修改最里层的子查询
2、Rowid
Select*fromt_xiaoxiwhererowidin(selectrownrn,ridfrom(selectrowidrid,cidfromt_xiaoxio
rderbyciddesc)whererownum<10000wherern>9980)orderbyciddesc;
3、按分析函数
Select*from（selectt.*,row_number()over(orderbyciddesc)rkfromt_xiaoxit）
whererk<10000andrk>9980;
用查询结果创建新表
Createtablemytable（id,name,sal,job,deptno）asselectempno,ename,sal,job,deptnofromemp;
创建表的同时把数据打入新表中
合并查询
集合操作符号 union，unionall，intersect，minus，查询比 and、or速度快。
1、union
该操作符用于取得两个结果集的并表，当使用该操作符时，会自动去掉结果集中的重复行。
Selectename，sal，
jobfromempwheresal>2500unionselectename,sal,jobfromempwherejob=’MANAGER’;
2、unionall
该操作和 union相似，但是它不会取消重复行，而且不会排序
3、Intersect
使用该操作用于取得两个结果集的交集。
4、Minus
使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合
中的数据，用一个集合减去另一个集合。
Java操作 Oracle
桥连接,不能远程连接
引 sql包
.*
加载驱动
(“”);
得到连接
Connectionct=(”jdbc:odbc:数据源”,”用户名”,”密码”);
连接，允许远程连接
引 sql包
.*
加载驱动
(“”);
得到连接
Connectionct=(”jdbc:oracle:thin:@:1521:数据库名称”,”
用户名”,”密码”);
事物
settransactionreadonly;--设为只读事物
使用子查询插入数据
使用子查询插入数据时，一条 Insert语句可以插入大量的数据，当处理行迁移或者装载外部表
的数据到数据库时，可以使用子查询来插入数据。
insertintokkk(myid,myname,mydept)selectempno,ename,deptnofromempwheredeptno=10;
使用子查询插入数据
使用 update语句更新数据时，既可以使用表达式或者数值直接修改数据，也可以使用子查询修改数
据。
案例 1：使员工 SCOTT的岗位、工资、补助与 SMITH员工一样
Updateempset(job,sal,comm)=(selectjob,sal,commfromempwhereename=’SMITH’)whereename=’
scott’;
Oracle中的事物
事物用于保证数据的一致性，它由一组相关的 dml语句组成，该组的 dml语句要么全部成功，要么全
部失败，dml 语句指数据操作语言，即增、删、改语句，没有查询语句。
如：网上转账就是典型的要用事物来处理，用以保证数据的一致性。
事物的几个重要操作：
1、设置保存点，savepointa
2、取消部分事物，rollbacktoa
3、取消全部事物，rollback
函数
字符函数
lower(char):将字符串转化为小写的格式
selectlower(ename)fromemp;--把所有人的名字按小写显示，多个逗号分开
upper(char):将字符串转化为大写的格式
selectupper(ename)fromemp;--把所有人的名字按大写显示，多个逗号分开
length(char):返回字符串的长度
select*fromempwherelength(ename)=5;--显示正好有 5个字符的名字
substr(char,m,n):取字符串的子串
selectsubstr(ename,1,3)fromemp;--显示所有姓名的前三个字符,中间数值指从第几个字符取值，后
面的数值指取几个字符，1个汉字算 1个字符；
replace(char1,serch_string,replace_string)替换字符串
Selectreplace(ename,’A’,’我’)fromemp;--显示所有姓名，用’我’替换所有’A’
instr(char1,char2,[,n[,m]])取子串在字符串的位置
案例：以首个字符为大写显示所有姓名
1) 首字母大写显示姓名
Selectupper(substr(ename,1,1))fromemp;
2) 从第二个字母小写显示姓名
Selectlower(substr(ename,2,length(ename)-1))fromemp;
3) 两者合并
Selectupper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1))fromemp;--
以首个字符为大写显示所有姓名
数学函数
数学函数的输入参数和返回值的数据类型都是数字类型的，数学函数包括
cos,cosh,exp,ln,log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round等；
round(n,[m])四舍五入
该函数用于执行四舍五入，如果省掉 m，则四舍五入到整数；如果 m是正数，则四舍五入到小数
点的 m位后；如果 m是负数，则四舍五入到小数点的 m位前；
selectround(sal,1)fromemp;
trunc(n,[m])截取数字
该函数用于截取数字，如果省掉 m，就截去小数部分，如果 m是正数就截取到小数点的 m位后，
如果 m是负数，则截取到小数点的前 m位；
selecttrunc(sal,1)fromemp;
mod(m,n)取摩
如 mod(10,2)值为 0,mod(10,3)值为 1，可用函数测试表 dual测试；
floor(n)向下取最大整数
返回小于或是等于 n的最大整数,如‘floor()’返回‘55’；
selectfloor(sal,1)fromemp;
ceil(n)向上取最小整数
返回大于或是等于 n的最小整数如‘ceil()’返回‘56’
selectceil(sal,1)fromemp;
案例：显示在一个月为 30天的情况所有员工的日薪金，忽略余数。
Selecttrunc(sal/30),enamefromemp;
Selectfloor(sal/30),enamefromemp;
其他数学函数：
abs(n)返回数字 n的绝对值
selectabs(-13)fromdual;--求-13的绝对值，dual是函数测试表
acos(n)返回数字的反余弦值
asin(n)返回数字的反正弦值
atan(n)返回数字的反正切
cos(n)返回数字的余弦值
exp(n)返回 e的 n次幂
log(m,n)返回对数值
power(m,n)返回 m的 n次幂
日期函数
日期函数用于处理 date类型的数据。
默认情况下日期格式是 dd-mon-yy即 12-7月-78
To_date函数
可以插入任意格式的日期函数。
如：按年-月-日格式插入日期
To_date(‘1988-12-12’,’yyyy-mm-dd’)
sysdate该函数返回系统时间；
selectsysdatefromdual；--显示当前系统时间，函数测试表 dual
add_months(d,n)；
select*fromempwheresysdate>add_months(hiredate,8);--查找已入职 8个月多的员工
last_day(d)返回指定日期所在月份的最后一天
案例：
显示满 10年服务年限的员工的姓名和受雇日期
selectename，hiredatefromempwheresysdate>=add_months(hiredate,12*10);
对于每个员工，显示其加入公司的天数
Selecttrunc(sysdate-hiredate)“入职天数”，enamefromemp；--对查出的天数取别名“入职天数”，
trunc函数截取整天数
找出各月倒数第 3天受雇的所有员工
Selecthiredate,enamefromempwherelast_day(hiredate)-2=hiredate;
转换函数
转换函数用于将数据类型从一种转为另外一种，在某种情况下，Oracleserver允许值的数据类型
和实际的不一样，这时 Oracleserver会隐含的转化数据类型,但是它并不适应所有的情况，为了提高
程序的可靠性，我们应该使用转换函数进行转换
案例 1：
Createtablet1(idint);--整数 int
Insertintot1values(‘10’);--这样 Oracle会自动的将‘10’转换为 10
案例 2：
Createtablet2(idvarchar2(10));
Insertintot2values(1);--这样 Oracle会自动的将 1转换为‘1’
To_char转换函数
9：显示数字，并忽略前面 0 yy：两位数字的年份 200404
0：显示数字，如位数不足，则用 0补齐 yyyy：四位数字的年份 2004年
.：在指定位置显示小数点 mm：两位数字的月份 8月08
，：在指定位置显示逗号 dd：2位数字的天 30号30
$：在数字前面加美元 hh24:24小时制的两位时钟晚 8点20
L：在数字前加本地货币符号 hh12:12小时制的两位时钟晚 8点08
C：在数字前加国际货币符号 mi:显示分钟
G：在指定位置显示组分隔符、 ss：显示秒
D：在指定位置显示小数点符号（.）
案例 1：
日期显示时、分、秒
Selectename，to_char(hiredate,’yyyy-mm-ddhh24:mi:ss’)fromemp;
Insertintoempvalues(8881,’test用户’,’MANAGER’,7782,sysdate,23,23,10);
--添加入职日期 sysdate为系统时间
案例 2：
薪水显示指定货币符号
Selectename，to_char(sal,’L99,’)fromemp;--L代表本地货币，99,代表五位整数
两位小数,中间的逗号代表千位符
案例 3：
显示 1980 年入职的员工
Select*fromempwhereto_char(hiredate,’yyyy’)=1980;
案例 4：
显示 12月份入职的员工
Select*fromempwhereto_char(hiredate,’mm’)=12;
To_date函数
Insertintoempvalues(8881,’test’,’MANAGER’,7782,to_date(1987-05-16,yyyy-mm-dd),10);
--按中国习惯添加入职日期
To_number函数
to_number(st[,fmt])st从字符型数据转换成按指定格式的数值，缺省时数值格式串的大小正好为整
个数
Nvl函数
nvl(m,n)如果 m值为 null,返回 n,否则返回 m
系统函数
Sys_context函数
通过该函数，可以查询一些重要信息,有以下参数：
1、 Terminal:当前会话客户所对应的终端标示符
2、 language:语言
3、 db_name:当前数据库名称
4、 nls_date_format:当前会话客户所对应的日期格式
5、 session_user:当前会话客户所对应的数据库的用户名
6、 current_schema:当前会话客户所对应的默认方案名？
7、 host:返回数据库所在主机的名称
案例：
Selectsys_context(‘userenv’,’db_name’)fromdual;--查询使用哪个数据库
Selectsys_context(‘userenv’,’language’)fromdual;--查询使用哪个语言
Selectsys_context(‘userenv’,’current_schema’)fromdual;--查询使用哪个方案
数据库管理
数据库管理员
管理数据库的用户主要有：sys和 system
Sys 和 system的主要区别:
Sys:所有 Oracle的数据字典的基表和视图都存放在 sys用户中，这些基表和视图对于 Oracle德运行
时至关重要的，由数据库自己维护，任何用户都不能手动更改，sys用户拥有 dba、sysdba、sysoper
角色或权限，是 Oracle权限最高的用户。
System：用于存放次一级的内部数据，如 Oracle的一些特性或工具的管理信息，system用户拥有
dba、sysdba 角色或系统权限。
Dba、Sysdba、Sysoper权限的用户
Dba 权限的用户：指具有 dba角色的数据库用户，特权用户可以执行启动实例，关闭实例等特殊操作，
而 dba用户只有在启动数据库后才能执行各种管理工作，dba用户不能启动和关闭数据库。
Sysdba是数据库最高权限。
Sysoper是数据库操作权限。
Sysdba权限>Sysoper权限>Dba权限
管理初始化参数
初始化参数用于设置实例或是数据库的特征，Oracle9i提供了 200个初始化参数，并且每个初始
化参数都有默认值。
显示初始化参数：
Showparameter命令
修改参数：
初始化参数可以在 D:\Orcale\admin\myoral\pfile\文件中修改；如修改实例名称等。
数据库（表）的逻辑备份和恢复
逻辑备份是指使用工具 export将数据对象的结构和数据导出到文件的过程，逻辑恢复是指当数据库
对象被误操作而损坏后使用工具 import利用备份的文件把数据对象导入数据库的过程。物理备份可
在数据库 open的状态进行也可以在关闭数据库后进行，但是逻辑备份和恢复只能在 open的状态下进
行。
导出
导出具体的分为：导出表，导出方案，导出数据库三种方式。
导入、导出时要到 Oracle的 bin目录中，如 D:\Oracle\ora92\bin，可以运行 cmd进入
导出使用 EXP命令来完成，该命令常用的选项有：
Userid：用于指定执行导出操作的用户名，口令，连接字符串
Tables：用于指定执行导出操作的表
Owner：用于指定执行导出操作的方案
Full=y：用于指定执行导出操作的数据库
Inctype：用于指定执行导出操作的增量类型
Rows：用于指定执行导出操作是否要导出表中的数据
File：用于指定导出文件名
1、导出表
导出自己的表
EXPUSERID=SCOTT/TIGER@MYORALTABLES=(EMP,DEPT)FILE=D:\;
导出其他方案的表
如果用户要导出其他方案的表，则需要 dba的权限或是 exp_full_database的权限，比如 system就
可以导出 Scott的表：
EXPUSERID=SYSTEM/MANAGER@MYORCALTABLES=(,)FILE=D:\;
导出表结构
EXPUSERID=SCOTT/TIGER@MYORALTABLES=(EMP,DEPT)FILE=D:\=N;
使用直接导出方式
EXPUSERID=SCOTT/TIGER@MYORCALTABLES=(EMP)FILE=D:\=Y;
这种方式比默认的常规方式速度要快，当数据量大时，可以考虑使用这样的方法，
注意：这时需要数据库的字符集要与客户端字符集完全一致，否则会报错...
导出方案
导出方案是指使用 export工具导出一个方案或是多个方案中的所有对象（表，索引，约束...）并存
放到文件中。
导出自己的方案
EXPSCOTT/TIGER@MYORCALOWNER=(SCOTT)FILE=D:\;
导出其他方案
如果用户要导出其他方案，则需要 dba权限或是 exp_full_database的权限，例如 system用户就可
以导出任何方案；
EXPSYSTEM/MANAGER@MYORCALOWNER=(SYSTEM,SCOTT)FILE=D:\;
导出数据库
导出数据库是指利用 export导出所有数据库中的对象及数据，要求该用户具有 dba的权限或是
exp_full_database权限
EXP_USERID=SYSTEM/MANAGER@MYORCALFULL=YINCTYPE=COMPLETEFILE=D:\;
导入
导入就是使用工具 import将文件中的对象和数据导入到数据库中，但是导入要使用的文件必须是
export所导出的文件，与导出相似，导入也分为导入表，导入方案，导入数据库三种方式。
IMP常用的选项有：
Userid：用于指定执行导入操作的用户名，口令，连接字符串；
Tables:用于指定执行导入操作的表：
Fromuser：用于指定源用户；
Touser：用于指定目标用户；
File：用于指定导入文件名；
Full=y:用于指定执行导入整个文件；
Inctype：用于指定执行导入操作的增量类型；
Rows:指定是否导入表行（数据）；
Ignore：如果表存在，则只导入数据。
1、导入表
导入自己的表
IMPUSERID=SCOTT/TIGER@MYORACLTABLES=(EMP)FILE=D:\;
导入表到其他用户
要求该用户具有 dba 的权限，或是 imp_full_database权限
IMPUSERID=SYSTEM/MANAGER@MYORCALTABLES=(EMP)FILE=D:\=SCOTT
导入表结构
只导入表的结构而不导入数据
IMPUSERID=SCOTT/TIGER@MYORACLTABLES=(EMP)FILE=D:\=N
导入数据
如果对象（比如表）已经存在可以只导入表的数据
IMPUSERID=SCOTT/TIGER@MYORACLTABLES=(EMP)FILE=D:\=Y;
2、导入方案
导入方案是指使用 import工具将文件中的对象和数据导入到一个或是多个方案中，如果要导入其他
方案，要求该用户具有 dba的权限，或是 imp_full_database权限。
导入自身的方案
IMPUSERID=SCOTT/TIGERFILE=D:\;
导入其他方案
要求该用户具有 dba 的权限
IMPUSERID=SYSTEM/MANAGERFILE=D:\=SYSTEMTOUSER=SCOTT;
导入数据库
在默认的情况下，当导入数据库时，会导入所有对象结构和数据，例如：
IMPUSERID=SYSTEM/MANAGERFULL=YFILE=D:\;
数据字典和动态性能视图
数据字典是 Oracle数据库中最重要的组成部分，它提供了数据库的一些系统信息。
动态性能视图记载了例程启动后的相关信息。
数据字典
数据字典记录了数据库的系统信息，它是只读表和视图的集合，数据字典的所有者为 sys用户。
用户只能在数据字典上执行查询操作（select语句），而其维护和修改是由系统自动完成。
数据字典的组成：数据字典包括数据字典基表和数据字典视图，其中基表存储数据库的基本信息，普
通用户不能直接访问数据字典的基表，数据字典视图是基于数据字典基表所建立的视图，普通用户可
以通过查询数据字典视图取得系统信息，数据字典视图主要包括：user_xxx，all_xxx，dba_xxx三种
类型。
1、User_tables；
用于显示当前用户所拥有的所有表。它只返回用户所对应方案的所有表。
例如：selecttables_namefromuser_tables;
2、all_tables
用于显示当前用户可以访问的所有表，它不仅会返回当前用户方案的所有表，还会返回当前用户可以
访问的其它方案的表；
例如：selecttables_namefromall_tables；
3、dba_tables
它会显示所有方案拥有的数据库表，但是查询这种数据库字典视图，要求用户必须是 dba角色或是有
select_any_table系统权限，例如：当前 system用户查询数据字典视图 dba_tables时，会返回
system，sys，Scott...方案所对应的数据库表。
数据字典-用户名、权限、角色
在建立用户时，Oracle会把用户的信息存放到数据字典中，当给用户授予权限或是角色时，Oracle
会将权限和角色的信息存放到数据字典中，
通过查询 dba_users可以显示所有数据库用户的详细信息；
Select*fromdba_users；--显示所有数据库用户的详细信息
通过查询数据字典视图 dba_sys_privs，可以显示用户具有的系统权限；
通过查询数据字典视图 dba_tab_privs，可以显示用户具有的对象权限；
通过查询数据字典视图 dba_col_privs，可以显示用户具有的列权限；
通过查询数据字典视图 dba_role_privs，可以显示用户所具有的角色；
Select*fromdba_role_privswheregrantee=‘Scott’；--查询 Scott用户具有的角色
//查询 Oracle中所有的系统权限，一般用 DBA查询
Select*fromsystem_privilege_maporderbyname；
//查询 Oracle中所有的角色，一般用 DBA查询
Select*fromdba_roles；
//查询 Oracle中所有对象权限，一般用 DBA查询
Selectdistinctprivilegefromdba_tab_privs；
//查询数据库的表空间
Selecttablespace_namefromdba_tablespaces；
1、查询一个角色包含的权限
a、一个角色包含的系统权限
Select*fromdba_sys_privswheregrantee=‘DBA’；--查询 DBA(大写)的系统权限
Select*fromrole_sys_privswhererole=‘DBA’；--查询 DBA(大写)的系统权限
b、一个角色包含的对象权限
Select*fromdba_tab_privswheregrantee=‘DBA’；--查询 DBA(大写)的对象权限
Select*fromrole_tab_privswhererole=‘DBA’；--查询 DBA(大写)的系统权限
2、查看某个用户具有什么样的角色
Select*fromdba_role_privswheregrantee=‘用户名’；--用户名大写
3、显示当前用户可以访问的所有数据字典视图
Select*fromdictwherecommentslike‘%grant%’;
4、显示当前数据库的全称
Select*fromglobal_name;
动态性能视图
动态性能视图用于记录当前例程的活动信息，当启动 Oracleserver时，系统会建立动态性能视图；
当停止 Oracleserver时，系统会删除动态性能视图，Oracle的所有动态性能视图都是以
V_$datafile的同义词为 V$datafile；动态性能视图的所有者为 sys，一般情况下，由 dba或是
特权用户来查询动态性能视图。
约束
约束用于确保数据库数据满足特定的商业规则，在 oracle中，约束包括：非空(notnull)、唯一
(unique)，主键(primarykey)，外键(foreignkey)和检查(check)五种。
notnull，在列上定义了 notnull，那么当插入数据时，必须为列提供数据。
unique，当定义了唯一约束后，该列值不能重复，但是可以为 null。
Primarykey，用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为空
（null）。
注意：一张表只能有一个主键，但是可以有多个 unqiue约束。可以有联合主键，即两列及两个
以上的列同时作为一个主键。
创建约束
案例 1：
现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成：
商品 goods（商品号 goodsid，商品名 goodsname，单价 unitprice，商品类别 category，供应商
provider）；
客户 customer（客户号 customerid，姓名 name，住址 address，电邮 email，性别 sex，身份证
cardid）；
购买 purcase（客户号 customerid，商品号 goodstid，购买数量 nums）；
试用 SQL语言完成下列功能：
1建表，在定义中要求声明：
(1)每个表的主外键；
(2)客户的姓名和商品名不能为空值；
(3)单价必须大于 0，购买数量必须再 0到 30之间；
(4)电邮不能够重复；
(5)客户的性别必须是‘男’或‘女’，默认是‘男’。
--建商品表
createtablegoods(goodsidchar(8)primarykey,--主键
goodsnamevarchar2(30),
unitpricenumber(10,2)check(unitprice>0),
providervarchar2(30));
--客户表
createtablecustomer(customeridchar(8)primarykey,
namevarchar2(50)notnull,
addressvarchar2(50),
emailvarchar2(50)unique,
sexchar(2)default'男'check(sexin('男','女')),
cardidchar(18));
--购买表
createtablepurchase(customeridchar(8)referencescustomer(customerid),--外键（列级定
义），foreignkey（表级定义）
goodsidchar(8),
addressvarchar2(50),
numsnumber(10)check(numsbetween1and30));
案例 2：
如果在建表时忘记建立必要的约束，但是要注意：增加 notnull约束时，需要使用 modify选项，
而增加其它四种约束使用 add选项。
（1） 每个表的主外键；
（2） 客户的姓名不能为空值；--增加商品名也不能为空
（3） 单价必须大于 0，购买数量必须再 0到 30之间；
（4） 电邮不能够重复；--增加身份证也不能重复
（5） 客户的性别必须是‘男’或‘女’，默认是‘男’；
（6） 增加客户的住址只能是’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’。
altertablegoodsmodifygoodsnamenotnull;--增加商品名也不能为空
altertablecustomeraddconstraintcarduniqueunique(cardid);--增加身份证也不能重复
altertablecustomerdropcolumncradid;--删除 craid列
altertablecustomeradd(cardidchar(18));--增加 carid列
altertablecustomeradd(constraintaddresscheckcheck(addressin(’海淀’,’朝阳’,’东
城’,’西城’,’通州’,’崇文’));
删除约束
当不需要某个约束时，可以删除。
altertable表名 dropconstraint约束名称；
在删除主键约束的时候，可能有错误，比如：
altertable表名 dropprimarykey；
这是因为在两张表存在主从关系，那么在删除主表的主键约束时，必须带上 cascade选项。如下：
altertable表名 dropprimarykeycascade；
显示约束信息
通过查询数据字典视图 user_constraints，可以显示当前用户所有约束信息
Selectconstraint_name,constraint_type,status,validatedfromuser_constraintswheretabl
e_name=’表名’；
显示约束列：
通过查询数据字典视图 user_cons_columns，可以显示约束所对应的表列信息。
Selectcolumn_name,positionfromuser_cons_columnswhereconstraint_name=’约束名’;
表级定义和列级定义
列级定义是在定义列的同时定义约束
如在 department表定义主键约束,constraintpk_department指给主键约束取一个名字
Createtabledepartment(dept_idnumber(2)constraintpk_departmentprimatykey,
Namevarchar2(12);
Locvarchar2(12);
表级定义是指在定义了所有列后，再定义约束，需要注意：notnull约束只能在列级上定义。
例如：建立 employees表时定义主键约束和外键约束
Createtableemployee(emp_idnumber(4),namevarchar2(15),dept_idnumber(2),
constraintpk_employeeprimarykey(emp_id),
constraintfk_departmentforeignkey(dept_id)
referencesdepartment(dept_id));
索引、权限
索引
创建索引
单列索引是基于单个列所建的索引，比如：
createindex 索引名 on表名（列名）
复合索引是基于两列或是多列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不
同，比如：
createindexemp_idxlonemp(ename,job);--先按 ename查询，再 job查询，sql语句从后往前执
行
createindexemp_idxlonemp(job,ename);--先按 job查询，再 ename查询，sql语句从后往前执
行
索引使用原则
1）、在大表上建立索引才有意义
2）、在 where子句或是连接条件上经常引用的列上建立索引
3）、索引的层次不要超过 4层
索引缺点分析
1）、建立索引，系统要占用大约为表的 倍的硬盘和内存空间来保存索引。
2）、更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的
一致性。
3）、不恰当的索引不但于事无补，反而会降低系统性能，大量的索引在进行插入、修改和删除
操作时比没有索引花费更多的系统时间。如：很少或从不引用的字段；逻辑型字段（男或女，是
或否）等；
其他索引
按照数据存储方式，可以分为 B*树、反向索引、位图索引；
按照索引列的个数分类，可以分为单列索引、复合索引；
按照索引列值的唯一性，可以分为唯一索引和非唯一索引；
此外还有函数索引，全局索引，分区索引……
在不同的情况会在不同的列上建立索引，甚至建立不同种类的索引，比如：
B*-树索引建立在重复值很少的列上，而位图索引则建立在重复值很多、不同值相对固定的列上。
显示表的所有索引
在同一张表上可以有多个索引，通过查询数据字典视图 dba_indexs和 user_indexs，可以显示索
引信息，其中 dba_indexs 用于显示数据库所有的索引信息，而 user_indexs用于显示当前用户
的索引信息；
Selectindex_name,index_typefromuser_indexeswheretable_name=’表名’；
显示索引列：
通过查询数据字段视图 user_ind_columns，可以显示索引对应列的信息，
Selecttable_name,column_namefromuser_ind_columnswhereindex_name=’ind_ename’;
权限
权限分为系统权限和对象权限
刚建了用户时，用户没有任何权限，也不能执行任何操作，如果要执行某种特定的数据库操作，
则必须为其授予对象的权限，为了简化权限的管理，可以使用角色。Oracle包含 25种角色。每
个用户对应一个方案。
系统权限
系统权限是指执行特定类型 sql命令的权利，它用于控制用户可以执行的一个或是一组数据库操
作，比如当用户具有 createtable权限时，可以在其方案中建表，当用户具有 createanytable
权限时，可以在任何方案中建表，oracle提供了 100多种系统权限。
常用系统权限的如下：
createsession连接数据库 createtable 建表
createview建视图 createpublicsynonym建同义词
createprocedure建过程、函数、包 createtrigger 建触发器
createcluster建簇
显示系统权限：
Oracle提供了 100多系统权限，而且 oracle得版本越高，提供的系统权限就越多，我们可以查
询数据字典视图 system_privilege_map，可以显示所有系统权限；
Select*fromsystem_privilege_maporderbyname；
授予系统权限：
一般情况，授予系统权限是由 dba完成的，如果用其他用户来授予系统权限，则要求该用户必须
具有 grantanyprivilege的系统权限在授予系统权限时，可以带有 withadminoptions选项，这
样被授予权限的用户或是角色还可以将该系统权限授予其它的用户或是角色。
1）、创建两个用户 ken，tom初始阶段他们没有任何权限，如果登录就会给出错误的信息。
Createuserkenidentifiedbyken；
2）、给用户 ken授权
Grantcreatesession，createtabletokenwithadminoptions；
Grantcreateviewtoken；
3）、给用户 tom授权
可以通过 ken给 tom授予 createsession，createtable两个权限，
回收系统权限：
一般情况下，回收系统权限时 dba来完成的，如果其它的用户来回收系统权限，要求该用户必须
具有相应系统权限及转授系统权限的选项（withadminoption）。回收系统权限使用 revoke来完
成，当回收了系统权限后，用户就不能执行相应的操作了，系统权限不是级联收回（即收回二级
用户的权限，不会收回二级用户授予给其他用户的权限）。
对象权限
指访问其他方案对象的权利，用户可以直接访问自己方案的对象，但是如果要访问别的方案的对
象，则必须具有对象的权限，比如 smith用户要访问 表（Scott：方案，emp：表），
则必须在 表上具有对象的权限。
常用的对象权限如下：
alter修改(修改表结构)，delete删除，select查询，insert添加，update修改（修改表数据），
index索引，references引用，execute 执行
显示对象权限：
通过数据字段视图可以显示用户或是角色所具有的对象庆权限，视图为 dba_tab_privs
Sql>connsystem/manger
Sql>selectdistionctprivilegefromdba_tab_privs;
Sql>selectgrantor,owner,table_name,privilegefromdba_tab_privswheregrantee=’BLAKE’;
授予对象权限：
在 oracle9i前，授予对象权限是由对象的所有者来完成的，如果用其他的用户来操作，则需要
用户具有相应的(withgrantoption)权限，从 oracle9i开始，dba用户(sys,system)可以将任何
对象上的对象权限授予其它用户，授予对象权限时用 grant命令来完成的。
对象权限可以授予用户，角色和 pubic，在授予权限时，如果带有 withgrantoption选项，则可
以将该权限转手给其他用户。但是要注意 withgrantopinion选项不能被授予角色。
案例 1：
Grantselectonemptomonkey；
Grantupdateonemptomonkey；
Grantallonemptomonkey；
案例 2：
希望 monkey只能修改 表的 ename，sal数据
Grantupdateonemp(sal)tomonkey;
希望 monkey只能查询 表的 ename，sal数据
Grantselectonemp(ename,sal)tomonkey;
案例 3：
如果用户想要执行其他方案的包、过程、函数，则需要有 execute权限。
如：让 ken可以执行包 dbms_transaction，可以授予 execute权限
Sql>connsystem/manager;
Sql>grantexecuteondbms_transactiontoken;
案例 4：
使用 withgrantoption选项，该选项用于转授对象权限，但是该选项只能被授予用户，而不能授
予角色。
Sql>connscott/tiger;
Sql>grantselectonemptoblackwithgrantoption;
Sql>connblack/shunping;
Sql>;
回收对象权限：
在 oracle9i中，收回对象权限可以由对象的所有者来完成，也可以用 dba用户（sys，system）
来完成，收回对象权限后，用户就不能执行相应的 sql命令，对象权限是级联收回（即收回二级
用户的权限，同时收回二级用户授予给其他用户的权限）。如下：
Sql>connscott/tiger@accp;
Sql>revokeselectonempfromblack;
角色
角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理，角色分为预定义角色
和自定义角色两类，假定用户 a，b，c为了让他们都拥有权限
1）、连接数据库，2）、在 表上 select，inser，update，如果采用直接授权操作，则需
要进行 12 次授权。
可以先将 createsession，，， 授予角色，
然后将该角色授予 a，b，c用户，这样三次授权搞定
预定义角色
预定义角色是指 oracle所提供的角色，每种角色都用于执行一些特定的管理任务，常用的预定义
角色有 connect，resource，dba
Connect角色
Connect角色具有一般应用开发人员需要的大部分权限，当建立了一个用户后，多数情况下，只
要给用户授予 connect和 resource角色就够了，connect包含以下 8个系统权限：
altersession,createcluster,createdatabaselink,createsession,createtable,createview,c
reatesequence.
resource角色
resource角色具有应用开发人员所需要的其他权限，比如建立存储过程、触发器等。Resource角
色隐含了 unlimitedtablespace 系统权限。
Resource角色包含以下 6个系统权限：
Createcluster，createtable,createubdextyoe,createtype,createprocedure,
Createtrigger。
dba角色
dba角色具有所有的系统权限，及 withadminoption 选项权限，默认的 dba用户为 sys和 system
他们可以将任何系统权限授予其它用户，但是 dba角色不具备 sysdba和 sysoper的特权（启动和
关闭数据库）。
自定义角色
自定义角色，根据自己的需要来定义，一般是 dba来建立，如果用别的用户来建立，则需要有
createrole的系统权限，在建立角色时可以指定验证方式(不验证、数据库验证等).
建立角色
1)、建立角色（不验证）：
如果角色是公用的角色，可以采用不验证的方式建立角色，
Createrole角色名 notidentified；
2)、建立角色（数据库验证）：
采用这样的方式时，角色名、口令存放在数据库中。当激活该角色时，必须提供口令，在建立这
种角色时，需要为其提供口令；
Createrole角色名 identifiedbyshunping；
角色授权
当建立角色时，角色没有任何权限，为了使得角色完成特定任务，必须为其授予相应的系统权限
和对象权限。
给角色授予权限和给用户授权没有太多区别，但是系统权限的 unlimitedtablespace和对象权限
的 withgrantoption选项是不能授予角色的。
给角色授权：
Sql>connsystem/manager;
Sql>grantcreatesessionto角色名 withadminpotion;
Sql>角色名；
Sql>grantinsert,update,角色名；
授予用户角色权限
一般分配角色是由 dba来完成的，如果要以其他用户身份分配角色，则要求用户必须具有
grantanyrole的系统权限，
Sql>connsystem/manager;
Sql>grant角色名 toblack[withadminoption]；
删除角色
使用 droprole，一般是 dba来执行，如用其他用户则要求该用户具有 dropanyrole系统权限，删
除角色同时删除所有已分配该角色的所对应的权限。
Droprole角色名；
显示角色信息
1)、显示所有角色
Sql>select*fromdba_roles;
2)、显示角色具有的系统权限
Sql>selectprivilege,admin_optionfromrole_sys_privswhererole=’角色名’;
3)、显示角色具有的对象权限
通过查询数据字典视图 dba_tab_privs可以查看角色具有的对象权限或是列的权限。
4)、显示用户具有的角色及默认的角色。
当以用户的身份连接到数据库时，oracle会自动的激活默认的角色，通过查询数据字典视图
dba_role_privs可以显示某个用户具有的所有角色及其默认的角色。
selectgranted_role,default_rolefromdba_role_privswheregrantee=’用户名’；
精细访问控制
是指用户可以使用函数、策略实现更加细微的安全访问控制，如果使用精细访问控制，则当在客
户端发出 sql语句（select，insert，update，delete）时，oracle会自动在 sql语句后面追加
谓词（where子句），并执行新的 sql语句，通过这样的控制，可以使得不同的数据库用户在访
问相同表时，返回不同的数据信息，如图：
ScottblakeJones
策略 emp_access
数据库表 emp
如上图所示，通过策略 emp_access，用户 scott，black，Jones在执行相同的 sql语句时，可以
返回不同的结果，例如：当执行 selectenamefromemp；时，实际情况可以返回不同的结果。
plsql编程
PL/SQL编程介绍
PL/SQL 编程可以提高应用程序的运行性能；模块化的设计思想；减少网络传输量；提高安全
性。但是 PL/SQL编程移植性差。
PL/SQL 编程：
1、可以用 PL/SQL编写过程、函数、触发器；
2、过程、函数、触发器存放在 Orcal中；
3、PL/SQL 是非常强大的数据库过程语言；
4、过程、函数可以在 JAVA中调用。
PL/SQL创建存储过程
案例 1：创建存储过程，可以向某张表添加记录。
1、创建一个简单的表
Createtablemytest(namevarchar2(30),passwdvarchar2(30);
2、创建过程
Createorreplaceproceduresp_pro1is--orreplace表示如果有 sp_prol，就替换
begin
--执行部分
Insertintomytestvalues(‘韩顺平’,’M1234’);
end;
3、查看错误
Showerror；
4、调用过程
①、exec过程名（参数值 1，参数值 2，...）
②、call过程名（参数值 1，参数值 2，...）
案例 2：创建存储过程，可以删除某张表的一条记录。
Createorreplaceproceduresp_pro2is--orreplace表示如果有 sp_prol，就替换
begin
--执行部分
Deletefrommytestwherename=‘韩顺平’
end;
PL/SQL编程分类
过程（存储过程
函数
触发器
块(编程)
包
PL/SQL编写规范
1、注释
单行注释--
多行注释/*...*/
2、标识符号的命名规范
当定义变量时，建议用 V_作为前缀 V_SAL
当定义常量时，建议用 C_作为前缀 C_RATE
当定义游标时，建议用_CURSOR作为后缀 EMP_CURSOR
当定义例外时，建议用 E_作为前缀 E_ERROR
PL/SQL编程—块
1、块的结构示意图：
PL/SQL块由三个部分构成：定义部分、执行部分、例外处理部分。如下所示：
declear
/*定义部分—定义常量、变量、游标、例外、复杂数据类型*/
begin
/*执行部分—要执行的 PL/SQL语句和 SQL语句*/
exception
/*例外处理部分—处理运行的各种错误*/
end；
案例 1、只包括执行部分的 PL/SQL模块
setserveroutputon--打开输出选项
begin
_line(‘hello’);
end;
/*说明：dbms_output是 Oracle所提供的包（类似 java的开发包），该包包含一些过程，
put_line就是 dbms_output包的一个过程*/
案例 2、包括定义部分和执行部分的 PL/SQL模块
declare
v_enamevarchar2(5);--定义字符串变量
v_salnumber(7,2);
begin
selectename,salintov_ename,v_salfromempwhereempno=&no;
_line('雇员名：'||v_ename||'工资：'||v_sal);
end;
/*into是指将查询出的值赋值给变量
&表示要接收从控制台输入的变量
||表示连接两个字符串*/
案例 3、包括定义部分、执行部分和例外部分的 PL/SQL模块
declare
v_enamevarchar2(5);--定义字符串变量
v_salnumber(7,2);
v_empnonumber(6);
begin
selectempno,ename,salintov_empno,v_ename,v_salfromempwhereempno=&no;
_line('雇员号'||v_empno||'雇员名'||v_ename||'工资'||v_sal);
exception
whenno_data_foundthen
_line('输入的雇员号不存在或者不正确，请重新输入！');
end;
/*no_data_found就是找不到数据的例外*/
PL/SQL编程—过程
过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指定输出参数
(out)。通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以
将执行部分的数据传递到应用环境，在sqlplus中可以使用createprocedure命令来建立过程。
案例 1：编写一个过程，可以输入雇员名、新工资，可以修改雇员的工资。
Createorreplaceproceduresp_pro3(spnamevarchar2,newsalnumber)is
begin
--执行部分，根据用户名去修改工资
updateempsetsal=newsalwhereename=spname;
end;
--调用 sp_pro3 过程
callsp_pro3('JAMES',2200);
execsp_pro3('SCOTT',3210);
//演示 Java程序去调用 Oracle的储存过程案例
;
.*;
PublicclassTestOraclePro{
/**
*@paramargs
*/
Publicstaticvoidmain(String[]args){
//TODOAuto-generatedmethodstub
Try{
//1.加载驱动
(“”);
//2.得到连接
connectionct=(”jdbc:oracle:thin:@
:1521:YY”,”scott”,”tiger”);
//3.创建 CallableStatement
CallableStatementcs=(”{callsp_pro3(？,？)}”)；
//4.给？赋值
(1,”SMITH”);
(2,2000);
//5.执行
();
//6.关闭资源
();
();
}catch(Exceptione){
();
//TODOhandleexception
}
PL/SQL编程—函数
函数用于返回特定的数据，当建立函数时，在函数头部必须包含 return子句，而在函数体内
必须包含 return语句返回的数据。使用 createfunction建立函数。
案例：
--输入雇员的姓名，返回该雇员的年薪
createfunctionsp_fun2(spnamevarchar2)returnnumberisyearsalnumber(7,2);
begin
--执行部分
selectsal*12+nvl(comm,0)*12intoyearsalfromempwhereename=spname;
returnyearsal;
end;
--在 sqlplus中调用函数
sql>varabcnumber；
sql>callsp_fun2(‘SCOTT’)into:abc;
sql>printabc；
--同样可以在 java程序中调用该函数
Selectsp_fun2(‘SCOTT’)fromdual;
可以通过 (1)得到返回的结果
PL/SQL编程—包
1、包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成，可以使用 createpackage
命令创建包。包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码，包体用
于实现包规范中的过程和函数。
案例：
--创建一个包 sp_package
--声明该包有一个过程 update_sal
--声明该包有一个函数 annual_income
createpackagesp_packageis
procedureupdate_sal(namevarchar2,newsalnumber);
functionannual_income(namevarchar2)returnnumber;
end;
2、建立包体可以使用 createpackagebody命令
--创建包体
createpackagebodysp_packageis
procedureupdate_sal(namevarchar2,newsalnumber)
is
begin
updateempsetsal=newsalwhereename=name;
end;
functionannual_income(namevarchar2)returnnumberis
annual_salarynumber;
begin
selectsal*12+nvl(comm,0)intoannual_salaryfromempwhereename=name;
returnannual_salary;
end;
end;
2、调用包的过程或是函数
当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其他方案的包，还需
要在包名前加方案名。
案例：
_sal(‘SCOTT’,1500);
PL/SQL编程—触发器
触发器是指隐含执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常
用的触发事件包括 insert、update、delete语句，而触发器操作实际就是一个 PL/SQL块。可
以使用 createtrigger来建立触发器。
PL/SQL编程—变量
在编写 pl/sql程序时，可以定义变量和常量；在 pl/sql程序中包括有：
标量类型(scalar)
复合类型(composite)
参照类型(reference)
Lob(largeobject)
PL/SQL编程—变量—标量类型
1、在编写 pl/sql块时，如果要使用变量，需在定义部分定义变量。pl/sql中定义变量和常
量的语法如下：
identifier[constant]datatype[notnull][:=|defaultexpr]
identifier:名称
constant：指定常量，需要指定它的初始值，且其值是不能改变的
datatype：数据类型
notnull：指定变量值不能为 null
:=给变量或是常量指定初始值
default：用于指定初始值
expr：指定初始值的 pl/sql表达式，可是文本值、其他变量、函数等。
标量定义的案例：
1) 、定义一个变长字符串
v_enamevarchar2(10);
2) 、定义一个小数，范围~
v_salnumber(6,2);
3) 、定义一个小数并给一个初始值为 :=是 pl/sql的赋值号
v_sal2number(6,2):=;
4) 、定义一个日期类型的数据
v_hiredatedate;
5) 、定义一个布尔变量，不能为空，初始值为 false
v_validbooleannotnulldefaultfalse;
2、标量(scalar)-使用标量
在定义好变量后，就可以使用这些变量，pl/sql块为变量赋值不同于其它的编程语言，需
要在等号前加冒号(:=)。
案例：输入员工号，显示雇员姓名、工资、个人所得税（税率为 ）；
--定义常量、变量
declare
c_tax_ratenumber(3,2):=;--定义常量税率=
v_enamevarchar2(5);--定义用户名变量
v_salnumber(7,2);--定义工资变量
v_tax_salnumber(7,2);--定义个人所得税变量
begin
--输入员工号查询出员工姓名、工资并赋值给变量
selectename,salintov_ename,v_salfromempwhereempno=&no;
--计算所得税
v_tax_sal:=v_sal*c_tax_rate;
--输出结果
_line('姓名：'||v_ename||'工资：'||v_sal||'个人所得税：
'||v_tax_sal);
end;
3、标量(scalar)-使用%type类型
如果输入的值超过定义变量的字符大小，就会造成数据溢出，为了解决这个问题，可以使
用%type属性定义变量，这样它就会按照数据库列来确定定义变量的类型和长度。
如：标识符名表名.列名%type；
declare
c_tax_ratenumber(3,2):=;
%type;--使用%type定义变量类型和长度与 emp表的 ename列相同
%type;--使用%type定义变量类型和长度与 emp表的 sal列相同
v_tax_salnumber(7,2);
begin
selectename,salintov_ename,v_salfromempwhereempno=&no;
v_tax_sal:=v_sal*c_tax_rate;
_line('姓名'||v_ename||'工资'||v_sal||'个人所得税：'||v_tax_sal);
end;
PL/SQL编程—变量—复合类型
用于存放多个值的变量。主要包括以下几种：
pl/sql记录
pl/sql表
嵌套表
varray动态数组
1、复合类型-PL/SQL 记录
类似于高级语言的结构体，当引用 PL/SQL记录成员时，必须要加记录变量作为前缀(记录
变量、记录成员)如下：
--pl/sql记录实例
declare
--定义一个 pl/sql记录类型 emp_record_type，类型包含三个数据 name、salary、title
typeemp_record_typeisrecord(%type,%type,
b%type);
--定义一个变量 sp_record，这个变量的类型是 emp_record_type
sp_recordemp_record_type;
begin
selectename,sal,jobintosp_recordfromempwhereempno=&no;
_line('员工名'||||'工资'||);
end;
2、复合类型-PL/SQL 表
相当于高级语言中的数组，但是在高级语言中数组的下标不能为负数，pl/sql是可以为负
数的，并且表元素的下标没有限制，实例如下：
--pl/sql表实例
declare
--定义一个 pl/sql表类型 emp_table_type，该类型用于存放 %type
--indexbybinary_integer表示下标是整数
%typeindexbybinary_integer;
--定义一个变量 sp_table，这个变量的类型是 emp_table_type
sp_tableemp_table_type;
begin
--sp_table(-1)则表示下标为-1的元素
selectenameintosp_table(-1)fromempwhereempno=&no;
_line('员工名'||sp_table(-1));
end;
PL/SQL编程—变量—参照类型
参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，
从而降低占用的空间。在编写 pl/sql 程序时，可以使用游标变量（refcursor）和对象类型变量
（refobj_type）两种参照变量类型
参照变量-refcursor游标变量
使用游标时，当定义游标时不需要指定相应的 select语句，但是当使用游标时（open时）需要
指定 select语句，这样一个游标就与一个 select语句结合了。实例如下：
--案例 1：输入部门号，显示该部门所有员工姓名和他的工资。
declare
typesp_emp_cursorisrefcursor;--定义游标类型
test_cursorsp_emp_cursor;--定义一个游标变量
%type;
%type;
begin
--把 test_cursor和一个 select结合
opentest_cursorforselectename,salfromempwheredeptno=&no;
--循环取出
loop
fetchtest_cursorintov_ename,v_sal;
--判断是否 test_cursor为空
exitwhentest_cursor%notfound;
_line('员工名:'||v_ename||'工资:'||v_sal);
endloop;
end;
--案例 2：输入部门号，显示该部门所有员工姓名和他的工资，并且当某个员工工资低于 2000元，
就增加 200元。
declare
typesp_emp_cursorisrefcursor;--定义游标类型
test_cursorsp_emp_cursor;--定义一个游标变量
%type;
%type;
begin
--把 test_cursor和一个 select结合
opentest_cursorforselectename,salfromempwheredeptno=&no;
--循环取出
loop
fetchtest_cursorintov_ename,v_sal;
--判断是否 test_cursor为空
exitwhentest_cursor%notfound;
--判断员工工资是否低于 2000元，如果低于 2000元，就增加 200元
ifv_sal<2000
then
updateempsetsal=sal+200wheresal=v_sal;
else
_line('员工名:'||v_ename||'工资:'||v_sal);
endif;
endloop;
end;
PL/SQL编程—控制结构
控制结构语句包含条件语句、循环结构、顺序控制结构等。
PL/SQL编程—条件分支语句
PL/SQL中提供了三种条件语句 if——then，if——then——else，
if——then——elsif——else。
1、简单的条件判断 if——then
案例 1：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于 2000就给该雇员工资
增加 10%
createorreplaceproceduresp_pro6(spnamevarchar2)is
--定义
%type;
begin
--执行
selectsalintov_salfromempwhereename=spname;
--判断
ifv_sal<2000
then
updateempsetsal=sal*=spname;
endif;
end;
--调用过程
execsp_pro6('SCOTT');
案例 2、编写一个过程，可以输入一个雇员名，如果该雇员的补助不是 0就在原来的基础上增
加 100；如果补助为 0就把补助设为 200
createorreplaceproceduresp_pro6(spnamevarchar2)is
--定义
%type;
begin
--执行
selectcommintov_commfromempwhereename=spname;
--判断,不等于用"<>"表示
ifv_comm<>0then
updateempsetcomm=comm+100whereename=spname;
else
updateempsetcomm=comm+200whereename=spname;
endif;
end;
案例 3、编写一个过程，可以输入一个雇员编号，如果该雇员的职位是 PRSIDENT就给他的工
资增加 1000，如果该雇员的职位是 MANAGER就给他的工资增加 500，其它职位的雇员工资增加
200.
createorreplaceproceduresp_pro7(spnonumber)is
--定义
%type;
begin
--执行
selectjobintov_jobfromempwhereempno=spno;
--判断
ifv_job='PRESIDENT'then
updateempsetsal=sal+1000whereempno=spno;
elsifv_job='MANAGER'then
updateempsetsal=sal+500whereempno=spno;
else
updateempsetsal=sal+200whereempno=spno;
endif;
end;
PL/SQL编程—循环 loop语句
LOOP是 PL/SQL中最简单的循环语句，这种循环语句以 LOOP开头，ENDLOOP结尾 ie，这种循
环至少会被执行一次。
案例 1:现有一张表 users，表结构如下：
用户 ID 用户名
编写一个过程，可输入用户名，并循环添加 10个用户到 users表中，编号从 1开始增加
--创建表 users
createtableusers(usernonumber(10),usernamevarchar2(40));
--创建过程 sp_pro8
createorreplaceproceduresp_pro8(spnamevarchar2)is
--定义
v_numnumber:=1;
begin
--执行
loop
insertintousersvalues(v_num,spname);
--判断是否要退出循环
exitwhenv_num=10;
--自增
v_num:=v_num+1;
endloop;
end;
PL/SQL编程—while循环语句
基本循环至少执行循环体一次，而对于 while循环来说，只有条件为 true时，才会执行循环
体语句，while循环以 while...loop开始，以 endloop结束。
案例 1:现有一张表 users，表结构如下：
用户 ID 用户名
编写一个过程，可输入用户名，并循环添加 10个用户到 users表中，编号从 11开始增加
--创建表 users
createtableusers(usernonumber(10),usernamevarchar2(40));
--创建过程 sp_pro9
createorreplaceproceduresp_pro9(spnamevarchar2)is
--定义
v_numnumber:=11;
begin
whilev_num<=20loop
--执行
insertintousersvalues(v_num,spname);
v_num:=v_num+1;--自增
endloop;
end;
PL/SQL编程—for循环语句
基本 for循环的基本结构如下：
begin
foriinreverse1..10loop
insertintousersvalues(i,’顺平’);
endloop;
end;
PL/SQL编程—goto循环语句
Goto语句用于跳转到特定标号去执行语句，注意由于使用 goto语句会增加程序的复杂性，并
使得应用程序可读性变差，
基本语法如下，gotolable，其中 lable是已经定义好的标号名，
declare
iint:=1;
begin
loop
_line(‘输出 i=’||i);
ifi=10then
gotoend_loop;
endif;
i:=i+1;
endloop;
_line(‘循环结束 0’);
<<end_loop>>
_line(‘循环结束 1’);
end;
PL/SQL编程—null循环语句
Null语句不会执行任何操作，并且会直接将控制传递到下一条语句，使用 null语句的主要好
处是可以提高 pl/sql的可读性。
declare
%type;
%type;
begin
selectename,salintov_ename,v_salfromempwhereempno=&no;
ifv_sal<3000then
updateempsetcomm=sal*=v_ename;
else
null;
endif;
end;
PL/SQL编程—分页过程编写
分页过程编写—无返回值的存储过程
案例 1：现有一张表 book，表结构如下：
书号 书名 出版社
编写一个过程，可以向 book表中添加书，要求通过 java程序调用该过程。
--建 book表
createtablebook(bookidnumber,booknamevarchar2(50),publishhousevarchar2(50));
--编写过程 sp_pro7
--in:表示这是一个输入参数，out：表示一个输出参数，默认为 in。
createorreplaceproceduresp_pro9
(spbookidinnumber,spbooknameinvarchar2,sppublishhouseinvarchar2)is
begin
insertintobookvalues(spbookid,spbookname,sppublishhouse);
end;
//演示 Java程序去调用 Oracle的储存过程案例
;
//一个无返回值的过程
.*;
PublicclassTestOraclePro{
/**
*@paramargs
*/
Publicstaticvoidmain(String[]args){
//TODOAuto-generatedmethodstub
Try{
//1.加载驱动
(“”);
//2.得到连接
connectionct=(”jdbc:oracle:thin:@
:1521:YY”,”scott”,”tiger”);
//3.创建 CallableStatement
CallableStatementcs=(”{callsp_pro9(？,？,？)}”)；
//4.给？赋值
(1,10);
(2,”笑傲江湖”);
(3,”人民出版社”);
//5.执行
();
//6.关闭资源
();
();
}catch(Exceptione){
();
//TODOhandleexception
}
分页过程编写—有输入和输出的存储过程
案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。
--有输入和输出的存储过程
createorreplaceproceduresp_pro8
(spnoinnumber,spnameoutvarchar2)is
begin
selectenameintospnamefromempwhereempno=spno;
end;
//演示 Java程序去调用 Oracle的储存过程案例
;
//一个无返回值的过程
.*;
PublicclassTestOraclePro{
/**
*@paramargs
*/
Publicstaticvoidmain(String[]args){
//TODOAuto-generatedmethodstub
Try{
//1.加载驱动
(“”);
//2.得到连接
connectionct=(”jdbc:oracle:thin:@
:1521:YY”,”scott”,”tiger”);
//3.创建 CallableStatement
CallableStatementcs=(”{callsp_pro8(？,？,?,?)}”);
//4.给？赋值
(1,7788);
(2,);
(2,);
(2,);
//5.执行
();
//取出返回值
Stringname=(2);
Stringsal=(3);
Stringjob=(4);
(“姓名:”+name+“岗位:”+job+“工资:”+sal);
//6.关闭资源
();
();
}catch(Exceptione){
();
//TODOhandleexception
}
分页过程编写—有返回值的存储过程（返回结果集）
案例：编写一个过程，输入部门号，返回该部门所有雇员信息。对该题分析如下：
由于 Oracle存储过程没有返回值，它的所有返回值都是通过 out参数来替代的，列表同样也
不例外，但由于是集合，所以不能用一般的参数，必须要用 pagkage了，所以要分两部分：
1) 建一个包，在该包中，定义类型 testcursor，是个游标。如下：
Createorreplacepackagetestpackageastypetest_cursorisrefcursor;
Endtestpackage;
2) 创建过程：
createorreplaceproceduresp_pro9
(spnoinnumber,_cursor)is
begin
openp_cursorforselect*fromempwheredeptno=spno;
end;
//演示 Java程序去调用 Oracle的储存过程案例
;
//一个无返回值的过程
.*;
PublicclassTestOraclePro{
/**
*@paramargs
*/
Publicstaticvoidmain(String[]args){
//TODOAuto-generatedmethodstub
Try{
//1.加载驱动
(“”);
//2.得到连接
connectionct=(”jdbc:oracle:thin:@
:1521:YY”,”scott”,”tiger”);
//3.创建 CallableStatement
CallableStatementcs=(”{callsp_pro8(？,？)}”);
//4.给？赋值
(1,10);
(2,);
//5.执行
();
//取出返回值
Resultsetrs=(Resultset)(2);
while(()){
((1)+””+(2));
}
//6.关闭资源
();
();
}catch(Exceptione){
();
//TODOhandleexception
}
分页过程编写
案例：编写一个存储过程，要求可以输入表名，每页显示记录数，当前页。返回总记录数，总
页数，和返回的结果集。
1、建一个包，
--在该包中，定义类型 testcursor，是个游标
Createorreplacepackagetestpackageastypetest_cursorisrefcursor;
Endtestpackage;
2、创建分页过程
createorreplaceprocedurefenye
(tablenameinvarchar2,
pagesizeinnumber,--一页显示记录数
pagenowinnumber,
myrowsoutnumber,--总记录数
mypagecountoutnumber,--总页数
_cursor)is--返回的记录集
--定义部分
--定义 sql语句，字符串
v_sqlvarchar2(1000);
--执行部分
v_beginnumber:=(pagenow-1)*pagesize+1;
v_endnumber:=pagenow*pagesize;
begin
--执行部分,先按工资升序排序，再查出结果
v_sql:='select*from(selectt1.*,rownumrnfrom
(select*from'||tablename||'orderbysal)t1whererownum<='||v_end||')
wherern>='||v_begin;
--把游标和 sql关联
openp_cursorforv_sql;
--计算 myrows和 mypagecount
--组织一个 sql
v_sql:='selectcount(*)from'||tablename;
--执行 sql，并把返回的值赋给 myrows
executeimmediatev_sqlintomyrows;
--计算 mypagecount
ifmod(myrows,pagesize)=0then
mypagecount:=myrows/pagesize;
else
mypagecount:=myrows/pagesize+1;
endif;
closep_cursor;
end;
3、Java调用分页过程
;
.*;
PublicclassTestOraclePro{
/**
*@paramargs
*/
Publicstaticvoidmain(String[]args){
//TODOAuto-generatedmethodstub
Try{
//1.加载驱动
(“”);
//2.得到连接
connectionct=(”jdbc:oracle:thin:@
:1521:YY”,”scott”,”tiger”);
//3.创建 CallableStatement
CallableStatementcs=(”{callfenye(?,?,?,?,?,?)}”);
//4.给？赋值
(1,”emp”);//从 EMP表中取数据
(2,5);//每页 5条记录
(3,1);//显示第 1页
//注册总记录数
(4,);
//注册总页数
(5,);
//注册返回的结果集
(6,);
//5.执行
();
//取出总记录数，getInt(4)中 4是由该参数的位置决定的
IntrowNum=(4);
IntpageCount=(5);
resultSetrs=(resultSet)(6);
//显示结果
(“总记录数:”+rowNum);
(“总页数:”+pageCount);
While(()){
(“编号：”+(1)+”名字：”+(2));
}
//6.关闭资源
();
();
}catch(Exceptione){
();
//TODOhandleexception
}
PL/SQL编程—例外
Oracle将例外分为预定义例外、非预定义例外和自定义例外三种。
预定义例外用于处理常见的 Oracle错误；
非预定义例外用于处理预定义例外不能处理的例外；
自定义例外用于处理与 Oracle错误无关的其他情况。
例外—预定义例外 no_data_found
预定义例外是由 pl/sql所提供的系统例外，当 pl/sql应用程序违反了 Oracle规定的限制时，
则会隐含的触发一个内部例外，Pl/sql为开发人员提供了 21 个预定义例外。
案例:编写一个过程，可以接收雇员的编号，并显示该雇员的姓名。
如果输入雇员编号不存在，如何处理？
declare
%type;
begin
selectenameintov_enamefromempwhereempno=&no;
_line('姓名'||v_ename);
exception
whenno_data_foundthen
_line('编号不存在');
end;
例外—预定义例外 case_not_found
在开发 pl/sql块中编写 case语句时，如果在 when子句中没有包含必须的条件分支，就会触
发 case_not_found的例外：
createorreplaceproceduresp_pro10(spnonumber)is
%type;
begin
selectsalintov_salfromempwhereempno=spno;
case
whenv_sal<1000then
updateempsetsal=sal+100whereempno=spno;
whenv_sal<2000then
updateempsetsal=sal+200whereempno=spno;
endcase;
exception
whencase_not_foundthen
_line('case语句没有与'||v_sal||'相匹配的条件');
end;
例外—预定义例外 cursor_already_open
当重新打开已经打开的游标时，会隐含的触发例外 cursor_already_open
declare
cursoremp_cursorisselectename,salfromemp;
begin
openemp_cursor;
foremp_record1inemp_cursorloop
_line();
endloop;
exception
whencursor_already_openthen
_line('游标已经打开');
end;
例外—预定义例外 dup_val_on_index
在唯一索引所对应的列上插入重复的值时，会隐含的触发例外 dup_val_on_index例外。
begin
insertintodeptvalues(10,'公关部','北京');
exception
whendup_sal_on_indexthen
_line('在 deptno列上不能出现重复值');
end;
例外—预定义例外 invaild_cursor
当试图在不合法的游标上执行操作时，会触发该例外。
例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标，则会触发该例外。
declare
cursoremp_cursorisselectename,salfromemp;
emp_recordemp_cursor%rowtype;
begin
--openenp_cursor;--打开游标
fetchemp_cursorintoemp_record;
_line();
closeemp_cursor;
exception
wheninvalid_cursorthen
_line('请检测游标是否打开');
end;
例外—预定义例外 invalid_number
当输入的数据有误时，会触发该例外
比如：数字 100写成 1oo就会触发该例外
begin
updateempsetsal=sal+'1oo';
exception
wheninvalid_numberthen
_line('输入的数字不正确');
end;
例外—预定义例外 too_many_rows
当执行 selectinto语句时，如果返回的值超过了一行，则会触发该例外。
declare
%type;
begin
selectenameintov_enamefromemp;
exception
whentoo_many_rowsthen
_line('返回了多行值');
end;
例外—预定义例外 zero_divide
当执行除以 0语句时，则会触发该例外。
declare
v_abcnumber(5,2);
begin
v_abc:=9/0;
_line(v_abc);
exception
whenzero_dividethen
_line('分母不能为 0');
end;
例外—预定义例外 value_error
当执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外 value_error,比
如：
declare
v_enamevarchar2(5);
begin
selectenameintov_enamefromempwhereempno=&no;
_line(v_ename);
exception
whenvalue_errorthen
_line('变量尺寸不足');
end;
例外—其他预定义例外
1、login_denide,当用户非法登录时，会触发该例外
2、not_logged_on，如果用户没有登录就执行 dml操作，就会触发该例外
3、storage_error，如果超出了内存空间或是内存被破坏，就触发该例外
4、timeout_on_resource，如果 oracle在等待资源时，出现了超时就触发该例外
例外—自定义预定义例外
预定义例外和自定义例外都是与 oracle错误相关的，并且出现的 oracle错误会隐含的触发相
应的例外，而自定义例外与 oracle错误没有任何关联，它是由开发人员为特定情况所定义的
例外
案例：编写一个 pl/sql块，接收一个雇员的编号，并给该雇员工资增加 1000元，如果该雇员
不存在，请提示。
createorreplaceprocedureex_test(spnonumber)
is
--定义一个例外
myexexception;
begin
--更新用户 sal
updateempsetsal=sal+100whereempno=spno;
--sql%notfound表示没有 update
--raisedmyex表示触发 myex
ifsql%notfoundthen
raisemyex;
endif;
exception
whenmyexthen
_line('输入编号不正确，没有更新任何用户工资');
end;
PL/SQL编程—视图
视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行
数据，但是，视图并不在数据库中以存储的数据集形式存在，行和列数据来自由定义视图的查
询所引用的表，并且在引用视图时动态生成。
视图与表的区别：
表需要占用空间，视图不需要
视图不能添加索引
使用视图可以简化复杂查询
视图可以提高安全性
1、创建视图：
Createview视图名 asselect语句[whitreadonly]
2、创建或修改视图
createorreplaceview视图名 asselect语句[withreadonly]
3、删除视图
dropview 视图名
案例：
--创建视图，把 emp 表的 sal<2000的雇员映射到该视图(view)
createviewmyviewasselect*fromempwheresal<2000;
