DB2 纯SQL存储过程与函数的一些限制

*给多个变量赋值

function只能使用

set (a,b,c)=(select a,b,c from #);

 

procedure只能使用

 

select a,b,c into a,b,c from #

 

*function无法嵌套调用带有inout或out参数的procedure，现象为：无法创建。

 今天的进展结论是：

function可以调用任何procedure，但必须声明为modifies sql data，同时必须返回table类型，不能使单值或row类型

*procedure可以嵌套调用，哪怕是带inout参数的存储过程.

*function 可以嵌套调用.

*IBM开发者网站一篇文章说：function的效率比procedure高。

  我的答案是相反。

  同样的语句，function和procedure的首次调用时间有10倍左右的差距。

 

经过测试有如下结论：

  1.如果参数完全一样，两次调用同样的function，首次调用速度很慢（2秒左右），但第二次开始就很快（0.2秒左右），但后来很快的调用应该是采用了缓存机制，而这个缓存的生存期未知。

  2.同样的函数，只要参数发生任何变化，那么首次还是会很慢。

  3.procedure很稳定，第一次和后面的调用差别不大。并且都是0.2秒级的。

 

  而且最让人纳闷的是哪怕代码仅是一些比较判断，根本不涉及数据，function的效率也不敢恭维。

  所以如果是使用频率相当高的代码，无论如何，想方设法都得改成procedure。

 

*procedure无法进行动态out参数procedure调用。

  什么意思呢，就是要调用的procedure不是硬编码的

--这是没有问题的 create procedure out_proc call in_proc; end --这也没有问题的 create procedure out_proc ... statment='call in_proc()'; prepare s1 from statment; execute s1; end --可以创建，但运行时会报错 create procedure out_proc ... declare out_param int; statment='call in_proc('||cast(out_param as char(10))||')'; prepare statment; execute statment; end

 

这些限制完全没有任何的文档可以查询到，是实际测试后得出的结论，特记于此，免得以后又走弯路

 

 

目前已经写好了调用procedure的function，性能确实要强过直接调用function。但碰到了新问题：

*调用了procedure的function不能象一般function一样在select语句中查询，比如

func1 returns table()

func2 returns table() --其中调用了procedure

 

那么

 

--直接获取func1,正常 select * from table(func1) as tmp --直接获取func2，正常 select * from table(func2) as tmp --func1放入其他select语句中，正常 select * from ( select * from table(func1) as tmp ) as t --func2放入其他select语句中，不能执行 --报错 SQL0969N 在此工作站上没有与消息文件中 SQL 错误 "-20267" 相对应的消息文本。错误是从模块 "SQLNQ7BD" 返回的，并带有原始标记 "proc2 SQL100317165046400 2"。 select * from ( select * from table(func2) as tmp ) as t

 

*procedure 默认modifies sql data

 经过试验,无法创建reads sql data类型的procedure

create procedure tmp() reads sql data language sql begin atomic end@

执行这个什么都不做的procedure,仅仅声明了reads sql data,报错:

DB21034E  该命令被当作 SQL
语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：
SQL0577N  用户定义例程 "TMP"（特定名称 ""）试图修改数据，但是该例程未定义为
MODIFIES SQL DATA。  LINE NUMBER=3.  SQLSTATE=42985

服了DB2了

找到原因了!!!!

http://tech.ddvip.com/2008-12/1230630845103709.html

说道:

如果在一个复合 SQL 过程中使用 BEGIN ATOMIC 子句，那么创建该存储过程时必须指定 MODIFIES SQL DATA

 

去掉atomic关键字后,procedure 可以声明为reads sql data,同样不能嵌套调用的func的根本原因是:

如果function是modifies sql data 的,就不能嵌套调用,

而如果function调用的procedure 是modifies sql data 的,那么该function也必须声明为modifies sql data