Oracle数据库知识汇总

1、创建表（例：A_id(主键)、A_name、A_age、A_birthday）

Create table 表名（

A_id number(10) not null,

A_name varchar2(20),

A_age number(5),

A_birthday data,

Constraint ZHU_JIAN primary key(A_id)

);

2、插入信息

Insert into 表名 (A_id,A_name,A_age) values (1,' ',22,to_date('2012-2-12','yyyy-mm-dd')) ;

3、删除信息

Delete * from table where A_id=1; //这里的那个from可有可无

张三4、修改信息

Update table 表名 set name='李四' where A_id=22;

5、通过此表创建另一张表

Create table 新表名 as select * from 原表名;（注意，此处必须用as，不能用is）

6、查询所有信息

Select * from 表名（或select t.* from 表名 t）；

7、通过A_id查询A_name

Select A_name from 表名 where A_id = 2;

8、清空信息

truncate table 表名

9、删除表

Drop table 表名

10、算数操作(包括+、-、*、/等操作)

Select A_id*2 as New_id from 表名 where A_name='张三';//此处的AS有起别名的作用

11、比较符操作(包括 =、!=、<、>、<=、>=、BETWEEN…AND、IN、LIKE 和 IS NULL等)

Select A_name from 表名 where

A_birthday>=to_date('2009-12-2','yyyy-mm-dd');

Select A_name from 表名 where A_age <> 12;// <>相当于'不等于'

Select A_name from 表名 where A_name like '张%'; //前面带'张'的

Select A_name from 表名 where A_name like '%三'; //后面带'三'的

Select A_name from 表名 where A_name like '%张%'; //含有关键字'张'的

Select A_name from 表名 where A_age>=12 and A_age<=34;（等价于：Select A_name from 表名 where A_age between 12 and 34;）

Select A_name from 表名 where A_age is null;

Select A_name from 表名 where A_age in (12,15,13……); //就是在这其中取

12、逻辑操作符（and、or、not）

Select A_name from 表名 where A_age>=12 and A_age<=34;

Select A_name from 表名 where A_age>=12 or A_age<=34;

Select A_name from 表名 where A_age is not null;

13、集合操作符（union、union all、intersect、minus）

UNION 操作符返回两个表的集合且不含相同的行（注意和下面的union Select * from 表1

union

Select * from 表2；

UNION ALL操作符返回两个表的集合包含相同的行

Select * from 表1

Union all

Select * from 表2；

all比较）

INTERSECT 操作符只返回两个表的公共行。

Select * from 表1

Intersect

Select * from 表2；

MINUS 操作符返回从第一个表结果中排除第二个表中出现的行。

Select * from 表1

minus

Select * from 表2；

14、连接操作符（连接操作符用于将多个字符串或数据值合并成一个字符串）

Select （A_name|| ' 地址是' ||A_id ||A_age ||A-birthday） from 表名；

效果：

15、操作符优先级（从上往下依次降低）

算术操作符（+、-、*、/、等）----------------最高优先级

连接操作符（ || ）

比较操作符（包括 =、!=、<、>、<=、>=、BETWEEN…AND、IN、LIKE 和 IS NULL等）

NOT逻辑操作符

AND逻辑操作符

OR逻辑操作符--------------------最低优先级

16、字符串函数（字符函数接受字符输入并返回字符或数值）

17、常用的转换函数

To_char 将一个数字转换成ASCII值

select to_char (sysdate,'YYYY\"年\"fmMM\"月\"fmDD\"日\" HH24:MI:SS') 表名;

To_number 将一个字符串转化成数字

Select to_number('1010') from 表名；

To_date 转化为日期格式

Select to_date('2012-3-11' , 'yyyy-mm-dd');

18、Oracle 服务器由Oracle 数据库和 Oracle 实例组成

from Oracle 实例由系统全局区内存结构和用于管理数据库的后台进程组成

Oracle 中用于访问数据库的主要查询工具有 SQL*Plus、iSQL*Plus 和 PL/SQL

Oracle 企业管理器是用于管理、诊断和调整多个数据库的工具

Oracle 中的 SYSTEM 用户和 SYS 用户具有管理权限，而 SCOTT 用户只有基本的权限

Oracle 服务在 Windows 注册表中注册，并由 Windows 操作系统管理

19、SQL 支持下列类别的命令：

数据定义语言（DDL）

数据操纵语言（DML）

事务控制语言（TCL）

数据控制语言（DCL）

20、常用的数据类型

1）、字符数据类型：

当需要固定长度的字符串时，使用 CHAR 数据类型。

CHAR 数据类型存储字母数字值。（1 到 2000 个字节）

VARCHAR2数据类型支持可变长度字符串、数据类型存储字母数字值（大小1至4000字节）

LONG 数据类型存储可变长度字符数据（数据类型最多能存储 2GB）

2）、数值数据类型

可以存储整数、浮点数和实数数据操纵语言（DML）

最高精度为 38 位数据控制语言（DCL）

NUMBER [( p[, s])]

P表示精度，S表示小数点的位数

3）、日期时间类型有：

DATE - 存储日期和时间部分，精确到整个的秒

TIMESTAMP - 存储日期、时间和时区信息，秒值精确到小数点后6位.

21、数据定义语言

数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象

用于操纵表结构的数据定义语言命令有：

CREATE TABLE（创建一张表）

ALTER TABLE（更新表）

TRUNCATE TABLE（清空表）

DROP TABLE（删除表）

22、数据操纵语言

数据操纵语言用于检索、插入和修改数据

数据操纵语言是最常见的SQL命令

数据操纵语言命令包括：

SELECT（查询信息）

INSERT（插入信息）

UPDATE（更新信息）

DELETE（删除信息）

23、DISTINCT关键字有去除重复的行

24、事务控制语言

事务是最小的工作单元，作为一个整体进行工作

保证事务的整体成功或失败，称为事务控制

用于事务控制的语句有：

COMMIT - 提交并结束事务处理

ROLLBACK - 撤销事务中已完成的工作

SAVEPOINT – 标记事务中可以回滚的点

25、数据控制语言

数据控制语言为用户提供权限控制命令

用于权限控制的命令有：

GRANT 授予权限

REVOKE 撤销已授予的权限

26、分组函数

AVG 求平均值

例、SELECT AVG(re_level) FROM itemfile WHERE p_category='accessories';

MAX 求最大值

SELECT MAX(max_level) FROM itemfile;

MIN 求最小值

SELECT MIN(max_level) FROM itemfile;

COUNT 求数目

SELECT COUNT(itemrate) FROM itemfile;

SUM 求和

SELECT SUM(itemrate*max_level) FROM itemfile;

27、GROUP BY子句

用于将信息划分为更小的组

每一组行返回针对该组的单个结果

HAVING子句（效率低，一般不建议用）

用于指定 GROUP BY 子句检索行的条件

例、SELECT p_category, MAX(itemrate) FROM itemfile

GROUP BY p_category

HAVING p_category NOT IN ('accessories');

28、小结

SQL 是通用的数据库语言

SQL 命令可分为数据定义语言、数据操纵语言、事务控制语言和数据控制语言

Oracle 支持的数据类型包括字符、数值、日期时间、RAW 和 LOB 等

SQL 支持的操作符包括算术、比较、逻辑、集合和连接操作符

SQL 函数可大致分为单行函数、聚合函数和分析函数

数据操纵语言用于查询和修改表中的数据

事物的4个特性：

1）原子性：指整个数据库事务是不可分割的工作单位。只有使据库中所有的操作执行成功，才算整个事务成功；事务中任何一个SQL语句执行失败，那么已经执行成功的SQL语句也必须撤销，数据库状态应该退回到执行事务前的状 态。

2）一致性：指数据库事务不能破坏关系数据的完成性以及业务逻辑上的一致性。例如对银行转帐事务，不管事务成功还是失败，应该保证事务结束后A帐号与B帐号加起来还是那些钱。

3）隔离性：指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。这个也是后面咱们研究的一个重点。

4）持久性：指的是只要事务成功结束，它对数据库所做的更新就必须永久保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。

第三章 锁和表分区

29、锁的基本概念

锁是数据库用来控制共享资源并发访问的机制。

锁用于保护正在被修改的数据

直到提交或回滚了事务之后，其他用户才可以更新数据

30、

并行性 允许多个的优点用户访问同一数据

一致性 一次只允许一个用户修改数据

完整性 为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改将反映给所有用户

31、锁的类型

行级锁（是一种排他锁，防止其他事务修改此行，其他用户可以访问除被锁定的行以外的行

）：

基本语法：

SELECT … FOR UPDATE语法:

SELECT … FOR UPDATE [OF columns]

[WAIT n | NOWAIT];

注：以下情况Oracle会自动应用行级锁：

INSERT

UPDATE

DELETE

SELECT … FOR UPDATE（语句允许用户一次锁定多条记录进行更新）

select * from userinfo for update;

这时候可以锁定选中的所有行

如果已经被锁定，就不用等待

select * from userinfo for update nowait;

如果已经被锁定，更新的时候等待5秒

select * from userinfo for update wait 5;

表级锁（锁定整个表，其他用户对表的访问）：

（1）基本语法：LOCK TABLE table_name IN mode MODE;

（2）表级锁包括：行共享、行排他、共享锁、排他锁、共享排他锁。

行共享：允许用户进行任何操作，禁止排他锁

lock table userinfo in row share mode;

行排他：允许用户进行任何操作，禁止排他锁和共享锁

lock table userinfo in row exclusive mode;

共享锁：其他用户只能看，不能修改，多个用户可以同时在同一个表上应用此锁

lock table userinfo in share mode;

共享行排他：比共享锁更多的，禁止使用共享锁及更高的锁

lock table userinfo in share row exclusive mode;

排他锁：其他用户只能看，不能修改，不能加其他锁

lock table userinfo in exclusive mode;

32、死锁的成因

当两个事务相互等待对方释放资源时，就会形成死锁（注：Oracle会自动检测死锁，并通过结束其中的一个事务来解决死锁）

33、表分区

需要掌握理解的概念：

允许用户将一个表分成多个分区

用户可以执行查询，只访问表中的特定分区

将不同的分区存储在不同的磁盘，提高访问性能和安全性

可以地备份和恢复每个分区

34、分区的方法（范围分区、散列分区、列表分区、复合分区）

范围分区：

（1）基本概念：以表中的一个列或一组列的值的范围分区

（2）基本语法：

PARTITION BY RANGE (column_name)

(

PARTITION part1 VALUE LESS THAN(range1),

PARTITION part2 VALUE LESS THAN(range2),

……

[PARTITION partN VALUE LESS THAN(MAXVALUE)]

);

（3）实例：

例一：

SQL> CREATE TABLE Sales

(

Product_ID varchar2 (5),

Sales_Cost number (10)

)

PARTITION BY RANGE (Sales_Cost)

(

PARTITION P1 VALUES LESS THAN (1000), // 小于1000的分区

PARTITION P2 VALUES LESS THAN (2000), // 大于等于1000小于2000的分区

PARTITION P3 VALUES LESS THAN (3000) // 大于等于2000小于3000的分区

);

例二：

SQL> CREATE TABLE SALES2 (

PRODUCT_ID VARCHAR2(5),

SALES_DATE DATE NOT NULL,

SALES_COST NUMBER(10))

PARTITION BY RANGE (SALES_DATE)

(

PARTITION P1 VALUES LESS THAN (DATE PARTITION P2 VALUES LESS THAN (DATE 2003-01-01’2004-01-01’),

),

‘‘PARTITION P3 VALUES LESS THAN (MAXVALUE) //2004-01-01 到 最大值 的分区

);

散列分区：

（1）基本概念：

允许用户对不具有逻辑范围的数据进行分区

通过在分区键上执行HASH函数决定存储的分区

将数据平均地分布到不同的分区

（2）基本语法：

PARTITION BY HASH (column_name)

PARTITIONS number_of_partitions;

或

PARTITION BY HASH (column_name)

( PARTITION part1 [TABLESPACE tbs1],

PARTITION part2 [TABLESPACE tbs2],

……

PARTITION partN [TABLESPACE tbsN]

);

（3）示例：

示例一：

示例二：

SQL> CREATE TABLE EMPLOYEE

(

EMP_ID NUMBER(4),

EMP_NAME VARCHAR2(14),

EMP_ADDRESS VARCHAR2(15),

DEPARTMENT VARCHAR2(10)

)

PARTITION BY HASH (DEPARTMENT)

PARTITIONS 4;

列表分区：

（1）基本概念：允许用户将不相关的数据组织在一起

（2）基本语法：

PARTITION BY LIST (column_name)

(

PARTITION part1 VALUES (values_list1),

PARTITION part2 VALUES (values_list2),

...

PARTITION partN VALUES (DEFAULT)

);

（3）示例：

复合分区：

（1）基本概念：范围分区与散列分区或列表分区的组合

（2）基本语法：

PARTITION BY RANGE (column_name1)

SUBPARTITION BY HASH (column_name2)

SUBPARTITIONS number_of_partitions

(

PARTITION part1 VALUE LESS THAN(range1),

PARTITION part2 VALUE LESS THAN(range2),

...

PARTITION partN VALUE LESS THAN(MAXVALUE)

);

（3）示例：

35、操纵已分区的表

在已分区的表中插入数据与操作普通表完全相同，Oracle会自动将数据保存到对应的分 区。

INSERT INTO SALES3 VALUES (‘P001’, ’02-3月-2001', 2000);

INSERT INTO SALES3 VALUES (‘P002’, ’10-5月-2001', 2508);

INSERT INTO SALES3 VALUES (‘P003’, ’05-7月-2001', 780);

INSERT INTO SALES3 VALUES (‘P004’, ’12-9月-2001', 1080);

查询、修改和删除分区表时可以显式指定要操作的分区 。

SELECT * FROM SALES3 PARTITION (P3); //查询

DELETE FROM SALES3 PARTITION (P2); //删除

36、分区维护操作

（1）分区维护操作修改已分区表的分区。

计划事件 － 定期删除最旧的分区

非计划事件 － 解决应用程序或系统问题

（2）分区维护的类型：

（3）分区维护操作有：

添加分区

删除分区

截断分区

合并分区

拆分分区

1）添加分区：在最后一个分区之后添加新分区

SQL> ALTER TABLE SALES

ADD PARTITION P4 VALUES LESS THAN (4000);

2）删除分区：删除一个指定的分区，分区的数据也随之删除

SQL> ALTER TABLE SALES DROP PARTITION P4;

3）截断分区：删除指定分区中的所有记录

SQL> ALTER TABLE SALES TRUNCATE PARTITION P3;

4）合并分区：将范围分区或复合分区的两个相邻分区连接起来

SQL> ALTER TABLE SALES

MERGE PARTITIONS S1, S2 INTO PARTITION S2;

5）拆分分区：将一个大分区中的记录拆分到两个分区中

SQL> ALTER TABLE SALES SPLIT PARTITION P2 AT (1500)

INTO (PARTITION P21 , PARTITION P22);

36、小结：

锁用于保护多用户环境下被修改的数据

锁分为两种级别，即行级锁和表级锁

表分区允许将一个表划分成几部分，以改善大型应用系统的性能

分区方法包括范围分区、散列分区、复合分区和列表分区

分区维护操作包括添加、删除、截断、合并和拆分分区

第四章 数据库对象

1、Oracle数据库对象（又称模式对象）：是逻辑结构的集合，最基本的数据库对象是表，

包括：同义词、序列、视图、索引。

（1）同义词：是现有对象的一个别名。

简化SQL语句

隐藏对象的名称和所有者

提供对对象的公共访问

同义词共有两种类型：

1）公有同义词：可被所有的数据库用户访问。

2）私有同义词：只能在其模式内访问，且不能与当前模式的对象同名。

3）

4）创建或替换现有的同义词

CREATE OR REPLACE SYNONYM emp_syn FOR SCOTT.emp;

5）删除同义词

SQL> DROP SYNONYM emp; //删除私有同义词

SQL> DROP PUBLIC SYNONYM emp_syn; //删除公有同义词

（2）序列：序列是用于生成唯一、连续序号的对象

序列可以是升序的，也可以是降序的

使用CREATE SEQUENCE语句创建序列

1）创建序列（指定内存中预先分配的序号数）：

SQL> CREATE SEQUENCE toys_seq

START WITH 10

INCREMENT BY 10

MAXVALUE 2000

MINVALUE 10

NOCYCLE

CACHE 10;

2）访问序列：

通过序列的伪列来访问序列的值

NEXTVAL 返回序列的下一个值

CURRVAL 返回序列的当前值

①指定序列的下一个值：

SQL> INSERT INTO toys (toyid, toyname, toyprice)

VALUES ( toys_seq.NEXTVAL, ‘TWENTY’, 25);

SQL> INSERT INTO toys (toyid, toyname, toyprice)

VALUES ( toys_seq.NEXTVAL, ’MAGIC PENCIL’, 75);

②检索当前序列

SQL> SELECT toys_seq.CURRVAL FROM dual;

3）更改删除序列

①使用ALTER SEQUENCE语句修改序列，不能更改序列的START WITH 参数

SQL> ALTER SEQUENCE toys_seq MAXVALUE 5000 CYCLE;

②使用DROP SEQUENCE语句删除序列

SQL> DROP SEQUENCE toys_seq;

（3）视图：

视图以经过定制的方式显示来自一个或多个表的数据

视图可以视为“虚拟表”或“存储的查询”

创建视图所依据的表称为“基表”

1）视图的优点有：

①提供了另外一种级别的表安全性

②隐藏的数据的复杂性

③简化的用户的SQL命令

④隔离基表结构的改变

⑤通过重命名列，从另一个角度提供数据

2）创建视图基本语法：

CREATE [OR REPLACE] [FORCE] VIEW

view_name [(alias[, alias]...)]

AS select_statement

[WITH CHECK OPTION]

[WITH READ ONLY];

3）创建视图

①使用 WITH CHECK OPTION 选项创建视图

CREATE OR REPLACE VIEW pause_view AS

SELECT * FROM order_master WHERE ostatus = 'p'

WITH CHECK OPTION CONSTRAINT chk_pv;

②使用 ORDER BY 子句创建视图

CREATE OR REPLACE VIEW ord_ven AS

SELECT * FROM vendor_master ORDER BY venname;

③创建带有错误的视图

CREATE FORCE VIEW ven AS

SELECT * FROM venmaster;

4）联接视图

5）视图上的DML语句

①在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE

②视图上的DML语句有如下：

只能修改一个底层的基表。

如果修改违反了基表的约束条件，则无法更新视图。

如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP

BY 子 句，则将无法更新视图。

如果视图包含伪列或表达式，则将无法更新视图。

6）键保留表：其中的一个属性既是此表中的主键，又是联络结果中的主键，这样的表是键保留表。

（4）视图：

1）图中的函数：

视图中可以使用单行函数、分组函数和表达式

CREATE VIEW item_view AS

SELECT itemcode, LOWER(itemdesc) item_desc

FROM itemfile;

2）使用DROP VIEW语句删除视图

SQL> DROP VIEW toys_view;

（5）索引：

1）基本概念：

索引是与表相关的一个可选结构

用以提高 SQL 语句执行的性能

减少磁盘I/O

使用 CREATE INDEX 语句创建索引

在逻辑上和物理上都于表的数据

Oracle 自动维护索引

2）创建标准索引

SQL> CREATE INDEX item_index ON itemfile (itemcode)

TABLESPACE index_tbs;

重建索引

SQL> ALTER INDEX item_index REBUILD;

删除索引

SQL> DROP INDEX item_index;

3）索引除标准类型外的其它类型：唯一索引、位图索引、组合索引、基于函数的索引、反向键索引。

①唯一索引：

唯一索引确保在定义索引的列中没有重复值

Oracle 自动在表的主键列上创建唯一索引

使用CREATE UNIQUE INDEX语句创建唯一索引

SQL> CREATE UNIQUE INDEX item_index

ON itemfile (itemcode);

②组合索引：

组合索引是在表的多个列上创建的索引

索引中列的顺序是任意的

如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度

SQL> CREATE INDEX comp_index

ON itemfile(p_category, itemrate);

③反向键索引：

反向键索引反转索引列键值的每个字节

通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上

创建索引时使用REVERSE关键字

SQL> CREATE INDEX rev_index

ON itemfile (itemcode) REVERSE;

SQL> ALTER INDEX rev_index REBUID NOREVERSE;

④位图索引：

位图索引适合创建在低基数列上

位图索引不直接存储ROWID，而是存储字节位到ROWID的映射

减少响应时间

节省空间占用

SQL> CREATE BITMAP INDEX bit_index

ON order_master (orderno);

⑤索引组织表：

索引组织表的数据存储在与其关联的索引中

索引中存储的是行的实际数据，而不是ROWID

基于主键访问数据

CREATE TABLE 命令与 ORGANIZATION INDEX 子句一起用于创建索引组织表

SQL> CREATE TABLE ind_org_tab (

vencode NUMBER(4) PRIMARY KEY,

venname VARCHAR2(20)

)

ORGANIZATION INDEX;

注：普通表与索引表的比较

⑥基于函数的索引：

基于一个或多个列上的函数或表达式创建的索引

表达式中不能出现聚合函数

不能在LOB类型的列上创建

创建时必须具有 QUERY REWRITE 权限

SQL> CREATE INDEX lowercase_idx

ON toys (LOWER(toyname));

SQL> SELECT toyid FROM toys

WHERE LOWER(toyname)='doll';

（4）索引中的分区

1）可以将索引存储在不同的分区中

2）与分区有关的索引有三种类型：

①局部分区索引：在分区表上创建的索引，在每个表分区上创建的索引，索引

的分区范围与表一致

②全局分区索引：在分区表或非分区表上创建的索引，索引单独指定分区的范围，

与表的分区范围或是否分区无关

③全局非分区索引：在分区表上创建的全局普通索引，索引没有被分区

（5）获取索引的信息

与索引有关的数据字典视图有：

USER_INDEXES － 用户创建的索引的信息

USER_IND_PARTITIONS － 用户创建的分区索引的信息

USER_IND_COLUMNS － 与索引相关的表列的信息

SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME

FROM USER_IND_COLUMNS

ORDER BY INDEX_NAME, COLUMN_POSITION;

2、小结：

同义词是现有数据库对象的别名

序列用于生成唯一、连续的序号

视图是基于一个或多个表的虚拟表

索引是与表相关的一个可选结构，用于提高 SQL 语句执行的性能

索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引

索引组织表基于主键访问数据

第五章 使用PL/SQL

1、基本概念：

PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言

PL/SQL 是对 SQL 的扩展

支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构

可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑

与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性

2、PL/SQL优点：

1）优点一：

①支持 SQL，在 PL/SQL 中可以使用：

数据操纵命令

事务控制命令

游标控制

②SQL 函数和 SQL 运算符

③支持面向对象编程 (OOP)

④可移植性，可运行在任何操作系统和平台上的Oralce 数据库

⑤更佳的性能，PL/SQL 经过编译执行

2）优点二：

①与 SQL 紧密集成，简化数据处理。

②支持所有 SQL 数据类型

③支持 NULL 值

④支持 %TYPE 和 %ROWTYPE 属性类型

⑤安全性，可以通过存储过程用户对数据的访问

3、1）PL/SQL 块是构成 PL/SQL 程序的基本单元

2）将逻辑上相关的声明和语句组合在一起

3）PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分

[DECLARE

declarations] //声明部分定义变量、游标和自定义异常

BEGIN

executable statements //包含 SQL 和 PL/SQL 语句的可执行部分

[EXCEPTION

handlers] //指定出现错误时需要执行的操作

END;

例：DECLARE

qty_on_hand NUMBER(5);

BEGIN

SELECT quantity INTO qty_on_hand

FROM Products

WHERE product = '芭比娃娃'

FOR UPDATE OF quantity;

IF qty_on_hand > 0 THEN

UPDATE Products SET quantity = quantity + 1

WHERE product = '芭比娃娃';

INSERT INTO purchase_record

VALUES ('已购买芭比娃娃', SYSDATE);

END IF;

COMMIT;

EXCEPTION /* 异常处理语句 */

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('出错:'|| SQLERRM);

END;

4、 1）PL/SQL 块中可以使用变量和常量

2）在声明部分声明，使用前必须先声明

3）声明时必须指定数据类型，每行声明一个标识符

4）在可执行部分的 SQL 语句和过程语句中使用

5）声明变量和常量的语法：

identifier [CONSTANT] datatype [NOT NULL]

[:= | DEFAULT expr];

5、1）给变量赋值有两种方法：

使用赋值语句 :=

使用 SELECT INTO 语句

2）例：

DECLARE

icode VARCHAR2(6);

p_catg VARCHAR2(20);

p_rate NUMBER;

c_rate CONSTANT NUMBER := 0.10;

BEGIN

...

icode := 'i205';

SELECT p_category, itemrate * c_rate

INTO p_catg, p_rate

FROM itemfile WHERE itemcode = icode;

……

END;

6、（1）PL/SQL 支持的内置数据类型

1）标量类型

①数字

②字符

③布尔

④日期时间

2）LOB类型

①NCLOB

②CLOB

③BLOB

④BFILE

3）属性类型

①%TYPE //提供某个变量或数据库表列的数据类型

②%ROWTYPE //提供表示表中一行的记录类型

（2）数字数据类型

1）BINARY_INTEGER：存储有符号整数，所需存储空间少于NUMBER类型值

①SIGNTYPE

②POSITIVEN

③POSITIVE

④NATURALLN

⑤NATURAL

2）NUMBER：存储整数、实数和浮点数

①REAL

②INTEGER

③FLOAT

④DECIMAL

3）PLS_INTEGER：存储有符号整数，可使算术计算快速而有效

（3）字符数据类型

（4）日期时间和布尔数据类型

1）日期时间类型

存储日期和时间数据

常用的两种日期时间类型：

DATE

TIMESTAMP

2）布尔数据类型

此类别只有一种类型，即BOOLEAN类型

用于存储逻辑值(TRUE、FALSE和NULL)

不能向数据库中插入BOOLEAN数据

不能将列值保存到BOOLEAN变量中

只能对BOOLEAN变量执行逻辑操作

（5）LOB 数据类型

1）用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。

2）LOB 数据类型可存储最大 4GB的数据。

3）LOB 类型包括：

① BLOB 将大型二进制对象存储在数据库中

② CLOB 将大型字符数据存储在数据库中

③ NCLOB 存储大型UNICODE字符数据

④ BFILE 将大型二进制对象存储在操作系统文件中

4）LOB 类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置

DBMS_LOB程序包用于操纵 LOB 数据

SET SERVEROUTPUT ON

DECLARE

clob_var CLOB;

amount INTEGER;

offset INTEGER;

output_var VARCHAR2(100);

BEGIN

SELECT chapter_text INTO clob_var //从表中选择 CLOB 定位符到 clob_var变量中

FROM my_book_text

WHERE chapter_id=5;

amount := 24; -- 要读取的字符数

offset := 1; -- 起始位置

DBMS_LOB.READ(clob_var,amount,offset,output_var);//从CLOB数据中读取24

个字 符存储到 output_var 变量中

DBMS_OUTPUT.PUT_LINE(output_var);//显示读到的信息

END;

(6) 属性数据类型

1）用于引用数据库列的数据类型，以及表示表中一行的记录类型

2）属性类型有两种：

%TYPE - 引用变量和数据库列的数据类型

%ROWTYPE - 提供表示表中一行的记录类型

3）使用属性类型的优点：

不需要知道被引用的表列的具体类型

如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变

icode itemfile.itemcode%TYPE;

emp_rec scott.emp%ROWTYPE;

（7）逻辑比较

1）布尔表达式由组成布尔表达式的结果为TRUE、FALSE或NULL，通常由逻辑运算

符 AND、OR和NOT连接

2）布尔表达式有三种类型：

数字布尔型

字符布尔型

日期布尔型

（6）控制结构

PL/SQL 支持的流程控制结构：

1）条件控制

IF 语句（IF 语句根据条件执行一系列语句，有三种形式：IF-

THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF-）

CASE 语句（CASE 语句用于根据单个变量或表达式与多个值进行比较

执行 CASE 语句前，先计算选择器的值）

2）循环控制（循环控制用于重复执行一系列语句、循环控制语句包括：

LOOP、EXIT 和 EXIT WHEN）

LOOP 循环 （无条件循环）

LOOP

sequence_of_statements

END LOOP;

WHILE 循环 （根据条件循环）

WHILE condition LOOP

sequence_of_statements

END LOOP;

FOR 循环（循环固定的次数）

FOR counter IN [REVERSE] value1..value2

LOOP

sequence_of_statements

END LOOP;

3）顺序控制

GOTO 语句（无条件地转到标签指定的语句）

NULL 语句 （什么也不做的空语句，放到最后面可协助执行退出）

例一：DECLARE

icode VARCHAR2(4);

irate NUMBER;

BEGIN

icode := 'i203';

SELECT itemrate INTO irate FROM itemfile

WHERE itemcode = icode;

IF irate > 200 THEN

UPDATE itemfile SET itemrate = itemrate - 200

WHERE itemcode = icode;

ELSE

UPDATE itemfile SET itemrate = itemrate - 50

WHERE itemcode = icode;

END IF;

DBMS_OUTPUT.PUT_LINE('itemrate='|| irate);

END;

例二：BEGIN

CASE ‘&grade’

WHEN ’A’ THEN DBMS_OUTPUT.PUT_LINE(’优异’);

WHEN ’B’ THEN DBMS_OUTPUT.PUT_LINE (优秀’);

WHEN ’C’ THEN DBMS_OUTPUT.PUT_LINE (’良好’);

WHEN ’D’ THEN DBMS_OUTPUT.PUT_LINE (’一般’);

WHEN ’F’ THEN DBMS_OUTPUT.PUT_LINE (’较差’);

ELSE DBMS_OUTPUT.PUT_LINE (’没有此成绩’);

END CASE;

END;

（7）动态SQL

动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句

编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分 析并执行DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过

动态 SQL 来执行

执行动态 SQL 的语法：

EXECUTE IMMEDIATE dynamic_sql_string

[INTO define_variable_list]

[USING bind_argument_list];

例：DECLARE

sql_stmt VARCHAR2(200);

emp_id NUMBER(4) := 7566;

emp_rec emp%ROWTYPE;

BEGIN

EXECUTE IMMEDIATE

'CREATE TABLE bonus1 (id NUMBER, amt NUMBER)';

sql_stmt := 'SELECT * FROM emp WHERE empno = :id';

EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

END;

4、错误处理

（1）基本概念：

1）在运行程序时出现的错误叫做异常

2）发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分

3）异常有两种类型：

预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统时隐式引 发。

用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异 通 过 RAISE 语句显式引发。

常

（2）处理预定义异常

处理用户定义异常

例：DECLARE

invalidCATEGORY EXCEPTION;

category VARCHAR2(10);

BEGIN

category := '&Category';

IF category NOT IN ('附件','顶盖','备件') THEN

RAISE invalidCATEGORY;

ELSE

DBMS_OUTPUT.PUT_LINE('您输入的类别是'|| category);

END IF;

EXCEPTION

WHEN invalidCATEGORY THEN

DBMS_OUTPUT.PUT_LINE('无法识别该类别');

END;

5、获取索引信息

与索引有关的数据字典视图有：

USER_INDEXES － 用户创建的索引的信息

USER_IND_PARTITIONS － 用户创建的分区索引的信息

USER_IND_COLUMNS － 与索引相关的表列的信息

SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME

FROM USER_IND_COLUMNS

ORDER BY INDEX_NAME, COLUMN_POSITION;

6、小结：PL/SQL 是一种可移植的高性能事务处理语言

PL/SQL 引擎驻留在 Oracle 服务器中

PL/SQL 块由声明部分、可执行部分和异常处理部分组成

PL/SQL 数据类型包括标量数据类型、LOB 数据类型和属性类型

控制结构包括条件控制、循环控制和顺序控制

PL/SQL 支持动态 SQL

运行时出现的错误叫做异常

异常可以分为预定义异常和用户定义的异常

第六章 游标管理

1、游标分为：

隐式游标：在PL/SQL中使用DML语句时自动创建隐式游标。

显示游标：显式游标在 PL/SQL 块的声明部分定义查询，该查询可以返回多行。

REF游标：REF 游标用于处理运行时才能确定的动态 SQL 查询的结果。

2、隐式游标自动声明、打开和关闭，其名为 SQL

通过检查隐式游标的属性可以获得最近执行的DML 语句的信息

隐式游标的属性有：

%FOUND – SQL 语句影响了一行或多行时为 TRUE

%NOTFOUND – SQL 语句没有影响任何行时为TRUE

%ROWCOUNT – SQL 语句影响的行数

%ISOPEN - 游标是否打开，始终为FALSE

例一：只有在 DML 语句影响一行或多行时，才返回 True

SQL> SET SERVEROUTPUT ON

SQL> BEGIN

UPDATE toys SET toyprice=270

WHERE toyid= 'P005';

IF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE(‘表已更新');

END IF;

END;

例二：返回 DML 语句影响的行数

SQL> SET SERVEROUTPUT ON

SQL> BEGIN

UPDATE vendor_master

SET venname= 'Rob Mathew'

WHERE vencode='V004';

DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT);

END;

3、显式游标

允许使用游标删除或更新活动集中的行

声明游标时必须使用 SELECT … FOR UPDATE语句

CURSOR IS

SELECT statement FOR UPDATE;

---------------------------------------更新的语法

UPDATE

SET

WHERE CURRENT OF

-------------------------------------删除的语法

DELETE FROM

WHERE CURRENT OF

例一：

SQL>SET SERVER OUTPUT ON

SQL>DECLARE

my_toy_price toys.toyprice%TYPE;

CURSOR toy_cur IS

SELECT toyprice FROM toys

WHERE toyprice<250;

BEGIN

OPEN toy_cur;

LOOP

FETCH toy_cur INTO my_toy_price;

EXIT WHEN toy_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE

('TOYPRICE=:玩具单价=：'||my_toy_price);

END LOOP;

CLOSE toy_cur;

END;

例二：SQL> SET SERVEROUTPUT ON

SQL> DECLARE

desig VARCHAR2(20);

emp_code VARCHAR2(5);

empnm VARCHAR2(20);

CURSOR emp_cur(desigparam VARCHAR2) IS

SELECT empno, ename FROM employee

WHERE designation=desig;

BEGIN

desig:= '&desig';

OPEN emp_cur(desig);

LOOP

FETCH emp_cur INTO emp_code,empnm;

EXIT WHEN emp_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(emp_code||' '||empnm);

END LOOP;

CLOSE emp_cur;

END;

例三：SQL> SET SERVEROUTPUT ON

SQL> DECLARE

new_price NUMBER;

CURSOR cur_toy IS

SELECT toyprice FROM toys WHERE toyprice<100

FOR UPDATE OF toyprice;

BEGIN

OPEN cur_toy;

LOOP

FETCH cur_toy INTO new_price;

EXIT WHEN cur_toy%NOTFOUND;

UPDATE toys

SET toyprice = 1.1*new_price

WHERE CURRENT OF cur_toy;

END LOOP;

CLOSE cur_toy;

COMMIT;

END;

4、循环游标

循环游标用于简化游标处理代码

当用户需要从游标中提取所有记录时使用

循环游标的语法如下：

FOR IN

LOOP

END LOOP;

例一：

SQL> SET SERVER OUTPUT ON

SQL> DECLARE

CURSOR mytoy_cur IS

SELECT toyid, toyname, toyprice

FROM toys;

BEGIN

FOR toy_rec IN mytoy_cur

LOOP

DBMS_OUTPUT.PUT_LINE(

‘玩具编号：'||' ' ||toy_rec.toyid||' '

||‘玩具名称：'||' '||toy_rec.toyname||' '

||‘玩具单价：'||' '||toy_rec.toyprice);

END LOOP;

END;

5、REF游标

REF 游标和游标变量用于处理运行时动态执行的 SQL 查询

创建游标变量需要两个步骤：

声明 REF 游标类型

声明 REF 游标类型的变量

用于声明 REF 游标类型的语法为：

TYPE IS REF CURSOR

[RETURN ];

打开游标变量的语法如下：

OPEN cursor_name FOR select_statement;

声明强类型的 REF 游标(必须有返回值)

TYPE my_curtype IS REF CURSOR

RETURN stud_det%ROWTYPE;

order_cur my_curtype;

声明弱类型的 REF 游标

TYPE my_ctype IS REF CURSOR;

stud_cur my_ctype;

例一：SQL> DECLARE

TYPE toys_curtype IS REF CURSOR

RETURN toys%ROWTYPE; // 声明REF游标类型

toys_curvar toys_curtype; //声明游标变量

toys_rec toys%ROWTYPE; //声明游标变量

BEGIN

OPEN toys_curvar FOR

SELECT * FROM toys;

FETCH toys_curvar INTO toys_rec;

...

CLOSE toys_curvar;

END;

6、小结：

游标用于处理查询结果集中的数据

游标类型有：隐式游标、显式游标和 REF 游标

隐式游标由 PL/SQL 自动定义、打开和关闭

显式游标用于处理返回多行的查询

显式游标可以删除和更新活动集中的行

要处理结果集中所有记录时，可使用循环游标

在声明 REF 游标时，不需要将 SELECT 语句与 其关联

第七章 子程序和程序包

1、子程序

1）子程序的各个部分：

声明部分

可执行部分

异常处理部分(可选)

2）子程序的分类：

过程 － 执行某些操作

函数 － 执行操作并返回值

2、子程序的优点：

模块化：将程序分解为逻辑模块

可重用性：可以被任意数目的程序调用

可维护性：简化维护操作

安全性：通过设置权限，使数据更安全

3、创建过程的语法：

CREATE [OR REPLACE] PROCEDURE

[()]

IS|AS

//创建过程，可指定运行过程需传递的参数

BEGIN

//包括在过程中要执行的语句

[EXCEPTION

] //处理异常

END;

例：CREATE OR REPLACE PROCEDURE

find_emp(emp_no NUMBER)

AS

empname VARCHAR2(20);

BEGIN

SELECT ename INTO empname

FROM EMP WHERE empno = emp_no;

DBMS_OUTPUT.PUT_LINE('雇员姓名是 '|| empname);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE ('雇员编号未找到');

END find_emp;

4、创建过程的三种模式：

过程参数的三种模式：

IN

——用于接受调用程序的值

——默认的参数模式

OUT

——用于向调用程序返回值

IN OUT

——用于接受调用程序的值，并向调用程序返回更新的值

5、执行过程的语法：

EXECUTE procedure_name(parameters_list);

例：SQL> CREATE OR REPLACE PROCEDURE

itemdesc(item_code IN VARCHAR2)

IS

v_itemdesc VARCHAR2(5);

BEGIN

SELECT itemdesc INTO v_itemdesc

FROM itemfile

WHERE itemcode = item_code;

DBMS_OUTPUT.PUT_LINE(item_code||

'项目的说明为'||v_itemdesc);

END;

/

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE itemdesc('i201');

6、将过程的执行权限授予其他用户：

SQL> GRANT EXECUTE ON find_emp TO MARTIN;

SQL> GRANT EXECUTE ON swap TO PUBLIC;

删除过程

SQL> DROP PROCEDURE find_emp;

7、函数是可以返回值的命名的 PL/SQL 子程序。

8、创建函数的语法：

CREATE [OR REPLACE] FUNCTION

[(param1,param2)]

RETURN IS|AS

[local declarations]

BEGIN

Executable Statements;

RETURN result;

EXCEPTION

Exception handlers;

END;

9、定义函数的：

函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数

形参不能是 PL/SQL 类型

函数的返回类型也必须是数据库类型

访问函数的两种方式：

使用 PL/SQL 块

使用 SQL 语句

10、创建函数

CREATE OR REPLACE FUNCTION fun_hello

RETURN VARCHAR2

IS

BEGIN

RETURN '朋友，您好';

END;

从 SQL 语句调用函数：

SQL> SELECT fun_hello FROM DUAL;

例：

CREATE OR REPLACE FUNCTION

item_price_range (price NUMBER)

RETURN VARCHAR2 AS

min_price NUMBER;

max_price NUMBER;

BEGIN

SELECT MAX(ITEMRATE), MIN(ITEMRATE)

INTO max_price, min_price

FROM itemfile;

IF price >= min_price AND price <= max_price

THEN

RETURN '输入的单价介于最低价与最高价之间';

ELSE

RETURN '超出范围';

END IF;

END;

11、过程和函数的比较

12、自主事务处理

-主事务处理启动事务处理

-然后主事务处理被暂停

-自主事务处理子程序内的 SQL 操作

-然后终止自主事务处理

-恢复主事务处理

PRAGMA AUTONOMOUS_TRANSACTION ：用于标记子程序为自主事务处理

13、程序包是对相关过程、函数、变量、游标和异常等对象的封装

程序包由规范和主体两部分组成

程序包规范

CREATE [OR REPLACE]

PACKAGE

package_name IS|AS

[Public item declarations]

[Subprogram specification]

END [package_name];

程序包主体

CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS

[Private item declarations]

[Subprogram bodies]

[BEGIN

Initialization]

END [package_name];

例：CREATE OR REPLACE PACKAGE BODY pack_me AS

PROCEDURE order_proc (orno VARCHAR2) IS

stat CHAR(1);

BEGIN

SELECT ostatus INTO stat FROM order_master

WHERE orderno = orno;

……

END order_proc;

FUNCTION order_fun(ornos VARCHAR2)

RETURN VARCHAR2

IS

icode VARCHAR2(5);

ocode VARCHAR2(5);

BEGIN

……

END order_fun;

END pack_me;

14、游标的定义分为游标规范和游标主体两部分

在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型

RETURN子句指定的数据类型可以是：

用 %ROWTYPE 属性引用表定义的记录类型

程序员定义的记录类型

例：

SQL> CREATE OR REPLACE PACKAGE BODY cur_pack AS

CURSOR ord_cur(vcode VARCHAR2)

RETURN order_master%ROWTYPE IS

SELECT * FROM order_master WHERE VENCODE=vcode;

PROCEDURE ord_pro(vcode VARCHAR2) IS

or_rec order_master%ROWTYPE;

BEGIN

OPEN ord_cur(vcode);

LOOP

FETCH ord_cur INTO or_rec;

EXIT WHEN ord_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LIne(’返回的值为' || or_rec.orderno);

END LOOP;

END ord_pro;

END cur_pack;

15、USER_OBJECTS 视图包含用户创建的子程序和程序包的信息

SELECT object_name, object_type

FROM USER_OBJECTS

WHERE object_type IN ('PROCEDURE', 'FUNCTION',

'PACKAGE', 'PACKAGE BODY');

USER_SOURCE 视图存储子程序和程序包的源代码

SELECT line, text FROM USER_SOURCE

WHERE NAME='TEST';

16、子程序是命名的 PL/SQL 块，可带参数并可在需要时随时调用

有两种类型的PL/SQL子程序，即过程和函数

过程用户执行特定的任务，函数用于执行任务并返回值

程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装

程序包由两部分组成，即包规范和包主体

使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳

第八章 触发器和内置程序包

1、触发器：

触发器是当特定事件出现时自动执行的存储过程

特定事件可以是执行更新的DML语句和DDL语句

触发器不能被显式调用

触发器的功能：

1）自动生成数据

2）自定义复杂的安全权限

3）提供审计和日志记录

4）启用复杂的业务逻辑

2、创建触发器的语法

CREATE [OR REPLACE] TRIGGER trigger_name

AFTER | BEFORE | INSTEAD OF

[INSERT] [[OR] UPDATE [OF column_list]]

[[OR] DELETE]

ON table_or_view_name

[REFERENCING {OLD [AS] old / NEW [AS] new}]

[FOR EACH ROW]

[WHEN (condition)]

pl/sql_block;

例：CREATE OR REPLACE TRIGGER aiu_itemfile

AFTER INSERT

ON itemfile

FOR EACH ROW

BEGIN

IF (:NEW.qty_hand = 0) THEN

DBMS_OUTPUT.PUT_LINE('警告：已插入记录，但数量为零');

ELSE

DBMS_OUTPUT.PUT_LINE(‘已插入记录');

END IF;

END;

3、触发器的组成部分

触发器由三部分组成：

触发器语句（事件）：定义激活触发器的 DML 事件和 DDL 事件

触发器：执行触发器的条件，该条件必须为真才能激活触发器

触发器操作（主体）：包含一些 SQL 语句和代码，它们在发出了触发器语句且触发的值为真时运行

4、触发器的类型：

1）模式(DDL)触发器：在模式中执行 DDL 语句时执行

2）数据库级触发器：在发生打开、关闭、登录和退出数据库等系统事件时执行

3）DML触发器：在对表或视图执行DML语句时执行

①行级触发器：对DML语句修改的每个行执行一次

②语句级触发器：无论受影响的行数是多少，都只执行一次

③INSTEAD OF触发器：用于用户不能直接使用 DML 语句修改的视图

例（行级触发器）：SQL> CREATE TABLE TEST_TRG

(ID NUMBER, NAME VARCHAR2(20));

SQL> CREATE SEQUENCE SEQ_TEST;

SQL> CREATE OR REPLACE TRIGGER BI_TEST_TRG

BEFORE INSERT OR UPDATE OF ID

ON TEST_TRG

FOR EACH ROW

BEGIN

IF INSERTING THEN

SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;

ELSE

RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值！');

END IF;

END;

例（语句级触发器）

SQL> CREATE OR REPLACE TRIGGER trgdemo

AFTER INSERT OR UPDATE OR DELETE

ON order_master

BEGIN

IF UPDATING THEN

DBMS_OUTPUT.PUT_LINE(‘已更新 ORDER_MASTER 中的数据');

ELSIF DELETING THEN

DBMS_OUTPUT.PUT_LINE(‘已删除 ORDER_MASTER 中的数据');

ELSIF INSERTING THEN

DBMS_OUTPUT.PUT_LINE(‘已在 ORDER_MASTER 中插入数据');

END IF;

END;

例（INSTEAD OF 触发器）：

SQL> CREATE OR REPLACE TRIGGER upd_ord_view

INSTEAD OF UPDATE ON ord_view

FOR EACH ROW

BEGIN

UPDATE order_master

SET vencode=:NEW.vencode

WHERE orderno = :NEW.orderno;

DBMS_OUTPUT.PUT_LINE(‘已激活触发器');

END;

例（模式触发器）：SQL> CREATE TABLE dropped_obj (

obj_name VARCHAR2(30),

obj_type VARCHAR2(20),

drop_date DATE);

SQL> CREATE OR REPLACE TRIGGER log_drop_obj

AFTER DROP ON SCHEMA

BEGIN

INSERT INTO dropped_obj

VALUES( ORA_DICT_OBJ_NAME,

ORA_DICT_OBJ_TYPE, SYSDATE);

END;

5、启用、禁用和删除触发器

SQL> ALTER TRIGGER aiu_itemfile DISABLE; //禁用触发器

SQL> ALTER TRIGGER aiu_itemfile ENABLE; //启用触发器

SQL> DROP TRIGGER aiu_itemfile; //删除触发器

6、内置程序包

扩展数据库的功能

为 PL/SQL 提供对 SQL 功能的访问

用户 SYS 拥有所有程序包

是公有同义词

可以由任何用户访问

一些内置程序包

例：DBMS_OUTPUT包显示 PL/SQL 块和子程序的调试信息

SQL> SET SERVEROUTPUT ON

SQL> BEGIN

DBMS_OUTPUT.PUT_LINE('打印三角形');

FOR i IN 1..9 LOOP

FOR j IN 1..i LOOP

DBMS_OUTPUT.PUT('*');

END LOOP for_j;

DBMS_OUTPUT.NEW_LINE;

END LOOP for_i;

END;

DBMS_LOB 包提供用于处理大型对象的过程和函数

DBMS_XMLQUERY 包用于将查询结果转换为 XML 格式

例：SQL> DECLARE

result CLOB;

xmlstr VARCHAR2(32767);

line VARCHAR2(2000);

line_no INTEGER := 1;

BEGIN

result := DBMS_XMLQuery.getXml('SELECT empno, ename

FROM employee');

xmlstr := DBMS_LOB.SUBSTR(result,32767);

LOOP

EXIT WHEN xmlstr IS NULL;

line := SUBSTR(xmlstr,1,INSTR(xmlstr,CHR(10))-1);

DBMS_OUTPUT.PUT_LINE(line_no || ':' || line);

xmlstr := SUBSTR(xmlstr,INSTR(xmlstr,CHR(10))+1);

line_no := line_no + 1;

END LOOP;

END;

DBMS_RANDOM 包可用来生成随机整数

例：

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

l_num NUMBER;

counter NUMBER;

BEGIN

counter:=1;

WHILE counter <= 10

LOOP

l_num := DBMS_RANDOM.RANDOM;

DBMS_OUTPUT.PUT_LINE(l_num);

counter:=counter+1;

END LOOP;

END;

UTL_FILE 包用于读写操作系统文本文件

操作文件的一般过程是打开、读或写、关闭

UTL_FILE 包指定文件路径依赖于 DIRECTORY 对象

SQL> CREATE DIRECTORY TEST_DIR AS 'C:\\DEVELOP';

SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT;

例：

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

input_file UTL_FILE.FILE_TYPE;

input_buffer VARCHAR2(4000);

BEGIN

input_file := UTL_FILE.FOPEN(

'TEST_DIR', 'employees.xml', 'r');

LOOP

UTL_FILE.GET_LINE(input_file,input_buffer);

DBMS_OUTPUT.PUT_LINE(input_buffer);

END LOOP;

UTL_FILE.FCLOSE(input_file);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('------------------');

END;

7、小结

（1）触发器是当特定事件出现时自动执行的存储过程

（2）触发器分为 DML 触发器、DDL 触发器和数据库级触发器三种类型

（3）DML 触发器的三种类型包括行级触发器、语句级触发器和 INSTEAD OF 触发器

（4）一些常用的内置程序包：

1）DBMS_OUTPUT 包输出 PL/SQL 程序的调试信息

2）DBMS_LOB 包提供操作 LOB 数据的子程序

3）DBMS_XMLQUERY 将查询结果转换为 XML 格式

4）DBMS_RANDOM 提供随机数生成器

5）UTL_FILE 用于读写操作系统文本文件

第九章 备份与恢复

1、Oracle数据库的备份包括以下两种类型：

（1）、逻辑备份：逻辑备份是对数据库逻辑组件（如表、视图和存储过程等数据库对象）的备份

（2）、物理备份：物理备份是对数据库的操作系统物理文件（如数据文件、控制文件和日志文件等）的备份

2、导致数据库操作中止的故障包括四种类型：

当 Oracle 的数据库实例由于硬件或软件问题而无法继续运行时，就会发生实例故障

在数据库无法正确读取或写入某个数据库文件时，会发生介质故障

3、导出和导入实用程序

（1）、导出和导入实用程序用于实施数据库的逻辑备份和恢复

导出实用程序将数据库中的对象定义和数据备份到一个操作系统二进制文件中

导入实用程序读取二进制导出文件并将对象和数据载入数据库中

（2）、导出和导入实用程序的特点有：

1）可以按时间保存表结构和数据

2）允许导出指定的表，并重新导入到新的数据库中

3）可以把数据库迁移到另外一台异构服务器上

4）在两个不同版本的Oracle数据库之间传输数据

5）在联机状态下进行备份和恢复

6)可以重新组织表的存储结构，减少链接及磁盘碎片

(3)、使用以下三种方法调用导出和导入实用程序：

允许用户将运行参数和参数值存储在参数文件中，以便重复使用参数

（4）、导出和导入数据库对象的四种模式是：

导出和导入一个或多个指定的表空间中的所有对象。

（5）、导出实用程序有以下常用命令参数：

（6）、导出实用程序

1）、按用户方式导出数据

exp scott/tiger@a file=scott_back owner=(scott)

2）、按表方式导出数据

exp scott/tiger@a tables=(emp, dept) file=scott_back_tab

3）、按表空间方式导出数据

exp system/aptech@a tablespaces=(users) file=tbs_users

4）、使用参数文件导出数据

exp system/aptech parfile='C:\\parameters.txt'

（7）、导入实用程序有如下常用命令参数：

（8）、导入实用程序

1）、将整个文件导入数据库

Imp 用户名/密码@orcl file=item_back.dmp ignore=y full=y

2）、将scott用户的表导入到martin用户

imp system/aptech@orcl file=scott_back fromuser=scott touser=martin

tables=(emp,dept)

3）、使用参数文件导入数据

imp system/oracle parfile='C:\\parameters.txt'

4、数据库归档方式

Oracle 数据库可以运行在两种归档方式：

非归档日志方式

归档日志方式