Oracle数据库全面详解（学习笔记）

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  from dual;

--获得序列seq的当前值
select  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 =;

2、左外/右外连接查询：

左外连接是在等号左边的集合，无论条件是否成立均在结果集合，写法就是在等号右边使用(+)，这个写法是oracle专用的，如果需要全数据库类型通用应该使用left join）

select ,,count()from dept d left join emp e 
on = groupby ,;

3、自连接查询

查询的2张表是同一张表，一般是该表的字段之间存在上下级关系

select  ||' 的老板是: '||  from emp e,emp b
where =;
【注意】上述查询语句中的||表示为字符的连接

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 , 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',''),
(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的类型和的类型相同
name %type;

运用

/*定义常量或变量、赋值使用示例*/
DECLARE
     p_empno constantnumber(4):=7369;
     p_ename %type;
     p_sal %type;
     p_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 %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 %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 %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 %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 %type,
       s_ename out %type,
       s_sal out %type
)
as
begin
select ename,sal into s_ename, s_sal from emp where empno= s_empno;
end;
/

-- 调用存储过程
declare
 p_ename %type;
 p_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 ;

import ;
import ;
import ;
import ;

import ;

publicclass TestProcedure {

	publicstaticvoid main(String[] args) {
		Connection conn = null;
		CallableStatement call = null;
		try {
			("");
			String url = "jdbc:oracle:thin:@localhost:1521:orcl";
			conn = (url, "itcast", "itcast");
			call = ("{call pro_query_enameAndSal_by_empno(?,?,?)}");
			//设置输入型参数
			(1, 7369);
			//注册输出型参数
			(2, );
			(3, );
			//调用存储过程
			();
			//获取返回值
			String ename = (2);//员工名称
			double sal = (3);//员工工资
			("员工号为7369的员工名称为：" + ename + "，工资为：" + sal);
		} catch (Exception e) {
			();
		} finally {
			try {
				if(call != null){
					();
				}
				if(conn != null){
					();
				}
			} catch (SQLException e) {
				();
			}
		}
	}
}

删除存储过程

【语法】
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 %type)
returnnumber
as
p_sal %type;
p_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 %type,
p_ename out %type,
p_comm out %type
)
returnnumber
as
p_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 %type;
p_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 ;

import ;
import ;
import ;
import ;

import ;

publicclass TestFunction {

	publicstaticvoid main(String[] args) {
		Connection conn = null;
		CallableStatement call = null;
		try {
			("");
			String url = "jdbc:oracle:thin:@localhost:1521:orcl";
			conn = (url, "itcast", "itcast");
			call = ("{? = call fun_get_annualSal_by_empno2(?,?,?)}");
			//注册存储函数返回值
			(1, );
			//设置输入参数，员工号
			(2, 7499);
			//注册输出参数，员工姓名
			(3, );
			//注册输出参数，奖金
			(4, );
			();
			("员工姓名为：" + (3) + "，奖金为：" + (4)
					+ "，年薪为：" + (1));
		} catch (Exception e) {
			();
		} finally {
			try {
				if(call != null){
					();
				}
				if(conn != null){
					();
				}
			} catch (SQLException e) {
				();
			}
		}
	}
}

删除存储函数

【语法】
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: <: then
    dbms_output.put_line('更新前工资为：'||:||'，更新后工资为：'||:);
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: 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=) 
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\ 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\  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子句最后。  
例如：查询员工的编号，姓名，工资，部门名  
      如果>1500能过滤掉半数记录的话，
      select ,,,
      from emp,dept
      where ( = ) and ( > 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语句都可以用其他方式重写。