Oracle PL/SQL语言初级教程

 

Oracle PL/SQL语言初级教程

 

 

 

 

之表和视图

Oracle数据库数据对象中最基本的是表和视图，其他还有约束、序列、函数、存储过程、包、触发器等。对数据库的操作可以基本归结为对数据对象的操作,理解和掌握Oracle数据库对象是学习Oracle的捷径。

　　表和视图

　　Oracle中表是数据存储的基本结构。ORACLE8引入了分区表和对象表，ORACLE8i引入了临时表，使表的功能更强大。视图是一个或多个表中数据的逻辑表达式。本文我们将讨论怎样创建和管理简单的表和视图。

　　管理表

　　表可以看作有行和列的电子数据表，表是关系数据库中一种拥有数据的结构。用CREATE TABLE语句建立表，在建立表的同时，必须定义表名，列，以及列的数据类型和大小。例如：

 

CREATE TABLE products
　　( PROD_ID NUMBER(4),
　　　PROD_NAME VAECHAR2(20),
　　　STOCK_QTY NUMBER(5,3)
　　);

　　这样我们就建立了一个名为products的表， 关键词CREATE TABLE后紧跟的表名，然后定义了三列，同时规定了列的数据类型和大小。

　　在创建表的同时你可以规定表的完整性约束，也可以规定列的完整性约束，在列上普通的约束是NOT NULL,关于约束的讨论我们在以后进行。

　　在建立或更改表时，可以给表一个缺省值。缺省值是在增加行时，增加的数据行中某一项值为null时，oracle即认为该值为缺省值。

　　下列数据字典视图提供表和表的列的信息：

　　　. DBA_TABLES
　　　. DBA_ALL_TABLES
　　　. USER_TABLES
　　　. USER_ALL_TABLES
　　　. ALL_TABLES
　　　. ALL_ALL_TABLES
　　　. DBA_TAB_COLUMNS
　　　. USER_TAB_COLUMNS
　　　. ALL_TAB_COLUMNS

　　表的命名规则

　　表名标识一个表，所以应尽可能在表名中描述表，oracle中表名或列名最长可以达30个字符串。表名应该以字母开始，可以在表名中包含数字、下划线、#、$等。

　　从其它表中建立表

　　可以使用查询从基于一个或多个表中建立表，表的列的数据类型和大小有查询结果决定。建立这种形式的表的查询可以选择其他表中所有的列或者只选择部分列。在CREATE TABLE语句中使用关键字AS，例如:

SQL>CREATE TABLE emp AS SELECT * FROM employee

TABLE CREATED

SQL> CREATE TABLE Y AS SELECT * FROM X WHERE no=2

　　需要注意的是如果查询涉及LONG数据类型，那么CREATE TABLE....AS SELECT....将不会工作。

　　更改表定义

　　在建立表后，有时候我们可能需要修改表，比如更改列的定义，更改缺省值，增加新列，删除列等等。ORACLE使用ALTER TABLE语句来更改表的定义

　　1、增加列

　　语法：

ALTER TABLE [schema.] table_name ADD column_definition

　　例：

ALTER TABLE orders ADD order_date DATE;

TABLE ALTER

　　对于已经存在的数据行，新列的值将是NULL.

　　2、更改列

　　语法：

ALTER TABLE [schema.] table_name MODIFY column_name new_attributes;

　　例：

ALTER TABLE orders MODITY (quantity number(10,3),status varchar2(15));

　　这个例子中我们修改了表orders，将STATUS列的长度增加到15,将QUANTITY列减小到10,3;

　　修改列的规则如下：

　　　. 可以增加字符串数据类型的列的长度，数字数据类型列的精度。

　　　. 减少列的长度时，该列应该不包含任何值，所有数据行都为NULL.

　　　. 改变数据类型时，该列的值必须是NULL.

　　　. 对于十进制数字，可以增加或减少但不能降低他的精度。

　　3、删除数据列

　　优化ORACLE数据库，唯一的方法是删除列，重新建立数据库。在ORACLE8i中有很多方法删除列，你可以删除未用数据列或者可以标示该列为未用数据列然后删除。

　　删除数据列的语法是：

ALTER TABLE [schema.] table_name DROP {COLUM column_names | (column_names)}[CASCADE CONSTRAINS]

　　要注意的是在删除列时关于该列的索引和完整性约束也同时删除。注意关键字CASCADE CONSTRAINS，如果删除的列是多列约束的一部分，那么这个约束条件相对于其他列也同时删除。

　　如果用户担心在大型数据库中删除列要花太多时间，可以先将他们标记为未用数据列，标记未用数据列的语法如下：

ALTER TABLE [schema.] table_name SET UNUSED {COLUM column_names | (column_names)}[CASCADE CONSTRAINS]

　　这个语句将一个或多个数据列标记为未用数据列，但并不删除数据列中的数据，也不释放占用的磁盘空间。但是，未用数据列在视图和数据字典中并不显示，并且该数据列的名称将被删除，新的数据列可以使用这个名称。基于该数据列的索引、约束，统计等都将被删除。

　　删除未用数据列的语句是：

ALTER TABLE [schema.] table_name DROP {UNUSED COLUM | COLUMN CONTINUE}
删除表和更改表名

　　删除表非常简单，但它是一个不可逆转的行为。

　　语法：

DROP TABLE [schema.] table_name [CASCADE CONSTRAINTS]
　　
　　删除表后，表上的索引、触发器、权限、完整性约束也同时删除。ORACLE不能删除视图，或其他程序单元，但oracle将标示他们无效。如果删除的表涉及引用主键或唯一关键字的完整性约束时，那么DROP TABLE语句就必须包含CASCADE CONSTRAINTS子串。

　　更改表名

　　RENAME命令用于给表和其他数据库对象改名。ORACLE系统自动将基于旧表的完整性约束、索引、权限转移到新表中。ORACLE同时使所有基于旧表的数据库对象，比如视图、程序、函数等，为不合法。

　　语法：

RENAME old_name TO new_name;

　　例：

SQL> RENAME orders TO purchase_orders;

TABLE RENAMED
 

　　截短表

　　TRUNCATE命令与DROP命令相似， 但他不是删除整个数据表，所以索引、完整性约束、触发器、权限等都不会被删除。缺省情况下将释放部分表和视图空间，如果用户不希望释放表空间，TRUNCATE语句中要包含REUSE STORAGE子串。TRUNCATE命令语法如下：

TRUNCATE {TABLE|CLUSTER} [schema.] name {DROP|REUSE STORAGE}

　　例：

SQL> TRUNCATE TABLE t1;

TABLE truncate.

　　管理视图

　　视图是一个或多个表中的数据的简化描述，用户可以将视图看成一个存储查询（stored query)或一个虚拟表(virtual table).查询仅仅存储在oracle数据字典中，实际的数据没有存放在任何其它地方，所以建立视图不用消耗其他的空间。视图也可以隐藏复杂查询，比如多表查询，但用户只能看见视图。视图可以有与他所基于表的列名不同的列名。用户可以建立限制其他用户访问的视图。

　　建立视图

　　CREATE VIEW命令创建视图，定义视图的查询可以建立在一个或多个表，或其他视图上。查询不能有FOR UPDATE子串，在早期的ORACLE8i版本中不支持ORDER BY子串，现在的版本中CREATE VIEW可以拥有ORDER BY子串。

　　例：

SQL> CREATE VIEW TOP_EMP AS
SELECT empno EMPLOYEE_ID,ename EMPLOYEE_NAME,salary
FROM emp
WHERE salary >2000

　　用户可以在创建视图的同时更改列名，方法是在视图名后立即加上要命名的列名。重新定义视图需要包含OR REPLACE子串。

SQL> CREATE VIEW TOP_EMP
（EMPLOYEE_ID，EMPLOYEE_NAME，SALARY) AS
SELECT empno ,ename ,salary
FROM emp
WHERE salary >2000

　　如果在创建的视图包含错误在正常情况下，视图将不会被创建。但如果你需要创建一个带错误的视图必须在CREATE VIEW语句中带上FORCE选项。如：

CREATE FORCE VIEW ORDER_STATUS AS
SELECT * FROM PURCHASE_ORDERS
WHERE STATUS='APPPOVE';

SQL>/

warning :View create with compilation errors

　　这样将创建了一个名为ORDER_STATUS的视图，但这样的视图的状态是不合法的，如果以后状态发生变化则可以重新编译，其状态也变成合法的。

　　从视图中获得数据

　　从视图中获得数据与从表中获得数据基本一样，用户可以在连接和子查询中使用视图，也可以使用SQL函数，以及所有SELECT语句的字串。

　　插入、更新、删除数据

　　用户在一定的限制条件下可以通过视图更新、插入、删除数据。如果视图连接多个表，那么在一个时间里只能更新一个表。所有的能被更新的列可以在数据字典USER_UPDATETABLE_COLUMNS中查到。

　　用户在CREATE VIEW中可以使用了WITH子串。WITH READ ONLY子串表示创建的视图是一个只读视图，不能进行更新、插入、删除操作。WITH CHECK OPTION表示可以进行插入和更新操作，但应该满足WHERE子串的条件。这个条件就是创建视图WHERE子句的条件，比如在上面的例子中用户创建了一个视图TOP_EMP,在这个视图中用户不能插入salary小于2000的数据行。

　　删除视图

　　删除视图使用DROP VIEW命令。同时将视图定义从数据字典中删除，基于视图的权限也同时被删除，其他涉及到该视图的函数、视图、程序等都将被视为非法。

　　例：

DROP VIEW TOP_EMP;

 

之操作和控制语言

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML， 数据定义语言DDL，数据控制语言DCL。其中用于定义数据的结构，比如 创建、修改或者删除数据库；DCL用于定义数据库用户的权限；在这篇文章中我将详细讲述这两种语言在Oracle中的使用方法。

　　DML语言

　　DML是SQL的一个子集，主要用于修改数据，下表列出了ORACLE支持的DML语句。

语句  用途
INSERT 向表中添加行
UPDATE 更新存储在表中的数据
DELETE  删除行
SELECT FOR UPDATE  禁止其他用户访问DML语句正在处理的行。
LOCK TABLE  禁止其他用户在表中使用DML语句

　　插入数据

　　INSERT语句常常用于向表中插入行，行中可以有特殊数据字段，或者可以用子查询从已存在的数据中建立新行。

　　列目录是可选的，缺省的列的目录是所有的列名，包括comlumn_id,comlumn_id可以在数据字典视图ALL_TAB_COLUMNS,USER_TAB_COLUMNS,或者DBA_TAB_COLUMNS中找到。

　　插入行的数据的数量和数据类型必须和列的数量和数据类型相匹配。不符合列定义的数据类型将对插入值实行隐式数据转换。NULL字符串将一个NULL值插入适当的列中。关键字NULL常常用于表示将某列定义为NULL值。

　　下面的两个例子是等价的。

 

INSERT INTO customers(cust_id,state,post_code)
VALUE('Ariel',NULL,'94501'); 

　　或

INSERT INTO customers(cust_id,state,post_code)
VALUE('Ariel',,'94501');

　　更新数据

　　UPDATE命令用于修改表中的数据。

UPDATE order_rollup
SET(qty,price)=(SELECT SUM(qty),SUM(price) FROM order_lines WHERE customer_id='KOHL'
WHERE cust_id='KOHL'
AND order_period=TO_DATE('01-Oct-2000')

　　删除数据

　　DELETE语句用来从表中删除一行或多行数据，该命令包含两个语句：

　　　1、关键字DELETE FROM后跟准备从中删除数据的表名。

　　　2、WHERE后跟删除条件

DELETE FROM po_lines
WHERE ship_to_state IN ('TX','NY','IL')
AND order_date 

　　清空表

　　如果你想删除表中所有数据，清空表，可以考虑使用DDL语言的TRUNCATE语句。TRUNCATE就像没有WHERE子句的DELETE命令一样。TRUNCATE将删除表中所有行。TRUNCATE不是DML语句是DDL语句，他和DELETE右不同的特点。

TRUNCATE TABLE (schema)table DROP(REUSE) STORAGE 

　　STORAGE子串是可选的，缺省是DROP STORAGE。当使用DROP STORAGE时将缩短表和表索引，将表收缩到最小范围，并重新设置NEXT参数。REUSE STORAGE不会缩短表或者调整NEXT参数。

　　TRUNCATE和DELETE有以下几点区别

　　1、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销，而TRUNCATE则不会被撤销。

　　2、TRUNCATE是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。

　　3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。

　　4、TRUNCATE不能触发任何DELETE触发器。

　　5、不能授予任何人清空他人的表的权限。

　　6、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。

　　7、不能清空父表。

　　SELECT FOR UPDATE

　　select for update语句用于锁定行，阻止其他用户在该行上修改数据。当该行被锁定后其他用户可以用SELECT语句查询该行的数据，但不能修改或锁定该行。

　　锁定表

　　LOCK语句常常用于锁定整个表。当表被锁定后，大多数DML语言不能在该表上使用。LOCK语法如下：

LOCK schema table IN lock_mode

　　其中lock_mode有两个选项：

　　　share 共享方式

　　　exclusive 唯一方式

　　例：

LOCK TABLE intentory IN EXCLUSIVE MODE

　　死锁

　　当两个事务都被锁定，并且互相都在等待另一个被解锁，这种情况称为死锁。

　　当出现死锁时，ORACLE将检测死锁条件，并返回一个异常。

事务控制
　　
　　事务控制包括协调对相同数据的多个同步的访问。当一个用户改变了另一个用户正在使用的数据时，oracle使用事务控制谁可以操作数据。

　　事务

　　事务表示工作的一个基本单元，是一系列作为一个单元被成功或不成功操作的SQL语句。在SQL和PL/SQL中有很多语句让程序员控制事务。程序员可以：

　　　1、显式开始一个事物，选择语句级一致性或事务级一致性

　　　2、设置撤销回滚点，并回滚到回滚点
　
　　　3、完成事务永远改变数据或者放弃修改。
　　
　　事务控制语句

语句 用途
Commit  完成事务，数据修改成功并对其他用户开放
Rollback  撤销事务，撤销所有操作
rollback to savepoint  撤销在设置的回滚点以后的操作
set transaction 响应事务或语句的一致性；特别对于事务使用回滚段

　　例：

BEGIN
UPDATE checking
SET balance=balance-5000
WHERE account='Kieesha';

INSERT INTO checking_log(action_date,action,amount)
VALUES (SYSDATE,'Transfer to brokerage',-5000);

UPDATE brokerage
SET cash_balance=cash_balance+5000
WHERE account='Kiesha';

INSERT INTO brokerage_log(action_date,action,amount)
VALUES (SYSDATE,'Tracfer from checking',5000)

COMMIT

EXCEPTION
WHEN OTHERS
ROLLBACK

END

　　Savepoint 和 部分回滚(Partial Rollback)

　　在SQL和PL/SQL中Savepoint是在一事务范围内的中间标志。经常用于将一个长的事务划分为小的部分。保留点Savepoint可标志长事务中的任何点，允许可回滚该点之后的操作。在应用程序中经常使用Savepoint；例如一过程包含几个函数，在每个函数前可建立一个保留点，如果函数失败，很容易返回到每一个函数开始的情况。在回滚到一个Savepoint之后，该Savepoint之后所获得的数据封锁被释放。为了实现部分回滚可以用带TO Savepoint子句的ROLLBACK语句将事务回滚到指定的位置。

　　例

BEGIN

INSERT INTO ATM_LOG(who,when,what,where)
VALUES ('Kiesha',SYSDATE,'Withdrawal of $100','ATM54')
SAVEPOINT ATM_LOGGED;

UPDATE checking
SET balance=balance-100
RETURN balance INTO new_balance;

IF new_balance<0
THEN
ROLLBACK TO ATM_LOGGED;
COMMIT
RAISE insufficient_funda;
END IF

END
 

　　关键字SAVEPOINT是可选的，所以下面两个语句是等价的：

ROLLBACK TO ATM_LOGGED;
ROLLBACK TO SAVEPOINT ATM_LOGGED;

　　一致性和事务

　　一致性是事物控制的关键慨念。掌握了oracle 的一致性模型，能使您更好的，更恰当的使用事务控制。oracle通过一致性保证数据只有在事务全部完成后才能被用户看见和使用。这项技术对多用户数据库有巨大的作用。

　　oracle常常使用语句级（state-level)一致性，保证数据在语句的生命期之间是可见的但不能被改变。事务由多个语句组成，当使用事务时，事物级（transaction-level)一致性在整个事务生命期中保证数据对所有语句都是可见的。

　　oracle通过SCN(syatem change number)实施一致性。一个SCN是一个面向时间的数据库内部键。SCN只会增加不会减少，SCN表示了时间上的一个点，每个数据块都有一个SCN,通过比较这个点实施操作。

　　事务级一致性

　　SET TRANSACTION 的一个作用是确保事务级一致或语句级一致中有一个实施。ORACLE使用这些术语：

　　　ISOLATION LEVEL READ COMMIT 表示语句级一致

　　　ISOLATION LEVEL SERIALIZABLE 表示事务级一致。

　　例：

SET TRANSACTION ISOLATION LEVEL READ COMMIT;

SET TRANSACTION ISOLATION LEVEL READ COMMIT

　　下面的语句也能确保事务级一致：

SET TRANSCATION READ ONLY

　　任何企图在只读（READ ONLY）事务中修改数据的操作都会抛出一个异常。但是，READ ONLY事务只能在下列语句中使用：

SELECT(没有FOR UPDATE子句）
LOCK TABLE
SET ROLE
ALTER SYSTEM
ALTER ALARM

　　即使没有改变任何数据，READ ONLY事务依然必须使用一个COMMIT或ROLLBACK以结束整个事务。

　　SET TRANSCTION的另外一个应用是在回滚时直接使用回滚段（ROLLBACK SEGMENT）。回滚段是ORACLE的一个特殊的数据对象，回滚段的头部包含正在使用该回滚段事务的信息。当用户回滚事务（ROLLBACK）时，ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。oracle用round-robin给事务随机分配回滚段。一个大的事务可以分配任何回滚段，这也许会导致回滚段的大小变得很大。因此要避免让大的事务随机分配回滚段。

　　事务以SET TRANSACTION开始,象下面这样：

SET TRANSACTION USE ROLLBACK SEGMENT rb_large;

　　rb_large是一个大的回滚段的名称，现在就给一个大的事务分配了一个大的回滚段，其他的小的回滚段将不由动态空间管理，这样就更有效率。

　　下面我们看一个例子.我们有一个回滚段表空间大小是2G，在高峰时期需要10个回滚段以满足用户的需要，这些高峰在线用户只有小的事务。一周我们连续运行了4个大的事务，这些事务需要删除和加载数据，每一个撤销需要1G，回滚段的大小如下：

rb_large(initial 100M minextenta 2)

rb1 (initial 1M next minextents 5)
rb2 (initial 1M next minextents 5)
rb3 (initial 1M next minextents 5)
rb4 (initial 1M next minextents 5)
rb5 (initial 1M next minextents 5)
rb6 (initial 1M next minextents 5)
rb7 (initial 1M next minextents 5)
rb8 (initial 1M next minextents 5)
rb9 (initial 1M next minextents 5)
rb10 (initial 1M next minextents 5) 

　　所有的都非常恰当的安排在2G的表空间中，如果我们缺省的round-robin给事务分配回滚段，4个大事务将有4个独立的回滚段，每个回滚段的大小将是1G,如果这样我们的2G表空间就不够，而数据库管理员就不得不在夜晚2点起来工作，每个事务都由以下面的语句开始：

SET TRANSACTION USE ROLLBACK SEGMENT rb_large 

　　现在 4个事务重用相同的表空间，保正4个回滚段的表空间在2G以内。数据库管理员可以睡到天亮。
建立和修改用户

　　CREATE USER 语句将建立一个用户。当一个用户连接到ORACLE数据库时，它必须被验证。ORACLE中验证有三种类型：

　　　Database

　　　external

　　　Global

　　缺省是数据库验证，当用户连接到数据库时，oracle将检测用户是否是数据库的合法用户，并且要提供正确的password.external验证，oracle将只检测用户是否是合法用户，password已经被网络或系统验证了。global验证也是只检测是否是合法用户，password由oraclesecurity server验证。

　　Database验证用户账号

　　数据库验证账号是张好的缺省类型，也是最普通的类型。建立一个账号是piyush,口令是welcome的账号，只需执行下面的命令：

CREATE USE piyush IDENTIFIED BY welcome

　　piyush可以通过下面的语句将口令改变为saraswatt:

ALTER USER piyush IDENTIFIED BY saraswati;

　　外部验证用户账号

　　用户账号进入数据库时可以不提供口令，这种情况下代替数据库识别口令的是客户端操作系统。外部验证账号有时也叫OPS$账号，当他们最初在oracle6开始介绍时，oracle账号都有关键字前缀OPS$,这也就是为什么init.ora 参数os_authent_prefix是OPS$--默认特征与oracle6保持一致。os_authent_prefix定义的字符串必须被预处理为用于Oracle外部识别账号的操作系统账号名。创建操作系统用户appl的语句是：

CREATE USER ops$appl IDENTIFIED EATERNALLY

　　但在通常情况下，os_authent_prefix将被设置为空，像下面这样：

CREATE USER appl IDENTIFIED EATERNALLY

　　这样效果是一样的,关键字IDENTIFIED EXTERNALLY告诉ORACLE这是一个外部识别账号。

　　GLOBAL用户账号

　　GLOBAL类型的用户账号数据库不检测口令，而是由X.509目录服务器检测口令。创建一个GLOBAL类型的用户账号的方法是：

CREATE USER scott IDENTIFIED GLOBALLY AS "CN=scott,OU=divisional,O=sybex,C=US"

　　关键字IDENTIFIED GLOBALLY AS表示建立的是一个GLOBAL类型的用户账号.

　　创建和更改用户账号

　　CREATE USER 用于建立用户账号和给用户账号的属性赋值。ALTER USER用于更改用户账号和属性。但CREATE USER语句必须包括用户名和口令。

　　有部分属性能用CREATER USER和ALTER USER语句设置，下面对是这些的属性具体描述：

　　给用户分配缺省表空间

　　表空间（tablespace)是放置表、索引、丛等用户对象的。如果在create user语句中没有包含表空间，那么缺省的是系统表空间。

CREATE USER piyush IDENTIFIED BY saraswati
DEFAULTE TABLESPACE user_data;
ALTER USER manoj DEFAULTE TABLESPACE dev1_data;

　　给用户分配临时表空间

　　临时表空间，顾名思义是临时存放表、索引等用户对象的临时段。建立方法一样

CREATE USER piyush IDENTIFIED BY saraswati
Temporary TABLESPACE user_data;
ALTER USER manoj Temporary TABLESPACE dev1_data;

　　给用户分配表空间的使用定额

　　使用定额限制用户在表空间中使用磁盘的数量。定额可以按字节、千字节、兆字节或者无限制来制定。

CREATE USER piyush IDENTIFIED BY saraswati
DEFAULT TABLESPACE user_data
QUOTA UNLIMITED ON user_data
QUOTA 20M ON tools;
ALTER USER manoj QUOTA 2500K ON tools;

　　给用户分配一个简表

　　简表可以限制用户在会话时消耗的资源。这些资源包括：连接数据库的时间，空闲时间，每次会话的逻辑读数据的数量等等，缺省的简表对资源无限制。

CREATE USER piyush IDENTIFIED BY saraswati
PROFILE TABLESPACE user_data;
ALTER USER manoj Temporary TABLESPACE dev1_data;

　　为用户响应指定角色

　　这个属性只能由ALTER USER语句设置，试图用CREATE USER语句设置将回返回一个例外。

ALTER USER manoj DEFAULT ROLE ALL EXCEPT salary_adm;

　　为用户的password设定到期时间以便在用户下次登录时更改

　　当用户的password到期，在下一次登录时将强迫修改password，oracle提示用户输入旧的password,然后输入新的password。这项功能常用于新用户，当新用户用缺省的password登录时必须修改立即修改password.

ALTER USER manoj IDENTIFIED BY welcome;
ALTER USER manoj PASSWORD EXPIRE;

　　锁定账号，是用户不能登录

ALTER USER ql AC
COUNT LOCK 

　　对账号解锁，以便用户能登录数据库

ALTER USER ql ACCOUNT UNLOCK

　　权限和角色

　　权限允许用户访问属于其它用户的对象或执行程序，ORACLE系统提供三种权限：

　　　Object 对象级

　　　System 系统级

　　　Role 角色级

　　这些权限可以授予给用户、特殊用户public或角色，如果授予一个权限给特殊用户"Public"（用户public是oracle预定义的，每个用户享有这个用户享有的权限）,那么就意味作将该权限授予了该数据库的所有用户。

　　对管理权限而言，角色是一个工具，权限能够被授予给一个角色，角色也能被授予给另一个角色或用户。用户可以通过角色继承权限，除了管理权限外角色服务没有其它目的。权限可以被授予，也可以用同样的方式撤销。

　　建立和使用角色

　　如前所诉，角色存在的目的就是为了使权限的管理变得轻松。建立角色使用CREATE ROLE语句，他的语法如下：

CREATE ROLE role_name IDENTIFIED BY password
CREATE ROLE role_name IDENTIFIED EXTERNALLY
CREATE ROLE role_name IDENTIFIED GLOBALLY

　　缺省情况下建立的角色没有password或者其他的识别。如果使用IDENTIFIED BY 子句建立，那么角色不会自动响应，必须用SET ROLE激活。

SET ROLE role_name IDENTIFIED BY password

　　EXTERNALLY和GLOBALLY类型的角色由操作系统和ORACLE Service server验证。通常用户需要权限修改应用程序中使用的表单中的数据，但是只有在应用程序运行时而不是在使用ad hoc工具时，这种上下文敏感安全可以通过有PASSWORD的角色来实现。当用户在应用程序内部连结数据库时，代码将执行SET ROLE命令，通过安全验证。所以用户不需要知道角色的password,也不需要自己输入SET ROLE命令。

　　对象权限

　　对象权限就是指在表、视图、序列、过程、函数或包等对象上执行特殊动作的权利。有九种不同类型的权限可以授予给用户或角色。如下表：

权限 ALTER DELETE EXECUTE INDEX INSERT READ  REFERENCE SELECT  UPDATE
Directory no  no  no  no  no  yes no no  no
function no no yes no  no no no no no
procedure no  no yes no no no  no no no
package no no yes no no no no no no
DB Object no  no  yes no no  no no no no
Libary  no no yes  no no no  no  no no
Operation  no  no  yes no  no  no  no  no  no
Sequence yes  no  no  no  no  no  no  no  no
Table  yes  yes  no yes yes  no  yes  yes  yes
Type no  no  yes  no  no  no  no  no  no
View  no  yes  no  no  yes  no  no  yes  yes

　　对象由不止一个权限，特殊权限ALL可以被授予或撤销。如TABLE的ALL权限就包括：

　　　SELECT,INSERT,UPDATE和DELETE,还有INDEX,ALTER,和REFERENCE。

　　如何看这个表我们以ALTER权限为例进行说明

　　ALTER权限

　　允许执行ALTER TABLE和LOCK TABLE操作，ALTER TABLE可以进行如下操作：

　　　　. 更改表名

　　　　. 增加或删除列

　　　　. 改变列的数据类型或大小

　　　　. 将表转变为分区表

　　在SEQUENCE上的ALTER权限允许执行ALTER Sequence语句，重新给sequence分配最小值、增量和缓冲区大小。

　　系统权限

　　系统权限需要授予者有进行系统级活动的能力，如连接数据库，更改用户会话、建立表或建立用户等等。你可以在数据字典视图SYSTEM_PRIVILEGE_MAP上获得完整的系统权限。对象权限和系统权限都通过GRANT语句授予用户或角色。需要注意的是在授予对象权限时语句应该是WITH GRANT OPTION子句，但在授予系统权象时语句是WITH ADMIN OPTION，所以在你试图授予系统权限时，使用语句WITH GRANT OPTION系统会报告一个错误：ONLY ADMIN OPTION can be specified。在考试中要特别注意这个语法和错误信息。

　　角色和角色权限

　　角色权限就是将属于用户的权限授予一个角色。任何权限都可以授予给一个角色。授予系统权限给被授予者必须使用WITH_ADMIN_OPTION子句，在会话期间通过SET ROLE语句授予或撤销角色权限。然而，角色权限不能依靠存储在SQL中的权限。如果函数、程序、包、触发器或者方法使用另一个计划拥有的对象，那么就必须直接给对象的拥有者授权，这是因为权限不会在会话之间改变。

　　授予和撤销权限

　　　给用户或者角色授予权限使用GRANT 语句,GRANT语句的语法如下：

GRANT ROLE（或system privilege） TO user(role,Public) WITH ADMIN OPTION（可选）

　　对象权限被授予 WITH GRANT OPTION，

　　权限和数据字典

　　数据字典是ORACLE存储有关数据库结构信息的地方，数据本身存放在其他地方，数据字典由表和视图组成。在考试中关于数据字典最容易考的内容是：查看那一类权限已经被授予。比如DBA_TAB_PRIV包含了用户授予给另一用户的对象权限和在授予时是否带有WITH GRANT OTPION子串的信息。注意DBA_TAB_PRIV不仅仅包含了对表的权限的关系，他还包括函数、包、队列等等上的权限的关系。下表列出了所有的权限和角色的数据字典视图：

　　表： 权限的数据字典视图

视图  作用
ALL_COL_PRIVS 表示列上的授权，用户和PUBLIC是被授予者
ALL_COL_PRIVS_MADE  表示列上的授权，用户是属主和被授予者
ALL_COL_RECD 表示列上的授权，用户和PUBLIC是被授予者
ALL_TAB_PRIVS 表示对象上的授权，用户是PUBLIC或被授予者或用户是属主
ALL_TAB_PRIVS_MADE  表示对象上的权限，用户是属主或授予者
ALL_TAB_PRIVS_RECD  表示对象上的权限, 用户是PUBLIC或被授予者
DBA_COL_PRIVS  数据库列上的所有授权
DBA_ROLE_PRIVS  显示已授予用户或其他角色的角色
DBA_SYS_PRIVS  已授予用户或角色的系统权限
DBA_TAB_PRIVS  数据库对象上的所有权限
ROLE_ROLE_PRIVS  显示已授予用户的角色
ROLE_SYS_PRIVS 显示通过角色授予用户的系统权限
ROLE_TAB_PRIVS 显示通过角色授予用户的对象权限
SESSION_PRIVS  显示用户现在可利用的所有系统权限
USER_COL_PRIVS 显示列上的权限，用户是属主、授予者或被授予者
USER_COL_PRIVS_MADE 显示列上已授予的权限，用户是属主或授予者
USER_COL_PRIVS_RECD  显示列上已授予的权限，用户是属主或被授予者
USER_ROLE_PRIVS  显示已授予给用户的所有角色
USER_SYS_PRIVS  显示已授予给用户的所有系统权限
USER_TAB_PRIVS 显示已授予给用户的所有对象权限
USER_TAB_PRIVS_MADE  显示已授予给其他用户的对象权限，用户是属主
USER_TAB_PRIVS_RECD 显示已授予给其他用户的对象权限，用户是被授予者

之游标

SQL是用于访问ORACLE数据库的语言，PL/SQL扩展和加强了SQL的功能，它同时引入了更强的程序逻辑。 PL/SQL支持DML命令和SQL的事务控制语句。DDL在PL/SQL中不被支持，这就意味作在PL/SQL程序块中不能创建表或其他任何对象。较好的PL/SQL程序设计是在PL/SQL块中使用象DBMS_SQL这样的内建包或执行EXECUTE IMMEDIATE命令建立动态SQL来执行DDL命令，PL/SQL编译器保证对象引用以及用户的权限。

　　下面我们将讨论各种用于访问ORACLE数据库的DDL和TCL语句。

　　查询

　　SELECT语句用于从数据库中查询数据，当在PL/SQL中使用SELECT语句时，要与INTO子句一起使用，查询的返回值被赋予INTO子句中的变量，变量的声明是在DELCARE中。SELECT INTO语法如下：

SELECT [DISTICT|ALL]{*|column[,column,...]}
INTO (variable[,variable,...] |record)
FROM {table|(sub-query)}[alias]
WHERE............

　　PL/SQL中SELECT语句只返回一行数据。如果超过一行数据，那么就要使用显式游标（对游标的讨论我们将在后面进行），INTO子句中要有与SELECT子句中相同列数量的变量。INTO子句中也可以是记录变量。

　　%TYPE属性

　　在PL/SQL中可以将变量和常量声明为内建或用户定义的数据类型，以引用一个列名，同时继承他的数据类型和大小。这种动态赋值方法是非常有用的，比如变量引用的列的数据类型和大小改变了，如果使用了%TYPE,那么用户就不必修改代码，否则就必须修改代码。

　例：

v_empno SCOTT.EMP.EMPNO%TYPE;
v_salary EMP.SALARY%TYPE; 

　　不但列名可以使用%TYPE,而且变量、游标、记录，或声明的常量都可以使用%TYPE。这对于定义相同数据类型的变量非常有用。

DELCARE
V_A NUMBER(5):=10;
V_B V_A%TYPE:=15;
V_C V_A%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE
('V_A='||V_A||'V_B='||V_B||'V_C='||V_C);
END

SQL>/
V_A=10 V_B=15 V_C=
PL/SQL procedure successfully completed.

SQL>

　　其他DML语句

　　其它操作数据的DML语句是:INSERT、UPDATE、DELETE和LOCK TABLE,这些语句在PL/SQL中的语法与在SQL中的语法相同。我们在前面已经讨论过DML语句的使用这里就不再重复了。在DML语句中可以使用任何在DECLARE部分声明的变量，如果是嵌套块，那么要注意变量的作用范围。

　　例：

CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number)
　AS
　　v_ename EMP.ENAME%TYPE;
　BEGIN
　　SELECT ename INTO v_ename
　　FROM emp
　　WHERE empno=p_empno;

　　INSERT INTO FORMER_EMP(EMPNO,ENAME)
　　VALUES (p_empno,v_ename);

　　DELETE FROM emp
　　WHERE empno=p_empno;

　　UPDATE former_emp
　　SET date_deleted=SYSDATE
　　WHERE empno=p_empno;

　　EXCEPTION
　　　WHEN NO_DATA_FOUND THEN
　　　DBMS_OUTPUT.PUT_LINE('Employee Number Not Found!');

　END

　　DML语句的结果

　　当执行一条DML语句后，DML语句的结果保存在四个游标属性中，这些属性用于控制程序流程或者了解程序的状态。当运行DML语句时，PL/SQL打开一个内建游标并处理结果，游标是维护查询结果的内存中的一个区域，游标在运行DML语句时打开，完成后关闭。隐式游标只使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT三个属性.SQL%FOUND,SQL%NOTFOUND是布尔值，SQL%ROWCOUNT是整数值。

　　SQL%FOUND和SQL%NOTFOUND

　　在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在执行DML语句后，SQL%FOUND的属性值将是：

　　. TRUE :INSERT

　　. TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE.

　　. TRUE :SELECT INTO至少返回一行

　　当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE。

　　SQL%ROWCOUNT

　　在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL,对于SELECT INTO语句，如果执行成功，SQL%ROWCOUNT的值为1,如果没有成功，SQL%ROWCOUNT的值为0，同时产生一个异常NO_DATA_FOUND.

　　SQL%ISOPEN

　　SQL%ISOPEN是一个布尔值，如果游标打开，则为TRUE, 如果游标关闭，则为FALSE.对于隐式游标而言SQL%ISOPEN总是FALSE，这是因为隐式游标在DML语句执行时打开，结束时就立即关闭。

　　事务控制语句

　　事务是一个工作的逻辑单元可以包括一个或多个DML语句，事物控制帮助用户保证数据的一致性。如果事务控制逻辑单元中的任何一个DML语句失败，那么整个事务都将回滚，在PL/SQL中用户可以明确地使用COMMIT、ROLLBACK、SAVEPOINT以及SET TRANSACTION语句。

　　COMMIT语句终止事务，永久保存数据库的变化，同时释放所有LOCK,ROLLBACK终止现行事务释放所有LOCK，但不保存数据库的任何变化,SAVEPOINT用于设置中间点，当事务调用过多的数据库操作时，中间点是非常有用的，SET TRANSACTION用于设置事务属性，比如read-write和隔离级等。

　　显式游标

　　当查询返回结果超过一行时，就需要一个显式游标，此时用户不能使用select into语句。PL/SQL管理隐式游标，当查询开始时隐式游标打开，查询结束时隐式游标自动关闭。显式游标在PL/SQL块的声明部分声明，在执行部分或异常处理部分打开，取数据,关闭。下表显示了显式游标和隐式游标的差别：

　　　　　　　　　　　　　　　　　表1 隐式游标和显式游标
隐式游标 显式游标
PL/SQL维护，当执行查询时自动打开和关闭  在程序中显式定义、打开、关闭，游标有一个名字。
游标属性前缀是SQL  游标属性的前缀是游标名
属性%ISOPEN总是为FALSE  %ISOPEN根据游标的状态确定值
SELECT语句带有INTO子串，只有一行数据被处理 可以处理多行数据，在程序中设置循环，取出每一行数据。

 

使用游标

　　这里要做一个声明，我们所说的游标通常是指显式游标，因此从现在起没有特别指明的情况，我们所说的游标都是指显式游标。要在程序中使用游标，必须首先声明游标。

　　声明游标

　　语法：

CURSOR cursor_name IS select_statement;

　　在PL/SQL中游标名是一个未声明变量，不能给游标名赋值或用于表达式中。

　　例：

DELCARE
CURSOR C_EMP IS SELECT empno,ename,salary
FROM emp
WHERE salary>2000
ORDER BY ename;
........
BEGIN

　　在游标定义中SELECT语句中不一定非要表可以是视图，也可以从多个表或视图中选择的列，甚至可以使用*来选择所有的列 。

　　打开游标

　　使用游标中的值之前应该首先打开游标，打开游标初始化查询处理。打开游标的语法是：

OPEN cursor_name

　　cursor_name是在声明部分定义的游标名。

　　例：

OPEN C_EMP;

　　关闭游标

　　语法：

CLOSE cursor_name

　　例：

CLOSE C_EMP;

　　从游标提取数据

　　从游标得到一行数据使用FETCH命令。每一次提取数据后，游标都指向结果集的下一行。语法如下：

FETCH cursor_name INTO variable[,variable,...]

　　对于SELECT定义的游标的每一列，FETCH变量列表都应该有一个变量与之相对应，变量的类型也要相同。

　　例：

SET SERVERIUTPUT ON
DECLARE
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
CURSOR c_emp IS SELECT ename,salary FROM emp;
BEGIN
OPEN c_emp;
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename
||'is'|| v_salary);
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename
||'is'|| v_salary);
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename
||'is'|| v_salary);
CLOSE c_emp;
END

　　这段代码无疑是非常麻烦的，如果有多行返回结果，可以使用循环并用游标属性为结束循环的条件，以这种方式提取数据，程序的可读性和简洁性都大为提高，下面我们使用循环重新写上面的程序：

SET SERVERIUTPUT ON
DECLARE
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
CURSOR c_emp IS SELECT ename,salary FROM emp;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_ename,v_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename
||'is'|| v_salary);
END 

　　记录变量

　　定义一个记录变量使用TYPE命令和%ROWTYPE，关于%ROWsTYPE的更多信息请参阅相关资料。

　　记录变量用于从游标中提取数据行，当游标选择很多列的时候，那么使用记录比为每列声明一个变量要方便得多。

　　当在表上使用%ROWTYPE并将从游标中取出的值放入记录中时，如果要选择表中所有列，那么在SELECT子句中使用*比将所有列名列出来要安全得多。

　　例：

SET SERVERIUTPUT ON
DECLARE
R_emp EMP%ROWTYPE;
CURSOR c_emp IS SELECT * FROM emp;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUT.PUT.PUT_LINE('Salary of Employee'||r_emp.ename||'is'|| r_emp.salary);
END LOOP;
CLOSE c_emp;
END;

　　%ROWTYPE也可以用游标名来定义，这样的话就必须要首先声明游标：

SET SERVERIUTPUT ON
DECLARE
CURSOR c_emp IS SELECT ename,salary FROM emp;
R_emp c_emp%ROWTYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUT.PUT.PUT_LINE('Salary of Employee'||r_emp.ename||'is'|| r_emp.salary);
END LOOP;
CLOSE c_emp;
END; 

　　带参数的游标

　　与存储过程和函数相似，可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用。它的语法如下：

CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;

　　定义参数的语法如下：

Parameter_name [IN] data_type[{:=|DEFAULT} value]

　　与存储过程不同的是，游标只能接受传递的值，而不能返回值。参数只定义数据类型，没有大小。

　　另外可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。游标中定义的参数只是一个占位符，在别处引用该参数不一定可靠。

　　在打开游标时给参数赋值，语法如下：

OPEN cursor_name[value[,value]....];

　　参数值可以是文字或变量。

　　例：

DECALRE

CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno;
CURSOR c_emp (p_dept VARACHAR2) IS
SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename
r_dept DEPT%ROWTYPE;
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
v_tot_salary EMP.SALARY%TYPE;

BEGIN

OPEN c_dept;
LOOP
FETCH c_dept INTO r_dept;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
v_tot_salary:=0;
OPEN c_emp(r_dept.deptno);
LOOP
FETCH c_emp INTO v_ename,v_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
CLOSE c_emp;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
CLOSE c_dept;
END;

游标FOR循环

　　在大多数时候我们在设计程序的时候都遵循下面的步骤：

　　1、打开游标

　　2、开始循环

　　3、从游标中取值

　　4、检查那一行被返回

　　5、处理

　　6、关闭循环

　　7、关闭游标

　　可以简单的把这一类代码称为游标用于循环。但还有一种循环与这种类型不相同，这就是FOR循环，用于FOR循环的游标按照正常的声明方式声明，它的优点在于不需要显式的打开、关闭、取数据，测试数据的存在、定义存放数据的变量等等。游标FOR 循环的语法如下：

FOR record_name IN
(corsor_name[(parameter[,parameter]...)]
| (query_difinition)
LOOP
statements
END LOOP;

　　下面我们用for循环重写上面的例子：

DECALRE

CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno;
CURSOR c_emp (p_dept VARACHAR2) IS
SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename

v_tot_salary EMP.SALARY%TYPE;

BEGIN

FOR r_dept IN c_dept LOOP
DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
v_tot_salary:=0;
FOR r_emp IN c_emp(r_dept.deptno) LOOP
DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;

END; 

　　在游标FOR循环中使用查询

　　在游标FOR循环中可以定义查询，由于没有显式声明所以游标没有名字，记录名通过游标查询来定义。

DECALRE

　v_tot_salary EMP.SALARY%TYPE;

BEGIN

　FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP
　　DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
　　v_tot_salary:=0;
　　FOR r_emp IN (SELECT ename,salary
　　　FROM emp
　　　WHERE deptno=p_dept
　　　ORDER BY ename) LOOP
　　DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);
　　v_tot_salary:=v_tot_salary+v_salary;
　　END LOOP;
　　DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
　END LOOP;

END; 

　　游标中的子查询

　　语法如下：

CURSOR C1 IS SELECT * FROM emp
WHERE deptno NOT IN (SELECT deptno
FROM dept
WHERE dname!='ACCOUNTING'); 

　　可以看出与SQL中的子查询没有什么区别。

　　游标中的更新和删除

　　在PL/SQL中依然可以使用UPDATE和DELETE语句更新或删除数据行。显式游标只有在需要获得多行数据的情况下使用。PL/SQL提供了仅仅使用游标就可以执行删除或更新记录的方法。

　　UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。要使用这个方法，在声明游标时必须使用FOR UPDATE子串，当对话使用FOR UPDATE子串打开一个游标时，所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，其他对象只能查询这些数据行，不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。

　　语法：

FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..
[nowait]

　　在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，直到数据行解锁。

　　在UPDATE和DELETE中使用WHERE CURRENT OF子串的语法如下：

WHERE{CURRENT OF cursor_name|search_condition}

　　例：

DELCARE

CURSOR c1 IS SELECT empno,salary
FROM emp
WHERE comm IS NULL
FOR UPDATE OF comm;

v_comm NUMBER(10,2);

BEGIN

FOR r1 IN c1 LOOP

IF r1.salary<500 THEN
v_comm:=r1.salary*0.25;
ELSEIF r1.salary<1000 THEN
v_comm:=r1.salary*0.20;
ELSEIF r1.salary<3000 THEN
v_comm:=r1.salary*0.15;
ELSE
v_comm:=r1.salary*0.12;
END IF;

UPDATE emp;
SET comm=v_comm
WHERE CURRENT OF c1l;

END LOOP;
END
 

之过程和函数

过程和函数

　　过程和函数都以编译后的形式存放在数据库中，函数可以没有参数也可以有多个参数并有一个返回值。过程有零个或多个参数，没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值，函数和过程的主要区别不在于返回值，而在于他们的调用方式。过程是作为一个独立执行语句调用的：

pay_involume(invoice_nbr,30,due_date);

　　函数以合法的表达式的方式调用：

order_volumn:=open_orders(SYSDATE,30);

　　创建过程的语法如下：

CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name
[parameter_lister]
{AS|IS}
declaration_section
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name] 

　　每个参数的语法如下：

paramter_name mode datatype [(:=|DEFAULT) value]

　　mode有三种形式：IN、OUT、INOUT。

　　IN表示在调用过程的时候，实际参数的取值被传递给该过程，形式参数被认为是只读的，当过程结束时，控制会返回控制环境，实际参数的值不会改变。

　　OUT在调用过程时实际参数的取值都将被忽略，在过程内部形式参数只能是被赋值，而不能从中读取数据，在过程结束后形式参数的内容将被赋予实际参数。

　　INOUT这种模式是IN和OUT的组合；在过程内部实际参数的值会传递给形式参数，形势参数的值可读也可写，过程结束后，形势参数的值将赋予实际参数。

　　创建函数的语法和过程的语法基本相同，唯一的区别在于函数有RETUREN子句

CREATE [ OR REPLACE] FINCTION [schema.]function_name
[parameter_list]
RETURN returning_datatype
{AS|IS}
declaration_section
BEGIN
executable_section
[EXCEPTION]
exception_section
END [procedure_name] 

　　在执行部分函数必须有哟个或多个return语句。

　　在创建函数中可以调用单行函数和组函数，例如：

CREATE OR REPLACE FUNCTION my_sin(DegreesIn IN NUMBER)
RETURN NUMBER
IS
pi NUMBER=ACOS(-1);
RadiansPerDegree NUMBER;

BEGIN
RadiansPerDegree=pi/180;
RETURN(SIN(DegreesIn*RadiansPerDegree));
END

包

　　包是一种将过程、函数和数据结构捆绑在一起的容器；包由两个部分组成：外部可视包规范，包括函数头，过程头，和外部可视数据结构；另一部分是包主体(package body),包主体包含了所有被捆绑的过程和函数的声明、执行、异常处理部分。

　　打包的PL/SQL程序和没有打包的有很大的差异，包数据在用户的整个会话期间都一直存在，当用户获得包的执行授权时，就等于获得包规范中的所有程序和数据结构的权限。但不能只对包中的某一个函数或过程进行授权。包可以重载过程和函数，在包内可以用同一个名字声明多个程序，在运行时根据参数的数目和数据类型调用正确的程序。

　　创建包必须首先创建包规范，创建包规范的语法如下：

CREATE [OR REPLACE] PACKAGE package_name
{AS|IS}
public_variable_declarations |
public_type_declarations |
public_exception_declarations |
public_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]

　　创建包主体使用CREATE PACKAGE BODY语句：

CREATE [OR REPLACE] PACKAGE BODY package_name
{AS|IS}
private_variable_declarations |
private_type_declarations |
private_exception_declarations |
private_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]

　　私有数据结构是那些在包主体内部，对被调用程序而言是不可见的。

　　触发器(Triggers)

　　触发器是一种自动执行响应数据库变化的程序。可以设置为在触发器事件之前或之后触发或执行。能够触发触发器事件的事件包括下面几种：

　　DML事件
　　DDL事件
　　数据库事件

　　DML事件触发器可以是语句或行级触发器。DML语句触发器在触发语句之前或之后触发DML行级触发器在语句影响的行变化之前或之后触发。用户可以给单一事件和类型定义多个触发器，但没有任何方法可以增强多触发器触发的命令。下表列出了用户可以利用的触发器事件：

事件 触发器描述
INSERT  当向表或视图插入一行时触发触发器
UPDATE  更新表或视图中的某一行时触发触发器
DELETE 从表或视图中删除某一行时触发触发器
CREATE 当使用CREATE语句为数据库或项目增加一个对象时触发触发器
ALTER 当使用ALTER语句为更改一个数据库或项目的对象时触发触发器
DROP 当使用DROP语句删除一个数据库或项目的对象时触发触发器
START 打开数据库时触发触发器，在事件后触发
SHUTDOWN  关闭数据库时触发，事件前触发
LOGON 当一个会话建立时触发，事件前触发
LOGOFF 当关闭会话时触发，事件前触发
SERVER 服务器错误发生时触发触发器，事件后触发

　　创建触发器的语法如下：

CREATE [OR REPLACE] TRIGGER trigger_name
{before|after|instead of} event
ON {table_or_view_name|DATABASE}
[FOR EACH ROW[WHEN condition]]
trigger_body

　　只有DML触发器（INSERT、UPDATE、DELETE)语句可以使用INSTEAD OF触发器并且只有表的DML触发器可以是BEFORE或AFTER触发器。

　　象约束一样触发器可以被设置为禁用或启用来关闭或打开他们的执行体(EXECUTE),将触发器设置为禁用或启用使用ALTER TRIGGER语句：

ALTER TRIGGER trigger_name ENABLE;
ALTER TRIGGER trigger_name DISABLE;

　　要禁用或启用表的所有触发器，使用ALTER TABLE语句

ALTER TRIGGER table_name DISABLE ALL TRIGGER;
ALTER TRIGGER table_name ENABLE ALL TRIGGER;

　　删除触发器使用DROP TRIGGER

DROP TRIGGER trigger_name;

　　数据字典

　　Oracle数据字典包含了用户数据库的元数据。带下划线的表名称中带OBJ$、UET$、SOURCE$，这些表是在执行CREATE DATABASE语句期间由sql.bsq脚本创建的，一般情况下用户很少访问这些表。脚本catalog.sql（通常位于$oracle_home/rdbms/admin）在CREATE DATABASE语句之后立即运行，创建数据字典视图。

　　数据字典视图大致可以分为三类：

　　.前缀为USER_的数据字典视图，包含了用户拥有的对象的信息。

　　.前缀为ALL_的数据字典视图，包含了用户当前可以访问的全部对象和权限的信息。

　　.前缀为DBA_的数据字典视图，包含了数据库拥有的所有对象和权限的信息。

　　在绝大多数数据字典视图中都有象DBA_TABLES,ALL_TABLES和USER_TABLES这样的视图家族。Oracle中有超过100个视图家族，所以要全面介绍这些视图家族是单调乏味的而且没有多大的意义。在下表中列出了最重要和最常用的视图家族，需要注意的是每个视图家族都有一个DBA_,一个ALL_一个USER_视图。

视图家族(View Family) 描述
COL_PRIVS 包含了表的列权限，包括授予者、被授予者和权限
EXTENTS  数据范围信息，比如数据文件，数据段名(segment_name)和大小
INDEXES 索引信息，比如类型、唯一性和被涉及的表
IND_COLUMNS  索引列信息，比如索引上的列的排序方式
OBJECTS  对象信息，比如状态和DDL time
ROLE_PRIVS 角色权限，比如GRANT和ADMIN选项
SEGMENTS  表和索引的数据段信息，比如tablespace和storage
SEQUECNCES 序列信息，比如序列的cache、cycle和ast_number
SOURCE  除触发器之外的所有内置过程、函数、包的源代码
SYNONYMS  别名信息，比如引用的对象和数据库链接db_link
SYS_PRIVS  系统权限，比如grantee、privilege、admin选项
TAB_COLUMNS  表和视图的列信息，包括列的数据类型
TAB_PRIVS  表权限，比如授予者、被授予者和权限
TABLES 表信息，比如表空间(tablespace),存储参数(storage parms)和数据行的数量
TRIGGERS  触发器信息，比如类型、事件、触发体(trigger body)
USERS 用户信息，比如临时的和缺省的表空间
VIEWS 视图信息，包括视图定义

　　在Oracle中还有一些不常用的数据字典表，但这些表不是真正的字典家族，他们都是一些重要的单一的视图。

VIEW NAME 描述
USER_COL_PRIVS_MADE 用户授予他人的列权限
USER_COL_PRIVS_RECD  用户获得的列权限
USER_TAB_PRIVS_MADE  用户授予他人的表权限
USER_TAB_PRIVS_RECD 用户获得的表权限

　　其他的字典视图中主要的是V$视图，之所以这样叫是因为他们都是以V$或GV$开头的。V$视图是基于X$虚拟视图的。V$视图是SYS用户所拥有的，在缺省状况下，只有SYS用户和拥有DBA系统权限的用户可以看到所有的视图，没有DBA权限的用户可以看到USER_和ALL_视图，但不能看到DBA_视图。与DBA_,ALL,和USER_视图中面向数据库信息相反，这些视图可视的给出了面向实例的信息。

　　　在大型系统上化几周时间手工输入每一条语句

　　　手工输入带用户名变量的语句，然后再输入每一个用户名，这需要花好几个小时的时间

　　　写一条SQL语句，生成需要的ALTER USER语句，然后执行他，这只需要几分钟时间

　　很明显我们将选择生成SQL的方法：

　　例：

SELECT 'ALTER USER'||username||
'TEMPORARY TABLESPACE temp;'
FROM DBA_USERS
WHERE username<>'SYS'
AND temporary_tablespace<>'TEMP';

　　这个查询的结果将被脱机处理到一个文件中，然后在执行：

ALTER USER SYSTEM TEMPORARY TABLESPACE temp;
ALTER USER OUTLN TEMPORARY TABLESPACE temp;
ALTER USER DBSNMP TEMPORARY TABLESPACE temp;
ALTER USER SCOTT TEMPORARY TABLESPACE temp;
ALTER USER DEMO TEMPORARY TABLESPACE temp; 

 
之完整性约束
 

完整性约束

　　完整性约束用于增强数据的完整性，Oracle提供了5种完整性约束：

　　　 Check
　　　 NOT NULL
　　　 Unique
　　　 Primary
　　　 Foreign key

　　完整性约束是一种规则，不占用任何数据库空间。完整性约束存在数据字典中，在执行SQL或PL/SQL期间使用。用户可以指明约束是启用的还是禁用的，当约束启用时，他增强了数据的完整性，否则，则反之，但约束始终存在于数据字典中。

　　禁用约束，使用ALTER语句

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

　　或

ALTER TABLE policies DISABLE CONSTRAINT chk_gender

　　如果要重新启用约束：

ALTER TABLE policies ENABLE CONSTRAINT chk_gender

　　删除约束

ALTER TABLE table_name DROP CONSTRAINT constraint_name

　　或

ALTER TABLE policies DROP CONSTRAINT chk_gender;

　　Check 约束

　　在数据列上Check 约束需要 一个特殊的布尔条件或者将数据列设置成TRUE，至少一个数据列的值是NULL，Check约束用于增强表中数据内容的简单的商业规则。用户使用Check约束保证数据规则的一致性。Check约束可以涉及该行同属Check约束的其他数据列但不能涉及其他行或其他表，或调用函数SYSDATE,UID,USER,USERENV。如果用户的商业规则需要这类的数据检查，那么可以使用触发器。Check约束不保护LOB数据类型的数据列和对象、嵌套表、VARRY、ref等。单一数据列可以有多个Check约束保护，一个Check约束可以保护多个数据列。

　　创建表的Check约束使用CREATE TABLE语句，更改表的约束使用ALTER TABLE语句。

　　语法：

CONSTRAINT [constraint_name] CHECK (condition);

　　Check约束可以被创建或增加为一个表约束，当Check约束保护多个数据列时，必须使用表约束语法。约束名是可选的并且如果这个名字不存在，那么oracle将产生一个以SYS_开始的唯一的名字。

　　例：

CREATE TABLE policies
(policy_id NUMBER,
holder_name VARCHAR2(40),
gender VARCHAR2(1) constraint chk_gender CHECK (gender in ('M','F'),
marital_status VARCHAR2(1),
date_of_birth DATE,
constraint chk_marital CHECK (marital_status in('S','M','D','W'))
);
 

　　NOT NULL约束

　　NOT NULL约束应用在单一的数据列上，并且他保护的数据列必须要有数据值。缺省状况下，ORACLE允许任何列都可以有NULL值。某些商业规则要求某数据列必须要有值，NOT NULL约束将确保该列的所有数据行都有值。

　　例：

CREATE TABLE policies
(policy_id NUMBER,
holder_name VARCHAR2(40) NOT NULL,
gender VARCHAR2(1),
marital_status VARCHAR2(1),
date_of_birth DATE NOT NULL
);

　　对于NOT NULL的ALTER TABLE语句与其他约束稍微有点不同。

ALTER TABLE policies MODIFY holder_name NOT NULL

唯一性约束(Unique constraint)

　　唯一性约束可以保护表中多个数据列，保证在保护的数据列中任何两行的数据都不相同。唯一性约束与表一起创建，在唯一性约束创建后，可以使用ALTER TABLE语句修改。

　　语法：

column_name data_type CONSTRAINT constraint_name UNIQUE 

　　如果唯一性约束保护多个数据列，那么唯一性约束要作为表约束增加。语法如下：

CONSTRAINT constraint_name (column) UNIQUE USING INDEX TABLESPACE (tablespace_name) STORAGE (stored clause)

　　唯一性约束由一个B-tree索引增强，所以可以在USING子串中为索引使用特殊特征，比如表空间或存储参数。CREATE TABLE语句在创建唯一性约束的同时也给目标数据列建立了一个唯一的索引。

CREATE TABLE insured_autos
(policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY,
vin VARCHAR2(10),
coverage_begin DATE,
coverage_term NUMBER,
CONSTRAIN unique_auto UNIQUE (policy_id,vin) USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0)
);

　　用户可以禁用未以性约束，但他仍然存在，禁用唯一性约束使用ALTER TABLE 语句

ALTER TABLE insured_autos DISABLE CONSTRAIN unique_name;

　　删除唯一性约束，使用ALTER TABLE....DROP CONSTRAIN语句

ALTER TABLE insured_autos DROP CONSTRAIN unique_name;

　　注意用户不能删除在有外部键指向的表的唯一性约束。这种情况下用户必须首先禁用或删除外部键（foreign key)。

　　删除或禁用唯一性约束通常同时删除相关联的唯一索引，因而降低了数据库性能。经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误。要避免这样错误，可以采取下面的步骤：

　　1、在唯一性约束保护的数据列上创建非唯一性索引。

　　2、添加唯一性约束

　　主键(Primary Key)约束

　　表有唯一的主键约束。表的主键可以保护一个或多个列，主键约束可与NOT NULL约束共同作用于每一数据列。NOT NULL约束和唯一性约束的组合将保证主键唯一地标识每一行。像唯一性约束一样，主键由B-tree索引增强。

　　创建主键约束使用CREATE TABLE语句与表一起创建，如果表已经创建了，可以使用ALTER TABLE语句。

CREATE TABLE policies
(policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY,
holder_name VARCHAR2(40),
gender VARCHAR2(1),
marital_status VARCHAR2(1),
date_of_birth DATE
);

　　与唯一性约束一样，如果主键约束保护多个数据列，那么必须作为一个表约束创建。

CREATE TABLE insured_autos
(policy_id NUMBER,
vin VARCHAR2(40),
coverage_begin DATE,
coverage_term NUMBER,
CONSTRAINT pk_insured_autos PRIMARY KEY (policy_id,vin)
USING INDEX TABLESPACE index
STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0)
);

　　禁用或删除主键必须与ALTER TABLE 语句一起使用

ALTER TABLE policies DROP PRIMARY KEY;

　　或
ALTER TABLE policies DISABLE PRIMARY KEY;

　　外部键约束（Foreign key constraint)

　　外部键约束保护一个或多个数据列，保证每个数据行的数据包含一个或多个null值，或者在保护的数据列上同时拥有主键约束或唯一性约束。引用（主键或唯一性约束）约束可以保护同一个表，也可以保护不同的表。与主键和唯一性约束不同外部键不会隐式建立一个B-tree索引。在处理外部键时，我们常常使用术语父表（parent table)和子表（child table),父表表示被引用主键或唯一性约束的表，子表表示引用主键和唯一性约束的表。

　　创建外部键使用CREATE TABLE语句，如果表已经建立了，那么使用ALTER TABLE语句。

CREATE TABLE insured_autos
(policy_id NUMBER CONSTRAINT policy_fk
REFERENCE policies(policy_id
ON DELETE CASCADE,
vin VARCHAR2(40),
coverage_begin DATE,
coverage_term NUMBER,
make VARCHAR2(30),
model VARCHAR(30),
year NUMBER,
CONSTRAIN auto_fk FROEIGN KEY (make,model,year)
REFERENCES automobiles (make,model,year)
ON DELETE SET NULL
);
 

　　ON DELETE子串告诉ORACLE如果父纪录（parent record)被删除后，子记录做什么。缺省情况下禁止在子记录还存在的情况下删除父纪录。

　　外部键和NULL值

　　在外部键约束保护的数据列中NULL值的处理可能产生不可预料的结果。ORACLE 使用ISO standar Match None规则增强外部键约束。这个规则规定如果任何外部键作用的数据列包含有一个NULL值，那么任何保留该键的数据列在父表中没有匹配值。

　　比如，在父表AUTOMOBILES中，主键作用于数据列MAKE，MODEL，YEAR上，用户使用的表INSURED_AUTOS有一个外部约束指向AOTOMOBILES，注意在INSURES_AUTOS中有一数据行的MODEL列为NULL值，这一行数据已经通过约束检查，即使MAKE列也没有显示在父表AUTOMOBILES中，如下表：

　　表1 AUTOMOBILES

MAKE  MODEL  YEAR
Ford  Taurus 2000
Toyota Camry  1999

　　表2 INSURED_AUTOS

POLICY_ID MAKE  MODEL  YEAR
576  Ford  Taurus  2000
577  Toyota Camry  1999 
578  Tucker  NULL  1949

　　延迟约束检验（Deferred Constraint Checking）

　　约束检验分两种情况，一种是在每一条语句结束后检验数据是否满足约束条件，这种检验称为立即约束检验（immediately checking）,另一种是在事务处理完成之后对数据进行检验称之为延迟约束检验。在缺省情况下Oracle约束检验是立即检验（immediately checking),如果不满足约束将先是一条错误信息，但用户可以通过SET CONSTRAINT语句选择延迟约束检验。语法如下：
SET CONSTRAINT constraint_name|ALL DEFEERRED|IMMEDIATE --;

 

之异常处理

 

PL/SQL处理异常不同于其他程序语言的错误管理方法，PL/SQL的异常处理机制与ADA很相似，有一个处理错误的全包含方法。当发生错误时，程序无条件转到异常处理部分，这就要求代码要非常干净并把错误处理部分和程序的其它部分分开。oracle允许声明其他异常条件类型以扩展错误/异常处理。这种扩展使PL/SQL的异常处理非常灵活。

　　当一个运行时错误发生时，称为一个异常被抛出。PL/SQL程序编译时的错误不是能被处理得异常，只有在运行时的异常能被处理。在PL/SQL程序设计中异常的抛出和处理是非常重要的内容。

　　抛出异常

　　由三种方式抛出异常

　　　. 通过PL/SQL运行时引擎

　　　. 使用RAISE语句

　　　. 调用RAISE_APPLICATION_ERROR存储过程

　　当数据库或PL/SQL在运行时发生错误时，一个异常被PL/SQL运行时引擎自动抛出。异常也可以通过RAISE语句抛出

　　RAISE exception_name;

　　显式抛出异常是程序员处理声明的异常的习惯用法，但RAISE不限于声明了的异常，它可以抛出任何任何异常。例如，你希望用TIMEOUT_ON_RESOURCE错误检测新的运行时异常处理器，你只需简单的在程序中使用下面的语句：

　　RAISE TIMEOUT_ON_RESOUCE;

　　下面看一个订单输入系统，当库存小于订单时抛出一个inventory_too_low异常。

DECLARE
inventory_too_low EXCEPTION;
---其他声明语句
BEGIN
.
.
IF order_rec.qty>inventory_rec.qty THEN
RAISE inventory_too_low;
END IF
.
.
EXCEPTION
WHEN inventory_too_low THEN
order_rec.staus:='backordered';
replenish_inventory(inventory_nbr=>
inventory_rec.sku,min_amount=>order_rec.qty-inventory_rec.qty);
END;
 

　　这里replenish_inventory是一个触发器。
　
　　处理异常

　　PL/SQL程序块的异常部分包含了程序处理错误的代码，当异常被抛出时，一个异常陷阱就自动发生，程序控制离开执行部分转入异常部分,一旦程序进入异常部分就不能再回到同一块的执行部分。下面是异常部分的一般语法：

EXCEPTION
　WHEN exception_name THEN
　　Code for handing exception_name
　[WHEN another_exception THEN
　　Code for handing another_exception]
　[WHEN others THEN
　　code for handing any other exception.]
 

　　用户必须在独立的WHEN子串中为每个异常设计异常处理代码，WHEN OTHERS子串必须放置在最后面作为缺省处理器处理没有显式处理的异常。当异常发生时，控制转到异常部分，ORACLE查找当前异常相应的WHEN..THEN语句，捕捉异常，THEN之后的代码被执行，如果错误陷阱代码只是退出相应的嵌套块，那么程序将继续执行内部块END后面的语句。如果没有找到相应的异常陷阱，那么将执行WHEN OTHERS。在异常部分WHEN 子串没有数量限制。

EXCEPTION

　WHEN inventory_too_low THEN
　　order_rec.staus:='backordered';
　　replenish_inventory(inventory_nbr=>
　　inventory_rec.sku,min_amount=>order_rec.qty-inventory_rec.qty);
　WHEN discontinued_item THEN
　　--code for discontinued_item processing
　WHEN zero_divide THEN
　　--code for zero_divide
　WHEN OTHERS THEN
　　--code for any other exception
END;
 

　　当异常抛出后，控制无条件转到异常部分，这就意味着控制不能回到异常发生的位置，当异常被处理和解决后，控制返回到上一层执行部分的下一条语句。

BEGIN
　DECLARE
　　bad_credit;
　BEGIN
　　RAISE bad_credit;
　　　--发生异常，控制转向；
　EXCEPTION
　　WHEN bad_credit THEN
　　　dbms_output.put_line('bad_credit');
　　END;

　　--bad_credit异常处理后，控制转到这里
　EXCEPTION
　　WHEN OTHERS THEN
　　　--控制不会从bad_credit异常转到这里
　　　--因为bad_credit已被处理
END;

　　当异常发生时，在块的内部没有该异常处理器时，控制将转到或传播到上一层块的异常处理部分。

BEGIN
　DECLARE ---内部块开始
　　bad_credit;
　BEGIN
　　RAISE bad_credit;
　　　--发生异常，控制转向；
　　EXCEPTION
　　WHEN ZERO_DIVIDE THEN --不能处理bad_credite异常
　　　dbms_output.put_line('divide by zero error');
　　END --结束内部块

　　　--控制不能到达这里，因为异常没有解决；
　　　--异常部分

　　EXCEPTION
　　WHEN OTHERS THEN
　　　--由于bad_credit没有解决，控制将转到这里
END;

　　异常传播

　　没有处理的异常将沿检测异常调用程序传播到外面，当异常被处理并解决或到达程序最外层传播停止。
在声明部分抛出的异常将控制转到上一层的异常部分。

BEGIN
executable statements
BEGIN
today DATE:='SYADATE'; --ERRROR
BEGIN --内部块开始
dbms_output.put_line('this line will not execute');
EXCEPTION
WHEN OTHERS THEN
--异常不会在这里处理
END;--内部块结束

EXCEPTION
WHEN OTHERS THEN
处理异常
END

　　执行部分抛出的异常将首先传递到同一块的异常部分，如果在同一块的异常部分没有处理这个异常的处理器，那么异常将会传播到上一层的异常部分中，一直到最外层。

　　在异常部分抛出的异常将控制转到上一层的异常部分。

　　处理异常将停止异常的传播和解决。有时用户希望在错误发生时，程序仍然能执行一些动作，要达到这个目的，可以把希望执行的动作放在异常处理器中，然后执行不带参数的RAISE语句，RAISE语句将重新抛出出现的异常，允许他继续传播。

DECLARE
order_too_old EXCEPTION;
BEGIN
RAISE order_too_old;
EXCEPTION
WHEN order_too_old THEN
DECLARE
file_handle UTL_FILE.FILE_TYPE;
BEGIN
--open file
file_handle:=UTL_FILE.FOPEN
(location=>'/ora01/app/oracle/admin/test/utlsir'
,filename=>'error.log'
.open_mode=>'W');
--write error stack
UTL_FILE.PUT_LINE(filehandle,
DBMS_UTILITY.FORMAT_ERROR_STACK);
--write the call stack
UTL_FILE.PUT_LINE(filehandle,
DBMS_UTILITY.FORMAT_CALL_STACK);
--close error log
UTL_FILE.FCLOSE(file_handle);
RAISE; --re-raise the exception
END
END

　　如果从FORMAT_XXX_STACK输出一个很大的值，那么使用DBMS_OUTPUT或UTL_FILE显示错误或调用堆的异常部分自身也会抛出异常，这两个堆常规下最多能返回2000字节，但utl_file.put_line被限制在1000字节以内，而dbms_output.put_line限制在512字节内。如果使用前面的代码并且不允许这种可能性，那么在异常处理器中将抛出一个未处理的异常。

　　GOTO语句不能用于将控制从执行部分传递到异常部分或反之。

　　已命名异常

　　在PL/SQL块的异常部分只有已命名的异常才能被WHEN子串处理，ORACLE包含了一系列已命名的异常，这些异常都声明在STANDARD包中，这些内建异常在这里就不一一讲述，有兴趣的读者可以查阅有关资料。

 

PL/SQL处理异常不同于其他程序语言的错误管理方法，PL/SQL的异常处理机制与ADA很相似，有一个处理错误的全包含方法。当发生错误时，程序无条件转到异常处理部分，这就要求代码要非常干净并把错误处理部分和程序的其它部分分开。oracle允许声明其他异常条件类型以扩展错误/异常处理。这种扩展使PL/SQL的异常处理非常灵活。

　　当一个运行时错误发生时，称为一个异常被抛出。PL/SQL程序编译时的错误不是能被处理得异常，只有在运行时的异常能被处理。在PL/SQL程序设计中异常的抛出和处理是非常重要的内容。

　　抛出异常

　　由三种方式抛出异常

　　　. 通过PL/SQL运行时引擎

　　　. 使用RAISE语句

　　　. 调用RAISE_APPLICATION_ERROR存储过程

　　当数据库或PL/SQL在运行时发生错误时，一个异常被PL/SQL运行时引擎自动抛出。异常也可以通过RAISE语句抛出

　　RAISE exception_name;

　　显式抛出异常是程序员处理声明的异常的习惯用法，但RAISE不限于声明了的异常，它可以抛出任何任何异常。例如，你希望用TIMEOUT_ON_RESOURCE错误检测新的运行时异常处理器，你只需简单的在程序中使用下面的语句：

　　RAISE TIMEOUT_ON_RESOUCE;

　　下面看一个订单输入系统，当库存小于订单时抛出一个inventory_too_low异常。

 

DECLARE
inventory_too_low EXCEPTION;
---其他声明语句
BEGIN
.
.
IF order_rec.qty>inventory_rec.qty THEN
RAISE inventory_too_low;
END IF
.
.
EXCEPTION
WHEN inventory_too_low THEN
order_rec.staus:='backordered';
replenish_inventory(inventory_nbr=>
inventory_rec.sku,min_amount=>order_rec.qty-inventory_rec.qty);
END;
 

　　这里replenish_inventory是一个触发器。
　
　　处理异常

　　PL/SQL程序块的异常部分包含了程序处理错误的代码，当异常被抛出时，一个异常陷阱就自动发生，程序控制离开执行部分转入异常部分,一旦程序进入异常部分就不能再回到同一块的执行部分。下面是异常部分的一般语法：

EXCEPTION
　WHEN exception_name THEN
　　Code for handing exception_name
　[WHEN another_exception THEN
　　Code for handing another_exception]
　[WHEN others THEN
　　code for handing any other exception.]
 

　　用户必须在独立的WHEN子串中为每个异常设计异常处理代码，WHEN OTHERS子串必须放置在最后面作为缺省处理器处理没有显式处理的异常。当异常发生时，控制转到异常部分，ORACLE查找当前异常相应的WHEN..THEN语句，捕捉异常，THEN之后的代码被执行，如果错误陷阱代码只是退出相应的嵌套块，那么程序将继续执行内部块END后面的语句。如果没有找到相应的异常陷阱，那么将执行WHEN OTHERS。在异常部分WHEN 子串没有数量限制。

EXCEPTION

　WHEN inventory_too_low THEN
　　order_rec.staus:='backordered';
　　replenish_inventory(inventory_nbr=>
　　inventory_rec.sku,min_amount=>order_rec.qty-inventory_rec.qty);
　WHEN discontinued_item THEN
　　--code for discontinued_item processing
　WHEN zero_divide THEN
　　--code for zero_divide
　WHEN OTHERS THEN
　　--code for any other exception
END;
 

　　当异常抛出后，控制无条件转到异常部分，这就意味着控制不能回到异常发生的位置，当异常被处理和解决后，控制返回到上一层执行部分的下一条语句。

BEGIN
　DECLARE
　　bad_credit;
　BEGIN
　　RAISE bad_credit;
　　　--发生异常，控制转向；
　EXCEPTION
　　WHEN bad_credit THEN
　　　dbms_output.put_line('bad_credit');
　　END;

　　--bad_credit异常处理后，控制转到这里
　EXCEPTION
　　WHEN OTHERS THEN
　　　--控制不会从bad_credit异常转到这里
　　　--因为bad_credit已被处理
END;

　　当异常发生时，在块的内部没有该异常处理器时，控制将转到或传播到上一层块的异常处理部分。

BEGIN
　DECLARE ---内部块开始
　　bad_credit;
　BEGIN
　　RAISE bad_credit;
　　　--发生异常，控制转向；
　　EXCEPTION
　　WHEN ZERO_DIVIDE THEN --不能处理bad_credite异常
　　　dbms_output.put_line('divide by zero error');
　　END --结束内部块

　　　--控制不能到达这里，因为异常没有解决；
　　　--异常部分

　　EXCEPTION
　　WHEN OTHERS THEN
　　　--由于bad_credit没有解决，控制将转到这里
END;

　　异常传播

　　没有处理的异常将沿检测异常调用程序传播到外面，当异常被处理并解决或到达程序最外层传播停止。
在声明部分抛出的异常将控制转到上一层的异常部分。

BEGIN
executable statements
BEGIN
today DATE:='SYADATE'; --ERRROR
BEGIN --内部块开始
dbms_output.put_line('this line will not execute');
EXCEPTION
WHEN OTHERS THEN
--异常不会在这里处理
END;--内部块结束

EXCEPTION
WHEN OTHERS THEN
处理异常
END

　　执行部分抛出的异常将首先传递到同一块的异常部分，如果在同一块的异常部分没有处理这个异常的处理器，那么异常将会传播到上一层的异常部分中，一直到最外层。

　　在异常部分抛出的异常将控制转到上一层的异常部分。

　　处理异常将停止异常的传播和解决。有时用户希望在错误发生时，程序仍然能执行一些动作，要达到这个目的，可以把希望执行的动作放在异常处理器中，然后执行不带参数的RAISE语句，RAISE语句将重新抛出出现的异常，允许他继续传播。

DECLARE
order_too_old EXCEPTION;
BEGIN
RAISE order_too_old;
EXCEPTION
WHEN order_too_old THEN
DECLARE
file_handle UTL_FILE.FILE_TYPE;
BEGIN
--open file
file_handle:=UTL_FILE.FOPEN
(location=>'/ora01/app/oracle/admin/test/utlsir'
,filename=>'error.log'
.open_mode=>'W');
--write error stack
UTL_FILE.PUT_LINE(filehandle,
DBMS_UTILITY.FORMAT_ERROR_STACK);
--write the call stack
UTL_FILE.PUT_LINE(filehandle,
DBMS_UTILITY.FORMAT_CALL_STACK);
--close error log
UTL_FILE.FCLOSE(file_handle);
RAISE; --re-raise the exception
END
END

　　如果从FORMAT_XXX_STACK输出一个很大的值，那么使用DBMS_OUTPUT或UTL_FILE显示错误或调用堆的异常部分自身也会抛出异常，这两个堆常规下最多能返回2000字节，但utl_file.put_line被限制在1000字节以内，而dbms_output.put_line限制在512字节内。如果使用前面的代码并且不允许这种可能性，那么在异常处理器中将抛出一个未处理的异常。

　　GOTO语句不能用于将控制从执行部分传递到异常部分或反之。

　　已命名异常

　　在PL/SQL块的异常部分只有已命名的异常才能被WHEN子串处理，ORACLE包含了一系列已命名的异常，这些异常都声明在STANDARD包中，这些内建异常在这里就不一一讲述，有兴趣的读者可以查阅有关资料。