浅谈IBM DB2数据库如何迁移问题(5)
create table t1 ( col1 int)
DB20000I The SQL command completed successfully.
create table t2 ( col1 int )
DB20000I The SQL command completed successfully.
create procedure abc(in p int) begin insert into t1 values(p); end
DB20000I The SQL command completed successfully.
create trigger ins after insert on t2 referencing NEW as new for EACH ROW MODE
DB2SQL BEGIN ATOMIC values ( call_procedure('DB2INST1.ABC', char(new.col1 * 2),
'SAMPLE', 'DB2INST1', 'db2inst1') ); END
DB20000I The SQL command completed successfully.
insert into t2 values 20
DB20000I The SQL command completed successfully.
/* validate that the trigger has fired - it should update t1 */
select * from t1
COL1
-----------
40
1 record(s) selected.
下一个例子演示了在 UDF 中调用包含了两个参数的存储过程。在该例中,我们创建表( c ),带有两个输入参数的存储过程( abc )以及带有两个参数(parm1,parm2)的 UDF( udf_withcall )。当执行该 UDF 时,它将调用存储过程,然后,该存储过程会将由 UDF 传递给它的值插入表 c。对表 c 进行 select 将验证表 c 的内容以及存储过程是否执行成功。可以在“下载”小节中找到该脚本( udf_calls_proc.db2)的源代码。