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

时间:2022-01-29 00:09:53

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对象返回结果