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)的源代碼。
create table c ( a int check (a <> 8), a1 int )
DB20000I The SQL command completed successfully.
create procedure abc(in p int, in p2 int) begin insert into c values(p,p2); end
DB20000I The SQL command completed successfully.
create function udf_withcall (parm1 int, parm2 int)
returns int
Language SQL
not deterministic
external action
return call_procedure('DB2INST1.ABC', char(parm1) ||','|| char(parm2), 'SAMPLE', 'DB2INST1', 'db2inst1'))
DB20000I The SQL command completed successfully.
select udf_withcall(30,40) from sysibm.sysdummy1
1
-----------
0
1 record(s) selected.
/* verify that the UDF has called the procedure and updated the table */
select * from c
A A1
----------- -----------
10 20
30 40
2 record(s) selected.
