DB2存储过程

 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 不知道是不是要加，也不确切的知道有什么用。

转载于:https://www.cnblogs.com/justbeginning/p/4326577.html