PLSQL新手新手向入门修炼（2）

由于本人对plSQL理解有限，如果文章中出现什么问题，麻烦大家帮我指出来，攻城狮之路，互勉以修远。
本篇文章主要就以下几点来进行展开
（1）plSQL中的package
（2）plSQL中对大对象的操作
（3）plSQL中的触发器

1.plSQL中的package

对于plSQL中的 package 简单来说就是一个对存储过程，函数进行集成，而后再调用的一个过程，通过使用 package 很大程度上提高代码的复用程度。

（1）首先我们先对 package 的写法来进行展开

package 分为两块，一块是包头代码段，一块是package body 代码段

1）包头代码段

create or replace package package_name
is
公共变量的定义；
公共类型的定义；
公共游标的定义；
函数( function )说明；
存储过程( procedure )说明；
end package_name;

2)package body 代码段

create or replace package body package_name
is
定义私有变量，类型，游标；
定义私有/公共函数（function）；
定义私有/公共存储过程（procedure）；
begin
初始化代码段
end package_name;

就以上格式我们来举个栗子：
1）包头代码段

CREATE OR REPLACE PACKAGE PLSQL_TEST1 IS

  C_ST_NO NUMBER(5) := 1;

FUNCTION ST_MAX_NO(V_ST_NO IN NUMBER) RETURN NUMBER;

PROCEDURE ST_MIN_NO(C_ST_NO OUT NUMBER);

END PLSQL_TEST1;

2)package body 代码段

CREATE OR REPLACE PACKAGE BODY PLSQL_TEST1 IS
  M_ST_NO NUMBER(5);
FUNCTION ST_MAX_NO(V_ST_NO IN NUMBER) RETURN NUMBER IS
F_ST_NO NUMBER(5);
BEGIN
SELECT MAX(TO_NUMBER(SUBSTR(S.STUDENT_NO, 2, 3)))
INTO F_ST_NO
FROM HAND_STUDENT S;
    RETURN F_ST_NO;
END ST_MAX_NO;

PROCEDURE ST_MIN_NO(C_ST_NO OUT NUMBER) IS
BEGIN
SELECT MIN(TO_NUMBER(SUBSTR(S.STUDENT_NO, 2, 3)))
INTO M_ST_NO
FROM HAND_STUDENT S;
    DBMS_OUTPUT.PUT_LINE(M_ST_NO);
    C_ST_NO := M_ST_NO;
    DBMS_OUTPUT.PUT_LINE(C_ST_NO);
END ST_MIN_NO;
END PLSQL_TEST1;

3)匿名块中的调用

DECLARE
  V_ST_NO  NUMBER(5) := 0;
  V_MIN_NO NUMBER(5) := 0;
  V_MAX_NO NUMBER(5) := 0;
BEGIN
  PLSQL_TEST1.ST_MIN_NO(V_ST_NO);
  V_MIN_NO := PLSQL_TEST1.C_ST_NO;
  V_MAX_NO := PLSQL_TEST1.ST_MAX_NO(V_ST_NO);
  DBMS_OUTPUT.PUT_LINE(V_MIN_NO || ' ' || V_MAX_NO);
END;

就我们在使用 package 的时候有以下几点需要注意：
1）当package中有一个function或者procedure被调用时，整个包都会被加载到内存中，所以尽量要避免巨无霸package，否则不但不能提高效率，反而会加剧系统消耗；
2）一个package中可以重载function 或者 procedure；
3）package 是具有隐藏性的，在body中定义变量的都是private属性，在package声明中定义的基本都为public，同时如果package声明中没有定义的，但body中存在的function，procedure，那么这些也是私有的
4）body中begin后的初始化过程代码，在没有需要的情况下可以为null，即不需要写；
5）务必注意package中属性的私有，局部性问题，不然得不到返回值的。

2.plSQL中对大对象的操作

首先在Oracle 9i 之后都建议使用 LOB 来处理对大对象进行操作，就 LOB 而言它拥有4中类型：

类型	处理什么对象	存储在何处
CLOB	字符大对象	存储在数据库内部
NCLOB	多字节字符大对象	存储在数据库内部
BLOB	二进制大对象	存储在数据库内部
BFILE	二进制文件	存储在数据库外部

对于数据库内部的 LOB 一般操作步骤为：

在表中添加 LOB 类型的列
在程序中声明和初始化 LOB 和 Locator
使用 SELECT FOR UPDATE 锁定目标行，准备更新行上的 LOB 列（ LOB 的 Locator）
生成 LOB 对象，可使用 DBMS_LOB 这样的 plSQL 包，也可以使用 OCI，JDBC 等
Commit 进行提交

对于数据库中使用 Bfile 一般步骤为：

在操作系统上创建目录，并给Oracle数据库进程赋予阅读权限，把外部文件放入这个目录
在Oracle数据库中表添加Bfile类型字段
在Oracle 数据库中创建Directory 对象
授权读权限给特定的数据库用户
往表中插入数据时使用 BFILENAME 函数，它可以关联外部文件和表上的Bfile列
在程序中声明和初始化LOB的Locator
Select 指定行上 Bfile 列到Locator
使用 DBMS_LOB 或者通过 OCI 读取Bfile (使用Locator作为文件的一个引用）

上述两种使用都提到了 DBMS_LOB 这个 plSQL 包对于这个包的主要函数有以下：

1、更改 LOB 的值：

APPEND：将源大对象的值全部添加到另一个大对象的之中
COPY ：该过程将源内部LOB的全部或一部分复制到目标内部LOB
ERASE：消除了整个内部LOB或内部LOB的一部分
TRIM：将内部LOB的值用newlen参数指定的长度进行计算
WRITE：这个过程将指定数量的数据写入内部LOB，从LOB开始时的绝对偏移量开始
LOADFROMFILE：将源外部LOB(BFILE)的全部或部分复制到目标内部LOB

2、读取和检查 LOB 的值

GETLENGTH ：判定长度
INSTR：从指定的位置开始，从大型对象中查找第N个与模式匹配的字符串
READ
SUBSTR ：1）获取文本，2）截取字段，3）获取某一变量保存的的所有值

3、Bfile 专用

FILECLOSE：关闭文件
FILECLOSEALL
FILEEXISTS
FILEGETNAME
FILEISOPEN
FILEOPEN

戳这进入官方文档，里面有更多更详细的内容

当我们对数据库中的大对象数据进行处理的时候，很多情况下与我们处理基本类型数据类似，但是在对大对象数据进行处理的时候有一点需要注意： EMPTY_CLOB() / EMPTY_CLOB()
这两个 empty 与 null 不同，在使用 ISNULL 进行判定时返回的是 FALSE

同时在对大对象数据进行处理的时候内部的几个条件属性需要我们多加注意，我们举两个栗子看看：

PROCEDURE READ (
lob_locIN BFILE|BLOB|CLOB ,
amount IN OUT BINARY_INTEGER,
offset IN INTEGER,
buffer OUT RAW|VARCHAR2 )

dbms_lob.write(
lob_loc in out nocopy clob character set any_cs,
amount in binary_integer,
offset in integer,
buffer in varchar2 character setlob_loc%charset);

lob_loc是要操作的大型对象定位器；
amount是要写道大型对象中去的字节数量；
offset是指定将数据写入到大型对象什么位置的偏移地址；
buffer是写入到大型对象的数据缓冲区；
any_cs指定要使用的字符集

3.plSQL触发器

首先我们需要知道什么时候需要使用触发器 Trigger：

当我们对数据库进行操作后，产生一系列的事件，而此时数据库中的某些事件需要基于这些事件完成，这时就需要Trigger出场了。

平时如果不是有需要，不要随意使用 Trigger ，因为这个类似于蝴蝶效应，频繁的使用会导致数据库维护起来难度加大，所以在我们使用的需要需要注意的很多：

时机：before ，after ，instead of
事件：insert ，update ，detele
对象：表名
类型：row级， statement级
条件：where 后的限制条件
内容

创建 Statement 级别的 Trigger 语法：

CREATE OR REPLACE TRIGGER trigger_name
what_time
event ON table_name
BEGIN
…
END;

创建 Row 级别的 Trigger 语法：

CREATE OR REPLACE TRIGGER trigger_name
what_time
event OF table_name ON table_name.列
FOR EACH ROW
WHEN　（限制条件，当什么时候触发器执行）
BEGIN
…
END;

来我们举个栗子：（单事件）

CREATE OR REPLACE TRIGGER employees_test_trigger
BEFORE INSERT ON employees
BEGIN
IF LTRIM(employees.salary,'0123456789') IS NOT NULL
THEN raise_application_error (-21232,'only allow to input number')
END IF;
END;

多事件：

CREATE OR REPLACE TRIGGER EMPLOYEES_TEST_TRIGGER
BEFORE INSERT OR UPDATE ON EMPLOYEES
BEGIN
IF (LTRIM(employees.salary,'0123456789') IS NOT NULL THEN
IF UPDATING THEN
RAISE_APPLICATION_ERROR(-21233, 'only allow to update number');
    ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR(-21234, 'only allow to insert number');
 END IF;
 END IF;
END;

多事件相对于单事件主要是 Trigger 中的判断增加，从而判断多事件

在使用触发器的时候，限制很多，所以慢慢调试吧。

如果在编写的时候遇到什么问题，欢迎留言提问！

秒客网

PLSQL新手新手向入门修炼（2）