达梦数据库存储过程的特点

1、达梦数据库存储过程的特点达梦数据库允许用户使用系统提供的DMPL/SQL语言创建过程或函数，这些过程或函数象普通的过程或函数一样，有输入、输出参数和返回值，它们与表和视图等数据库对象一样被存储在数据库中，供用户随时调用。存储过程和存储函数在功能上相当于客户端的一段SQL批处理程序，但是在许多方面有着后者无法比拟的优点，它为用户提供了一种高效率的编程手段，成为现代数据库系统的重要特征。25亿网站后台管理系统将存储过程和存储函数统称为存储模块。达梦数据库的存储模块机制是一种技术，而不是一种独立的工具，它是和服务器紧密结合在一起的。可以认为这种技术是执行DMPL/SQL语言的一种机器，它可以接受任何

2、有效的存储模块，按照语言本身所规定的语义执行，并将结果返回给客户。达梦数据库的存储模块机制具有如下优点：1. 提供更高的生产率(1) 在设计应用时，围绕存储过程/函数设计应用，可以避免重复编码，提高生产率；(2) 在自顶向下设计应用时，不必关心实现的细节；(3) 编程方便。2. 便于维护(1) 用户的存储模块在数据库集中存放；(2) 用户可以随时查询、删除它们，而应用程序可以不作任何修改，或只做少量调整。3. 提供更好的性能(1) 存储模块在创建时被编译成伪码序列，在运行时不需要重新进行编译和优化处理，它具有更快的执行速度，可以同时被多个用户调用，并能够减少操作错误；如用sqlserver修改

3、mssql2000的存储过程所有者为dbo，这些小地方还是有些麻烦的。(2) 存储模块在执行时数据对用户是不可见的，提高了数据库的安全性；(3) 存储模块具有更高的可靠性；(4) 存储模块是一种高效访问数据库的机制，网站后台管理模板使用存储模块可减少应用对DM的调用，降低了系统资源浪费，显著提高性能，尤其是在网络上与DM通讯的应用更显著。 DM与Oracle存储过程对比表操作OracleDM差异基本语法存储过程：CREATE OR REPLACE PROCEDURE procedure_name ( argumentIN | OUT | IN OUT type,.argument

4、IN | OUT | IN OUT type) IS | ASprocedure_body其中procedure_name是要创建的过程名，argument是过程的参数名，type是关联参数的类型，procedure_body是构成该过程代码的PL/SQL块。IN,OUT,和IN OUT是参数的模式，如果没有为参数指定模式，则参数缺省的模式是IN。存储函数：CREATE OR REPLACE FUNCTION function_name( argumentIN | OUT | IN OUT type,.argumentIN | OUT | IN OUT type)RETURN return_t

5、ypeIS | ASfunction_body其中function_name是函数的名称，参数argument和type的含义与过程相同， return_type是函数返回值的类型，function_body是包括函数体的P L / S Q L块。IN , OUT,和IN OUT是参数的模式。如果没有为参数指定模式，则参数缺省的模式是IN。存储过程：CREATE OR REPLACE PROCEDURE <存储过程名>(<参数名> <参数模式> <参数类型> ,<参数名> <参数模式> <参数类型>)AS |

6、IS<说明部分><执行部分><异常处理部分>END;存储函数：CREATE OR REPLACE FUNCTION <存储函数名>(<参数名> <参数模式> <参数类型>,<参数名> <参数模式> <参数类型>)RETURN <返回数据类型>AS | IS<说明部分><执行部分><异常处理部分>END;在存储函数中必须使用RETURN语句向函数的调用环境返回一个值。存储函数不能用CALL语句调用，它只能出现在表达式中。存储函数和

7、存储过程很相似，它们的区别在于：1. 存储过程没有返回值，而存储函数有；2. 存储过程中可以没有返回语句，而存储函数必须通过返回语句结束；3. 存储过程的返回语句中不能带表达式，而存储函数必须带表达式；4. 存储过程不能出现在一个表达式中，而存储函数只能出现在表达式中。无创建存储过程CREATE OR REPLACE PROCEDURE <存储过程名定义> (<参数名> <参数模式> <参数类型> <默认值表达式> ,<参数名> <参数模式> <参数类型> <默认值表达式> ) WITH

8、 ENCRYPTION AS | IS<说明部分>BEGIN <执行部分> <异常处理部分>END; CREATE OR REPLACE PROCEDURE <存储过程名定义> (<参数名> <参数模式> <参数类型> <默认值表达式> ,<参数名> <参数模式> <参数类型> <默认值表达式> ) WITH ENCRYPTION AS | IS<说明部分>BEGIN <执行部分> <异常处理部分>END;无撤销存储

9、过程DROP PROCEDURE <存储过程名>DROP PROCEDURE <存储过程名>无调用存储过程CALL <存储过程名>(<参数>,<参数>);1. CALL <存储过程名>(<参数>,<参数>);2. EXEC <存储过程名>(<参数>,<参数>);3. <存储过程名>(<参数>,<参数>);DM支持多种调用方式创建存储函数CREATE OR REPLACE FUNCTION <存储函数名定义> (<

10、;参数名> <参数模式> <参数类型> <默认值表达式>,<参数名> <参数模式> <参数类型> <默认值表达式>)RETURN <返回数据类型>WITH ENCRYPTION AS | IS<说明部分>BEGIN<执行部分><异常处理部分>END;CREATE OR REPLACE FUNCTION <存储函数名定义> (<参数名> <参数模式> <参数类型> <默认值表达式>,<参数名&g

11、t; <参数模式> <参数类型> <默认值表达式>)RETURN <返回数据类型>WITH ENCRYPTION AS | IS<说明部分>BEGIN<执行部分><异常处理部分>END;无撤销存储函数DROP FUNCTION <存储函数名>DROP FUNCTION <存储函数名>无调用存储函数SELECT <存储函数名>(<参数>,<参数>);SELECT <存储函数名>(<参数>,<参数>);无PLSQL支持的

12、结构1. 顺序结构；2. 分支结构，包括条件、循环结构等；3. 迭代结构，包括子过程、子函数的调用。1. 顺序结构；2. 分支结构，包括条件、循环结构等；3. 迭代结构，包括子过程、子函数的调用。无语句块结构DECLARE.BEGIN.EXCEPTION.END;语法格式：DECLARE <变量说明> BEGIN<执行部分><异常处理部分>ENDDMPL/SQL基本的程序单元；一个语句可以当作一个整体SQL语句对待，允许嵌套，可出现在SQL语句能出现的任何地方。无赋值语句variable := expression;给对象赋值；语法格式：1. <赋值对

13、象>:=<值表达式>2. SET <赋值对象>=<值表达式>需要注意的是，使用第2种形式时，不需要冒号“:”。DM支持多种赋值方式变量定义variable data_type:=|DEFAULT default_value;1. 在语句块的说明部分可以定义变量、游标、异常变量、子过程或子函数；2. 需要强调的一点是，一个语句块意味着一个作用域范围，也就是说，在一个语句块的说明部分定义的任何对象，其作用域就是该语句块；3. 暂不支持缺省值的定义。ORACLE支持缺省值；DM不支持缺省值；返回语句RETURN;RETURN <结果值>RETUR

14、N;RETURN <结果值>1. 结束存储模块的运行，将控制返回给调用者；2. 如果从函数返回，同时返回函数的结果。无退出循环EXIT WHEN condition;EXIT WHEN condition;1. EXIT与循环语句一起使用，用于终止循环语句的执行，将控制转移到循环语句的下一个语句；2. 可以无条件终止循环语句；3. 可以带条件终止循环语句，当检测条件满足时才执行EXIT语句。无IF语句IF <condition1> THEN<perform action1>ELSIF <condition2><perform action2

15、>ELSIF <conditionN><perform actionN>ELSE<perform default action>END IF;根据布尔表达式的值，进行程序的分支控制；语法格式：IF <条件表达式> THEN<执行部分>ELSEIF|ELSIF <条件表达式> THEN<执行部分>ELSE <执行部分>END IF;注意：ELSEIF与ELSIF两种写法。DM支持两种语法标记循环语句1. WHILEWHILE <condition> LOOP<statement

16、1><statement2>.END LOOP;2. LOOP <<label>> LOOPstatementsEND LOOP label ;2 FORFOR 循环计数器 IN REVERSE 下限 . 上限 LOOP要执行的语句;END LOOP;5. FOR_QUERYFOR loop_name IN <select_statement> LOOP<statement1><statement2>END LOOP;1. LOOP：循环执行，直至EXIT语句终止；CREATE OR REPLACE PROCUDUR

17、E P2AS A INT;BEGIN A := 0; LOOP IF A>10 THEN EXIT; ELSE A := A+1; END IF; END LOOP;END;2. WHILE：循环检测执行条件，为TRUE时执行，否则退出；CREATE OR REPLACE PROCEDURE P3ASDECLARE A INT;BEGIN A := 10; WHILE A>0 LOOP A := A-1; END LOOP;END;3. FOR：执行指定次数；CREATE OR REPLACE PROCEDURE P4ASDECLARE A INT;BEGIN FOR A IN 1

18、 . 10 LOOP PRINT A; END LOOP;END;4. REPEAT：重复执行，直至达到条件要求。CREATE OR REPLACE PROCEDURE P5ASDECLARE A INT;BEGIN A := 0; REPEAT A := A+1; UNTIL A>10;END;无NULL语句NULL;NULL;1. 不执行任何操作；2. 只用于增强程序的可读性。无打印语句dbms_output.put_line(v_out.xx);PRINT <值表达式>打印调试信息，用于调试存储模块。打印语句不同，但不影响整理逻辑性动态语句执行EXECUTE IMMED

19、IATE commandstringINTO USING IN OUT ;EXECUTE IMMEDIATE commandstringINTO USING IN OUT ;如下例所示：CREATE OR REPLACE PROCEDURE P8AS V_SQL VARCHAR(2000); V_A INT; V_B INT;BEGIN V_SQL := INSERT INTO T1(A,B) VALUES(?,?); V_A := 10; V_B := 10; EXECUTE IMMEDIATE V_SQL USING V_A,V_B; END;无游标的使用1. 定义游标CURSOR cur

20、sor_name(parameter, parameter) IS select_statement;2. 打开游标OPEN cursor_name(parameter => value, parameter=> value);OPEN curso_name FOR query_string USING IN OUTINOUT ;3. 使用游标获取数据FETCH cursor_name INTO variable_list |record_variable ;4. 关闭游标CLOSE cursor_name;5. 游标的属性(1) %FOUND 布尔型属性，当最近一次读记录时成功返

21、回,则值为TRUE；(2) %NOTFOUND 布尔型属性，与%FOUND相反；(3) %ISOPEN 布尔型属性，当游标已打开时返回 TRUE；(4) %ROWCOUNT 数字型属性，返回已从游标中读取的记录数。1. 游标不能作为参数传递，也不能被赋值；2. 游标的四个属性：(1) FOUND：拨动游标，取到数据为真，否则为假；(2) NOTFOUND：拨动游标，取到数据为假，否则为真；(3) ISOPEN：游标打开为真，否则为假；(4) ROWCOUNT：第一次拨动之前为0，否则为拨动游标后已经取得的数据数。3. 游标变量与普通游标不同，游标变量可以指向不同的游标工作区，它为用户提供了更灵

22、活的数据操作方法。定义和打开游标变量的语法与普通游标不同。定义游标变量： <游标变量名> CURSOR 打开游标变量： OPEN <游标变量名> FOR <不带INTO查询表达式> 或 OPEN <游标变量名> FOR <表达式> USING <绑定参数> ,<绑定参数> 其它的游标操作与普通游标相同。例子如下： CREATE OR REPLACE PROCEDURE curvar ASc1 CURSOR;vename CHAR(10);vempno NUMERIC(4);vsal NUMERIC(7,2);B

23、EGIN vsal:=2000;OPEN c1 FOR 'SELECT ENAME, EMPNO FROM OTHER.EMPSALARY where SAL> ?' USING vsal; LOOPFETCH c1 INTO vename, vempno; EXIT WHEN c1%NOTFOUND;PRINT 'NAME = '| vename |' NO = '|vempno;END LOOP;CLOSE c1;END;本例中的OPEN语句也可写成如下等价的形式：OPEN c1 FOR SELECT ENAME, EMPNO FROM

24、 EMP where SAL > vsal;无动态语句游标定义TYPE name_type IS REF CURSOR;name name_type;暂不提供动态游标的支持。此功能非常用功能CASE语句CASE expression1WHEN expression2 THEN<statement1><statement2>.WHEN expression3 THEN<statement3><statement4>.ELSE<statement5><statement6>.END CASE;暂不提供CASE语句的支持。

25、可以使用if语句代替。CASE表达式CASE WHEN condition THENresultWHEN .ELSE resultEND暂不提供CASE表达式的支持。可以使用if语句代替。异常的处理1. 自定义异常声明<异常情况>EXCEPTION;2. 抛出自定义异常RAISE <异常情况>；1. 异常的处理(1) 在模块正常执行的过程中，可能会出现未预料的事件，称之为异常；(2) 异常会导致执行不正确的结束，因此需要进行异常处理；(3) DM提供了一些预定义的异常，与常见的DM错误相对应；(4) OTHERS：特殊的异常名，用于处理没有明确列出的异常。2. 异常变量

26、的定义(1) 用户可以自定义异常变量；<异常变量名> EXCEPTION FOR <错误号>；(2) 其中，FOR子句用来为异常变量绑定错误号（SQLCODE值）；(3) 异常变量类似于一般的变量，必须在块的说明部分说明，有同样的生存期和作用域；(4) 但是异常变量不能作参数传递，也不能被赋值；(5) 需要注意的是，为异常变量绑定的错误号不一定是DM返回的系统错误，但是该错误号必须是一个负整数。3. 抛出异常(1) 当异常发生时，系统会自动抛出异常；(2) 用户也可以主动抛出异常：RAISE <异常名>；(3) 一旦异常抛出，执行将转到相应的异常处理部分。4

27、. 异常处理器(1) 语法格式：<异常处理部分> := EXCEPTION <异常处理语句> <异常处理语句> :=WHEN <异常名> THEN <执行部分> (2) OTHERS必须在最后。5. 异常处理的实例CREATE OR REPLACE PROCEDURE P6AS A INT; E1 EXCEPTION;BEGIN A := A/0; RAISE E1;EXCEPTION WHEN ZERO_DIVIDE THEN PRINT DIVIDED BY ZERO; WHEN E1 THEN PRINT E1; WHEN OT

28、HERS THEN PRINT ERROR;END;无SELECT INTO赋值SELECT select_expressions INTO targetFROM .;EXECUTE IMMEDIATE commandstringINTO USING IN OUTSELECT select_expressions INTO targetFROM .;EXECUTE IMMEDIATE commandstringINTO USING IN OUT无GOTO语句goto label_name;只能由内部的语句块跳往外部块 设置标签<<label_name>>可以为循环设置标

29、签declareid number:=1;beginloop dbms_output.put_line('循环次数-'|id);id :=id+1;if id=10 then goto a;end if;end loop;<<a>>dbms_output.put_line('跳出循环');end;1. GOTO语句无条件地跳转到一个标号所在的位置。2. 标号的定义在一个语句块中必须是唯一的。其语法如下：GOTO <标号名> GOTO语句将控制权交给带有标号的语句或语句块。3. 为了保证GOTO语句的使用不会引起程序的混乱，达梦

30、数据库对GOTO语句的使用有下列限制：(1) GOTO语句不能跳入一个IF语句、循环语句或下层语句块中； (2) GOTO语句不能从一个异常处理器跳回当前块，但是可以跳转到包含当前块的上层语句块。无返回查询结果集CREATE OR REPLACE PROCEDURE p_sel_result(UserTag byte) AS BEGINIF (UserTag = 1) THENSELECT * FROM SYSTABLES WHERE ID > 1000; ELSESELECT * FROM SYSTABLES WHERE ID < 1000;END IF;END;在存储过程中如果

31、执行了不带INTO子句的查询语句，系统将在调用结束时将该查询结果集返回给调用者。当出现多个查询语句时，只有最后被执行的查询语句的查询结果集被返回。下面给出一个例子：CREATE OR REPLACE PROCEDURE p_sel_result(UserTag byte) AS BEGINIF (UserTag = 1) THENSELECT * FROM SYSTABLES WHERE ID > 1000; ELSESELECT * FROM SYSTABLES WHERE ID < 1000;END IF;END;无数据类型引用%TYPE和%ROWTYPE扩展了存储过程类型，方

32、便用户使用。1. %TYPE在许多情况下，存储过程变量可以被用来处理存储在数据库表中的数据。在这种情况下，变量应该拥有与表列相同的类型。2. %ROWTYPE在存储过程中将一个记录声明为具有相同类型的数据库行的作法也很常见。下例子中使用的表T的结果如下： CREATE TABLE T(ID INT, NAME VARCHAR(10); INSERT INTO T VALUES(1, '达梦数据库'); COMMIT;1. %TYPE例如表T中有个字段NAME类型为VARCHAR(20)。对应的在存储过程中，可以声明一个变量： DELCARE V_NAME VARCHAR(20)

33、; 但是如果T中的NAME字段定义发生了变化，比如变为VARCHAR(100)。那么存储过程中的变量V_NAME也要做相应修改为DELCARE V_NAME VARCHAR(100); 如果您有很多的变量以及存储过程代码，这种处理可能是十分耗时和容易出错的。为了解决这个问题，我们提供了%TYPE类型定义方法。%TYPE可以附加在表列或者另外一个变量上，并返回其类型。例如： DECLARE V_NAME T.NAME % TYPE; 通过使用%TYPE，V_NAME将拥有T表的NAME列所拥有的类型；如果表T的NAME列类型定义发生变化，V_NAME的类型也随之自动发生变化，而不需要用户手动修改

34、。%TYPE的各种使用方法： DECLARE V_NAME T.NAME % TYPE; V_STR V_NAME % V_NAME; V_ID INT; V_ID2 V_ID % TYPE; 使用%TYPE是非常好的编程风格，因为它使得过程更加灵活，更加适应对于数据库定义的变更。2. %ROWTYPEDM提供了%ROWTYPE运算符使得这样的处理更为方便。类似与%TYPE，%ROWTYPE将返回一个基于表定义的运算类型。例如，像下面这样的声明DECLARE V_TREC T % ROWTYPE; 将定义一个记录，该记录中的字段将与表T中的列相对应。V_TREC变量会拥有下面这样的结构： （I

35、D INT, NAME VARCHAR(10)） 与%TYPE类似，如果表定义改变了，那么%ROWTYPE定义的变量也会随之改变。%ROWTYPE的使用举例如下： CREATE OR REPLACE PROCEDURE P AS DECLARE V1 T % ROWTYPE; C1 CURSOR; BEGIN OPEN C1 FOR SELECT * FROM T; FETCH C1 INTO V1; PRINT V1.ID; PRINT V1.NAME; END;无记录类型在Oracle中，如果使用记录类型，对于记录内每个字段，可以指定也可以使用%TYPE和%ROWTYPE动态指定。1. 直

36、接指定字段类型假定表定义为 CREATE TABLE myTable ( V_vid INTEGER, V_vname VARCHAR2(10), V_Address VARCHAR2(100) ); 我们定义一个记录类型： TYPE myRecord IS RECORD ( V_vid INTEGER, V_vname VARCHAR2(10), V_Address VARCHAR2(100) ); 在使用自定义记录类型时，每个字段类型和表字段类型相同，并且类型已经指定，执行的时候性能更好。缺点是，表字段类型修改后，还需要修改记录字段类型，有时候可能会忘记，从而出现错误。2. 使用%TYPE

37、和%ROWTYPE动态指定如果使用%TYPE和%ROWTYPE动态指定记录字段类型： TYPE myReord IS RECORD ( V_vid myTable.V_vid%TYPE, V_vname myTable.V_vname%TYPE, V_Address myTable.V_Address%TYPE ); 或myRecord myTable%ROWTYPE ;1. 记录类型定义定义记录类型的语法是： TYPE record_type IS RECORD( field1 type1, field2 type2, fieldn typen);这里，record_type是记录类型名，f

38、ield1到fieldn是记录字段名，type1到typen是记录字段类型。声明记录类型以后，就可以用记录类型定义变量。上面的变量声明可以修改为： DECLARE TYPE t_rec IS RECORD( ID INT, NAME VARCHAR(10); V_REC1 t_rec; V_REC2 t_rec; 上面的例子，先定义了记录类型t_rec，然后定义了t_rec类型的两个变量V_REC1和V_REC2。2. 记录赋值记录类型可以单独对记录中的字段赋值，也可以将记录作为一个整体进行赋值。可以使用点标记引用一个记录中的字段（记录名.字段名），下面例子中对t_rec类型的ID字段和NAM

39、E字段分别进行赋值操作： DECLARE TYPE t_rec IS RECORD( ID INT, NAME VARCHAR(10); V_REC1 t_rec; V_REC2 t_rec; BEGINV_REC1.ID := 100; V_REC1.NAME:= '雷锋' END; 也可以将一个记录直接赋值给另外一个记录。记录赋值，将记录中的字段分别赋值给对应的字段。下面的例子将V_REC1记录赋值给V_REC： DECLARE TYPE t_rec IS RECORD( ID INT, NAME VARCHAR(10); V_REC1 t_rec; V_REC2 t_re

40、c; BEGINV_REC1.ID := 100; V_REC1.NAME:= '雷锋' V_REC2 := V_REC1; END; 如果要进行记录赋值，那么这两个记录必须是相同类型的。如果记录类型不相同，哪怕是记录中的字段类型定义完全一致，系统也会报错。下面的例子，不是一个合法的记录赋值操作： DECLARE TYPE t_rec IS RECORD( ID INT, NAME VARCHAR(10); TYPE t_rec_new IS RECORD( ID INT, NAME VARCHAR(10); V_REC1 t_rec; V_REC2 t_rec_new; BE

41、GINV_REC1.ID := 100; V_REC1.NAME:= '雷锋' V_REC2 := V_REC1; END;也可以使用SELECT语句对记录赋值。这将会从数据库中检索出数据并将该数据存储到记录中。记录中的字段应该与查询选择结果列表中的字段相匹配。举例如下： DECLARE TYPE t_rec IS RECORD( ID INT, NAME VARCHAR(10); V_REC1 t_rec; BEGINSELECT ID, NAME INTO V_REC1 FROM T WHERE ID = 1; PRINT V_REC1.ID; PRINT V_REC1.N

42、AME; END;无2.1.5 触发器功能ORACLEORACLE差异CREATE TIGGERCREATE OR REPLACE TIGGER触发器名 触发时间 触发事件ON操作对象FOR EACH ROWBEGINpl/sql语句ENDCREATE OR REPLACE TIGGER触发器名 触发时间 触发事件ON操作对象FOR EACH ROWBEGINpl/sql语句END无触发器状态修改ALTER TRIGGER 触发器名 DISABLE/ENABLEALTER TRIGGER 触发器名DISABLE/ENABLE无DROP TRIGGERDROP TRIGGER 触发器名DROP TRIGGER 触发器名无DML事件支持INSERT、DELETE、UPDATEINSERT、DELETE、UPDATE无DDL事件支持CREATE、ALTER、DR