晨魅--ORACLER常用命令

ORACLER常用命令
3    创建和管理表
3.1    数据类型
3.1.1    字符类型
VARCHAR2()
CLOB 是最大的字符数据类型，最大长度4GB。
3.1.2    数字型
NUMBER 表示整数或小数。
NUMBER(n)表示一个n位的整数。
NUMBER(m,n) 如NUMBER(6,3)表示一个小数有6位有效数，其中3位小数。
3.1.3    日期类型
DATE
3.1.4    图片
BLOB 最大为4GB。
3.2    创建表
3.2.1    表和列的命名规则
(1)    必须以字母开头。
(2)    长度不能超过30字符
(3)    不能使用Oracle保留字
(4)    只能使用如下字符：A~Z，a~z，0~9，$，#等。
(5)    不能和已经存在的其他对象重名。
3.2.2    建表语句
create table tbbook  --tbbook  表名
(
    b_id number not null,  --设置字段不为空
    b_title varchar2(20),  --字段为字符型，长度20
    b_type varchar2(20),
    b_publisher varchar2(20),
    constraint tbbook_pk primary key(b_id)  --设置该表的主键为b_id。
);
3.2.3    虚拟列
创建表的时候可以创建虚拟列，其完整定义包括列名，数据类型，GENERATED ALWAYS关键字，AS（列表达式）和VIRTUAL关键字，如下所示。
列名 数据类型 GENERATED ALWAYS AS (列表达式) VIRTUAL
例：创建职工信息表，有职工编号W_ID字段，职工姓名W_NAME字段，职工出生年份W_BIRTHYEAR字段和入职年份W_INTIME字段，另外有虚拟列W_INAGE表示职工入职时的年龄，由职工出生年份和入职年份计算得出。创建职工信息表代码如下。
CREATE TABLE TBWORK(
    W_ID NUMBER,
    W_NAME VARCHAR2(20),
    W_BIRTHYEAR NUMBER,
    W_INTIME NUMBER,
    W_INAGE NUMBER GENERATED ALWAYS AS (W_INTIME-W_BIRTHYEAR) VIRTUAL
);
向表中添加数据。
INSERT INTO TBWORK(W_ID,W_NAME,W_BIRTHYEAR,W_INTIME) VALUES(1,'何明',1984,2007);
COMMIT;
查看表中数据。
SELECT * FROM TBWORK;

3.2.4    不可见列
在oracle 12c中，用户可以定义不可见列。不可见列查询时不会被查询出来，除非在SQL语句或条件中有显示地提及这一列，或是在表定义中有DESCRIBED。
虚拟列和区分列同样也可以定义为不可见类型。但临时表，外部表和集群表并不支持不可见列。对于不可见列添加数据，必须在INSERT语句中显示提及不可见列名，将不可见列插入到数据库中。
列的可见和不可见属性可通过INVISIBLE（不可见）和VISIBLE（可见）两个关键字来设置，如在创建表的时候创建不可见列，代码如下。
列名 数据类型 INVISIBLE
也可以使用ALTER TABLE语句修改数据列的可见和不可见属性，代码如下。
ALTER TABLE 表名 MODIFY (列名 INVISIBLE | VISIBLE);
例：创建学生成绩表，有学生学号，姓名，语文成绩，数学成绩和总成绩字段，为保护学生隐私，将学生姓名设置为不可见列，在查询成绩时仅显示学生学号和成绩信息。另外，将总成绩字段设置为虚拟列，值为语文成绩和数学成绩的和，代码如下。
CREATE TABLE TBREPORT(
    R_ID NUMBER NOT NULL,
    R_NAME VARCHAR2(20) INVISIBLE,
    R_CHINESE NUMBER,
    R_MATHS NUMBER,
    R_TOTAL NUMBER GENERATED ALWAYS AS (R_CHINESE + R_MATHS) VIRTUAL
);
向表中插入数据。
INSERT INTO TBREPORT(R_ID,R_NAME,R_CHINESE,R_MATHS) VALUES(1,'梁红',88,72);
COMMIT;
查看表中数据。
SELECT * FROM TBREPORT;
执行结果。
R_ID    R_CHINESE    R_MATHS    R_TOTAL    
1        88            72         160
默认查询没有提供学生的姓名信息。使用查询语句查询表中指定列的数据。
SELECT R_NAME,R_CHINESE,R_MATHS,R_TOTAL FROM TBREPORT;
执行结果如下所示。
R_NAME    R_CHINESE    R_MATHS    R_TOTAL    
梁红        88            72         160
3.3    修改表
alter table tbbook rename to tbbooks; --将表名tbbook修改为tbbooks
3.4    添加列
添加单个列：
ALTER TABLE tbbooks ADD b_writer VARCHAR2(20); --向表tbbooks里添加列，列名为b_writer
添加多个列：
ALTER TABLE t_rygzffxx_shff_zz ADD(
  V_TFBS     VARCHAR2(50),                
  N_NZJ      NUMBER(16,2),                
  N_WXJBC   NUMBER(16,2),
N_FWTZBT  NUMBER(16,2)
);
commit;
查询表结构
DESC TBBOOKS --查询上表的表结构
3.5    修改列
3.5.1    修改列名
ALTER TABLE tbbooks RENAME COLUMN b_publisher TO b_pub; 
--将tbbooks 表的b_publisher列名修改为b_pub
3.5.2    修改列类型及数据精度
如果表中有数据只能由小修改到大，无数据可以由大修改到小。
ALTER TABLE table_name MODIFY column_name new_datatype; 
--table_name表名， column_name列名，new_datatype修改之后的数据类型
3.5.3    修改列的默认值
ALTER TABLE table_name MODIFY(column_name DEFAULT default_value)；
--table_name表名，column_name列名，default_value修改后的默认值
3.6    删除列
ALTER TABLE 表名 DROP COLUMN 列名； --指定删除的列名

3.7    删除数据表
DROP TABLE 表名 [CASCADE CONSTRAINTS] [PURGE];
--CASCADE CONSTRAINTS 可选参数，删除表的同事也删除表的视图，索引，约束和触发器。
--PURGE 可选参数，删除成功后释放占用的资源
3.7.1    清空表数据
TRUNCATE TABLE 表名；
--TRUNCATE 清空表中数据，不能回滚。
3.8    约束简介
表级别的约束：定义在一个表中，可以用于表中多个列。
列级别的约束：对表中的一列进行约束，只能应用于一个列。
PRIMARY KEY:主键约束
FOREIGN KEY:外键约束
UNIQUE:唯一性约束
NOT UNLL:非空约束
CHECK:检查约束
使用CONSTRAINT关键字为约束命名。
3.8.1    主键约束
3.8.1.1    创建主键约束用CONSTRAINT约束名PRIMARYKEY语句
CONSTRAINT 约束名 PRIMARY KEY (主键字段)
3.8.1.2    为已经创建的表添加主键约束使用ADD CONSTRAINT语句
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (主键字段)
3.8.1.3    删除主键使用ALTER TABLE 表名 DROP 语句
ALTER TABLE 表名 DROP CONSTRINT 约束名
3.8.2    唯一约束
范例：设置唯一约束
CREATE TABLE WORKERS --表名
(
W_ID NUMBER NOT NULL,
  W_NAME VARCHAR2(20) CONSTRAINT NAME_PK UNIQUE,
--字段名   字段类型    CONSTRAINT 约束名   UNIQUE
  W_SEX VARCHAR2(20),
  W_AGE NUMBER
)
3.8.2.1    创建唯一约束使用CONSTRAINT UNIQUE语句
字段名 字段类型 CONSTRAINT 约束名 UNIQUE
3.8.2.2    在现有表中添加UNIQUE唯一约束使用ADD UNIQUE语句
ALTER TABLE 表名 ADD UNIQUE(列名);
3.8.2.3    添加约束的同时为唯一约束命名
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名)
3.8.2.4    删除UNIQUE约束
3.8.2.4.1    删除表中指定的UNIQUE约束
ALTER TABLE 表名 DROP UNIQUE(列名);
3.8.2.4.2    删除多个列的唯一约束
ALTER TABLE 表名 DROP CONSTRAINT 约束名;
3.8.3    非空约束
数据不能为空，但是可以是0或空字符串；一个表里可以定义多个NOT NULL约束，定义后该列不能包含NULL值；如果数据中有NULL值，不能添加NOT NULL约束。
范例：
CREATE TABLE WORKERS --表名
(
W_ID NUMBER NOT NULL, --非空约束
W_NAME VARCHAR2(20) NOT NULL,--非空约束
W_SEX VARCHAR2(20) NULL, --可以为空
W_AGE NUMBER
);
3.8.3.1    修改非空约束使用ALTER TABLE 表名 MODIFY语句
ALTER TABLE 表名 MODIFY 列名 NOT NULL/NULL;
3.8.4    外键约束
CREATE TABLE 表名
(
F_ID NUMBER NOT NULL,
F_NAMF VARCHAR2(20) NOT NULL,
F_TYPE VARCHAR2(20) NULL,
F_PRICE NUMBER,
F_WORKER REFERENCES 关联表名(W_ID) 
-- 列名 数据类型 REFERENCES 关联表名(关联列)
)
注意：表1的F_WORKER字段和表2的W_ID字段关联，名可以不同，类型必须相同。
3.8.4.1    添加外键约束
ALTER TABLE 表1  ADD CONSTRAINT  约束名 FOREIGN KEY (表1外键列) REFERENCES 表2(表2关联列)
3.8.4.2    删除外键
ALTER TABLE 表名 DROP CONSTRAINT 约束名
3.8.5    检查约束
CREATE TABLE 表名
(
S_ID NUMBER NOT NULL,
S_NAME VARCHAR2(20),
S_AGE NUMBER CONSTRAINT 约束名 CHECK(S_AGE>0 AND S_AGE<20)
--列名 数据类型 CONSTRAINT 约束名 CHECK(约束条件)
)
3.8.5.1    添加CHECK约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK (约束条件)
3.8.5.2    删除CHECK约束
ALTER TABLE 表名 DROP CONSTRAINT 约束名
3.8.6    禁止和激活约束
禁止约束使用DISABLE关键字
ALTER TABLE 表名 DISABLE CONSTRAINT 约束名;
激活约束使用ENABLE关键字。
方法1：ALTER TABLE 表名 ENABLE CONSTRAINT 约束名;
方法2：ALTER TABLE 表名 MODIFY ENABLE CONSTRAINT 约束名;
通过查询字典USER_CONS_COLUMNS，可以了解定义约束的列。下面是USER_CONS_COLUMNS视图中部分的说明，如下所示。
owner      varchar2(30)    约束的所有者
constraint_name    varchar2(30)    约束名
table_name     varchar2(30)      约束所属的表
column_name   varchar2(4000)    约束所定义的列
例：查询TEACHER表中的约束定义在哪列上。
SELECT CONSTRAINT_NAME,COLUMN_NAME FROM USER_CONS_COLUMNS 
WHERE TABLE_NAME=’TEACHER’;
查询TEACHER表中的约束信息。
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS  FROM USER_CONSTRAINTS 
WHERE TABLE_NAME=’TEACHER’;
3.8.6.1    约束状态
激活验证状态：默认状态。
激活非验证状态
禁止验证状态
禁止非验证状态
在非验证状态下激活约束比在验证状态下激活约束节省时间。
3.8.7    延迟约束
延迟约束的两种状态：
INITIALLY DEFERRED:约束的初始状态是延迟检查
INITIALLY IMMEDIATE:约束的初始状态是立即检查。
修改约束的延迟状态使用ALTER TABLE … MODIFY CONSTRAINT语句。
ALTER TABLE 表名 MODIFY CONSTRAINT 约束名 INITIALLY DEFERRED |INITIALLY IMMEDIATE;
如果约束的延迟已经存在，则可以使用SET CONSTRAINTS ALL语句将所有约束切换为延迟状态。表现如下：
如果设置为SET CONSTRAINTS ALL DEFERRED,则延迟检查。
如果设置为SET CONSTRAINTS ALL IMMEDIATE,则立即检查。
延迟约束是在事务被提交时强制执行的约束。添加约束时可以通过DEFERRED子句来指定约束为延迟约束。约束一旦创建以后，就不能修改为DEFERRED延迟约束。
注意：oracle中是不能修改任何非延迟性约束的延迟状态的。
4    单表查询
4.1    简单查询
4.1.1    SELECT语句的语法
SELECT [ALL | DISTINCT] 字段名   --ALL 返回查询结果集的所有行；DISTINCT去重复的。
FROM 表名
WHERE 条件
GROUP BY 字段名  HAVING 条件 --GROUP BY分组函数，HAVING对分组进行条件控制。两个组合使用
ORDER BY 字段名 [ASC | DESC]   --排序 升序|降序
4.1.2    算数表达式
加+ ，减- ，乘* ，除/ ;
例：SELECT ename,sal,sal+200+300 FROM emp 
4.1.3    为列指定别名
例：SELECT ename,sal,sal+200+300 AS newsal FROM emp  --关键字AS，不是必须的，可不写。如果别名包含特殊字符，需要用双引号括起来。
4.1.4    获取不重复数据
SELECT DISTINCT(job) FROM emp   --job需要去重复的字段
4.2    Where子句
4.2.1.1    基本比较运算符
SELECT * FROM emp WHERE ename='JONES'
4.2.1.2    使用IS NULL 和IS NOT NULL运算符
IS NULL和IS NOT NULL用来判断内容是否为NULL值。在where子句中需要考虑空值可能带来的影响，NULL和任意值（包括NULL值）运算，结果仍为空值。用比较运算符比较NULL值和任意值（包括NULL值）时，结果都为UNKNOWN。
SELECT COUNT(*) FROM emp WHERE comm IS NULL;
SELECT COUNT(*) FROM emp WHERE comm IS NOT NULL;
4.2.1.3    使用IN和NOT IN运算符
IN表示在指定的列表范围内查找内容，而NOT IN与之相反。（in的范围列表不能大于1000）
列：SELECT empno,ename,jop,sal FROM emp WHERE empno IN (1900,3900,5900,7900,9900);
4.2.1.4    使用LIKE和NOT LIKE运算符
例：要求ename列以任意字符开头，第二个字母必须是M。
SELECT * FROM emp WHERE ename LIKE '_M%';  --通配符%匹配任意长度，_匹配单个长度。
4.2.1.5    使用BETWEEN AND 和 NOT BETWEEN AND运算符
BETWEEN AND表示取指定范围内的记录，NOT BETWEEN AND反之。
例：查询emp表中empno列的值不在7500和8000之间的记录。
SELECT empno,ename,job,sal FROM emp WHERE empno NOT BETWEEN 7500 AND 8000;                                                                                                                                                                                      
4.2.2    使用逻辑运算符
逻辑运算符：AND（与），OR（或），NOT（非）。
列：查询emp表中comm列的值为NULL，并且empno列的值在7300~7500之间的记录。
SELECT empno,ename,job,sal FROM emp 
WHERE comm IS NULL AND(empno BETWEEN 7300 AND 7500);
4.2.3    获取前N条数据
4.2.3.1    ROWNUM对于等于某值的查询条件
ROWNUM都是从1开始，1以上的自然数在ROWNUM做等于判断时被认为是FALSE条件，所以无法查询ROWNUM=n（n是大于1的自然数）
例：通过伪列查询emp表中的第一条记录。
SELECT * FROM emp WHERE ROWNUM=1;
4.2.3.2    ROWNUM对于大于某值的查询条件
如果想找到从第二行记录以后的记录需要用子查询，在子查询中ROWNUM必须要有别名，否则还是不会查出任何记录。如果不起别名，无法知道ROWNUM是子查询列还是主查询的列。
例：SELECT *FROM(SELECT ROWNUM wl,empno,ename,job FROM emp ) WHERE wl>9;
如果使用ROWNUM 查询在某区间的数据，必须用子查询。
例：查询2行到3行之间的数据，包括2行和第3行数据。
SELECT * FROM (SELECT ROWNUM wl,empno,ename,job 
FROM emp WHERE ROWNUM <=3) WHERE wl>=2;  
--ROWNUM 无法查询大于1的自然数，所以先查<=3，在用主查询查>=2。（数据量较大会影响查询速度）
4.2.3.3    ROWNUM 对于小于某值的查询条件
ROWNUM 伪列对于ROWNUM <n（n表示大于1的自然数）的条件成立。
例：用ROWNUM 查询emp表中前三条记录。
SELECT empno,ename,sal FROM emp WHERE ROWNUM <=3;
4.3    操作查询结果
4.3.1    对查询结果排序 ORDER BY
使用ORDER BY子句排序，ASC升序，DESC降序。
对多个列排序：SELECT empno,ename,sal FROM emp WHERE ROWNUM<=3 
ORDER BY sal DESC,ename;
4.3.2    对查询结果分组 GROUP BY
对多个列分组：SELECT deptno,job,COUNT(*),SUM(sal),AVG(sal) 
FROM emp GROUP BY deptno,job    
Group by支持rollup和cube运算符，如果是Group by  ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作；如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。
4.3.3    对查询结果筛选 HAVING
Having子句与group子句通常一起使用，这样可以在汇总相关数据后再进一步筛选汇总数据，在使用where子句或having子句都能查询出相同的结果时，where子句放在group by子句之前，而having子句放在group by子句之后。
例：查询工作类型的员工人数不等于1的记录。
SELECT job,COUNT(*),SUM(sal),AVG(sal) FROM emp GROUP BY job HAVING COUNT(*)!=1;
5    多表查询和子查询
5.1    查询多个表
5.1.1    基本连接
多个列之间用逗号分隔；列名为多表共有时使用“表名.列名”形式；from子句后的多个表用逗号分隔。
基本连接简单语法：
SELECT 列名 FROM 表1,表2 [WHERE 同等连接表达式]
例：
SELECT emp.empno,emp.ename,dept.dname FROM emp,dept
WHERE emp.deptno = dept.deptno;
5.1.2    指定表别名
例：
SELECT e.empno,e.ename,d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
5.1.3    连接多个表
例：
SELECT e.empno,e.ename,d.dname,s.grade FROM emp e, dept d,salgrade s 
WHERE e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisal;
5.2    内连接
内连接消除了与另一个表中的任何不匹配的行，条件不满足时可能会丢失信息。
SELECT 列名列表 FROM 表名1 [INNER] JOIN 表名2 ON 表名1.列名=表名2.列名
5.2.1    等值连接
连接条件中使用等号
SELECT e.empno,e.ename,d.dname FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;
5.2.2    不等值连接
连接条件中不使用等号
SELECT e.empno,e.ename,d.dname FROM emp e INNER JOIN dept d ON e.deptno = d.deptno AND d.deptno<30;
5.3    外连接
5.3.1    左外连接
左表某记录在右表没有匹配行，该记录属于右表的相应值为NULL。
例：查询emp表数据，根据emp表中的部门编号（deptno）查询dept表中对应部门名称。这里用emp表左外连接dept表。
SELECT e.*,d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;
5.3.2    右外连接
左表某记录在右表没有匹配行，该记录属于右表的相应值为null，反之，左表为NULL。
例：根据emp表中的部门编号dept使用右外连接dept表，查询出emp表所有列的数据，以及dept表的dname列。
SELECT e.*,d.dname FROM emp e RIGHT JOIN dept d ON e.depton=d.deptno;
5.3.3    加号表示
左关系属性=右关系属性（+）：将“+”放在等号的右边，那么此时表示的是左外连接。
左关系属性（+）=右关系属性：将“+”放在等号的左边，那么此时表示的是右外连接。
5.3.4    全外连接
系统开销大，慢。
例：SELECT e.*,d.dname FROM emp e FULL JOIN dept d ON e.depton=d.deptno;
5.3.5    自连接
自连接是表与自身进行的内连接或者外链接。
例：select e1.empno,e1.ename,e2.empno,e2.ename
from emp e1,emp e2
where e1.mgr=e2.empno order by e1.empno;
5.4    联合查询
在构成复合查询的各个查询中，各SELECT语句指定的列必须在数量上和数据类型上相匹配；
不允许在构成复合查询的各个查询中指定ORDER BY子句；
不允许在BLOB、LONG这样大数据类型对象上使用集合操作符。
5.4.1    UNION查询
UNION可以将第一个查询中的所有行与第二个查询中的所有行相加，并消除其中重复的行形成一个合集。NUION相当于一个OR运算，是并集运算。使用NUION或UNION ALL可以代替OR运算符，且性能比OR快得多。
例：
SELECT ename FROM emp WHERE ename LIKE 's%'
UNION
SELECT ename FROM emp WHERE ename LIKE 'a%'
5.4.2    UNION ALL查询
使用UNION ALL时会将重复的行输出，而使用UNION时会过滤掉重复行。
例：
SELECT ename FIRST FROM emp WHERE ename LIKE 's%' OR ename LIKE 'a%'
UNION ALL
SELECT ename FIRST FROM emp WHERE ename LIKE 'a%' OR ename LIKE 'm%';

5.4.3    INTERSECT查询
INTERSECT相当于AND运算，是交集运算。
SELECT ename FIRST FROM emp WHERE ename LIKE 's%' OR ename LIKE 'a%'
INTERSECT
SELECT ename FIRST FROM emp WHERE ename LIKE 'a%' OR ename LIKE 'm%';
5.4.4    MINUS查询
MINUS返回的是两个集合间的差集，会返回第一个查询结果中不包含第二个查询结果的结果，不返回第二个查询结果。第一个查询减去第二个查询。MINUS有去重作用。
SELECT ename FIRST FROM emp WHERE ename LIKE 's%' OR ename LIKE 'a%'
MINUS
SELECT ename FIRST FROM emp WHERE ename LIKE 'a%' OR ename LIKE 'm%';
5.5    子查询
5.5.1    使用IN关键字
IN后括号中的参数个数有限制，Oracle 9i 中个数不能超过256,Oracle 10g个数不能超过1000。
在IN之前用NOT关键字，查询结果与之相反。
语法：
SELECT select_list FROM table_name WHERE expression IN|NOT IN (id1,id2,id3...);
例：字段写法
SELECT * FROM table_name WHERE id IN (id1,id2,id3...);
子查询写法
SELECT empno,ename,sal FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE dname='SALES');
5.5.2    使用EXISTS关键字
Exist关键字只注重子查询是否返回行，如果子查询返回一行或多行，那么exists返回true，否则返回false。输出列表时可以使用“*”代替。
语法：
SELECT select_list FROM table_source WHERE EXISTS|NOT EXISTS (subquery)
例：
SELECT * FROM emp em WHERE EXISTS
(SELECT * FROM dept dep WHERE em.deptno=dep.deptno AND dneme='SALES');
5.5.3    in和exists关键字的区别
in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
例如：表A（小表），表B（大表）  
select * from A where cc in (select cc from B)  
效率低，用到了A表上cc列的索引；  
select * from A where exists(select cc from B where cc=A.cc)  
效率高，用到了B表上cc列的索引。 
not in 和not exists关键字的区别：
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。
5.5.4    使用比较运算符
常用的比较运算符有=，！=，<，>，<=，>=。
在使用比较运算符连接子查询时，必须保证子查询的返回结果只包含一个值，否则整个查询语句失败。
例：
SELECT empno,ename,sal FROM emp WHERE job='CLERK' 
and sal >=(SELECT AVG(sal) FROM emp WHERE job='CLERK');
6    更新数据
6.1    INSERT语句
使用insert语句时，插入的数据要与表中的字段相对应。
6.1.1    插入单行数据
DEFAULT设置默认值。在一个允许NULL且没有声明默认值的列中使用DEFAULT关键字时，NULL会被插入到该列中。如果在一个声明NOT NULL且没有默认值的列中指定NULL或DEFAULT，或者完全省略了该值，都会导致错误。
例：
INSERT INTO table_name (id,name)
VALUES ('001','张三');
简化：
INSERT INTO table_name  VALUES ('001','张三');
6.1.2    插入多行数据
使用INSERT SELECT语句将一个数据表中的数据插入到另一个新数据表中的时候注意：必须保证插入行数据的表已经存在。
对于插入新数据的表，各个需要插入数据的列的类型必须和源数据表中各列数据类型一致；
必须明确是否存在默认值，是否允许为NULL值。如果不允许为空，则必须在插入时，为这些列提供列值。
在把值从一列复制到另一列时，值所在列不必具有相同的数据类型，只要插入目标表的值符合该表的数据限制即可。
INSERT INTO SELECT语句
语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1
或者：Insert into Table2 select  *  from Table1
例：
INSERT INTO table1
SELECT * FROM table2
WHERE id<300;
6.2    UPDATE语句
UPDATE无法更新标识列。
更新违反约束或规则，比如违反了列NULL设置，会提示错误。
每次只能修改一个表中的数据。
可以同时把一列或多列，一个变量或多个变量放在一个表达式中。
6.2.1    更新单列
例：
UPDATE table_name SET rprice=305 WHERE rno='R101';
6.2.2    更新多列
例：
UPDATE table_name
SET rprice=298,rtype='标准1',rfloor=8,toward='东南'
WHERE rno='R101';
6.2.3    基于他表更新列
UPDATE语句能在多个表中进行操作，使用带FROM子句的UPDATE语句修改表。
例：
UPDATE atariff
SET atprice=atprice*0.9
WHERE atno IN (SELECT atno FROM consumelist WHERE gno='G002');
6.3    DELETE语句
删除表的数据时，如果某个字段有外键关系，需要先删除外键表的数据，再删除该表中的数据，否则报错。
(1)    DELETE语句不能删除单个列的值，只能删除整行数据。要删除单个列的值，可以采用UPDATE语句，将其更新为NULL。
(2)    使用DELETE语句仅能删除记录即表中的数据，不能删除表本身，删除表用DROP TABLE语句。
(3)    同INSERT和UPDATE语句一样，从一个表中删除记录将引起其他表的参照完整性问题，需要注意。
语法：DELETE table_or_view FROM table_name WHERE 条件;
table_or_view是要删除的表或视图，table_name要删除的表名
6.3.1    删除数据
例：DELETE FROM table_name WHERE rprice<300;
6.3.2    清空表
使用TRUNCATE清空表，注意：
TRUNCATE语句删除表中所有的数据；
释放表的存储空间；
TRUNCATE语句不能回滚。
语法：TRUNCATE TABLE table_name;
6.3.3    MERGE语句
MERGE语句可以在同一个步骤中更新（UPDATE）并插入（INSERT）。
(1)    MERGE语句有两次过滤条件，第一次是MERGE语句中的ON子句指定的，第二次是有UPDATE和INSERT子句中的WHERE指定的。
(2)    UPDATE或INSERT子句是可选的；
(3)    UPDATE和INSERT子句可以加WHERE子句；
(4)    在ON条件中使用常量过滤谓词来INSERT所有的行到目标表中，不需要连接源表和目标表；
(5)    UPDATE子句后面可以跟DELETE子句来删除一些不需要的行；
(6)    使用MERGE语句时，INSERT可以将源表符合条件的数据合并到另一个表中，而使用UPDATE语句可以将源表不符合条件的数据合并到另外一个表中。
语法：
MERGE INTO table1
USING table2
ON (条件)
WHEN MATCHED THEN UPDATE...
WHEN NOT MATCHED THEN INSERT...;   --MATCHED：合并
6.3.4    省略INSERT子句
使用MERGE语句之前要确保需要合并的表结构完全相同。
例：使用省略INSERT子句的MERGE语句实现以roominfo1为基准，对roominfo_copy表以rno列作为关联更新toward列，即只更新匹配的数据而不添加新数据。
MERGE INTO roominfo_copy r1
USING roominfo1 r2
ON (r1.rno=r2.rno)
WHEN MATCHED THEN 
    UPDATE SET r1.toward=r2.toward;
执行后MERGE语句会对roominfo_copy表的数据进行更新。
6.3.5    省略UPDATE子句
在MERGE语句中省略UPDATE子句，即MERGE语句中只有NOT MATCHED语句，表示直插入新数据而不更新旧数据。
例：将roominfo1表的数据添加到roominfo_copy表中，添加条件是rno列不相同。
MERGE INTO roominfo_copy r1
USING roominfo1 r2
ON (r1.rno=r2.rno)
WHEN NOT MATCHED THEN     
    INSERT VALUES(r2.rno,r2.rtype,r2.rprice,r2.rfloor,r2.toward);
MERGE语句会对roominfo_copy表执行插入操作。
6.3.6    带条件的UPDATE和INSERT子句
例：将roominfo1表中朝向位于“正西”的房间信息插入到roominfo_copy表；
    将roominfo1表中朝向位于“正东”的房间价格更新到roominfo_copy表。
MERGE INTO roominfo_copy r1
USING roominfo1 r2
ON (r1.rno=r2.rno)
WHEN MATCHED THEN
    UPDATE SET r1.rprice=r2.rprice
    WHERE r2.toward='正东'
WHEN NOT MATCHED THEN     
    INSERT VALUES(r2.rno,r2.rtype,r2.rprice,r2.rfloor,r2.toward);
    WHERE r2.toward='正西';
6.3.7    使用常量表达式
不设置条件，一次性将源表中的所有数据添加到目标表，可以在MERGE语句的ON条件中使用常量表达式。如ON（1=0）。
ON(1=0)返回false，等同于users2与users1没有匹配的数据，就把users1的新信息插入到users2。尽管users2中已经存在的数据，也会插入进去。常量可以是任何值，例如2=5,1=3等。
例：将users1表的数据添加到users2表中。
MERGE INTO users2 m1
USING users1 m
ON(1=0)
WHEN NOT MATCHED THEN
    INSERT VALUES(m.id,m.name);
6.3.8    使用DELETE语句
例：使用user1表作为源表来更新user2表，同时删除user2表中id大于2的数。
MERGE INTO user2 m1
USING user1 m
ON(m1.id=m.id)
WHEN MATCHED THEN
UPDATE SET m1.name=m.name
DELETE WHERE m1.id>2;
注意：DELETE子句必须有一个WHERE条件来删除匹配WHERE条件行，而且必须同时满足ON后的条件和DELETE WHERE后的条件才有效，匹配DELETE WHERE条件但不匹配ON条件的行不会被删除。
7    PL/SQL编程基础
7.1    PL/SQL简介
PL/SQL是oracle数据库对SQL语句的扩展。
缺点，在网络环境下会占用大量的网络带宽。
PL/SQL支持两种类型的程序：一种是匿名块程序；一种是命令块程序。
注意：最简单的PL/SQL块可以不做任何事情，但是在PL/SQL程序中要求，执行部分必须要有一条语句，哪怕写一个NULL也行，如：DECLARE NULL; END; 。
7.1.1    PL/SQL块结构
DECLARE
    --变量，游标，用户自定义的特殊类型等
BEGIN
    --SQL语句或PL/SQL语句
EXCEPTION
    --错误发生时的处理动作
END;
声明部分：又关键字DECLARE开始，如果不需要声明变量或常量，那么可以忽略这一部分。
执行部分：由关键字BEGIN开始，END结束。执行部分是必须的，也是主要的。
异常处理部分：异常处理部分可以忽略。下面介绍。
例：
DECLARE
    v_num NUMBER;
BEGIN
    v_num:=&num;
  DBMS_OUTPUT.put_line('用户输入的值是：'||v_num);
END;
上述代码，首先在DECLARE部分定义一个NUMBER类型的v_num变量，然后在BEGIN部分中接收输入的值（&表示要接收从控制台输入的变量）并赋予v_num变量，之后将输入的值进行输出。
7.1.2    PL/SQL程序注释
1.    单行注释
--注释代码
2.    多行注释
  /*
注释代码
*/
7.2    变量
所有的变量和常量都必须在程序块DECLARE部分，每一个变量，都必须指定其名称和数据类型，以便在可执行部分为其赋值。
技巧： &符号：在Oracle中,&符号常用来指出一个变量。例如，&fox是一个变量，稍微有点不同的一种&& fox。每当&fox出现在Oracle脚本中时，都会要求您为它提供一个值。而使用&&fox，您只需要在& &fox第一次出现时为它提供变量值。
在SQL语句中如果希望重新使用某个变量并且不希望重新提示输入值，那么可以使用“&&”符号来定义临时变量。使用&&符号替代&符号，可以避免为同一个变量提供两个不同的值，而且使得系统为同一个变量值只提示一次信息。
7.2.1    标识符
标识符是一个PL/SQL对象的名称，变量，常量，异常，游标，程序的名称（如存储过程，函数，包，对象类型以及触发器等），以及标签都是标识符。
标识符的名称不能超过30个字符，最多只能30个字符。
标识符的名称必须以字母开头。
标识符可以由字母，数字，_，$和#等符号组成。
标识符中不能包含减号和空格。
标识符的名称不能是Oracle的关键字（保留字）。
7.2.2    变量的声明
所有变量必须在它声明之后才可以使用，变量的名称规则需遵循标识符的命名规则，另外还需要注意以下两点：
不同块中的两个变量可以同名；变量的名称不能与块中表的列同名。
在程序中定义变量，常量和参数时，必须为它们指定PL/SQL数据类型。可以使用标量类型，复合类型，参数类型和LOB类型等4种类型。单独值使用标量变量，多个值使用复合型变量。
例：在程序中分别声明v_name和v_password两个变量，其中v_password变量的默认值为“123456”。
DECLARE
    v_username VARCHAR2(20);
    v_password VARCHAR2(20):='123456';
BEGIN
    NULL;
END;
7.2.3    变量赋值
声明变量后为变量赋值，使用赋值操作符“:=”和DEFAULT关键字赋值。
例：
DECLARE
    v_outtext VARCHAR2(50) := 'unknown';
    v_outnum NUMBER DEFAULT 5;
BEGIN
    v_outtext := 'lucy';
    dbms_output.put_line('v_outtext变量的值:'||v_outtext);
    dbms_output.put_line('v_outnum变量的值：'||v_outnum);
END;
输出结果：
v_outtext变量的值：lucy
v_outnum变量的值：5
除了赋值操作符和DEFAULT外，还可以使用SELECT INTO语句或FETCH INTO语句。使用SELECT INTO语句时，查询结果只能是一条记录，不能是零行或是多行。
例：
DECLARE v_username VARCHAR2(20) DEFAULT 'jerry';
BEGIN
 SELECT username INTO v_username FROM SYS.all_users WHERE user_id=102;
    dbms_output.put_line(v_username);
END;
使用SELECT INTO语句对变量v_username赋值。
7.2.4    使用%TYPE
PL/SQL变量可以用来存储数据库表中的数据，在这种情况下，变量应该拥有与表列相同的类型。
%TYPE的特性：
开发人员不需要知道所引用的数据库列的数据类型。
所引用的数据库列的数据类型可以实时改变，容易保存一致，也不用修改PL/SQL程序。
例：用“%TYPE”操作符，v_name变量将同student表的name列的类型相同。
DECLARE
    v_name student.name%TYPE;
例：在 DECLARE 声明 部 分 用 %TYPE 类 型 定义 与 SYS.all_users 表 相 匹配 的 字段 ， 然 后声明 接收 数据 的 变量 。BEGIN在 END 部 分 查询 结果 并 显示 结果代。码如下 ：
DECLARE
    -- 用 sTYPE 类 型 定义 与 表 相 配 的 字段
    TYPE T_Record IS RECORD(  --定义记录数据类型
        T_name SYS.all_users.username%TYPE,
        T_id SYS.all_users.user_id%TYPE,
        T_created SYS.all_users.created%TYPE ); 
    v_test T_Record;    --声明接收数据的变量
BEGIN
    SELECT username, user_id, created INTO v_test FROM SYS.all_users WHERE user_id=102;
    DBMS_OUTPUT.put_line(TO_CHAR(v_test.t_name)||' '||v_test.t_id||' '|| TO_CHAR(v_test.t_created));
END;
执行上述代码，输出结果如下 ：
C##SCOTT    102    05-8    月 -14
RECORD复合数据类型定义格式：
TYPE recordName IS RECORD(
     字段名称 字段类型，
     字段名称 字段类型
 );
7.2.5    使用%ROWTYPE
%ROWTYPE操作符返回一个记录类型。特点：
开发人员不必知道所引用的数据库中列的个数和数据类型；
所引用的数据库中列的个数和数据类型可以实时改变，容易保持一致，也不用修改PL/SQL程序；
在SELECT语句中使用%ROWTYPE可以有效地检索表中的行。
例：
DECLARE
    v_userid SYS.all_users.user_id%TYPE :=&id;
    res SYS.all_users%ROWTYPE;
BEGIN
    SELECT * INTO res FROM SYS.all_users WHERE user_id=v_userid;
END;
7.3    常量
常量只可以在声明部分DECLARE处赋值，声明时包含CONSTANT关键字，一般以“c_”作为前缀。
例：
DECLARE
    c_pi CONSTANT UNMBER :=3.14;--圆周率
v_radiu NUMBER DEFAULT 5;--半径
v_area NUMBER;--面积
BEGIN
    v_area:=c_pi*v_radiu*v_radiu;--计算面积
    DBMS_OUTPUT.put_line(v_area);--输出面积
END;
7.4    字符集
单字节编码
多字节编码
Unicode编码
7.4.1    查看字符集
影响Oracle数据库字符集最重要的参数是NLS_LANG参数。该参数的格式如下：
NLS_LANG=language_territory.charset;
Language（语言）territory（地域）charset（字符集）
7.4.1.1    查看数据库当前字符集参数设置
SELECT * FROM v$nls_parameters;
或者
SELECT * FROM nls_database_parameters;
或者
SELECT USERENV ('language') FROM dual;
7.4.1.2    查看数据库可用字符集参数设置
SELECT * FROM v$nls_valid_values;
7.4.1.3    客户端设置NLS_LANG
SET NLS_LNAG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK;
7.4.1.4    Unicode字符集设置
SET NLS_LANG=american_america.AL32UTF8;
例：注册表修改NLS_LANG参数值。
win+R键，输入regedit打开注册表，选择HKEY_LOCAL_MACHINE|SOFTWARE|ORACLE选项，双击NLS_LANG选项打开编辑字符串对话框，更改后点击确定。
7.5    运算符
7.5.1    连接运算符：||
例：DBMS_OUTPUT.PUT_LINE('公司地址：'||v_companyname||'，网址：'||v_url);
7.5.2    比较运算符
基本关系运算符：< ,> ,= ,>= ,<= ,!= ,<>
判断NULL：IS NULL,IS NOT NULL
介于列表之中：IN, NOT IN
指定范围：BETWEEN AND,NOT BETWEEN AND
模糊匹配：LIKE,NOT LIKE
7.5.3    逻辑运算符：AND,OR,NOT
在三种逻辑运算符中，NOT(求反操作)运算符的优先级别最高，然后依次是AND和OR。
7.5.4    算术运算符：+，-，*，/
求余运算只能借助MOD()函数。
7.6    控制语句
7.6.1    条件语句
7.6.1.1    IF语句
if语句：if-then-end if
If-then-else-end if
If-then-elseif-then-else-end if
If-then-else-end if例：
DECLARE
    v_totalcount NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_totalcount FROM table_name;
    IF v_totalcount>10 THEN
        DBMS_OUTPUT.PUT_LINE('查询结果大于10条记录');
    ELSE
        DBMS_OUTPUT.PUT_LINE('查询结果小于等于10条记录');
    END IF;
END;
If-then-elsif-then-else-end if例：
DECLARE
    v_totalcount NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_totalcount FROM table_name;
    IF v_totalcount>10 THEN
        DBMS_OUTPUT.PUT_LINE('查询结果大于10条记录');
    ELSIF v_totalcount<10 THEN   --写成elseif编译报错
        DBMS_OUTPUT.PUT_LINE('查询结果小于10条记录');
    ELSE
        DBMS_OUTPUT.PUT_LINE('查询结果等于10条记录');
    END IF;
END;
7.6.1.2    CASE语句
语法：
CASE[变量]
WHEN [|] THEN statementsn;
... ...
ELSE
statementsn+1;
END CASE;
例:
DECLARE
    v_num NUMBER :=&number;
BEGIN
    CASE
        WHEN v_num<=10 THEN
            DBMS_OUTPUT.PUT_LINE('输入的值小于等于10');
        WHEN v_num>10 AND v_num<=20 THEN
            DBMS_OUTPUT.PUT_LINE('输入的值在11和20中间');
        WHEN v_num>20 AND v_num<=50 THEN
            DBMS_OUTPUT.PUT_LINE('输入的值在21和50中间');
    ELSE
        DBMS_OUTPUT.PUT_LINE('输入的值大于50');
    END CASE;
END;
7.6.2    循环语句
7.6.2.1    LOOP循环
语法：EXIT是退出包含它的最内层循环体。现执行后判断。
LOOP
    statements;
    ......
    EXIT[WHEN condition];
END LOOP;
例：10以内的正数相加的结果是
DECLARE
    v_count NUMBER :=1;                 --定义一个变量，用于循环
    v_sum NUMBER DEFAULT 0;             --保存变量相加的总和，初始值赋值为0
BEGIN
    LOOP
        v_sum := v_sum + v_count;       --计算相加
        v_count := v_count + 1;         --变量值加1
        EXIT WHEN v_count>10;           --变量大于10退出循环
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('10以内的正数相加的结果是：'||v_sum);  --结果是55
END;
7.6.2.2    WHILE-LOOP循环
语法：先判断，满足后执行循环。
WHILE condition LOOP
    statements;
    ......
END LOOP;
例：10以内的正数相加的结果
DECLARE
    v_count NUMBER := 1;
    v_sum NUMBER DEFAULT 0;
BEGIN
    WHILE(v_count<=10) LOOP
        v_sum := v_sum + v_count;
        v_count := v_count + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('10以内的正数相加的结果是：'||v_sum);
END;
7.6.2.3    FOR-LOOP循环
语法：for-loop需要给出上限和下限。默认情况下，FOR-LOOP循环是按照升序的方式进行增长的，使用REVERSE关键字降序循环。
FOR counter IN [REVERSE]  
    lower_bound..upper_bound LOOP
    statements;
END LOOP;
例：
DECLARE
    v_num NUMBER :=1;
BEGIN
    FOR v_num IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('v_num='||v_num);
    END LOOP;
END;
输出结果是：v_num=1 v_num=2 ...v_num=5
例：降序循环
DECLARE
    v_num NUMBER :=1;
BEGIN
    FOR v_num IN REVERSE 1 .. 5 LOOP
        DBMS_OUTPUT.PUT_LINE('v_num='||v_num);
    END LOOP;
END;
7.6.3    跳转语句
三种跳转语句：EXIT、CONTINUE、和GOTO。
7.6.3.1    EXIT语句：
使用EXIT会强制性的结束循环操作，继续执行循环语句之后的操作。
例：
DECLARE
    v_num NUMBER :=1;
    v_sum NUMBER DEFAULT 0;
BEGIN
    FOR v_num IN 1 .. 10 LOOP
        IF v_num = 5 THEN
            EXIT;
         END IF;
        v_sum := v_sum + v_num;
        DBMS_OUTPUT.PUT_LINE('10以内的所有正整数是：'||v_num);  --v_num=1...v_num=4
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('10以内的所有正整数的和（除5以外）：'||v_num);  --v_num=10
END;
7.6.3.2    CONTINUE语句
continue不会退出整个循环，只是跳出当前循环。
例：
DECLARE
    v_num NUMBER :=1;
    v_sum NUMBER DEFAULT 0;
BEGIN
    FOR v_num IN 1..10 LOOP
        IF v_num=5 THEN
            CONTINUE;  --当v_num=5时跳出循环，不执行下面的v_sum,继续执行下一次的循环
        END IF;
        v_sum := v_sum + v_num;
        DBMS_OUTPUT.PUT_LINE('10以内的所有正数是：'||v_num);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('10以内的所有正数的和（除5以外）：'||v_num);
END;
7.6.3.3    GOTO语句（不建议使用）
GOTO表示无条件转移语句，直接转移到指定标号处。GOTO语句不能转入IF语句，循环体和子块，但是可以从IF语句，循环体和子块中转出。使用GOTO语句可以控制执行顺序，语法如下：
GOTO label
label是指向语句标记，标记必须符合标识符的规则。标记的定义形式如下：
<<label>>
语句块;
例：
DECLARE
    v_num NUMBER :=1;
    v_sum NUMBER DEFAULT 0;
BEGIN
    FOR v_num IN 1..10 LOOP
        IF v_num=5 THEN
            GOTO endText;
        END IF;
        v_sum := v_sum + v_num;
        DBMS_OUTPUT.PUT_LINE('10以内的所有正数是：'||v_num);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('10以内的所有正数的和（除5以外）：'||v_num);
    <<endText>>
    DBMS_OUTPUT.PUT_LINE('使用goto语句进行跳转');
END;
7.6.4    语句嵌套
嵌套要注意变量，定义在最外部程序块中的变量可以在所有子块中使用，如果在子块中定义了与外部程序块变量相同的变量名，在执行子块时将使用子块中定义的变量。子块中定义的变量不能被父块引用。
例：
DECLARE
    v_id NUMBER DEFAULT 0;
BEGIN
    SELECT user_id INTO v_id FORM table_name WHERE username='system';
    IF v_id!=0 THEN
        IF v_id BETWEEN 1 AND 50 THEN
            DBMS_OUTPUT.PUT_LINE('用户id值是：'||v_id);
        ELSE
            DBMS_OUTPUT.PUT_LINE('查询到的id值大于50，具体值为：'||v_id);
        END IF;
    ELSE
        DBMS_OUTPUT.PUT_LINE('很抱歉，v_id变量的值为0');
    END IF;
END;
7.7    异常处理
oracle错误：报告为ORA-xxxx形式的oracle错误号。
oracle提供异常情况（EXCEPTION）和异常处理（EXCEPTION HANDLER）来实现错误处理。
7.7.1    异常语法
Oracle三种类型错误：预定义异常，非预定义异常和用户自定义异常。
自定义异常语法：异常处理部分一般放在PL/SQL程序体的后半部分。
BEGIN
EXCEPTION  --处理异常关键字
    WHEN first_exception THEN <code to handle first exception>  --first_exception预定异常或自定义异常
    WHEN second_exception THEN <code to handle second exception> --second_exception预定异常或自定义异常
    WHEN OTHERS THEN <code to handle others exception> --中间可以写多个WHEN，但是WHEN OTHERS必须放最后面。
END;
7.7.2    预定义异常
OTHERS捕获任意异常                                                            
异常代码     异常名称                 说明
ORA-00001 DUP_VAL_ON_INDEX     在数据库中增加重复数据(主键重复)时触发
ORA-00051TIMEOUT_ON_RESOURCE 当访问锁定资源时间过长时触发ORA-01001INVALID_CURSOR     在游标操作中指针出现异常(未打开或关闭)时触发

ORA-01722INVALID-NUMBER      试图将非数字赋值给数字变量时触发
ORA-01017 LOGIN_DENIED        输入了错误的用户名或密码时触发
ORA-01403 NO_DATA_FOUND 当在 SELECT子句中使用INTO命令中返回结果为nul时触发

ORA-01012 NOT_LOGGED_ON  程序发送数据库命令,但未与 Oracle连接时触发
ORA-01410 SYS_INVALID_ROWID  当字符串转换为无效的 ROWID时触发
ORA-01422 TOO_MANY_ROWS当在 SELECT子句使用INTO命令中返回结果为多行数据时触发

ORA-01476 ZERO_DIVID  当使用除法计算被除数为0时触发
RA-06500 STORAGE_ERROR当  SGA消耗完内存或被破坏时触发
ORA-0651 PROGRAM_ERROR当 Oracle未正常捕获异常时由数据库触发ORA-06502 VALUE_ERROR 试图将一个变量的内容赋值给另一种不能容纳该变量内容时触发

ORA-06504 ROWTYPE_MISMATCH  当游标结构不适合于 PL/SQL游标变量时触发ORA-06511  CURSOR-ALREADY-OPEN  试图打开一个已处于打开状态的游标
ORA-06530  ACCESS_INTO_NULI  试图访问未初始化的对象属性时触发
ORA-06531  COLLECTION_IS_NULL  试图操作未初始化的嵌套表或可变数据时触发

ORA-06532 SUBSCRIPT_OUTSIDE_LIMIT 当访问嵌套表或可变数组时使用非法索引值时触发
ORA-06533 SUBSCRIPT_BEYOND_COUNT 当程序引用一个嵌套表或可变数组元素,但使用的下
标索引超过嵌套表或变长数组元素总个数时触发ORA-06592  CASE_NOT_FOUND       case语句格式有误,没有分支语句时触发
ORA-30625  SELF_IS_NULL    当程序调用一个未实例对象方法时触发
7.7.3    非预定义异常
语法：
(1)    在程序块声明部分定义异常情况
<异常情况> EXCEPTION;
(2)    使用EXCEPTION_INIT语句将已经定义好的异常情况与标准的oracle异常联系起来
PRAGMA EXCEPTION_INIT(<异常情况>,<错误代码>);
例：向student表中添加一条记录，该记录的主键是有用户输入的值决定的，如果输入的值已经存在，则输出异常。
DECLARE
    v_stuno student.stuno%TYPE := &stuno;
    stuno_remaining EXCEPTION;
    PRAGMA EXCEPTION_INIT(stuno_remaining,-00001);
BEGIN
    INSERT INTO student values(v_stuno,'lucy');
EXCEPTION
    WHEN stuno_remaining THEN
        DBMS_OUTPUT.PUT_LINE('主键是唯一的，不能重复！');
    WHEN OTHERS THEN
        --SQLCode：数据库操作的返回码，其中0--成功；-1--失败；100--没有检索到数据。
        DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
7.7.4    自定义异常
用户自定义的异常错误通过显式使用RAISE语句来触发。RAISE手动抛出异常
语法：
(1)    在程序块的声明部分定义异常情况：
<异常情况> EXCEPTION;
(2)    在程序块执行部分执行以下代码：
RAISE <异常情况>
例：使用RAISE手动抛出用户异常
DECLARE
    v_result NUMBER; 
    v_exp EXCEPTION;
BEGIN
    v_result := &number;
    IF v_result BETWEEN 0 AND 100 THEN 
        RAISE v_exp;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ('您输入的数字有点小，请输入大一点的数字吧'）; 
    DBMS_OUTPUT.put_line('SQLCODE='||SQLCODE); 
    DBMS_OUTPUT.put_line('SQLERRM='||SQLERRM);
END;
在PL/SQL程序中，可以将用户定义的异常添加到异常列表（错误堆栈）中，这时 需要使用到RAISE_APPLICATION_ERROR异常。语法如下：
RAISE_APPLICATION_ERROR (error_number,error_ message,[keep_errors]);
其中，error_umber表示错误号，只接收-20000〜-29999范围的错误号，和声明的错误号一致。error_message用于定义在使用SQLERRM输出时的错误提示信息。 keep_errors表示是否添加到异常列表中，取值为FALSE (默认值）或TRUE。
例：下面的代码演示RAISE_APPLICATION_ERROR异常的使用。
DECLARE
    v_result NUMBER; 
    v_exp EXCEPTION;
    PRAGMA EXCEPTION_INIT(v_exp, -20789);
BEGIN
    v_result := &number;
    IF v_result BETWEEN 0 AND 100 THEN
        RAISE_APPLICATION_ERROR(-20789,'输入的数字不能小于 l00');
    END IF;
EXCEPTION
    WHEN v_exp THEN
        DBMS_OUTPUT.put_line('您输入的数字有点小，请请输入大一点的数字吧'); 
        DBMS_OUTPUT.put_line('SQLCODE='||SQLCODE);
        DBMS_OUTPUT.put_line('SQLERRM='||SQLERRM);
END;
7.8    事务处理
事物特性：原子性，一致性，隔离性，持久性。
7.8.1    事务控制
Oracle不能显示的开始事物处理，使用COMMIT或ROLL BACK语句会显式终止事务处理。
1.    设置事务属性
使用SET TRANSACTION设置事务的隔离级别
SET TRANSACTION ISOLACTION LEVEL SERIALIZABLE
2.    设置约束延期性
使用SET CONSTRAINT 语句设置事务事务延迟约束
SET CONSTRAINT ALL | <constraint_name> DEFERRED | IMMEDIATE
上述语法可以选择要延迟的约束名，用ALL关键字延期所有约束。DEFERRED表示延期，IMMEDIATE表示应用。
使用延期约束，需在创建时进行说明：
ALTER TABLE t1 ADD CONSTRAINT <constralnt_name> DEFERRABLE INITIALLY IMMEDIATE
3.    存储点
设置存储点可以在发生错误时回滚到指定的存储点。
创建语法：SAVEPOINT <savepint_name>;
使用语法：ROLLBACK TO [SAVEPOINT] <savepoint_name>
4.    结束事务
使用commit提交事务，数据被永久保存。使用commit提交事务时会生成唯一的系统变化号（SCN）保存到事务表。
使用ROLLBACK回滚。
7.8.2    锁
数据锁，字典锁，内部锁与闩，分布式锁，并行高速缓存管理锁。
5种数据锁：
共享锁：一个有效的共享锁明确的用SELECT...FOR UPDATE形式锁定行。
语法：LOCK TABLE tablename IN SHARE MODE;--锁定整个表
一个共享锁有一个事务控制，防止其他事务更新该表或执行下面的语句：
LOCK TABLE tablename IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE tablename IN ROW EXCLUSIVE MODE;
排它锁：限制最多，在一个表中只能有一个事务对该表实行排它锁，排它锁仅允许其他的事务查询该表。加排它锁的事务不能在加其他类型的锁。
语法：LOCK TABLE tablename IN  EXCLUSIVE MODE;
行级锁：行级锁在锁类型中是限制最少的，也足在表的并发程度中使用程度最高的。一个行级锁需要该事务在被锁定行的表上用UPDATE的形式加锁。当有下面语句被执行的时候行级锁自动加在操作的表上：
SELECT...FROM TableName...FOR UPDATE OF...;
LOCK TABLE TableUame IN ROW SHARE MODE;
行级共亨锁由一个事务控制，允许其他事务查询、插入、更新、删除或同时在同一张表上锁定行。因此其他时务可以同时在同一张表上得到行级锁、共享行级排他锁、行级排它锁、棑他锁。但是需要注意的是，拥有行级锁的事务不允许其他事务执行排他锁.
行级排他锁：
行级排他锁比行级锁稍微多一些限制，它通常需要事务拥有的锁在表上被更新一行或多行。当有下面语句执行的时候行级排他锁被加在操作的表上:
INSERT INTO TableName...;
UPDATE TableName....;
DELETE FROM TableName ...;
LOCK TABLE TableName IN ROW EXCLUSIVE MODE;
共享行级排他锁：共亨行级排他锁比共享锁有更多限制。它仅允许一个事务在某一时刻得到行级排他锁。拥有行级排他锁事务允许其他事务在被锁定的表上执行查询或使用SELECT...FROM TableName FOR UPDATE来准确在锁定行而不能更新行。定义共享行级排他锁的语法为：
LOCK TABLE TableName IN SHARE ROM EXCLUSIVE MODE;
禁止的操作：拥有行级排他锁的事务不允许其他事务有除共享锁外的他形式的锁加在同一张表上或更新该表。即下面的语句是不被允许的。
LOCK TABLE TableName IN SHARE MODE;
LOCK TABLE TableName IN SHARE ROW EXCLUSIVE MODE; 
LOCK TABLE TableName IN ROW EXCLUSIVE MODE;
LOCK TABLE TableName IN EXCLUSIVE MODE;
7.8.3    锁查询语句
7.8.3.1    査询数琚库中的锁
查询数据库中的锁时需要利用v$lock视图，该视图列出系统中的所有的锁。代码如下：
SELECT * FROM v$lock;
可以在SELECT语句之后跟WHERE子句，根据指定的条件进行查询。如下代码查询block列的值为1时的全部记录：
SELECT * FROM v$lock WHERE block=1;
block表示是否阻塞其他会话锁申请。取值为1时表示阻塞，取值为0时表示不阻塞。
7.8.3.2    查询被锁的对象
查询被锁的对象时需要利用v$locked_object视图，该视图只包含DML的锁信息，包括回滚段和会话信息。代码如下：
SELECT * FROM v$locked_object;
7.8.3.3    查询阻塞
查询阻塞包括查询被阻塞的会话和查询阻塞级別的会话锁。使用以下代码查询被阻的会话：
SELECT * FROM v$lock WHERE lmode=0 and type in ('TM','TX');
使用以下代码查询阻塞级別的会话锁：
SELECT * FROM v$lock WHERE lmode>0 and type in ('TM','TX');
7.8.3.4    查询数据库正在等待锁的线程
v$scssion用于查询会话的信息和锁的信息。可以使用该视图查询数据库正在等待锁的线程。
SELECT * FROM v$session WHERE lockwait IS NOT NULL;
7.8.3.5    査询会话之间锁等待的关系
使用以下代码查询会话之间锁等待的关系：
SELECT a.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime 
FROM v$lock a,v$lock b 
WHERE a.id1=b.id1 AND a.id2=b.id2 AND a.block=1 AND b.block=0;
7.8.3.6    查询锁等待事件
V$session_wait视图查询等待的会话信息，如下查询等待事件：
SELECT * FROM v$session_wait WHERE event='enqueue';

图片显示不出来，懒得插入了。文字太多发布不了，只好删减些，可以去我的资源处下载完整文档。
文档下载链接：https://download.csdn.net/download/chenmeixxl/11268986