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

9/6/2015来源:Java教程人气:1060

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

1. 子程序的各个部分:

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

2.子程序的分类:

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 能

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

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

20.JDBC连接Oracle数据库

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

Connection con = null;

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

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

A.步骤:

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

b. 创建PreparedStatemen对象

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

d. 执行PreparedStatement对象

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

A. 语法:

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

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

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

B.步骤:

a. 明确CallableStatement调用字符串

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

c. 绑定输入(IN)参数

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

e. 执行CallableStatement对象返回结果