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;
游标中只有3条数据~~~
存储过程附件
附件:
s.sql (2.7 KB)
收起