Oracle复习
1、查询
1.1、伪表dual
DUAL是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。以用它来做很多事情，如：
1. 查看当前用户
Select user from dual;
2. 用来调用系统函数
--查询系统的当前时间并格式化
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;
3. 得到序列的下一个值或当前值
--获得序列seq的下一个值
select seq.nextval from dual;
--获得序列seq的当前值
select seq.currval from dual;
1.2、伪列rowid
rowid是物理结构上的，在每条记录insert到数据库中时，都会有一个唯一的物理记录，同一条记录在不同查询中对应的rowid相同。
【用法】
SELECT ROWID,字段名... FROM 表名;
【示例】
selectrowid, emp.*from emp;
1.3、伪列rownum
rownum是根据sql查询出的结果给每行分配一个逻辑编号；每次的查询都会有不同的编号。编号从1开始。
【用法】
SELECT ROWNUM,字段名... FROM 表名;
【注意】
ROWNUM 不能使用大于号“>”
即 select rownum, emp.* from emp where rownum > 2 是不对的，没有任何结果
【示例】
selectrownum, emp.*from emp;
/*关于分页：由于不能使用>，所以为了达到分页目的得如下执行；如获取第2页数据（每页3条）*/
select*from(selectrownum r,emp.*from emp whererownum<7)where r >3;
/*关于排序：由于rownum是查询结果的行编号，排序后这个编号便有可能被打乱，如果需要该编号和排序的结果列表序号保持一致可以如下执行*/
selectrownum,t.*from(select empno,ename from emp orderby empno desc) t;
1.4、连接查询
1、等值查询
--查询emp表中各用户对应的部门名称
select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;
2、左外/右外连接查询：
左外连接是在等号左边的集合，无论条件是否成立均在结果集合，写法就是在等号右边使用(+)，这个写法是oracle专用的，如果需要全数据库类型通用应该使用left join）
select d.deptno,d.dname,count(e.empno)from dept d left join emp e
on d.deptno=e.deptno groupby d.deptno,d.dname;
3、自连接查询
查询的2张表是同一张表，一般是该表的字段之间存在上下级关系
select e.ename ||' 的老板是: '|| b.ename from emp e,emp b
where e.mgr=b.empno;
【注意】上述查询语句中的||表示为字符的连接
1.5、组合查询
1、 计算部门工资总和，最高工资，最低工资
select deptno,sum(sal),max(sal),min(sal)from emp groupby deptno;
1、部门平均工资
--查询部门的平均工资
select deptno,avg(sal)from emp groupby deptno;
--查询平均工资大于2000的部门，并按照平均工资降序排序
select deptno,avg(sal)平均工资from emp
group by deptno
having avg(sal)>2000
order by平均工资desc;
--查询除了20部门以外，平均工资大于2000的部门
select deptno,avg(sal)from emp
where deptno <>20
group by deptno
having avg(sal)>2000;
【注意】SQL语句中的各子句执行顺序：
from->where->group by->having->select->order by
3、子查询
将子查询放入括号中；group by后不能使用子查询；select、from、where后面都可以使用子查询；可以将子查询看作一张新表
--select后面的子查询
select(select dname from dept where deptno=10),ename from empwhere deptno=10;
--from后面的子查询
select*from(select ename,sal from emp);
--将子查询视为一个表
select e.ename,e.sal from(select ename,sal from emp) e;
4、其他查询
--查询姓名是5个字符的员工，且第二个字符是C，使用_只匹配一个字符并且不能标识0或多个字符
Select * from emp where ename like'_C___';
--查询员工姓名中含有‘_’的员工，使用\转义字符
Select * from emp where ename like'%\_%' escape '\';
2、TCL事务控制语言
2.1、提交
要永久变更数据需要显示地执行提交、回滚或者退出当前回话（如退出sqlplus）。
提交的命名为：commit；
2.2、保存点与回滚
保存点savepoint一般与回滚rollback配合使用。在设置了savepoint后事务的粒度可以控制的更加细化，可以回滚到特定的保存点。
【语法】保存点savepoint
SAVEPOINT <savepoint_name>;
【示例】
--创建一个保存点，名称为a
savepoint a;
【注意】当创建保存点之后执行的DML操作，可以进行回滚，而保存点之前未提交的DML操作不受影响。
【语法】回滚
ROLLBACK [TO savepoint];
【示例】
--回滚到保存点a,即在保存点a之后的所有未提交的DML都无效。
rollback to a;
3、运算符
3.1、算术运算符
+、-、*、/
3.2、比较（关系）运算符
＝、!＝、<>、< 、 > 、 <= 、 >= 、 between…and… 、in 、like
、is null
3.3、逻辑运算符
AND(逻辑与)，表示两个条件必须同时满足
OR(逻辑或)，表示两个条件中有一个条件满足即可
NOT(逻辑非)，返回与某条件相反的结果
3.4、连接运算符
【示例】
select'工号为：'|| empno ||' 的员工的姓名为：'|| ename from emp;
3.5、集合运算符
union（并集无重复）
union all（并集有重复）
intersect（交集，共有部分）
minus（减集，第一个查询具有，第二个查询不具有的数据）
【注意】：列数相关，对应列的数据类型兼容，不能含有Long类型的列，第一个select语句的列或别名作为结果标题
--union(并集将去重复)
Select * from emp where deptno=10
union
select * from emp where deptno=20;
--intersect(交集) 查询工资即属于1000~2000区间和1500~2500区间的工资
select ename,sal from emp where sal between1000and2000
intersect
select ename,sal from emp where sal between1500and2500;
--minus(减集)
select ename,sal from emp where sal between1000and2000
minus
select ename,sal from emp where sal between1500and2500;
3.6、运算符优先级
| 优先级 | 运算符 |
|---|---|
| 1 | 算术运算符 |
| 2 | 连接符 |
| 3 | 比较符 |
| 4 | IS[NOT]NULL, LIKE, [NOT]IN |
| 5 | [NOT] BETWEEN |
| 6 | NOT |
| 7 | AND |
| 8 | OR |
可以使用括号改变优先级顺序；OR的优先级最低，算术运算符的优先级最高。
4、常用函数
4.1、数值型函数
round(x，y)
【功能】返回四舍五入后的值
【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则四舍五入为y位小数，如果y小于0则四舍五入到小数点向左第y位。
【示例】
Selectround(5555.6666,2.1),round(5555.6666,2.6),round(5555.6666)from dual;
trunc(x，y)
【功能】返回x按精度y截取后的值
【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示。
【示例】
selecttrunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333)from dual;
4.2、字符型函数
LENGTH(c1)
【功能】返回字符串的长度;
【说明】多字节符(汉字、全角符等)，按1个字符计算
【示例】
selectlength('abcd'),length('itcastabcd')from dual;
LPAD(c1，n，c2)****、RPAD(c1，n，c2)
【功能】在字符串c1的左（右）边用字符串c2填充，直到长度为n时为止
【说明】如果c1长度大于n，则返回c1左边n个字符
【示例】
selectlpad('itcast',10,'*'),rpad('itcast',10,'*')from dual;
REPLACE(c1，c2，c3])
【功能】将字符表达式值中，部分相同字符串，替换成新的字符串
【参数】
c1 希望被替换的字符或变量
c2 被替换的字符串
c3 要替换的字符串，默认为空(即删除之意，不是空格)
【示例】
selectreplace('he love you','he','i')from dual;
SUBSTR(c1，n1，n2)
【功能】取子字符串
【说明】多字节符(汉字、全角符等)，按1个字符计算
【参数】在字符表达式c1里，从n1开始取n2个字符;若不指定n2,则从第n1个字符直到结束的字串.
【示例】
Select substr('123456789',4,4),substr('123456789',3)from dual;
4.4、日期函数
sysdate
【功能】：返回当前日期。
【参数】：没有参数，没有括号
【返回】：日期
【示例】select sysdate from dual;
add_months(d1,n1)
【功能】：返回在日期d1基础上再加n1个月后新的日期。
【参数】：d1，日期型，n1数字型
【返回】：日期
【示例】select sysdate,add_months(sysdate,3)from dual;
months_between(d1,d2)
【功能】：返回日期d1到日期d2之间的月数。
【参数】：d1，d2 日期型
【返回】：数字
如果d1>d2，则返回正数
如果d1<d2，则返回负数
【示例】
selectsysdate,
months_between(sysdate,to_date('2015-01-01','YYYY-MM-DD'))距2015元旦,
months_between(sysdate,to_date('2016-01-01','YYYY-MM-DD'))距2016元旦from dual;
extract(c1 from d1)
【功能】：日期/时间d1中，参数(c1)的值
【参数】：d1日期型(date)/日期时间型(timestamp),c1为字符型(参数)
【参数表】：c1对应的参数表详见示例
【返回】：字符
【示例】
select
extract(YEAR from timestamp'2015-5-1 12:26:18 ')年,
extract(MONTH from timestamp'2015-5-1 12:26:18 ')月,
extract(DAY from timestamp'2015-1-5 12:26:18 ')日,
extract(hour from timestamp'2015-5-1 12:26:18 ')小时,
extract(minute from timestamp'2015-5-1 12:26:18')分钟,
extract(second from timestamp'2015-5-1 12:26:18 ')秒
from dual;
4.5、转换函数
TO_CHAR(x，c2，C3)
【功能】将日期或数据转换为char数据类型
【参数】
x是一个date或number数据类型。
c2为格式参数
c3为NLS设置参数
【返回】varchar2字符型
【示例】
select to_char（sysdate,'YYYY-MM-DD HH24:MI:SS'）FROM dual;
select to_char（1210.7,'$9,999.00'）FROM dual;
TO_DATE(X，c2，c3)
【功能】将字符串X转化为日期型
【参数】c2,c3,字符型，参照to_char()
【返回】字符串
如果x格式为日期型(date)格式时，则相同表达：date x
如果x格式为日期时间型(timestamp)格式时，则相同表达：timestamp x
【示例】
select to_date('201212','yyyymm'),
to_date('2012.12.20','yyyy.mm.dd'),
(date'2012-12-20') XXdate,
to_date('2012-12-20 12:31:30','yyyy-mm-dd hh24:mi:ss'),
to_timestamp('2012-12-20 12:31:30','yyyy-mm-dd hh24:mi:ss'),
(timestamp'2012-12-20 12:31:30') XXtimestamp
from dual;
TO_NUMBER(X，c2，c3)
【功能】将字符串X转化为数字型
【参数】c2,c3,字符型
【返回】数字串
【示例】
select TO_NUMBER('201212')+3,TO_NUMBER('450.05')+1from dual;
--等同上述结果
Select '201212'+3 from dual;
4.6、聚合函数
sum:求和
avg:求平均数
count：计数
max：求最大值
min：求最小值
4.7、分析函数
分析函数中了解rank()/dense_rank()/row_number()的使用：
--RANK 使用相同排序排名一样，后继数据空出排名；即有2个排序为1的，那么接下来的排序号则为3
select deptno,ename,job,rank()over(partition by deptno order by job)as myRank from emp e;
--DENSE_RANK使用，使用相同排序排名一样，后继数据不空出排名；即有2个排序为1的，那么接下来的排序号则为2
--ROW_NUMBER使用，不管排名是否一样，都按顺序排名；即有2个排序为1的，那么排序号不4.会重现重复
4.8、其他函数
NVL()/NVL2()
【语法】NVL (expr1, expr2)
【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。注意两者的类型要一致
【示例】将员工的奖金如果是空的话则设置为0
select ename,sal,comm,nvl(comm,0)from emp;
【语法】NVL2 (expr1, expr2, expr3)
【功能】expr1不为NULL，返回expr2；expr1为NULL，返回expr3。
expr2和expr3类型不同的话，expr3会转换为expr2的类型
【示例】
select ename,job,nvl2(job,'job有值','job无值')from emp;
**decode(**条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值)
【功能】根据条件返回相应值
【参数】c1, c2, ...,cn,字符型/数值型/日期型，必须类型相同或null
注：值1……n 不能为条件表达式,这种情况只能用case when then end解决
【示例】根据员工的部门号，条件判断找到对应的部门名称
select ename,deptno,decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES','无部门')from emp;
5、视图
视图是由一个或者多个表组成的虚拟表。一般出于对基本的安全性和常用的查询语句会建立视图；并且一般情况下不对视图进行新增、更新操作。
作用：
①简化了操作，把经常使用的数据定义为视图。
②安全性，用户只能查询和修改能看到的数据。
③逻辑上的独立性，屏蔽了真实表的结构带来的影响。
缺点:
①性能差
②修改限制
视图分为简单视图和复杂视图**：**
1、简单视图只从单表里获取数据，复杂视图从多表；
2、简单视图不包含函数和数据组，复杂视图包含；
3、简单视图可以实现DML操作，复杂视图不可以。
【语法】
--创建视图
CREATE [OR REPLACE] VIEW <view_name>
AS
<SELECT 语句>;
--删除视图
DROP VIEW <view_name> ;
6、同义词
同义词是数据库模式对象(表)的一个别名，经常用于简化对象访问和提高对象访问的安全性。
在Oracle数据库中的大部分数据库对象，如表、视图、同义词、序列、存储过程等，数据库管理员都可以根据实际情况为他们定义同义词。隐藏对象名称和所有者。
6.1、私有同义词
私有Oracle同义词由创建它的用户所有；创建的用户需要具有CREATE SYNONYM权限。
【语法】
CREATE SYNONYM <synonym_name> for <tablename/viewname...>
【示例】
--管理员授权用户itcast创建同义词的权限
Grant create synonym to itcast;
--创建私有同义词
Create synonym syn_emp for emp;
--为视图v_emp创建私有同义词（别名）
Create synonym syn_v_emp for v_emp;
6.2、公有同义词
公有Oracle同义词由一个特殊的用户组Public所拥有。顾名思义，数据库中所有的用户都可以使用公有同义词。
【语法】
CREATE PUBLIC SYNONYM <synonym_name> for <tablename/viewname...>
--登陆sys管理员用户，授权用户itcast创建、删除(公有的删除权限需要特别给定)公有同义词权限
Grant create public synonym,drop public synonym to itcast;
--revoke create public synonym,drop public synonym from itcast;
--登陆itcast用户创建公有同义词 conn itcast/itcast;
Create public synonym syn_public_emp for emp;
视图和同义词的区别：
1.视图可以对应一张或多张表，同义词只能对应一张表名称
2.视图可以设置其他约束条件
3.可以在同义词上建立视图
4.可以通过对同义词进行任何DML操作，复杂视图不支持DML操作
7、索引
索引是建立在数据库表中的某些列的上面，是与表关联的，可提供快速访问数据方式，但会影响增删改的效率；常用类型（按逻辑分类）：单列索引和组合索引、唯一索引和非唯一索引。
什么时候要创建索引
（1）在经常需要搜索、主键、连接的列上
（2）表很大，记录内容分布范围很广
（3）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
（4）在经常使用在WHERE子句中的列上面创建索引
什么时候不要创建索引
（1）表经常进行 INSERT/UPDATE/DELETE 操作
（2）表很小(记录超少)
（3）列名不经常作为连接条件或出现在 WHERE 子句中
（4）对于那些定义为text, image和bit数据类型的列不应该增加索引
优点
1.大大加快数据的检索速度;
2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
3.加速表和表之间的连接;
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
缺点
1.索引需要占物理空间。
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
【语法】
CREATE [UNIQUE] INDEX <index_name> ON <table_name>(字段 [ASC|DESC]);
【说明】
UNIQUE –保证列中值的唯一性。
[ASC|DESC] --在列上按指定排序创建索引。
【示例】
--创建单列唯一索引，表中的列值将不允许重复
Create unique index index_emp_empno on emp(empno);
--创建单列非唯一索引
Create index index_emp_ename on emp(ename);
--创建组合列、唯一索引
Create unique index index_emp_ename_job on emp(ename,job);
--创建组合列、非唯一索引
Create index index_emp_job_sal on emp(job,sal);
删除索引
【语法】
DROP INDEX <index_name>;
【示例】
--删除索引
Drop index index_emp_empno;
8、序列
序列是oracle提供的一个产生唯一数值型值的机制。通常用于表的主健值，序列只能保证唯一，不能保证连续。
创建序列
【语法】
CREATE SEQUENCE <sequencen_name>
[INCREMENT BY n]
[START WITH n]
[MAXVALUE n][MINVALUE n]
[CYCLE|NOCYCLE]
[CACHE n|NOCACHE];
INCREMENT BY n --表示序列每次增长的幅度;默认值为1.
START WITH n --表示序列开始时的序列号。默认值为1.
MAXVALUE n --表示序列可以生成的最大值(升序).
MINVALUE n --表示序列可以生成的最小值(降序).
CYCLE --表示序列到达最大值后，在重新开始生成序列.默认值为 NOCYCLE。
CACHE n--允许更快的生成序列.预先生成n个序列值到内存（如果没有使用完，那下次序列的值从内存最大值之后开始；所以n不应该设置太大）
【示例】
--创建递增序列
Create sequence seq_test
Increment by 1
Start with 1
Maxvalue 1000
nocycle;
--创建递减序列
createsequence seq_test2
incrementby-1
startwith5
maxvalue5
minvalue1
nocycle;
序列使用
1、NEXTVAL 返回序列下一个值；第一次访问时，返回序列的初始值，后继每次调用时，按步长增加的值返回
【语法】
select <sequence_name>.nextval from dual;
【示例】
select seq_test.nextval from dual;
2、CURRVAL 返回序列的当前值.注意在刚建立序列后,序列的CURRVAL值为NULL，所以不能直接使用。使用过NEXTVAL访问序列后才能使用
【语法】查看序列的当前值
select <sequence_name>.currval from dual;
【示例】
select seq_test.nextval from dual;
select seq_test.currval from dual;
3、修改序列
--修改序列
Alter sequence seq_emp_empno
Maxvalue 9999
cycle;
4、删除序列
【语法】
DROP SEQUENCE <sequence_name>
【示例】
Drop sequence seq_test;
5、序列与sys_guid
sys_guid和序列都可以作为主键值。
--使用SYS_GUID函数，32位，由时间戳和机器标识符生成，保证唯一
select sys_guid()from dual;
9、PL/SQL
pl/sql:块结构语言，是sql语言的一种扩展，结合了oracle过程语言进行使用。
pl/sql块由三部分构成：声明部分、执行部分、异常部分。
9.1、PL/SQL结构
[DECLARE]
--声明变量等；
BEGIN
--程序主要部分，一般用来执行过程语句或SQL语句；
[EXCEPTION]
--异常处理；
END;
9.2、运算符
| ＝ | 等于 | 比较运算符 |
|---|---|---|
| <>,!=,~=,^= | 不等于 | |
| < | 小于 | |
| > | 大于 | |
| <= | 小于或等于 | |
| >= | 大于或等于 | |
| + | 加号 | 算术运算符 |
| - | 减号 | |
| * | 乘号 | |
| / | 除号 | |
| := | 赋值号 | 赋值运算符 |
| => | 关系号 | 关系号 |
| … | 范围运算符 | 范围运算符 |
| || | 字符连接符 | 连接运算符 |
| is null | 是空值 | 逻辑运算符 |
| between and | 介于两者之间 | |
| in | 在一系列值中间 | |
| and | 逻辑与 | |
| or | 逻辑或 | |
| not | 取反 |
9.2、变量与常量
数据类型：
常用标准类型：CHAR(CHARATER,NCHAR),VARCHAR2,NUMBER(P,S),DATE,BOOLEAN等。
属性类型：%TYPE 与 %ROWTYPE
%TYPE:可以用来定义数据变量的类型与已定义的数据变量（表中的列）一致。
%ROWTYPE：与某一数据库表的结构一致(修改数据库表结构，可以实时保持一致）；访问方式声明为 rowtype的 变量名.字段名。
9.3、基本类型
声明
【变量声明】
<变量名> 类型[:=初始值];
【示例】
name varchar2(20) := 'itcast';
【常量声明】
<变量名> CONSTANT 类型:=初始值;
【示例】
pi constant number(5,3):=3.14;
运用
/*定义常量或变量、赋值使用示例*/
DECLARE
p_empno constant number(4):=7369;
p_ename varchar2(10);
p_sal number(7,2);
p_comm number(7,2);
BEGIN
--赋值方式一：使用select into给变量赋值
select ename,sal into p_ename,p_sal from emp where empno =p_empno;
--赋值方式二：使用赋值操作符“:=”给变量赋值
p_comm:=500;
--输出相关信息，DBMS_OUTPUT.PUT_LINE为具有输出功能的函数
dbms_output.put_line('员工号:'|| p_empno||',姓名:'|| p_ename||',工资:'|| p_sal||',奖金:'|| p_comm);
END;
9.4、%type类型
声明
【声明】
变量名称 表名.字段%type;
【示例:】
--表示变量name的类型和emp.ename的类型相同
name emp.ename%type;
运用
/*定义常量或变量、赋值使用示例*/
DECLARE
p_empno constantnumber(4):=7369;
p_ename emp.ename%type;
p_sal emp.sal%type;
p_comm emp.comm%type;
BEGIN
--赋值方式一：使用select into给变量赋值
select ename,sal into p_ename,p_sal from emp where empno = p_empno;
--赋值方式二：使用赋值操作符“:=”给变量赋值
p_comm:=500;
--输出相关信息，DBMS_OUTPUT.PUT_LINE为具有输出功能的函数
dbms_output.put_line('员工号:'|| p_empno||',姓名:'|| p_ename||',工资:'|| p_sal||',奖金:'|| p_comm);
END;
9.5、%rowtype类型
声明
【声明】
变量名称 表%rowtype;
【示例:】
--表示变量test的类型为emp表的行类型;也有 .empno; .ename; .sal ;等属性
test emp%rowtype;
运用
EGIN
--赋值方式一：使用select into给变量赋值
select*into emp_info from emp where empno = p_empno;
--赋值方式二：使用赋值操作符“:=”给变量赋值
p_comm:=500;
--输出相关信息，DBMS_OUTPUT.PUT_LINE为具有输出功能的函数
dbms_output.put_line('员工号:'|| p_empno||',姓名:'|| emp_info.ename ||',工资:'|| emp_info.sal ||',奖金:'|| p_comm);
END;
9.6、控制语句
9.6.1、条件语句
【语法】
IF <条件1> THEN
语句
[ELSIF <条件2> THEN
语句]
.
.
.
[ELSIF <条件n> THEN
语句]
[ELSE
语句]
END IF;
【示例】
/*
根据员工的工资判断其工资等级（工资大于等于5000为A级，工资大于等于4000为B级，工资大于等于3000为C级，工资大于等于2000为D级，其它为E级）
*/
DECLARE
p_empno number(4):=7566;
p_sal emp.sal%type;
BEGIN
--用变量代替条件语句中的真值
select sal into p_sal from emp where empno = p_empno;
IF p_sal >=5000THEN
dbms_output.put_line('员工号为：'|| p_empno ||'的员工的工资级别为：A级');
ELSIF p_sal >=4000THEN
dbms_output.put_line('员工号为：'|| p_empno ||'的员工的工资级别为：B级');
ELSIF p_sal >=3000THEN
dbms_output.put_line('员工号为：'|| p_empno ||'的员工的工资级别为：C级');
ELSIF p_sal >=2000THEN
dbms_output.put_line('员工号为：'|| p_empno ||'的员工的工资级别为：D级');
ELSE
dbms_output.put_line('员工号为：'|| p_empno ||'的员工的工资级别为：E级');
END IF;
END;
9.6.2、循环语句
1、LOOP
【语法】
LOOP
语句;
EXIT WHEN <条件>
END LOOP;
【示例】
/*
计算1-10的总和
*/
DECLARE
p_sum number(4):=0;
p_num number(2):=1;
BEGIN
LOOP
p_sum := p_sum + p_num;
p_num := p_num +1;
EXITWHEN p_num >10;
END LOOP;
dbms_output.put_line('1-10的总和为：'|| p_sum);
END;
2、WHILE LOOP
【语法】
WHILE <条件>
LOOP
语句;
END LOOP;
【示例】
/*
计算1-10的总和
*/
DECLARE
p_sum number(4):=0;
p_num number(2):=1;
BEGIN
WHILE p_num <=10
LOOP
p_sum := p_sum + p_num;
p_num := p_num +1;
ENDLOOP;
dbms_output.put_line('1-10的总和为：'|| p_sum);
END;
3**、FOR**
【示例】
FOR <循环变量> IN[REVERSE] 下限..上限
LOOP
语句;
END LOOP;
【说明】..两点表示范围，1..4表示时将从1到4进行循环，起始（例如 1）写前边，REVERSE表示反转，循环时变成从4到1进行。
【示例】
/*
计算1-10的总和
*/
DECLARE
p_sum number(4):=0;
p_num number(2):=1;
BEGIN
FOR p_num IN 1..10
LOOP
p_sum := p_sum + p_num;
ENDLOOP;
dbms_output.put_line('1-10的总和为：'|| p_sum);
END;
9.6.3、顺序语句
指定顺序执行的语句；主要包括 null语句。null语句：是一个可执行语句，相当于一个占位符或不执行操作的空语句。主要用来提高程序语句的完整性和程序的可读性。
/*
输出1-10的数字但跳过数字4
*/
DECLARE
flag number(2):=0;
BEGIN
WHILE flag <10
LOOP
flag := flag +1;
if flag =4then
null;-- 占位，不能去掉
else
dbms_output.put_line(flag);
endif;
ENDLOOP;
END;
9.7、异常处理
9.7.1、异常语法
EXCEPTION
WHEN <异常类型> THEN
语句;
WHEN OTHERS THEN
语句;
常配套使用的函数：
SQLCODE函数：返回错误代码，
SQLERRM函数：返回错误信息
【示例】
输出异常信息：
DBMS_OUTPUT.PUT_LINE('其它异常，代码号：'||SQLCODE||'，异常描述:'||SQLERRM);
9.7.2、预定义异常
预定义异常指PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发(由oracle自动引发)。
常见的预定义异常：
CURSOR_ALREADY_OPEN 试图"OPEN"一个已经打开的游标
DUP_VAL_ON_INDEX 试图向有"UNIQUE"中插入重复的值
INVALID_CURSOR 试图对以关闭的游标进行操作
ZERO_DIVIDE 除数为零
【示例】
/*
预定义异常捕获并处理
*/
DECLARE
p_result number(2);
BEGIN
p_result :=1/0;
dbms_output.put_line('没有异常！');
EXCEPTION
WHEN ZERO_DIVIDE THEN
dbms_output.put_line('除数不能为0！代码为：'||sqlcode||'，异常信息为：'||sqlerrm);
WHEN OTHERS THEN
dbms_output.put_line('其它异常！代码为：'||sqlcode||'，异常信息为：'||sqlerrm);
END;
9.7.3、自定义异常
自定义异常：程序在运行过程中，根据业务等情况，认为非正常情况，可以自定义异常。
对于这种异常，主要分三步来处理：
1、定义相关异常；在声明部分定义相关异常
【语法】
<自定义异常名称> EXCEPTION;
2、抛出异常；在出现异常部分抛出异常
【语法】
RAISE <异常名称>；
3、处理异常；在异常处理部分对异常进行处理
【语法】
when <自定义异常名称> then ...，
其中参数ERROR_NUMBER取值为-20999~-20000的负整数，参数ERROR_MESSAGE为异常文本消息。
/*
判断emp中相应empno对应用户的奖金是否低于500，如果低于则抛出并处理自定义异常
*/
DECLARE
p_comm emp.comm%type;
--自定义异常，名称为comm_exception
comm_exception EXCEPTION;
BEGIN
Select nvl(comm,0)into p_comm from emp where empno=7499;
--nvl(comm,0)如果comm为null就填充0
if p_comm >=500then
dbms_output.put_line('奖金大于等于500。');
else
RAISE comm_exception;
End if;
EXCEPTION
WHEN comm_exception THEN
RAISE_APPLICATION_ERROR(-20001,'奖金低于500，太少了！');
--dbms_output.put_line('奖金低于500！');
WHEN OTHERS THEN
dbms_output.put_line('其它异常！代码为：'||sqlcode||'，异常信息为：'||sqlerrm);
END;
10、游标
10.1、显示游标
游标是映射在结果集中一行数据上的位置实体，使用游标，便可以访问结果集中的任意一行数据了，将游标放置到某行后，即可对该行数据进行操作；从上向下依次迭代结果集。
游标语法
【定义语法】
CURSOR <游标名> IS <SELECT 语句> ;
【操作】
OPEN <游标名> --打开游标
FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,;
或者
FETCH <游标名> INTO 行对象; --取出游标当前位置的值
CLOSE <游标名> --关闭游标
【属性】
%NOTFOUND --如果FETCH语句失败，则该属性为"TRUE"，否则为"FALSE";
%FOUND --如果FETCH语句成果，则该属性为"TRUE"，否则为"FALSE";
%ROWCOUNT --返回游标当前行的行数;
%ISOPEN --如果游标是开的则返回"TRUE"，否则为"FALSE";
游标使用
代参数的游标
【定义】
CURSOR <游标名>(参数列表) IS <SELECT 语句>;
【示例】
declare
cursor cur_emp(dno emp.deptno%type)isselect ename,job,sal from emp where deptno=dno;
r_cur_emp cur_emp%rowtype;
begin
--打开游标
open cur_emp(20);
loop
--取游标数据，从上往下移动一行
fetch cur_emp into r_cur_emp;
--如果下移后没有数据，则退出
exitwhen cur_emp%notfound;
--如果存在数据，则处理
dbms_output.put_line('姓名为：'|| r_cur_emp.ename ||'，工作为：'|| r_cur_emp.job ||'，工资为：'|| r_cur_emp.sal);
endloop;
--关闭游标
close cur_emp;
end;
10.2、隐式游标
当执行一个SQL语句时，Oracle会自动创建一个隐式游标，隐式游标主要处理DML语句，该游标的名称是sql。隐试游标不能进行"OPEN" ,“CLOSE”,"FETCH"这些操作。
属性:
%NOTFOUND 如果DML语句没有影响到任何一行时，则该属性为"TRUE"，否则为"FALSE";
%FOUND 如果DML语句影响到一行或一行以上时，则该属性为"TRUE"，否则为"FALSE";
%ROWCOUNT 返回游标当最后一行的行数;
/*
通过更新语句判断隐式游标的存在
*/
begin
update emp set comm=comm +300 where empno =7369;
if sql %notfound then
dbms_output.put_line('empno对应的员工不存在');
else
dbms_output.put_line('empno对应的员工数为：'||sql%rowcount);
endif;
end;
11、存储过程与存储函数
11.1、存储过程
存储过程是命名的pl/sql程序块，封装数据业务操作，具有模块化、可重用、可维护、更安全等特点；并且可以被程序调用。
一般有4类型的存储过程，分别为不带参数、带输入参数、带输出参数、带输入输出参数。
【语法】
CREATE [OR REPLACE] PROCEDURE <过程名>[(参数列表)] IS|AS
[局部变量声明]
BEGIN
可执行语句
[EXCEPTION
异常处理语句]
END [<过程名>];
OR REPLACE：如果系统已存在该存储过程，将被替换
参数列表：参数不需要声明长度，可选
参数变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出;
【调用方式】
在PL/SQL块中直接使用过程名;
在PL/SQL程序外使用 exec[ute] <过程名>[(参数列表)];
无参存储过程
-- 授予itcast创建存储过程的权限
Grant create procedure to itcast;
/*
使用无参存储过程，注意无参存储过程创建时不能使用()
*/
Create or replace procedure pro_helloWorld
as
begin
dbms_output.put_line('Hello World.');
end;
-- 方式一：调用存储过程，可加可不加()
begin
pro_helloWorld;
end;
-- 方式二：调用存储过程，可加可不加()
Exec pro_helloWorld;
有输入参数存储过程
/*
使用有输入参存储过程
*/
create or replace procedure pro_add_emp(
p_empno in emp.empno%type,
p_ename in varchar2,
p_sal number
)
as
begin
--将输入参数对应的数据插入emp表
insertinto emp(empno, ename,sal)values(p_empno, p_ename, p_sal);
end;
/
-- 调用存储过程，向emp表插入新数据
begin
pro_add_emp(2001,'itcast2001',3000);
pro_add_emp(2002,'itcast2002',2000);
pro_add_emp(2003,'itcast2003',4000);
end;
有输出参数存储过程
/*
使用有输出参存储过程，计算1到10的总和并通过参数返回
*/
create or replace procedure pro_1to10_sum(
p_sum out number
)
As
tem_sum number(4):=0;
begin
for i in1..10
loop
tem_sum := tem_sum + i;
endloop;
p_sum := tem_sum;
end;
/
-- 调用存储过程
declare
p_sum number(4);
begin
pro_1to10_sum(p_sum);
dbms_output.put_line('1至10的和为：'|| p_sum);
end;
有输入输出参数存储过程
/*
使用有输入、输出参存储过程；根据empno查询该员工号对应的员工的姓名和工资
*/
createorreplaceprocedure pro_query_enameAndSal_by_empno(
s_empno emp.empno%type,
s_ename out emp.ename%type,
s_sal out emp.sal%type
)
as
begin
select ename,sal into s_ename, s_sal from emp where empno= s_empno;
end;
/
-- 调用存储过程
declare
p_ename emp.ename%type;
p_sal emp.sal%type;
begin
--pro_query_enameAndSal_by_empno(7369, p_ename, p_sal);
pro_query_enameAndSal_by_empno(7369, s_sal => p_sal, s_ename => p_ename);
dbms_output.put_line('员工号为7369的员工名称为：'|| p_ename||'，其工资为：'|| p_sal);
end;
程序中调用存储过程
package cn.itcast;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
publicclass TestProcedure {
publicstaticvoid main(String[] args) {
Connection conn = null;
CallableStatement call = null;
try {
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
conn = DriverManager.getConnection(url, "itcast", "itcast");
call = conn.prepareCall("{call pro_query_enameAndSal_by_empno(?,?,?)}");
//设置输入型参数
call.setInt(1, 7369);
//注册输出型参数
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
//调用存储过程
call.execute();
//获取返回值
String ename = call.getString(2);//员工名称
double sal = call.getDouble(3);//员工工资
System.out.println("员工号为7369的员工名称为：" + ename + "，工资为：" + sal);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(call != null){
call.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
删除存储过程
【语法】
DROP PROCEDURE <过程名>;
【示例】
Drop procedure pro_1to10_sum;
11.2、存储函数
存储函数与过程不同的是，存储函数有return语句；一般情况下如果在需要一个返回值时可使用存储函数。
语法
CREATE [OR REPLACE] FUNCTION <函数名>[(参数列表)] RETURN 数据类型 IS|AS
[局部变量声明]
BEGIN
可执行语句
[EXCEPTION
异常处理语句]
RETURN 返回值;
END [<函数名>];
变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出;
【使用方式】
直接在select中使用和其它系统函数使用方式一样；
在PL/SQL块中调用使用；
无参存储函数
使用无参存储函数；注意创建时函数名称不能使用()
但是在调用时候可加可不加()
*/
createorreplacefunction fun_helloWorld
returnvarchar2
as
begin
return'Hello World';
end;
/
-- 方式1：调用存储函数
select fun_helloWorld()from dual;
-- 方式2：调用存储函数
declare
str varchar2(20);
begin
str :=fun_helloWorld;
dbms_output.put_line(str);
end;
有输入参数存储函数
/*
使用存储函数：根据员工号，查询并返回该员工的年薪
*/
createorreplacefunction fun_get_annualSal_by_empno(p_empno emp.empno%type)
returnnumber
as
p_sal emp.sal%type;
p_comm emp.comm%type;
begin
select sal,comm into p_sal, p_comm from emp where empno=p_empno;
return12*p_sal +nvl(p_comm,0);
end;
/
-- 调用存储函数
select fun_get_annualSal_by_empno(7369)from dual;
有输入输出参数存储函数
/*
使用具有输入输出参数的存储函数：根据员工号，查询并返回该员工的年薪,姓名，奖金
*/
createorreplacefunction fun_get_annualSal_by_empno2(
p_empno emp.empno%type,
p_ename out emp.ename%type,
p_comm out emp.comm%type
)
returnnumber
as
p_sal emp.sal%type;
begin
select ename,sal,nvl(comm,0)into p_ename,p_sal, p_comm from emp where empno=p_empno;
return12*p_sal + p_comm;
end;
/
-- 调用存储函数
declare
p_annualSal number(10,2);
p_ename emp.ename%type;
p_comm emp.comm%type;
begin
p_annualSal := fun_get_annualSal_by_empno2(7499,p_ename,p_comm);
dbms_output.put_line('员工姓名为：'||p_ename||',奖金为：'||p_comm||'，年薪为：'||p_annualSal);
end;
程序中调用存储函数
package cn.itcast;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
publicclass TestFunction {
publicstaticvoid main(String[] args) {
Connection conn = null;
CallableStatement call = null;
try {
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
conn = DriverManager.getConnection(url, "itcast", "itcast");
call = conn.prepareCall("{? = call fun_get_annualSal_by_empno2(?,?,?)}");
//注册存储函数返回值
call.registerOutParameter(1, OracleTypes.DOUBLE);
//设置输入参数，员工号
call.setInt(2, 7499);
//注册输出参数，员工姓名
call.registerOutParameter(3, OracleTypes.VARCHAR);
//注册输出参数，奖金
call.registerOutParameter(4, OracleTypes.DOUBLE);
call.execute();
System.out.println("员工姓名为：" + call.getString(3) + "，奖金为：" + call.getDouble(4)
+ "，年薪为：" + call.getDouble(1));
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(call != null){
call.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
删除存储函数
【语法】
DROP FUNCTION <函数名>;
【示例】
dropfunction fun_helloWorld;
dropfunction fun_get_annualSal_by_empno;
dropfunction fun_get_annualSal_by_empno2;
存储过程与存储函数的区别
1、函数有返回值，过程没有返回值
2、调用的区别,函数可以在sql语句中直接调用,而存储过程必须单独调用；
3、函数一般情况下是用来计算并返回一个计算结果，而存储过程一般是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等）
12、触发器
当发生特定的DML操作时触发操作。
语法
【语法】
CREATE [OR REPLACE] TRIGGER <触发器名>
BEFORE|AFTER
INSERT|DELETE|UPDATE [OF <列名>] ON <表名>
[FOR EACH ROW] --行级触发器
<pl/sql块>
【说明】
关键字"BEFORE"在操作完成前触发;"AFTER"则是在操作完成后触发;
关键字"FOR EACH ROW"指定触发器每行触发一次，若不指定则为表级触发器.
关键字"OF <列名>" 不写表示对整个表的所有列.
pl/sql块中不能使用commit;
【特殊变量】
:new --为一个引用最新的行值;
:old --为一个引用以前的行值;
这些变量只有在使用了关键字 "FOR EACH ROW"时才存在.且update语句两个都有,而insert只有:new ,delect 只有:old;
12.1、行级触发器
【示例1】涨工资
/*
触发器使用：给员工涨工资（涨后工资应该大于涨前）后，在后台输出更新前和更新后的工资
*/
Create or replace trigger tri_emp_upd_sal
after
update of sal on emp
for each row
begin
if:old.sal <:new.sal then
dbms_output.put_line('更新前工资为：'||:old.sal||'，更新后工资为：'||:new.sal);
else
raise_application_error(-20002,'工资不能越涨越低！');
endif;
end;
/
-- 更新工资值，并触发行级触发器
update emp set sal =8888where empno =1002;
【示例2】触发器+序列实现主键自增长
/*
触发器使用：给emp表的empno添加触发器，在插入记录时自动填入值
*/
-- 1、创建序列
Create sequence seq_emp_empno;
-- 2、创建触发器
Create or replace trigger tri_emp_ins_empno
before
inserton emp
for eachrow
begin
-- 给将要插入表的记录:new 中的empno设置sequence中的值
select seq_emp_empno.nextval into:new.empno from dual;
end;
/
-- 新增员工数据，测试触发器+序列的组合使用
insertinto emp(ename,sal)values('itcast002',2000);
commit;
12.2、表级触发器
/*
触发器使用：删除表的同时备份表数据到另一张备份表
*/
-- 1、从emp表结果中创建一张表并复制数据
createtable emp2 as select*from emp;
-- 2、创建备份表emp_bak
createtable emp_bak as select*from emp2 where1=2;
-- 3、创建表触发器，当对表操作时触发
createorreplacetrigger tri_emp2_del
before
delete on emp2
begin
-- 将emp2表中的数据备份到emp_bak
Insert into emp_bak select*from emp2;
end;
/
-- 4、测试删除emp2表的数据
deletefrom emp2;
select*from emp2;
select*from emp_bak;
12.3、开启禁用触发器
【禁用某个触发器】
ALTER TRIGGER <触发器名> DISABLE
【示例】
altertrigger tri_emp_upd_sal disable;
update emp set sal =8888where empno =1002;
【重新启用触发器】
ALTER TRIGGER <触发器名> ENABLE
【示例】
altertrigger tri_emp_upd_sal enable;
update emp set sal =8888where empno =1002;
【禁用表的所有触发器】
ALTER TABLE <表名> DISABLE ALL TRIGGERS;
【示例】
altertable emp disablealltriggers;
【启用表的所有触发器】
ALTER TABLE <表名> ENABLE ALL TRIGGERS;
【示例】
altertable emp enablealltriggers;
【删除触发器】
DROP TRIGGER <触发器名>;
【示例】
droptrigger tri_emp_upd_sal;
13、数据字典
Oracle 数据字典是有表和视图组成，它们存放在 SYSTEM 表空间中， 数据字典中的表是不能直接被访问的，但是可以访问数据字典中的视图。
数据字典分类
数据字典分为数据字典表和数据字典视图
数据字典表
数据字典表里的数据是 Oracle 系统存放的系统数据，而普通表存放的是用户的数据。
数据字典视图
静态数据字典（静态性能视图）：包括了所有数据库对象的信息。
动态数据字典（动态性能视图）：这些视图会不断的进行更新，从而提供了关于内存和磁盘的运行情况，所以我们只能对其 进行只读访问而不能修改它们。
14、角色
Oracle提供了三种标准的角色（role）：CONNECT、RESOURCE和DBA。
CONNECT Role(连接角色)
临时用户，特别是那些不需要建表的用户，通常只赋予他们CONNECT role。
CONNECT是使用Oracle的简单权限，这种权限只有在对其他用户的表有访问权时，包括select、insert、update和delete等，才会变得有意义。
RESOURCE Role(资源角色)
更可靠和正式的数据库用户可以授予RESOURCE role。RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程、触发器、索引和簇。
DBA Role(数据库管理员角色)
DBA role拥有所有的系统权限--包括无限制的空间限额和给其他用户授予各种权限的能力。
14.1、创建角色
创建角色后，可以对角色授予权限；授权的语法和前面授权给用户的语法相同。
【语法】
CREATE ROLE <role_name>;
【示例】
-- system 用户登录，授予itcast 创建角色的权限
Grant create role to itcast;
-- 创建角色
Creat erole role_itcast;
-- 授予emp的select 操作权限给role_itcast角色
Grant selecton emp to role_itcast;
-- 给scott用户授予role_itcast的角色
grant role_itcast to scott;
14.2、删除角色
【语法】
DROP ROLE <role_name>;
【示例】
droprole role_itcast;
15、闪回
闪回技术可以实现数据的快速恢复，而且不需要数据备份。
闪回特点
传统的恢复技术缓慢:它是整个数据库或者一个文件恢复，不只恢复损坏的数据在数据库日志中每个修改都必须被检查；
闪回速度快:通过行和事务把改变编入索引，仅仅改变了的数据会被恢复；
闪回命令容易，没有复杂步骤。
闪回类型
主要有三种闪回：闪回表(flashback table)、闪回删除(flashback drop)、闪回数据库（flashback database）；一般情况下对数据库的闪回需要配置闪回数据库，然后自动产生闪回日志；再根据闪回日志恢复数据库。
15.1、闪回查询
根据闪回日志可以快速查询在某个时间点的数据。
--查看10秒之前的emp表
select * from emp as of timestamp sysdate - interval'10'second;
select * from emp as of scntimestamp_to_scn(sysdate - interval'10'second);
【说明】
as of timestamp 是固定写法,查询某个时间点对应的数据
as of scn查询某scn对应的数据
sysdate – interval ‘10’second 是时间值的计算
--通过查询某个时间的数据来更新现有数据
--将7499员工的姓名更新为5分钟之前的姓名
update emp e set ename =
(select ename from emp
as of timestamp systimestamp - interval'5'minute where empno=e.empno)
where empno=7499;
15.2、闪回表
闪回表(flashback table)实际上是将表中的数据快速恢复到过去的一个焦点或者系统改变号SCN上；对进行表闪回的表必须row movement为enable。
SCN: System Change Number.
实现表的闪回，需要使用到与撤销表空间相关的undo信息，通过show parameter undo命令可以了解这些信息。
conn sys/orcl as sysdba
show parameters undo; // undo表空间
alter system set undo_retention=1200 scope=both;
undo_retention：数据保留时间长度（默认是900秒）
scope参数的值：
momory-当前session中有效
spfile: 修改配置文件，但当前会话中无效
both:当前会话有效，同时修改配置文件
undo表空间：保存了所有的操作记录(2G的空间) 因为有了该表空间才可以进行闪回
【语法】
flashback table [schema.]table_name[,...n] to {[scn] | [timestamp] [[enable | disable] triggers]};
【说明】
scn：表示通过系统改变号进行闪回；scn系统改变号一般和系统时间相对应；查看当前系统时间和所对应系统scn：
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'), timestamp_to_scn(sysdate)from dual;
timestamp：表示通过时间戳的形式来进行闪回；
enable|disable triggers：表示触发器恢复之后的状态，默认为disable。
rowid这个伪列是Oracle默认提供给每一个表的，主要用于记录每一行数据存储的磁盘物理地址。当删除一行记录后，后面的记录依次跟进上来，当
需要恢复某一个中间的行时，就需要行具备行移动功能（alter table <表名> enable row movement;）
【示例】
-- 授权用户闪回表的权限
Grant flashback any table to itcast;
-- 查看当前时间点或scn号
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'), timestamp_to_scn(sysdate)from dual;
-- 删除数据
deletefrom emp where empno =7449;
commit;
--允许行移动
altertable emp enablerowmovement;
-- 方式一；使用时间点闪回表
Flashback table emp to timestamp to_timestamp('时间格式字符串','yyyy-mm-dd HH24:mi:ss');
-- 方式二；使用SCN闪回表
flashbacktable emp to scn SCN号;
15.3、闪回删除
闪回删除(flashback drop)。当整个表被删除并在回收站查询到的话；可以对表进行闪回
对于系统参数的修改有两种，全局的修改和会话的修改：
（1）alter system set param_name=param_value;
（2）alter session set param_name=param_value;
select * from recyclebin; --查看回收站
delete from recyclebin; --清空回收站
【语法】
flashback table table_name to before drop [rename to new_name];
【说明】
rename to new_name：如果在删除原表之后又重新创建了一个一样名称的表，那么恢复回收站的表时可以对表名进行重命名
【示例】
-- 删除表
droptable emp;
-- 恢复表
flashbacktable emp tobeforedrop;
16、数据备份与恢复
16.1、数据备份
--全表备份
exp itcast/itcast@orcl file=d:\database\oracle_data\itcast.dmp full=y;
--指定表备份
exp itcast/itcast@orcl file=d:\database\oracle_data\itcast_emp_dept.dmp tables=(emp,dept);
【说明】full：完整导出数据库，一般使用system具有管理员权限的用户在命令行下进行操作。
16.2、数据恢复
--全表恢复
imp itcast/itcast@orcl ignore=y file=d:\database\oracle_data\itcast.dmp full=y;
--指定表恢复
imp itcast/itcast@orcl ignore=y file=d:\database\oracle_data\itcast_emp_dept.dmp tables=(emp,dept);
【说明】ignore：忽略创建错误
17、性能优化
1、 查两张以上表时，把记录少的放在右边
2、 WHERE****子句中的连接顺序
ORACLE采用自上而下的顺序解析WHERE子句,根据这个原则,那些可以过滤掉最大数量记录的条件应写在WHERE子句最后。
例如：查询员工的编号，姓名，工资，部门名
如果emp.sal>1500能过滤掉半数记录的话，
select emp.empno,emp.ename,emp.sal,dept.dname
from emp,dept
where (emp.deptno = dept.deptno) and (emp.sal > 1500)
3、 SELECT****子句中避免使用*号
ORACLE在解析的过程中,会将*依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间
4、 避免对大表进行无条件或无索引的的扫描
5、 清空表时用TRUNCATE替代DELETE
6、 尽量多使用COMMIT；因为COMMIT会释放回滚点
7、 用索引提高查询效率，善用索引*****
避免在索引列上使用NOT；因为Oracle服务器遇到NOT后，他就会停止目前的工作，转而执行全表扫描。
避免在索引列上使用计算；WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描，这样会变得慢
例如，SAL列上有索引，
低效：
SELECT EMPNO,ENAME
FROM EMP
WHERE SAL*12 > 24000;
高效：
SELECT EMPNO,ENAME
FROM EMP
WHERE SAL > 24000/12;
8、字符串型，能用=号，不用like
=号表示精确比较，like表示模糊比较
9、 用 >= 替代 >
低效：
SELECT * FROM EMP WHERE DEPTNO > 3
首先定位到DEPTNO=3的记录并且扫描到第一个DEPT大于3的记录
高效：
SELECT * FROM EMP WHERE DEPTNO >= 4
直接跳到第一个DEPT等于4的记录
10、 用IN替代OR
select * from emp where sal = 1500 or sal = 3000 or sal = 800;
select * from emp where sal in (1500,3000,800);
11、 用exists代替in；not exists代替 not in
not in 字句将执行一个内部的排序和合并，任何情况下，not in是最低效的，子查询中全表扫描；表连接比exists更高效
12、 用UNION-ALL 替换UNION
当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率会因此得到提高。
13、 避免使用耗费资源的操作
带有DISTINCT,UNION,MINUS,INTERSECT的SQL语句会启动SQL引擎 执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写。
663
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
