内容


比较 IBM DB2 和 IBM solidDB SQL 过程,第 2 部分

比较 SQL 过程中的条件语句、循环语句、错误处理和返回结果集

Comments

系列内容:

此内容是该系列 # 部分中的第 # 部分: 比较 IBM DB2 和 IBM solidDB SQL 过程,第 2 部分

敬请期待该系列的后续内容。

此内容是该系列的一部分:比较 IBM DB2 和 IBM solidDB SQL 过程,第 2 部分

敬请期待该系列的后续内容。

简介

本系列文章比较了 IBM DB2 9.5 SQL 过程语言(也称为 SQL PL)与 IBM solidDB 6.3 SQL 过程语言。DB2 和 solidDB 过程都经过一次编译和解析,然后存储到数据库中供日后执行。尽管这两种语言之间存在一定的相似性,但是也有一些比较显著的差别。

第 1 部分

  • SQL 过程的结构
  • 参数和调用 SQL 过程
  • SQL 过程的变量和分配
  • SQL 过程的过程体
  • SQL 过程的游标
  • 动态 SQL 过程

第 2 部分

SQL 过程的条件语句

本节介绍 DB2 和 solidDB SQL 过程在条件语句方面的差异。

DB2 SQL 过程中的条件语句

SQL PL 过程支持两种类型的条件语句:CASE 和 IF。

CASE 语句

清单 1 展示了一个 SQL 过程的示例,包含一个 CASE 语句和一个 simple-case-statement-when-clause

清单 1. 使用 simple-case-statement-when-clause
CREATE PROCEDURE UPDATE_DEPT (IN p_workdept) 
LANGUAGE SQL 
BEGIN 
DECLARE v_workdept CHAR(3); 
SET v_workdept = p_workdept; 
CASE v_workdept 
        WHEN ’A00’ THEN 
           UPDATE department SET deptname = ’D1’; 
        WHEN ’B01’ THEN 
            UPDATE department SET deptname = ’D2’; 
        ELSE UPDATE department SET deptname = ’D3’; 
END CASE 
END

IF 语句

IF 语句在逻辑上类似于 CASE 语句。有三种类型的 IF 语句:

  • IF THEN
  • IF THEN ELSEIF
  • IF THEN ELSE

IF 语句可能包括 ELSEIF 或 ELSE 子句;END IF 子句用于表示语句的结束。清单 2 展示了一个包含 IF 语句的示例过程。

清单 2. 包含 IF 语句的过程
CREATE PROCEDURE UPDATE_SAL (IN empNum CHAR(6), INOUT rating SMALLINT) 
LANGUAGE SQL 
BEGIN 
    IF rating = 1 THEN 
          UPDATE employee SET salary = salary * 1.10, bonus = 1000 
                 WHERE empno = empNum; 
    ELSEIF rating = 2 THEN 
              UPDATE employee SET salary = salary * 1.05, bonus = 500 
                  WHERE empno = empNum; 
    ELSE
            UPDATE employee SET salary = salary * 1.03, bonus = 0 
                  WHERE empno = empNum; 
    END IF; 
END

solidDB 过程中的条件语句

和 DB2 一样,solidDB 中也有三种类型的 IF 语句:

  • IF THEN
  • IF THEN ELSEIF
  • IF THEN ELSE

它们与 DB2 中使用的 IF 语句等效。但是,与 DB2 不同的是,END IF 语句不需要使用分号。

尽管 solidDB 过程不支持 CASE 语句,但是可以方便地使用 IF 语句替代它。表 1 展示了这一点。

表 1. 条件语句
DB2solidDB
CREATE PROCEDURE update_dept (IN _workdept)
LANGUAGE SQL 
BEGIN 
DECLARE v_workdept CHAR(3); 
SET v_workdept = p_workdept; 
CASE 
    WHEN v_workdept = ’A00’ THEN
       UPDATE department SET 
           deptname = ’D1’; 
    WHEN v_workdept = ’B01’ THEN 
       UPDATE department SET 
               deptname = ’D2’; 
      ELSE UPDATE department SET deptname =
         ’D3’; 
END CASE 
END
“CREATE PROCEDURE update_dept (IN  
           p_workdept CHAR(3))
  BEGIN
      DECLARE v_workdept CHAR(3);
      v_workdept := p_workdept;
      IF v_workdept = ‘A00’ THEN 
         EXEC SQL EXECDIRECT UPDATE 
            department SET deptname = ‘D1’;
      ELSEIF
          v_workdept  = ‘B01’ THEN
               EXEC SQL EXECDIRECT UPDATE 
                   department SET deptname
                      = ‘D2’;
      ELSE
           EXEC SQL EXECDIRECT UPDATE
               Department SET deptname =
                          ‘D3’;
      END IF
   END”;

SQL 过程的循环语句

本节介绍 DB2 和 solidDB SQL 过程在循环语句方面的差异。

DB2 SQL 过程中的循环语句

SQL PL 中支持以下循环语句:

  • FOR
  • LOOP
  • REPEAT
  • WHILE

FOR 语句

FOR 语句用来遍历一个确定的结果集中的行。在执行 FOR 语句时,游标被隐式声明,这样对于 FOR 循环的每一次迭代,取回的下一行就是结果集。循环将一直进行下去,直到结果集中不存在任何行。FOR 循环与 solidDB 过程中的游标处理和 WHILE 循环相对应。

清单 3 展示了一个仅包含简单 FOR 语句的示例 SQL 过程。

清单 3. 一个简单 FOR 语句
CREATE PROCEDURE P()
LANGUAGE SQL
BEGIN ATOMIC 
   DECLARE fullname CHAR(40); 
   FOR v AS cur1 CURSOR FOR 
      SELECT firstnme, midinit, lastname FROM employee 
   DO
          SET fullname = v.lastname || ’,’ || v.firstnme ||’ ’ || v.midinit; 
          INSERT INTO tnames VALUES (fullname); 
  END FOR; 
END

LOOP 语句

LOOP 语句没有终止条件子句。它定义了一系列反复执行的语句,直到一条控制语句强制控制流跳转到循环外的某个位置。LOOP 语句通常和以下转移控制语句之一结合使用:

  • LEAVE
  • GOTO
  • ITERATE
  • RETURN

清单 4 展示了一个 LOOP 语句的示例。

清单 4. LOOP 语句的示例
CREATE PROCEDURE ITERATOR() 
LANGUAGE SQL 
BEGIN 
   DECLARE v_deptno CHAR(3); 
   DECLARE v_deptname VARCHAR(29); 
   DECLARE at_end INTEGER DEFAULT 0; 
   DECLARE not_found CONDITION FOR SQLSTATE ’02000’; 
   DECLARE c1 CURSOR FOR SELECT deptno, deptname
           FROM department ORDER BY deptno; 
   DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1; 
   OPEN c1; 
   ins_loop: LOOP 
           FETCH c1 INTO v_deptno, v_deptname; 
           IF at_end = 1 THEN
              LEAVE ins_loop; 
           ELSEIF v_dept = ’D11’ THEN 
               ITERATE ins_loop; 
          END IF; 
          INSERT INTO department (deptno, deptname) VALUES (’NEW’, v_deptname); 
   END LOOP; 
   CLOSE c1; 
END

WHILE 语句

WHILE 语句定义了一个语句集合,如果在 WHILE 循环开始时某个条件的求值结果为 false,那么就将执行这个语句集合。while-loop-condition 在循环的每次迭代开始之前计算。DB2 SQL WHILE 循环等效于 solidDB 过程中的 WHILE-LOOP。

清单 5 展示了一个包含 WHILE 循环的示例 SQL 过程。

清单 5. 使用 WHILE 循环
CREATE PROCEDURE sum_mn (IN p_start INT, IN p_end INT , OUT p_sum INT) 
SPECIFIC sum_mn 
LANGUAGE SQL 
smn: BEGIN
   DECLARE v_temp INTEGER DEFAULT 0; 
   DECLARE v_current INTEGER; 
   SET v_current = p_start; 
   WHILE (v_current <= p_end)  DO 
       SET v_temp = v_temp + v_current; 
       SET v_current = v_current + 1; 
   END WHILE; 
   p_sum = v_current; 
END smn;

REPEAT 语句

REPEAT 语句定义了一个语句集合,如果在 REPEAT 循环结束时某个条件的求值结果为 true,那么就将执行这个语句集合。

清单 6 展示了一个示例 REPEAT 循环。

清单 6. 使用 REPEAT 循环
CREATE PROCEDURE sum_mn2 (IN p_start INT, IN p_end INT, OUT p_sum INT)
SPECIFIC sum_mn2 
LANGUAGE SQL 
smn2: BEGIN
 DECLARE v_temp INTEGER DEFAULT 0; 
 DECLARE v_current INTEGER; 
 SET v_current = p_start; 
 REPEAT SET v_temp = v_temp + v_current; 
    SET v_current = v_current + 1; 
    UNTIL (v_current > p_end) 
 END REPEAT; 
END

控制转移语句

有 4 种语句可以在 DB2 SQL 过程中转移控制。这些语句分别为:

  • GOTO:将控制转移到过程中的一个特定标签
  • ITERATE:将控制转移到某个带标签的循环的开始部分
  • LEAVE:将控制转移到循环之外或一个复合语句
  • RETURN:将一个整数值和控制流返回给存储过程的调用方

solidDB 过程中只支持 LEAVE 和 RETURN 语句。

solidDB 过程中的循环语句

WHILE-LOOP 是 solidDB 过程中惟一受支持的循环结构。大部分 DB2 循环结构都可以转换为 solidDB WHILE-LOOP。WHILE-LOOP 将一个条件与使用关键字 LOOP 和 END LOOP 包围的一个语句序列关联在一起,如清单 7 所示。

清单 7. 使用 WHILE-LOOP
WHILE condition LOOP
    statement_list;
END LOOP

在循环的每次迭代开始之前,将对条件进行求值。如果条件的求值结果为 TRUE,那么就将执行语句列表,在循环的顶部恢复控制。如果条件的求值结果为 FALSE 或 NULL,那么将绕过循环,并且控制被传递给下一条语句。

清单 8 展示了一个使用迭代 WHILE-LOOP 的例子。

清单 8. 使用迭代 WHILE-LOOP
WHILE total <= 25000 LOOP
   ...
   total := total + salary;
END LOOP

对于在 DB2 中使用 REPEAT 和 LOOP 结构的逻辑,在 solidDB 中应该重写为 WHILE-LOOP 结构。

表 2 比较了 DB2 过程中的 FOR 循环和 solidDB 中的等效处理。

表 2. 循环结构
DB2solidDB
CREATE PROCEDURE P()
LANGUAGE SQL
BEGIN ATOMIC 
   DECLARE fullname CHAR(40); 
   FOR v AS cur1 CURSOR FOR 
      SELECT firstnme, midinit, lastname FROM
             employee 
   DO
          SET fullname = v.lastname || ’,’ 
           || v.firstnme ||’ ’ ||
                        v.midinit; 
          INSERT INTO tnames VALUES
             (fullname); 
  END FOR; 
END
“CREATE PROCEDURE P
BEGIN
   DECLARE fullname CHAR(40);
   DECLARE fn CHAR(15);
     DECLARE ln CHAR (20);
   DECLARE mi CHAR (2);    
   EXEC SQL PREPARE cur1
         SELECT firstnme, midinit, lastname 
           FROM EMPLOYEE;
    EXEC SQL PREPARE cur2
         INSERT INTO tnames VALUES(?); 
    EXEC SQL EXECUTE cur1 INTO (fn, mi,
                    ln);
    EXEC SQL FETCH cur1;
    WHILE (SQLSUCCESS) LOOP
       SET fullname = ln + ’,’ + fn + ’ ’ +
                  mi;
       EXEC SQL EXECUTE cur2 USING
            (fullname);
       EXEC SQL FETCH cur1;
    END LOOP;
  END”;

Leaving 循环

有时需要强制将过程提前结束循环。与在 DB2 中一样,可以使用 LEAVE 关键字在 solidDB 中实现这一点,如清单 9 所示。

清单 9. 使用 LEAVE 关键字
WHILE total < 25000 LOOP
   total := total + salary;
   IF exit_condition THEN
      LEAVE;
   END IF
END LOOP
statement_list2

SQL 过程的错误处理

本节介绍 DB2 和 solidDB SQL 过程在错误处理方面的差异。

DB2 SQL 过程中的错误处理

SQLCODE 和 SQLSTATE 变量

要使用 SQLCODE 和 SQLSTATE 值,就必须像清单 10 那样声明这些变量。

清单 10. 声明 SQLCODE 和 SQLSTATE 变量
DECLARE SQLCODE INTEGER DEFAULT 0; 
DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’;

DB2 在执行语句时将隐式地设置这些变量。如果某条语句发出一个存在处理程序的条件,那么 SQLSTATE 和 SQLCODE 变量的值就可以在开始执行处理程序时获得。将 SQLSTATE 和 SQLCODE 的值复制到处理程序的第一个语句的本地变量中,这是一种常见的做法,如清单 11 所示。

清单 11. 将值复制到本地变量中
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND 
SET retcode = SQLCODE; 
    executable-statements 
END

条件处理程序

条件处理程序确定在出现一个条件时 SQL 过程的行为。条件可能是下列之一:

  • SQL 警告(SQLWARNING)
  • SQL 错误(SQLEXCEPTION)
  • 没有发现条件(NOT FOUND)
  • 特殊的 SQLCODE 或 SQLSTATE 代码

您可以在 SQL 过程中声明一个或多个条件处理程序。

如果 SQL 过程中有一个语句发出 SQLWARNING 或 NOT FOUND 条件,并且为相应的条件声明了一个处理程序,那么 DB2 将把控制转移到相应的处理程序。清单 12 展示了一个处理程序,它将在遇到 SQL 警告和 not found 条件时退出过程。

清单 12. 声明处理程序
DECLARE EXIT HANDLER FOR SQLWARNING, NOT FOUND
 BEGIN                
        GET DIAGNOSTICS EXCEPTION 1 err_str = MESSAGE_TEXT;                
        SET sql_err = SQLCODE;                
        SET message = 'Unknown Error - ' || err_str;            
  END;

如果没有为条件声明处理程序,DB2 将把控制器传递到 SQL 过程体中的下一个语句。然而,如果 SQLCODE 和 SQLSTATE 变量都已被声明,那么它们将为语句包含相应的值,并且可以检查这些值。

如果 SQL 过程的某条语句发出 SQLEXCEPTION 条件,并且您为特定的 SQLSTATE 或 SQLEXCEPTION 条件声明了处理程序,那么 DB2 将把控制器传递给该处理程序。如果已经声明了 SQLSTATE 和 SQLCODE 变量,那么在成功执行处理程序后,它们的值将被分别重置为 000000

清单 13 展示了一个特定的错误如何将控制转移给退出处理程序,其中根据 SQLCODE 的值返回用户定义的错误消息。

清单 13. 特定的错误将控制转移给退出处理程序
DECLARE EXIT HANDLER FOR SQLSTATE '23505', SQLSTATE '23503'
 BEGIN               
        GET DIAGNOSTICS EXCEPTION 1 err_str = MESSAGE_TEXT;                
         SET sql_err = SQLCODE;                
         IF sql_err = -803 THEN 
              SET message = 'Duplicate Account Code';                
         ELSE
               SET message = 'Unknown User Specified';                
         END IF;     
   END;

如果 SQL 过程中的语句发出一个 SQLEXCEPTION 条件,并且没有为特定的 SQLSTATE 或 SQLEXCEPTION 条件声明处理程序,那么 DB2 将终止 SQL 过程并返回给调用者。

SIGNAL 语句

SIGNAL 语句用于显式地发出一个错误并强制调用处理程序。它将促使返回一个错误或警告,包含指定的 SQLSTATE 以及可选的消息文本。任何有效 SQLSTATE 值都可以用于 SIGNAL 语句中,包括用户定义的 SQLSTATE 和条件。然而,建议您根据为应用程序预留的特定范围定义用户定义的 SQLSTATE。以 79,或以 IZ 开头的 SQLSTATE 类可用于这个目的。

清单 14 展示了一个示例 SIGNAL 语句。

清单 14. 使用 SIGNAL 语句
CREATE PROCEDURE submit_order (IN onum INTEGER, IN cnum INTEGER, IN pnum INTEGER,  
               IN qnum INTEGER)
SPECIFIC SUBMIT_ORDER
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
      DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’;
      DECLARE EXIT HANDLER FOR SQLSTATE VALUE '23503'
             SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = 'Customer number is not known';
      INSERT INTO ORDERS (ORDERNO, CUSTNO, PARTNO, QUANTITY) VALUES (ONUM, CNUM, PNUM,
                  QNUM);
END

在清单 14 中,表插入操作失败,因为客户编号违背了外键约束(SQLSTATE 23503)。INSERT 错误将控制转移到 SQLSTATE 代码的退出处理程序。退出处理程序发出一个用户定义的错误代码(SQLSTATE 75002)和消息文本声明。用户定义的错误代码针对现有的过程,以 OUT 参数的形式返回。

RETURN 语句

在 SQL 过程中,可以将状态设置为返回给调用程序。状态使用一个整数表示,表示过程是否成功。如果没有设置状态,DB2 将根据 SQLCODE 的值将状态设置为 0-1。对于大于或等于 0 的 SQLCODE,DB2 将返回的状态设置为 0,如果 SQLCODE 小于 0,则状态被设置为 -1

下面的语句展示了调用另一个过程的过程如何检索嵌套过程的返回状态:GET DIAGNOSTICS RETVAL = RETURN_STATUS ;

solidDB 过程中的错误处理

当在过程中遇到了错误,将自动填充一些错误变量。这些错误变量(可能不需要进行显式声明)分别为 SQLSUCCESS、SQLERRNUM 和 SQLERRSTR。

根据所遇到的错误的类型和过程的编码方式,可以使用不同的方法处理错误。在执行期间的 PREPARE 阶段生成的错误,比如 table does not exist 错误,会立即出现并且无法捕捉。在这种情况下,过程会立即退出并向调用者返回一个错误状态。SQLERRNUM 和 SQLERRSTR 变量将自动使用错误值填充。

对于在执行期间的 EXECUTE 或 FETCH 阶段产生的错误,比如 unique key constraint 违背,可以以多种方式返回。如果错误发生并且使用了 RETURN SQLERROR 或 WHENEVER SQLERROR 子句,那么 SQLERRNUM 和 SQLERRSTR 将自动填充,并且过程向调用者返回错误代码和失败的执行状态。或者,可以编写过程来捕捉 SQLERRNUM 和 SQLERRSTR 的值并通过用户定义 OUT 或 RETURNS 变量返回给调用者。对于后一种情况,过程向调用者返回一个成功的执行状态。

后面的小节将进一步描述 solidDB 过程中的每一种错误处理。

SQLSUCCESS

在过程体内部执行的每个 EXEC SQL 过程语句都被存储到变量 SQLSUCCESS 中。如前所述,这个变量将针对每个过程自动生成。如果前面的 SQL 语句执行成功,1 的值将被存储到 SQLSUCCESS 中。如果 SQL 语句失败,0 的值将存储到 SQLSUCCESS 中。

SQLSUCCESS 的值通常用来确定游标何时达到结果集的末尾,如清单 15 所示。

清单 15. SQLSUCCESS 展示了结果集的末尾
EXEC SQL FETCH sel_tab;
WHILE SQLSUCCESS LOOP
    EXEC SQL FETCH sel_tab;
END LOOP

当 FETCH 游标语句失败并且没有找到另一个要检索的行,SQLSUCCESS 的值被设置为 0 并且 WHILE LOOP 结束。

SQLERRNUM

该变量包含最近执行的一条 SQL 语句的错误代码。它将针对每个过程自动生成。成功执行后,SQLERRNUM 包含 0。这个变量类似于 DB2 中的 SQLCODE。

在表 3 右侧列的 solidDB 示例中,使用 SQLSUCCESS 变量检查 INSERT 语句的状态。如果语句不成功,将使用 RETURN 语句和 RETURNS 子句捕捉 SQLERRNUM 的值并返回给调用者。表 3 还显示用户定义的错误消息也被返回。在这个场景中,过程成功执行并将 0 的返回码返回给调用者。

对于表 3 左侧列的 DB2 示例,如果某条 SQL 语句失败并生成一个错误,那么控制将被传递给条件处理程序。将针对由 SQLSTATE 标识的两个特定错误声明处理程序。当 INSERT 语句失败并且错误代码在 DECLARE EXIT HANDLER 语句中指定,那么控制将传递到处理程序,而过程将退出。错误代码和消息被作为 OUT 参数返回给调用者。

表 3. 错误处理比较
DB2solidDB
CREATE PROCEDURE account(IN…, OUT ret_val1 VARCHAR(20), OUT ret_val2 INT)
…
BEGIN
  DECLARE SQLCODE ….;
  DECLARE EXIT HANDLER FOR SQLSTATE 
    '23505', SQLSTATE '23503'
  BEGIN               
         SET sql_err = SQLCODE;                
         IF sql_err = -803 THEN 
              SET msg = 'Duplicate Account 
                Code';                
         ELSE
             SET msg = 'Unknown User
                   Specified';                
         END IF;   
        INSERT statement…. …
  END;       
  SET ret_val1 = msg;
  SET ret_val2 = sql_err
END
“CREATE PROCEDURE account (..) RETURNS 
      (msg VARCHAR, sql_err INT)
BEGIN  
    EXEC SQL PREPARE cursor INSERT
                 statement….
    EXEC SQL EXECUTE …insert statement….
    IF NOT SQLSUCCESS THEN
         sql_err := SQLERRNUM;
    IF SQLERRNUM = 10005 THEN
            msg := ‘Duplicate Account 
              Code’;
     ELSEIF SQLERRNUM = 10029 THEN
            msg := ‘Unknown User
                    Specified’;
     END IF;
    . . .
   RETURN;
END”;

注意:DB2 和 solidDB 错误代码是不同的。

SQLERRSTR

该变量包含最后一个失败的 SQL 语句的错误字符串。

在 DB2 中,最后一个失败的 SQL 语句的错误字符串可以通过使用 GET DIAGNOSTICS 语句检索,参见表 4。

表 4. solidDB SQLERRSTR
DB2solidDB
DECLARE err_str VARCHAR(40);
GET DIAGNOSTICS EXCEPTION 1 err_str =  
           MESSAGE_TEXT;
DECLARE err_str VARCHAR(40);
err_str := SQLERRSTR;

SQLROWCOUNT

执行完 UPDATE、INSERT 和 DELETE 语句后,将使用额外的一个变量来检查语句的结果。变量 SQLROWCOUNT 包含受最后一条语句影响的行的数量。

在 DB2 中,最近执行的 UPDATE、INSERT 或 DELETE 语句的行数可以通过 GET DIAGNOSTICS 语句检索,参见表 5。

表 5. 检索行计数
DB2solidDB
DELETE FROM T; 
GET DIAGNOSTICS v_rcount = ROW_COUNT;
DELETE FROM T;
SET v_rcount = SQLROWCOUNT;

SQLERROR

要从过程生成用户错误,可以使用 SQLERROR 变量检索一个实际的错误字符串,该错误字符串使语句无法作用于调用应用程序,如清单 16 所示。

清单 16. 使用 SQLERROR 返回一个错误字符串
RETURN SQLERROR 'error string'
RETURN SQLERROR char_variable

错误使用以下格式返回:User error: error_string。该语句是最接近 DB2 SIGNAL 语句的 solidDB 语句。

在表 6 中,对 Orders 表执行 INSERT 语句失败,因为 custno 违背了外键约束。当执行 RETURN SQLERROR 语句时,处理暂停并将错误代码 10029 返回给调用者。过程并没有成功完成,用户定义消息 Customer number is not known 也被返回。

在表 6 的 DB2 过程示例中,SIGNAL 语句用于生成用户定义的 SQLSTATE 和一条消息,该消息通过 SQLCA 返回给调用方。

表 6. solidDB SQLERROR 变量
DB2solidDB
CREATE PROCEDURE submit_order (IN onum
   INTEGER, IN cnum INTEGER, IN pnum
           INTEGER, IN qnum INTEGER)
SPECIFIC SUBMIT_ORDER
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
      DECLARE SQLSTATE CHAR(5) DEFAULT 
                  ‘00000’;
      DECLARE EXIT HANDLER FOR SQLSTATE
        VALUE '23503' SIGNAL SQLSTATE
        '75002' SET MESSAGE_TEXT = 
            'Customer number is not known';                        
      INSERT INTO ORDERS (ORDERNO, CUSTNO, 
         PARTNO, QUANTITY) VALUES (ONUM,
              CNUM, PNUM, QNUM);
END
“CREATE PROCEDURE submit_order (onum 
       INTEGER, cnum INTEGER, pnum INTEGER,
           qnum INTEGER) 
 BEGIN
   DECLARE err_str VARCHAR(30);
    EXEC SQL PREPARE c1 
        INSERT INTO orders (orderno, 
           custno, partno, quantity) 
               VALUES(?,?,?,?);   
     EXEC SQL EXECUTE c1 USING (onum, 
         cnum, pnum, qnum);
     IF SQLERRNUM = 10029 THEN
        err_str := ‘Customer number is not 
                known’; 
        RETURN SQLERROR err_str;
    END IF;
    EXEC SQL CLOSE c1;
    EXEC SQL DROP c1;
END”;

SQLERROR OF cursorname

该结构用于将实际的错误返回给调用应用程序,这个错误致使语句执行失败,如清单 17 所示。

清单 17. 返回错误
EXEC SQL PREPARE cursorname sql_statement;
EXEC SQL EXECUTE cursorname;
IF NOT SQLSUCCESS THEN
   RETURN SQLERROR OF cursorname;
END IF

在执行该语句时处理将立即停止,而过程返回给调用者的代码是 SQLERROR 的值。

表 7 展示了 solidDB RETURN SQLERROR OF cursorname 语句的使用,与 DB2 中的异常处理形成对比。

表 7. cursorname 语句的 solidDB SQLERROR
DB2solidDB
CREATE PROCEDURE tabs_in_schema (schema_nm
        VARCHAR(8), OUT nr_of_rows INT)
language SQL
BEGIN
    DECLARE tab_nm VARCHAR(128);
    DECLARE SQLSTATE CHAR(5) DEFAULT 
               '00000';
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE v_sqlcode integer default 0;  
    DECLARE rownum INTEGER;
    DECLARE rowcount_not_one CONDITION FOR 
            SQLSTATE '90001';
    DECLARE sel_tab CURSOR FOR
      SELECT tabname FROM syscat.tables 
          WHERE tabschema = schema_nm;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN  
          SET v_sqlcode=SQLCODE;
          GET DIAGNOSTICS rownum = 
               ROW_COUNT;
          IF rownum <> 1 THEN
             SIGNAL rowcount_not_one  SET
                MESSAGE_TEXT = 'insert
                        error';
          END IF;
    END;    
    OPEN sel_tab;
    SET nr_of_rows = 0;
    FETCH FROM sel_tab INTO tab_nm;    
    WHILE (SQLCODE = 0) DO
        SET nr_of_rows = nr_of_rows +1;
        INSERT INTO my_table (table_name,
         schema)VALUES (tab_nm, schema_nm); 
        FETCH FROM sel_tab INTO tab_nm;
    END WHILE;
END@
"CREATE PROCEDURE tabs_in_schema (schema_nm 
     VARCHAR) RETURNS (nr_of_rows INTEGER)
BEGIN
   DECLARE tab_nm VARCHAR;
   EXEC SQL PREPARE sel_tab
	SELECT table_name FROM sys_tables
                   WHERE table_schema = ?;
  EXEC SQL PREPARE ins_tab
        INSERT INTO my_table (table_name, 
           schema) VALUES (?,?);
  nr_of_rows := 0;
  EXEC SQL EXECUTE sel_tab USING
         (schema_nm)INTO (tab_nm);
  EXEC SQL FETCH sel_tab;
  WHILE SQLSUCCESS LOOP
      nr_of_rows := nr_of_rows + 1;
     EXEC SQL EXECUTE ins_tab USING
           (tab_nm, schema_nm);
     IF SQLROWCOUNT <> 1 THEN
         RETURN SQLERROR OF ins_tab;
    END IF;
    EXEC SQL FETCH sel_tab;
  END LOOP
END";

在上面的例子中,DB2 和 solidDB 过程使用 <> 作为 is-not-equal-to 比较符。DB2 还支持 != 作为 is-not-equal-to 比较符,但是 solidDB 不支持这样做。

EXEC SQL WHENEVER SQLERROR

EXEC SQL WHENEVER SQLERROR 语句用于减少在过程中的每个已执行的 SQL 语句后使用 IF NOT SQLSUCCESS THEN 测试的需求。

当这个语句被包含到一个存储过程中后,将对已执行语句的所有返回值进行错误检查。如果语句执行返回一个错误,过程将自动中断或回滚事务。最后一个游标的 SQLERROR 被返回,而最后一个失败的 SQL 语句的错误字符串将被存储到变量 SQLERRSTR 中。

WHENEVER SQLERROR 语句被添加到 solidDB 过程的开始部分,紧随着 BEGIN 关键字之后,并且有些类似于 DB2 EXIT 条件处理程序的行为。表 8 展示了使用 WHENEVER SQLERROR 语句的示例 solidDB 过程,而不是检查每一条语句并在生成错误后退出过程。WHENEVER SQLERROR 语句类似于在 DB2 中声明一个条件处理程序。

表 8. solidDB WHENEVER SQLERROR 语句
DB2solidDB
CREATE PROCEDURE sys_tabs         
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
  DECLARE SQLCODE INTEGER DEFAULT 0;
   DECLARE v_sqlcode INTEGER DEFAULT 0;
  DECLARE sel_tables CURSOR WITH RETURN FOR 
       SELECT tabname FROM syscat.tables
           WHERE tabname LIKE 'SYS%';
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SET v_sqlcode = SQLCODE;
       
    OPEN sel_tables;
END@
"CREATE PROCEDURE sys_tabs RETURNS (tab
        VARCHAR)
BEGIN
  EXEC SQL WHENEVER SQLERROR ABORT;
    EXEC SQL PREPARE sel_tables
        SELECT table_name
             FROM sys_tables
                 WHERE table_name LIKE 
                            ‘SYS%';
    EXEC SQL EXECUTE sel_tables INTO (tab);
    EXEC SQL FETCH sel_tables;
    WHILE SQLSUCCESS LOOP
         RETURN ROW;
         EXEC SQL FETCH sel_tables;
    END LOOP;
    EXEC SQL CLOSE sel_tables;
    EXEC SQL DROP sel_tables;
END";

过程执行跟踪

如果希望在存储过程中跟踪每一条语句,可以启用 proctrace,它将跟踪指定存储过程中的每一条语句。跟踪的输出被写入到 soltrace.out 文件,您可以在 solidDB 工作目录中找到该文件。

要针对指定的过程启用 proctrace,可以使用 ADMIN COMMAND 语句: ADMIN COMMAND 'proctrace { on | off }user username procedure procedure_name'

此外,可以通过在过程中使用 writetrace 函数,向 soltrace.out 文件发送一个字符串,从而创建一个用户定义的跟踪: WRITETRACE (string VARCHAR)

可以通过下面的命令开启或关闭输出: ADMIN COMMAND 'usertrace { on | off } user username procedure procedure _name'

注意,IBM Data Studio Developer 工具提供了一个调试器和一个 SQL 分析器组件,可以用于分析 SQL PL 过程的执行。

返回 SQL 过程的结果集

这个小节描述 DB2 和 solidDB SQL 过程在返回结果集方面的差异。

DB2 SQL 过程的返回结果集

在 SQL 过程中,游标不仅仅可以用来遍历结果集中的行。它们还可以用来将结果集返回给调用程序。

通过使用 CLI 应用程序编程接口、Java®、CLI 或 .NET CLR 语言,结果集可以由 SQL 过程(对于一个嵌套的过程调用)或使用 C 编写的客户机应用程序检索。

要从一个 SQL 过程返回一个结果集,执行以下步骤:

  1. 在 CREATE PROCEDURE 语句中指定 DYNAMIC RESULT SETS 子句。
  2. 使用 WITH RETURN 子句声明游标。
  3. 在 SQL 过程中打开游标。
  4. 使游标对客户机应用程序保持打开状态(不要关闭它)。

清单 18 展示了一个示例 SQL 过程,它仅返回一个单一的结果集。

清单 18. 返回一个单一的结果集
CREATE PROCEDURE read_emp() 
SPECIFIC read_emp 
LANGUAGE SQL 
DYNAMIC RESULT SETS 1 
Re: BEGIN 
DECLARE c_emp CURSOR WITH RETURN FOR 
    SELECT salary, bonus, comm. FROM employee WHERE job != ’PRES’; 
OPEN c_emp; 
END Re

如果在返回 SQL 过程之前使用 CLOSE 语句关闭游标,那么游标结果集就不会返回到调用者或客户机应用程序。

通过使用多个游标,可以从一个 SQL 过程中返回多个结果集。要返回多个游标,执行以下步骤:

  1. 在 CREATE PROCEDURE 语句中指定 DYNAMIC RESULT SETS 子句。
  2. 指定可能返回的结果集的最大数量。实际返回的结果集的数量一定不能超过这个数字。
  3. 对于每个将要返回的结果集,使用 WITH RETURN 子句声明游标。
  4. 打开将被返回的游标。
  5. 使游标对客户机应用程序保持打开状态(不要关闭它)。

每个将要返回的结果集都需要一个游标。结果集按照它们被打开的顺序返回给调用者。一旦创建了返回结果集的 SQL 过程,可能需要调用它并检索结果集。

solidDB 过程的返回结果集

RETURNS 变量

可以使用存储过程来返回一个结果集表,其中在单独的列中保存多行数据。这是一种用来返回数据的 IBM solidDB 属性方法,您可以使用 RETURNS 结构完成它。

在使用 RETURNS 结构时,必须为输出数据行单独声明结果集列名。可以使用任意数量的结果集列名。结果集列名在过程定义的 RETURNS 部分声明,如清单 19 所示。

清单 19. 过程定义的 RETURNS 部分
"CREATE PROCEDURE procedure_name (IN in_param1 datatype, in_param2 datatype,)   
     RETURNS (out_param3 datatype, out_param4 datatype)
BEGIN
…
END";

默认情况下,当存储过程在运行状态或被强制退出时,过程只返回一行包含值的数据。然而,也可以使用下面的命令从过程中返回结果集:RETURN ROW;

每个 RETURN ROW 调用向返回的结果集增添一个新行,该结果集中的列值为结果集列名的当前值。清单 20 创建的过程为输出行提供了两个输入参数和两个结果集列名。

清单 20. 两个输入参数和两个结果集列名
"CREATE PROCEDURE lookup (IN first_name VARCHAR, last_name VARCHAR) RETURNS
                (phone_nr NUMERIC, city VARCHAR)
BEGIN
--procedure_body
    WHILE …LOOP
          RETURN ROW;
          EXEC SQL FETCH….
    END LOOP;
END";

例如,如果输入:call lookup ('SUE','DAVIS'); ,那么结果如清单 21 所示。

清单 21. call lookup 的结果
3433555 CHICAGO
2345226 BOSTON

有关 SQL 过程的其他内容

SQL 的过程所有权、访问权和特权

DB2 和 solidDB SQL 过程都由创建者所有。他们都属于创建者模式的一部分。需要在其他模式中运行存储过程的用户需要使用以下命令获得对过程的 EXECUTE 特权:GRANT EXECUTE ON proc_name TO { USER | ROLE };

使用 TIMESTAMPDIFF

清单 22 中的过程演示了 solidDB 标量函数 TIMESTAMPDIFF,用于一条复制语句。

清单 22. 使用 TIMESTAMPDIFF
"CREATE PROCEDURE grown_up (birth_date DATE) RETURNS (description VARCHAR)
BEGIN
   DECLARE age INTEGER;
   age := {fn TIMESTAMPDIFF(SQL_TSI_YEAR, birth_date, now())};
   IF age >= 18  THEN
      description := 'ADULT';
   ELSE
       description := 'MINOR';
   END IF
END";

在清单 22 中,原生 solidDB 函数 TIMESTAMPDIFF 是使用 {fn TIMESTAMPDIFF(…) } 格式编写的。这个格式表示的是 ODBC 函数的语法。solidDB 通过 ODBC 解析器运行 SQL 语句,将 {fn <function>} 转换为原生 solidDB 格式。这通常会导致删除语法的 {fn } 部分。例如,{fn CURDATE()} 被转换为 CURDATE()。您可以在 solidDB 过程中使用 ODBC 形式的函数,或者使用原生形式的函数。

使用转义序列

solidDB 字符串使用单引号分隔。要创建实际的省略号,将两个单引号并排放在一起(‘’),从而在输出中生成一个引号。这通常也称为 转义序列。清单 23 展示了这一技巧。

清单 23. 使用转义序列
"CREATE PROCEDURE quotes
RETURNS (string_var VARCHAR(20))
BEGIN
string_var := 'IBM''s solidDB';
END";

CALL quotes;

结果为 IBM's solidDB

从 solidDB SQL 编辑器中执行 solidDB 过程

从 solidDB SQL 编辑器(solsql)中执行 solidDB 脚本时,必须在执行脚本之后或在离开编辑器之前提交您的工作。SQL 编辑器的默认命令为 autocommit off

也可以通过 -a 选项启动 SQL 编辑器,以自动提交所有 SQL 语句。DB2 命令行处理器(CLP)的默认行为是 autocommit on

可以使用三种方法中的任意一种来从 solidDB SQL 编辑器执行过程。

  • 使用 solsql 命令连接到 solidDB,然后执行过程,如清单 24 所示。
    清单 24. 连接和执行
    C:\hsbmaster63>solsql "tcp 1315" dba dba
    Solid SQL Editor (teletype) v.06.00.1018
    Copyright (C) Solid Information Technology Ltd 1993-2007
    Connected to 'tcp 1315'.
    Execute SQL statements terminated by a semicolon.
    Exit by giving command: exit;
    
    call proc(?);
    Param 1:
    10;
    Command completed successfully, -1 rows affected.

    在清单 24 中,过程被编码为返回一个 OUT 参数值,使用参数标记(?)表示,并且不能接受输入值。然而,solidDB SQL 编辑器提示您为输入参数输入一个值。在清单 24 的例子中,输入了一个虚构的值 10。这里可以输入任何值。这个行为是目前已发现的一个 solsql 限制。要避免这种情况,使用 CREATE PROCEDURE 语句的 RETURNS 子句来将数据返回给调用者。

  • 从 solsql 命令行执行过程。可以使用下面的命令调用过程: solsql -e "call proc(?)" "tcp 1315" dba dba,假设在 tcp 1315 侦听 solid,并且用户名和密码都为 dba。
  • 将过程调用放到一个文件中,然后使用下面的命令引用该文件: solsql "tcp 1315" dba dba filename.sql

结束语

不管您目前学习的 SQL 过程是 DB2 还是 solidDB,您现在都拥有了学习另一种过程的好起点。阅读本系列的 比较 IBM DB2 和 IBM solidDB SQL 过程,第 1 部分:比较结构、参数、变量、赋值、过程体、游标和动态 SQL 语句 继续学习。


相关主题


评论

添加或订阅评论,请先登录注册

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=416449
ArticleTitle=比较 IBM DB2 和 IBM solidDB SQL 过程,第 2 部分: 比较 SQL 过程中的条件语句、循环语句、错误处理和返回结果集
publish-date=07242009