通过一个实际的例子学习SQLServer存储过程

1. --定义存储过程 ??
2. CREATE?PROCEDURE??xxxxxxxx_p ??
3. ?( ??
4. ??--传递参数 ??
5. ??@ym???char(6) ??
6. ?) ??
7. As??
8. ??--定义变量，@xx表示局部变量，@@xx表示全局变量。定义多个变量用","号分割 ??
9. ?declare?@ym_ln?char(6) ??
10. ?declare?@cpcode?char(10),??
11. ??@cpname?char(50),??
12. ??@swcode?char(10),??
13. ??@swname?char(50),??
14. ??@czgscode?char(10),??
15. ??@czgscode_?char(10),??
16. ??@czgsname?char(50),??
17. ??@qylx?char(2),??
18. ??@qyxz?char(30) ??
19. ?declare?@tdcode?char(10),??
20. ??@sb_amt?numeric,??
21. ??@sb_ln_amt?numeric,??
22. ??@sh_amt?numeric,??
23. ??@sh_ln_amt?numeric,??
24. ??@ts_amt?numeric,??
25. ??@ts_ln_amt?numeric??
26. ?declare?@ybmy?numeric(12,6),??
27. ??@jljg?numeric(12,??
28. ??@other?numeric(12,??
29. ??@ybmy_ln?numeric(12,??
30. ??@jljg_ln?numeric(12,??
31. ??@other_ln?numeric(12,6) ??
32. ?declare?@rowcount?int??
33. ?--删除表中现有符合ym=@ym的数据 ??
34. ?delete?from?cs_xxxxxxxx?where?ym=@ym ??
35. ??--给变量赋初值,用到了cast，substring函数。cast用于类型转换，substring用户截取字符串 ??
36. ?set?@ym_ln=cast((substring(@ym,1,4)-1)?as?char(4))?+substring(@ym,5,2) ??
37. ?--声明一个游标 ??
38. ?declare?cur_xxxx?cursor?for? ??
39. ??select?cpcode.code?as?cpcode?,cpcode.name?as?cpname?,cpcode.swcode?as?swcode,swcode.name??
40. ??as?swname,cs_swcode_czgs.czgs?as?czgscode,cpcode.qylx?as?qylx?from?cpcode? ??
41. ??left?join?cs_swcode_czgs?on?cpcode.swcode=cs_swcode_czgs.swcode? ??
42. ??left?join?swcode?on?cpcode.swcode=swcode.code?where?cpcode.swcode<>''? ??
43. ?--打来游标 ??
44. ?open?cur_xxxx ??
45. ?--取游标中第一行记录并且写入变量。 ??
46. ?fetch?next?from?cur_xxxx? ??
47. ??into??@cpcode,@cpname,@swcode,@swname,@czgscode,@qylx ??
48. ?--当@@fetch_status?=?0即取出了有效行时处理，用到了while语句，结构while?xx?begin?xxx?end ??
49. ?while?@@fetch_status?=?0 ??
50. ?begin??
51. ??--用select语句给变量赋值 ??
52. ??select?@czgscode_=czgs?from?cs_cpcode_czgs?where?cpcode=@cpcode ??
53. ??--if语句，完整结构if?xx?begin?xxx?end ??
54. ??if?@czgscode_?is?not?null??
55. ???set?@czgscode=@czgscode_ ??
56. ??select?@czgsname=name?from?cs_czgs?where?code=@czgscode ??
57. ??--if?else结构，完整结构?if?xx?begin?xxx?else?xxxx?end ??
58. ??if?@qylx='11' ??
59. ???set?@qyxz='内资企业' ??
60. ??else??
61. ???set?@qyxz='外商投资企业' ??
62. ??--用select语句给变量赋值，用到了isnull函数。 ??
63. ??select?@sb_amt=isnull(sum(mdtse),0)?from?mdtsb?where?sb_ym=@ym?and?cpcode=@cpcode ??
64. ??select?@sb_ln_amt=isnull(sum(mdtse),0)?from?mdtsb?where?sb_ym=@ym_ln?and?cpcode=@cpcode ??
65. ??-- ??
66. ??--省略n行类似赋值语句 ??
67. ??-- ??
68. ??--goto语句,跳转到insertmodule ??
69. ??goto?insertmodule ??
70. ???-- ??
71. ??--省略n行同类处理语句 ??
72. ??-- ??
73. insertmodule: ??
74. ??--用is?null表达式判断是否为null ??
75. ??if?@sb_amt??????is?null?set?@sb_amt????=0???????? ??
76. ??if?@sb_ln_amt????is?null?set?@sb_ln_amt???=0??????????? ??
77. ??if?@sh_amt??????is?null?set?@sh_amt????=0???????? ??
78. ??if?@sh_ln_amt????is?null?set?@sh_ln_amt???=0??????????? ??
79. ??if?@ts_amt??????is?null?set?@ts_amt????=0???????? ??
80. ??if?@ts_ln_amt?????is?null?set?@ts_ln_amt???=0?? ??
81. ??--插入一般贸易 ??
82. ????select?@ybmy=zb?from?cs_scqybl?where?tdcode='一般贸易'?and?ym=@ym?and?cpcode=@cpcode ??
83. ????select?@ybmy_ln=zb?from?cs_scqybl?where?tdcode='一般贸易'?and?ym=@ym_ln?and?cpcode=@cpcode ??
84. ????if?@ybmy?is?null??
85. ?????begin??
86. ??????--在存储过程中执行存储过程 ??
87. ??????exec?xxxbl?@cpcode,@ym ??
88. ?????end??
89. ????if?@ybmy_ln?is?null??
90. ?????begin??
91. ??????exec?xxxxbl?@cpcode,@ym_ln ??
92. ?????end??
93. ????select?@jljg=zb?from?cs_scqybl?where?tdcode='进料加工'?and?ym=@ym?and?cpcode=@cpcode ??
94. ????select?@jljg_ln=zb?from?cs_scqybl?where?tdcode='进料加工'?and?ym=@ym_ln?and?cpcode=@cpcode ??
95. ????select?@other=zb?from?cs_scqybl?where?tdcode='其他'?and?ym=@ym?and?cpcode=@cpcode ??
96. ????select?@other_ln=zb?from?cs_scqybl?where?tdcode='其他'?and?ym=@ym_ln?and?cpcode=@cpcode ??
97. ??--把上面各个步骤运算得到的值insert进表中cs_xxxxxxxx ??
98. ????insert?into?cs_xxxxxxxx?(xx,xxx,xxxx)?values(vv,vvv,vvvv?) ??
99. ??--取取游标中下一行记录并写入变量 ??
100. ??fetch?next?from?cur_xxxx?into??@cpcode,@qylx ??
101. ?--结束while循环 ??
102. ?end??
103. ?--关闭游标 ??
104. ?close?cur_xxxx ??
105. ?--删除游标 ??
106. ?deallocate?cur_xxxx ??
107. GO ??

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!