ORACLE数据库编程

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了ORACLE数据库编程，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含34311字，纯文字阅读大概需要50分钟。

内容图文

一、介绍
Oracle数据库系统是美国Oracle（甲骨文）公司提供的以分布式数据库为
核心的一组软件产品，是目前最流行的客户/服务器（Client/Server，C/S）或
浏览器/服务器（Browser/Server，B/S）体系结构的数据库之一。
二、特点
1、完整的数据管理功能：
数据的大量性
数据的保存的持久性
数据的共享性
数据的可靠性
Oracle优点：
可用性强
可扩展性强
数据安全性强
稳定性强
三、Oracle数据库的辑逻结构
1、表空间
一个数据库被分割为数个被称作表空间（tablespace）的逻辑存储单位，每个
表空间内保存的是一组相关的逻辑对象。
2、段
段是为一个特定逻辑结构分配的一组数据区（extents）。
3、数据区
数据区是由一次存储空间分配而获得的数个连续数据块的集合，用于存储信息
。
4、数据块
在Oracle数据库中，最精细的数据存储粒度是数据块，一个数据块相当于磁盘
上一段连续的物理存储空间。

第二章 PL/SQL 编程基础

一、PL/SQL块结构
DECLARE
/*
* 声明部分——定义常量、变量、复杂数据类型、游标、用户自定义异常
*/
BEGIN
/*
* 执行部分——PL/SQL语句和SQL语句
*/
EXCEPTION
/*
* 异常处理部分——处理运行异常
*/
END;
/*块结束标记 */

二、标识符
PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同：
?标识符名第一个字符必须为字母
?标识符名不分大小写
?标识符名不能超过30字符
?不能用‘-’(减号)
?不能是SQL保留字
注意: 一般不要把变量名声明与表中字段名完全一样，如果这样可能得到不正
确的结果。
三、变量
变量定义
声明：
variable_name Type
赋值：
variable := expression

1. BOOLEAN
该数据用于定义布尔变量，其变量的值为TRUE、FALSE或NULL。注意此类型只能
在PL/SQL中使用，表列是没有此类型的。

2. %TYPE
通常用于指定表的某个列的数据类型，可以理解为“的类型”(小技巧：%读“
的”)。

3. %ROWTYPE
%ROWTYPE操作符，返回一个记录类型，其数据类型和数据库表的数据结构相一
致。

4. 复合数据类型
4.1. 记录（Record）
4.2. 表(TABLE)
4.2.1. 索引表
关键字INDEX BY表示创建一个主键索引，以便引用记录表变量中的特定行。
4.2.2 嵌套表
嵌套表的元素下标从1开始，并且元素个数没有限制。嵌套表数组元素值可以是
稀疏的。
注意：索引表类型不能作为表列的数据类型使用，但嵌套表类型可以作为表列
的数据类型。
4.3. 数组（VARRAY）
数组是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标，
它取决于成员在数组中的位置。
5. 绑定变量
绑定变量是在主机环境中定义的变量。在PL/SQL 程序中可以使用绑定变量作为
他们将要使用的其它变量。为了在PL/SQL 环境中声明绑定变量，使用命令VAR
或VARIABLE。
当在SQL*Plus中与PL/SQL块之间进行数据交互时，需要使用SQL*Plus绑定变量
来完成。
当在PL/SQL中引用非PL/SQL变量时，必须要在非PL/SQL变量前加冒号(“:”)
。

六、常用类型转换函数
CHAR 转换为 NUMBER：
使用 TO_NUMBER 函数来完成字符到数字的转换
NUMBER 转换为CHAR：
使用 TO_CHAR 函数可以实现数字到字符的转换
字符转换为日期：
使用 TO_DATE 函数可以实现 字符到日期的转换
日期转换为字符：
使用 TO_CHAR 函数可以实现日期到字符的转换

七、RETURNNING的使用
RETURNING子句作用：用于检索INSERT语句中所影响的数据行数，当INSERT语
句使用VALUES子句插入数据时，RETURNING 字句还可将列表达式、ROWID和REF
值返回到输出变量中。使用RETURNING子句是应注意以下几点限制：
?不能与DML语句和远程对象一起使用；
?不能检索LONG类型信息；
?当通过视图向基表中插入数据时，只能与单基表视图一起使用。
说明：
RETURNING子句用于检索被修改行的信息。当UPDATE语句修改单行数据时，
RETURNING子句可以检索被修改行的ROWID和REF值，以及行中被修改列的列表达
式，并可将他们存储到PL/SQL变量或复合变量中；当UPDATE语句修改多行数据
时，RETURNING子句可以将被修改行的ROWID和REF值，以及列表达式值返回到复
合变量数组中。在UPDATE中使用RETURNING 子句的限制与INSERT语句中对
RETURNING子句的限制相同。

RETURNING子句用于检索被删除行的信息：当DELETE语句删除单行数据时，
RETURNING子句可以检索被删除行的ROWID和REF值，以及被删除列的列表达式，
并可将他们存储到PL/SQL变量或复合变量中；当DELETE语句删除多行数据时，
RETURNING子句可以将被删除行的ROWID和REF值，以及列表达式值返回到复合变
量数组中。在DELETE中使用RETURNING子句的限制与INSERT语句中对RETURNING
子句的限制相同。

八、注释
在PL/SQL里，有两种注释：
?使用’–’(双减号)注释，它的作用范围是只能在一行有效。
?使用’/* */’注释，这是多行注释，可注释一行，也可注释多行。

第三章 PL/SQL 程序控制结构

一、条件结构
1. 简单IF结构

-- 简单IF结构
IF <布尔表达式> THEN
满足条件时执行的语句
END IF;
2. IF-ELSE结构

-- IF-ELSE结构
IF <布尔表达式> THEN
满足条件时执行的语句
ELSE
不满足条件时执行的语句
END IF;
3. 多重IF

-- 多重IF
IF <布尔表达式1> THEN
满足条件1时执行的语句
ELSIF <布尔表达式2> THEN
满足条件2时执行的语句
ELSIF <布尔表达式3> THEN
满足条件3时执行的语句
ELSE
满足条件1、2、3均不满足时执行的语句
END IF;

注意：ELSIF不能写成ELSEIF

4. CASE

语法一：

CASE 条件表达式
WHEN 条件表达式结果1 THEN
语句1
WHEN 条件表达式结果2 THEN
语句2
......
WHEN 条件表达式结果n THEN
语句n
[ELSE 条件表达式结果]
END;
语法二：

CASE
WHEN 条件表达式1 THEN
语句1
WHEN 条件表达式2 THEN
语句2
......
WHEN 条件表达式n THEN
语句n
[ELSE 语句]
END;
二、循环结构

1. 简单循环

语法：

LOOP
循环体语句;
[EXIT WHEN <条件语句>]
END LOOP;
2. WHILE循环

语法：

WHILE <布尔表达式> LOOP
循环体语句;
END LOOP;
3. FOR循环

语法：
[<<标签>>]
FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP
循环体语句;
END LOOP [<<标签>>];
使用关键字REVERSE，循环变量自动减1。跟在IN REVERSE后面的数字应是从小
到大的顺序，而且必须是整数，不能是变量或表达式。

三、顺序结构
1. GOTO
GOTO语句用于跳转到指定<<标号>>去执行语句，是无条件跳转到指定的标号去
的意思。
注意：标号是用<<　>>括起来的标识符。
GOTO语句缺点是会增加程序的复杂性，降低可读性，所以Oracle建议不要使用
。
2. NULL
空语句，执行没有任何实际效果，可以使某些语句变得有意义，提高程序的可
读性，保证其他语句结构的完整性和正确性，通常用于占位置。

Oracle数据库之PL/SQL异常处理

1. 异常处理
异常处理是用来处理正常执行过程中未预料的事件。
PL/SQL编程过程中，有三种类型的异常：
1.1 定义异常
对这种异常情况的处理，无需在程序中定义，当PL/SQL程序违反Oracle规则或
超越系统限制时隐式引发。
1.2 非预定义异常
其他标准的Oracle错误。对这种异常情况的处理，需要用户在程序中定义，然
后由Oracle自动将其引发。
1.3 用户定义异常
程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，
需要用户在程序中定义，然后显式地在程序中将其引发。
异常处理通常放在PL/SQL程序的后部，语法结构为：
EXCEPTION
WHEN { exception [ OR exception ]... | OTHERS }
THEN statement [ statement ]...
SQLCODE用于取得Oracle错误号。
SQLERRM则用于取得与之相关的错误消息。

第四章 在PL/SQL中更改和更改数据和管理事务

1. 什么是事务
在数据库中事务是工作的逻辑单元，一个事务是由一个或多个完成一组的相关
行为的SQL语句组成，通过事务机制确保这一组SQL语句所作的操作要么都成功
执行，完成整个工作单元操作，要么一个也不执行。

2. 事务特性
SQL92标准定义了数据库事务的四个特点：
? 原子性(Atomicity)：一个事务里面所有包含的SQL语句是一个执行整
体，不可分割，要么都做，要么都不做。
? 一致性(Consistency)：事务开始时，数据库中的数据是一致的，事
务结束时，数据库的数据也应该是一致的。
? 隔离性(Isolation)：是指数据库允许多个并发事务同时对其中的数
据进行读写和修改的能力，隔离性可以防止事务的并发执行时，由于他们的操
作命令交叉执行而导致的数据不一致状态。
? 持久性 (Durability) : 是指当事务结束后，它对数据库中的影响是
永久的，即便系统遇到故障的情况下，数据也不会丢失。
一组SQL语句操作要成为事务，数据库管理系统必须保证这组操作的原子性
（Atomicity）、一致性（consistency）、隔离性（Isolation）和持久性
（Durability），这就是ACID特性。
3. 数据异常
因为Oracle中支持多个事务并发执行，所以会出现下面的数据异常。
3.1 脏读
3.2 不可重复读
3.3 幻读
4. 事务隔离级别
5. 事务控制命令
5.1 提交事务
在执行使用COMMIT语句可以提交事务，当执行了COMMIT语句后，会确认事务的
变化，结束事务，删除保存点，释放锁。当使用COMMIT语句结束事务之后，其
他会话将可以查看到事务变化后的新数据。
5.2 回滚事务
保存点（savepoint）：是事务中的一点，用于取消部分事务，当结束事务时，
会自动的删除该事务所定义的所有保存点。当执行ROLLBACK时，通过指定保存
点可以回退到指定的点。
设置保存点：
sql> Savepoint a;
? 1
删除保存点：
sql> Release Savepoint a;
? 1
回滚部分事务：
sql> Rollback To a;
? 1
回滚全部事务：
sql> Rollback;
6.1 锁分类
根据保护对象的不同，Oracle数据库锁可分为：
? DML lock（data locks，数据锁）：用于保护数据的完整性。
? DDL lock（dictionary locks，字典锁）：用于保护数据库对象的结
构（例如表、视图、索引的结构定义）。
? Internal locks 和latches（内部锁与闩）：保护内部数据库结构。
? Distributed locks（分布式锁）：用于OPS（并行服务器）中。
? PCM locks（并行高速缓存管理锁）：用于OPS（并行服务器）中。
6.2 共享锁（S锁）
6.3 排他锁（X锁）
6.4 行级共享锁（RS锁）
6.5 行级排他锁（RX锁）
6.6 共享行级排他锁（SRX锁）
7. 数据库事务实现机制

Oracle数据库之FORALL与BULK COLLECT语句

index_name：一个无需声明的标识符，作为集合下标使用。
lower_bound .. upper_bound：数字表达式，来指定一组连续有效的索引数字
下限和上限。该表达式只需解析一次。
INDICES OF collection_name：用于指向稀疏数组的实际下标。跳过没有赋值
的元素，例如被 DELETE 的元素，NULL 也算值。
VALUES OF index_collection_name：把该集合中的值当作下标，且该集合值
的类型只能是 PLS_INTEGER/BINARY_INTEGER。
SAVE EXCEPTIONS：可选关键字，表示即使一些DML语句失败，直到FORALL
LOOP执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。
dml_statement：静态语句，例如：UPDATE或者DELETE；或者动态（EXECUTE
IMMEDIATE）DML语句。
2. FORALL的使用

3. FORALL注意事项
使用FORALL时，应该遵循如下规则：
FORALL语句的执行体，必须是一个单独的DML语句，比如INSERT，UPDATE或
DELETE。
不要显式定义index_row，它被PL/SQL引擎隐式定义为PLS_INTEGER类型，并且
它的作用域也仅仅是FORALL。
这个DML语句必须与一个集合的元素相关，并且使用FORALL中的index_row来索
引。注意不要因为index_row导致集合下标越界。
lower_bound和upper_bound之间是按照步进 1 来递增的。
在sql_statement中，不能单独地引用集合中的元素，只能批量地使用集合。
在sql_statement中使用的集合，下标不能使用表达式。

4. BULK COLLECT介绍
BULK COLLECT子句会批量检索结果，即一次性将结果集绑定到一个集合变量中
，并从SQL引擎发送到PL/SQL引擎。
5. BULK COLLECT的使用
5.1 在SELECT INTO中使用BULK COLLECT
5.2 在FETCH INTO中使用BULK COLLECT
在游标中可以使用BLUK COLLECT一次取出一个数据集合，比用游标单条取数据
效率高，尤其是在网络不大好的情况下。

6. BULK COLLECT的注意事项
BULK COLLECT INTO 的目标对象必须是集合类型。
只能在服务器端的程序中使用BULK COLLECT，如果在客户端使用，就会产生一
个不支持这个特性的错误。
不能对使用字符串类型作键的关联数组使用BULK COLLECT子句。
复合目标(如对象类型)不能在RETURNING INTO子句中使用。
如果有多个隐式的数据类型转换的情况存在，多重复合目标就不能在BULK
COLLECT INTO子句中使用。
如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用
于BULK COLLECTINTO子句中。

7. FORALL与BULK COLLECT综合运用
FORALL与BULK COLLECT是实现批量SQL的两个重要方式，我们可以将其结合使
用以提高性能。
-- 创建表tb_emp
CREATE TABLE tb_emp AS
SELECT empno, ename, hiredate
FROM emp
WHERE 1 = 0;

DECLARE
-- 声明游标
CURSOR emp_cur IS
SELECT empno, ename, hiredate FROM emp;
-- 基于游标的嵌套表类型
TYPE nested_emp_type IS TABLE OF emp_cur%ROWTYPE;
-- 声明变量
emp_tab nested_emp_type;
BEGIN
SELECT empno, ename, hiredate
BULK COLLECT INTO emp_tab
FROM emp
WHERE sal > 1000;

-- 使用FORALL语句将变量中的数据插入到表tb_emp
FORALL i IN 1 .. emp_tab.COUNT
INSERT INTO (SELECT empno, ename, hiredate FROM tb_emp)
VALUES emp_tab( i );

COMMIT;
DBMS_OUTPUT.put_line(‘总共向 tb_emp 表中插入记录数： ‘ ||
emp_tab.COUNT);
END;

 

第五章 在PL/SQL 使用游标获取数据

1. 游标概念
字面意思是游动的光标，是指向上下文区域的句柄或指针。

2. 显式游标
显式游标使用主要有四个步骤：
声明/定义游标
打开游标
读取数据
关闭游标
2.1 声明/定义游标
[RETURN datatype]是可选的，表示游标返回数据的数据。如果选择，则应该严
格与select_statement中的选择列表在次序和数据类型上匹配。一般是记录数
据类型（RECORD）或带“%ROWTYPE”的数据。

2.2 打开游标
执行游标所对应的SELECT语句，将其查询结果放入工作区，并且指针指向工作
区的首部，标识游标结果集。

2.3 读取数据
检索结果集合中的数据行，放入指定的输出变量中。
执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据
行。当检索到最后一行数据时，如果再次执行FETCH语句，将操作失败，并将游
标属性%NOTFOUND置为TRUE。所以每次执行完FETCH语句后，检查游标属
性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行，以便确定
是否给对应的变量赋了值。

2.4 关闭游标
当处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统
资源。

关闭游标后不能再使用FETCH语句获取其中数据。关闭后的游标可以使用OPEN语
句重新打开。

3. 显式游标属性
游标的状态（如是否打开，获取了多少行数据等）可以使用游标属性来获取。
游标属性以“%属性名”的形式加在游标名之后。显式游标属性有：
属性名 说明
%FOUND 如果记录成功获取，返回TRUE，否则返回FALSE
%NOTFOUND 如果记录获取失败，返回TRUE，否则返回FALSE
%ROWCOUNT 返回已经从游标中获取的记录数
%ISOPEN 如果游标是打开的，返回TRUE，否则返回FALSE

4. 基于游标定义记录变量
使用%ROWTYPE属性不仅可以基于表和视图定义记录变量，也可以基于游标定义
记录变量。当基于游标定义记录变量时，记录成员名实际就是SELECT语句的列
名和列别名。
为了简化显式游标的数据处理，建议使用基于游标的记录变量存放游标数据。
基于游标定义记录变量，比声明记录类型变量要方便，不容易出错。
5. 隐式游标
如果在PL/SQL块中使用了SELECT语句进行操作，PL/SQL会隐含处理游标定义，
而对于非查询语句，如修改、删除操作，则由ORACLE系统自动地为这些操作设
置游标并创建其工作区。由系统隐含创建的游标称为隐式游标，隐式游标的名
字为SQL。
隐式游标的属性：
SQL%FOUND 如果记录成功获取，返回TRUE，否则返回FALSE
SQL%NOTFOUND 如果记录获取失败，返回TRUE，否则返回FALSE
SQL%ROWCOUNT 返回已经从游标中获取的记录数
SQL%ISOPEN 如果游标是打开的，返回TRUE，否则返回FALSE
隐式游标在INSERT，UPDATE，DELETE，SELECT语句中不必明确定义游标。

6. 游标FOR循环
游标FOR循环和显示游标的一种快捷使用方式，它使用FOR循环依次读取结果集
中的行数据，当FOR循环开始时，游标自动打开（不需要OPEN）,每循环一次系
统自动读取游标当前行的数据（不需要FETCH)，当退出FOR循环时，游标被自动
关闭（不需要使用CLOSE）使用游标FOR循环的时候不能使用OPEN语句，FETCH语
句和CLOSE语句，否则会产生错误。

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

8. 游标变量
与游标类似，游标变量指向多行查询的结果集的当前行。但是，游标与游标变
量是不同的，就像常量和变量的关系一样。游标是静态的，游标变量是动态的
，因为它不与特定的查询绑定在一起。

 

第六章 动态SQL

1. 静态SQLSQL与动态SQL
2. 动态SQL介绍
Oracle数据库有两种动态SQL技术：使用DBMS_SQL包和本地动态SQL。
动态SQL语句是在运行时由程序创建的字符串，它们必须是有效的SQL语句或
PL/SQL块，也可以包含用于数据绑定的占位符。占位符是未声明的标识符，名
称并不重要，只需以冒号开头。
一般在下列的情况下我们才需要使用动态SQL：
1. 在PL/SQL块中执行数据定义语句，数据控制语句或会话控制语句(如
ALTER SESSION)，因为在PL/SQL中，这样的语句是不允许静态执行的。
2. 为了获取更多的灵活性。例如，想在运行时根据实际需求来为SELECT
语句的WHERE子句选择不同的schema对象。
3. 动态地使用包DBMS_SQL执行SQL语句，但是为了获得更好的性能，或
是DBMS_SQL不支持的功能。
通常有三种执行不同类型的动态SQL方法：
1. 使用EXECUTE IMMEDIATE语句。
除不能处理多行查询语句，其他的动态SQL包括DDL语句，DCL语句以及单行的
SELECT查询都可以。
2. REF CURSOR动态游标，使用OPEN-FOR，FETCH，CLOSE。
能处理动态的多行查询操作，必须要使用OPEN-FOR语句打开游标，使用FETCH语
句循环提取数据，最终使用CLOSE语句关闭游标。
3. 使用批量BULK COLLECT执行动态SQL。
通过使用批量动态SQL语句，可以加快SQL语句处理，进而提高PL/SQL的性能。

3. 使用EXECUTE IMMEDIATE语句
1. 可以把所有的绑定参数放到USING子句中，默认的参数模式是IN。对于
含有RETURNING子句的DML语句来说，我们可以把OUT参数放到RETURNING INTO
之后，并且不用指定它们的参数模式，因为默认就是OUT。如果我们既使用了
USING又使用RETURNING INTO，那么，USING子句中就只能包含IN模式的参数了
。
2. 每个占位符必须与USING子句和/或RETURNING INTO子句中的一个绑定
参数对应。
3. 可以使用数字、字符和字符串作为绑定参数，但不能使用布尔类型
(TRUE，FALSE和NULL)。
4. 动态SQL是不支持PL/SQL特有的类型，所以不能使用布尔型或索引表。
当动态INSERT、UPDATE或DELETE语句有一个RETURNING子句时，输出绑定参数
可以放到RETURNING INTO或USING子句的后面。

4. REF CURSOR动态游标，使用OPEN-FOR，FETCH，CLOSE
在运行时，USING子句中的绑定变量可以替换动态SELECT语句中相对应的占位符
。
5. 批量动态SQL
批量绑定能减少PL/SQL和SQL引擎之间的切换，改善性能。批量绑定能让Oracle
把SQL语句中的一个变量与一个集合相绑定。集合类型可以是任何PL/SQL集合类
型(索引表、嵌套表或变长数组)。但是，集合元素必须是SQL数据类型，如
VARCHAR2、DATE或NUMBER。有三种语句支持动态批量绑定：EXECUTE
IMMEDIATE、FETCH和FORALL。
5.1 EXECUTE IMMEDIATE批量
5.2 FETCH批量
5.3 FORALL批量

第七章 开发PL/SQL子程序和包

Oracle数据库之PL/SQL过程与函数

PL/SQL块分为匿名块与命名块，命名块又包含子程序、包和触发器。
过程和函数统称为PL/SQL子程序，我们可以将商业逻辑、企业规则写成过程或
函数保存到数据库中，以便共享。
过程和函数均存储在数据库中，并通过参数与其调用者交换信息。过程和函数
的唯一区别是函数总向调用者返回数据，而过程不返回数据。
1. 存储过程概念
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功
能的SQL语句集，存储在数据库中。经过第一次编译后再次调用不需要再次编译
，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执
行它。
存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都
应该用到存储过程。

2. 创建过程
语法：
CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure_name
[ ( parameter_declaration [, parameter_declaration ]... ) ]
[ invoker_rights_clause ]
{ IS | AS }
{ [ declare_section ] body | call_spec | EXTERNAL} ;
说明：

procedure_name：过程名称。

parameter_declaration：参数声明，格式如下：
parameter_name [ [ IN ] datatype [ { := | DEFAULT } expression ]
| { OUT | IN OUT } [ NOCOPY ] datatype
IN：输入参数。
OUT：输出参数。
IN OUT：输入输出参数。
invoker_rights_clause：这个过程使用谁的权限运行，格式：
AUTHID { CURRENT_USER | DEFINER }
declare_section：声明部分。
body：过程块主体，执行部分。
一般只有在确认procedure_name过程是新过程或是要更新的过程时，才使用OR
REPALCE关键字，否则容易删除有用的过程。

3. 使用过程参数
当建立过程时，既可以指定过程参数，也可以不提供任何参数。
过程参数包括输入参数、输出参数和输入输出参数，其中输入参数（IN）用于
接收调用环境的输入数据，输出参数（OUT）用于将输出数据传递到调用环境，
而输入输出参数（IN OUT）不仅要接收输入数据，而且还要输出数据到调用环
境。
3.1 带有输入参数的过程
通过使用输入参数，可以将应用程序数据传递到过程。当定义过程参数时，默
认参数模式是输入参数，另外可以使用IN关键字显式定义输入参数
3.2 带有输出参数的过程
通过在过程中使用输出参数，可以将数据或消息传递到调用环境和应用程序。
当定义输出参数时，需要指定参数模式OUT。
4. 调用过程
当在SQL*PLUS中调用过程时，需要使用CALL或者EXECUTE命令，而在PL/SQL块
中过程可以直接引用。
ORACLE使用EXECUTE语句来调用存储过程语法：
EXEC[UTE] procedure_name(parameter1, parameter2,
5. 函数介绍
函数是一段独立的PL/SQL程序代码，它执行某个特定的、明确的任务。通常，
函数将处理从程序的调用部分传递给它的信息，然后返回单个值。信息通过称
为参数的特殊标识符传递给函数，然后通过RETURN语句返回。
6. 创建函数
函数参数也有输入、输出、输入输出三种模式：IN、OUT、IN OUT是形参的模式
。若省略，则为IN模式。
和过程一样，IN模式的形参只能将实参传递给形参，进入函数内部，但只能读
不能写，函数返回时实参的值不变。
OUT模式的形参会忽略调用时的实参值（或说该形参的初始值总是NULL），但在
函数内部可以被读或写，函数返回时形参的值会赋予给实参。
IN OUT具有前两种模式的特性，即调用时，实参的值总是传递给形参，结束时
，形参的值传递给实参。
调用时，对于IN模式的实参可以是常量或变量，但对于OUT和IN OUT模式的实参
必须是变量。
7. 函数调用
8. 删除过程或函数
删除过程语法：
DROP PROCEDURE [schema.]procudure_name;
删除函数语法：
DROP FUNCTION [schema.]function_name;

9. 过程与函数比较
过程 函数
作为PL/SQL语句执行 作为表达式的一部分执行
在规范中不包含RETURN子句 必须在规范中包含RETURN子句
不返回任何值 必须返回单个值
可以RETURN语句，但是与函数不同，它不能用于返回值必须包含至少一条
RETURN语句
过程与函数的相同功能有：
1.都使用IN模式的参数传入数据、OUT模式的参数返回数据。
2.输入参数都可以接受默认值，都可以传值或传引导。
3.调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。
4.都有声明部分、执行部分和异常处理部分。
5.其管理过程都有创建、编译、授权、删除、显示依赖关系等。

 

Oracle数据库之PL/SQL包

1. 简介
包(PACKAGE)是一种数据对象，它是一组相关过程、函数、变量、常量和游标等
PL/SQL程序设计元素的组合，作为一个完整的单元存储在数据库中，用名称来
标识。
包类似于JAVA或C#语言中的类，包中的变量相当于类中的成员变量，过程和函
数相当于类方法。
通过使用包，可以简化应用程序设计，提高应用性能，实现信息隐藏、子程序
重载等面向对象语言所具有的功能。
一般是先编写独立的过程与函数，待其较为完善或经过充分验证无误后，再按
逻辑相关性组织为程序包。

2. 包的优点

模块化：使用包，可以封装相关的类型、对象和子程序。把一个大的功能模块
划分成多个小的功能模块，分别完成各自的功能，这样组织的程序易于编写，
理解和管理。
更轻松的应用程序设计：包规范部分和包体部分可以分别创建并编译。换言之
，我们可以在没有编写包体的情况下编写包规范的代码并进行编译。
信息隐藏：包中的元素可以分为公有元素和私有元素，公有元素可被程序包内
的过程、函数等访问，还可以被包外的PL/SQL访问。但对于私有元素只能被包
内的过程、函数等访问。对于用户，只需知道包规范，不用了解包体的具体细
节。
性能更佳：应用程序第一次调用程序包中的某个元素时，就将整个程序包加载
到内存中，当第二次访问程序包中的元素时，ORACLE将直接从内在中读取，而
不需要进行磁盘I/O操作而影响速度，同时位于内存中的程序包可被同一会话期
间的其它应用程序共享。因此，程序包增加了重用性并改善了多用户、多应用
程序环境的效率。
3. 包的定义

PL/SQL中的包由包规范和包体两部分组成。建立包时，首先要建立包规范，然
后再建立对包规范的实现–包体。

包规范用于声明包的公用组件，如变量、常量、自定义数据类型、异常、过程
、函数、游标等。包规范中定义的公有组件不仅可以在包内使用，还可以由包
外其他过程、函数使用。但需要说明与注意的是，为了实现信息的隐藏，建议
不要将所有组件都放在包规范处声明，只应把公共组件放在包规范部分。

包体是包的具体实现细节，它实现在包规范中声明的所有公有过程、函数、游
标等。也可以在包体中声明仅属于自己的私有过程、函数、游标等。
3.1 建立包规范
说明：

package_name：包名。

invoker_rights_clause：使用谁的权限运行，格式如下：

AUTHID { CURRENT_USER | DEFINER }
item_list_1：声明包的公用组件列表

{ type_definition -- 数据类型
| cursor_declaration -- 游标
| item_declaration -- 变量、常量等
| function_declaration -- 函数
| procedure_declaration -- 过程

示例：

CREATE OR REPLACE PACKAGE emp_mgmt AS
-- 函数
FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2,
manager_id NUMBER, salary NUMBER,
commission_pct NUMBER, department_id NUMBER)
RETURN NUMBER;
FUNCTION create_dept(department_id NUMBER, location_id NUMBER)
RETURN NUMBER;
-- 过程
PROCEDURE remove_emp(employee_id NUMBER);
PROCEDURE remove_dept(department_id NUMBER);
PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER);
PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER);
-- 异常
no_comm EXCEPTION;
no_sal EXCEPTION;
END emp_mgmt;
3.2 建立包体

5. 包中的游标

在包中使用无参游标，示例：

--定义包规范
CREATE OR REPLACE PACKAGE PKG_STU IS
CURSOR getStuInfo RETURN stuInfo%ROWTYPE;
END PKG_STU;

--定义包体
CREATE OR REPLACE PACKAGE BODY PKG_STU AS
CURSOR getStuInfo RETURN stuInfo%ROWTYPE IS
SELECT * FROM stuInfo;
END PKG_STU;

--调用包组件
BEGIN
FOR stu_Record IN PKG_STU.getStuInfo LOOP
DBMS_OUTPUT.PUT_LINE(‘学员姓名:‘||stu_Record.name||‘，学
号:‘||stu_Record.id||‘，年龄:‘||stu_Record.age);
END LOOP;
END;
在包中使用有参数的游标，示例：

--定义包规范
CREATE OR REPLACE PACKAGE PKG_STU IS
CURSOR getStuInfo(studentNo VARCHAR2) RETURN stuInfo%ROWTYPE;
END PKG_STU;

--定义包体
CREATE OR REPLACE PACKAGE BODY PKG_STU AS
CURSOR getStuInfo(studentNo VARCHAR2) RETURN stuInfo%ROWTYPE
IS
SELECT * FROM stuInfo WHERE id=studentNo;
END PKG_STU;

--调用包组件
BEGIN
FOR stu_Record IN PKG_STU.getStuInfo(2) LOOP
DBMS_OUTPUT.PUT_LINE(‘学员姓名:‘||stu_Record.name||‘，学
号:‘||stu_Record.id||‘，年龄:‘||stu_Record.age);
END LOOP;
END;
由于游标变量是一个指针，其状态是不确定的，因此它不能随同包存储在数据
库中，即不能在PL/SQL包中声明游标变量。但在包中可以创建游标变量参照类
型，并可向包中的子程序传递游标变量参数。

示例：

-- 创建包规范
CREATE OR REPLACE PACKAGE CURROR_VARIBAL_PKG AS
TYPE dept_cur_type IS REF CURSOR RETURN dept%ROWTYPE; --强类型

TYPE cur_type IS REF CURSOR;-- 弱类型

PROCEDURE proc_open_dept_var(
dept_cur IN OUT dept_cur_type,
choice INTEGER DEFAULT 0,
dept_no NUMBER DEFAULT 50,
dept_name VARCHAR DEFAULT ‘%‘);
END;

-- 创建包体
CREATE OR REPLACE PACKAGE BODY CURROR_VARIBAL_PKG
AS
PROCEDURE proc_open_dept_var(
dept_cur IN OUT dept_cur_type,
choice INTEGER DEFAULT 0,
dept_no NUMBER DEFAULT 50,
dept_name VARCHAR DEFAULT ‘%‘)
IS
BEGIN
IF choice = 1 THEN
OPEN dept_cur FOR SELECT * FROM dept WHERE deptno = dept_no;
ELSIF choice = 2 THEN
OPEN dept_cur FOR SELECT * FROM dept WHERE dname LIKE
dept_name;
ELSE
OPEN dept_cur FOR SELECT * FROM dept;
END IF;
END proc_open_dept_var;
END CURROR_VARIBAL_PKG;
定义一个过程，打开弱类型的游标变量：

--定义过程
CREATE OR REPLACE PROCEDURE proc_open_cur_type(
cur IN OUT CURROR_VARIBAL_PKG.cur_type,
first_cap_in_table_name CHAR)
AS
BEGIN
IF first_cap_in_table_name = ‘D‘ THEN
OPEN cur FOR SELECT * FROM dept;
ELSE
OPEN cur FOR SELECT * FROM emp;
END IF;
END proc_open_cur_type;
测试包中游标变量类型的使用：

DECLARE
dept_rec Dept%ROWTYPE;
emp_rec Emp%ROWTYPE;
dept_cur CURROR_VARIBAL_PKG.dept_cur_type;
cur CURROR_VARIBAL_PKG.cur_type;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘游标变量强类型：‘);
CURROR_VARIBAL_PKG.proc_open_dept_var(dept_cur, 1, 30);
FETCH dept_cur INTO dept_rec;
WHILE dept_cur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(dept_rec.deptno||‘:‘||dept_rec.dname);
FETCH dept_cur INTO dept_rec;
END LOOP;
CLOSE dept_cur;

DBMS_OUTPUT.PUT_LINE(‘游标变量弱类型：‘);
CURROR_VARIBAL_PKG.proc_open_dept_var(cur, 2, dept_name => ‘A
%‘);
FETCH cur INTO dept_rec;
WHILE cur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(dept_rec.deptno||‘:‘||dept_rec.dname);
FETCH cur INTO dept_rec;
END LOOP;

DBMS_OUTPUT.PUT_LINE(‘游标变量弱类型—dept表：‘);
proc_open_cur_type(cur, ‘D‘);
FETCH cur INTO dept_rec;
WHILE cur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(dept_rec.deptno||‘:‘||dept_rec.dname);
FETCH cur INTO dept_rec;
END LOOP;

DBMS_OUTPUT.PUT_LINE(‘游标变量弱类型—emp表：‘);
proc_open_cur_type(cur, ‘E‘);
FETCH cur INTO emp_rec;
WHILE cur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.empno||‘:‘||emp_rec.ename);
FETCH cur INTO emp_rec;
END LOOP;
CLOSE cur;
END;
6. 子程序重载

所谓重载时指两个或多个子程序有相同的名称，但拥有不同的参数变量、参数
顺序或参数数据类型。
在调用重载子程序时，主程序将根据实际参数的类型和数目，自动确定调用哪
个子程序。
PL/SQL允许对包内子程序和本地子程序进行重载。

 

第八章 使用PL/SQL 编写触发器

1. 介绍
触发器（trigger）是数据库提供给程序员和数据分析员来保证数据完整性的一
种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也
不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，
delete，update）时就会激活它执行。触发器经常用于加强数据的完整性约束
和业务规则等。
ORACLE触发器有三种类型，分别是：DML触发器、替代触发器和系统触发器。
DML触发器
顾名思义，DML触发器是由DML语句触发的。例如数据库的INSERT、UPDATE、
DELETE操作都可以触发该类型的触发器。它们可以在这些语句之前或之后触发
，或者在行级上触发(就是说对于每个受影响的行都触发一次)。
替代触发器
替代触发器只能使用在视图上，与DML不同的是，DML触发器是运行在DML之外的
，而替代触发器是代替激发它的DML语句运行。替代触发器是行触发器。
系统触发器
这种触发器是发生在如数据库启动或关闭等系统事件时，不是在执行DML语句时
发生，当然也可以在DDL时触发。
触发器功能强大，轻松可靠地实现许多复杂的功能，但是我们也应该慎用。为
什么又要慎用呢？触发器本身没有过错，但如果我们滥用，会造成数据库及应
用程序的维护困难。在数据库操作中，我们可以通过关系、触发器、存储过程
、应用程序等来实现数据操作，同时约束、缺省值也是保证数据完整性的重要
保障。如果我们对触发器过分的依赖，势必影响数据库的结构，同时增加了维
护的复杂程度。
2. 触发器组成
触发器主要由以下几个要素组成：
1. 触发事件：引起触发器被触发的事件。
2. 触发时间：触发器是在触发事件发生之前（BEFORE）还是之后
(AFTER)触发，也就是触发事件和该触发器的操作顺序。
3. 触发操作：触发器被触发之后的目的和意图，是触发器本身要做的事
情。
4. 触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了
符合触发条件的触发事件，才会执行触发操作。
5. 触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为
TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。
6. 触发频率：说明触发器内定义的动作被执行的频率。即语句级
(STATEMENT)触发器和行级(ROW)触发器：
语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；
行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据
，触发器都单独执行一次。
3. 创建触发器
BEFORE和AFTER指出触发器的触发时间分别为前触发和后触发方式，前触发是在
执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触
发当前所创建的触发器。
REFERENCING子句说明相关名称，在行触发器的PL/SQL块和WHEN子句中可以使
用相关名称参照当前的新、旧列值，默认的相关名称为OLD和NEW。触发器的
PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不
能加冒号。
NEW只在UPDATE、INSERT的DML触发器内可用，它包含了修改发生后被影响行的
值。
OLD只在UPDATE、DELETE的DML触发器内可用，它包含了修改发生前被影响行的
值。
FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现
在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的
每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器
将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省
略FOR EACH ROW 选项时，BEFORE和AFTER触发器为语句触发器，而INSTEAD OF
触发器则只能为行触发器。
WHEN子句说明触发约束条件。Condition为一个逻辑表达时，其中必须包含相关
名称，而不能包含查询语句，也不能调用PL/SQL函数。WHEN子句指定的触发约
束条件只能用在BEFORE和AFTER行触发器中，不能用在INSTEAD OF行触发器和
其它类型的触发器中。
INSTEAD OF选项（创建替代触发器）使ORACLE激活触发器，而不执行触发事件
。只能对视图和对象视图建立INSTEAD OF触发器，而不能对表、模式和数据库
建立INSTEAD OF触发器。
4. DML触发器
DML触发器对我们开发人员来说是最常用的。DML触发器是由数据库的INSERT、
UPDATE、DELETE操作触发，该类触发器可以在上述语句之前或之后执行，也可
以每个受影响的行执行一次。
条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组
合时，为了分别针对不同的事件进行不同的处理，需要使用ORACLE提供的条件
谓词：
1. INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。
2. UPDATING [（column_1,column_2,…,column_x）]：当触发事件是
UPDATE时，如果修改了column_x列，则取值为TRUE，否则为FALSE。
3. DELETING：当触发事件是DELETE时，则取值为TRUE，否则为FALSE。

5. 替代触发器
INSTEAD OF用于对视图的DML触发，由于视图有可能是由多个表联结(JOIN)而
成，因而并非所有的视图都是可更新的，但可以按照所需的方式执行更新。
创建INSTEAD OF触发器需要注意以下几点：
1. 只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项
。
2. 不能指定BEFORE或AFTER选项。
3. FOR EACH ROW子句是可选的。
4. 没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建
DML触发器就可以了。

6. 系统触发器
系统触发器可以在DDL或数据库系统上被触发，数据库系统事件包括数据库服务
器的启动或关闭，用户的登录与退出、数据库服务错误等。
系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建
立在模式(SCHEMA)之上时，只有模式所指定用户的DDL操作和它们所导致的错误
才激活触发器，默认时为当前用户模式。当建立在数据库(DATABASE)之上时，
该数据库所有用户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均
可激活触发器。

-- 创建记录用户登录注销日志的表
CREATE TABLE log_on_off_log
(user_name VARCHAR2(20),
logon_date timestamp,
logoff_date timestamp);

-- 创建登录触发器
CREATE OR REPLACE TRIGGER logon_trigger
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO log_on_off_log (user_name, logon_date) VALUES
(ora_login_user, systimestamp);
END logon_trigger;

-- 创建退出触发器
CREATE OR REPLACE TRIGGER logoff_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
INSERT INTO log_on_off_log (user_name, logoff_date) VALUES
(ora_login_user, systimestamp);
END logoff_trigger;

 

Oracle数据库之rownum

Oracle提供了一个rownum的伪列，它会根据返回记录生成一个序列化的数字。
rownum和rowid都是伪列，但是两者的根本是不同的。rownum是根据SQL查询出
的结果给每行分配一个逻辑编号，所以SQL不同也就会导致最终rownum不同；
rowid是物理结构上的，在每条记录INSERT到数据库中时，都会有一个唯一的物
理记录。
2. 限定查询行数
如果希望限定查询结果集的前几条数据，通过ROWNUM可以轻松实现。

4. 使用rownum的注意事项
1. 不能对rownum使用>（大于1的数值）、>=（大于1的数值）、=（大于
1的数值），否则无结果。
2. 在使用rownum时，只有当Order By的字段是主键时，查询结果才会先
排序再计算rownum，但是，对非主键字段（如：name）进行排序时，结果可能
就混乱了。出现混乱的原因是：oracle先按物理存储位置（rowid）顺序取出满
足rownum条件的记录，即物理位置上的前5条数据，然后在对这些数据按照
Order By的字段进行排序，而不是我们所期望的先排序、再取特定记录数。

 

Oracle数据库之视图与索引

1. 视图简介
视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对
表里面的数据进行查询和修改。
视图基于的表称为基表，视图是存储在数据字典里的一条SELECT语句。通过创
建视图可以提取数据的逻辑上的集合或组合。
我们可以像使用表一样使用视图，但需要注意的是：查询视图没有什么限制，
插入/更新/删除视图的操作会受到一定的限制；所有针对视图的操作都会影响
到视图的基表；为了防止用户通过视图间接修改基表的数据，可以将视图创建
为只读视图(带上with read only选项)。
2. 创建视图
FORCE：”强制”创建视图，不考虑基表是否存在，也不考虑是否具有使用基表
的权限。
alias：视图的列别名，别名的个数必须与SELECT查询中列的个数相同，如果
SELECT查询包含函数或表达式，则必须为其定义列别名。
subquery：查询语句。
READ ONLY：创建的视图只能用于查询数据，而不能用于更改数据。
CHECK OPTION：指定对视图执行的dml操作必须满足“视图子查询”的条件，即
对通过视图进行的增删改操作进行“检查”，要求增删改操作的数据，必须是
SELECT查询所能查询到的数据，否则不允许操作并返回错误提示。
3. 视图的优点
1. 简化对数据库的访问，因为视图可以有选择性的选取数据库里的一部
分。
2. 用户通过简单的查询可以从复杂查询中得到结果。
3. 维护数据的独立性，视图可从多个表中检索数据。
4. 对于相同的数据可产生不同的视图。
5. 提供各种数据表现形式，可以使用各种不同的方式将基表的数据展现
在用户面前，以便符合用户的使用习惯。
6. 提供安全性保证，视图提供了一种可以控制的方式，即可以让不同的
用户看见不同的列，而不允许访问那些敏感的列，这样就可以保证敏感数据不
被用户看见。
7. 简化用户权限的管理，可以将视图的权限授予用户，而不必将基表中
某些列的权限授予用户，这样就简化了用户权限的定义。
4. 索引
为了提高查询的速度，当用户对查询速度不满意而需要对数据库的性能进行调
校时，优先考虑建立索引。
适当的使用索引可以提高数据检索速度，可以给经常需要进行查询的字段创建
索引。
向表中“添加”行或从表中“删除”行时，必须花费额外的时间来更新该表的
索引，所以当需要从大表中检索少数几行时创建索引。一般我们认为当任何单
个查询要检索的行小于整个表总行数的10%时，索引就非常有用。
表的主键和唯一键将自动创建索引。

 

 

第九章 在JDBC中应用Oracle

JDBC 访问数据库需要六个步骤：
1. 注册并加载驱动程序
2. 创建连接。
3. 创建SQL语句对象
4. 提交SQL语句
5. 显示结果
6. 关闭连接

使用PreparedStatement对象能够方便在JAVA中执行PL/SQL匿名块
在访问子程序过程中需要处理输入、输出参数。
使用JDBC访问包中的子程序需要在子程序前加上包名和访问连接符(:)。
在JDBC中使用绑定变量，减少不必要的硬解析，提高执行效率。
使用JDBC的批量处理特性也可以执行所有类似的SQL语句以提高速度。

 

ORACLE数据库编程

标签：

本文系统来源：http://www.cnblogs.com/zzwx/p/4606237.html

内容总结

以上是互联网集市为您收集整理的ORACLE数据库编程全部内容，希望文章能够帮你解决ORACLE数据库编程所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---