CREATE PROCEDURE PRO_TREND_EXTRACT (IN IN_TBNAME VARCHAR(30), -- 目标表名 IN IN_FOCUSFIELD VARCHAR(100), -- 关注字段(多字段逗号分隔) IN IN_FIELD VARCHAR(2000), -- 显示字段 IN IN_WHERE VARCHAR(2000), -- 筛选条件 IN IN_ORDERBY VARCHAR(2000), -- 排序字段(多字段逗号分隔) IN IN_GROUPBY VARCHAR(100), -- 分组字段(多字段逗号分隔) IN IN_TREND VARCHAR(5), -- 连续趋势(desc减,asc增) IN IN_TRENDSTEP VARCHAR(20), -- 连续步长 IN IN_RESULTTABLE VARCHAR(100), -- 结果表名 IN IN_TABLESPACE VARCHAR(100), -- 结果表生成表空间 IN IN_INDEXSPACE VARCHAR(100), -- 结果表索引空间 OUT OUT_CODE INTEGER, OUT OUT_MSG VARCHAR(8000) ) SPECIFIC PRO_TREND_EXTRACT LANGUAGE SQL NOT DETERMINISTIC CALLED ON NULL INPUT MODIFIES SQL DATA INHERIT SPECIAL REGISTERS begin declare SQLCODE integer default 0; ------------sql 变量 ------------sql 变量 DECLARE sql_select VARCHAR(8000); DECLARE sql_select1 VARCHAR(8000); DECLARE sql_insert VARCHAR(8000); DECLARE sql_create VARCHAR(8000); DECLARE V_FOCUSFIELD_1 DECIMAL(31,8); DECLARE V_FOCUSFIELD_2 DECIMAL(31,8); DECLARE v_groupid int; DECLARE v_groupid_c char(20); DECLARE trd_task1 CURSOR FOR SQL_CUR1; ------------------创建结果表------------------------- SET sql_create = 'create table ' ||IN_RESULTTABLE|| ' as ( select ' ||IN_FIELD|| ' from ' ||IN_TBNAME|| ' )' || 'definition only IN ' ||IN_TABLESPACE|| ' INDEX IN ' ||IN_INDEXSPACE|| ' not logged initially';---- EXECUTE IMMEDIATE sql_create;---- COMMIT;-- --创建中间表 create table zjb (groupid int); SET sql_create = 'insert into zjb ( select groupid from(select '||IN_FOCUSFIELD||',rank() over(order by '||IN_GROUPBY||') as groupid from '||IN_TBNAME||') a group by groupid having max(cast('||IN_FOCUSFIELD||' as int)) - min(cast('||IN_FOCUSFIELD||' as int)) >= cast('||IN_TRENDSTEP||' as int))'; EXECUTE IMMEDIATE sql_create;---- COMMIT;-- set sql_select = 'select distinct groupid from zjb'; PREPARE SQL_CUR1 FROM sql_select; OPEN trd_task1; f_loop1:loop FETCH trd_task1 INTO v_groupid; set v_groupid_c=char(v_groupid); SET sql_insert = 'insert into ' ||IN_RESULTTABLE|| ' (select distinct '||IN_FIELD|| ' from (SELECT '||IN_FIELD||',RANK() OVER(ORDER BY '||IN_GROUPBY||') as groupid from '||IN_TBNAME||' where '||IN_WHERE||' order by '||IN_FOCUSFIELD||','||IN_GROUPBY||','||IN_ORDERBY||' '||IN_TREND||') a where groupid='||v_groupid_c||')'; EXECUTE IMMEDIATE sql_insert;---- END loop f_loop1;---- CLOSE trd_task1;-- --删除中间表 drop table zjb; COMMIT; END;
附件:
s.sql (2.7 KB)