过程、函数、触发器和包

转载：https://blog.csdn.net/ithdq/article/details/79420289

Oracle提供了四种可以存储的PL/SQL程序块：过程、函数、触发器和包。

1、存储过程

存储过程是一种命名的PL/SQL程序块，通常没有返回值，只能同EXECUT命令执行或在PL/SQL程序块内部被调用，执行效率高。

其语法如下：

create [or replace] procedure 存储过程名称 [参数]ia|as

begin

PL/SQL语句；

[exception]

[异常处理语句]

end 存储过程名称；

补充：

SQL修改表字段长度：

语法： alter table 表名 modify 字段名(字符类型(度))

范例：

执行存储过程：EXECUTE [EXEC] 存储过程名

范例：

 存储过程参数

   存储过程参数模式有：IN、OUT和IN OUT 3种。

（1）IN模式参数

     此模式参数是一种输入类型参数，参数值由调用方传入，并且只能被存储过程读取。默认参数模式。

范例：

在Oracle中IN模式参数可以初始化默认值进行操作即：

并且，建议使用“指定名称传递”的方式传值。

补充：在Oracle中可以使用

       create table 新表名 as select * from 旧表名

       将一张表的数据复制到另一张表中，但是其只复制数据本身，并未复制其关系和约束等。

（2）OUT模式

     OUT模式是一种输出类型参数

 范例：

执行方法一：

执行方法二：

如上面实验结果，执行方法二用户看不了变量var_dname,var_loc的值，可采用print命令或select语句来输出变量的值。

 ( 3 ) IN OUT模式

     IN OUT模式同时具备IN和OUT的优点。

范例：

2、函数

    函数一般用于计算和返回一个值，用于计算或功能实现。

（1）创建函数

     语法：

     create  [or replace]  function  函数名称  [函数参数]  return 返回值类型 is

     [函数内部变量]

     begin

     PL/SQL语句；

     [exception]

     异常处理代码

     end [函数名称]；

     注意返回值类型要与函数声明是的返回值类型一样。

（2）调用函数

      必须使用一个变量来保存返回值

范例：

（3）删除函数

     drop function 函数名称；

补： 查看Oracle里的函数：

3、触发器

（1）触发器概述

    触发器可以看作一种特殊的存储过程，通常用于管理复杂的完整性约束，或监控对表的修改、或通知其它的程序，甚至可以实现对数据的审计功能。

触发事件：引起触发器运行的操作

触发器语法格式：

create [or replace] trigger 触发器名称

[before | after | instead of] 触发事件

on 操作的数据表 | 视图 | 用户模式 | 数据库

[for each row] [when 触发条件表达式]

begin

PL/SQL语句；

end 触发器名称；

（2）语句触发器

     针对一条DML语句而引起的触发器执行，该触发器不使用 for each row字句，也就是无论数据操作影响多少行，触发器都只会执行一行。

范例：

（3）行级触发器

     行级触发器针对DML操作所影响的每一行数据都执行一次触发。在行级触发器必须使用for each row。

范例：

（4）替换触发器

     替换触发器，其关键字为instead of 触发器，定义在视图。

详情见视图。

（5）用户事件触发器

     用户事件触发器因进行DDL操作和用户登录，退出等操作而引起的一种触发器。

范例：

补充常用事件属性

常用事件属性函数

ora_client_ip_address	用于返回客户端的IP地址
ora_database_name	用于返回当前数据库名
ora_des_encrypted_password	用于返回DES加密后的用户口令
ora_dict_obj_name	用于返回DDL操作所对应的数据库对象名
ora_dict_obj_name_list(name_list_ OUT ora_name_list_t)	用于返回在事件中被修改的对象名列表
ora_dict_obj_owner	用于返回DDL操作所对应的对象的所有者名
ora_dict_obj_ower_list(ower_list OUT ora_name_list_t):	用于返回在事件中被修改对象的所有者列表
ora_dict_obj_type	用于返回DDL操作所对应的数据库对象的类型
ora_grantee(user_list OUT ora_name_list_t)	用于返回授权时事件授权者
ora_instance_num	用于返回Oracle实例号
ora_is_alter_column(column_name IN VARCHAR2)	用于检测特定列是否被修改
ora_is_creating_nested_table	用于检测是否正在建立嵌套表
ora_is_drop_column(column_name IN VARCHAR2)	用于检测特定列是否被删除
ora_is_servererror(error_number)	用于检测是否返回了特定Oracle错误
ora_login_user	用于返回登录用户名
ora_sysevent	用于返回触发 触发器的系统时间名

4、程序包

   程序包由PL/SQL程序元素（如变量、类型）和匿名PL/SQL程序块（游标）、命名PL/SQL块（如存储过程和函数）组成。

   程序包在结构上由规范和包主体组成。

（1）程序包规范

     其语法格式：

     craete [or replace] package 程序包名 is

     [声明变量];

     [声明类型]；

     [定义游标]；

     [定义函数]；

     [声明存储过程]；

     end 程序包名；

 范例：

（2）程序包主体

     程序包主体包含了在规范中声明的游标、过程和函数等实现代码。其中，也可声明变量。要求程序包规范名字要与主体一致。

其语法格式：

      craete [or replace] package body 程序包名

      [主体内部变量]

      [游标主体]

      [在规范中定义函数]

    [{  begin    

       PL/SQL语句      

       [异常处理]

       end 函数名；

    }]

      [在规范中定义存储过程]

      [{  begin    

       PL/SQL语句      

       [异常处理]

       end 存储过程名；

    }]

 ……

end 程序包名;

范例：

执行该程序包结果如下：