DB2 UDB有没有SQL块的写法？请教一下

zjsharp

DB2 UDB有没有SQL块的写法？不想写存储过程。偶试了一阵子没有找到，麻烦帮忙。
比如能实现类似功能的：
......
declare aaa int;
set aaa = 1
select 'result'  from sysibm.sysdummy1 where aaa=1;
......


或者有类似功能的其它写法？谢谢

[ 本帖最后由 zjsharp 于 2006-11-6 20:55 编辑 ]

大梦

declare可以用!
set好象是procedure里面的赋值语句呀!

zjsharp

找到一种马马虎虎的方法，凑合用用，使用dynamic compound statement....
BEGIN ATOMIC
    SQL-variable-declaration;
    condition-declaration;
    SQL-routine-statement;
END
不过这里的sql-routine-statement可以是insert，但不能是独立的select语句来返回结果集， 只好把结果集插入临时用的表算了。

还有什么好方法就请大家补充吧。

[ 本帖最后由 zjsharp 于 2006-11-6 22:38 编辑 ]

lklgdkp

SET 也可以用于SQL PL, 找了个例子：

While the SQL PL can command a variety of SQL commands, a SELECT statement will not be returned to the calling program (either CLP or an application) as a open cursor that records can be fetched from. From a user perspective, issuing a SELECT statement within a BEGIN ATOMIC block will not return a result set, while outside of the block it will. For instance, if the SQL PL in figure 15-29 was modified to place the SELECT statement outside the control block, no result set would be returned when you issued the DB2 command (Figure 15-31):

Figure 15-31 SQL PL with no output

1. 
   
2. CONNECT TO DB2CERT@
   
3. DROP TABLE TEST@
   
4. CREATE TABLE TEST (NUMBER INT)@
   
5. 
   
6. BEGIN ATOMIC
   
7.   DECLARE I INT DEFAULT 1;
   
8.   WHILE I <= 10 DO
   
9.     INSERT INTO TEST VALUES(I);
   
10.     SET I = I + 1;
    
11.   END WHILE;
    
12.   SELECT * FROM TEST;
    
13. END@
    
14. 
    
15. QUIT@
    

复制代码

Normally you would use SQL PL blocks to apply complex logic to INSERT, UPDATE, and DELETE statements. If you do need to see the results of these statements, you must issue the SELECT outside the scope of the SQL PL block. However, you can use a SELECT statement within an SQL PL block to calculate values, find values, or for any other purpose other than to display results.

[ 本帖最后由 lklgdkp 于 2006-11-7 21:48 编辑 ]