DM存储模块及系统视图

CREATE [OR REPLACE] PROCEDURE [IF NOT EXISTS] <过程声明> <AS_OR_IS> <模块体>

<过程声明> ::= <存储过程名定义> [WITH ENCRYPTION][(<参数名><参数模式><参数类型> [<默认值表达式>]

{,<参数名><参数模式><参数类型> [<默认值表达式>] })][<调用权限子句>]

<存储过程名定义> ::=[<模式名>.]<存储过程名><AS_OR_IS>::= AS | IS

<模块体> ::= [<声明部分>]

BEGIN

<执行部分>

[<异常处理部分>]

END [存储过程名]

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

<声明定义>::=<变量声明>

|<异常变量声明>

|<游标定义>

|<子过程定义>

|<子函数定义>;

<执行部分>::=<DMSQL程序语句序列>{;<DMSQL程序语句序列>}

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

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

<DMSQL程序语句>::=<SQL语句>|<控制语句>

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

2.参数说明

< 存储过程名>：指明被创建的存储过程的名字
< 模式名>：指明被创建的存储过程所属模式的名字，缺省为当前模式名
< 参数名>：指明存储过程参数的名称
< 参数模式>：参数模式可设置为 IN、OUT 或 IN OUT（OUT IN），缺省为 IN 类型
< 参数类型>：指明存储过程参数的数据类型
< 声明部分>：由变量、游标和子程序等对象的声明构成，可缺省
< 执行部分>：由 SQL 语句和过程控制语句构成的执行代码
< 异常处理部分>：各种异常的处理程序，存储过程执行异常时调用，可缺省
< 调用权限子句>：指定该过程中的 SQL 语句默认的模式

DBA 或具有 CREATE PROCEDURE 权限的用户可以使用上述语法新创建一个存储过程。OR REPLACE 选项的作用是当同名的存储过程存在时，首先将其删除，再创建新的存储过程。IF NOT EXISTS 选项的作用是当同名的存储过程存在时，忽略本次存储过程创建操作。当同时指定 OR REPLACE 和 IF NOT EXISTS 选项时，按照 OR REPLACE 选项的策略执行。

WITH ENCRYPTION 为可选项，如果指定 WITH ENCRYPTION 选项，则对存储过程名之后的语句部分进行加密，防止非法用户查看其具体内容。加密后的存储过程的定义可在 SYS.SYSTEXTS 系统表中查询。

存储过程可以带有参数，这样在调用存储过程时就需指定相应的实际参数，如果没有参数，过程名后面的圆括号和参数列表就可以省略了。

可执行部分是存储过程的核心部分，由 SQL 语句和流控制语句构成。支持的 SQL 语句包括：

数据查询语句(SELECT)
数据操纵语句(INSERT、DELETE、UPDATE)
游标定义及操纵语句(DECLARE CURSOR、OPEN、FETCH、CLOSE)
事务控制语句(COMMIT、ROLLBACK)
动态 SQL 执行语句(EXECUTE IMMEDIATE)

（二）存储函数

1.语法格式

CREATE [OR REPLACE ] FUNCTION [IF NOT EXISTS] <函数声明> <AS_OR_IS> <模块体>

<函数声明> ::= <存储函数名定义> [WITH ENCRYPTION][FOR CALCULATE][(<参数名><参数模式><参数类型> [<默认值表达式>]{,<参数名><参数模式><参数类型>[<默认值表达式>]})]RETURN <返回数据类型> [<调用选项子句>][PIPELINED]

<存储函数名定义> ::=[<模式名>.]<存储函数名>

<调用选项子句> ::= <调用选项> {<调用选项>}

<调用选项> ::= <调用权限子句> | DETERMINISTIC

<AS_OR_IS>::= AS | IS

<模块体> ::= [<声明部分>]

BEGIN

<执行部分>

[<异常处理部分>]

END [存储函数名]

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

<声明定义>::=<变量声明>

|<异常变量声明>

|<游标定义>

|<子过程定义>

|<子函数定义>;

<执行部分>::=<DMSQL程序语句序列>{;<DMSQL程序语句序列>}

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

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

<DMSQL程序语句>::=<SQL语句>|<控制语句>

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

2.参数说明

< 存储函数名>：指明被创建的存储函数的名字
< 模式名>：指明被创建的存储函数所属模式的名字，缺省为当前模式名
< 参数名>：指明存储函数参数的名称
< 参数模式>：参数模式可设置为 IN、OUT 或 IN OUT（OUT IN），缺省为 IN 类型
< 参数类型>：指明存储函数参数的数据类型
< 返回数据类型>：指明存储函数返回值的数据类型
< 调用权限子句>：指定该过程中的 SQL 语句默认的模式
PIPELINED：指明函数为管道表函数

存储函数与存储过程在结构和功能上十分相似，主要的差异在于：

存储过程没有返回值，调用者只能通过访问 OUT 或 IN OUT 参数来获得执行结果，而存储函数有返回值，它把执行结果直接返回给调用者；
存储过程中可以没有返回语句，而存储函数必须通过返回语句结束；
不能在存储过程的返回语句中带表达式，而存储函数必须带表达式；
存储过程不能出现在一个表达式中，而存储函数可以出现在表达式中。

CALCULATE 指定存储函数为计算函数。计算函数中不支持对表进行 INSERT、DELETE、UPDATE、SELECT、上锁、设置自增列属性；对游标 DECLARE、OPEN、FETCH、CLOSE；事务的 COMMIT、ROLLBACK、SAVEPOINT、设置事务的隔离级别和读写属性；动态 SQL 的执行 EXEC、创建 INDEX、创建子过程。对于计算函数体内的函数调用必须是系统函数或者计算函数。计算函数可以被指定为表列的缺省值。

DETERMINISTIC 指定存储函数为确定性函数。在调用其的语句中，对于相同的参数返回相同的结果。如果要将一个函数作为表达式在函数索引中使用，必须指定该函数为确定性函数。当系统遇到确定性函数，它将会试图重用之前的计算结果，而不是重新计算。在确定性函数实现中，虽然没有限制不确定元素（如随机函数等）和 SQL 语句的使用，但是不推荐使用这些可能会导致结果不确定的内容。

（三）客户端DMSQL程序

客户端 DMSQL 程序不需要存储，创建后立即执行，执行完毕即被释放。

客户端 DMSQL 程序的定义语法与存储过程的定义语法类似。

1.语法格式

[<声明部分>]

BEGIN

<执行部分>

[<异常处理部分>]

END

注意：客户端DMSQL程序的声明部分必须包含DECLARE。

客户端 DMSQL 程序无法被其他程序调用，但它可以调用包括存储过程和存储函数等在内的其他函数，对于不需要反复执行的脚本，使用客户端 DMSQL 程序是一个比较合适的选择。

（四）参数

存储模块及模块中定义的子模块都可以带参数，用来给模块传送数据及向外界返回数据。在存储过程或存储函数中定义一个参数时，必须说明名称、参数模式和数据类型。三种可能的参数模式是：IN(缺省模式)、OUT 和 IN OUT，意义分别为：

IN：输入参数，用来将数据传送给模块；
OUT：输出参数，用来从模块返回数据到进行调用的模块；
IN OUT：既作为输入参数，也作为输出参数。

在存储模块中使用参数时要注意下面几点：

最多能定义不超过 1024 个参数；
IN 参数能被赋值；

例 1 在客户端 DMSQL 程序中定义一个子过程 raise_salary，其三个参数分别为 IN，IN OUT 和 OUT 类型。调用 raise_salary 为工号为 emp_num 的员工加薪 bonus 元，在 raise_salary 中将加薪后的薪水值赋给 IN OUT 参数 bonus，将员工职位赋给 OUT 参数 title。

DECLARE

emp_num INT := 1;

bonus DEC(19,4) := 6000;

title VARCHAR(50);

PROCEDURE raise_salary (emp_id IN INT, //输入参数

amount IN OUT DEC(19,4), //输入输出参数

emp_title OUT VARCHAR(50) //输出参数

)

IS

BEGIN

UPDATE RESOURCES.EMPLOYEE SET SALARY = SALARY + amount WHERE EMPLOYEEID =emp_id;

SELECT TITLE,SALARY INTO emp_title,amount FROM RESOURCES.EMPLOYEE WHERE

EMPLOYEEID = emp_id;

END raise_salary;

BEGIN

raise_salary(emp_num, bonus, title);

DBMS_OUTPUT.PUT_LINE

('工号：'||emp_num||' '||'职位：'||title||''||'加薪后薪水：'||bonus);

END;

/

执行这个例子，将打印如下信息：

工号：1 职位：总经理加薪后薪水：46000.0000

例 2 使用赋值符号“:=”或关键字 DEFAULT，可以为 IN 参数指定一个缺省值。如果调用时未指定参数值，系统将自动使用该参数的缺省值。例如：

CREATE PROCEDURE proc_def_arg(a varchar(10) default 'abc', b INT:=123) AS

BEGIN

PRINT a;

PRINT b;

abc

123

END;

/

调用过程 PROC_DEF_ARG，不指定输入参数值：

CALL proc_def_arg;

系统使用缺省值作为参数值，打印结果为：

也可以只指定第一个参数，省略后面的参数：

CALL proc_def_arg('我们');

系统对后面的参数使用缺省值，打印结果为：

我们

123

（五）变量

变量的声明应在声明部分。

1.语法格式

<变量名>{,<变量名>}[CONSTANT]<变量类型>[NOT NULL][<缺省值定义符><表达式>]

<缺省值定义符> ::= DEFAULT | ASSIGN | :=

声明一个变量需要给这个变量指定名字及数据类型。

变量名必须以字母或下划线、$、#符号开头，包含数字、字母、下划线以及$、#符号，长度不能超过 128 字符，并且不能与DM的DMSQL程序保留字相同，变量名与大小写是无关的。

变量的数据类型可以是基本的 SQL 数据类型，也可以是 DMSQL 程序数据类型，比如一个游标、异常等。

用赋值符号“:=”或关键字 DEFAULT、ASSIGN，可以在定义时为变量指定一个缺省值。

支持同时声明多个变量，此时仅支持同时为多个变量指定数据类型，例如“a,b,c int;”。

在 DMSQL 程序的执行部分可以对变量赋值，赋值语句有两种方式：

直接赋值语句，语法如下：

<变量名>:=<表达式>

或

SET <变量名>=<表达式>

通过 SQL SELECT INTO 或 FETCH INTO 给变量赋值，语法如下

SELECT <表达式>{,<表达式>} [INTO <变量名>{,<变量名>}] FROMb <表引用>{,<表引用>} …;

或

FETCH [NEXT|PREV|FIRST|LAST|ABSOLUTE N|RELATIVE N]<游标名> [INTO<变量名>{,<变量名>}];

常量与变量相似，但常量的值在程序内部不能改变，常量的值在定义时赋予，它的声明方式与变量相似，但必须包含关键字CONSTANT。

如果需要打印变量的值，则要调用PRINT语句或DBMS_OUTPUT.PUT_LINE语句，如果数据类型不一致，则系统会自动将它转换为VARCHAR类型输出。除了变量的声明外，变量的赋值、输出等操作都要放在DMSQL程序的可执行部分。

2.举例

DECLARE //可以在这里赋值

salary DEC(19,4);

worked_time DEC(19,4) := 60;

hourly_salary DEC(19,4) := 1055;

bonus DEC(19,4) := 150;

position VARCHAR(50);

province VARCHAR(64);

counter DEC(19,4) := 0;

done BOOLEAN;

valid_id BOOLEAN;

emp_rec1 RESOURCES.EMPLOYEE%ROWTYPE;

emp_rec2 RESOURCES.EMPLOYEE%ROWTYPE;

TYPE meeting_type IS TABLE OF INT INDEX BY INT;

meeting meeting_type;

BEGIN //也可以在这里赋值

salary := (worked_time * hourly_salary) + bonus;

SELECT TITLE INTO position FROM RESOURCES.EMPLOYEE WHERE LOGINID='L3';

province := 'ShangHai';

province := UPPER('wuhan');

done := (counter > 100);

valid_id := TRUE;

emp_rec1.employeeid := 1;

emp_rec1.managerid := null;

emp_rec1 := emp_rec2;

meeting(5) := 20000 * 0.15;

PRINT position||'来自'||province;

PRINT ('加班工资'||salary);

END;

/

变量只在定义它的语句块(包括其下层的语句块)内可见，并且定义在下一层语句块中的变量可以屏蔽上一层的同名变量。当遇到一个变量名时，系统首先在当前语句块内查找变量的定义；如果没有找到，再向包含该语句块的上一层语句块中查找，如此直到最外层。举例如下：

DECLARE

a INT :=5;

BEGIN

DECLARE

a VARCHAR(10); //此处定义的变量a与上一层中的变量a同名

BEGIN

a:= 'ABCDEFG';

PRINT a; //第一条打印语句

END;

PRINT a; //第二条打印语句

END;

/

先定义了一个整型变量 a，然后又在其下层的语句块中定义了一个同名的字符型变量 a。由于在该语句块中，字符型变量 a 屏蔽了整型变量 a，所以第一条打印语句打印的是字符型变量 a 的值，而第二条打印语句打印的则是整型变量 a 的值。执行结果如下：

ABCDEFG

5

（六）OR REPLACE和IF NOT EXISTS选项

OR REPLACE 选项的作用是，如果系统中已经有同名的存储模块，服务器会删除原先的存储模块，再创建新的存储模块。成功使用 OR REPLACE 选项的前提条件是当前用户具有删除原存储模块的权限，如果没有删除权限，则创建失败。使用 OR REPLACE 选项重新定义存储模块后，原存储模块的授权信息全部保留。

IF NOT EXISTS 选项的作用是，如果系统中已经有同名的存储模块，则忽略本次存储模块创建操作。

若同时指定OR REPLACE和IF NOT EXISTS选项，则按照OR REPLACE选项的策略执行。

若均不指定OR REPLACE和IF NOT EXISTS选项，则当创建的存储模块与系统中已有的存储模块同名时，服务器会报错。

（七）调用权限子句

调用权限子句用于解析存储模块中的SQL语句中没有指定所在模式的对象名，是否在当前模式下运行。DM8提供两种策略：定义者权限和调用者权限，系统默认使用定义者权限。

1.语法格式：

AUTHID CURRENT_USER //sql语句在当前模式下执行

或

AUTHID DEFINER //sql语句在过程或函数所在的模式下执行

2.使用说明：

AUTHID CURRENT_USER：采用调用者权限，即SQL语句在当前模式下执行

AUTHID DEFINER：采用定义者权限，即SQL语句在过程或函数所在模式下执行

二、包

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

（一）创建包

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

1.创建包规范

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

（1）语法格式

CREATE [OR REPLACE] PACKAGE [IF NOT EXISTS] [<模式名>.]<包名> [WITH ENCRYPTION] [<调用者权限>] AS|IS <包内声明列表> END [包名]

<调用者权限>::=AUTHID DEFINER |

             AUTHID CURRENT_USER

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

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

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

<变量定义>::= <变量名><变量类型>[<赋值标识><表达式>];

<变量类型>::=<DMSQL程序类型> |

           [<模式名>.]<表名>.<列名>%TYPE |

           [<模式名>.]<表名>%ROWTYPE> |

           <记录类型>

<赋值标识>::=DEFAULT |

       ASSIGN |

       :=

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

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

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

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

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

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

（2）使用说明

创建包的名称不能与系统创建的模式名称相同；

包部件可以以任意顺序出现，其中的对象必须在引用之前被声明；

过程和函数的声明都是前向声明，包规范中不包括任何实现代码；

<赋值标识>中DEFAULT、ASSIGN和:=均用于为变量赋值，三种赋值标识功能和用法完全一样。

（3）权限

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

<调用者权限> 中用关键字AUTHID DEFINER或AUTHID CURRENT_USER指定包的调用者权限。DEFINER为采用包定义者权限；CURRENT_USER为当前用户权限。缺省为DEFINER。

2.创建包主体

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

（1）语法格式

  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过程语句序列>;

（2）使用说明

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

（3）权限

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

3.重编译包

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

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

（1）语法格式

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

（2）参数

< 模式名 > 指明被重编译的包所属的模式；
< 包名 > 指明被重编译的包的名字；
[CASCADE] 当指定 CASCADE 后，将级联重编译所有直接或间接引用该包的对象，需要考量影响范围，建议谨慎使用；
[DEBUG] 可忽略。

（3）权限

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

（二）删除包

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

1.删除包规范

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

（1）语法格式

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

（2）参数

<模式名> 指明被删除的包所属的模式，缺省为当前模式；
<包名> 指明被删除的包的名字。

（3）使用说明

删除不存在的包规范会报错。若指定IF EXISTS关键字，删除不存在的包规范，不会报错；
如果被删除的包不属于当前模式，必须在语句中指明模式名；
如果一个包规范被删除，那么对应的包主体被自动删除。

（4）权限

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

2.删除包主体

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

（1）语法格式

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

（2）参数

<模式名> 指明被删除的包所属的模式，缺省为当前模式；
<包名> 指明被删除的包的名字。

（3）使用说明

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

（4）权限

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

（四）应用实例

1.创建数据

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','上海');

COMMIT;

2.创建包规范：

CREATE OR REPLACE PACKAGE PersonPackage AS

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 表中的记录数。

3.创建包主体：

  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;

4.重新编译包：

ALTER PACKAGE PersonPackage COMPILE;

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

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

当前记录变化如下所示：

ID NAME CITY

1 TOM 武汉

2 JACK 北京

3 MARY 上海

4 black 南京

调用包中的 RemovePerson 过程，删除第二条记录：

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

或者

CALL PersonPackage. RemovePerson (2) ;

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

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

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来自南京

三、游标

（一）静态游标

静态游标是只读游标，它总是按照打开游标时的原样显示结果集，在编译时就能确定静态游标使用的查询。

静态游标又分为两种：隐式游标和显式游标。

1.隐式游标

隐式游标无需用户进行定义，每当用户在 DMSQL程序中执行一个DML语句（INSERT、UPDATE、DELETE）或者SELECT...INTO语句时，DMSQL程序都会自动声明一个隐式游标并管理这个游标。

隐式游标的名称统一称为“SQL”，用户可以通过隐式游标获取语句执行的一些信息。

（1）使用隐式游标的步骤

数据库自动执行隐式游标的相关操作。比如：打开游标、提取数据，关闭游标等。

（2）隐式游标属性

DMSQL 程序中的每个游标都有 %FOUND、%NOTFOUND、%ISOPEN 和 %ROWCOUNT 四个属性，对于隐式游标，这四个属性的意义如下：

%FOUND：语句是否修改或查询到了记录，是返回 TRUE，否则返回 FALSE；
%NOTFOUND：语句是否未能成功修改或查询到记录，是返回 TRUE，否则返回 FALSE；
%ISOPEN：游标是否打开。是返回 TRUE，否返回 FALSE。由于系统在语句执行完成后会自动关闭隐式游标，因此隐式游标的 %ISOPEN 属性永远为 FALSE；
%ROWCOUNT：DML 语句执行影响的行数，或 SELECT…INTO 语句返回的行数。

例将孙丽的电话号码修改为 13818882888。示例中的“SQL”为隐式游标的名称。

BEGIN

UPDATE PERSON.PERSON SET PHONE=13818882888 WHERE NAME='孙丽';

IF SQL%NOTFOUND THEN

PRINT '此人不存在';

ELSE

PRINT '已修改';

END IF;

END;

/

2.显式游标

显式游标指向一个查询语句执行后的结果集区域。当需要处理返回多条记录的查询时，应显式地定义游标以处理结果集的每一行。

（1）使用显式游标步骤

使用显式游标一般包括四个步骤：

定义游标：在 DMSQL 程序的声明部分定义游标，声明游标及其关联的查询语句；
打开游标：执行游标关联的语句，将查询结果装入游标工作区，将游标定位到结果集的第一行之前；
拨动游标：根据应用需要将游标位置移动到结果集的合适位置；
关闭游标：游标使用完后应关闭，以释放其占有的资源。

下面对这四个步骤进行具体介绍。

■ 定义显式游标

语法如下：

CURSOR <游标名> [FAST | NO FAST] <cursor选项>;|

<游标名> CURSOR [FAST | NO FAST] <cursor选项>;

<cursor选项> :=<cursor选项1>|<cursor选项2>|<cursor选项3>|<cursor选项4>

<cursor选项1>:= <IS|FOR> {<查询表达式>|<连接表>}

<cursor选项2>:= <IS|FOR> TABLE <表名>

<cursor选项3>:= (<参数声明> {,<参数声明>})IS <查询表达式>

<cursor选项4>:= [(<参数声明> {,<参数声明>})] RETURN <DMSQL数据类型> IS <查询表达式>

<参数声明> ::= <参数名> [IN] <参数类型> [ DEFAULT|:= <缺省值> ]

<DMSQL数据类型> ::= <普通数据类型>

                | <变量名> %TYPE

                | <表名> %ROWTYPE

                | CURSOR

                | REF <游标名>

语法中的“FAST”指定游标是否为快速游标。缺省为 NO FAST，为普通游标。快速游标提前返回结果集，速度上提升明显，但是存在以下的使用约束：

FAST 属性只在显式游标中支持；
使用快速游标的 DMSQL 程序语句块中不能修改快速游标所涉及的表。这点需用户自己保证，否则可能导致结果不正确；
不支持游标更新和删除；
不支持 NEXT 以外的 FETCH 方向；
不支持快速游标作为函数返回值；
MPP 环境下不支持对快速游标进行 FETCH 操作。

必须先定义一个游标，之后才能在别的语句中使用它。定义显式游标时指定游标名和与其关联的查询语句。可以指定游标的返回类型，也可以指定关联的查询语句中的 WHERE 子句使用的参数。

举例说明：

例如何使用不同语法定义各种显式游标。

DECLARE

CURSOR c1 IS SELECT TITLE FROM RESOURCES.EMPLOYEE WHERE MANAGERID = 3;

CURSOR c2 RETURN RESOURCES.EMPLOYEE%ROWTYPE IS SELECT * FROM

RESOURCES.EMPLOYEE;

c3 CURSOR IS TABLE RESOURCES.EMPLOYEE;

……

■ 打开显式游标

语法如下：

OPEN <游标名>;

指定打开的游标必须是已定义的游标，此时系统执行这个游标所关联的查询语句，获得结果集，并将游标定位到结果集的第一行之前。

注意

当再次打开一个已打开的游标时，游标会被重新初始化，游标属性数据可能会发生变化。

■ 拨动游标

将游标拨动到结果集的某个位置，获取数据。

语法如下：

FETCH [<fetch选项> [FROM]] <游标名> [ [BULK COLLECT] INTO <主变量名>{,<主变量名>} ] [LIMIT <rows>];

<fetch选项>::= NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n

被拨动的游标必须是已打开的游标。

fetch 选项指定将游标移动到结果集的某个位置：
NEXT：游标下移一行
PRIOR：游标前移一行
FIRST：游标移动到第一行
LAST：游标移动到最后一行
ABSOLUTE n：游标移动到第 n 行
RELATIVE n：游标移动到当前指示行后的第 n 行

FETCH 语句每次只获取一条记录，除非指定了“BULK COLLECT”。若不指定 FETCH 选项，则第一次执行 FETCH 语句时，游标下移，指向结果集的第一行，以后每执行一次 FETCH 语句，游标均顺序下移一行，使这一行成为当前行。

INTO 子句中的变量个数、类型必须与游标关联的查询语句中各 SELECT 项的个数、类型一一对应。典型的使用方式是在 LOOP 循环中使用 FETCH 语句将每一条记录数据赋给变量，并进行处理，使用 %FOUND 或 %NOTFOUND 来判断是否处理完数据并退出循环。如下例所示：

例 1 使用 %NOTFOUND 来判断是否处理完数据并退出循环

DECLARE

v_name VARCHAR(50);

v_phone VARCHAR(50);

c1 CURSOR FOR SELECT NAME,PHONE FROM PERSON.PERSON A,RESOURCES.EMPLOYEE B WHERE A.PERSONID=B.PERSONID;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO v_name,v_phone;

EXIT WHEN c1%NOTFOUND;

PRINT v_name || v_phone;

END LOOP;

CLOSE c1;

END;

/

使用 FETCH…BULK COLLECTINTO可以将查询结果批量地、一次性地赋给集合变量。FETCH…BULK COLLECTINTO和LIMIT rows配合使用，可以限制每次获取数据的行数。

例 2 FETCH…BULK COLLECT INTO 的使用方法

DECLARE

TYPE V_rd IS RECORD(V_NAME VARCHAR(50),V_PHONE VARCHAR(50));

TYPE V_type IS TABLE OF V_rd INDEX BY INT;

v_info V_type;

c1 CURSOR IS SELECT NAME,PHONE FROM PERSON.PERSON A,RESOURCES.EMPLOYEE B WHERE

A.PERSONID=B.PERSONID;

BEGIN

OPEN c1;

FETCH c1 BULK COLLECT INTO v_info;

CLOSE c1;

FOR I IN 1..v_info.COUNT LOOP

PRINT v_info(I).V_NAME ||v_info(I).V_PHONE;

END LOOP;

END;

/

BULK COLLECT 可以和 SELECT INTO、FETCH INTO、RETURNING INTO 一起使用，BULK COLLECT 之后 INTO 的变量必须是集合类型。针对 FETCH…BULK COLLECT INTO 语句，BULK COLLECT 之后 INTO 的变量不支持索引类型为 VARCHAR 的索引表。

■ 关闭游标

语法如下：

CLOSE <游标名>;

游标在使用完后应及时关闭，以释放它所占用的内存空间。当游标关闭后，不能再从游标中获取数据，否则将报错。如果需要，可以再次打开游标。

（2）显式游标的属性

每一个显式游标也有 %FOUND、%NOTFOUND、%ISOPEN 和 %ROWCOUNT 四个属性，但这些属性的意义与隐式游标的有一些区别。

%FOUND：如果游标未打开，产生一个异常。否则，在第一次拨动游标之前，其值为 NULL。如果最近一次拨动游标时取到了数据，其值为 TRUE，否则为 FALSE。
%NOTFOUND：如果游标未打开，产生一个异常。否则，在第一次拨动游标之前，其值为 NULL。如果最近一次拨动游标时取到了数据，其值为 FALSE，否则为 TRUE。
%ISOPEN：游标打开时为 TRUE，否则为 FALSE。
%ROWCOUNT：如果游标未打开，产生一个异常。如游标已打开，在第一次拨动游标之前其值为 0，否则为最近一次拨动后已经取到的元组数。

下面的例子说明了显式游标属性的使用方法。

例显式游标属性的使用方法。对于基表 EMPSALARY，输出表中的前 5 行数据。如果表中的数据不足 5 行，则输出表中的全部数据。

DECLARE

CURSOR c1 FOR SELECT * FROM OTHER.EMPSALARY;

my_ename CHAR(10);

my_empno NUMERIC(4);

my_sal NUMERIC(7,2);

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO my_ename, my_empno, my_sal;

EXIT WHEN c1%NOTFOUND; //当游标取不到数据时跳出循环

PRINT my_ename || ' ' || my_empno || ' ' || my_sal;

EXIT WHEN c1%ROWCOUNT=5; //已经输出了5行数据，跳出循环

END LOOP;

CLOSE c1;

END;

/

（二）动态游标

与静态游标不同，动态游标在声明部分只是先声明一个游标类型的变量，并不指定其关联的查询语句，在执行部分打开游标时才指定查询语句。动态游标的使用主要在定义和打开时与显式游标不同，下面进行详细介绍。

1.定义动态游标

语法如下：

CURSOR <游标名>；

2.打开动态游标

打开动态游标的语法如下：

OPEN [WITH FAST]<游标名><for表达式>;

<for表达式>::=<for_item1>|<for_item2>

<for_item1>::= FOR <查询表达式>

<for_item2>::= FOR <表达式> [USING <绑定参数> {,<绑定参数>}]

动态游标在 OPEN 时通过 FOR 子句指定与其关联的查询语句。

WITH FAST：在打开游标时指定 FAST 属性。OPEN 语句中的 WITH FAST 必须在 DM.INI 参数 ENABLE_FAST_REFCURSOR=1 的情况下才能生效，否则只是语法支持并未生效。

例 1 使用动态游标输出员工的姓名、工号和薪水。

DECLARE

my_ename CHAR(10);

my_empno NUMERIC(4);

my_sal NUMERIC(7,2);

c1 CURSOR;

BEGIN

OPEN C1 FOR SELECT * FROM OTHER.EMPSALARY;

LOOP

FETCH c1 INTO my_ename, my_empno, my_sal;

EXIT WHEN c1%NOTFOUND;

PRINT '姓名'||my_ename || '工号' || my_empno || ' 薪水' || my_sal;

END LOOP;

CLOSE c1;

END;

/

动态游标关联的查询语句还可以带有参数，参数以“?”指定，同时在打开游标语句中使用 USING 子句指定参数，且参数的个数和类型与语句中的“?”必须一一匹配。

例 2 使用关联的语句中带有参数的动态游标。

DECLARE

str VARCHAR;

CURSOR csr;

BEGIN

OPEN csr FOR 'SELECT LOGINID FROM RESOURCES.EMPLOYEE WHERE TITLE =? OR TITLE =?'

USING '销售经理','总经理';

LOOP

FETCH csr INTO str;

EXIT WHEN csr%NOTFOUND;

PRINT str;

END LOOP;

CLOSE csr;

END;

/

（三）游标变量

游标变量不是真正的游标对象，而是指向源游标对象的一个指针。游标变量和游标的关系就像变量与常量的关系。

游标变量继承所有源游标的属性。如果源游标已经打开，则此游标变量也已经打开，游标变量指向的位置和源游标也是完全一样的。

1.使用游标变量的步骤

■ 定义游标变量

语法如下：

<游标变量名> CURSOR[= <源游标>]; |

<游标变量名> SYS_REFCURSOR [= <源游标>];

<源游标>：= <源游标名> | <游标表达式>

<游标表达式>：= CURSOR [FAST] (<查询表达式>)

■ 游标变量赋值

游标变量可以在两个阶段进行赋值。一是在定义时赋值；二是使用时在 < 执行部分 > 对它赋值。

语法如下：

<游标变量名> = <源游标>;

<源游标>：= <源游标名> | <游标表达式>

■ 打开游标

打开已赋值游标变量。

语法如下：

OPEN <游标变量名>;

游标表达式会自动打开，不需要使用 OPEN 方法。

如果游标变量未赋值，那么在 < 执行部分 > 打开游标变量的同时，必须为其动态关联一条查询语句。

打开未赋值游标变量。

语法如下：

OPEN [WITH FAST]<游标变量名><for表达式>;

<for表达式>::=<for_item1>|<for_item2>

<for_item1>::= FOR <查询表达式>

<for_item2>::= FOR <表达式> [USING <绑定参数> {,<绑定参数>}]

WITH FAST：在打开游标时指定 FAST 属性。OPEN 语句中的 WITH FAST 必须在 DM.INI 参数 ENABLE_FAST_REFCURSOR=1 的情况下才能生效，否则只是语法支持并未生效。

■ 拨动游标和关闭游标

拨动游标和关闭游标的方法和显式游标完全相同。

例 1 定义游标变量 c2 的同时为 c2 赋值。

DECLARE

CURSOR c1 IS SELECT TITLE FROM RESOURCES.EMPLOYEE WHERE MANAGERID = 3;

c2 CURSOR =c1;

begin

open c2;

close c2;

END;

例 2 定义游标变量 c2 时没有赋值，使用时在 BEGIN……END 的 < 执行部分 > 中对 c2 进行赋值。

DECLARE

CURSOR c1 IS SELECT TITLE FROM RESOURCES.EMPLOYEE WHERE MANAGERID = 3;

c2 CURSOR;

BEGIN

c2 =c1;

open c2;

close c2;

END;

例 3 定义游标变量 c1 时没有赋值，在打开 c1 时对其动态关联一条查询语句。

DECLARE

c1 CURSOR;

BEGIN

open c1 FOR SELECT TITLE FROM RESOURCES.EMPLOYEE WHERE MANAGERID = 3;

close c1;

END;

例 4 使用游标表达式对 c1 进行赋值。

DECLARE

c1 CURSOR;

v_title VARCHAR(50);

BEGIN

c1:= CURSOR (SELECT TITLE FROM RESOURCES.EMPLOYEE WHERE MANAGERID = 3);

LOOP

FETCH c1 INTO v_title;

EXIT WHEN c1%NOTFOUND;

PRINT v_title;

END LOOP;

close c1;

END;

例 5 打开游标的同时赋值。

DECLARE

c1 CURSOR;

v_title VARCHAR(50);

BEGIN

OPEN c1 FOR SELECT TITLE FROM RESOURCES.EMPLOYEE WHERE MANAGERID = 3;

LOOP

FETCH c1 INTO v_title;

EXIT WHEN c1%NOTFOUND;

PRINT v_title;

END LOOP;

close c1;

END;

2.游标变量的特性

与常规的游标相比，游标变量具有以下特性：

游标变量不局限于一个查询，可以为一个查询声明或者打开一个游标变量，然后对其结果集进行处理，之后又可以将这个游标变量为其它的查询打开；
可以对游标变量进行赋值；
可以像用一个变量一样在一个表达式中使用游标变量；
游标变量可以作为一个子程序的参数；
可以使用游标变量在 DMSQL 程序的不同子程序中传递结果集。同子程序中传递结果集。

例 1 使用游标变量在存储函数中传递结果集。C 为游标变量。

CREATE OR REPLACE FUNCTION F1(C OUT CURSOR) RETURN INT

AS

CNT INT := 0;

BEGIN

       OPEN C;

    LOOP

       FETCH C;

       EXIT WHEN C%NOTFOUND;

       CNT := CNT + 1;

    END LOOP;

     CLOSE C;

     RETURN CNT;

END;

（四）引用游标

引用游标是一种 <REF 类型名 > 类型的游标变量。引用游标实现了在程序间传递结果集的功能

1.定义 REF CURSOR 类型

语法如下：

TYPE <REF类型名> IS REF CURSOR [RETURN <DMSQL数据类型>];

参数说明：

<REF 类型名 >：是游标类型的名字；

<DMSQL 数据类型 >：是该游标类型返回的数据类型。

2.声明引用游标变量

语法如下：

<引用游标变量名> <REF类型名>;

参数说明：

< 引用游标变量名 >：为游标变量的名字；

<REF 类型名 >：是上一步定义的游标类型名字。

3.打开游标

打开引用游标，关联结果集。打开方法和游标变量完全相同，请参考 5.2.3 游标变量。

4.拨动游标和关闭游标

拨动游标和关闭游标的方法和显式游标完全相同。请参考 5.2.1.2 显式游标。

举例说明：

例使用引用游标在子程序中传递结果集。

DECLARE

TYPE Emptype IS REF CURSOR RETURN PERSON.PERSON%ROWTYPE;

emp Emptype;

PROCEDURE process_emp(emp_v IN Emptype) IS

person PERSON.PERSON%ROWTYPE;

BEGIN

LOOP

FETCH emp_v INTO person;

EXIT WHEN emp_v%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('姓名：'||person.NAME || ' 电话：' || person.PHONE);

END LOOP;

END;

BEGIN

OPEN emp FOR SELECT A.* FROM PERSON.PERSON A,RESOURCES.EMPLOYEE B WHERE A.PERSONID=B.PERSONID;

process_emp(emp);

CLOSE emp;

END;

/

使用静态游标更新、删除数据

可以使用静态游标更新或删除结果集中的数据。若需要使用游标更新或删除数据，则在游标关联的查询语句中一定要使用“FOR UPDATE 选项”。FOR UPDATE 选项出现在查询语句中，用于对要修改的行上锁，以防止用户在同一行上进行修改操作。

当游标拨动到需要更新或删除的行时，就可以使用 UPDATE/DELETE 语句进行数据更新/删除。此时必须在 UPDATE/DELETE 语句结尾使用“WHERE CURRENT OF 子句”，以限定删除/更新游标当前所指的行。

语法如下：

< WHERE CURRENT OF子句> ::=WHERE CURRENT OF <游标名>

举例说明：

例 1 使用游标更新表中的数据。

DECLARE

CURSOR csr is SELECT SALARY FROM RESOURCES.EMPLOYEE WHERE TITLE='销售经理' FOR UPDATE;

BEGIN

OPEN csr;

IF csr%ISOPEN THEN

FETCH csr;

UPDATE RESOURCES.EMPLOYEE SET SALARY = SALARY + 10000 WHERE CURRENT OF csr;

ELSE

PRINT 'CURSOR IS NOT OPENED';

END IF;

CLOSE csr;

END;

/

例 2 使用游标删除表中的数据。

DECLARE

CURSOR dcsr is SELECT EMPNO FROM OTHER.EMPSALARY WHERE ENAME='KING' FOR UPDATE;

BEGIN

OPEN dcsr;

IF dcsr%ISOPEN THEN

FETCH dcsr;

DELETE FROM OTHER.EMPSALARY WHERE CURRENT OF dcsr;

ELSE

PRINT 'CURSOR IS NOT OPENED';

END IF;

CLOSE dcsr;

END;

/

（五）使用游标 FOR 循环

游标通常与循环联合使用，以遍历结果集数据。实际上，DMSQL 程序还提供了一种将两者综合在一起的语句，即游标 FOR 循环语句。游标 FOR 循环自动使用 FOR 循环依次读取结果集中的数据。当 FOR 循环开始时，游标会自动打开（不需要使用 OPEN 方法）；每循环一次系统自动读取游标当前行的数据（不需要使用 FETCH）；当数据遍历完毕退出 FOR 循环时，游标被自动关闭（不需要使用 CLOSE），大大降低了应用程序的复杂度。

1.隐式游标 FOR 循环

语法如下：

FOR <cursor_record> IN (<查询语句>)

LOOP

<执行部分>

END LOOP;

参数说明：

<cursor_record>：一个记录类型的变量。它是 DMSQL 程序根据 SQL%ROWTYPE 属性隐式声明出来的，不需要显式声明。也不能显式声明一个与<cursor_record>同名的记录，会导致逻辑错误。

FOR循环不断地将行数据读入变量<cursor_record>中，在循环中也可以存取<cursor_record>中的字段。

举例说明：

例使用隐式游标 FOR 循环。

BEGIN

FOR v_emp IN (SELECT * FROM RESOURCES.EMPLOYEE)

LOOP

DBMS_OUTPUT.PUT_LINE(V_EMP.TITLE || '的工资' || V_EMP.SALARY);

END LOOP;

END;

/

2.显式游标 FOR 循环

语法如下：

FOR <cursor_record> IN <游标名>

LOOP

<执行部分>

END LOOP;

显式游标 FOR 循环与隐式游标的语法和使用方式都非常相似，只是关键字“IN”后不指定查询语句而是指定显式游标名，<cursor_record>则为<游标名>%ROWTYPE 类型的变量。

例使用显式的游标 FOR 循环。

DECLARE

CURSOR cur_emp IS SELECT * FROM RESOURCES.EMPLOYEE;

BEGIN

FOR V_EMP IN CUR_EMP LOOP

DBMS_OUTPUT.PUT_LINE(V_EMP.TITLE || '的工资' || V_EMP.SALARY);

END LOOP;

END;

/

四、系统表和视图

达梦数据库中的动态性能视图能自动收集数据库中的一些活动信息，系统管理员根据这些信息可以了解数据库运行的基本情况，为数据库的维护和优化提供依据。动态性能视图信息也是数据库中数据字典的一部分，与我们平常所说的数据字典不同的是，平常意义上的数据字典是指静态数据字典信息，也即用户访问数据字典信息时，内容不会发生改变，而动态视图信息是随着数据库的运行随时更改，具有一定的即时性。

系统管理员为了更好地了解数据库的一些运行时信息，可以查询动态视图表。首先系统管理员需要知道达梦数据库中提供了多少动态视图，有哪些类型动态视图，以及这些动态视图的用途是什么。关于这些内容可以参考附录 2，也可以通过查询 V$DYNAMIC_TABLES 获得所有的动态性能视图名称。

动态视图表与静态字典信息表命名方式不同，静态字典表一般以 SYS 为前缀，如系统表 SYSINDEXES，而动态视图则以 V$为前缀，如 V$DM_INI。

（一）系统信息

包括数据库版本、实例、统计信息、资源限制信息、进程信息、全局索引 IID 信息、事件信息；涉及的动态视图有 V$SESSIONS、V$INSTANCE、V$RESOURCE_LIMIT、V$PROCESS、V$IID、V$SYSSTAT 等。

（二）存储信息

包括数据库信息、表空间信息、数据文件信息、日志相关信息；涉及的动态视图有 V$DATAFILE、V$DATABASE、V$TABLESPACE、V$HUGE_TABLESPACE、V$RLOGFILE 等。

（三）内存管理信息

包括内存池使用情况、BUFFER 缓冲区信息、虚拟机信息、虚拟机栈帧信息；涉及的动态视图有 V$MEM_POOL、V$VMS、V$STKFRM、V$BUFFERPOOL、V$BUFFER_LRU_FIRST、V$BUFFER_UPD_FIRST、V$BUFFER_LRU_LAST、V$BUFFER_UPD_LAST、V$RLOG_PKG、V$COSTPARA 等。

（四）事务信息

包括所有事务信息、当前事务可见的事务信息、事务锁信息（TID 锁、对象锁）、回滚段信息、事务等待信息；涉及的动态视图有 V$TRX、V$TRXWAIT、V$TRX_VIEW、V$LOCK、V$PURGE 等。

（五）线程信息

包括所有活动线程信息、线程作业信息、线程锁信息、线程的资源等待信息；涉及的动态视图有 V$THREADS、V$LATCHES 等。

（六）历史模块

包括SQL历史信息、SQL执行节点历史信息、检查点历史信息、命令行历史信息、线程等待历史信息、死锁历史信息、回滚段历史信息、运行时错误历史信息、DMSQL程序中执行DDL语句的历史信息、返回大数据量结果集的历史信息、所有活动过线程的历史信息；涉及的动态视图有V$CKPT_HISTORY、V$CMD_HISTORY、V$DEADLOCK_HISTORY、V$PLSQL_DDL_HISTORY、V$PRE_RETURN_HISTORY、V$RUNTIME_ERR_HISTORY、V$WAIT_HISTORY、V$SQL_HISTORY、V$SQL_NODE_HISTORY、V$SQL_NODE_NAME等。

（七）缓存信息

包括 SQL 语句缓存、执行计划缓存、结果集缓存、字典缓存信息、字典缓存中的对象信息、代价信息；涉及的动态视图有 V$CACHEITEM、V$SQL_PLAN、V$CACHERS、V$CACHESQL、V$DICT_CACHE_ITEM、V$DICT_CACHE 等。

（八）会话信息

包括连接信息、会话信息；涉及的动态视图有 V$CONNECT、V$STMTS、V$SESSIONS 等。

（九）常用的系统表

系统表名	作用
SYSOBJECTS	可查询所有对象
SYSCOLUMNS	可查询表的所有列的信息
dba_tables	可查询表信息（包括系统表）
dba_users	可查询系统所有用户
dba_data_files	可查询数据库文件信息
dba_segments	可查询模式
all_tables	可查询所有用户的表
user_tables	可查询当前用户所拥有的所有表
user_tablespaces	可查询表空间

产品手册 | 达梦技术文档

→_→星空←_←

关注

8
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
DM存储模块及系统视图

CREATE [OR REPLACE] PROCEDURE [IF NOT EXISTS] <过程声明> <AS_OR_IS> <模块体><过程声明> ::= <存储过程名定义> [WITH ENCRYPTION][(<参数名><参数模式><参数类型> [<默认值表达式>]{,<参数名><参数模式><参数类型> [<默认值表达式>] })][<调用权限子句>]<存储过程名定义> ::=[<模式名>.]<存储过程名><AS_OR_IS>::= AS | IS<模块体> ::= [<声明部分>]
复制链接

扫一扫