存储过程的定义、修改和删除

1.存储过程的分类

最近写T-sql语句比较多, 想把这段时间遇到过的貌似有用的一些sql server的函数,存储过程,临时表等总结一下,以便不时之需,当然不会一下子所有都总结,但会分段总结;

  • 系统存储过程
  • 本地存储过程(用户自定义)
  • 临时存储过程(局部【#】、全局【##】临时存储过程)

 

2.创建存储过程

1)存储过程定义:

--选出价格区间的商品信息
create procedure sp_goods_price
@minprice float ,@maxprice float
as 
select * from goods 
where price>=@minprice and price <=@maxprice
go

 

 

create proc [proc_name]
@para_1 nvarchar(250),
@para_2 int=1,
@para_3 nchar(10) output
as

金沙官网线上,执行存储过程: execute sp_goods_price 200 2000

 

3.修改存储过程

上面这段sequal就是创建一个名为proc_name的存储过程,她接受两个输入参数,分别为@para_1和有默认值为1的@para_2,且返回一个nchar类型的值;
@para_2 int=1, 指明了@para_2参数在调用该存储过程时可以不提供,系统会给个默认值为1;
@para_3 nchar(10) output, 说明@para_3是一个输出参数,存储过程可以指明多个输出参数,如还可以在后面加一个输出参数:@para_4 bit output,要在退出存储过程前给她赋值,如果不赋值她一直保持为null;
可以像下面那样去调用:

create procedure sp_goods_betw
@minprice float =200,@maxprice float=3000
as 
select * from goods 
where price>=@minprice and price <=@maxprice
go
declare @arg_3 nchar(10)
exec proc proc_name 'staff_001',0,@arg_3 output

-- 或者不指供有默认值的para_2, 如
declare @arg_3 nchar(10)
exec proc proc_name 'staff_001',@arg_3 output

 

 

4.删除存储过程

 

drop procedure sp_goods_price

 

 5.查看存储过程

2)临时表:

sp_helptext procedureName
sp_help procedureName

2种临时表,分别为:全局,局部
全局和局部和平时学的编程语言里的全局变量和局部变量语义是一样,只是意思上有点差别;
如全局临时表一旦创建了,她就像一个普通的表一样,只要创建她的那个连接未断开,当前任何的连接都可以访问该全局临时表,如果创建她的连接断开了,那么已经引用她的其它任务,可以继续引用她,但新的任务或连接不能再引用她,一旦没有任何任务引用她时,该临时表即被系统释放掉;
局部临时表只能在创建她的任务里引用, 比喻创建了一个连接,如平时那样连接上一个数据库,然后创建一个临时表,那么该临时表,可能被在该连接里的所有任务引用,但如果在该连接里的某个存储过程创建,则只在被该存储过程引用,其它存储过程或函数(这些统称为任务)都不能引用她;

 6.重命名存储过程

创建全局临时表, 全局临时表都是以##开头的,这是sql server 内定的,不能改:

exec sp_rename oldName newName
create table [##g_temp_name](
    staff_no nvarchar(250) primary key,
    staff_name nvarchar(250) null,
    state int default 0
    )
-- 或者
select * into [##g_temp_name] from real_table

-- 或者
exec('select * into [##g_temp_name] from real_table')

 

 

**局部存储过程

 

create procedure #sp_goods_betw
@minprice float ,@maxprice float
as 
select * from goods 
where price>=@minprice and price <=@maxprice
go

上面3种试创建的全局临时表效果是一样的,只要连接未断开,都可以像访问普通表一样访问她:

 

 

**全局存储过程

select * from ##g_temp_name

-- or

exec('select * from ##g_temp_name')
create procedure ##sp_goods_betw
@minprice float ,@maxprice float
as 
select * from goods 
where price>=@minprice and price <=@maxprice
go

 

 

 

**不加缓存的存储过程

 

create procedure sp_goods_betw
@minprice float ,@maxprice float

with recompile

as 
select * from goods 
where price>=@minprice and price <=@maxprice
go

 

 

创建局部临时表, 局部临时表都以单个#开头的,也是sql server 内定的:

**加密存储过程

 

create procedure sp_goods_betw
@minprice float ,@maxprice float

with enctyption

as 
select * from goods 
where price>=@minprice and price <=@maxprice
go
create table [#temp_name](
    staff_no nvarchar(250) primary key,
    staff_name nvarchar(250) not null,
    state int default 0
    )

-- 或者
select * into #temp_name from real_table

-- 或者

exec ('select * into #temp_name from real_table')

 

 

 

本文由金沙官网线上发布于数据库,转载请注明出处:存储过程的定义、修改和删除

您可能还会对下面的文章感兴趣: