DM存储过程

DM存储过程

一、存储过程概述

存储过程数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

二、语法：

CREATE [OR REPLACE ] PROCEDURE<过程声明><AS_OR_IS><模块体>
<过程声明> ::= <存储过程名定义> [WITH ENCRYPTION][(<参数名><参数模式><参数类型> [<默认值表达式>] {,<参数名><参数模式><参数类型> [<默认值表达式>] })][<调用权限子句> <存储过程名定义> ::=[<模式名>.]<存储过程名>
<AS_OR_IS>::= AS | IS
<模块体> ::= [<声明部分>]
BEGIN
<执行部分>
[<异常处理部分>]
END
[存储过程名]
<声明部分> ::=[DECLARE]<声明定义>{<声明定义>}
<声明定义>::=<变量声明>|<异常变量声明>|<游标定义>|<子过程定义>|<子函数定义>;
<执行部分>::=<DMSQL程序语句序列>{;<DMSQL程序语句序列>}
<DMSQL程序语句序列> ::= [<标号说明>]<DMSQL程序语句>; <标号说明>::=<<<标号名>>>
<DMSQL程序语句>::=<SQL语句>|<控制语句>
<异常处理部分>::=EXCEPTION<异常处理语句>{;<异常处理语句>}

可执行部分是存储过程的核心部分，由SQL语句和流控制语句构成。支持的SQL语句包括：
数据查询语句(SELECT)
数据操纵语句(INSERT、DELETE、UPDATE)
游标定义及操纵语句(DECLARE CURSOR、OPEN、FETCH、CLOSE)
事务控制语句(COMMIT、ROLLBACK)
动态SQL执行语句(EXECUTE IMMEDIATE)
SQL语句必须以分号结尾，否则语法分析报错。

例：创建存储过程test,传入参数a员工职位，得出在JOB表中该职位的ID号和最低，最高工资

create or replace procedure test(a in VARCHAR)
AS
	B1 VARCHAR;B2 INT;B3 INT ; 
BEGIN
	SELECT 
	JOB_ID,
	MIN_SALARY,
	MAX_SALARY INTO B1,B2,B3 
	FROM DMHR.JOB 
	WHERE JOB_TITLE=A;
	print('ID='||B1||'    最低工资:'||B2||'     最高工资:'||B3);
END;

三、存储函数

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<异常处理语句>{;<异常处理语句>}

参数说明：

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

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

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

例：创建一个简单的存储函数定义，结果为2数之和

CREATE OR REPLACE FUNCTION RESOURCES.fun_1(A INT, B INT) RETURN INT AS
     s INT;
BEGIN
 		s:=A+B;
    RETURN s; 	
  EXCEPTION  		
	WHEN OTHERS THEN NULL; 
 END; 
 /

调用

SELECT RESOURCES.fun_1(3,5);

四、变量声明

4.1语法

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

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

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

变量名必须以字母或下划线、KaTeX parse error: Expected 'EOF', got '#' at position 2: 、#̲符号开头，包含数字、字母、下划…、#符号，长度不能超过 128 字符，并且不能与 DM 的 DMSQL 程序保留字相同，变量名与大小写是无关的

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

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

达梦数据库社区地址：https://eco.dameng.com