oracle 数据库不用了,改用SQL,要学SQL了,第一个手工写的存储过程

1if exists (select * from sysobjects where id=object_id('PrHelloWord_12') 
2 and objectproperty(id,'isprocedure')=1) 
3drop procedure PrHelloWord_12 
4go 
5create procedure PrHelloWord_12 
6as 
7
8select * from sysobjects 
9

  1
  2CREATE    procedure sp_m_DWE_INFO
  3(
  4  @btype         int=null,-- 维护类型 1 增加 2 修改 3 删除 4 修改副标题 5帖子功能设置 
  5                         --6帖子栏目的移植 7浏览次数增加 9帖子给予金狐币修改标志 10地区的置顶、首页显示
  6  @P_DI_ID             bigint=null,
  7  @P_DC_ID             bigint = null ,
  8  @P_DC_WRITER         VARCHAR(50)=null,
  9  @P_DC_TITLE          VARCHAR(200)=null,
 10  @P_DC_SOURCE         int=null,
 11  @P_DC_CONTENT        VARCHAR(6000)=null,
 12  @P_DC_INFO_TYPE      int=null,
 13  @P_DC_LOAD_TYPE      int=null,
 14  @P_DC_INFO_DESC      VARCHAR(300)=null,
 15  @P_DC_STATUS         int=null,
 16  @P_DC_SUBTITLE       VARCHAR(200)=null,
 17  @P_TU_ID             bigint=null,
 18  @P_ISATTACH          int=null,
 19  @P_DC_INFO_PATH      VARCHAR(255)=null,
 20  @P_DC_ISSHOW         int=null,
 21  @P_DC_ISTOP          int=null,
 22  @P_DC_ISHOT          int=null,
 23  @P_DC_ISRECOMMEND    int=null,
 24  @P_DC_ISFRONTSHOW    int=null,
 25  @P_DC_ISESSENCE      int=null,
 26  @P_DCF_ACT_DB        bigint=null,
 27  @P_DCF_ACT_CD        bigint=null,
 28  @P_DCF_POOL_DB       bigint=null,
 29  @P_DCF_POOL_CD       bigint=null,
 30  @P_DCF_INACT_DB      bigint=null,
 31  @P_DCF_INACT_CD      bigint=null,
 32  @P_DC_INFO_PATH1     VARCHAR(200)=null,
 33  @P_DC_INFO_PATH2     VARCHAR(200)=null,
 34  @P_DC_INFO_PATH3     VARCHAR(200)=null,
 35  @P_DC_INFO_PATH4     VARCHAR(200)=null,
 36  @P_DC_INFO_PATH5     VARCHAR(200)=null,
 37  @P_DC_EXTREND01      VARCHAR(200)=null,
 38  @P_DC_EXTREND02      VARCHAR(200)=null,
 39  @P_DC_EXTREND03      VARCHAR(200)=null,
 40  @P_DC_EXTREND04      VARCHAR(200)=null,
 41  @P_DC_EXTREND05      VARCHAR(200)=null,
 42  @P_DC_EXTREND06      VARCHAR(200)=null,
 43  @P_DC_EXTREND07      VARCHAR(200)=null,
 44  @P_DC_EXTREND08      VARCHAR(200)=null,
 45  @P_DC_EXTREND09      VARCHAR(200)=null,
 46  @P_DC_EXTREND10      VARCHAR(200)=null,
 47  ----------------------------------------------------
 48  @P_DC_INFO_PATH6     VARCHAR(200)=null,
 49  @P_DC_INFO_PATH7     VARCHAR(200)=null,
 50  @P_DC_INFO_PATH8     VARCHAR(200)=null,
 51  @P_DC_INFO_PATH9     VARCHAR(200)=null,
 52  @P_DC_INFO_PATH10    VARCHAR(200)=null,
 53  @P_DC_EXTREND11      VARCHAR(200)=null,
 54  @P_DC_EXTREND12      VARCHAR(200)=null,
 55  @P_DC_EXTREND13      VARCHAR(6000)=null,
 56  @P_DC_EXTREND14      VARCHAR(6000)=null,
 57  @P_DC_EXTREND15      VARCHAR(200)=null,
 58  @P_DC_EXTREND16      VARCHAR(200)=null,
 59  @P_DC_EXTREND17      VARCHAR(200)=null,
 60  @P_DC_EXTREND18      VARCHAR(200)=null,
 61  @P_DC_EXTREND19      VARCHAR(200)=null,
 62  @P_DC_EXTREND20      VARCHAR(200)=null,
 63  @P_DC_INT01        bigint=null,
 64  @P_DC_INT02        bigint=null,
 65  @P_DC_INT03        bigint=null,
 66  @P_DC_INT04        bigint=null,
 67  @P_DC_INT05        bigint=null,
 68  @bRt_id        bigint=null out, -- 1 返回当前记录id
 69  @bResult       int=null out -- 1 正常 6 未找到相应记录 8 其它错误
 70) as
 71
 72declare   @P_Result int
 73declare  @tran_flag int 
 74begin
 75-- 维护帐（卡）号档案表
 76-- 1 增加
 77  set @bResult = 1
 78  set @bRt_id  = @P_DI_ID
 79set @tran_flag=1
 80  ---
 81    begin transaction t1
 82    if @btype =1 
 83    begin
 84
 85        insert into DWE_INFO(DC_ID,DC_WRITER,DC_TITLE,DC_SOURCE,DC_CONTENT,DC_INFO_TYPE,
 86            DC_LOAD_TYPE,DC_INFO_DESC,DC_STATUS,DC_SUBTITLE,TU_ID,ISATTACH,DC_INFO_PATH,
 87            DC_ISSHOW,DC_ISTOP,DC_ISHOT,DC_ISRECOMMEND,DC_ISFRONTSHOW,DC_ISESSENCE,
 88            DCF_ACT_DB,DCF_ACT_CD,DCF_POOL_DB,DCF_POOL_CD,DCF_INACT_DB,DCF_INACT_CD,
 89            DC_INFO_PATH1,DC_INFO_PATH2,DC_INFO_PATH3,DC_INFO_PATH4,DC_INFO_PATH5,
 90            DC_EXTREND01,DC_EXTREND02,DC_EXTREND03,DC_EXTREND04,DC_EXTREND05,
 91            DC_EXTREND06,DC_EXTREND07,DC_EXTREND08,DC_EXTREND09,DC_EXTREND10,
 92            DC_INFO_PATH6,DC_INFO_PATH7,DC_INFO_PATH8,DC_INFO_PATH9,DC_INFO_PATH10,
 93            DC_EXTREND11,DC_EXTREND12,DC_EXTREND13,DC_EXTREND14,DC_EXTREND15,
 94            DC_EXTREND16,DC_EXTREND17,DC_EXTREND18,DC_EXTREND19,DC_EXTREND20,
 95            DC_INT01,DC_INT02,DC_INT03,DC_INT04,DC_INT05)
 96            values(@P_DC_ID,@P_DC_WRITER,@P_DC_TITLE,@P_DC_SOURCE,@P_DC_CONTENT,@P_DC_INFO_TYPE,
 97            @P_DC_LOAD_TYPE,@P_DC_INFO_DESC,@P_DC_STATUS,@P_DC_SUBTITLE,@P_TU_ID,@P_ISATTACH,@P_DC_INFO_PATH,
 98            @P_DC_ISSHOW,@P_DC_ISTOP,@P_DC_ISHOT,@P_DC_ISRECOMMEND,@P_DC_ISFRONTSHOW,@P_DC_ISESSENCE,
 99            @P_DCF_ACT_DB,@P_DCF_ACT_CD,@P_DCF_POOL_DB,@P_DCF_POOL_CD,@P_DCF_INACT_DB,@P_DCF_INACT_CD,
100            @P_DC_INFO_PATH1,@P_DC_INFO_PATH2,@P_DC_INFO_PATH3,@P_DC_INFO_PATH4,@P_DC_INFO_PATH5,
101            @P_DC_EXTREND01,@P_DC_EXTREND02,@P_DC_EXTREND03,@P_DC_EXTREND04,@P_DC_EXTREND05,
102            @P_DC_EXTREND06,@P_DC_EXTREND07,@P_DC_EXTREND08,@P_DC_EXTREND09,@P_DC_EXTREND10,
103            @P_DC_INFO_PATH6,@P_DC_INFO_PATH7,@P_DC_INFO_PATH8,@P_DC_INFO_PATH9,@P_DC_INFO_PATH10,
104            @P_DC_EXTREND11,@P_DC_EXTREND12,@P_DC_EXTREND13,@P_DC_EXTREND14,@P_DC_EXTREND15,
105            @P_DC_EXTREND16,@P_DC_EXTREND17,@P_DC_EXTREND18,@P_DC_EXTREND19,@P_DC_EXTREND20,
106            @P_DC_INT01,@P_DC_INT02,@P_DC_INT03,@P_DC_INT04,@P_DC_INT05)            
107        if @@error<> 0
108        begin
109            goto error
110        end
111        else
112            select @bRt_id = @@identity 
113      end
114    
115    if @btype =2 
116    begin    
117        update DWE_INFO  set
118              DC_ID           = @P_DC_ID,
119        --      DC_WRITER       = @P_DC_WRITER,
120              DC_TITLE        = @P_DC_TITLE,
121              DC_SOURCE       = @P_DC_SOURCE,
122              DC_CONTENT      = @P_DC_CONTENT,
123              DC_INFO_TYPE    = @P_DC_INFO_TYPE,
124              DC_LOAD_TYPE    = @P_DC_LOAD_TYPE,
125              DC_INFO_DESC    = @P_DC_INFO_DESC  ,
126              DC_STATUS       = @P_DC_STATUS ,
127              DC_SUBTITLE     = @P_DC_SUBTITLE,
128              --dc_addtime      = sysdate,
129              --TU_ID           = @P_TU_ID,
130              ISATTACH        = @P_ISATTACH,
131              DC_INFO_PATH    = @P_DC_INFO_PATH,
132              DC_INFO_PATH1   = @P_DC_INFO_PATH1,
133              DC_INFO_PATH2   = @P_DC_INFO_PATH2,
134              DC_INFO_PATH3   = @P_DC_INFO_PATH3,
135              DC_INFO_PATH4   = @P_DC_INFO_PATH4,
136              DC_INFO_PATH5   = @P_DC_INFO_PATH5,
137              DC_EXTREND01    = @P_DC_EXTREND01,
138              DC_EXTREND02    = @P_DC_EXTREND02,
139              DC_EXTREND03    = @P_DC_EXTREND03,
140              DC_EXTREND04    = @P_DC_EXTREND04,
141              DC_EXTREND05    = @P_DC_EXTREND05,
142              DC_EXTREND06    = @P_DC_EXTREND06,
143              DC_EXTREND07    = @P_DC_EXTREND07,
144              DC_EXTREND08    = @P_DC_EXTREND08,
145              DC_EXTREND09    = @P_DC_EXTREND09,
146              DC_EXTREND10    = @P_DC_EXTREND10,
147              DC_ISSHOW       = @P_DC_ISSHOW,
148              DC_ISTOP        = @P_DC_ISTOP,
149              DC_ISHOT        = @P_DC_ISHOT,
150              DC_ISRECOMMEND  = @P_DC_ISRECOMMEND,
151              DC_ISFRONTSHOW  = @P_DC_ISFRONTSHOW,
152              DC_ISESSENCE    = @P_DC_ISESSENCE,
153              DC_INFO_PATH6   = @P_DC_INFO_PATH6,
154              DC_INFO_PATH7   = @P_DC_INFO_PATH7,
155              DC_INFO_PATH8   = @P_DC_INFO_PATH8,
156              DC_INFO_PATH9   = @P_DC_INFO_PATH9,
157              DC_INFO_PATH10  = @P_DC_INFO_PATH10,
158              DC_EXTREND11    = @P_DC_EXTREND11,
159              DC_EXTREND12    = @P_DC_EXTREND12,
160              DC_EXTREND13    = @P_DC_EXTREND13,
161              DC_EXTREND14    = @P_DC_EXTREND14,
162              DC_EXTREND15    = @P_DC_EXTREND15,
163              DC_EXTREND16    = @P_DC_EXTREND16,
164              DC_EXTREND17    = @P_DC_EXTREND17,
165              DC_EXTREND18    = @P_DC_EXTREND18,
166              DC_EXTREND19    = @P_DC_EXTREND19,
167              DC_EXTREND20    = @P_DC_EXTREND20,
168              DC_INT01        = @P_DC_INT01,
169              DC_INT02        = @P_DC_INT02,
170              DC_INT03        = @P_DC_INT03,
171              DC_INT04        = @P_DC_INT04,
172              DC_INT05        = @P_DC_INT05
173             where DI_ID = @P_DI_ID
174        
175/**//*        
176        if @@error<>0
177        begin
178            goto error
179        end
180*/
181        if @@rowcount=0
182            goto nofound
183      end
184
185     if @btype =3 
186    begin
187        delete from DWE_INFO 
188        where DI_ID = @P_DI_ID
189        
190/**//*        
191        if @@error<>0
192        begin
193            goto error
194        end
195*/
196        if @@rowcount=0
197            goto nofound
198      end
199
200    if @btype =4 --修改副标题
201    begin
202        update DWE_INFO  set     DC_SUBTITLE     = @P_DC_SUBTITLE      where DI_ID = @P_DI_ID
203        
204/**//*        
205        if @@error<>0
206        begin
207            goto error
208        end
209*/
210        if @@rowcount=0
211            goto nofound
212    end
213
214    if @btype =5  --功能设置
215    begin
216        update DWE_INFO  set  
217         DC_SUBTITLE     = @P_DC_SUBTITLE,
218         DC_ISSHOW       = @P_DC_ISSHOW,
219              DC_ISTOP        = @P_DC_ISTOP,
220              DC_ISHOT        = @P_DC_ISHOT,
221              DC_ISRECOMMEND  = @P_DC_ISRECOMMEND,
222              DC_ISFRONTSHOW  = @P_DC_ISFRONTSHOW,
223              DC_ISESSENCE    = @P_DC_ISESSENCE
224             where DI_ID = @P_DI_ID
225/**//*        
226        if @@error<>0
227        begin
228            goto error
229        end
230*/
231        if @@rowcount=0
232            goto nofound
233      end
234
235    if @btype =6  --帖子栏目的移植
236    begin
237        update DWE_INFO  set
238         DC_ID           = @P_DC_ID
239         where DI_ID = @P_DI_ID
240        
241/**//*        
242        if @@error<>0
243        begin
244            goto error
245        end
246*/
247        if @@rowcount=0
248            goto nofound
249      end
250
251    if @btype =9  --帖子给予金狐币
252    begin
253        update DWE_INFO  set
254         DC_EXTREND12    = 1
255         where DI_ID = @P_DI_ID
256        
257/**//*        
258        if @@error<>0
259        begin
260            goto error
261        end
262*/
263        if @@rowcount=0
264            goto nofound
265      end
266
267    if @btype =10  --地区置顶、首页显示
268    begin
269         update DWE_INFO  set
270            dc_subtitle=@P_dc_subtitle,
271            DC_ISSHOW=@P_DC_ISSHOW,
272              DC_INT02=@P_DC_INT02,
273              DC_INT03=@P_DC_INT03
274             where DI_ID = @P_DI_ID
275        
276/**//*        
277        if @@error<>0
278        begin
279            goto error
280        end
281*/
282        if @@rowcount=0
283            goto nofound
284
285      end     
286
287    if @tran_flag=1
288    begin    
289        commit  tran  t1        
290    end    
291--print  @bresult    
292end
293other:
294    return
295error:
296    if @tran_flag=1
297    begin
298        rollback  tran  t1        
299        set @tran_flag=0
300    end    
301
302    set @bResult = 8
303    --print 'err'
304    ---print  @bresult
305    return
306nofound:
307    set @bResult = 6
308    if @tran_flag=1
309    begin
310        commit tran t1
311        set @tran_flag=0
312    end
313    --print 'no'
314    ---print  @bresult
315    return
316GO
317