积极啃书，触发器，PL/SQL面向对象编程

最新推荐文章于 2024-07-25 23:12:39 发布

羞儿

最新推荐文章于 2024-07-25 23:12:39 发布

阅读量113

点赞数 1

分类专栏：啃书《Oracle开发实战经典》文章标签： sql oracle 数据库开发数据库架构

本文链接：https://blog.csdn.net/weixin_43424450/article/details/119739467

版权

啃书《Oracle开发实战经典》专栏收录该内容

9 篇文章 1 订阅

订阅专栏

《Oracle开发实战经典》

DDL触发器

DDL事件	触发时机	描述
ALTER	before/after	修改对象的结构时触发
ANALYZE	before/after	分析数据库对象时触发
ASSOCIATE STATISTICS	before/after	启动统计数据库对象时触发
AUDIT	before/after	开启审核数据库对象时触发
COMMENT	before/after	为数据库对象设置注释时触发
CREATE	before/after	创建数据库对象时触发
DDL	before/after	针对出现的所有DDL事件触发
DISASSOCIATE STATISTICS	before/after	关闭统计数据库对象时触发
DROP	before/after	删除数据库对象时触发
GRANT	before/after	用户授权时触发
NOAUDIT	before/after	禁用审核数据库对象时触发
RENAME	before/after	为数据库重命名时触发
REVOKE	before/after	用户撤销权限时触发
TRUNCATE	before/after	截断数据表时触发

事件属性函数	描述
ora_client_ip_address	取得客户端IP地址，如果是本地连接则返回null，返回的数据类型为varchar2
ora_database_name	取得数据库名，返回类型为varchar2
ora_des_encrypted_password	取得加密口令，返回varchar2
ora_dict_obj_name	取对象名称，返回数据类型varchar2
ora_dict_obj_name_list(nameList out ora_name_list_t)	返回被修改的对象列表
ora_dict_obj_owner	返回对象的拥有者，varchar2
ora_dict_obj_owner_list(objlist out ora_name_list_t)	返回修改对象的所有者列表
ora_dict_obj_type	返回对象类型
ora_grantee(nameList out ora_name_list_t)	返回授权的权限和角色列表
ora_instance_num	取得当前数据库的实例编号
ora_is_alter_column(columnname varchar2)	判断一个列名是否被修改，返回布尔类型
ora_iscreating_nested_table	如果创建一个嵌套表，返回的数据类型书布尔
ora_is_servererror(errorCodeNumber)	判断是否出现了一个指定的错误编号，返回数据类型是布尔
ora_login_user	取得当前模式名，返回的数据类型是varchar2
ora_revoke(namelist out ora_name_list_t)	返回撤销的权限和角色列表
ora_server_error(point number)	返回错误堆栈信息中的错误号，其中1为错误堆栈顶端，返回的数据类型为number
ora_sysevent	返回触发器的操作事件，返回的数据类型为varchar2
ora_server_error_msg	返回错误堆栈信息中的错误信息，其中1为错误堆栈顶端，返回的数据类型为varchar2
ora_is_drop_column(columnname varchar2)	判断一个列名是否被删除，返回布尔类型

系统触发器

系统触发器用于监控数据库服务的打开和关闭，错误信息等的取得，或是监控用户行为等操作。

CREATE [OR REPLACE] TRIGGER 触发器名称
    [BEFORE | AFTER] [数据库事件] ON [DATABASE | SCHEMA]
    [WHEN 触发条件]
    [DECLARE]
        [程序声明部分 ;]
    BEGIN
        程序代码部分 ;
    END [触发器名称] ;

事件	触发时机	描述
STARTUP	AFTER	数据库实例启动之后触发
SHUTDOWN	BEFORE	数据库实例关闭之前触发
SERVERERROR	AFTER	出错误时触发
LOGON	AFTER	用户登录后触发
LOGOFF	BEFOR	用户注销前触发

使用管理登录，查看user_log表
- ```
SELECT * FROM user_log;
```

管理触发器

触发器是一种特殊的存储过程，从被创建之日起，触发器就被存储在数据库中，直到被删除。触发器与一般存储过程或者存储函数的区别在于触发器可以自动执行，而一般的存储过程或者存储函数需要调用才能执行。

Oracle提供检索与触发器相关信息的数据字典。与触发器相关的数据字典主要包括以下几种：

数据属性	描述
USER_TRIGGERS	存储当前用户所拥有的触发器。
DBA_TRIGGERS	存储管理员所拥有的触发器。
ALL_TRIGGERS	存储所有的触发器。
USER_OBJECTS	存储当前用户所拥有的对象，包括触发器。
DBA_OBJECTS	存储管理员所拥有的对象，包括触发器。
ALL_OBJECTS	存储数据库中所有的对象，包括触发器。

启用和禁用触发器
- ```
ALTER TRIGGER [schema.] trigger_name DISABLE | ENABLE;
```
- Oracle提供的ALTER TRIGGER语句用于启用和禁用触发器。禁用触发器就将它挂起，它仍然存储在数据库中，但是不会被触发，就好像根本没有触发器一样。禁用触发器常常用在当DBA有大量记录要导入到数据库中时，且DBA知道这些数据时安全和可靠的。但禁用触发器后可以节省大量的处理时间。
修改触发器
- 当创建的触发器需要修改时可以使用REPLACE关键字。如果在SQL Plus中创建触发器时带上了OR REPLACE关键字，可以完成过程的修改，也就是覆盖。
删除触发器
- 对应不用的触发器除了设置禁用属性外也可以直接将其删除。
- ```
DROP TRIGGER [schema.]trigger_name;
```

USER_TRIGGERS表主要列的说明如下表：

列名	描述
TRIGGER_NAME	触发器名
TRIGGER_TYPE	触发时间AFTER和BEFORE，以及是行级触发器或语句级触发
TRIGGERING_EVENT	触发器的事件
TABLE_NAME	与触发器相关联的表或视图
BASE_OBJECT_TYPE	与触发器相关联的对象类型：表、视图、模式和数据库
WHEN_CLAUSE	触发器的WHEN子句代码
STATUS	触发器的状态：启动或禁用
DESCRIPTION	类型或事件信息的概述
TRIGGER_BODY	触发器的PL/SQL块

触发器调用子程序
- 一个触发器只能最多32kb大小的代码，这时如果编写代码过多，则可以考虑代码定义在过程或者函数中，触发器只需要完成调用即可。

动态SQL

Oracle编译PL/SQL程序块分为两个种：其一为前期联编（early binding），即SQL语句在程序编译期间就已经确定，大多数的编译情况属于这种类型；另外一种是后期联编（late binding），即SQL语句只有在运行阶段才能建立，例如当查询条件为用户输入时，那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定，只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。通常，静态SQL采用前一种编译方式，而动态SQL采用后一种编译方式。
理解了动态SQL编译的原理，也就掌握了其基本的开发思想。动态SQL既然是一种”不确定”的SQL，那其执行就有其相应的特点。Oracle中提供了Execute immediate语句来执行动态SQL，语法如下：
- ```
Excute immediate 动态SQL语句 using 绑定参数列表 returning into 输出参数列表;
```
- 动态SQL是指DDL和不确定的DML（即带参数的DML）
- 绑定参数列表为输入参数列表，即其类型为in类型，在运行时刻与动态SQL语句中的参数（实际上占位符，可以理解为函数里面的形式参数）进行绑定。
- 输出参数列表为动态SQL语句执行后返回的参数列表。
- 由于动态SQL是在运行时刻进行确定的，所以相对于静态而言，其更多的会损失一些系统性能来换取其灵活性。
- 动态SQL编写技巧：
  - 尽量使用类似的SQL语句，这样Oracle本身通过SGA中的共享池来直接对该SQL语句进行缓存，那么在下一次执行类似语句时就直接调用缓存中已解析过的语句，以此来提高执行效率。
  - 当涉及到集合单元的时候，尽量使用批联编。
  - 使用NOCOPY编译器来提高PL/SQL性能。缺省情况下，out类型和in out类型的参数是由值传递的方式进行的。但是对于大的对象类型或者集合类型的参数传递而言，其希望损耗将是很大的，为了减少损耗，可以采用引用传递的方式，即在进行参数声明的时候引用NOCOPY关键字来说明即可到达这样的效果。
  - EXECUTE IMMEDIATE将不会提交一个DML事务执行，应该显式提交如果通过EXECUTE IMMEDIATE处理DML命令，那么在完成以前需要显式提交或者作为EXECUTE IMMEDIATE自己的一部分. 如果通过EXECUTE IMMEDIATE处理DDL命令,它提交所有以前改变的数据;
  - 不支持返回多行的查询,这种交互将用临时表来存储记录(参照例子如下)或者用REF cursors.
  - 当执行SQL语句时，不要用分号，当执行PL/SQL块时，在其尾部用分号.
execute immediate语句
- 在动态sql中最重要的执行命令，使用此语句可以方便地在PL/SQL程序执行DML（insert，update，delete），DDL（create,alter,drop）,DCL(grant,revoke)
- ```
execute immediate 动态sql字符串[[bulk collect] into 自定义变量，...|记录类型]
[using [in|out|in out]绑定参数，...]
[[returning|return][bulk collect]into 绑定参数，...]；
```
- into：保存动态sql执行结果，如果返回多行记录，则可以通过bulk collect设置批量保存
- using:用动态sql设置占位符设置内容
- returning|return：使用效果一样，是取得更新表记录被影响的数据，通过bulk collect来设置批量绑定
设置绑定变量
- 在使用动态sql，可以在定义sql字符串里采用占位符的方式设置绑定变量，所设置的绑定变量需要在运行时动态使用using语句设置占位符的内容，而设置绑定变量的方式采用“：占位符名称”的方式表示
- 如果字段为null,则不能直接绑定null,需要通过变量设置
- 批量绑定
  - 通过动态sql进行查询或更新操作，每次想数据库提交一条操作语句，如果现在希望数据库可以一次性接收多条sql，以及数据库可以一次性将操作结果返回到某一个集合中，就可以采用批量处理操作来完成。主要依靠bulk collect进行操作。
  - 使用forall设置多个参数
    - ```
    forall 索引变量 in 参数集合最小值 .. 参数集合最大值
    execute immediate 动态sql字符串
    [using 绑定参数|绑定参数（索引），...]
    [[returning|return] bulk collect into 绑定参数集合，...]；
```
- 处理游标操作
  - 打开游标变量时需要使用using子句
  - ```
  open 游标变量名 for 动态sql [using 绑定变量，绑定变量，...]
```
  - 利用批处理可以在fetch语句中利用bulk collect 一次性将多个数据保存到集合类型
  - ```
  fetch 动态游标 bulk collect into 集合变量 ...；
```

面向对象编程

自Oracle9i以来，Oracle就不再是单纯的关系数据库管理系统，它在关系数据库模型的基础上，添加了一系列面向对象的特性。Oracle9i就号称面向对象数据库。Oracle的对象体系遵从面向对象思想的基本特征，许多概念同C++、JAVA中类似，具有继承，重载，多态等特征，但又有自己的特点。
封装性
- 是某些程序结构对外部不可见，如果需要访问被封装的程序结构，就需要通过某些特定的程序结构。
继承性
- 可以在现存的程序结构上继续扩充新的功能，或者是将某一规范定义得更加严格
多态性
- 在继承得基础上，不同得子类型创建不同得对象，会针对同一类型操作有不同实现方式。
面向对象类型TYPE，在PL/SQL语言中，面向对象的程序设计是基于对象类型。
对象类型的定义包括对象类型头（或称为对象规范，specification）和对象类型体（body）。对象类型头包括对象类型的属性、函数和过程的声明，而对象类型体则是对象类型具体的实现，即函数和过程的实现。所以如果对象类型中只有属性，不含函数和过程的话，就只要声明对象类型头就可。

对象类型头声明语法：

CREATE OR REPLACE TYPE type_name AS OBJECT(
              --属性的声明
              propertyname1     TYPE1,
              propertyname2     TYPE2,
              ...
              properynamen     TYPEn,

              --函数的声明
              member function funcname1(param1 TYPE1, ...) return TYPE11,
              static function funcname2(param1 TYPE2, ...) return TYPE22,
              ...

              --过程的声明
              member procedure  procname1(param1 TYPE1, ...),
              static procedure  procname2(param1 TYPE2, ...),
              ...
      );

*对象类型体定义语法：*

CREATE OR REPLACE TYPE BODY type_name  --No 'AS OBJECT'
      AS  --NO 'BEGIN'
          member function funcname1 return TYPE11
          IS
             //变量定义
          BEGIN
             //处理过程
             return var1;
          END funcname1;      
          static function funcname2 return TYPE22
          IS
             //变量定义
          BEGIN
             //处理过程
             return var2;
          END funcname2;
          ...
          member procedure procname1(param1 TYPE1,...)
          IS
             //变量定义
          BEGIN
             //处理过程
          END procname1;
          static procedure procname2(param1 TYPE2,...)
          IS
             //变量定义
          BEGIN
             //处理过程
          END procname2;
          ...
       END;

方法有成员方法和静态方法，过程也有成员过程和静态过程。成员方法和成员过程通过关键字member标识，静态方法
和静态过程通过关键字static标识。
静态方法或静态过程能直接被对象类型调用，但不能被对象实例调用（区别java）。成员方法或成员过程只能被对
象类型的实例调用，不能直接被对象类型调用。
在静态方法或过程中不能访问对象类型的属性。

可变数组AS VARRAY(n) OF
- 可变数组，是一种集合。一个可变数组是对象的一个集合，其中每个对象都具有相同的数据类型。可变数组的大小由创建时决定。在表中建立可变数组后，可变数组在主表中作为一个列对待。
- ```
CREATE OR REPLACE TYPE varray_name AS VARRAY(n) OF type;
```
- varray_name ：数组名。
- n：数组的包含元素的最大个数。如果插入数组的元素大于这个上限，将会报错。
- type：数组的类型，可以是基本变量，如varchar2，integer等，也可以是自定义的对象类型，如上面定义的NAME_TYPE。
- first属性、last属性和count属性分别获得数组的首元素、末元素和总数。如有一个表记录变量v_student_tbl，那么v_student_tbl.first获得是表记录的首元素。在进行FOR或FORALL循环时比较常用。
嵌套表AS TABLE OF
- 嵌套表是表中之表，一个嵌套表是某些行的集合，它在主表中表示为其中的一列。对主表中的每一条记录，嵌套表可以包含多个行。
- ```
CREATE OR REPLACE TYPE table_name AS TABLE OF type;
```
- table_name ：嵌套表名。
- type：数组的类型，可以是基本变量，如varchar2，integer等，也可以是自定义的对象类型，如上面定义的NAME_TYPE。
- first属性、last属性和count属性分别获得表记录（或称为数组）的首元素、末元素和总数。如有一个表记录变量v_student_tbl，那么v_student_tbl.first获得是表记录的首元素。在进行FOR或FORALL循环
继承-Oracle中面向对象特征
- 继承父类的子类对象类型将有父类的所有属性、方法和过程。父类型必须声明为NOT FINAL,子类型使用关键字UNDER。
- 如果父类没有声明为NOT FINAL，子类在继承的时候将报错：Error: PLS-00590: attempting to create a subtype UNDER a FINAL type。
重写overriding
- 重写就是在子类中对父类又有的方法或过程重新实现。重写关键字为overriding，在子类中把要重写的方法或过程声明和实现之前加上该关键字。
对象表
- 对象表是指该表的一行都是一个对象（对象类型的实例），每个对象有一个OID(object ID)。
- ```
CREATE TABLE table_name OF object_type;
```
- table_name：对象表名称，执行创建对象表语句后，数据库中将会生成一个名字为table_name的表。
- object_type：对象类型，生成的表的字段和对象类型时对应的。
在进行数据对象表查询时，用户还可以利用value函数或ref函数进行数据的查询
- value（）函数：可以将对象表中的数据转换为对象返回，这样就可以利用查询后的对象信息进行排序。
- ref()函数：利用value函数是将嵌套的对象直接保存在对象中，但这样的作法很多时候就会造成数据冗余，可以创建类时使用ref设置引用关系，之后才可以在创建表对象使用此应用类型。
对象表的关联
- 对象表之间没有主外键关联的概念，为了体现这层关系，oracle中用了ref对象来实现。
- ref操作符：声明引用类型。如 name ref NAME_TYPE, 变量或字段name就是引用类型，存储NAME_TYPE型对象的OID。
- ref(表的别名)函数：获得对象表中对象OID值，如select ref(a) from otable a。
- deref(OID)函数：通过OID找到并返回行对象表中对象。

定义对象类型——类

对象规范（类规范）：定义对象得公共操作标准，如公共属性和子程序

 create [or replace] type 类规范名称
 [authid current_user|definer]
 [is|as] object|under 父规范类型名称（
 	属性名称 数据类型，...
 	[map|order] member 函数名称，
 	[final|notfinal] member 函数名称，
 	[instantiable|notinstantiable] member 函数名称，
 	constructor member 子程序名称，...
 	overriding member 子程序名称，...
 	[member|sratic] 子程序，...
 ）[final|nofinal]
 [instantiable|notinstantiable];
 /

create [or replace] type 类规范名称:用于定义类规范
[authid current_user|definer]：此类得使用授权
[is|as] object|under 父规范类型名称:使用object表示定义一个新对象，如果使用under，则表示要定义指定父类对象规范得子类对象规范
属性名称数据类型，…：定义若干组成属性
[map|order] member 函数名称，：定义该函数是否用于对象之间得比较
[final|notfinal] member 函数名称，：如果定义final，则表示子类实例不可以复写这个函数，而nofinal表示子类可以覆写
[instantiable|notinstantiable] member 函数名称，：表示这个函数是否可以实例化对象调用，其中instantiable表示此函数可以被实例化，可以通过对象调用，而notinstantiable则表示这个函数专门用于子类重载函数使用，实例化对象无法
constructor member 子程序名称，…：定义构造方法
overriding member 子程序名称，…：进行函数覆写
[member|sratic] 子程序，…：定义函数，其中member定义得函数表示由实例化对象调用，如果是static定义得函数表示有类进行调用
[final|nofinal]：如果是final,表示此类不允许有子类，nofinal表示可以有子类
[instantiable|notinstantiable];：此类是否可以被实例化

结构	PL/SQL	java
类名称	create [or replace] type 类规范名称	public\|protected class 类名称
属性	属性名称数据类型，…	[public\|protected\|private] 数据类型属性名称
排序	[map\|order] member 函数名称	实现comparable或是comparator
方法（函数）	[final\|notfinal] member 函数名称	[public\|protected\|private] [final] 返回类型方法名称(参数列表)
抽象方法	[instantiable\|notinstantiable] member 函数名称	[public\|protected\|private] abstract返回类型方法名称(参数列表)
普通方法和静态方法	[member\|static] 子程序名称	[public\|protected\|private] [final] [static] 返回类型方法名称(参数列表)
不能被继承得父类	final\|nofinal	[public\|protected] final class 类名称

对象体（类体）：实现对象类型规范中得公共子程序

create [or replace] type body 对象规范名称
[as|is]
[map|order] member 函数体；
[member|static] 子程序体，...
end;
/

删除类型
- ```
drop type 类型名称
```

定义函数
- member函数，该函数通过对象进行定义，使用member定义得函数可以利用self关键字访问类中得属性内容。Oracle中self关键字与Java中的this关键字有一样的作用。
- static函数：该函数独立于类之外，可以直接通过类名进行调用，使用static定义得函数无法范文类的属性
构造函数
- 当创建一个类之后，希望可以自动完成某些操作，例如对对象函数赋值，则可以利用构造函数完成。
- - 构造函数名必须与类名相同
  - 构造函数必须使用constructor关键字进行定义
  - 构造函数必须定义返回值，且返回值类型必须self as result
  - 构造函数也可以被重载，重载的构造函数参数的类型和个数不同。
定义map和order函数
- map函数：用于定义的函数将会按照用户定义的数据组合值区分大小，然后利用order by子句进行排序
- order函数：order函数与map函数类似，也是定义了一个排序规则，在进行数据排序时会默认调用，同时order函数还可以比较两个对象的大小关系，所以如果要比较多个对象时order函数会被重复调用，性能不如map函数
- map函数和order函数调用只能二选一
对象的多态性
- 函数的多态性：
  - 体现为函数的重载和覆写
- 对象的多态性:
  - 子类对象可以为父类对象实例化
定义抽象函数
- 当定义一个类后，默认情况下可以直接利用此类的实例化对象进行类中结构的操作。如果现在类中的函数不希望被类对象直接使用，而需要通过继承其子类来实现，就可以在定义函数时使用农not instantiable 标记即可，这样的函数就被称为抽象函数。同时包含抽象函数所在的类也必须使用not instantiable定义，这样的类同样被称为抽象类。
对象视图
- ```
create or replace view 视图名称 of 类
	with object identifier(主键对象)
as 子查询；
```
- 利用对象视图，可以将指定视图查询的数据按照顺序填充到相应对象的属性中，这样用户在操作的时候就可以直接将数据以对象形式返回。

羞儿

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
积极啃书，触发器，PL/SQL面向对象编程

《Oracle开发实战经典》DDL触发器DDL事件触发时机描述ALTERbefore/after修改对象的结构时触发ANALYZEbefore/after分析数据库对象时触发ASSOCIATE STATISTICSbefore/after启动统计数据库对象时触发AUDITbefore/after开启审核数据库对象时触发COMMENTbefore/after为数据库对象设置注释时触发CREATEbefore/after创建数据
复制链接

扫一扫