DB2存储过程
浏览数：23 /
时间：2015年06月12日
1 CREATE PROCEDURE "SH"."ATG" () 2 LANGUAGE SQL 3 MODIFIES SQL DATA 4 CALLED ON NULL INPUT 5 BEGIN 6 declare v_sno varchar(100); 7 declare v_cno varchar(18); 8 declare v_cmu varchar(12); 9 declare v_node int; 10 declare v_count int; 11 declare v_fakeid varchar(30); 12 declare v_count_f int; 13 --declare v_sql varchar(100); 14 select count(certificate_no) into v_count_f from TX_VERIFY_APPLY_INFO 15 where current_node_code < 4 16 and current_node_code > 1 ; 17 begin 18 declare v_cur cursor for select server_no,certificate_no,community,current_node_code 19 from TX_VERIFY_APPLY_INFO 20 where current_node_code < 4 21 and current_node_code > 1 ; 22 23 open v_cur; 24 set v_count = 0; 25 while (v_count_f > 0) do 26 FETCH v_cur into v_sno,v_cno,v_cmu,v_node; 27 --delete 28 delete from tx_income_gz where certificate_no=v_cno; 29 delete from tx_income_jy where certificate_no=v_cno; 30 delete from tx_income_zy where certificate_no=v_cno; 31 delete from tx_income_cc where certificate_no=v_cno; 32 --set v_sql = ‘delete from tx_income_cc where certificate_no=‘||v_cno; 33 --insert into temp_msg values(v_sql); 34 set v_count=v_count+1; 35 set v_fakeid = RTRIM(‘S20150310FAKE‘||CHAR(v_count)); 36 if (v_node = 2) then 37 --jd 38 --1 39 INSERT INTO tx_income_jy (LSH,SERVER_NO,SERVICE_TYPE,CERTIFICATE_NO,PERSON_ID,DOOR_ID,COMMUNITY,D204B120001,D204B120002,D204B120003,D204B120004,D204B120005,D204B120006,D204B120007,D204B120008,D204B120009,D204B120010,D204B120011,D204B120012,D204B120013,D204B120014,D204B120015,D204B120016,D204B120017,D204B120018,D204B120019,D204B120020,CURRENT_NODE_CODE,LOCK_FLAG) 40 VALUES (v_fakeid||‘1‘,v_sno,‘ccsyw‘,v_cno,null,null,v_cmu,0.00,0.00,‘‘,‘‘,‘‘,‘‘,‘‘,0.00,0.00,‘‘,‘‘,‘‘,‘‘,null,null,null,null,‘‘,‘‘,0.00,1,1); 41 INSERT INTO tx_income_zy (LSH,SERVER_NO,SERVICE_TYPE,CERTIFICATE_NO,PERSON_ID,DOOR_ID,COMMUNITY,D204B140001,D204B140002,D204B140003,D204B140004,D204B140005,D204B140006,D204B140007,D204B140008,D204B140009,D204B140010,D204B140011,D204B140012,D204B140013,D204B140014,D204B140015,D204B140016,D204B140017,D204B140018,D204B140019,D204B140020,D204B140021,D204B140022,D204B140023,CURRENT_NODE_CODE,LOCK_FLAG) 42 VALUES (v_fakeid||‘2‘,v_sno,‘ccsyw‘,v_cno,null,null,v_cmu,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,1,1); 43 INSERT INTO tx_income_cc (LSH,SERVER_NO,SERVICE_TYPE,CERTIFICATE_NO,PERSON_ID,DOOR_ID,COMMUNITY,D204B130001,D204B130002,D204B130003,D204B130004,D204B130005,D204B130006,D204B130007,D204B130008,CURRENT_NODE_CODE,LOCK_FLAG) 44 VALUES (v_fakeid||‘3‘,v_sno,‘ccsyw‘,v_cno,null,null,v_cmu,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,1,1); 45 INSERT INTO tx_income_gz (LSH,SERVER_NO,SERVICE_TYPE,CERTIFICATE_NO,PERSON_ID,DOOR_ID,COMMUNITY,D204B110001,D204B110002,D204B110003,D204B110004,D204B110005,D204B110006,D204B110007,D204B110008,D204B110009,CURRENT_NODE_CODE,LOCK_FLAG) 46 VALUES (v_fakeid||‘4‘,v_sno,‘ccsyw‘,v_cno,null,null,v_cmu,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,1,1); 47 else 48 if (v_node = 3) then 49 --qx 50 --1 51 INSERT INTO tx_income_jy (LSH,SERVER_NO,SERVICE_TYPE,CERTIFICATE_NO,PERSON_ID,DOOR_ID,COMMUNITY,D204B120001,D204B120002,D204B120003,D204B120004,D204B120005,D204B120006,D204B120007,D204B120008,D204B120009,D204B120010,D204B120011,D204B120012,D204B120013,D204B120014,D204B120015,D204B120016,D204B120017,D204B120018,D204B120019,D204B120020,CURRENT_NODE_CODE,LOCK_FLAG) 52 VALUES (v_fakeid||‘1‘,v_sno,‘ccsyw‘,v_cno,null,null,v_cmu,0.00,0.00,‘‘,‘‘,‘‘,‘‘,‘‘,0.00,0.00,‘‘,‘‘,‘‘,‘‘,null,null,null,null,‘‘,‘‘,0.00,1,1); 53 INSERT INTO tx_income_zy (LSH,SERVER_NO,SERVICE_TYPE,CERTIFICATE_NO,PERSON_ID,DOOR_ID,COMMUNITY,D204B140001,D204B140002,D204B140003,D204B140004,D204B140005,D204B140006,D204B140007,D204B140008,D204B140009,D204B140010,D204B140011,D204B140012,D204B140013,D204B140014,D204B140015,D204B140016,D204B140017,D204B140018,D204B140019,D204B140020,D204B140021,D204B140022,D204B140023,CURRENT_NODE_CODE,LOCK_FLAG) 54 VALUES (v_fakeid||‘2‘,v_sno,‘ccsyw‘,v_cno,null,null,v_cmu,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,1,1); 55 INSERT INTO tx_income_cc (LSH,SERVER_NO,SERVICE_TYPE,CERTIFICATE_NO,PERSON_ID,DOOR_ID,COMMUNITY,D204B130001,D204B130002,D204B130003,D204B130004,D204B130005,D204B130006,D204B130007,D204B130008,CURRENT_NODE_CODE,LOCK_FLAG) 56 VALUES (v_fakeid||‘3‘,v_sno,‘ccsy‘,v_cno,null,null,v_cmu,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,1,1); 57 INSERT INTO tx_income_gz (LSH,SERVER_NO,SERVICE_TYPE,CERTIFICATE_NO,PERSON_ID,DOOR_ID,COMMUNITY,D204B110001,D204B110002,D204B110003,D204B110004,D204B110005,D204B110006,D204B110007,D204B110008,D204B110009,CURRENT_NODE_CODE,LOCK_FLAG) 58 VALUES (v_fakeid||‘4‘,v_sno,‘ccsyw‘,v_cno,null,null,v_cmu,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,1,1); 59 --2 60 INSERT INTO tx_income_jy (LSH,SERVER_NO,SERVICE_TYPE,CERTIFICATE_NO,PERSON_ID,DOOR_ID,COMMUNITY,D204B120001,D204B120002,D204B120003,D204B120004,D204B120005,D204B120006,D204B120007,D204B120008,D204B120009,D204B120010,D204B120011,D204B120012,D204B120013,D204B120014,D204B120015,D204B120016,D204B120017,D204B120018,D204B120019,D204B120020,CURRENT_NODE_CODE,LOCK_FLAG) 61 VALUES (v_fakeid||‘5‘,v_sno,‘ccsyw‘,v_cno,null,null,v_cmu,0.00,0.00,‘‘,‘‘,‘‘,‘‘,‘‘,0.00,0.00,‘‘,‘‘,‘‘,‘‘,null,null,null,null,‘‘,‘‘,0.00,2,1); 62 INSERT INTO tx_income_zy (LSH,SERVER_NO,SERVICE_TYPE,CERTIFICATE_NO,PERSON_ID,DOOR_ID,COMMUNITY,D204B140001,D204B140002,D204B140003,D204B140004,D204B140005,D204B140006,D204B140007,D204B140008,D204B140009,D204B140010,D204B140011,D204B140012,D204B140013,D204B140014,D204B140015,D204B140016,D204B140017,D204B140018,D204B140019,D204B140020,D204B140021,D204B140022,D204B140023,CURRENT_NODE_CODE,LOCK_FLAG) 63 VALUES (v_fakeid||‘6‘,v_sno,‘ccsyw‘,v_cno,null,null,v_cmu,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,2,1); 64 INSERT INTO tx_income_cc (LSH,SERVER_NO,SERVICE_TYPE,CERTIFICATE_NO,PERSON_ID,DOOR_ID,COMMUNITY,D204B130001,D204B130002,D204B130003,D204B130004,D204B130005,D204B130006,D204B130007,D204B130008,CURRENT_NODE_CODE,LOCK_FLAG) 65 VALUES (v_fakeid||‘7‘,v_sno,‘ccsyw‘,v_cno,null,null,v_cmu,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,2,1); 66 INSERT INTO tx_income_gz (LSH,SERVER_NO,SERVICE_TYPE,CERTIFICATE_NO,PERSON_ID,DOOR_ID,COMMUNITY,D204B110001,D204B110002,D204B110003,D204B110004,D204B110005,D204B110006,D204B110007,D204B110008,D204B110009,CURRENT_NODE_CODE,LOCK_FLAG) 67 VALUES (v_fakeid||‘8‘,v_sno,‘ccsyw‘,v_cno,null,null,v_cmu,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,2,1); 68 end if; 69 end if; 70 set v_count_f = v_count_f - 1; 71 end while; 72 close v_cur; 73 end; 74 END 75 @
以上是一个简易存储过程，包含 while循环 、delete 、insert、游标操作。CALLED ON NULL INPUT 不知道是不是要加，也不确切的知道有什么用。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
