DB2存储过程杂谈

DB2常用的异常处理方式分为三种：UNDO（回滚）、EXIT（退出）、CONTINUE（继续执行）
DECLARE UNDO|EXIT|CONTINUE HANDLER FOR

异常处理的对象可以为针对指定的sqlstate代码
DECLARE UNDO|EXIT|CONTINUE HANDLER FOR SQLSTATE XXXX

也可以针对某些condition,如sql异常，sql警告，以及游标末尾
DECLARE UNDO|EXIT|CONTINUE HANDLER FOR SQLEXCEPTION|SQLWARNING|NOT FOUND

异常处理的内容 ，可以是一些回滚，提交语句，也可以是日志记录等
DECLARE UNDO|EXIT|CONTINUE HANDLER FOR SQLEXCEPTION 

BEGIN

    --异常处理内容

END;

 

通常如何使用GET DIAGNOSTICS语句
解答：使用GET DIAGNOSTICS 变量 = ROW_COUNT语句可以获取最后一次执行INSERT、UPDATE或DELETE之后的记录数；DB2 SQL PL 支持GET DIAGNOSTICS语句获取SQL语句执行的信息，它可以用来返回CONDITION HANDLER的错误信息；GET DIAGNOSTICS EXCEPTION 1 变量 = MESSAGE_TEXT；但这样就不能再获取行数了。注意：GET DIAGNOSTICS语句是不支持SELECT和SELECT INTO语句的。

(1):CASE
        WHEN 条件  THEN 结果1     （条件为：true/false）
        WHEN 条件2  THEN 结果12
        WHEN 条件3  THEN 结果13
        ELSE            结果2
      END
 (2):CASE 表达式1
        WHEN 表达式2   THEN 结果1    （当表达式1==表达式2）
        WHEN 表达式3   THEN 结果13
        WHEN 表达式4   THEN 结果14
        ELSE            结果2
   END

 

LEFT(ARG,LENGTH)、RIGHT(ARG,LENGTH)
LEFT,RIGHT 分别返回ARG的最左边或最右边length个字符，ARG可以是字符或String

 

cast(N AS TYPE)将参数N转换为TYPE型。
比如
select cast(1 as float)/3 from sysibm.sysdummy1; ---将1转换为float型，然后除以3.
select cast(1 as decimal(10,2))/3 from sysibm.sysdummy1;  --将1转换为decimal(10,2)型，然后除以3.
select cast(1111 as char(4)) from sysibm.sysdummy1;  --将1111转换为字符型的'1111',要注意的是不可以转换为varchar,这是db2不允许的。

动态sql
-----
PREPARE  FROM

261.----定义     
262.    DECLARE CC VARCHAR(4000); 
263.    DECLARE SQLSTR VARCHAR(4000); 
264.    DECLARE st STATEMENT;     
265.    DECLARE CUR CURSOR WITH RETURN TO CLIENT FOR CC; 
266. 
267.----执行动态SQL不返回 
268.    PREPARE st FROM SQLSTR; 
269.    EXECUTE st; 

--------
在动态 sql中调用存储过程，如果存储过程中带有参数，必须要用到into和using 参数
 对应的每一个in和inout的参数需要一个using的宿主变量，对于每个out和inout的参数需要一个into的宿主变量，
 例如：
 proc1(in param1, out param2)
 set call_statement = 'call proc1(?,?)'
 prepare s1 from call_statement
 execute s1 into myvar2 using myvar1;
 

proc2(in param1, inout param2)
 set call_statement = 'call proc2(?,?)'
 prepare s1 from call_statement
 execute s1 into myvar2 using myvar1, myvar2;

------

.创建临时表

DECLARE GLOBAL TEMPORARY TABLE 语句来定义临时表。DB2的临时表是基于会话的，且在会话之间是隔离的。当会话结束时，临时表的数据被删除，临时表被隐式卸下。对临时表的定义不会在SYSCAT.TABLES中出现.
 
eg: DECLARE GLOBAL TEMPORARY TABLE SESSION.Temp_K_Cig (Cig varchar(20)) WITH REPLACE NOT LOGGED on commit preserve rows;
 
其中not logged 指不记录事务日志,with replace 指在程序结束后自动删除该临时表

1). DB2的临时表需要用命令Declare Temporary Table来创建， 并且需要创建在用户临时表空间上;
2). DB2在数据库创建时， 缺省并不创建用户临时表空间， 如果需要使用临时表， 则需要用户在创建临时表之前创建用户临时表空间;
3). 临时表的模式为SESSION，SESSION即基于会话的，且在会话之间是隔离的。当会话结束时，临时表的数据被删除，临时表被隐式卸下。对临时表的定义不会在SYSCAT.TABLES中出现 .;
4). 缺省情况下， 在Commit命令执行时， DB2临时表中的所有记录将被删除; 这可以通过创建临时表时指定不同的参数来控制;
5). 运行ROLLBACK命令时， 用户临时表将被删除;
还提供on commit preserve rows, 和on commit delete rows功能。
但是只提供global功能，意即表本身不会自动删除。 只是提供是否保留数据的功能。这样，preserve rows相当于会话级，而on commit delete rows则相当于事务级的临时表了。用处还是蛮

 
大的

为什么需要定义临时表：只有当我们在一个工作单元中的多条SQL语句中使用同一临时集合时，我们才需要定义临时表。临时表（TEMPORARY TABLE）通常应用在需要定义临时集合的场合。但是，在大部分需要临时集合的时候，我们根本就不需要定义临时表。当我们在一条SQL语句中只使用一次临时集合时，我们可以使用嵌套表表达式来定义临时集合；当我们在一条SQL语句中需要多次使用同一临时集合时，我们可以使用公共表表达式

存储过程：没有返回值；对于sqlserver、db2，可以进行查询、插入、更新、删除操作
函数：有返回值；对于sqlserver、db2，不可以进行插入、更新、删除操作

----DB2 MERGE INTO语句的使用
 
DB2中的Merge语句可以将一个表中的数据合并到另一个表中，在合并的同时可以根据条件进行插入、删除、更新等操作，功能非常强大。
 
语法如下：
MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
     UPDATE table_name SET col1 = col_val1,col2 = col2_val
WHEN NOT MATCHED THEN
     INSERT (column_list) VALUES (column_values)
 
关键字、参数
into子句 
在into子句中指定所要修改或者插入数据的目标表
using子句 
在using子句中指定用来修改或者插入的数据源。数据源可以是表、视图或者一个子查询语句。
on子句 
在on子句中指定执行插入或者修改的满足条件。
when matched | not matched 
用该子句通知数据库如何对满足或不满足条件的结果做出相应的操作。可以使用以下的两类子句。
  
merge_update子句 
merge_update子句执行对目标表中的字段值修改。当在符合on子句条件的情况下执行。如果修改子句执行，则目标表上的修改触发器将被触发。
------------

 

db2的命令行执行，登陆系统
 su - db2odb  
db2 connect to tsodb
 db2 "select 1 from syscat.columns where colname='COL1' and tabname='TAB1' and tabschema='SCH1'"