SQL Server 2008 存储过程示例

2024-04-08 16:20:03 160


--有输入参数的存储过程--
createprocGetComment
(@commentidint)
as
select*fromCommentwhereCommentID=@commentid

--有输入与输出参数的存储过程--
createprocGetCommentCount
@newsidint,
@countintoutput
as
select@count=count(*)fromCommentwhereNewsID=@newsid


--返回单个值的函数--
createfunctionMyFunction
(@newsidint)
returnsint
as
begin
declare@countint
select@count=count(*)fromCommentwhereNewsID=@newsid
return@count
end

--调用方法--
declare@countint
exec@count=MyFunction2
print@count

--返回值为表的函数--
CreatefunctionGetFunctionTable
(@newsidint)
returnstable
as
return
(select*fromCommentwhereNewsID=@newsid)

--返回值为表的函数的调用--
select*fromGetFunctionTable(2)

SQLServer存储过程中不拼接SQL字符串实现多条件查询

--以前拼接的写法
set@sql='select*fromtablewhere1=1'
if(@addDateisnotnull)
set@sql=@sql+'andaddDate='+@addDate+''
if(@name<>''andisnotnull)
set@sql=@sql+'andname='+@name+''
exec(@sql)

下面是不采用拼接SQL字符串实现多条件查询的解决方案

--第一种写法是感觉代码有些冗余
if(@addDateisnotnull)and(@name<>'')
select*fromtablewhereaddDate=@addDateandname=@name
elseif(@addDateisnotnull)and(@name='')
select*fromtablewhereaddDate=@addDate
elseif(@addDateisnull)and(@name<>'')
select*fromtablewhereandname=@name
elseif(@addDateisnull)and(@name='')
select*fromtable
--第二种写法是
select*fromtablewhere(addDate=@addDateor@addDateisnull)and(name=@nameor@name='')
--第三种写法是
SELECT*FROMtablewhere
addDate=CASE@addDateISNULLTHENaddDateELSE@addDateEND,
name=CASE@nameWHEN''THENnameELSE@nameEND

SQLSERVER存储过程基本语法

一、定义变量

--简单赋值
declare@aint
set@a=5
print@a

--使用select语句赋值
declare@user1nvarchar(50)
select@user1='张三'
print@user1
declare@user2nvarchar(50)
select@user2=NamefromST_UserwhereID=1
print@user2

--使用update语句赋值
declare@user3nvarchar(50)
updateST_Userset@user3=NamewhereID=1
print@user3

二、表、临时表、表变量

--创建临时表1
createtable#DU_User1
(
[ID][int]NOTNULL,
[Oid][int]NOTNULL,
[Login][nvarchar](50)NOTNULL,
[Rtx][nvarchar](4)NOTNULL,
[Name][nvarchar](5)NOTNULL,
[Password][nvarchar](max)NULL,
[State][nvarchar](8)NOTNULL
);
--向临时表1插入一条记录
insertinto#DU_User1(ID,Oid,[Login],Rtx,Name,[Password],State)values(100,2,'LS','0000','临时','321','特殊');

--从ST_User查询数据，填充至新生成的临时表
select*into#DU_User2fromST_UserwhereID<8

--查询并联合两临时表
select*from#DU_User2whereID<3unionselect*from#DU_User1

--删除两临时表
droptable#DU_User1
droptable#DU_User2

--创建临时表
CREATETABLE#t
(
[ID][int]NOTNULL,
[Oid][int]NOTNULL,
[Login][nvarchar](50)NOTNULL,
[Rtx][nvarchar](4)NOTNULL,
[Name][nvarchar](5)NOTNULL,
[Password][nvarchar](max)NULL,
[State][nvarchar](8)NOTNULL,
)

--将查询结果集（多条数据）插入临时表
insertinto#tselect*fromST_User
--不能这样插入
--select*into#tfromdbo.ST_User

--添加一列，为int型自增长子段
altertable#tadd[myid]intNOTNULLIDENTITY(1,1)
--添加一列，默认填充全球唯一标识
altertable#tadd[myid1]uniqueidentifierNOTNULLdefault(newid())

select*from#t
droptable#t
--给查询结果集增加自增长列

--无主键时：
selectIDENTITY(int,1,1)asID,Name,[Login],[Password]into#tfromST_User
select*from#t

--有主键时：
select(selectSUM(1)fromST_UserwhereID<=a.ID)asmyID,*fromST_UseraorderbymyID
--定义表变量
declare@ttable
(
idintnotnull,
msgnvarchar(50)null
)
insertinto@tvalues(1,'1')
insertinto@tvalues(2,'2')
select*from@t

三、循环

--while循环计算1到100的和
declare@aint
declare@sumint
set@a=1
set@sum=0
while@a<=100
begin
set@sum+=@a
set@a+=1
end
print@sum

四、条件语句

--if,else条件分支
if(1+1=2)
begin
print'对'
end
else
begin
print'错'
end

--whenthen条件分支
declare@todayint
declare@weeknvarchar(3)
set@today=3
set@week=case
when@today=1then'星期一'
when@today=2then'星期二'
when@today=3then'星期三'
when@today=4then'星期四'
when@today=5then'星期五'
when@today=6then'星期六'
when@today=7then'星期日'
else'值错误'
end
print@week

五、游标

declare@IDint
declare@Oidint
declare@Loginvarchar(50)

--定义一个游标
declareuser_curcursorforselectID,Oid,[Login]fromST_User
--打开游标
openuser_cur
while@@fetch_status=0
begin
--读取游标
fetchnextfromuser_curinto@ID,@Oid,@Login
print@ID
--print@Login
end
closeuser_cur
--摧毁游标
deallocateuser_cur

五、游标

declare@IDint
declare@Oidint
declare@Loginvarchar(50)

--定义一个游标
declareuser_curcursorforselectID,Oid,[Login]fromST_User
--打开游标
openuser_cur
while@@fetch_status=0
begin
--读取游标
fetchnextfromuser_curinto@ID,@Oid,@Login
print@ID
--print@Login
end
closeuser_cur
--摧毁游标
deallocateuser_cur

六、触发器

触发器中的临时表：
Inserted
存放进行insert和update操作后的数据
Deleted
存放进行delete和update操作前的数据

--创建触发器
CreatetriggerUser_OnUpdate
OnST_User
forUpdate
As
declare@msgnvarchar(50)
--@msg记录修改情况
select@msg=N'姓名从“'+Deleted.Name+N'”修改为“'+Inserted.Name+'”'fromInserted,Deleted
--插入日志表
insertinto[LOG](MSG)values(@msg)

--删除触发器
droptriggerUser_OnUpdate

七、存储过程

--创建带output参数的存储过程
CREATEPROCEDUREPR_Sum
@aint,
@bint,
@sumintoutput
AS
BEGIN
set@sum=@a+@b
END

--创建Return返回值存储过程
CREATEPROCEDUREPR_Sum2
@aint,
@bint
AS
BEGIN
Return@a+@b
END

--执行存储过程获取output型返回值
declare@mysumint
executePR_Sum1,2,@mysumoutput
print@mysum

--执行存储过程获取Return型返回值
declare@mysum2int
execute@mysum2=PR_Sum21,2
print@mysum2

八、自定义函数

函数的分类：
1）标量值函数
2）表值函数
a:内联表值函数
b:多语句表值函数
3）系统函数

--新建标量值函数
createfunctionFUNC_Sum1
(
@aint,
@bint
)
returnsint
as
begin
return@a+@b
end

--新建内联表值函数
createfunctionFUNC_UserTab_1
(
@myIdint
)
returnstable
as
return(select*fromST_UserwhereID<@myId)

--新建多语句表值函数
createfunctionFUNC_UserTab_2
(
@myIdint
)
returns@ttable
(
[ID][int]NOTNULL,
[Oid][int]NOTNULL,
[Login][nvarchar](50)NOTNULL,
[Rtx][nvarchar](4)NOTNULL,
[Name][nvarchar](5)NOTNULL,
[Password][nvarchar](max)NULL,
[State][nvarchar](8)NOTNULL
)
as
begin
insertinto@tselect*fromST_UserwhereID<@myId
return
end

--调用表值函数
select*fromdbo.FUNC_UserTab_1(15)
--调用标量值函数
declare@sint
set@s=dbo.FUNC_Sum1(100,50)
print@s

--删除标量值函数
dropfunctionFUNC_Sum1

SQL Server 2008 存储过程示例

热门推荐

随机推荐