JAVA高级面试进阶训练营视频教程

admin

存储过程最后一行加结束符@：

然后执行：db2 -td@ -vf /home/WGJ/proc_data_calculate.sql

  1 [slsadmin@localhost /]$ db2 -td@ -vf /home/WGJ/proc_data_calculate.sql
  2 CREATE OR REPLACE PROCEDURE "SLSADMIN"."PROC_DATA_CALCULATE"
  3 (
  4 IN i_report_id  VARCHAR(20),
  5 IN i_report_date  VARCHAR(20),
  6 IN i_organ_id  VARCHAR(20),
  7 OUT o_msg varchar(32672),
  8 OUT o_ret varchar(32672)
  9 )
 10 LANGUAGE SQL
 11 SPECIFIC SQL140801094913965
 12 RESULT SETS 2
 13 BEGIN
 14   DECLARE v_propotype_table CHAR(30);
 15   DECLARE v_physical_table CHAR(50);
 16   DECLARE v_temp_table CHAR(30);
 17   DECLARE v_history_table CHAR(30);
 18   DECLARE v_columns VARCHAR(4000);
 19   declare sSql varchar(1000) ;  
 20   declare eSql varchar(32672);
 21 
 22     SELECT phy_table, phy_table||'_'||substr(i_report_date, 5, 2)
 23       INTO v_propotype_table,v_temp_table
 24       FROM code_rep_report t
 25      WHERE t.pkid = i_report_id;
 26     --真心不会啊
 27     IF v_propotype_table = 'rep_dataf' THEN
 28 
 29     IF i_report_id = '9101' THEN
 30      set v_physical_table = 'VIEW_DATAF_' || substr(i_report_date, 5, 2);
 31      set  v_history_table  = 'wgj_data_submit_info_hist_f';
 32      set  v_columns = 'ORGAN_ID,REPORT_ID,ITEM_ID,REPORT_DATE,ITEMVALUE1,ITEMVALUE2,ITEMVALUE21';
 33     ELSE
 34     SELECT phy_table||'_'||substr(i_report_date, 5, 2)||'_0'
 35       INTO v_physical_table
 36       FROM code_rep_report t
 37      WHERE t.pkid = i_report_id;
 38      set  v_history_table  = 'wgj_data_submit_info_hist_f';
 39      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';
 40     
 41     END IF;
 42     
 43     ELSE
 44     SELECT phy_table||'_'||substr(i_report_date, 5, 2)
 45       INTO v_physical_table
 46       FROM code_rep_report t
 47      WHERE t.pkid = i_report_id;
 48      set  v_history_table = 'wgj_data_submit_info_hist_d';
 49      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';
 50     END IF;
 51            
 52     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;
 53 
 54     set eSql = 'INSERT INTO wgj_data_submit_info
 55       SELECT wgj_data_submit_info_seq.nextval pkid,
 56              report_id report_id,
 57              decode(flag,''1'',''A'',''2'',''D'',''3'',''C'') operation_type,
 58              '''' remark,
 59              organ_id organ_id,
 60              report_date report_date,
 61              item_id item_id
 62         FROM (SELECT report_id, organ_id, report_date, item_id, SUM(d) flag
 63                 FROM (SELECT report_id, organ_id, report_date, item_id, 1 d
 64                         FROM (SELECT ' || v_columns || '
 65                                 FROM '||v_physical_table||' t
 66                                WHERE t.report_id = ' ||
 67                       i_report_id || '
 68                                  AND t.report_date = ''' ||
 69                       i_report_date || '''
 70                                  AND t.organ_id = ''' ||
 71                       i_organ_id || '''
 72                               except
 73                               SELECT ' || v_columns || '
 74                                 FROM ' ||
 75                       v_history_table || ' f
 76                                WHERE f.report_id = ' ||
 77                       i_report_id || '
 78                                  AND f.report_date = ''' ||
 79                       i_report_date || '''
 80                                  AND f.organ_id = ''' ||
 81                       i_organ_id || '''
 82                                  AND f.send_bat = ''1''
 83                                  AND f.operation_type !=''D'')
 84                       UNION ALL
 85                       SELECT report_id, organ_id, report_date, item_id, 2 d
 86                         FROM (SELECT ' || v_columns || '
 87                                 FROM ' ||
 88                       v_history_table || ' f
 89                                WHERE f.report_id = ' ||
 90                       i_report_id || '
 91                                  AND f.report_date = ''' ||
 92                       i_report_date || '''
 93                                  AND f.organ_id = ''' ||
 94                       i_organ_id || '''
 95                                  AND f.send_bat = ''1''
 96                                  AND f.operation_type !=''D''
 97                               except
 98                               SELECT ' || v_columns || '
 99                                 FROM '||v_physical_table||' t
100                                WHERE t.report_id = ' ||
101                       i_report_id || '
102                                  AND t.report_date =  ''' ||
103                       i_report_date || '''
104                                  AND t.organ_id = ''' ||
105                       i_organ_id || '''))
106                        group by report_id, organ_id, report_date, item_id
107                                 )';
108         prepare s3 from eSql;
109         execute s3;  
110         commit;
111   set o_msg =eSql; 
112   set o_ret = '0';
113 END
114 DB20000I  The SQL command completed successfully.