DMSQL 程序的定义、调用与删除

本章主要介绍如何使用DMSQL程序语言来定义存储模块及客户端DMSQL程序。为使用户获得对DMSQL程序的整体印象,本节中提供了一些例子,对于例子中涉及到的各种控制语句,其详细的使用方法将在第五章进行介绍。

3.1 存储过程

定义存储过程的语法如下:

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

<过程声明> ::= <存储过程名定义> [WITH ENCRYPTION][(<参数名><参数模式><参数类型> [<默认值表达式>]
{,<参数名><参数模式><参数类型> [<默认值表达式>] })][<调用权限子句>]

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

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

			BEGIN

			<执行部分>

			[<异常处理部分>]

			END [存储过程名]

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

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

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

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

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

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

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

参数说明:

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

DBA或具有CREATE PROCEDURE权限的用户可以使用上述语法新创建一个存储过程。OR
REPLACE选项的作用是当同名的存储过程存在时,首先将其删除,再创建新的存储过程,前提条件是当前用户具有删除原存储过程的权限,如果没有删除权限,则创建失败。使用OR
REPLACE选项重新定义存储过程后,由于不能保证原有对象权限的合法性,所以全部去除。

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

存储过程可以带有参数,这样在调用存储过程时就需指定相应的实际参数,如果没有参数,过程名后面的圆括号和参数列表就可以省略了。关于参数使用的具体介绍见3.4节。

声明部分进行变量声明,对其的具体介绍见3.5节。

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

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

SQL语句必须以分号结尾,否则语法分析报错。

异常处理部分用于处理存储过程在执行过程中可能出现的错误。

下面是一个定义存储过程的简单示例:

1  	CREATE OR REPLACE PROCEDURE RESOURCES.proc_1(a IN OUT INT) AS

2  		b INT:=10;

3 	BEGIN

4  		a:=a+b;

5  		PRINT a;

6  	EXCEPTION

7  		WHEN OTHERS THEN NULL;

8  END;

9  /

该例子在模式RESOURCES下创建了一个名为proc_1的存储过程。例子中第2行是该存储过程的说明部分,这里声明了一个变量b。注意在DMSQL程序中说明变量时,变量的类型放在变量名称之后。第4行和第5行是该程序块运行时被执行的代码段,这里将a与b的和赋给参数a。如果发生了异常,第6行开始的异常处理部分就对产生的异常情况进行处理。说明部分和异常处理部分都是可选的。如果用户在模块中不需要任何局部变量或者不想处理发生的异常,则可以省略这两部分。

3.2 存储函数

定义存储函数的语法如下:

CREATE [OR REPLACE ] FUNCTION <函数声明><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<异常处理语句>{;<异常处理语句>}

参数说明:

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

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

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

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

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

下面是一个定义存储函数的简单示例:

1 	CREATE OR REPLACE FUNCTION RESOURCES.fun_1(a INT, b INT) RETURN INT AS

2 		s INT;

3 	BEGIN

4 		s:=a+b;

5 		RETURN s;

6 	EXCEPTION

7 		WHEN OTHERS THEN NULL;

8 END;

9 /

这个例子在模式RESOURCES下创建一个名为fun_1的存储函数。第1行说明了该函数的返回类型为INT类型。第4行将两个参数a、b的和赋给了变量s,第5行的RETURN语句则将变量s的值作为函数的返回值返回。

调用这个存储函数:

SELECT RESOURCES.fun_1(1,2);

查询结果为3。

下面的例子创建了一个计算函数F1,并在表T中使用其定义列的缺省值。

CREATE OR REPLACE FUNCTION F1 FOR CALCULATE

	RETURN INT

	IS

BEGIN

	RETURN 1;

END;

/

--在表T中使用

CREATE TABLE T(C1 INT, C2 INT DEFAULT F1());

或者CREATE TABLE T(C1 INT, C2 INT DEFAULT F1);

3.3 客户端DMSQL程序

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

客户端DMSQL程序的定义语法与存储过程的定义语法类似,如下:

[<声明部分>]

BEGIN

<执行部分>

[<异常处理部分>]

END
注意

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

读者可能已经发现,本书之前章节的很多示例都是使用的客户端DMSQL程序,对于不需要反复执行的脚本,使用客户端DMSQL程序是一个比较合适的选择。

客户端DMSQL程序无法被其他程序调用,但它可以调用包括存储过程和存储函数等在内的其他函数。例如:

DECLARE

	r INT:=0;

BEGIN

	SELECT RESOURCES.fun_1(1,2)*2 INTO r;

	CALL RESOURCES.proc_1(r);

EXCEPTION

	WHEN OTHERS THEN NULL;

END;

/

3.4 参数

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

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

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

  • 最多能定义不超过1024个参数;
  • IN参数能被赋值;
  • OUT参数的初值始终为空,无论调用该模块时对应的实参值为多少;
  • 调用一个模块时,OUT参数及IN OUT参数的实参必须是可赋值的对象。

下面的例子说明了不同模式的参数的使用方法。例子中在客户端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

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

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

BEGIN

	PRINT a;

	PRINT b;

END;

/

调用过程PROC_DEF_ARG,不指定输入参数值:

CALL proc_def_arg;

系统使用缺省值作为参数值,打印结果为:

abc

123

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

CALL proc_def_arg('我们');

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

我们

123

3.5 变量

变量的声明应在声明部分,其语法为:

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

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

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

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

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

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

在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程序的可执行部分。

下面的例子说明了如何对变量进行定义与赋值。

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

3.6 使用OR REPLACE选项

您可能已经发现,在前面的例子中,我们在创建存储模块的时候,都使用了OR REPLACE选项。使用OR REPLACE选项的好处是,如果系统中已经有同名的存储模块,服务器会删除原先的存储模块,再创建新的存储模块。如果不使用OR REPLACE选项,当创建的存储模块与系统中已有的存储模块同名时,服务器会报错。

当已存在同名的存储模块时,能成功使用OR REPLACE选项的前提条件是当前用户具有删除原存储过程的权限,如果没有删除权限,则创建失败。使用OR REPLACE选项重新定义存储过程后,由于不能保证原有对象权限的合法性,原对象权限全部去除。

3.7 调用权限子句

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

调用权限子句的语法如下:

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

或

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

使用说明:

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

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

3.8 调用、重新编译与删除存储模块

3.8.1 调用存储模块

对存储过程的调用可通过CALL语句来完成,也可以什么也不加直接通过名字及相应的参数执行即可,两种方式没有区别。

对于存储函数,除了可以通过CALL语句和直接通过名字调用外,还可以通过SELECT语句来调用,且执行方式存在一些区别:

  • 通过CALL和直接使用名字调用存储函数时,不会返回函数的返回值,仅执行其中的操作;
  • 通过SELECT语句调用存储函数时,不仅会执行其中的操作,还会返回函数的返回值。SELECT调用的存储函数不支持含有OUT、IN
    OUT模式的参数。

如下面的例子:

CREATE OR REPLACE FUNCTION proc(A INT) RETURN INT AS

DECLARE

	s INT;

	lv INT;

	rv INT;

BEGIN

	IF A = 1 THEN

	s = 1;

	ELSIF A = 2 THEN

	s = 1;

	ELSE

		rv = proc(A - 1);

		lv = proc(A - 2);

	s = lv + rv;

		print lv || '+' || rv || '=' || s;

	END IF;

	RETURN S;

END;

/

通过CALL来调用函数proc:

CALL proc(3);

执行结果为:

1+1=2

使用SELECT来调用这个函数proc:

SELECT proc(3);

则显示结果为:

行号 			PROC(3)

---------- -----------

1 				2

可见,使用CALL调用存储函数会执行其中打印操作,但不返回结果集;而如果用SELECT调用的话就会输出返回值55,这个相当于是结果集。

3.8.2 重新编译存储模块

存储模块中常常会访问或修改一些数据库表、索引等对象,而这些对象有可能已被修改甚至删除,这意味着对应的存储模块已经失效了。

若用户想确认一个存储模块是否还有效,可以重新编译该存储模块。

重新编译存储模块的语法如下:

ALTER PROCEDURE|FUNCTION <存储模块名定义> COMPILE [DEBUG];

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

语法中的“DEBUG”没有实际作用,仅语法支持。

注意

系统存储模块不能进行重新编译。

例如,下面的语句对存储过程RESOURCES.person_account进行重新编译。

ALTER PROCEDURE RESOURCES.person_account COMPILE;

3.8.3 删除存储模块

当用户需要从数据库中删除一个存储模块时,可以使用存储模块删除语句。其语法如下:

DROP PROCEDURE [IF EXISTS] <存储过程名定义>;

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

或

DROP FUNCTION [IF EXISTS] <存储函数名定义>;

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

当模式名缺省时,默认为删除当前模式下的存储模块,否则,应指明存储模块所属的模式。除了DBA用户外,其他用户只能删除自己创建的存储模块。

指定IF EXISTS关键字后,删除不存在的存储过程或者存储函数时不会报错,否则会报错。

例如,下面的语句删除之前创建的存储过程RESOURCES.proc_1和存储函数RESOURCES.fun_1;

DROP PROCEDURE RESOURCES.proc_1;

DROP FUNCTION RESOURCES.fun_1;
微信扫码
分享文档
扫一扫
联系客服