存储过程除了银行，社保等机构，在大多数的项目中并不会使用。博主将常用的语法汇总。如果有兴趣可以学习。
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
