|
简述
工作过程：利用档案类型自定义工具对STFILETYPE表进行新增、删除记录后，调用触发器更新视图varchqueryview，以实现跨档案类型进行文件查询。
用到触发器2个，存储过程2个。
相关表一个：STFILETYPE，相关视图一个：varchqueryview
注意事项：
1、 db2版本需要8.2以上才能（不需c编译器）执行存储过程；
2、 存放sql的字符串长度暂定为8000；
3、 错误信息存入表proclog，需要时可新增该表（没有该表不会出现异常，只是不记录错误信息），字段名称随意，长度可以加大（因为在其中存放sql语句）。
4、 测试：1、先删除视图再操作：不影响视图的生成。2、新的视图sql如果有错，则不生成，保留原来的视图。
记录新增触发器TI_STFILETYPE
-- DROP TRIGGER CBARCH.TI_STFILETYPE;
CREATE TRIGGER CBARCH.TI_STFILETYPE
AFTER
INSERT
ON CBARCH.STFILETYPE
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
declare stmt varchar(8000);
set stmt = ' ';
for cur as
select id,name,tafilename
from stfiletype
do
if stmt=''then
set stmt=stmt||
' select id,systemcode,filearchno,filetitle,expired,archyear,secret,fileflag,areaid,'''
||rtrim(char(cur.id))|| ''' as filetypeid_s,'''||cur.name||
''' as typename from '||cur.tafilename;
else
set stmt=stmt||
' union select id,systemcode,filearchno,filetitle,expired,archyear,secret,fileflag,areaid,'''
||rtrim(char(cur.id))|| ''' as filetypeid_s,'''||cur.name||
''' as typename from '||cur.tafilename;
end if;
end for;
CALL P_CREATEQUERYVIEW(stmt);
END;
记录删除触发器TD_STFILETYPE
-- DROP TRIGGER CBARCH.TD_STFILETYPE;
CREATE TRIGGER CBARCH.TD_STFILETYPE
AFTER
DELETE
ON CBARCH.STFILETYPE
--REFERENCING OLD AS OROW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
declare stmt varchar(8000);
set stmt = ' ';
for cur as select id,name,tafilename from stfiletype do
if stmt=''then
set stmt=stmt||' select id,systemcode,filearchno,filetitle,expired,archyear,secret,fileflag,areaid,''' ||rtrim(char(cur.id))|| ''' as filetypeid_s,'''||cur.name||''' as typename from '||cur.tafilename;
else
set stmt=stmt||' union select id,systemcode,filearchno,filetitle,expired,archyear,secret,fileflag,areaid,''' ||rtrim(char(cur.id))|| ''' as filetypeid_s,'''||cur.name||''' as typename from '||cur.tafilename;
end if;
end for;
CALL P_CREATEQUERYVIEW(stmt);
END;
主存储过程P_CREATEQUERYVIEW
-- drop PROCEDURE CBARCH.P_CREATEQUERYVIEW;
CREATE PROCEDURE CBARCH.P_CREATEQUERYVIEW ( IN stmt VARCHAR(8000))
SPECIFIC CBARCH.P_CREATEQUERYVIEW
LANGUAGE SQL
-----------------------------------------------------------------------
-- SQL 存储过程
------------------------------------------------------------------------
P1: BEGIN
declare o_err_no int;
DECLARE o_err_msg CHAR(5) ;
declare errmsg varchar(200);
declare createview varchar(200);
-- 先做创建视图测试
call CBARCH.P_EXECSQL('drop view view_temp', o_err_no, o_err_msg);
call CBARCH.P_EXECSQL('create view view_temp as '|| stmt, o_err_no, o_err_msg);
set createview = 'create view varchqueryview as ';
if(o_err_no is not null and o_err_no=0 ) then
-- 成功，则创建视图
call CBARCH.P_EXECSQL('drop view varchqueryview', o_err_no, o_err_msg);
call CBARCH.P_EXECSQL(createview|| stmt, o_err_no, o_err_msg);
call CBARCH.P_EXECSQL('drop view view_temp', o_err_no, o_err_msg);
else
set errmsg = 'insert into proclog values('''|| char(current date) || ' '||char(current time) ||' '||'create view error: SQLCODE='|| char(o_err_no) || ',SQLSTATE=' ||o_err_msg||', sql='|| createview|| stmt ||''')';
call CBARCH.P_EXECSQL(errmsg, o_err_no, o_err_msg);
end if;
return o_err_no;
END P1;
SQL语句执行子过程P_EXECSQL
-- drop PROCEDURE CBARCH.P_EXECSQL;
CREATE PROCEDURE CBARCH.P_EXECSQL ( IN stmt varchar(8000), OUT o_err_no int, OUT o_err_msg CHAR(5) )
-- OUT o_err_msg varchar(1024)
SPECIFIC CBARCH.P_EXECSQL
LANGUAGE SQL
-----------------------------------------------------------------------
-- SQL 存储过程
------------------------------------------------------------------------
P1: BEGIN
--临时变量出错变量
declare SQLCODE integer default 0;
DECLARE SQLSTATE CHAR(5) ;
declare at_end integer default 0;
--声明变量
DECLARE st STATEMENT;
--声明出错处理
DECLARE EXIT HANDLER FOR SQLEXCEPTION
begin
-- rollback;
set o_err_no=SQLCODE;--set o_err_no=1;
set o_err_msg=SQLSTATE;
--set o_err_msg='处理'||state||'出错 '||'错误代码SQLCODE:'||CHAR(r_code);
end;
--开始拉
set o_err_no=0;
PREPARE st FROM stmt;
EXECUTE st;
END P1;
原文：http://sunshinehuash.blog.ccidnet.com/blog-htm-itemid-180838-do-showone-type-blog-uid-60683.html
|
