存储过程最后一行加结束符@：
然后执行：db2 -td@ -vf /home/WGJ/proc_data_calculate.sql
[slsadmin@localhost /]$ db2 -td@ -vf /home/WGJ/proc_data_calculate.sql
CREATE OR REPLACE PROCEDURE "SLSADMIN"."PROC_DATA_CALCULATE"
(
IN i_report_id VARCHAR(20),
IN i_report_date VARCHAR(20),
IN i_organ_id VARCHAR(20),
OUT o_msg varchar(32672),
OUT o_ret varchar(32672)
)
LANGUAGE SQL
SPECIFIC SQL140801094913965
RESULT SETS 2
BEGIN
DECLARE v_propotype_table CHAR(30);
DECLARE v_physical_table CHAR(50);
DECLARE v_temp_table CHAR(30);
DECLARE v_history_table CHAR(30);
DECLARE v_columns VARCHAR(4000);
declare sSql varchar(1000) ;
declare eSql varchar(32672); SELECT phy_table, phy_table||'_'||substr(i_report_date, 5, 2)
INTO v_propotype_table,v_temp_table
FROM code_rep_report t
WHERE t.pkid = i_report_id;
--真心不会啊
IF v_propotype_table = 'rep_dataf' THEN IF i_report_id = '' THEN
set v_physical_table = 'VIEW_DATAF_' || substr(i_report_date, 5, 2);
set v_history_table = 'wgj_data_submit_info_hist_f';
set v_columns = 'ORGAN_ID,REPORT_ID,ITEM_ID,REPORT_DATE,ITEMVALUE1,ITEMVALUE2,ITEMVALUE21';
ELSE
SELECT phy_table||'_'||substr(i_report_date, 5, 2)||'_0'
INTO v_physical_table
FROM code_rep_report t
WHERE t.pkid = i_report_id;
set v_history_table = 'wgj_data_submit_info_hist_f';
set v_columns = 'ITEMVALUE63,ITEMVALUE64,ITEMVALUE65,ITEMVALUE66,ITEMVALUE67,ITEMVALUE68,ITEMVALUE69,ITEMVALUE7,ITEMVALUE70,ITEMVALUE71,ITEMVALUE72,ITEMVALUE73,ITEMVALUE74,ITEMVALUE75,ITEMVALUE76,ITEMVALUE77,ITEMVALUE78,ITEMVALUE79,ITEMVALUE8,ITEMVALUE80,ITEMVALUE9,ORGAN_ID,REPORT_DATE,REPORT_ID,ITEMVALUE81,ITEMVALUE82,ITEMVALUE83,ITEMVALUE84,ITEMVALUE85,ITEMVALUE86,ITEMVALUE87,ITEMVALUE88,ITEMVALUE89,ITEMVALUE90,ITEMVALUE91,ITEMVALUE92,ITEMVALUE93,ITEMVALUE94,ITEMVALUE95,ITEMVALUE96,ITEMVALUE97,ITEMVALUE98,ITEMVALUE99,ITEMVALUE100,ITEMVALUE101,ITEMVALUE102,ITEMVALUE103,ITEMVALUE104,ITEMVALUE105,ITEMVALUE106,ITEMVALUE107,ITEMVALUE108,ITEMVALUE109,ITEMVALUE110,ITEMVALUE111,ITEMVALUE112,ITEMVALUE113,ITEMVALUE114,ITEMVALUE115,ITEMVALUE116,ITEMVALUE117,ITEMVALUE118,ITEMVALUE119,ITEMVALUE120,ITEMVALUE121,ITEMVALUE122,ITEMVALUE123,ITEMVALUE124,ITEMVALUE125,ITEMVALUE126,ITEMVALUE127,ITEMVALUE128,ITEMVALUE129,ITEMVALUE130,ITEMVALUE131,ITEMVALUE132,ITEMVALUE133,ITEMVALUE134,ITEMVALUE135,ITEMVALUE136,ITEMVALUE137,ITEMVALUE138,ITEMVALUE139,ITEMVALUE140,ITEM_ID,ITEMVALUE1,ITEMVALUE10,ITEMVALUE11,ITEMVALUE12,ITEMVALUE13,ITEMVALUE14,ITEMVALUE15,ITEMVALUE16,ITEMVALUE17,ITEMVALUE18,ITEMVALUE19,ITEMVALUE2,ITEMVALUE20,ITEMVALUE21,ITEMVALUE22,ITEMVALUE23,ITEMVALUE24,ITEMVALUE25,ITEMVALUE26,ITEMVALUE27,ITEMVALUE28,ITEMVALUE29,ITEMVALUE3,ITEMVALUE30,ITEMVALUE31,ITEMVALUE32,ITEMVALUE33,ITEMVALUE34,ITEMVALUE35,ITEMVALUE36,ITEMVALUE37,ITEMVALUE38,ITEMVALUE39,ITEMVALUE4,ITEMVALUE40,ITEMVALUE41,ITEMVALUE42,ITEMVALUE43,ITEMVALUE44,ITEMVALUE45,ITEMVALUE46,ITEMVALUE47,ITEMVALUE48,ITEMVALUE49,ITEMVALUE5,ITEMVALUE50,ITEMVALUE51,ITEMVALUE52,ITEMVALUE53,ITEMVALUE54,ITEMVALUE55,ITEMVALUE56,ITEMVALUE57,ITEMVALUE58,ITEMVALUE59,ITEMVALUE6,ITEMVALUE60,ITEMVALUE61,ITEMVALUE62'; END IF; ELSE
SELECT phy_table||'_'||substr(i_report_date, 5, 2)
INTO v_physical_table
FROM code_rep_report t
WHERE t.pkid = i_report_id;
set v_history_table = 'wgj_data_submit_info_hist_d';
set v_columns = 'ITEMVALUE38,ITEMVALUE39,ITEMVALUE40,ITEMVALUE41,ITEMVALUE42,ITEMVALUE43,ITEMVALUE44,ITEMVALUE45,ITEMVALUE46,ITEMVALUE47,ITEMVALUE48,ITEMVALUE49,ITEMVALUE50,ORGAN_ID,REPORT_DATE,REPORT_ID,ITEM_ID,ITEMVALUE1,ITEMVALUE2,ITEMVALUE3,ITEMVALUE4,ITEMVALUE5,ITEMVALUE6,ITEMVALUE7,ITEMVALUE8,ITEMVALUE9,ITEMVALUE10,ITEMVALUE11,ITEMVALUE12,ITEMVALUE13,ITEMVALUE14,ITEMVALUE15,ITEMVALUE16,ITEMVALUE17,ITEMVALUE18,ITEMVALUE19,ITEMVALUE20,ITEMVALUE21,ITEMVALUE22,ITEMVALUE23,ITEMVALUE24,ITEMVALUE25,ITEMVALUE26,ITEMVALUE27,ITEMVALUE28,ITEMVALUE29,ITEMVALUE30,ITEMVALUE31,ITEMVALUE32,ITEMVALUE33,ITEMVALUE34,ITEMVALUE35,ITEMVALUE36,ITEMVALUE37';
END IF; DELETE FROM wgj_data_submit_info t WHERE t.report_id =i_report_id and t.organ_id=i_organ_id and t.report_date=i_report_date; set eSql = 'INSERT INTO wgj_data_submit_info
SELECT wgj_data_submit_info_seq.nextval pkid,
report_id report_id,
decode(flag,'''',''A'','''',''D'','''',''C'') operation_type,
'''' remark,
organ_id organ_id,
report_date report_date,
item_id item_id
FROM (SELECT report_id, organ_id, report_date, item_id, SUM(d) flag
FROM (SELECT report_id, organ_id, report_date, item_id, 1 d
FROM (SELECT ' || v_columns || '
FROM '||v_physical_table||' t
WHERE t.report_id = ' ||
i_report_id || '
AND t.report_date = ''' ||
i_report_date || '''
AND t.organ_id = ''' ||
i_organ_id || '''
except
SELECT ' || v_columns || '
FROM ' ||
v_history_table || ' f
WHERE f.report_id = ' ||
i_report_id || '
AND f.report_date = ''' ||
i_report_date || '''
AND f.organ_id = ''' ||
i_organ_id || '''
AND f.send_bat = ''''
AND f.operation_type !=''D'')
UNION ALL
SELECT report_id, organ_id, report_date, item_id, 2 d
FROM (SELECT ' || v_columns || '
FROM ' ||
v_history_table || ' f
WHERE f.report_id = ' ||
i_report_id || '
AND f.report_date = ''' ||
i_report_date || '''
AND f.organ_id = ''' ||
i_organ_id || '''
AND f.send_bat = ''''
AND f.operation_type !=''D''
except
SELECT ' || v_columns || '
FROM '||v_physical_table||' t
WHERE t.report_id = ' ||
i_report_id || '
AND t.report_date = ''' ||
i_report_date || '''
AND t.organ_id = ''' ||
i_organ_id || '''))
group by report_id, organ_id, report_date, item_id
)';
prepare s3 from eSql;
execute s3;
commit;
set o_msg =eSql;
set o_ret = '';
END
DB20000I The SQL command completed successfully.