一、存储过程
1.概述
存储过程数据库系统中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升
2.语法
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语句必须以分号结尾,否则语法分析报错。
二、存储函数
1.语法
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<异常处理语句>{;<异常处理语句>}
注
计算函数中不支持对表进行INSERT、DELETE、UPDATE、SELECT、上锁、设置自增列属性;
对游标DECLARE、OPEN、FETCH、CLOSE;
事务的COMMIT、ROLLBACK、SAVEPOINT、设置事务的隔离级别和读写属性;
动态SQL的执行EXEC、创建INDEX、创建子过程。
对于计算函数体内的函数调用必须是系统函数或者计算函数。
计算函数可以被指定为表列的缺省值。
存储函数与存储过程在结构和功能上十分相似,主要的差异在于:
存储过程没有返回值,调用者只能通过访问OUT或IN OUT参数来获得执行结果,而存储函数有返回值,它把执行结果直接返回给调用者;
存储过程中可以没有返回语句,而存储函数必须通过返回语句结束;
不能在存储过程的返回语句中带表达式,而存储函数必须带表达式;
存储过程不能出现在一个表达式中,而存储函数可以出现在表达式中。
三、客户端DMSQL程序
1.语法
[<声明部分>]
BEGIN
<执行部分>
[<异常处理部分>]
END
注
客户端DMSQL程序无法被其他程序调用,但它可以调用包括存储过程和存储函数等在内的其他函数。
客户端DMSQL程序不需要存储,创建后立即执行,执行完毕即被释放。
四、存储过程和存储函数中参数的使用
1.参数
在存储过程或存储函数中定义一个参数时,必须说明名称、参数模式和数据类型。
三种可能的参数模式是:IN(缺省模式)、OUT和IN OUT,意义分别为:
IN:输入参数,用来将数据传送给模块;
OUT:输出参数,用来从模块返回数据到进行调用的模块;
IN OUT:既作为输入参数,也作为输出参数。
注
最多能定义不超过1024个参数;
IN参数能被赋值;
OUT参数的初值始终为空,无论调用该模块时对应的实参值为多少;
调用一个模块时,OUT参数及IN OUT参数的实参必须是可赋值的对象。
2.变量
2.1语法
<变量名>{,<变量名>}[CONSTANT]<变量类型>[NOT NULL][<缺省值定义符><表达式>]
<缺省值定义符> ::= DEFAULT | ASSIGN | :=
2.2注
声明一个变量需要给这个变量指定名字及数据类型。
变量名必须以字母开头,包含数字、字母、下划线以及$、#符号,长度不能超过128字符,并且不能与DM的DMSQL程序保留字相同,变量名与大小写是无关的。
用赋值符号“:=”或关键字DEFAULT、ASSIGN,可以在定义时为变量指定一个缺省值。
在DMSQL程序的执行部分可以对变量赋值,赋值语句有两种方式:
1). 直接赋值语句,语法为:
<变量名>:=<表达式>
SET <变量名>=<表达式>
2). 通过SQL SELECT INTO 或FETCH INTO给变量赋值,语法:
SELECT <表达式>{,<表达式>} [INTO <变量名>{,<变量名>}] FROM <表引用>{,<表引用>} …;
FETCH [NEXT|PREV|FIRST|LAST|ABSOLUTE N|RELATIVE N]<游标名> [INTO<变量名>{,<变量名>}];
注
常量与变量相似,但常量的值在程序内部不能改变,常量的值在定义时赋予,它的声明方式与变量相似,但必须包含关键字CONSTANT。
变量只在定义它的语句块(包括其下层的语句块)内可见,并且定义在下一层语句块中的变量可以屏蔽上一层的同名变量。
当遇到一个变量名时,系统首先在当前语句块内查找变量的定义;如果没有找到,再向包含该语句块的上一层语句块中查找,如此直到最外层。
五、调用权限子句
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语句调用存储函数时,不仅会执行其中的操作,还会返回函数的返回值。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中最大的数。
函数 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为空,则返回 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 功能:返回其参数中第一个非空的值,如果所有参数均为 NULL,则返回 NULL。如果参数为多媒体数据类型,如 TEXT类型,则系统会将 TEXT类型先转换为 VARCHAR类型或VARBINARY类型,转换的最大长度为 8188,超过部分将被截断。
函数 IFNULL 功能:当表达式 n1为非 NULL时,返回 n1;若 n1为 NULL,则返回表达式 n2的值。若 n1与 n2为不同数据类型时,DM会进行隐式数据类型转换,若数据类型转换出错,则会报错。
1.1.5类型转换函数
函数 CAST 功能:将参数 value转换为 type类型返回。类型之间转换的相容性如下表所示:表中,“允许”表示这种语法有效且不受限制,“-”表示语法无效,“受限”表示转换还受到具体参数值的影响。
函数 CONVERT 功能:将参数 value 转换为 type 类型返回。其类型转换相容矩阵与函数 CAST()的相同。
1.1.6杂类函数
函数 LENGTHB 功能:返回value的字节数。
函数 FIELD 功能:根据指定元素value在输入列表“e1、e2、e3、e4…en”中的位置返回相应的位置序号,不在输入列表时则返回0。
2外部函数
2.1C外部函数
使用 C、C++语言编写,在数据库外编译并保存在.dll、.so共享库文件中,被用户通过 DMSQL程序调用的函数。
当用户调用 C 外部函数时,服务器操作步骤如下:
首先,确定调用的(外部函数使用的)共享库及函数;然后,通知代理进程工作。代理进程装载指定的共享库,并在函数执行后将结果返回给服务器。
编写方案
DM结构化参数
该方案中,用户必须使用 DM8提供的编写 C外部函数动态库的接口,严格按照如下格式书写外部函数的C代码。
de_data 函数名(de_args *args)
{
C 语言函数实现体;
}
标量类型参数
该方案中,用户不必引用 DM 提供的外部函数接口,可以按照标准的 C 风格编码,使用 C 标量类型作为参数类型。使用该方案编写的 C 函数,只能在使用 X86 CPU 的 64 位非Windows 系统中,被数据库引用作为外部函数。
返回类型函数名(参数列表)
{
C 语言函数实现体;
}
C 外部函数创建
CREATE OR REPLACE FUNCTION [<模式名>.]<函数名>[(<参数列表>)]
RETURN <返回值类型>
EXTERNAL ‗<动态库路径>‘ [<引用的函数名>] USING < C | CS >;
2.2JAVA外部函数
JAVA 外部函数是使用 JAVA 语言编写,在数据库外编译生成的 jar 包,被用户通过DMSQL 程序调用的函数。JAVA 外部函数的执行都通过代理 dmagent 工具进行,为了执行 JAVA 外部函数,需要先启动 dmagent 服务。
当用户调用 JAVA 外部函数时,服务器操作步骤如下:
首先,确定调用(外部函数使用的)jar 包及函数;
然后,通知代理进程工作。代理进程装载指定的 jar 包,并在函数执行后将结果返回给服务器。
生成 jar 包:用户必须严格按照 JAVA 语言的格式书写代码,完成后生成 jar 包。
JAVA 外部函数创建
CREATE OR REPLACE FUNCTION [<模式名>.]<函数名>[(<参数列表>)]
RETURN <返回值类型>
EXTERNAL ‗<jar 包路径>‘ [<引用的函数名>] USING JAVA;