DM支持DMSQL程序包来扩展数据库功能,用户可以通过包来创建应用程序或者使用包来管理过程和函数。

11.1 创建包

包的创建包括包规范和包主体的创建。

11.1.1 创建包规范

包规范中包含了有关包中的内容信息,但是它不包含任何过程的代码。定义一个包规范的详细语法如下。

语法格式

  CREATE [OR REPLACE] PACKAGE [<模式名>.]<包名>[WITH ENCRYPTION] [AUTHID DEFINER | AUTHID CURRENT_USER] AS|IS <包内声明列表> END [包名]

  <包内声明列表> ::= <包内声明>;{<包内声明>;}

  <包内声明> ::= <变量列表定义>|<游标定义>|<异常定义>|<过程定义>|<函数定义>|<类型声名>

  <变量列表定义>::= <变量定义>{<变量定义>}

  <变量定义> ::= <变量名><变量类型>[DEFAULT|ASSIGN|:=<表达式>]

  <变量类型>::=<DMSQL程序类型> | [<模式名>.]<表名>.<列名>%TYPE | [<模式名>.]<表名>%ROWTYPE> | <记录类型>

  <记录类型>::= RECORD(<变量名> <DMSQL程序类型>;{<变量名> <DMSQL程序类型>;})

  <游标定义> ::= CURSOR <游标名> [FOR <查询语句>]

  <异常定义> ::= <异常名> EXCEPTION [FOR <异常码>]

  <过程定义> ::= PROCEDURE <过程名> <参数列表>

  <函数定义> ::= FUNCTION <函数名><参数列表> RETURN <返回值数据类型>[RESULT_CACHE] [DETERMINISTIC] [PIPELINED]

  <类型声名> ::= TYPE <类型名称> IS <数据类型>

图例

创建包规范

创建包规范

使用说明

  1. 包部件可以以任意顺序出现,其中的对象必须在引用之前被声明;
  2. 过程和函数的声明都是前向声明,包规范中不包括任何实现代码。

权限

  1. 使用该语句的用户必须是DBA或该包对象的拥有者且具有CREATE PACKAGE数据库权限的用户;
  2. 可以用关键字AUTHID DEFINER |AUTHID CURRENT_USER指定包的调用者权限,若为DEFINER,则采用包定义者权限,若为CURRENT_USER则为当前用户权限,默认为包定义者权限。

11.1.2 创建包主体

包主体中包含了在包规范中的前向子程序声明相应的代码。它的创建语法如下。

语法格式

  CREATE [OR REPLACE] PACKAGE BODY [<模式名>.]<包名> [WITH ENCRYPTION] AS|IS <包体部分> END [包名]

  <包体部分> ::= <包体声明列表> [<初始化代码>]

  <包体声明列表> ::=<包体声明>[,{<包体声明> }……]

  <包体声明>::=<变量定义>|<游标定义>|<异常定义>|<过程定义>|<函数定义>|<类型声名> |<存储过程实现>|<函数实现>

  <变量定义> ::= <变量名列表> <数据类型> [<默认值定义>]

  <游标定义> ::= CURSOR <游标名> [FOR <查询语句>]

  <异常定义> ::= <异常名> EXCEPTION [FOR <异常码>]

  <过程定义> ::= PROCEDURE <过程名> <参数列表>

  <函数定义> ::= FUNCTION <函数名> <参数列表> RETURN <返回值数据类型>

  <类型声明> ::= TYPE <类型名称> IS <数据类型>

  <存储过程实现> ::= PROCEDURE <过程名> <参数列表> AS|IS BEGIN <实现体> END [<过程名>];

  <函数实现> ::= FUNCTION <函数名><参数列表> RETURN <返回值数据类型>[DETERMINISTIC] [PIPELINED]<AS|IS> BEGIN <实现体> END [<函数名>];

  <初始化代码> ::= [[<说明部分>]BEGIN<执行部分>[<异常处理部分>]]

  <说明部分> ::=[DECLARE]<说明定义>{<说明定义>}

  <说明定义>::=<变量列表说明>|<异常变量说明>|<游标定义>|<子过程定义>|<子函数定义>;

  <变量列表说明>::= <变量初始化>{<变量初始化>}

  <记录类型>::= RECORD(<变量名> <DMSQL程序类型>;{<变量名> <DMSQL程序类型>;})

  <异常变量说明>::=<异常变量名>EXCEPTION[FOR<错误号>]

  <游标定义>::=cursor <游标名> [FOR<查询表达式>|<表连接>]

  <子过程定义>::=PROCEDURE<过程名>[(<参数列>)]<IS|AS><模块体>

  <子函数定义>::=FUNCTION<函数名>[(<参数列>)]RETURN<返回数据类型><IS|AS><模块体>

  <执行部分>::=<SQL过程语句序列>{< SQL过程语句序列>}

  < SQL过程语句序列>::=[<标号说明>]<SQL过程语句>;

  <标号说明>::=<<<标号名>>>

  <SQL过程语句>::=<SQL语句>|<SQL控制语句>

  <异常处理部分>::=EXCEPTION<异常处理语句>{<异常处理语句>}

  <异常处理语句>::= WHEN <异常名> THEN < SQL过程语句序列>;

图例

创建包主体

创建包主体

使用说明

  1. 包规范中定义的对象对于包主体而言都是可见的,不需要声明就可以直接引用。这些对象包括变量、游标、异常定义和类型定义;
  2. 包主体中不能使用未在包规范中声明的对象;
  3. 包主体中的过程、函数定义必须和包规范中的前向声明完全相同。包括过程的名字、参数定义列表的参数名和数据类型定义;
  4. 包中可以有重名的过程和函数,只要它们的参数定义列表不相同。系统会根据用户的调用情况进行重载(Overload);
  5. 用户在第一次访问包(如调用包内过程、函数,访问包内变量)时,系统会自动将包对象实例化。每个会话根据数据字典内的信息在本地复制包内变量的副本。如果用户定义了package的初始化代码,还必须执行这些代码(类似于一个没有参数的构造函数执行);
  6. 对于一个会话,包头中声明的对象都是可见的,只要指定包名,用户就可以访问这些对象。可以将包头内的变量理解为一个session内的全局变量;
  7. 关于包内过程、函数的调用:DM支持按位置调用和按名调用参数两种模式。除了需要在过程、函数名前加入包名作为前缀,调用包内的过程、函数的方法和普通的过程、函数并无区别;
  8. 包体内声明的变量、类型、方法以及实现的未在包头内声明的方法被称作本地变量、方法,本地变量、方法只能在包体内使用,用户无法直接使用;
  9. 在包体声明列表中,本地变量必须在所有的方法实现之前进行声明;本地方法必须在使用之前进行声明或实现;
  10. 如果创建包时,在函数定义中使用DETERMINISTIC指定该函数为确定性函数,在函数实现中可以省略指定该函数的确定性;如果函数定义中没有指定该函数为确定性函数,则函数实现时不能指定该函数为确定性函数。
  11. 结果集缓存RESULT_CACHE,只是语法支持,没有实际意义。

权限

使用该语句的用户必须是DBA或该包对象的拥有者且具有CREATE PACKAGE数据库权限的用户。

11.2 重编译包

重新对包进行编译,如果重新编译失败,则将包置为禁止状态。

重编功能主要用于检验包的正确性。

语法格式

  ALTER PACKAGE [<模式名>.]<包名> COMPILE [DEBUG];

参数

1.<模式名> 指明被重编译的包所属的模式;

2.<包名> 指明被重编译的包的名字;

3.[DEBUG] 可忽略。

图例

重编译包

重编译包

权限

执行该操作的用户必须是包的创建者,或者具有DBA权限。

11.3 删除包

和创建方式类似,包对象的删除分为包规范的删除和包主体的删除。

11.3.1 删除包规范

从数据库中删除一个包对象。

语法格式

  DROP PACKAGE [IF EXISTS] [<模式名>.]<包名>;

参数

1.<模式名> 指明被删除的包所属的模式,缺省为当前模式;

2.<包名> 指明被删除的包的名字。

图例

删除包规范

删除包规范

使用说明

1.删除不存在的包规范会报错。若指定IF EXISTS关键字,删除不存在的包规范,不会报错;

2.如果被删除的包不属于当前模式,必须在语句中指明模式名;

3.如果一个包规范被删除,那么对应的包主体被自动删除。

权限

执行该操作的用户必须是该包的拥有者,或者具有DBA权限。

11.3.2 删除包主体

从数据库中删除一个包的主体对象。

语法格式

  DROP PACKAGE BODY [IF EXISTS] [<模式名>.]<包名>;

参数

1.<模式名> 指明被删除的包所属的模式,缺省为当前模式;

2.<包名>  指明被删除的包的名字。

图例

删除包主体

删除包主体

使用说明

  1. 删除不存在的包主体会报错。若指定IF EXISTS关键字,删除不存在的包主体,不会报错;
  2. 如果被删除的包不属于当前模式,必须在语句中指明模式名。

权限

执行该操作的用户必须是该包的拥有者,或者具有DBA权限。

11.4 应用实例

以下是一个包规范的使用实例:

创建实例数据库,这些数据将在之后的例子中用到。

  CREATE TABLE Person(Id INT IDENTITY, Name VARCHAR(100), City VARCHAR(100));

  INSERT INTO Person(Name, City) VALUES('Tom','武汉');

  INSERT INTO Person(Name, City) VALUES('Jack','北京');

  INSERT INTO Person(Name, City) VALUES('Mary','上海');

表中数据如表11.3.1所示。

表11.3.1
ID NAME CITY
1 TOM 武汉
2 JACK 北京
3 MARY 上海

创建包规范:

	CREATE OR REPLACE PACKAGE

		E_NoPerson EXCEPTION;

		PersonCount INT;

		Pcur CURSOR;

		PROCEDURE AddPerson(Pname VARCHAR(100), Pcity varchar(100));

		PROCEDURE RemovePerson(Pname VARCHAR(100), Pcity varchar(100));

		PROCEDURE RemovePerson(Pid INT);

		FUNCTION GetPersonCount RETURN INT;

		PROCEDURE PersonList;

	END PersonPackage;

这个包规范的部件中包括1个变量定义,1个异常定义,1个游标定义,4个过程定义和1个函数定义。

以下是一个包主体的实例,它对应于前面的包规范定义,包括4个子过程和1个子函数的代码实现。在包主体的末尾,是这个包对象的初始化代码。当一个会话第一次引用包时,变量PersonCount被初始化为Person表中的记录数。

创建包主体:

  CREATE OR REPLACE PACKAGE BODY PersonPackage AS

    PROCEDURE AddPerson(Pname VARCHAR(100), Pcity varchar(100) )AS
  
      BEGIN
  
    	INSERT INTO Person(Name, City) VALUES(Pname, Pcity);
  
    	PersonCount = PersonCount + SQL%ROWCOUNT;
  
      END AddPerson;
  
    PROCEDURE RemovePerson(Pname VARCHAR(100), Pcity varchar(100)) AS
  
      BEGIN
  
    	DELETE FROM Person WHERE NAME LIKE Pname AND City like Pcity;
  
    	PersonCount = PersonCount - SQL%ROWCOUNT;
  
      END RemovePerson;
  
    PROCEDURE RemovePerson(Pid INT) AS
  
      BEGIN
  
    	DELETE FROM Person WHERE Id = Pid;
  
    	PersonCount = PersonCount - SQL%ROWCOUNT;
  
      END RemovePerson;
  
    FUNCTION GetPersonCount RETURN INT AS
  
    BEGIN
  
      RETURN PersonCount;
  
    END GetPersonCount;
  
    PROCEDURE PersonList AS
  
    DECLARE
  
      V_id INT;
  
      V_name VARCHAR(100);
  
      V_city VARCHAR(100);
  
    BEGIN
  
      IF PersonCount = 0 THEN
  
    	RAISE E_NoPerson;
  
      END IF;
  
      OPEN Pcur FOR SELECT Id, Name, City FROM Person;
  
      LOOP
  
    	FETCH Pcur INTO V_id,V_name,V_city;
  
    	EXIT WHEN Pcur%NOTFOUND;
  
    	PRINT ('No.' || (cast (V_id as varchar(100))) || ' ' || V_name || '来自' || V_city );
  
      END LOOP;
  
      CLOSE Pcur;
  
    END PersonList;

  BEGIN

	SELECT COUNT(*) INTO PersonCount FROM Person;

  END PersonPackage;

重新编译包:

  ALTER PACKAGE PersonPackage COMPILE;

调用包中的AddPerson过程,往数据表中增加一条记录:

  CALL PersonPackage. AddPerson ('black', '南京') ;

当前记录变化如表11.3.2所示。

表11.3.2

ID NAME CITY
1 TOM 武汉
2 JACK 北京
3 MARY 上海
4 black 南京

调用包中的RemovePerson过程,删除第二条记录:

  CALL PersonPackage. RemovePerson ('JACK', '北京') ;

  或者

  CALL PersonPackage. RemovePerson (2) ;

在此例中,以上两种写法可以得到相同的结果,系统对同名过程根据实际参数进行了重载。如果过程执行结果没有删除任何一条表中的记录,那么会抛出一个包内预定义的异常:E_NoPerson。

此时表中的数据如表11.3.3所示。

表11.3.3
ID NAME CITY
1 TOM 武汉
3 MARY 上海
4 BLACK 南京

引用包中的变量。

  SELECT PersonPackage. PersonCount;

  或者

  SELECT PersonPackage. GetPersonCount;

以上两句语句的作用是等价的。前一句是直接引用了包内变量,后一句是通过调用包内的子函数来得到想要的结果。

调用包中的过程PersonList查看表中的所有记录:

  CALL PersonPackage. PersonList;

可以得到以下输出:

  No.1 Tom来自武汉

  No.3 MARY来自上海

  No.4 BLACK来自南京
微信扫码
分享文档
扫一扫
联系客服