达梦数据库学习之存储过程、存储函数、函数

1、达梦数据库学习之存储过程、存储函数、函数、存储过程1.概述存储过程数据库系统中，组为了完成特定功能的SQL 语句集，它存储在数据库中，次编译后永久有效，户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执它。存储过程是数据库中的个重要对象。在数据量特别庞的情况下利存储过程能达到倍速的效率提升2.语法CREATE OR REPLACE PROCEDURE := WITH ENCRYPTION( ,) :=.:= AS | IS := BEGINEND存储过程名 :=DECLARE:=|;:=; := ;:=:=|:=EXCEPTION;注可执部分是存储过程的核部分，由SQL语句和流控

2、制语句构成。持的SQL语句包括：数据查询语句(SELECT)数据操纵语句(INSERT、DELETE、UPDATE)游标定义及操纵语句(DECLARE CURSOR、OPEN、FETCH、CLOSE)事务控制语句(COMMIT、ROLLBACK)动态SQL执语句(EXECUTE IMMEDIATE)SQL语句必须以分号结尾，否则语法分析报错。、存储函数1.语法CREATE OR REPLACE FUNCTION := WITH ENCRYPTIONFOR CALCULATE( , )RETURN PIPELINED :=. := := | DETERMINISTIC:= AS | IS :=

3、BEGINEND存储函数名 :=DECLARE:=|;:=; := ;:=:=|:=EXCEPTION;注计算函数中不持对表进INSERT、DELETE、UPDATE、SELECT、上锁、设置增列属性；对游标DECLARE、OPEN、FETCH、CLOSE；事务的COMMIT、ROLLBACK、SAVEPOINT、设置事务的隔离级别和读写属性；动态SQL的执EXEC、创建INDEX、创建过程。对于计算函数体内的函数调必须是系统函数或者计算函数。计算函数可以被指定为表列的缺省值。存储函数与存储过程在结构和功能上分相似，主要的差异在于：不能在存储过程的返回语句中带表达式，存储函数必须带表达式；存储

4、过程不能出现在个表达式中，存储函数可以出现在表达式中。三、客户端DMSQL程序1.语法BEGINEND注客户端DMSQL程序法被其他程序调，但它可以调包括存储过程和存储函数等在内的其他函数。客户端DMSQL程序不需要存储，创建后即执，执完毕即被释放。四、存储过程和存储函数中参数的使1.参数在存储过程或存储函数中定义个参数时，必须说明名称、参数模式和数据类型。三种可能的参数模式是：IN(缺省模式)、OUT和IN OUT，意义分别为：IN：输参数，来将数据传送给模块；OUT：输出参数，来从模块返回数据到进调的模块；IN OUT：既作为输参数，也作为输出参数。注最多能定义不超过1024个参数；IN参

5、数能被赋值；OUT参数的初值始终为空，论调该模块时对应的实参值为多少；调个模块时，OUT参数及IN OUT参数的实参必须是可赋值的对象。2.变量2.1语法,CONSTANTNOT NULL := DEFAULT | ASSIGN | :=2.2注声明个变量需要给这个变量指定名字及数据类型。变量名必须以字母开头，包含数字、字母、下划线以及$、#符号，长度不能超过128字符，并且不能与DM的DMSQL程序保留字相同，变量名与写是关的。赋值符号“:=”或关键字DEFAULT、ASSIGN，可以在定义时为变量指定个缺省值。在DMSQL程序的执部分可以对变量赋值，赋值语句有两种式：1）.直接赋值语句，语

6、法为：:=SET =2）.通过SQL SELECT INTO 或FETCH INTO给变量赋值，语法：SELECT , INTO , FROM , ;FETCH NEXT|PREV|FIRST|LAST|ABSOLUTE N|RELATIVE N INTO,;注常量与变量相似，但常量的值在程序内部不能改变，常量的值在定义时赋予，它的声明式与变量相似，但必须包含关键字CONSTANT。变量只在定义它的语句块(包括其下层的语句块)内可见，并且定义在下层语句块中的变量可以屏蔽上层的同名变量。当遇到个变量名时，系统先在当前语句块内查找变量的定义；如果没有找到，再向包含该语句块的上层语句块中查找，如此直

7、到最外层。五、调权限句5.1语法：AUTHID CURRENT_USER sql语句在当前模式下执AUTHID DEFINER sql语句在过程或函数所在的模式下执5.2使说明：AUTHID CURRENT_USER：采调者权限，即SQL语句在当前模式下执AUTHID DEFINER：采定义者权限，即SQL语句在过程或函数所在模式下执5.3调存储模块对存储过程的调可通过CALL语句来完成直接通过名字及相应的参数执通过SELECT语句来调5.4区别通过CALL和直接使名字调存储函数时，不会返回函数的返回值，仅执其中的操作；通过SELECT语句调存储函数时，不仅会执其中的操作，还会返回函数的返回值

8、。SELECT调的存储函数不持含有OUT、IN OUT模式的参数。5.5重新编译存储模块5.5.1语法ALTER PROCEDURE|FUNCTION COMPILE DEBUG; := .5.5.2注语法中的“DEBUG”没有实际作，仅语法持。5.6删除存储模块5.6.1语法DROP PROCEDURE ; := .或DROP FUNCTION ;:= .5.6.2注当模式名缺省时，默认为删除当前模式下的存储模块，否则，应指明存储模块所属的模式。除了DBA户外，其他户只能删除创建的存储模块。六、函数1.分类1.1内置函数1.1.1数值函数函数 GREATEST 功能：求 n1、n2和 n3中

9、最的数。函数 ROUND 功能：返回四舍五到数点后 m位的 n值。m应为个整数，缺省值为 0，m为负整数则四舍五到数点的左边，m为正整数则四舍五到数点的右边。若 m为数，系统将动将其转换为整数。1.1.2字符串函数函数 TO_SINGLE_BYTE 功能：将多字节形式的字符（串）转换为对应的单字节形式函数 REGEXP_COUNT 功能：根据 pattern 正则表达式，从 str字符串的第 position个字符开始查找符合正则表达式的串的个数，并符合匹配参数 match_param。position 默认值为 1， position为正整数，于 0则报错；如果 position为空，则返回

10、NULL。pattern必须符合正则表达式的规则，否则报错。match_param不合法，则报错。1.1.3期函数函数 TO_DATE/TO_TIMESTAMP/TO_TIMESTAMP_TZ 功能：将 CHAR或者 VARCHAR类型的值转换为DATE/TIMESTAMP数据类型。TO_DATE的结果不带毫秒精度，TO_TIMESTAMP的结果带 6位毫秒精度。TO_TIMESTAMP_TZ的结果带上服务器的时区。函数 CURRENT_DATE 功能：返回当前期值，结果类型为 DATE，等价于 CURDATE()。1.1.4空值判断函数函数 COALESCE 功能：返回其参数中第个空的值，如

11、果所有参数均为 NULL，则返回 NULL。如果参数为多媒体数据类型，如 TEXT类型，则系统会将 TEXT类型先转换为 VARCHAR类型或VARBINARY类型，转换的最长度为 8188，超过部分将被截断。函数 IFNULL 功能：当表达式 n1为 NULL时，返回 n1；若 n1为 NULL，则返回表达式 n2的值。若 n1与 n2为不同数据类型时，DM会进隐式数据类型转换，若数据类型转换出错，则会报错。1.1.5类型转换函数1.1.5类型转换函数函数 CAST 功能：将参数 value转换为 type类型返回。类型之间转换的相容性如下表所：表中，“允许”表这种语法有效且不受限制，“”表

12、语法效，“受限”表转换还受到具体参数值的影响。函数 CONVERT 功能：将参数 value 转换为 type 类型返回。其类型转换相容矩阵与函数 CAST()的相同。1.1.6杂类函数函数 LENGTHB 功能：返回value的字节数。函数 FIELD 功能：根据指定元素value在输列表“e1、e2、e3、e4en”中的位置返回相应的位置序号，不在输列表时则返回0。2外部函数2.1C外部函数使 C、C+语编写，在数据库外编译并保存在.dll、.so共享库件中，被户通过 DMSQL程序调的函数。当户调 C 外部函数时，服务器操作步骤如下：先，确定调的（外部函数使的）共享库及函数；然后，通知代

13、理进程作。代理进程装载指定的共享库，并在函数执后将结果返回给服务器。编写案DM结构化参数该案中，户必须使 DM8提供的编写 C外部函数动态库的接，严格按照如下格式书写外部函数的代码。de_data 函数名(de_args *args)C 语函数实现体；标量类型参数该案中，户不必引 DM 提供的外部函数接，可以按照标准的 C 风格编码，使 C 标量类型作为参数类型。使该案编写的 C 函数，只能在使 X86 CPU 的 64 位Windows 系统中，被数据库引作为外部函数。返回类型函数名(参数列表)C 语函数实现体；C 外部函数创建CREATE OR REPLACE FUNCTION .()RETURN EXTERNAL USING ;2.2JAVA外部函数JAVA 外部函数是使 JAVA 语编写，在数据库外编译成的 jar 包，被户通过DMSQL 程序调的函数。JAVA 外部函数的执都通过代理 dmagent 具进，为了执 JAVA 外部函数，需要先