SQLSERVER存储过程写法及使用


declare @aint

set @a=5

print @a--输出

--使用select语句赋值

declare @user1 nvarchar(50)

select @user1='张三'

print @user1

declare @user2 nvarchar(50)

select @user2 =Name from ST_Userwhere ID=1

print @user2

--使用update语句赋值

declare @user3 nvarchar(50)

update ST_Userset @user3 =Name where ID=1

print @user3

二、表、临时表、表变量

--创建临时表1

create table #DU_User1

(

[ID] [int]NOT NULL,

[Oid] [int]NOT NULL,

[Login] [nvarchar](50)NOT NULL,

[Rtx] [nvarchar](4)NOT NULL,

[Name] [nvarchar](5)NOT NULL,

[Password] [nvarchar](max)NULL,

[State] [nvarchar](8)NOT NULL

);

--向临时表1插入一条记录

insert into #DU_User1 (ID,Oid,[Login],Rtx,Name,[Password],State)values (100,2,'LS','0000','临时','321','特殊');

--从ST_User查询数据，填充至新生成的临时表

select * into #DU_User2from ST_Userwhere ID<8

--查询并联合两临时表

select * from #DU_User2where ID<3 union select * from #DU_User1

--删除两临时表

drop table #DU_User1

drop table #DU_User2

--创建临时表

CREATE TABLE #t

(

[ID] [int]NOT NULL,

[Oid] [int]NOT NULL,

[Login] [nvarchar](50)NOT NULL,

[Rtx] [nvarchar](4)NOT NULL,

[Name] [nvarchar](5)NOT NULL,

[Password] [nvarchar](max)NULL,

[State] [nvarchar](8)NOT NULL,

)

--将查询结果集（多条数据）插入临时表

insert into #tselect * from ST_User

--不能这样插入

--select * into #t from dbo.ST_User

--添加一列，为int型自增长字段

alter table #tadd [myid]int NOT NULL IDENTITY(1,1)

--添加一列，默认填充全球唯一标识

alter table #tadd [myid1] uniqueidentifierNOT NULL default(newid())

select * from #t

drop table #t

--给查询结果集增加自增长列

--无主键时：

select IDENTITY(int,1,1)as ID,Name,[Login],[Password]into #tfrom ST_User

select * from #t

--有主键时：

select (select SUM(1)from ST_Userwhere ID<= a.ID)as myID,* from ST_User aorder by myID

--定义表变量

declare @ttable

(

idint not null,

msg nvarchar(50)null

)

insert into @tvalues(1,'1')

insert into @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

--when then条件分支

declare @todayint

declare @week nvarchar(3)

set @today=3

set @week=case

    when @today=1 then '星期一'

    when @today=2 then '星期二'

    when @today=3 then '星期三'

    when @today=4 then '星期四'

    when @today=5 then '星期五'

    when @today=6 then '星期六'

    when @today=7 then '星期日'

    else '值错误'

end

print @week

五、游标

declare @IDint

declare @Oidint

declare @Loginvarchar(50)

--定义一个游标

declare user_curcursor for select ID,Oid,[Login]from ST_User

--打开游标

open user_cur

fetch next from user_curinto @ID,@Oid,@Login

while @@fetch_status=0

begin

--读取游标

    print @ID

fetch next from user_curinto @ID,@Oid,@Login

--print @Login

end

close user_cur

--摧毁游标

deallocate user_cur

六、触发器

触发器中的临时表：

Inserted

存放进行insert和update 操作后的数据

Deleted

存放进行delete 和update操作前的数据

--创建触发器

create trigger user_onupdate

on st_user

for update

as

    declare @msg nvarchar(50)

--@msg记录修改情况

    select @msg =n'姓名从“' + deleted.name +n'”修改为“' + inserted.name +'”' from inserted,deleted

--插入日志表

    insert into [log](msg)values(@msg)

--删除触发器

drop trigger User_OnUpdate

七、存储过程

--创建带output参数的存储过程

create procedure pr_sum

@aint,

@bint,

@sumint output

as

begin

    set @sum=@a+@b

end

--创建Return返回值存储过程

CREATE PROCEDURE PR_Sum2

@aint,

@bint

AS

BEGIN

    Return @a+@b

END

--执行存储过程获取output型返回值

declare @mysumint

execute PR_Sum1,2,@mysumoutput

print @mysum

--执行存储过程获取Return型返回值

declare @mysum2int

execute @mysum2= PR_Sum21,2

print @mysum2

八、自定义函数

函数的分类：

1）标量值函数

2）表值函数

a:内联表值函数

b:多语句表值函数

3）系统函数

--新建标量值函数

create function FUNC_Sum1

(

@aint,

@bint

)

returnsint

as

begin

    return @a+@b

end

--新建内联表值函数

create function FUNC_UserTab_1

(

@myIdint

)

returnstable

as

return (select *from ST_Userwhere ID<@myId)

--新建多语句表值函数

create function FUNC_UserTab_2

(

@myIdint

)

returns @ttable

(

[ID] [int]NOT NULL,

[Oid] [int]NOT NULL,

[Login] [nvarchar](50)NOT NULL,

[Rtx] [nvarchar](4)NOT NULL,

[Name] [nvarchar](5)NOT NULL,

[Password] [nvarchar](max)NULL,

[State] [nvarchar](8)NOT NULL

)

as

begin

    insert into @tselect * from ST_Userwhere ID<@myId

return

end

--调用表值函数

select * from dbo.FUNC_UserTab_1(15)

--调用标量值函数

declare @sint

set @s=dbo.FUNC_Sum1(100,50)

print @s

--删除标量值函数

drop function FUNC_Sum1

谈谈自定义函数与存储过程的区别：

一、自定义函数：

1. 可以返回表变量

2. 限制颇多，包括

不能使用output参数；

不能用临时表；

函数内部的操作不能影响到外部环境；

不能通过select返回结果集；

不能update，delete，数据库表；

3. 必须return 一个标量值或表变量

自定义函数一般用在复用度高，功能简单单一，争对性强的地方。

二、存储过程

1. 不能返回表变量

2. 限制少，可以执行对数据库表的操作，可以返回数据集

3. 可以return一个标量值，也可以省略return

存储过程一般用在实现复杂的功能，数据操纵方面。
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,590评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 86,808评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,151评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,779评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,773评论 5赞 367
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,656评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,022评论 3赞 398
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,678评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 41,038评论 1赞 299
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,659评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,756评论 1赞 330
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,411评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,005评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,973评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,203评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,053评论 2赞 350
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,495评论 2赞 343

推荐阅读更多精彩内容