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

来源：互联网发布：wish加钻产品怎么优化编辑：程序博客网时间：2024/05/16 05:46

--定义存储过程
CREATE PROCEDURE xxxxxxxx_p
(
--传递参数
@ym char(6)
)
As
--定义变量，@xx表示局部变量，@@xx表示全局变量。定义多个变量用","号分割
declare @ym_ln char(6)
declare @cpcode char(10),
@cpname char(50),
@swcode char(10),
@swname char(50),
@czgscode char(10),
@czgscode_ char(10),
@czgsname char(50),
@qylx char(2),
@qyxz char(30)
declare @tdcode char(10),
@sb_amt numeric,
@sb_ln_amt numeric,
@sh_amt numeric,
@sh_ln_amt numeric,
@ts_amt numeric,
@ts_ln_amt numeric
declare @ybmy numeric(12,6),
@jljg numeric(12,6),
@other numeric(12,6),
@ybmy_ln numeric(12,6),
@jljg_ln numeric(12,6),
@other_ln numeric(12,6)
declare @rowcount int
--删除表中现有符合ym=@ym的数据
delete from cs_xxxxxxxx where ym=@ym
--给变量赋初值,用到了cast，substring函数。cast用于类型转换，substring用户截取字符串
set @ym_ln=cast((substring(@ym,1,4)-1) as char(4)) +substring(@ym,5,2)
--声明一个游标
declare cur_xxxx cursor for
select cpcode.code as cpcode ,cpcode.name as cpname ,cpcode.swcode as swcode,swcode.name
as swname,cs_swcode_czgs.czgs as czgscode,cpcode.qylx as qylx from cpcode
left join cs_swcode_czgs on cpcode.swcode=cs_swcode_czgs.swcode
left join swcode on cpcode.swcode=swcode.code where cpcode.swcode<>''
--打来游标
open cur_xxxx
--取游标中第一行记录并且写入变量。
fetch next from cur_xxxx
into @cpcode,@cpname,@swcode,@swname,@czgscode,@qylx
--当@@fetch_status = 0即取出了有效行时处理，用到了while语句，结构while xx begin xxx end
while @@fetch_status = 0
begin
--用select语句给变量赋值
select @czgscode_=czgs from cs_cpcode_czgs where cpcode=@cpcode
--if语句，完整结构if xx begin xxx end
if @czgscode_ is not null
set @czgscode=@czgscode_
select @czgsname=name from cs_czgs where code=@czgscode
--if else结构，完整结构 if xx begin xxx else xxxx end
if @qylx='11'
set @qyxz='内资企业'
else
set @qyxz='外商投资企业'
--用select语句给变量赋值，用到了isnull函数。
select @sb_amt=isnull(sum(mdtse),0) from mdtsb where sb_ym=@ym and cpcode=@cpcode
select @sb_ln_amt=isnull(sum(mdtse),0) from mdtsb where sb_ym=@ym_ln and cpcode=@cpcode
--
--省略n行类似赋值语句
--
--goto语句,跳转到insertmodule
goto insertmodule
--
--省略n行同类处理语句
--
insertmodule:
--用is null表达式判断是否为null
if @sb_amt is null set @sb_amt =0
if @sb_ln_amt is null set @sb_ln_amt =0
if @sh_amt is null set @sh_amt =0
if @sh_ln_amt is null set @sh_ln_amt =0
if @ts_amt is null set @ts_amt =0
if @ts_ln_amt is null set @ts_ln_amt =0
--插入一般贸易
select @ybmy=zb from cs_scqybl where tdcode='一般贸易' and ym=@ym and cpcode=@cpcode
select @ybmy_ln=zb from cs_scqybl where tdcode='一般贸易' and ym=@ym_ln and cpcode=@cpcode
if @ybmy is null
begin
--在存储过程中执行存储过程
exec xxxbl @cpcode,@ym
end
if @ybmy_ln is null
begin
exec xxxxbl @cpcode,@ym_ln
end
select @jljg=zb from cs_scqybl where tdcode='进料加工' and ym=@ym and cpcode=@cpcode
select @jljg_ln=zb from cs_scqybl where tdcode='进料加工' and ym=@ym_ln and cpcode=@cpcode
select @other=zb from cs_scqybl where tdcode='其他' and ym=@ym and cpcode=@cpcode
select @other_ln=zb from cs_scqybl where tdcode='其他' and ym=@ym_ln and cpcode=@cpcode
--把上面各个步骤运算得到的值insert进表中cs_xxxxxxxx
insert into cs_xxxxxxxx (xx,xxx,xxxx) values(vv,vvv,vvvv )
--取取游标中下一行记录并写入变量
fetch next from cur_xxxx into @cpcode,@cpname,@swcode,@swname,@czgscode,@qylx
--结束while循环
end
--关闭游标
close cur_xxxx
--删除游标
deallocate cur_xxxx
GO

0 0