SQL Server Note [vaynexiao]
生活随笔
收集整理的這篇文章主要介紹了
SQL Server Note [vaynexiao]
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
declare @ln varchar(300),@mn varchar(200),@fn varchar(200)
declare @ln_n int,@mn_n int,@fn_n int
set @ln='李王張劉陳楊黃趙周吳徐孫朱馬胡郭林何高梁鄭羅宋謝唐韓曹許鄧蕭馮曾程蔡彭潘袁于崔任陸廖姚方金邱夏譚韋賈鄒石熊孟秦閻薛侯雷白龍段郝孔邵康賀嚴尹錢施牛'
set @mn='德紹宗邦裕傅家積善昌世貽維孝友繼緒定呈祥大正啟仕執必定仲元魁家生先澤遠永盛在人為任伐風樹秀文逸昌文涵亮涵浩波浩博初浩歌浩廣昊景哲永昌子昂智宇智然'
set @fn='123456789'
set @ln_n=len(@ln)
set @mn_n=len(@mn)
set @fn_n=len(@fn)
declare @tmp varchar(1000),@i int
set @i=100
while @i<100000001
beginset @tmp = cast(substring(@ln, cast(rand() * @ln_n as int), 1) as varchar)set @tmp = @tmp + cast(substring(@mn,cast(rand() * @mn_n as int), 1) as varchar)set @tmp = @tmp + cast(substring(@fn,cast(rand() * @fn_n as int), 1) as varchar)insert into student2 (sno, sname, pwd, salary) values ('2005' + @i, @tmp, '123456', '5000.00')set @i = @i + 1
endcreate table student2 (sno int ,sname varchar(200),pwd varchar(200),salary float(2)
)select count(1) from student2drop table student2
create/alter proc/procedure myproc
@id int,
@name varchar(200),
@content varchar(200)
as
begindeclare @birthday datetimeset @birthday='2019-12-12'delete from report_day;insert into report_day(id,name,content,create_date,birthday)values(@id,@name,@content,getdate(),@birthday);select * from report_day
endexec myproc 1, 'kobe', 'mvpppppp'execute/exec
exec pro_real_holiday '2019-02-01','2019-02-28','01'
--不指定參數名稱，則按照該存儲過程聲明時參數的順序來接收參數，
exec pro_real_holiday @date_end='2019-02-28',@org_code='01',@date_start='2019-02-01'
--此時可以打亂聲明順序，它自動根據名字來對應接收參數exec/execute proc_name
drop proc_name# 舉例
ALTER PROCEDURE [dbo].[pro_copy_kaoqin_his_oneday]
AS
BEGIN--聲明需要的變量 @date 當天日期declare @date varchar(50) set @Date = CONVERT(varchar(100),getdate()-1,23) --只針對執行調度時的前一天，調度時間為凌晨--第1大步：考勤明細表 加工至 考勤表, 且關聯考勤班次，算出遲到、早退時間if( 0 = (select day_type from oams_attr_calendar where date_time=@Date) )BEGINinsert into oams_att_kaoqin (username)select username from uend--第2大步：給“應打卡且一整天沒打卡的人”插入該日期的空記錄--3.1 聲明一個游標 保存所有“應打卡且一整天沒打卡的人”的usernamedeclare @username varchar(50) declare user_cur cursor for select * from pcmc_user open user_cur fetch next from user_cur into @username --獲取當前結果集中的字段usernamewhile @@fetch_status=0 begininsert into oams_att_kaoqin values(@username,@date,@date,null,null,null,null)fetch next from user_cur into @username --獲取當前結果集中的字段usernameendclose user_cur deallocate user_cur
END--全勤表 簡化語法案例
create PROCEDURE [dbo].[pro_sheet_month_hz]
@sheet_type VARCHAR(20),
@date_start VARCHAR(20),
@date_end VARCHAR(20),
@org_code VARCHAR(20)
--exec [pro_sheet_month_hz] @date_start='2019-02-01',@date_end='2019-02-28',@org_code='01'
AS
BEGIN--聲明需要的變量 @date 當天日期
declare @date varchar(50)
set @Date = CONVERT(varchar(100),getdate()-1,23) --只針對執行調度時的前一天，調度時間為凌晨--第一大步：移植考勤歷史明細 至 考勤明細表
insert into kaoqin_checkinout_his_copy
select right('00000'+u.BADGENUMBER,5) as BADGENUMBER, c.CHECKTIME, c.Memoinfo, c.sn
from [10.22.50.232].[ZKT_DB].dbo.Checkinout c
left join [10.22.50.232].[ZKT_DB].dbo.USERINFO u on u.userid=c.userid----日常加班天數create table #richang_temp(deptid varchar(50) NULL ,dname varchar(50) NULL)insert into #richang_tempselect z.deptid, sum(z.overtime) min, sum(z.overtime)/210 dayfrom oams_att_kaoqinwhere z.kq_bdate between @date_start and @date_end----請休假天數create table #jia_temp(deptid varchar(50) NULL)insert into #jia_temp select --所有臨時表進行關聯查詢 select *from #richang_temp rtleft join #jia_temp jia on jia.deptid=d.deptid--把所有臨時表關聯起來查詢if(@sheet_type is not null and @sheet_type='tech_day')beginselect * from pcmc_user pendELSE if(@sheet_type is not null and @sheet_type='zhineng')BEGINselect * from pcmc_user pendelsebegin select * from pcmc_user pendprint '第一大步：移植考勤歷史明細 至 考勤明細表'--第四大步：給“應打卡且一整天沒打卡的人”插入該日期的空記錄
--3.1 聲明一個游標 保存所有“應打卡且一整天沒打卡的人”的username
declare @username varchar(50)
declare user_cur cursor for select a.username from ( --所有需要打卡的人 select usernamefrom pcmc_user pleft join attend_type_shift t on t.attend_shift_code=p.attend_shiftwhere t.attend_time is not null and p.disable='0') as aleft join ( --當天所有打過卡的人select p.username as busernamefrom oams_att_kaoqin kleft join pcmc_user p on p.username = k.usernamewhere CONVERT(varchar(100),kq_bdate,23) =@Date ) as b on a.username = b.busernamewhere b.busername is null
open user_cur
fetch next from user_cur into @username --獲取當前結果集中的字段username
while @@fetch_status=0
begin
insert into oams_att_kaoqin values(@username,@date,@date,null,null,null,null)
print @username
fetch next from user_cur into @username --獲取當前結果集中的字段username
end
close user_cur
deallocate user_curDROP TABLE #richang_tempDROP TABLE #jia_tempEND
總結
以上是生活随笔為你收集整理的SQL Server Note [vaynexiao]的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： unity3D 鼠标点击游戏物体碰撞 （
- 下一篇： anaconda的默认位置修改pkgs以