下面通过一个实际的例子学习SQLServer存储过程。所谓应用而学。
- --定义存储过程
- 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