本文记录sqlserver 创建存储过程及基本语法的讲解,附上作者实现功能的实例
背景:作者的task是需要两个数据库之间进行数据迁移,本可以用java程序来实现,后面觉得可以学习下存储过程,故采用这种方式
实例:上代码
if(exists(select * from sysobjects where name='sp_data_move' ))
drop proc sp_data_move
go
--声明存储过程
create procedure sp_data_move
@staffChannelId varchar(36),
@agencyChannelId varchar(36),
@medicalChanneId varchar(36)
as
--声明变量
declare @lms_id varchar(50), @lms_realname varchar(50), @lms_phone varchar(50), @lms_email varchar(50), @lms_work_no varchar(50),
@lms_open_id varchar(50), @lms_province varchar(50), @lms_city varchar(50), @lms_company varchar(50), @lms_job_title varchar(50),
@lms_department varchar(50), @lms_user_type int, @lms_dist_name varchar(50), @lms_create_time date, @lms_update_time date,
@lms_privacy_flag int, @lms_privacy_time date, @lms_promotion_flag int, @lms_promotion_time date
declare @app_id varchar(50), @form_id varchar(50), @form_name varchar(50), @form_title varchar(50), @social_campaign_id varchar(50),
@channel_id varchar(50), @form_url varchar(50), @privacy_flag varchar(50), @opt_in_type varchar(50), @is_send_sfdc int
--定义游标
declare lms_data_cursor cursor for (select su.id, su.realname, su.phone, su.email, su.work_no, lu.open_id, lu.province, lu.city, lu.company, lu.job_title,
lu.department, lu.user_type, lu.dist_name, lu.create_time, lu.update_time, luc.privacy_flag, luc.privacy_time, luc.promotion_flag,
luc.promotion_time from lms_sys_user su, lms_user lu, lms_user_consent luc where su.id = lu.sys_user_id and su.id = luc.sys_user_id)
--打开游标
open lms_data_cursor
--开始循环游标
fetch next from lms_data_cursor into @lms_id, @lms_realname, @lms_phone, @lms_email, @lms_work_no, @lms_open_id, @lms_province, @lms_city, @lms_company,
@lms_job_title, @lms_department, @lms_user_type, @lms_dist_name, @lms_create_time, @lms_update_time, @lms_privacy_flag, @lms_privacy_time,
@lms_promotion_flag, @lms_promotion_time
while @@fetch_status = 0
begin
--编写逻辑语句
--判断此用户存不存在wep中wc_weixin_user,如不在则添加openid
declare @num int, @weixinsql nvarchar(max)
set @weixinsql = 'select @a=count(*) from wc_weixin_user where open_id =@b'
exec sp_executesql @weixinsql, N'@b varchar(50),@a int output',@b=@lms_open_id, @a=@num output
if @num = 0 begin
insert into wc_weixin_user(open_id, app_id, subscribe) values(@lms_open_id, '', 1)
end
--根据usertype不同身份对应不同表单下的渠道id
declare @channel_value varchar(50)
select @channel_value =
case
when @lms_user_type = 0 then @staffChannelId
when @lms_user_type = 1 then @agencyChannelId
when @lms_user_type = 2 then @medicalChanneId
else ''
end
--根据渠道id查询表单相关信息
declare @queryformsql nvarchar(max)
set @queryformsql = 'select @d=sf.app_id, @e=sf.form_id, @f=sf.form_name, @g=sf.form_title, @h=sf.social_campaign_id, @i=sc.channel_id, @j=sc.form_url
from smf_channel sc, smf_form sf where sc.form_id = sf.form_id and sc.channel_id = @k'
exec sp_executesql @queryformsql, N'@k varchar(50), @d varchar(50) output, @e varchar(50) output, @f varchar(50) output, @g varchar(50) output,
@h varchar(50) output, @i varchar(50) output, @j varchar(50) output', @k=@channel_value, @d=@app_id output, @e=@form_id output, @f=@form_name output,
@g=@form_title output,@h=@social_campaign_id output, @i=@channel_id output, @j=@form_url output
--数据做转换
if @lms_privacy_flag = 1 begin
set @privacy_flag = '是'
end
else begin
set @privacy_flag = '否'
end
if @lms_promotion_flag = 1 begin
set @opt_in_type = '是'
end
else begin
set @opt_in_type = '否'
end
if @social_campaign_id is null begin
set @is_send_sfdc = 0
end
else begin
set @is_send_sfdc = 1
end
--插入smf_form_data表数据
insert into smf_form_data(id, form_id, form_name, form_url, form_title, social_campaign_id, channel_id, open_id, submit_time, update_time
, is_send_sfdc, is_sync_leads, name, phone, email, province, city, company_name, job_title, depart, employee_number, agency_name, privacy_flag
, privacy_time, opt_in_type, opt_in_time) values (@lms_id, @form_id, @form_name, @form_url, @form_title, @social_campaign_id, @channel_id, @lms_open_id
, @lms_create_time, @lms_update_time, @is_send_sfdc, 0, @lms_realname, @lms_phone, @lms_email, @lms_province, @lms_city, @lms_company, @lms_job_title
, @lms_department, @lms_work_no, @lms_dist_name, @privacy_flag, @lms_privacy_time, @opt_in_type, @lms_promotion_time)
--判断此用户在smf_user_profile表中是否有基本信息,没有添加,有的话修改
declare @count int, @profilesql nvarchar(max)
set @profilesql = 'select @x=count(*) from smf_user_profile where open_id = @y;'
exec sp_executesql @profilesql, N'@y varchar(50),@x int output', @y=@lms_open_id,@x=@count output
if @count = 0 begin
insert into smf_user_profile(open_id, app_id, name, phone, email, province, city, company_name, job_title, depart, employee_number, agency_name)
values(@lms_open_id, @app_id, @lms_realname, @lms_phone, @lms_email, @lms_province, @lms_city, @lms_company, @lms_job_title, @lms_department,
@lms_work_no, @lms_dist_name)
end
fetch next from lms_data_cursor into @lms_id, @lms_realname, @lms_phone, @lms_email, @lms_work_no, @lms_open_id, @lms_province, @lms_city, @lms_company,
@lms_job_title, @lms_department, @lms_user_type, @lms_dist_name, @lms_create_time, @lms_update_time, @lms_privacy_flag, @lms_privacy_time,
@lms_promotion_flag, @lms_promotion_time
end
close lms_data_cursor
deallocate lms_data_cursor
下面作者会根据代码实现来讲解一下使用到的基本语法,可能不全,只是用到多少讲多少
1.if(exists(select * from sysobjects where name='sp_data_move' ))
drop proc sp_data_move
go
和建表语句一样,先检查后创建
2.create procedure sp_data_move
@staffChannelId varchar(36),
@agencyChannelId varchar(36),
@medicalChanneId varchar(36)
as
创建存储过程语法,注意:参数必须使用@开头,后面跟上类型和长度
3.declare @lms_id varchar(50), @lms_realname varchar(50), @lms_phone varchar(50), @lms_email varchar(50), @lms_work_no varchar(50),
@lms_open_id varchar(50), @lms_province varchar(50), @lms_city varchar(50), @lms_company varchar(50), @lms_job_title varchar(50),
@lms_department varchar(50), @lms_user_type int, @lms_dist_name varchar(50), @lms_create_time date, @lms_update_time date,
@lms_privacy_flag int, @lms_privacy_time date, @lms_promotion_flag int, @lms_promotion_time date
变量的声明,格式和参数一致,可用declare 声明多个参数,逗号隔开,也可一个一个声明
如:
declare @lms_id varchar(50)
declare @lms_realname varchar(50)
声明变量也可在后续用到时再声明 如:
declare @count int, @profilesql nvarchar(max)
set @profilesql = 'select @x=count(*) from smf_user_profile where open_id = @y;'
此处需注意,声明sql时类型必须使用nvarchar类型
4.使用游标循环,由于代码比较繁琐,字段较多,下面改动简单例子进行展示
--定义游标
declare lms_data_cursor cursor for (select a,b,c from tableA)
--打开游标
open lms_data_cursor
--开始循环游标
fetch next from lms_data_cursor into @a, @b, @c
while @@fetch_status = 0
begin
--编写逻辑语句
fetch next from lms_data_cursor into @a, @b, @c
end
close lms_data_cursor
deallocate lms_data_cursor
5.执行动态sql,传参和反参
declare @num int, @weixinsql nvarchar(max)
set @weixinsql = 'select @a=count(*) from wc_weixin_user where open_id =@b'
exec sp_executesql @weixinsql, N'@b varchar(50),@a int output',@b=@lms_open_id, @a=@num output
首先声明出参和入参的变量名,然后动态sql中替换,使用临时变量
使用exec sp_executesql @weixinsql执行sql
N'@b varchar(50),@a int output'定义临时变量为入参还是出餐,默认为入参故@b不需指定
@b=@lms_open_id, @a=@num output将临时变量赋值声明的变量,后续逻辑即可使用
6.流程控制语法
本文中使用了if语句流程,很简单,看例子即可,中间也可以加入 else if 条件 begin
if @lms_promotion_flag = 1 begin
set @opt_in_type = '是'
end
else begin
set @opt_in_type = '否'
end
还使用了case when语句流程,实例简单易懂
declare @channel_value varchar(50)
select @channel_value =
case
when @lms_user_type = 0 then @staffChannelId
when @lms_user_type = 1 then @agencyChannelId
when @lms_user_type = 2 then @medicalChanneId
else ''
end
7.静态sql可以直接执行,SQL中拼接参数即可执行
由于作者开发过程中任务需求需要用到存储过程,故查阅资料学习语法,完成功能开发,也是第一次开发sqlserver存储过程,有些复杂的场景逻辑中可能没涉及到,暂时没深入研究,但是本文介绍的应对简单的存储过程开发足够了,希望对各位有帮助!