mysql的存儲過程

存儲過程除了銀行，社保等機構，在大多數的項目中并不會使用。部落客将常用的文法彙總。如果有興趣可以學習。

1. BEGIN
2.      #兩個遊标相隔需要 begin end 隔開
3.       begin
4.                      declare temp_id int;
5.                      declare sum1 int;               
6.                      declare done INT DEFAULT 0;             
7. 
8.                      #插入管理者主表遊标 
9.                      declare cursor_three CURSOR FOR
10.             #跟sql語句指定遊标包括的值
11.                         select id from org_department_info;
12.             #指定跳出遊标的條件
13.                         declare continue handler for not found set done =1;
14.                       #打開遊标
15.                         open cursor_three;
16.             #開始循環遊标
17.                         myLoop:LOOP
18.             #通過遊标向變量中指派
19.                         fetch cursor_three into temp_id; 
20.             #跳出循環的條件 
21.                         if done=1 then
22.                                 leave myLoop;
23.                         end if; 
24.             #查詢是或否已有
25. select count(1) as sum1 into sum1 from org_manager where  departId = temp_id;
26.             #判斷
27.             if sum1 = 0 THEN
28. INSERT INTO org_manager
29.                                          ( `departId`, `departManagerId`, `applicationId`, `status`, `approvalSign`, `maintenancePeriod`, `remark`, `addPeople`, `addTimes`, `operatorId`, `operateTimes`) 
30.                                             VALUES ( temp_id, 1, 1, '1', '1', '1', 1, 1, SYSDATE(), 1,  SYSDATE());
31. end if;
32.                         end loop myLoop;
33.                         CLOSE cursor_three ;
34. 
35.         end;
36. 
37. BEGIN
38. declare doneTwo INT DEFAULT 0;
39. declare manage_id int;
40. declare sum2 int;
41. 
42.                         #插入管理者主表遊标 
43.                      declare cursor_two CURSOR FOR
44.                      select id from org_manager;
45. 
46. declare continue handler for not found set doneTwo =1; 
47. 
48. open cursor_two;
49.            l2: LOOP
50.                                 fetch cursor_two into manage_id;  
51.                                                         if doneTwo = 1 then
52.                                                                 leave l2;
53.                                                         end if;
54.             #查詢是或否已有
55. select count(1) as sum2 into sum2 from org_manager_records where orgManagerInfoId = manage_id;
56.             if sum2 = 0 THEN
57. INSERT INTO org_manager_records 
58.                 ( `orgManagerInfoId`, `departName`, `departCode`, `departManagerId`, `managerName`, `status`, `applicationId`, `approvalSign`, `maintenancePeriod`, `remark`, `addPeople`, `addTimes`, `operatorId`, `operateTimes`) 
59. VALUES
60.                  ( manage_id, '', '', '1', '', '1', '1', '1', '1', '111', '1', SYSDATE(), '1', SYSDATE());
61. end if;
62. end loop l2;
63. CLOSE cursor_two ;
64. end ;
65. 
66. END