代码
--###################################################################################
/* 缩写:
DDL(Database Definition Language): 数据库定义语言
DML(Database Manipulation Language): 数据库操作语言
DCL(Database Control Language): 数据库控制语言
DTM(Database Trasaction Management): 数据库事物管理
知识概要:
|---1.查询Select
|
|---2.数据库定义语言DDL: 对表,视图等的操作, 包括create,drop,alter,rename,truncate
|
数据库操作--|---3.数据库操作语言DML: 对记录进行的操作, 包括insert,delete,update
|
|---2.数据库控制语言DCL: 对访问权限等的操作, 包括grant,revoke
|
|---2.数据库事物管理DTM: 对事物的操作, 包括commit,rollback,savepoint
事物的是由DML(insert,delete,update)开启的;
而引起事物的提交或结束原因有:
1.DTM操作: commit,rollback,savepoint
2.系统崩溃宕机: 隐式rollback
3.正常: 隐式commit
4.DDL和DCL操作: DDL(create,drop,alter,rename,truncate)
DCL(grant,revoke)
注意MS-Sql的特殊处:
MS-Sql中的事物: 自动事物(commit)和手动事物(begin transaction).
在Sql中DML(insert,delete,update)会引起自动事物commit, 而Oracle中不会
MS-Sql的参数: 只能返回0或非0(如: 1,2)数字
MS-Sql的存储过程: 一定会有返回值(return value), 默认返回0(成功). 在程序获取改值, 需要创建return参数对象(添加到参数列表), 并设定其方向.
MSSqlServer的登录过程分两种:
1. Windows集成验证: windows用户名和口令 -> SqlServer登录列表 -> 映射到用户列表 -> 登录成功
2. SqlServer验证: Sql用户名和口令 -> 映射到用户列表 -> 登录成功
两种登录方式的连接串:
string connectionStr = "data source=.;database=Test;user id=sa;password=sa";
string connectiongStr ="data source=.\sqlexpress;database=Test;integrated security=true";
数据库设计原则:
1. 数据库设计指导原则(关系数据库中的数据通过表来体现): 先确定表后确定业务字段.
每个业务对象在数据库中对应一张表(若业务对象复杂则可对应多张表), 业务对象间每有一个关系也要对应一张表.
注意: 业务字段需要跟项目结合, 例如: 学生的健康情况可以用一个字段(优、良等)表示, 但针对健康普查, 学生的健康情况需进一步划分为身高、体重、血压等
如: 学校中的学生对象: 学院表(学院ID, 专业ID); 专业表: 专业表(专业ID, 专业名);学生表(学生ID, 学院ID,专业ID)
2. 数据库设三大计原则:
a. 完整性: 设计方案能够保存项目中的各种信息(要全)
b. 低冗余: 通过主键保证记录的不重复、通过表间关系减少冗余字段
c. 尽可能满足3范式(NormalForm): 1NF: 1个字段只能包含一个业务信息片(即项目中的业务字段表示的信息不可再分)
2NF: 能找到1个或多个字段的组合, 用来唯一的确定表中的记录(即必须有主键).
3NF: 主键唯一且直接确定表中的其他字段(即无传递依赖, 如: 教师id, 办公室id, 办公室电话关系中存在传递依赖)
注意事项: 尽可能不用业务字段做主键, 通常的手段为自增列当主键, 并且末尾添加默认时间字段(getdate()).
尽量采用SQL92代码, 保证可移植性. 如: 在Sql2000中, top函数只能跟常量(Sql2005可跟变量). 通用的解法为拼Query串, 用exec(query串)取结果
备注:
Sql中使用+拼接字符串, Oracle中使用||拼接字符串.
C#数据类型:
整数: sbyte,byte,short,ushort,int,uint,long,ulong
实数: float,double,decimal
字符: char,string
布尔: boolean
日期: datetime
对象: object
全局唯一标识: GUID
Sql数据类型:
整数: bit(0,1),tinyint(8),smallint(16),int(32)，bigint(64)
实数: float,real,numeric
字符: char(8000),nchar(4000),varchar(8000),nvarchar(4000),ntext
日期: smalldatetime(1900,1,1-2079,6,6),datetime(1753,1,1-9999,12,31)
货比: money
二进制: binary
图像: image
标识: uniqueidentity
*/
--###################################################################################
--创建数据库:
ifexists(select*from sysdatabases where[name]='TestStudent')
dropdatabase TestStudent
go
createdatabase TestStudent
on
(
name ='TestStudent_DB', --数据库逻辑名
filename ='D:\WorkCode\DB\TestStudent.mdf',
size =3,
filegrowth =10,
maxsize =100
)
log
on
(
name ='TestStudent_Log',
filename ='D:\WorkCode\DB\TestStudent.log',
size =3,
filegrowth =10,
maxsize =100
)
go
--###################################################################################
use TestStudent
go
--创建表, 约束类型: 主键、外键、唯一、check约束、非空约束
ifexists( select*from sysobjects where[name]='T_Student'and[type]='U')
droptable T_Student
go
createtable T_Student
(
Sno intidentity(100,1) primarykey, --可以使用scope_identity获得刚生成的id
Sname nvarchar(50) notnull,
Sgender nchar(1),
Sage tinyintcheck(Sage >=20and Sage <=30),
home nvarchar(100) default('北京'),
idcard nvarchar(18) unique
)
go
ifexists( select*from sysobjects where[name]='T_Score'and[type]='U')
droptable T_Score
go
createtable T_Score
(
id intprimarykey,
Sno intidentity(100,1) foreignkeyreferences T_Student(Sno),
Score tinyint
)
go
--修改表结构
altertable T_Student
add Education nchar(3)
go
altertable T_Student
dropcolumn Education
go
--修改表约束
altertable T_Student
addconstraint PK_Sno primarykey(Sno),
constraint CK_gender check(gender in('男','女')),
constraint DF_home default('北京') for home,
constraint UQ_idcard unique(idcard)
go
altertable T_Score
addconstraint FK_Sno foreignkeyreferences T_Student(Sno)
go
--创建联合主键
altertable T_SC withnocheck
addconstraint[PK_T_SC]primarykeynonclustered(
studentId,
courseId
)
go
--###################################################################################
--新增(插入)数据
insertinto T_Student(Sname,Sgender,Sage) values('张三','男',23)
go
insertinto T_Student(Sname,Sgender,Sage)
select'李四','男',25union
select'王五','女',26union
select'赵六','男',28
go
--删除数据
truncatetable T_Student --只删除表的数据
deletefrom T_Student where sgender ='男'
--修改数据
update T_Student set sgender ='女'where sgender='男'
--###################################################################################
--查询数据
select*from T_Student where sgender ='女'
selecttop3*from T_Student --选择前三项, top 3是用来筛选记录的, 所以得紧跟select, 不用考虑字段顺序
selecttop40percent*from T_Student --选择前百分之几的记录
select sname as'姓名', '年龄'= sage from T_Student --起别名的两种方式as和=
select*from T_Student where sage >=20and age <=30
select*from T_Student where sage between20and30
select*from T_Student where sage isnull
select sname into T_Stu from T_StuInfo --用T_StuInfo中的sname字段创建新表
--模糊查询呢: 通配符: %(0-任意字符), _(1个字符),[abc](选择含a或b或c),[^abc](选择不含a或b或c)
select*from T_Student where sname like'张%'
select*from T_Student where sname like'王_'
select*from T_Student where sname like'[张李]%'
select*from T_Student where sname like'[^张李]%'
--###################################################################################
--排序: order by子句的结果将额外生成一个新表(2字段: 原位置索引字段和排好序的字段)
select*from T_Student orderby Sage desc--默认是升序asc
--###################################################################################
--聚合函数: 若有聚合函数, 即使不写group by 子句, 也会默认将表分为一个大组
selectsum([sid]) from T_StuScore
selectcount([sid]) from T_StuScore --count(*)表示记录数, 而count(字段)忽略掉null值
selectavg([sid]) from T_StuScore
selectmax([sid]) from T_StuScore
selectmin([sid]) from T_StuScore
selectdistinct([sid]) from T_StuScore
--###################################################################################
--分组函数, where用于对记录的筛选, having用于对组的筛选
select gender,Counter =count(*) from T_Stuinfo groupby gender
select gender,Counter =count(*) from T_Stuinfo groupby gender havingcount(*) >=2
--###################################################################################
--表连接: 笛卡尔积(m*n条记录), 内连接, 外连接(左外连接、右外连接、全外连接)
--笛卡尔积
select sname,[sid],cid,score
from T_StuInfo s crossjoin T_StuScore c on s.[sid]= c.[sid]
--内连接: 先从m和n中选择, 然后再连接
select sname,[sid],cid,score
from T_StuInfo s innerjoin T_StuScore c on s.[sid]= c.[sid]
--左外连接(左连接): 内连接 + 左表剩余记录(右表记录置null)
select sname,[sid],cid,score
from T_StuInfo s leftjoin T_StuScore c on s.[sid]= c.[sid]
--右外连接(右连接): 内连接 + 右表剩余记录(左表记录置null)
select sname,[sid],cid,score
from T_StuInfo s rightjoin T_StuScore c on s.[sid]= c.[sid]
--全外连接(全连接): 内连接 + 左表剩余记录(右表记录置null) + 右表剩余记录(左表记录置null)
select sname,[sid],cid,score
from T_StuInfo s fullouterjoin T_StuScore c on s.[sid]= c.[sid]
--###################################################################################
--函数: 字符串函数、日期函数、数学函数、系统函数
--字符串函数
printcharindex('1','ab1cd') --Sql中下表从1开始, 类似于C#中indexof、lastindexof, 返回包含'1'的下表位置
printlen('abcd') --C#中的length
printlower('ABCD') --ToLower(), ToUpper()
printupper('abcd') --ToUpper()
printltrim(' abcd') --LTrim()
printrtrim('abcd ') --RTrim()
printrtrim(ltrim(' abcd ')) --Trim()
printright('abcd',2) --从右截取, C#中的SubString(length-3,2)截取下表从0开始的2个
printleft('abcd',2) --SubString(0,2)
printreplace('abcdef','cd','1234') --Replace(), 用1234替换cd
update[card]set[passWord]=Replace(Replace([PassWord] ,'O','0'),'i','1') from T_UserInfo
printstuff('abcdef',2,3,'#') --填充替换, 从第2个开始的3个字符替换成#
printcast('2010-11-08'asdatetime) --数据类型转换
printconvert(datetime,'2010-11-08') --数据类型转换
printstr(67) --数字变字符串
printnewid() --新uniqueidentifier, 它将会为记录临时添加一列, 且内容是随机的
printgetdate() --获得当前日期
--日期函数
printgetdate() --DateTime.Now
printdateadd(yyyy,10,'2010-1-2') --增加10年
printdateadd(mm,2,getdate()) --增加月, 其他可以查sql联机丛书
printdatediff(yyyy,'1985-12-13','2010-10-10') --时间差距
printdatediff(dd,'1985-12-13','2010-10-10') --时间差距
printdatename(mm,'2010-10-01') +'2'--返回月份+'2'
printdatepart(mm,'2010-10-01') +'2'--日期10+2，结果为12
--数学函数
printabs(-100) --求绝对值, Abs()
printceiling(10.05) --取整数, 如果有小数则进1
printfloor(10.05) --取整数, 忽略小数
printround(10.567,2) --四舍五入
printpower(10,2) --求幂运算
printsqrt(108) --求平方根
printrand(10) --只能选择0-1之间的数, Random.Ran(0,1)
printrand(10)*10--随机0-10之间的数
printsign(30) --只返回±1
--系统函数
print'abcd'+convert(nvarchar(10),5) --ToString()
print'abcd'+cast(5asnvarchar(10)) --同上
printdatalength('1+1=2') --返回表达式的字节数
printcurrent_user--返回当前登录的角色
printhost_name() --返回当前计算机名
printsystem_user--返回当前用户id
printuser_name() --给定用户id返回其角色名
printisnull(filedname,0) --替换null为0
raiserror('抛出异常',16,1) --抛出异常, 1-15被系统占用, 对应C#中的throw
select*from sysobjects whereobjectproperty(id,N'IsUserTable') =1--判断是否用户表(y=1,n=0), N表示后边的串为unicode字符串.
select*from sysobjects where type='U'--等价于上一行
select databasepropertyex('Northwind','IsBrokerEnabled') --查询该库是否开启缓存技术中的通知机制, 1为开启, 0为关闭
alterdatabase northwind set enable_broker --开启数据库中, 缓存技术中的通知机制
--注意以下三个函数的用法 eg: 结果集(1,5,11,17,19,25)
select row_number() over(orderby[sid]) from T_StuInfo --1,2,3,4,5,6
select rank() over(orderby[sid]) from T_StuInfo --1,1,1,4,5,6
select dense_rank() over(orderby[sid]) from T_StuInfo --1,1,1,2,3,4
select ntile(2) over(orderby[sid]) from T_StuInfo --1,5 11,17 19,25
select row_number() over(orderby[sid]) as sequence, sname, age, (case gender when'0'then'男'else'女'end) gender
from T_StuInfo s leftjoin T_StuScore c on s.sid = c.sid
go
--###################################################################################
--范式: 1NF: 原子性, 2NF: 单主键, 3NF: 去除传递依赖
--E-R模型(表间关系): 1对1： 任何一个表添加外键; 1对多: 在多的一方添加外键; 多对多: 需要第三个表, 添加前两表的外键
--###################################################################################
--变量
--系统变量:
select*from T_StuInfo
print@@identity; --获得结果集最大标识值
print@@error; --有错误, 返回大于0的数; 无错误返回0
print@@rowcount; --返回结果集的行数
--自定义变量
declare@agetinyint
declare@age2tinyint
declare@namenvarchar(20)
declare@name2nvarchar(20)
set@age=15--一次只能对一个量变赋值
select@name='张三',@name2='李四'--一次性给多个变量赋值
select@age2=max(age) from T_StuInfo --可以用在查询语句中
print@age
print@age2
print@name
print@name2
--###################################################################################
--条件表达式
declare@ageint
set@age=1
if (@age<20)
begin
set@age=@age+1
end
else
begin
set@age=@age-1
end
--循环
declare@indexint
declare@sumint
set@index=1
set@sum=0
while (@index<11)
begin
set@sum=@sum+@index
set@index=@index+1
end
print@sum
--批处理Sql语句: 练习---打印三角形, 即成批处理语句+go即可, 只访问一次数据库
declare@rowint
declare@colint
declare@nint--总行数
declare@resultnvarchar(2000)
set@row=0
set@col=0
set@n=10--可以修改n的值
set@result=''
while (@row<@n)
begin
set@col=0--复位
set@result=''
while (@col<@n+@row)
begin
if (@col<@n-@row-1)
begin
set@result=@result+''
end
else
begin
set@result=@result+'*'
end
set@col=@col+1
end
print@result
set@row=@row+1
end
go
--case when
--搜索case when(用于一个范围)
select'评语'=casewhen SqlServer>=90then'优秀'
when SqlServer >=80and SqlServer <90then'良'
when SqlServer >=60and SqlServer <80then'及格'
else'不及格'
end
from T_StuInfo
--简单case when(类似swtich, 用于一个定值)
declare@genderbit
set@gender='true'
printcase@genderwhen'true'then'男'else'女'end
--###################################################################################
--事物: 事物的特性ACID(一致性(Consistency)、原子性(Atomicity)、隔离性(Isolation)、持久性(Durability))
declare@errorcountint
set@errorcount=0
begintransaction--if控制事物的提交
begin
update T_StuInfo set age = age +1where gender ='男'
set@errorcount=@@error--@@error无错误返回0, 有错误返回非0的数
update T_StuInfo set age = age -1where cardno ='女'
set@errorcount=@errorcount+@@error
if(@errorcount=0)
begin
commit
end
else
begin
rollback
end
end
begintransaction--异常控制事物提交, raiserror('XXXXX',16,1)用于抛出xxxx的异常
begin
begin try
update T_StuInfo set age = age +1where gender ='男'
update T_StuInfo set age = age -1where cardno ='女'
commit
end try
begin catch
raiserror('性别字段输入了不合适的字符',16,1) --1-15级为系统使用
rollback
end catch
end
--###################################################################################
--索引: 聚集索引(Clustered Index)或称物理所引,非聚集索引(Nonclustered Index)或称逻辑索引,唯一索引(Unique Index),主键索引(PK Index)
--优缺点: 查询快, 但增删改慢.
--何时用: 数据量特别大的情况适合建索引; 经常查找的字段建索引(聚集索引, 此时要求取消主键索引)
--注意事项:
-- 使用索引时, 需要注意查询时的where子句: 若有索引, 先查索引, 之后再根据索引查找原表记录位置, 拼接结果; 若无索引, 则不查索引, 直接拼结果.
-- 如此, 针对索引字段, 若从带where的查询结果中去掉前5项(不带where), 则会出现错误(真分页有类似情况).
-- 解决方案: 在子查询中也添加排序字段的永真where条件, 如: where sortfield > -1
selecttop20 sortfiled, filed1 from T_S where sortfiled notin (selecttop5 sortfiled from T_S where sortfiled >-1)
createclusteredindex idx_age on T_StuInfo(age) --创建聚集索引(每表仅一份), 将对记录排序, 而且索引将会和表保存在一起(采用二分查找)
createnonclusteredindex idx_age on T_StuInfo(age) --创建非聚集索引(任意多个), 不排序但会创建独立表(含2列: 原表中的位置索引,已排序的字段)
--###################################################################################
--视图: 将会创建一张虚拟表, 且对视图的insert、delete和update操作会修改源数据, 但工作中禁止通过视图修改源数据.
-- 视图就是个Sql语句, 也就是Select结果的虚表, 视图相当于虚表的别名而已.
-- 注意: 视图的别名的使用.
--优点: 代码易读; 经过预编译(存储过程也是预编译的), 效率高; 屏蔽了表结构, 比较安全性; 缺点: 增加管理开销
ifexists(select*from sysobjects where[name]='V_SnoName'and[type]='V')
dropview V_SnoName
go
createview V_SnoName
as
select[sid],sname from T_StuInfo
go
select*from V_SnoName
select*from T_StuInfo
insertinto V_SnoName(sname) values('候八')
--###################################################################################
--存储过程(Stored Procedure): sp_help查看SP以及sp参数的信息, sp_helptext查看SP内部代码
ifexists(select*from sysobjects where[name]='P_Triangle'and[type]='P')
dropprocedure P_Triangle
go
createprocedure P_Triangle(
@nint
) with encryption --加密, 不影响编译但将无法查看SP内部代码(sp_helptext)
as--局部变量
declare@rowint
declare@colint
declare@resultnvarchar(2000)
begin
set@row=0
set@col=0
set@result=''
while (@row<@n)
begin
set@col=0--复位
set@result=''
while (@col<@n+@row)
begin
if (@col<@n-@row-1)
begin
set@result=@result+''
end
else
begin
set@result=@result+'*'
end
set@col=@col+1
end
print@result
set@row=@row+1
end
end
go
exec P_Triangle 10
sp_help P_Triangle --查看SP及其参数的信息
sp_helptext P_Triangle --查看SP内部代码
declare@resultint--以下代码证明, SP默认返回值为0
set@result=-1
exec@result= P_Triangle 15
print@result
--存储过程 + 事物 + 输出参数
ifexists(select*from sysobjects where[name]='P_InsertRecord'and[type]='P')
dropprocedure P_InsertRecord
go
createprocedure P_InsertRecord(
@snamenvarchar(20),
@gendernchar(1) ='男', --等号后边是默认值
@agetinyint,
@statusnchar(2),
@birdatedatetime,
@retrunsidint output --用以保存该记录的主键
)
as--局部变量
begintransaction
begin
begin try
insertinto T_StuInfo(sname,gender,age,[status],birdate)
values(@sname,@gender,@age,@status,@birdate)
set@retrunsid=@@identity
commit
return0
end try
begin catch
raiserror('插入数据异常',16,1)
rollback
return1
end catch
end
go
declare@sidint--保存输出参数
declare@returnint--保存返回值
exec P_InsertRecord '测试2','男',35,'毕业','1977-06-07',@sid output
exec@return= P_InsertRecord '测试2','男',35,'毕业','1977-06-07',@sid output --用@return接受SP返回值
print@sid
print@return
--###################################################################################
--触发器: 执行时将自动创建inserted或deleted临时表(update, 同时创建两表), 且均是只读的; 因为无调用痕迹, 系统调试时增加困难
ifexists(select*from sysobjects where[name]='TR_DelStu'and[type]='TR')
droptrigger TR_DelStu
go
createtrigger TR_DelStu --级联删除
on T_StuInfo
instead ofdelete--(for,after,instead of), 注意for和after效果是一样的
as
declare@currentidint
begintransaction
begin
begin try
--set @currentid = (select [sid] from deleted) --insert和update会用到临时表inserted
select@currentid=[sid]from deleted
deletefrom T_StuScore where[sid]=@currentid
deletefrom T_StuInfo where[sid]=@currentid
commit
end try
begin catch
raiserror('删除失败操作异常',16,1)
rollback
end catch
end
deletefrom T_StuInfo where[sid]=3
--###################################################################################
--用户定义函数(User Defined Function): 标量函数、内嵌表值函数、多语句表值函数
--标量函数(Scalar Functions)
ifexists(select*from sysobjects where[name]='GetCountByGender'and[type]='FN')
dropfunction GetCountByGender
go
createfunction GetCountByGender
(
@gendernchar(1) --函数的参数列表
)
returnsint--函数的返回值类型
as
begin
declare@countint--返回值变量
set@count= (
selectcount([sid])
from T_StuInfo
where gender =@gender
)
return@count--执行返回
end
go
select dbo.GetCountByGender('男') as 数量 --调用函数时, 必须加上所有者
--内嵌表值函数(Inline Table-valued Functions)
ifexists(select*from sysobjects where[name]='GetInfoByStatus'and[type]='IF')
dropfunction GetInfoByStatus
go
createfunction GetInfoByStatus
(
@statusnchar(2) --参数列表
)
returnstable--返回值为数据表
as
return
(
select*
from T_StuInfo
where[status]=@status
)
go
select*from dbo.GetInfoByStatus('毕业') --调用函数时, 必须加上所有者
go
--多语句表值函数(Multistatement Table-valued Functions)
ifexists(select*from sysobjects where[name]='GetNameBySegAge'and[type]='TF')
dropfunction GetNameBySegAge
go
createfunction GetNameBySegAge
(
@firstageint, --18岁
@secondageint, --18-30岁
@thirdageint--30岁以上
)
returns@infotabletable--定义返回值变量(table类型), 以及返回值表的字段
(
AgeSegment nvarchar(30),
countnum int
)
as
begin
--局部变量, 用于填充返回值表
declare@currentcountint--当前年龄段的计数
declare@currentdescnvarchar(30) --当前年龄段的描述
set@currentcount= (selectcount([sid]) from T_StuInfo
where age<@firstage)
set@currentdesc='小于(含)-'+Convert(nvarchar(10),@firstage)+'岁'
insertinto@infotable(AgeSegment,countnum) values(@currentdesc,@currentcount)
set@currentcount= (selectcount([sid]) from T_StuInfo
where age>=@firstageand age<@secondage)
set@currentdesc=Convert(nvarchar(10),@firstage)+'岁(含)-'+Convert(nvarchar(10),@secondage)+'岁'
insertinto@infotable(AgeSegment,countnum) values(@currentdesc,@currentcount)
set@currentcount= (selectcount([sid]) from T_StuInfo
where age>=@secondageand age<@thirdage)
set@currentdesc=Convert(nvarchar(10),@secondage)+'岁(含)-'+Convert(nvarchar(10),@thirdage)+'岁'
insertinto@infotable(AgeSegment,countnum) values(@currentdesc,@currentcount)
set@currentcount= (selectcount([sid]) from T_StuInfo
where age>=@thirdage)
set@currentdesc=Convert(nvarchar(10),@thirdage)+'岁(含)-不限'
insertinto@infotable(AgeSegment,countnum) values(@currentdesc,@currentcount)
return--执行已定义的返回值表的返回操作
end
go
select*from dbo.GetNameBySegAge(20,30,40) --调用函数时, 必须加上所有者
--###################################################################################
--游标:
begintransaction MoveUserInfoTrans
begin
declare@errcountint
set@errcount=0
declare MoveUserInfoTwo cursor--声明游标
for
select userid,userpwd from UserInfoTwo
open MoveUserInfoTwo --打开游标，准备开始读取操作
declare@useridnvarchar(20),@userpwdnvarchar(30)
fetchnextfrom MoveUserInfoTwo into@userid,@userpwd--执行读取
while(@@fetch_status=0)
begin
insertinto UserInfoOne(userid,userpwd) values (@userid,@userpwd)
if(@@error!=0) --验证单次操作的是否成功
begin
set@errcount=@errcount+1
break
end
fetchnextfrom MoveUserInfoTwo into@userid,@userpwd--取下一条
end
close MoveUserInfoTwo --完成游标操作，关闭游标
deallocate MoveUserInfoTwo --释放游标
if(@errcount=0) --用if验证事务的操作过程
begin
committransaction MoveUserInfoTrans
print'事务已成功提交！'
end
else
begin
rollbacktransaction MoveUserInfoTrans
print'执行过程出错，事务已回滚！'
end
end
go
//补充个东东, 临时表的AutoDrop脚本, 转自 --- http://blog.csdn.net/xgw2001/article/details/5506796
1.判断一个临时表是否存在
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tempcitys') and type='U')
drop table #tempcitys
注意tempdb后面是两个. 不是一个的
---临时表
if exists(select * from tempdb..sysobjects where name like ‘#tmp1%‘)
drop table #tmp1
或
if exists( select * from tempdb..sysobjects where id=OBJECT_ID('tempdb..#tmp') )
drop table #tmp1
--视图
if exists (select * from sysobjects where id = object_id(N‘[dbo].[ESTMP]‘)
and OBJECTPROPERTY(id, N‘IsView‘) = 1)
drop view ESTMP
判断表是否存在
if exists (select * from sysobjects where id = object_id(N'[dbo].[phone]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[phone]
//Sql server 异常处理中, 抛出系统错误信息, 例如:
三个系统函数: ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE();
View Code
BEGIN CATCH SET @v_ReturnValue = 0 SET @v_ErrorInfo = N'Transaction Error, SpName:[USP_UMS_CreateLogicalPools].Inserted LogicalPool Count:'+str(@v_TotalCount) +char(10) + ERROR_MESSAGE() RAISERROR(@v_ErrorInfo,16,1) ROLLBACK END CATCH
//备注: CASE WHEN时候的类型问题
MySQL 存在这问题, SqlServer 没试
======
使用简单 CASE WHEN 格式时:
如: CASE input_expression WHEN When_expression THEN 1 ELSE 0 END
input_expression 所比较的简单表达式。When_expression 是任意有效的 SQL Server 表达式。Input_expression 和每个 when_expression 的数据类型必须相同，或者是隐性转换。
使用搜索型CASE WHEN 格式时:
如: CASE WHEN Boolean_expression THEN 1 ELSE 0 END
Boolean_expression 是任意有效的布尔表达式。
//语句中执行Identity的问题.
如以下SQL语句:
string sql="insert into users values('admin','admin','男');select @@Identity";
该sql语句拼装在一起执行，等于是先执行插入操作，然后返回所插入的数据的id
这条sql语句直接 用sqlcommand的ExecuteScalar方法执行 会返回所插入的数据的的id号
?
如果是sql server 最好用select SCOPE_IDENTITY()
因为@@identity全局的
同类还有IDENT_CURRENT（‘table’）
IDENT_CURRENT 返回为任何会话和任何作用域中的特定表最后生成的标识值。IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。
@@IDENTITY 返回为当前会话的所有作用域中的任何表最后生成的标识值。
SCOPE_IDENTITY 返回为当前会话和当前作用域中的任何表最后生成的标识值
SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是，SCOPE_IDENTITY 只返回插入到当前作用域中的值；@@IDENTITY 不受限于特定的作用域
有两个表 T1 和 T2，并且在 T1 上定义了 INSERT 触发器。当将某行插入 T1 时，触发器被激发，并在 T2 中插入一行。
该方案演示了两个作用域：在 T1 上的插入，以及在 T2 通过触发器的插入。
假设 T1 和 T2 都有标识列，
@@IDENTITY 和 SCOPE_IDENTITY 将在 T1 上的 INSERT 语句的最后返回不同的值。
@@IDENTITY 将返回在当前会话中的任何作用域内插入的最后一个标识列的值。这是在 T2 中插入的值。
SCOPE_IDENTITY() 将返回在 T1 中插入的 IDENTITY 值。这是在同一个作用域内发生的最后的插入。
如果在任何 INSERT 语句作用于作用域中的标识列之前调用 SCOPE_IDENTITY() 函数，则该函数将返回空值。
//新旧系统, 数据库倒数据的脚本.
操作步骤: Navicat将需要的数据表, 从Oracle导入到Sql Server中, 然后在Sql Server中连接两个库做数据同步和导入操作.
/***** 带导入的数据表 0. 数据表传输 创建数据库(注意数据库字符集) 导入列表: DT_XT_METRICUNIT DT_ZH_PROVIDER DT_XT_PROVIDERQULITY DT_CD_OBJCODE DT_ST_MAINACCOUNT DB_ST_WAREHOUSE DT_CD_OBJPROP ******/ /* --导入单位信息 -- select * from [JnSmsDb_Test].dbo.T_Unit insert into [JnSmsDb_Test].dbo.T_Unit select METRICUNITNAME,'',1,'','' from [OrclData_Test].dbo.DT_XT_METRICUNIT where METRICUNITNAME not in ( select UnitName from [JnSmsDb_Test].dbo.T_Unit ) -- 导入供应商信息 -- select * from [JnSmsDb_Test].dbo.T_Supplier -- select * from [JnSmsDb_Test].dbo.T_SupplierType insert into [JnSmsDb_Test].dbo.T_Supplier select PROVIDERCHINANAME ,'' ,case PROVIDERTYPEID when 0 THEN 1 when 1 then 2 else 3 end ,PROVIDERQULITYNAME ,'' ,'' ,'' from [OrclData_Test].dbo.DT_ZH_PROVIDER pro inner join [OrclData_Test].dbo.DT_XT_PROVIDERQULITY quoli on PRO.PROVIDERQULITYID = QUOLI.PROVIDERQULITYID where PROVIDERCHINANAME not in ( select SupplierName from [JnSmsDb_Test].dbo.T_Supplier ) */ -- 清洗仓库数据, 单位数据不需要清洗 update [OrclData_Test].dbo.DB_ST_WAREHOUSE set WHNAME = WHNAME + '库' where right(WHNAME,1) != '库' select WHID,WHCODE,WHNAME from [OrclData_Test].dbo.DB_ST_WAREHOUSE where WHNAME not in ( select WHName from [JnSmsDb_Test].dbo.T_WareHouseInfo ) -- 导入库存数据: 需要导入物资数据、仓位数据、仓库数据 --游标: begin try begin transaction ImportStorage begin declare @errcount int; set @errcount=0; --声明游标 declare ImportStorageRecord cursor for select wh.WHNAME as WHNAME, isnull(objProp.WZNAME,'') as WZNAME, objCode.OBJNAME as OBJNAME, isnull(objCode.SPECTYPE,'') as SPECTYPE, unit.METRICUNITNAME as METRICUNITNAME, mainAcc.BALQUANTITY as BALQUANTITY, mainAcc.BALPRICE as BALPRICE, mainAcc.BALMONEY as BALMONEY from [OrclData_Test].dbo.DT_ST_MAINACCOUNT mainAcc inner join [OrclData_Test].dbo.DT_CD_OBJCODE objCode on mainAcc.MATERIALID = OBJCODE.OBJID inner join [OrclData_Test].dbo.DB_ST_WAREHOUSE wh on wh.WHID = mainAcc.WHID inner join [OrclData_Test].dbo.DT_XT_METRICUNIT unit on unit.METRICUNITID = objCode.MEASUREUNITID inner join [OrclData_Test].dbo.DT_CD_OBJPROP objProp on objProp.OBJID = objCode.OBJID; --打开游标，准备开始读取操作 open ImportStorageRecord ; declare @warehouseId int, @warehouseName nvarchar(32), @binId int, @binCode nvarchar(32), @mateId int, @mateName nvarchar(32), @mateSpec nvarchar(32), @unitId int, @unitName nvarchar(16), @amount decimal(18,8), @unitPrice decimal(18,8), @totalPrice decimal(18,8); --执行读取 fetch next from ImportStorageRecord into @warehouseName, @binCode, @mateName, @mateSpec, @unitName, @amount, @unitPrice, @totalPrice; while(@@fetch_status=0) begin --判断仓库编号(最后要对0的单独处理) select @warehouseId = case @warehouseName when '专业' then 6 when '材料' then 7 when '汽机库' then 9 when '电气库' then 10 when '继保库' then 11 when '锅炉库' then 12 when '热控库' then 13 when '化学库' then 14 when '除灰库' then 15 when '燃料库' then 16 when '脱硫库' then 17 when '脱硝库' then 18 when '金属库' then 19 when '土建库' then 20 when '信息库' then 21 when '阀门' then 22 when '螺栓' then 23 when '轴承' then 24 when '五金' then 25 when '钢材' then 26 when '建材' then 27 when '油类' then 28 when '燃料' then 29 when '材料' then 30 when '油类' then 31 when '一号汽机库' then 32 when '二号汽机库' then 33 when '运行' then 34 when '液氨' then 35 when '石子' then 36 when '脱硫增效剂' then 37 when '大宗材料' then 38 when '酸' then 39 when '碱' then 40 when '粉煤灰' then 41 when '办公' then 42 when '消防' then 43 when '零购' then 44 else 0 end; --判断仓位 insert into [JnSmsDb_Test].dbo.T_BinInfo(WHNo,Bincode,BinStatus,BinRemarks,BinRev1,BinRev2) values(@warehouseId,@binCode,1,'','',''); select @binId = SCOPE_IDENTITY(); if(@binId <= 0) begin print N'未能正确插入仓位信息!'; set @errcount = @errcount+1; end --物料信息插入 --判断单位(最后要对0的单独处理) select @unitId = case @unitName when '升' then 4 when '吨' then 5 when '千克' then 6 when '盒' then 7 when '头' then 8 when '台' then 9 when '盘' then 10 when '令' then 11 when '平方米' then 12 when '扎' then 13 when '桶' then 14 when '只' then 15 when '立方米' then 16 when '批' then 17 when '组' then 18 when '把' then 19 when '斤' then 20 when '粒' then 21 when '轴' then 22 when '卷' then 23 when '罐' then 24 when '床' then 25 when '件' then 26 when '块' then 27 when '箱' then 28 when '公升' then 29 when '面' then 30 when '包' then 31 when '条' then 32 when '排' then 33 when '辆' then 34 when '捆' then 35 when '袋' then 36 when '架' then 37 when '个' then 38 when '部' then 39 when '圈' then 40 when '片' then 41 when '幅' then 42 when '筒' then 43 when '方' then 44 when '套' then 45 when '副' then 46 when '颗' then 47 when '本' then 48 when '具' then 49 when '价' then 50 when '根' then 51 when '档' then 52 when '双' then 53 when '张' then 54 when '团' then 55 when '支' then 56 when '顶' then 57 when '扇' then 58 when '瓶' then 59 when '米' then 60 when '打' then 61 when '节' then 62 when '屏' then 63 when '尺' then 64 when '提' then 65 when '加仑' then 66 when '公斤' then 67 when '对' then 68 else 0 end; --物料信息插入 insert into [JnSmsDb_Test].dbo.T_MaterialInfo(MatSupperNo,MaterialMTNO,MaterialName,MaterialModel,MaterialSpec,MaterialPrice,MaterialAmount,MaterialUnit,MaterialStatus,MatTotalPrice,MatCreateTime,MaterialRev1,MaterialRev2) values(1,7,@mateName,@mateSpec,'',@unitPrice,@amount,@unitId,1,@totalPrice,'2016-03-01','',''); select @mateId = SCOPE_IDENTITY(); if(@mateId <= 0) begin print N'未能正确插入物料信息!'; set @errcount = @errcount+1; end --插入库存 insert into [JnSmsDb_Test].dbo.T_InventoryInfo(WHNo,BinNo,MateNo,MateAmount,MateUnitPrice,MateSumPrice,InveCreateTime,InveUpdateTime,InveRev1,InveRev2) values(@warehouseId,@binId,@mateId,@amount,@unitPrice,@totalPrice,'2016-03-01','2016-03-01','',''); --插入账务 insert into [JnSmsDb_Test].dbo.T_AccountInfo(WHNo,MateNo,MateTotalAmount,MatePerPrice,MateTotalPrice,AccoCreateTime,AccoUpdateTime,AccoRev1,AccoRev2) values(@warehouseId,@mateId,@amount,@unitPrice,@totalPrice,'2016-03-01','2016-03-01','',''); -- 验证单次操作的是否成功 if(@@error!=0) begin set @errcount = @errcount+1; break end fetch next from ImportStorageRecord into @warehouseName, @binCode, @mateName, @mateSpec, @unitName, @amount, @unitPrice, @totalPrice; -- 取下一条 end --完成游标操作，关闭游标 close ImportStorageRecord; --释放游标 deallocate ImportStorageRecord; --用if验证事务的操作过程 if(@errcount = 0) begin commit transaction ImportStorage; print'事务已成功提交！'; end end end try begin catch --关闭游标 close ImportStorageRecord; --释放游标资源 deallocate ImportStorageRecord; rollback transaction ImportStorage; print N'执行过程出错，事务已回滚'; end catch
//自定义表值函数，拆分字符串
-- 自定义表值函数, 拆分字符串 if exists(select * from sysobjects where [name] = 'fun_SPLIT_STR'and [type] = 'TF') DROP FUNCTION fun_SPLIT_STR go CREATE FUNCTION fun_SPLIT_STR( @s VARCHAR(8000), --待分拆的字符串 @split VARCHAR(10) --数据分隔符 )RETURNS @re TABLE(col VARCHAR(100)) AS BEGIN DECLARE @splitlen INT SET @splitlen=LEN(@split+'a')-2 WHILE CHARINDEX(@split,@s)>0 BEGIN INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1)) SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'') END INSERT @re VALUES(@s) RETURN END GO
//创建前删除语句
1 判断数据库是否存在 Sql代码 if exists (select * from sys.databases where name = ’数据库名’) drop database [数据库名] if exists (select * from sys.databases where name = ’数据库名’) drop database [数据库名] 2 判断表是否存在 Sql代码 if exists (select * from sysobjects where id = object_id(N’[表名]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1) drop table [表名] if exists (select * from sysobjects where id = object_id(N’[表名]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1) drop table [表名] 3 判断存储过程是否存在 Sql代码 if exists (select * from sysobjects where id = object_id(N’[存储过程名]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1) drop procedure [存储过程名] if exists (select * from sysobjects where id = object_id(N’[存储过程名]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1) drop procedure [存储过程名] 4 判断临时表是否存在 Sql代码 if object_id(’tempdb..#临时表名’) is not null drop table #临时表名 if object_id(’tempdb..#临时表名’) is not null drop table #临时表名 5 判断视图是否存在 Sql代码 --SQL Server 2000 IF EXISTS (SELECT * FROM sysviews WHERE object_id = ’[dbo].[视图名]’ --SQL Server 2005 IF EXISTS (SELECT * FROM sys.views WHERE object_id = ’[dbo].[视图名]’ --SQL Server 2000 IF EXISTS (SELECT * FROM sysviews WHERE object_id = ’[dbo].[视图名]’ --SQL Server 2005 IF EXISTS (SELECT * FROM sys.views WHERE object_id = ’[dbo].[视图名]’ 6 判断函数是否存在 Sql代码 -- 判断要创建的函数名是否存在 if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[函数名]’) and xtype in (N’FN’, N’IF’, N’TF’)) drop function [dbo].[函数名] -- 判断要创建的函数名是否存在 if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[函数名]’) and xtype in (N’FN’, N’IF’, N’TF’)) drop function [dbo].[函数名] 7 获取用户创建的对象信息 Sql代码 SELECT [name],[id],crdate FROM sysobjects where xtype=’U’ /* xtype 的表示参数类型，通常包括如下这些 C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 L = 日志 FN = 标量函数 IF = 内嵌表函数 P = 存储过程 PK = PRIMARY KEY 约束（类型是 K） RF = 复制筛选存储过程 S = 系统表 TF = 表函数 TR = 触发器 U = 用户表 UQ = UNIQUE 约束（类型是 K） V = 视图 X = 扩展存储过程 */ SELECT [name],[id],crdate FROM sysobjects where xtype=’U’ /* xtype 的表示参数类型，通常包括如下这些 C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 L = 日志 FN = 标量函数 IF = 内嵌表函数 P = 存储过程 PK = PRIMARY KEY 约束（类型是 K） RF = 复制筛选存储过程 S = 系统表 TF = 表函数 TR = 触发器 U = 用户表 UQ = UNIQUE 约束（类型是 K） V = 视图 X = 扩展存储过程 */ 8 判断列是否存在 Sql代码 if exists(select * from syscolumns where id=object_id(’表名’) and name=’列名’) alter table 表名 drop column 列名 if exists(select * from syscolumns where id=object_id(’表名’) and name=’列名’) alter table 表名 drop column 列名 9 判断列是否自增列 Sql代码 if columnproperty(object_id(’table’),’col’,’IsIdentity’)=1 print ’自增列’ else print ’不是自增列’ SELECT * FROM sys.columns WHERE object_id=OBJECT_ID(’表名’) AND is_identity=1 if columnproperty(object_id(’table’),’col’,’IsIdentity’)=1 print ’自增列’ else print ’不是自增列’ SELECT * FROM sys.columns WHERE object_id=OBJECT_ID(’表名’) AND is_identity=1 10 判断表中是否存在索引 Sql代码 if exists(select * from sysindexes where id=object_id(’表名’) and name=’索引名’) print ’存在’ else print ’不存在 if exists(select * from sysindexes where id=object_id(’表名’) and name=’索引名’) print ’存在’ else print ’不存在 11 查看数据库中对象 Sql代码 SELECT * FROM sys.sysobjects WHERE name=’对象名’ SELECT * FROM sys.sysobjects WHERE name=’对象名’
//行列转换问题：
行转列有两种方法： case when， pivot
列转行有两种方法：unionAll，unpivot
-- 行专列， 转弯后保存到testTab2表中 select * from ( select r.rowName,c.colName,value from valTab v right join colTab c on v.colId = c.colId right join rowTab r on v.rowId = r.rowId ) pTest pivot( --sum(value) for colName in (price,num,mony,xx) sum(value) for colName in (price,num,mony,xx) ) pvt -- 列转行 select rowName,colName,value from testTab2 unpivot( value for colName in (price,num,mony,xx) ) upvt
//分组后取第一条数据
select * from( select distinct bib.pk_invbasdoc as 'dim', bib.pk_invbasdoc as 'pk', bib.pk_invbasdoc as 'code' ,bib.pk_invbasdoc,bib.invname,igb.vuserdef17,ROW_NUMBER() over(PARTITION BY bib.pk_invbasdoc ORDER BY bib.pk_invbasdoc) as row_idx from ic_general_b igb inner join ic_general_h igh on igh.cgeneralhid = igb.cgeneralhid and igh.dr=igb.dr and igb.dr=0 inner join bd_invbasdoc bib on bib.pk_invbasdoc = igb.cinvbasid and bib.dr=igb.dr where igh.cbilltypecode = '4D' and igb.vuserdef17 is not null and igh.pk_defdoc20 is not null and igh.vuserdef10 is not null group by bib.pk_invbasdoc,bib.invname,igb.vuserdef17 ) dual where dual.row_idx = 1
//时间函数, 求指定日期的所有日期
-- 查询当月的天数 select convert(varchar(10),dateadd(dd,number,convert(varchar(8),getdate(),120)+'01'),120) as dt from master..spt_values where type='P' and dateadd(dd,number,convert(varchar(8),getdate(),120)+'01')<=dateadd(dd,-1,convert(varchar(8),dateadd(mm,1,getdate()),120)+'01') -- 查询2016-04-01到现在的天数 SELECT DATEADD(DAY, number, CONVERT(DATETIME, '2016-04-01')) DataTime FROM master..spt_values WHERE type = 'P' AND number BETWEEN 0 AND DATEDIFF(DAY, '2016-04-01', GETDATE()) -- 查询2016-04-01到现在的月份 SELECT DATEADD(MONTH, number, CONVERT(DATETIME, '2016-04-01')) month FROM master..spt_values WHERE type = 'P' AND number BETWEEN 0 AND DATEDIFF(MONTH, '2016-04-01', GETDATE()) 备注: --查询系统时间 select getdate() -- dateadd 在向指定日期加上一段时间的基础上，返回新的 datetime 值，例如：向日期加上2天 select dateadd(day,2,'2004-10-15') --返回：2004-10-17 00:00:00.000 --datediff 返回跨两个指定日期的日期和时间边界数 select datediff(day,'2004-09-01','2004-09-18') --返回：17 --datepart 返回代表指定日期的指定日期部分的整数 SELECT DATEPART(month, '2004-10-15') --返回 10 --datename 返回代表指定日期的指定日期部分的字符串 SELECT datename(weekday, '2004-10-15') --返回：星期五 --day(), month(),year() --可以与datepart对照一下 select 当前日期=convert(varchar(10),getdate(),120), select 当前时间=convert(varchar(8),getdate(),114), select datename(dw,'2004-10-15') select 本年第多少周=datename(week,'2004-10-15'), select 今天是周几=datename(weekday,'2004-10-15') --求相差天数 select datediff(day,'2004-01-01',getdate()) -- 一个月第一天的 SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) --本周的星期一 SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) select dateadd(wk,datediff(wk,0,getdate()),6) -- 一年的第一天 SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) -- 季度的第一天 SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) --当天的半夜 SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) -- 上个月的最后一天 SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) -- 去年的最后一天 SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) --本月的最后一天 SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) --本年的最后一天 SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) -- 本月的第一个星期一 select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
//封装为表值函数
-- 自定义表值函数，给定起始日期获取全部天数 if exists(select * from sysobjects where [name] = 'fun_CONTI_DATE'and [type] = 'TF') DROP FUNCTION fun_CONTI_DATE go CREATE FUNCTION fun_CONTI_DATE( @dsStart CHAR(10), --待分拆的字符串 @dsEnd CHAR(10) --数据分隔符 )RETURNS @re TABLE(dt VARCHAR(10)) AS BEGIN -- 给定日期获取全部天数 INSERT @re select dateadd(dd,number,convert(DATE,@dsStart,120)) as dt from master..spt_values where type='P' and dateadd(dd,number,convert(DATE,@dsStart,120))<=dateadd(ms,-3,convert(DATETIME,dateadd(dd,1,@dsEnd),120)) RETURN END GO --测试 -- select * from fun_CONTI_DATE('2021-01-01','2021-03-01')
//从会计期间关联出月份
-- 代码块实现会计期间与日期关联 DECLARE @i bigint = 1 ,@count bigint = 0 ,@dsStart char(10) ,@dsEnd char(10) BEGIN IF OBJECT_ID('tempdb..#tempDate',N'U') IS NOT NULL DROP TABLE #tempDate CREATE TABLE #tempDate ( rowid BIGINT ,pk_accperiodscheme char(20) ,pk_accperiod char(20) ,pk_accperiodquarter char(20) ,pk_accperiodmonth char(20) ,periodyear char(4) ,ybegindate char(10) ,yenddate char(10) ,halfyear char(1) ,[quarter] char(1) ,beginmonth char(2) ,endmonth char(2) ,[month] char(2) ,begindate char(10) ,enddate char(10) ,ym char(7) ,[date] char(10) ) --清空 TRUNCATE TABLE #tempDate --插入临时表 INSERT INTO #tempDate select ROW_NUMBER() over(order by bap.periodyear,bapm.[month]) as rowid ,bap.pk_accperiodscheme,bap.pk_accperiod,bapq.pk_accperiodquarter,bapm.pk_accperiodmonth ,bap.periodyear,bap.begindate as ybegindate,bap.enddate as yenddate ,case when bapm.[month] <= 6 then '1' else '2' end as halfyear ,cast(bapq.[quarter] as char) as 'quarter',bapq.beginmonth,bapq.endmonth ,bapm.[month],bapm.begindate,bapm.enddate,left(bapm.enddate,7) as ym ,NULL as [date] from bd_accperiod bap inner join bd_accperiodquarter bapq on bapq.pk_accperiodscheme = bap.pk_accperiodscheme and bapq.pk_accperiod = bap.pk_accperiod and bapq.dr = bap.dr and bap.dr=0 inner join bd_accperiodmonth bapm on bapm.pk_accperiodscheme = bap.pk_accperiodscheme and bapm.pk_accperiod = bap.pk_accperiod and bapm.[month] between bapq.beginmonth and bapq.endmonth and bapm.dr=bap.dr --读取数据集 SELECT @count = COUNT(1) from bd_accperiodmonth where dr = 0 WHILE @i <= @count BEGIN SELECT @dsStart = begindate,@dsEnd = enddate from #tempDate where rowid=@i INSERT INTO #tempDate select 0 as rowid ,bap.pk_accperiodscheme,bap.pk_accperiod,bapq.pk_accperiodquarter,bapm.pk_accperiodmonth ,bap.periodyear,bap.begindate as ybegindate,bap.enddate as yenddate ,case when bapm.[month] <= 6 then '1' else '2' end as halfyear ,cast(bapq.[quarter] as char) as 'quarter',bapq.beginmonth,bapq.endmonth ,bapm.[month],bapm.begindate,bapm.enddate,left(bapm.enddate,7) as ym ,fcd.dt as [date] from bd_accperiod bap inner join bd_accperiodquarter bapq on bapq.pk_accperiodscheme = bap.pk_accperiodscheme and bapq.pk_accperiod = bap.pk_accperiod and bapq.dr = bap.dr and bap.dr=0 inner join bd_accperiodmonth bapm on bapm.pk_accperiodscheme = bap.pk_accperiodscheme and bapm.pk_accperiod = bap.pk_accperiod and bapm.[month] between bapq.beginmonth and bapq.endmonth and bapm.dr=bap.dr inner join fun_CONTI_DATE(@dsStart,@dsEnd) as fcd on year(fcd.dt) = bap.periodyear and fcd.dt between bapm.begindate and bapm.enddate SET @i = @i+1 END --删除为空的数据 DELETE FROM #tempDate WHERE [date] is null --获取查询数据 SELECT * FROM #tempDate order by [date] END --表值函数实现会计期间与日期关联 -- ================================================ -- 调用的时候就 SELECT XX FROM dbo.fun_Accperiod_date('xx'), 不加dbo可能会被认为是系统函数而报错。 /* 如果函数中要定义变量，进行判断计算处理什么的，写法有点不一样了，要定义表变量才行，表值函数里 是【不允许创建临时表】的，只能是表变量 */ -- 自定义表值函数，获取会计期间对应的全部天数 if exists(select * from sysobjects where [name] = 'fun_Accperiod_date'and [type] = 'TF') DROP FUNCTION fun_Accperiod_date go CREATE FUNCTION fun_Accperiod_date( )RETURNS @re TABLE( rowid BIGINT ,pk_accperiodscheme char(20) ,pk_accperiod char(20) ,pk_accperiodquarter char(20) ,pk_accperiodmonth char(20) ,periodyear char(4) ,ybegindate char(10) ,yenddate char(10) ,halfyear char(1) ,[quarter] char(1) ,beginmonth char(2) ,endmonth char(2) ,[month] char(2) ,begindate char(10) ,enddate char(10) ,ym char(7) ,[date] char(10) ) AS BEGIN -- 局部变量 declare @i bigint = 1 declare @count bigint = 0 declare @dsStart char(10) declare @dsEnd char(10) --插入临时表 INSERT INTO @re select ROW_NUMBER() over(order by bap.periodyear,bapm.[month]) as rowid ,bap.pk_accperiodscheme,bap.pk_accperiod,bapq.pk_accperiodquarter,bapm.pk_accperiodmonth ,bap.periodyear,bap.begindate as ybegindate,bap.enddate as yenddate ,case when bapm.[month] <= 6 then '1' else '2' end as halfyear ,cast(bapq.[quarter] as char) as 'quarter',bapq.beginmonth,bapq.endmonth ,bapm.[month],bapm.begindate,bapm.enddate,left(bapm.enddate,7) as ym ,NULL as [date] from bd_accperiod bap inner join bd_accperiodquarter bapq on bapq.pk_accperiodscheme = bap.pk_accperiodscheme and bapq.pk_accperiod = bap.pk_accperiod and bapq.dr = bap.dr and bap.dr=0 inner join bd_accperiodmonth bapm on bapm.pk_accperiodscheme = bap.pk_accperiodscheme and bapm.pk_accperiod = bap.pk_accperiod and bapm.[month] between bapq.beginmonth and bapq.endmonth and bapm.dr=bap.dr --读取数据集 SELECT @count = COUNT(1) from bd_accperiodmonth where dr = 0 WHILE @i <= @count BEGIN SELECT @dsStart = begindate,@dsEnd = enddate from @re where rowid=@i INSERT INTO @re select 0 as rowid ,bap.pk_accperiodscheme,bap.pk_accperiod,bapq.pk_accperiodquarter,bapm.pk_accperiodmonth ,bap.periodyear,bap.begindate as ybegindate,bap.enddate as yenddate ,case when bapm.[month] <= 6 then '1' else '2' end as halfyear ,cast(bapq.[quarter] as char) as 'quarter',bapq.beginmonth,bapq.endmonth ,bapm.[month],bapm.begindate,bapm.enddate,left(bapm.enddate,7) as ym ,fcd.dt as [date] from bd_accperiod bap inner join bd_accperiodquarter bapq on bapq.pk_accperiodscheme = bap.pk_accperiodscheme and bapq.pk_accperiod = bap.pk_accperiod and bapq.dr = bap.dr and bap.dr=0 inner join bd_accperiodmonth bapm on bapm.pk_accperiodscheme = bap.pk_accperiodscheme and bapm.pk_accperiod = bap.pk_accperiod and bapm.[month] between bapq.beginmonth and bapq.endmonth and bapm.dr=bap.dr inner join fun_CONTI_DATE(@dsStart,@dsEnd) as fcd on year(fcd.dt) = bap.periodyear and fcd.dt between bapm.begindate and bapm.enddate SET @i = @i+1 END --删除为空的数据 DELETE FROM @re WHERE [date] is null RETURN END GO --测试 -- select * from fun_Accperiod_date('2021-01-01','2021-03-01')
//游标和循环处理
1.使用while IF OBJECT_ID('tempdb..#temptest',N'U') IS NOT NULL DROP TABLE #temptest GO CREATE TABLE #temptest ( ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,PatchID NUMERIC ,PatchName VARCHAR(50) ,[Status] INT DEFAULT 0 ) TRUNCATE TABLE #temptest -- 插入临时表 INSERT INTO #temptest SELECT PatchID,PatchName,0 FROM dbo.SystemPatch DECLARE @count INT DECLARE @i INT SET @count=0 SET @i=1 SELECT @count = COUNT(1) FROM #temptest -- 循环 WHILE @i<= @count BEGIN UPDATE #temptest SET Status=1 WHERE ID=@i SET @i = @i +1 END -- SELECT * FROM #temptest 2.使用游标 IF OBJECT_ID('tempdb..#temptest2',N'U') IS NOT NULL DROP TABLE #temptest2 CREATE TABLE #temptest2 ( ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,PatchID NUMERIC ,PatchName VARCHAR(50) ,[Status] INT DEFAULT 0 ) -- 插入临时表 INSERT INTO #temptest2 SELECT PatchID,PatchName,0 FROM dbo.SystemPatch DECLARE @patchid INT SET @patchid=0 -- 游标 DECLARE syspatch CURSOR FOR SELECT PatchID FROM #temptest2 OPEN syspatch FETCH NEXT FROM syspatch INTO @patchid -- 读取第一行 WHILE @@FETCH_STATUS =0 BEGIN UPDATE #temptest2 SET Status=1 WHERE PatchID = @patchid FETCH NEXT FROM syspatch INTO @patchid -- 读取下一行 END CLOSE syspatch DEALLOCATE syspatch -- SELECT * FROM #temptest2 /** 测试： 1. 处理 13 条记录 while 处理耗时 ： 0 s 游标处理耗时： 0 s 474801c4a26d1c3e6a8e048ee177507b164.jpg 2. 处理617条记录 while 处理耗时 ：0s 游标处理耗时：0s 8d98a82193aee5c0ae7c983e272057cc537.jpg 3.处理169390 while 处理耗时 ：2s 4888a9a88102339d7516a93908003d4e91d.jpg 游标处理耗时： 30m40s **/