一个完整的存储过程示例如下:
CREATE PROCEDURE read_address(lastname CHAR(15))
RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(5);
DEFINE p_lname, p_fname CHAR(15);
DEFINE p_addr CHAR(20);
DEFINE p_zip CHAR(5);
SELECT fname, address1, zipcode INTO p_fname, p_addr, p_zip
FROM customer
WHERE lname=lastname;
RETURN p_fname, lastname, paddr, p_zip;
END PPROCEDURE
DOCUMENT “this procedure takes the last name of a customer as its
only argument, it returns the name and the address of the customer”
WITH LISTING IN “/tmp/test /listfile”
Foreach 的使用示例如下:
CREATE PROCEDURE foreach_ex()
DEFINE i, j INT;
FOREACH SELECT c1 INTO i FROM tab1 ORDER BY 1
INSERT INTO tab1 VALUES(i)
END FOREACH
FOREACH cur1 FOR SELECT c2, c3 INTO i, j FROM tab1
IF j >100 THEN
DELETE FROM tab1 WHERE CURRENT OF cur1;
CONTINUE FOREACH
END IF
UPDATE tab1 SET c2=c2+10 WHERE CURRENT OF cur1
END FOREACH
FOREACH EXECUTE PROCEDURE bar(10, 20) INTO i
INSERT INTO tab2 VALUE(i);
END FOREACH
END PROCEDURE
函数 function 定义,使用 trace 功能调试存储过程:
CREATE FUNCTION test_trace () RETURNING INT
DEFINE count, a INT;
LET count = 0;
SET DEBUG FILE TO "/tmp/trace_out";
TRACE ON;
LET count = (SELECT count(*) FROM tab1);
RETURN count;
END FUNCTION;
通过 C 预约自定义 C-UDR,这里假设需要通过 C 程序自定函数查找某个字符出现在字符串中的位置信息。
第一步:vi add_three.c
使用 vi 编辑 add_three.c,内容如下所示。
#include <mi.h>
mi_integer f_add(mi_integer i_num1,mi_integer i_num2)
{
return i_num1 + i_num2;
}
第二步:编译
编译和链接 f_add.c,生成 f_add.so。
/usr/bin/gcc -I$INFORMIXDIR/incl -I$INFORMIXDIR/incl/public f_add.c -fPIC -shared -o f_add.so
第三步:注册函数
create function f_add (i_num1 integer,i_num2 integer)
returns integer
external name "/opt/IDS115/myudr/f_add.so(f_add)"
language c;
第四步:可以通过 SQL 使用刚自定义的函数 f_add
execute function f_add (1,2);
select f_add(tabid,tabid) from systables where tabid=1;