开发PL/SQL子程序和包及使用PL/SQL编写触发器、在JDBC中应用Oracle

1. 子程序的各个部分：

声明部分、可执行部分、异常处理部分(可选)

A. 过程 － 执行某些操作

a. 创建过程的语法：

CREATE [OR REPLACE] PROCEDURE <procedure name> [(<parameter list>)] IS|AS

<local variable declaration>

BEGIN

<executable statements>

[EXCEPTION

<exception handlers>]

END;

b. 过程参数的三种模式：

IN：用于接受调用程序的值、默认的参数模式

OUT：用于向调用程序返回值

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

c. 将过程的执行权限授予其他用户：

SQL> GRANT EXECUTE ON find_emp TO MARTIN;

SQL> GRANT EXECUTE ON swap TO PUBLIC;

d. 删除过程：SQL> DROP PROCEDURE find_emp;

B. 函数 － 执行操作并返回值

a. 创建函数的语法：

CREATE [OR REPLACE] FUNCTION <function name> [(param1,param2)] RETURN <datatype> IS|AS [local declarations]

BEGIN

Executable Statements;

RETURN result;

EXCEPTION

Exception handlers;

END;

b. 定义函数的限制：

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

形参不能是 PL/SQL 类型

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

c.访问函数的两种方式：使用 PL/SQL 块、使用 SQL 语句

3. 子程序的优点：

模块化、可重用性、可维护性、安全性

4. 程序包：

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

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

a.规范：声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等

b.主体：声明程序包私有对象和实现在包规范中声明的子程序和游标

B . 创建程序包

a. 程序包规范

CREATE [OR REPLACE] PACKAGE package_name IS|AS

[Public item declarations] [Subprogram specification]

END [package_name];

b. 程序包主体

CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS

[Private item declarations]

[Subprogram bodies]

[BEGIN Initialization]

END [package_name];

5. 程序包的优点

a. 模块化

b. 更轻松的应用程序设计

c. 信息隐藏

d. 新增功能

e. 性能更佳

6. 程序包中的游标

A. 游标的定义分为游标规范和游标主体两部分

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

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

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

b. 程序员定义的记录类型

7. 有关子程序和程序包的信息

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

SELECT object_name, object_type FROM USER_OBJECTS WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY');

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

SELECT line, text FROM USER_SOURCE WHERE NAME='TEST';

8. 触发器：

是当特定事件出现时自动执行的存储过程（特定事件可以是执行更新的DML语句和DDL语句）

注意：触发器不能被显式调用

9. 触发器的功能：

自动生成数据、自定义复杂的安全权限、提供审计和日志记录、启用复杂的业务逻辑

10. 创建触发器的语法

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;

11. DML触发器的相关概念

A. BEFORE触发器：是在某种操作发生之前执行的触发器

B. AFTER触发器：是在某种操作发生之后执行的触发器

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

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

E. NEW伪记录：只在UPDATE和INSERT DML触发器内可用，它包含了修改发生后被 影响的行的值

F. OLD伪记录：只在UPDATE和DELETE DML触发器内可用，它包含了修改发生前被 影响的行的值

G. WHEN子句：某段代码是否被执行

12.触发器由三部分组成：

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

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

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

13. DML触发器：

当表发生数据修改事件(增加、删除、修改)自动执行的PL/SQL 块

语法:

CREATE OR REPLACE TRIGGER 触发器名称{AFTER|BEFORE } --指定触发时机{INSERT|DELETE|UPDATE}--指定触发事件类型ON 表名--指定所监控的表{FOR EACH ROW} --指定触发次数

------上面：执行条件--------------下面：执行代码----------------declare

BEGIN 代码;END;

14. 条件谓词

INSERTING、UPDATING、DELETING

15. 触发器应用

使用场合：控制数据安全、实现数据审计、实现数据完整性、实现参照完整性

16. INSTEAD OF触发器（替代触发器）

17.系统事件触发器

18.管理触发器

A. 启用和禁用触发器：何时禁止：数据导入导出时禁止

SQL> ALTER TRIGGER aiu_itemfile DISABLE;

SQL> ALTER TRIGGER aiu_itemfile ENABLE;

B. 重新编译触发器：数据库对象发生改变后

C. 删除触发器

SQL> DROP TRIGGER aiu_itemfile;

19. 触发器和存储过程

触发器 存储过程

编写 无参数，返回值 有参数，有返回值(参数)

编写 不能commit或rollback 能

调用方式 事件发生时自动调用 手工调用

操作对象 基于某张表 与表无关

Class.forName(“oracle.jdbc.driver.OracleDriver”);

Connection con = null;

con = DriverManager.getConnection(jdbc:oracle:thin:@localhost:1521:数据库实例);

21. 在JDBC中执行PL/SQL匿名块

a. 明确PreparedStatement执行匿名块的字符串

b. 创建PreparedStatemen对象

c. 根据需要设定绑定变量

d. 执行PreparedStatement对象

22. 在JDBC中执行PL/SQL的存储过程

A. 语法：

a. {call 过程名[(?,?,?....)]} //访问带参数的存储过程

b. {? = cal 过程名[(?,?,?.......)]} //访问返回结果参数的存储过程

c. {call 过程名} //访问不带参数的存储过程

a. 明确CallableStatement调用字符串

b. 创建CallableStatement对象（ CallableStatement = Connection.prepareCall(sql);）

c. 绑定输入（IN）参数

d. 注册输出（OUT）参数(CallableStatement .registerOutParameter(索引, OracleTypes.数据类型))

e. 执行CallableStatement对象返回结果