答：使用signal SQLSTATE　＇ii0002＇ set message_text  = ‘dddd’;  指定SQLSTATE信息返回自定义的错误信息。 注意sqlstate 必须是 5 位字符，可以是 0 – 9 ，不允许大写字符A-Z和其他特殊字符。不允许前两个字符是‘00’。Message_text 信息限制在70字节长度。

举例如下

CREATE PROCEDURE raise ( IN rating INTEGER )

  LANGUAGE SQL

    BEGIN

       DECLARE new_salary DECIMAL(9,2);

       DECLARE service DECIMAL(8,0);

       DECLARE v_empno CHAR(6) DEFAULT '123456';

            SELECT salary, current_date - hiredate

               INTO new_salary, service

               FROM employee

               WHERE empno = v_empno;

            IF service < 600

              THEN SIGNAL SQLSTATE 'II001'

                     SET MESSAGE_TEXT = 'Insufficient time in service.';

            END IF;

            IF rating = 1

             THEN SET new_salary =

                          new_salary + (new_salary * .10);

             ELSEIF rating = 2

             THEN SET new_salary =

                          new_salary + (new_salary * .05);

            END IF;

            UPDATE employee

               SET salary = new_salary

               WHERE empno = v_empno;

     END;

28．Db2中return的限制和使用？

答： return 不允许在触发器中使用。

29．Db2中如何创建trigger？

答：Db2的trigger 和Sybase 的触发器有些区别，Sybase中触发器全部市after方式。

Db2可以定义触发器的触发时机(after,before)

具体格式：

create Trigger info_plu_ti after insert on info_plu_tab Referencing New as new for each row

30．Db2的CASE 控制语句用法和例子？

答：用法举例如下：

   CASE evaluation

WHEN 100 THEN UPDATE employee SET salary = salary * 1.3;

WHEN 90 THEN UPDATE employee SET salary = salary * 1.2;

WHEN 80 THEN UPDATE employee SET salary = salary * 1.1;

ELSE UPDATE employee SET salary = salary * 1.05;

END CASE;

或者：

CASE

WHEN evaluation = 100 THEN UPDATE employee SET salary = salary * 1.3;

WHEN evaluation = 90 THEN UPDATE employee SET salary = salary * 1.2;

WHEN evaluation = 80 THEN UPDATE employee SET salary = salary * 1.1;

ELSE UPDATE employee SET salary = salary * 1.05;

END CASE;

QQ:50839655

email:zhangtao@XHZQ.COM,dekker99@163.com

html://www.xhzq.com

上一页  [1] [2]