简介

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

第 1 部分

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

第 2 部分

• SQL 过程的条件语句
• SQL 过程的循环语句
• SQL 过程的错误处理
• SQL 过程的返回结果集
• 有关 SQL 过程的其他内容

SQL 过程中的结构

本节介绍 DB2 和 solidDB SQL 过程在结构方面的差异。

DB2 SQL 过程的结构

DB2 SQL 过程的核心是一个复合语句（compound statement）。复合语句也称为复合块（compound block），所绑定的关键字为 BEGIN 和 END。清单 1 解释了 DB2 SQL 过程的结构化格式。

				
CREATE PROCEDURE procedure_name (parameters)
LANGUAGE SQL
BEGIN 
   Local variable declarations
   Condition declarations                       
   Cursor declarations                            
   Condition handler declarations         
   Procedural body
        Assignment, 
        flow of control,
        looping
        SQL statements
        cursors
        BEGIN 
        …                     
        END
   Other compound statements either nested or serially placed
END 
    

SQL 过程可以包含一个或多个复合块。这些块可以被嵌套或顺序排列在 SQL 过程中。对于每一个块，对变量、条件和处理程序声明都有一个指定的顺序。这些声明必须位于 SQL 过程逻辑说明的前面。然而，游标可以在 SQL 过程体中的任何位置声明。

有两种类型的复合语句（块）：原子性（atomic）和非原子性。

原子性复合语句可以看作是过程中的一个单个工作单元。如果该块中的任何语句失败，那么执行到该失败点的任何语句都将被认为是失败的，并且所有语句都将被执行回滚。换句话说，块中的语句要么全部成功，要不就全部失败。COMMIT、SAVEPOINT 和 ROLLBACK 语句是不允许的。这些语句只在非原子性 块中受支持。

非原子性语句块是默认的类型。即使块内的某个语句失败，其他语句可能会成功并被提交（或回滚），只要工作被显式提交（在过程内或过程所属的工作单元内）。

清单 2 展示了 ATOMIC 和 NOT ATOMIC 块的语法。

				
BEGIN ATOMIC
    … procedure code…
END 

BEGIN NOT ATOMIC
    …procedure code…
END 
    

solidDB 过程的结构

和 DB2 过程一样，solidDB 过程也包含若干部分。包括参数部分、用于本地变量的声明部分和过程体部分。清单 3 展示了 solidDB 过程的格式。

				
"CREATE PROCEDURE procedure_name (parameter_section)
BEGIN
  declare_section_local_variables
  procedure_body
    assignment
    flow of control
    looping
    cursor processing
    error handling statements
END";
    

您可以找出一些不同的地方。和 DB2 不同，solidDB 过程并没有包含多个复合语句块。相反，只在过程的开始和结束处包含了一对 BEGIN 和 END 关键字。

solidDB 过程需要将完整的定义部分放到一对双引号之中。

solidDB 过程中的事务可以在过程内部或过程外部提交或回滚。当 solidDB 过程返回到具有 autocommit on 的调用应用程序时（JDBC 或 ODBC），除非指定了回滚，否则将隐式地提交过程。

在过程内部，提交或回滚语句（以及所有其他 SQL 语句）的前面都要使用关键字 EXEC SQL。这与 DB2 SQL 过程不同，后者不需要在 SQL 语句前面使用 EXEC SQL 关键字。清单 4 展示了一个 solidDB 语法的示例。

				
EXEC SQL COMMIT WORK;
EXEC SQL ROLLBACK WORK;
    

和 DB2 相同的是，可以在过程中的任意位置声明 游标，而本地变量必须声明部分中声明，声明部分在 BEGIN 之后过程逻辑之前。

回页首

参数和调用 SQL 过程

本节描述 DB2 和 solidDB SQL 过程在参数方面的不同之处。

DB2 过程的参数

参数用于将标量值传递给过程或从过程中传出。DB2 还可以以数组的方式将多组值作为单个参数传递。对于标量值，有三种类型的参数：IN、INOUT 和 OUT。清单 5 展示了一个使用所有三种类型的参数创建过程的示例。参数 p1、p2 和 p3 都各自被声明为 INTEGER(INT)。

				
CREATE PROCEDURE proc_name (IN p1 INT, INOUT p2 INT, OUT p3 INT) 
LANGUAGE SQL
BEGIN 
 ….
END@ 
    

除了 OUT 和 INOUT 参数外，DB2 还向调用程序返回结果集（包括多个行和多个列）。然而，结果集并不是使用 CREATE PROCEDURE 语句中的参数返回的。

solidDB 过程的参数

和 DB2 过程一样，solidDB 过程也有三种类型的参数：IN、OUT 和 INOUT。如果没有指定参数类型，那么默认情况下使用 IN 类型。清单 6 展示了使用 solidDB 语法的示例。

				
"CREATE PROCEDURE proc_name(p1 INTEGER = 8, OUT p3 INTEGER, INOUT p2 INTEGER)
BEGIN
…
END"
    

在过程内，输入和输出参数被作为逻辑变量处理。在参数列表中可以为 solidDB 参数指定一个默认值，如清单 6 所示。DB2 不支持在参数列表中初始化变量。

在调用为参数定义了默认值的过程时，不需要指定参数。比如，如果 proc_name 中的所有参数都指定了默认值，那么就可以使用 call proc_name; 调用命令。

您可以在调用过程时通过使用等号（=）为参数赋值，如清单 7 所示。

				
call proc_name (p1 = 8,  p2,  p3);
    

该命令将参数 p1 的值指定为 8，并为参数 p2 和 p3 指定默认值。如果参数名未在调用语句中使用，solidDB 将假设参数的顺序与 CREATE PROCEDURE 语句中的参数顺序一样。

在 solidDB 中返回值的另一种方法是通过 CREATE PROCEDURE 语句的 RETURNS 子句。RETURNS 子句一般情况下会返回一个结果集表，或者仅返回输出值。这种方法不同于 DB2 中返回结果集所使用的方法，本系列 第 2 部分 将对此加以描述。

回页首

SQL 过程的变量和赋值

本节描述 DB2 和 solidDB SQL 过程在变量和赋值方面的区别。

DB2 过程的变量和赋值

SQL 语句用于声明变量并为变量赋值。下面是一些与变量有关的语句类型：

• DECLARE <variable_name datatype>
• DECLARE <condition>
• DECLARE <condition handler>
• DECLARE CURSOR <cursor_name> FOR <SQL statement>
• SET (assignment-statement)

DB2 过程中的本地变量使用 DECLARE 语句定义。此外，通过使用 DEFAULT 关键字和 DECLARE 语句，变量可以被初始化为默认值。DEFAULT 关键字在 solidDB 中不受 DECLARE 语句的支持。

通过 SET 语句执行赋值。

solidDB 过程中的变量和赋值

在 solidDB 中声明本地变量和赋值的语法与 DB2 相似：DECLARE <variable_name datatype> 。清单 8 展示了一个例子。

				
"CREATE PROCEDURE …. (parameter list)
BEGIN
   DECLARE i INTEGER;
   DECLARE dat DATE;
END";
    

所有变量默认情况下被初始化为 NULL。要在 solidDB 中为变量赋值，可以使用 SET variable_name = expression;，或者可以使用 variable_name := expression;。清单 9 展示了一个例子。

				
SET i = i + 20;
i := 100;
    

表 1 展示了 DB2 中与 solidDB 对应的各种赋值方法。

回页首

SQL 过程的过程体

本节介绍 DB2 和 solidDB SQL 过程体的结构差异。

DB2 SQL 过程体

SQL 过程支持以下类型的语句：

• 条件语句
• 循环语句
• 控制转移语句
• 错误管理语句
• 结果集操作语句

下面概述了程序体内支持的 SQL PL 语句：

条件语句：

• CASE（有 2 中形式）
• IF

循环语句：

• FOR
• LOOP
• REPEAT
• WHILE

控制转移语句：

• CALL
• GOTO
• ITERATE
• LEAVE
• RETURN

错误管理语句：

• SIGNAL
• RESIGNAL

结果集操作语句：

• ASSOCIATE LOCATOR(S)
• ALLOCATE CURSOR

DB2 过程也支持在过程体内使用注释，注释使用两个短横线（--）标注。

solidDB 过程体

任何有效 SQL 语句，比如 CREATE TEMPORARY TABLE，都可用于 solidDB 存储过程内部，包括 DDL。CASE 语句是一个例外，因为它在过程中不受支持，但是在其他 solidDB 应用程序界面中受支持。

solidDB 过程使用的 SQL 结构类似于 DB2 过程中的 SQL 结构，包括使用两个短横线添加注释。表 2 展示了 DB2 SQL PL 语句与 solidDB 过程语句之间的映射。

在 solidDB 过程中有两种方法可以执行 SQL：

• EXECDIRECT 语法
• 游标语法

如果没有返回任何行，并且不需要使用变量作为参数，那么应当选用 EXECDIRECT 语法。例如，以下语句插入了一行数据：EXEC SQL EXECDIRECT INSERT INTO table1 (id, name) VALUES (1, 'Smith');

EXECDIRECT 语句还可以结合用于游标名。该语句使您能够准备并执行语句，而不需要使用单独的 PREPARE 语句。清单 10 展示了一个例子。

				
EXEC SQL c1 USING (host_x) INTO (host_y) EXECDIRECT 
   SELECT y from foo where x = ?;
EXEC SQL FETCH c1;
EXEC SQL CLOSE c1;
EXEC SQL DROP c1;
    

在清单 10 中：

• c1 是游标名
  
  
• host_x 是一个变量，其值将代替 ?
  
  
• host_y 是一个变量，列 y 的值将存储到这个变量中

同时注意，尽管不需要您准备游标，但您必须关闭和销毁它。

游标语法将 SQL 看作包含多个行、一个 PREPARE 语句和一个 EXECUTE 语句的结果。在 solidDB 中，在以下情况下使用游标：

• 处理返回多个行（SELECT）
• 需要使用作为参数提供的不同变量值，不断重复单个语句。其中包括 SELECT、UPDATE、INSERT 和 DELETE 语句。

回页首

SQL 过程的游标

本节描述 DB2 和 solidDB SQL 过程在使用游标方面的不同之处。

在 DB2 SQL 过程中使用游标

在一个 DB2 过程中，游标用于定义一个结果集并逐行执行逻辑。指针每次只能引用一个行，但是在需要时可以指向结果集中的其他行。要在 SQL 过程中使用游标，需完成下面的步骤：

1. 声明一个游标，定义一个结果集。比如： DECLARE CURSOR cursor_name FOR < sql statement >;
   
   
2. 打开游标来建立结果集。比如：OPEN cursor_name;
   
   
3. 根据需要从游标中取出数据并放入到本地变量中，每次取出一个行。例如：FETCH FROM cursor_name INTO variable ;
   
   
4. 完成后关闭游标。例如：CLOSE cursor_name ;

在 solidDB SQL 过程中使用游标

要使用游标返回多个行，需要执行以下步骤：

1. 准备游标（定义）
2. 执行游标（执行语句）
3. 为选择过程调用获得游标（逐行取回结果）
4. 使用完后关闭游标（仍然支持它重新执行）
5. 从内存中销毁游标（删除它）

现在详细解释这些步骤。

1. 使用 EXEC SQL PREPARE cursor_name SQL_statement; 准备游标

   通过准备游标，分配内存空间以容纳语句的一行结果集，语句被解析并优化，如清单 11 所示。

   
   
   清单 11. 准备游标的示例
   

   EXEC SQL PREPARE sel_tables SELECT table_name FROM sys_tables WHERE table_name LIKE 'SYS%';

   
   

   这个语句准备名为 sel_tables 的游标，但是它并没有执行它所包含的语句。

   
   
   
2. 使用 EXEC SQL EXECUTE cursor_name USING (var1 [var2…]), [ INTO ( var1 [, var2...] ) ]; 执行游标

   成功准备好一条语句后，就可以执行该语句。执行将可能的输入和输出变量绑定到该语句，然后返回实际的语句。

   可选的 INTO 部分将语句的结果数据绑定到变量。INTO 关键字后的圆括号中列出的变量在运行 SELECT 或 CALL 语句时使用。SELECT 或 CALL 语句产生的结果列在执行语句时被绑定到这些变量。可选的 USING 子句将数据绑定到 SQL 语句，比如 WHERE 子句中的语句。清单 12 展示了一个执行语句。

   
   
   清单 12. 包括执行语句的示例代码
   

   EXEC SQL PREPARE sel_tables SELECT table_name FROM sys_tables WHERE table_name LIKE 'SYS%'; EXEC SQL EXECUTE sel_tables INTO (tab);

   
   

   语句现在被执行，产生的表名在后续的 Fetch 语句中被返回给变量 tab。

   
   
   
3. 使用 EXEC SQL FETCH cursor_name; 获得游标

   当 SELECT 或 CALL 语句被准备并执行时，它已经准备好获取。其他语句，比如 UPDATE、INSERT 和 DELETE，则不需要获取，因为不会产生结果集。

   在执行语句时，示例命令从游标中获取单个行，放到与 INTO 关键字绑定在一起的变量中。

   清单 13 展示了完整的示例代码。

   
   
   清单 13. 包含获取语句的示例代码
   

   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;

   
   

   运行该示例后，变量 tab 包含找到的第一个满足 WHERE 子句的表的名称。获取游标 sel_tables 的后续调用获得后面的行。

   要获取所有表名，使用了一个循环结构，如清单 14 所示。

   
   
   清单 14. 循环结构示例
   

   WHILE expression LOOP EXEC SQL FETCH sel_tables; END LOOP

   
   
   
   
4. 使用 EXEC SQL CLOSE cursor_name; 关闭游标

   关闭游标并不会从内存中删除实际的游标定义。可以在需要时再次运行。

   
   
   
5. 使用 EXEC SQL DROP cursor_name; 销毁游标

   可以从内存中销毁游标，这将释放所有资源。

表 3 比较了在获取行时的游标处理步骤。

表 4 展示了 DB2 和 solidDB 如何使用游标获取行。

CREATE PROCEDURE sum_salaries (OUT sum 
      INTEGER)
 LANGUAGE SQL
 BEGIN 
    DECLARE p_sum INTEGER; 
    DECLARE p_sal INTEGER; 
    DECLARE c CURSOR FOR SELECT SALARY 
            FROM EMPLOYEE; 
    DECLARE SQLSTATE CHAR(5) DEFAULT 
            ’00000’; 
    SET p_sum = 0; 
    OPEN c; 
    FETCH FROM c INTO p_sal; 
    WHILE(SQLSTATE = ’00000’) 
    DO 
       SET p_sum = p_sum + p_sal; 
       FETCH FROM c INTO p_sal; 
    END WHILE; 
   CLOSE c; 
   SET sum = p_sum; 
END@
    

“CREATE PROCEDURE sum_salaries (OUT sum
           INTEGER)
BEGIN
    DECLARE p_sum INTEGER;
    DECLARE p_sal INTEGER;
    EXEC SQL PREPARE c  
        SELECT SALARY FROM EMPLOYEE;
    p_sum := 0;
    EXEC SQL EXECUTE c INTO (p_sal);
    EXEC SQL FETCH c;
    WHILE (SQLSUCCESS) LOOP
          p_sum := p_sum + p_sal;
          EXEC SQL FETCH c ;
     END LOOP;
     EXEC SQL CLOSE c;
     EXEC SQL DROP c;
     sum := p_sum;
 END”;          
    

为了实现动态的游标，solidDB 在执行时使用参数标记来将值绑定到实际参数值。问号（？）被用做参数标记。清单 15 展示了一个例子。

				
EXEC SQL PREPARE sel_tabs
     SELECT table_name FROM sys_tables
        WHERE table_name LIKE ? AND table_schema LIKE ?;
    

执行语句使用 USING 关键字将变量绑定到参数标记，例如 EXEC SQL EXECUTE sel_tabs USING ( var1, var2 ) INTO (tabs);

通过这种方法，单个游标可以被多次使用，而不需要重新准备游标。由于准备游标涉及解析和优化语句，因此通过使用可重用游标显著提升了性能。

只有 USING 列表接受变量；因此不能直接传递数据。例如，如果需要对表执行一个插入操作，其中表的某个列值应当始终为 status = 'NEW'，那么该语句将出现错误： EXEC SQL EXECUTE ins_tab USING (nr, desc, dat, 'NEW');

正确的做法是在准备部分定义一个常量值，如清单 16 所示。

				
EXEC SQL PREPARE ins_tab 
	INSERT INTO my_tab (id, descript, in_date, status) 
             VALUES (?,?,?,'NEW');
EXEC SQL EXECUTE ins_tab USING (nr, desc, dat);
    

如前所述，在 solidDB 过程中，只要 SQL 语句（比如 INSERT、UPDATE 或 DELETE）包含参数形式的变量，都应该使用游标处理语法。相比之下，DB2 中的变量不需要进行游标处理就可以作为参数使用。

表 5 展示了一个 DB2 过程示例，该过程被转换为一个使用插入和游标处理的 solidDB 过程。

CREATE PROCEDURE p2
LANGUAGE SQL
BEGIN
   DECLARE id INT;
   CREATE TABLE table1 (id_col INT);
   INSERT INTO table1 (id_col) VALUES(1);
   SET id = 2;
   INSERT INTO table1 (id_col) VALUES(id);
   WHILE id <= 10 DO
         INSERT INTO table1(id_col)
              VALUES(id);
         SET id = id + 1;
    END WHILE;
 END@    
    

"CREATE PROCEDURE p2
  BEGIN
     DECLARE id INT;
     EXEC SQL EXECDIRECT create table
             table1(id_col INT);
     EXEC SQL EXECDIRECT insert into table1
           (id_col) values (1);
     EXEC SQL PREPARE cursor1
           INSERT INTO table1 (id_col)
                  values (?);
      id := 2;
      WHILE id <= 10 LOOP
         EXEC SQL EXECUTE cursor1 USING
                  (id);
         id := id + 1;
      END LOOP;
      EXEC SQL CLOSE cursor1;
      EXEC SQL DROP cursor1;
END";

清单 17 展示了一个 solidDB 过程，它对 SELECT、UPDATE 或 INSERT 使用游标处理，并且游标指向 DELETE>。该过程将新数据行与现有数据行合并在一起。新的数据和现有数据根据 row_type 和 row_id 的键值进行匹配。当实现一个匹配后，将使用新的价格值更新现有价格值。接下来，从新的数据中删除匹配的行，这样只保留未匹配的行。未匹配的新行被插入到现有表中。

				
"CREATE PROCEDURE merge_rows RETURNS (new_rows INT, updated_rows INT)
BEGIN
	DECLARE v_row_type int;
	DECLARE v_row_id int;	
	DECLARE v_price float;

	new_rows := 0;
	updated_rows := 0;

	EXEC SQL PREPARE select_cursor
		SELECT n.row_type , n.row_id , n.price 
		    FROM new_rows n, old_rows o
		       WHERE n.row_type = o.row_type AND n.row_id = o.row_id;		
	EXEC SQL PREPARE update_cursor UPDATE old_rows SET price = ? WHERE row_type = ?
                            AND row_id = ?;		
               EXEC SQL EXECUTE select_cursor INTO ( v_row_type, v_row_id, v_price);	
               EXEC SQL FETCH select_cursor;	
               WHILE SQLSUCCESS LOOP
		EXEC SQL EXECUTE update_cursor USING ( v_price ,v_row_type, v_row_id);
		EXEC SQL EXECDIRECT DELETE FROM new_rows
			WHERE CURRENT OF select_cursor;		
               updated_rows := updated_rows + 1;
		EXEC SQL FETCH select_cursor;
	END LOOP;
       EXEC SQL CLOSE select_cursor;
	EXEC SQL DROP select_cursor;
	                      …..
	EXEC SQL PREPARE insert_cursor INSERT INTO old_rows (row_type , row_id , price)
		    (SELECT * FROM new_rows);
       EXEC SQL EXECUTE insert_cursor;
	new_rows := SQLROWCOUNT;
	EXEC SQL CLOSE insert_cursor;
	                     ….
	EXEC SQL EXECDIRECT DROP TABLE new_rows;
	EXEC SQL COMMIT WORK;
END";
            

回页首

动态 SQL 过程

目前为止，本文已经比较了使用静态 SQL 编写的 DB2 SQL 过程和 solidDB 过程。如果使用静态 SQL 编写 DB2 过程，那么在运行过程之前要先准好好 SQL。准备好的 SQL 被作为编译 SQL 存储在数据库的包对象中。编译后的代码在运行时调用。

DB2 SQL 过程也可以使用动态 SQL 编写。当使用动态 SQL 时，将在调用过程中的语句时准备 SQL。表 6 比较了使用动态 SQL 编写的 solidDB 过程和 DB2 过程：

CREATE PROCEDURE create_table 
        (IN new_name VARCHAR(10))
LANGUAGE SQL
  BEGIN
    DECLARE stmt VARCHAR(1000);    
    SET stmt = '';    
    SET stmt = 'CREATE TABLE '||new_name||
     '( empno CHAR(6) NOT NULL, '||
     'firstnme VARCHAR(12) NOT NULL, '||
     'midinit CHAR(1) NOT NULL, '||
     'lastname VARCHAR(15) NOT NULL )';    
    EXECUTE IMMEDIATE stmt;    
END@
    

“CREATE PROCEDURE create_table (IN new_name
          VARCHAR(10))
BEGIN
    DECLARE stmt VARCHAR(1000);
    SET stmt = ‘’;
    SET stmt = ‘CREATE TABLE ’ + new_name + 
       ‘(empno CHAR(6) NOT NULL,’ +
        ‘firstnme VARCHAR(12) NOT NULL,’ +
        ‘midinit CHAR(1) NOT NULL,’ +
         ‘ lastname VARCHAR(15) NOT NULL)’;
    EXEC SQL EXECDIRECT stmt;    
END”;
COMMIT WORK;
    

注意：solidDB 支持在 SELECT 列表中使用的连接操作符 ( || )。例如，SELECT ‘a' || ‘b', col1 FROM…

DB2 的 EXECUTE IMMEDIATE 语句在运行时准备并执行 SQL，并且它等效于 solidDB 的 EXECDIRECT 语句。DB2 也支持 PREPARE 和 EXECUTE 语句。通过使用独立的 PREPARE 和 EXECUTE 语句，可以只准备一次 SQL 语句，然后多次执行它。这消除了重复准备相同语句的开销。由于 solidDB 过程并不支持静态 SQL，因此 DB2 动态 SQL 过程与 solidDB 过程更加类似。

回页首

结束语

不管您目前学习的 SQL 过程是 DB2 还是 solidDB，您现在都拥有了学习另一种过程的好起点。阅读本系列的 第 2 部分 进一步学习 SQL 过程。

参考资料

学习

• 使用 RSS 提要 在发表本系列未来的文章时获得通知。（了解更多有关 developerWorks 内容的 RSS 提要 的信息）。
  
  
• developerWorks Information Management 专区：在这里可以学到更多关于 Information Management 的知识。还可以找到技术文档、how-to 文章、培训、下载、产品信息等。
  
  
• 随时关注 developerWorks 技术活动和网络广播。
  
  

获得产品和技术

• 使用可直接从 developerWorks 下载的 IBM 产品评估试用软件 构建您的下一个开发项目。
  
  

讨论

• 参与 developerWorks blogs 并加入 developerWorks 社区。
  
  

关于作者

Nora Sokolof 是位于北美的 IBM Software Group 的顾问技术销售专家。她是 Data Servers and Application Development 团队的成员。Nora 的专长是 Information Management 软件产品。她拥有 Pace 大学软件工程专业的理科硕士学位，并且在 IBM 从事专业软件开发已经超过 22 年。她曾经担任过 DB2、Informix、Oracle 和 PeopleSoft 开发数据库管理员等职位。

为本文评分

评论

回页首

内容

• 简介
• SQL 过程中的结构
• 参数和调用 SQL 过程
• SQL 过程的变量和赋值
• SQL 过程的过程体
• SQL 过程的游标
• 动态 SQL 过程
• 结束语
• 参考资料
• 关于作者
• 建议