首页 / 更多教程 / 在SQL中存储过程的一般语法
在SQL中存储过程的一般语法
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了在SQL中存储过程的一般语法,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5330字,纯文字阅读大概需要8分钟。
内容图文
![在SQL中存储过程的一般语法](/upload/InfoBanner/zyjiaocheng/490/767c10f0a24a4459beb2013880828af0.jpg)
一般分为十种情况,每种语法各不相同:
1、 创建语法
1 2 3 4 5 6 7 |
create proc | procedure pro_name
[{@参数数据类型} [=默认值] [ output ],
{@参数数据类型} [=默认值] [ output ],
....
]
as
SQL_statements
|
2、 创建不带参数存储过程
1 2 3 4 5 6 7 8 9 10 |
--创建存储过程
if (exists ( select * from sys.objects where name = ‘proc_get_student‘ ))
drop proc proc_get_student
go
create proc proc_get_student
as
select * from student;
--调用、执行存储过程
exec proc_get_student;
|
3、 修改存储过程
1 2 3 4 |
--修改存储过程
alter proc proc_get_student
as
select * from student;
|
4、 带参存储过程
1 2 3 4 5 6 7 8 9 10 |
--带参存储过程
if (object_id( ‘proc_find_stu‘ , ‘P‘ ) is not null )
drop proc proc_find_stu
go
create proc proc_find_stu(@startId int , @endId int )
as
select * from student where id between @startId and @endId
go
exec proc_find_stu 2, 4;
|
5、 带通配符参数存储过程
1 2 3 4 5 6 7 8 9 10 11 |
--带通配符参数存储过程
if (object_id( ‘proc_findStudentByName‘ , ‘P‘ ) is not null )
drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@ name varchar (20) = ‘%j%‘ , @nextName varchar (20) = ‘%‘ )
as
select * from student where name like @ name and name like @nextName;
go
exec proc_findStudentByName;
exec proc_findStudentByName ‘%o%‘ , ‘t%‘ ;
|
6、 带输出参数存储过程
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
if (object_id( ‘proc_getStudentRecord‘ , ‘P‘ ) is not null )
drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
@id int , --默认输入参数
@ name varchar (20) out , --输出参数
@age varchar (20) output --输入输出参数
)
as
select @ name = name , @age = age from student where id = @id and sex = @age;
go
--
declare @id int ,
@ name varchar (20),
@ temp varchar (20);
set @id = 7;
set @ temp = 1;
exec proc_getStudentRecord @id, @ name out , @ temp output ;
select @ name , @ temp ;
print @ name + ‘#‘ + @ temp ;
|
7、 不缓存存储过程
1 2 3 4 5 6 7 8 9 10 11 |
--WITH RECOMPILE 不缓存
if (object_id( ‘proc_temp‘ , ‘P‘ ) is not null )
drop proc proc_temp
go
create proc proc_temp
with recompile
as
select * from student;
go
exec proc_temp;
|
8、 加密存储过程
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--加密WITH ENCRYPTION
if (object_id( ‘proc_temp_encryption‘ , ‘P‘ ) is not null )
drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
select * from student;
go
exec proc_temp_encryption;
exec <a href= "https://www.baidu.com/s?wd=sp_helptext&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1YYm103n1DYmHfknhD3nWD10ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EPW63nHcdrH6" target= "_blank" class= "baidu-highlight" >sp_helptext</a> ‘proc_temp‘ ;
exec <a href= "https://www.baidu.com/s?wd=sp_helptext&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1YYm103n1DYmHfknhD3nWD10ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EPW63nHcdrH6" target= "_blank" class= "baidu-highlight" >sp_helptext</a> ‘proc_temp_encryption‘ ;
|
9、 带游标参数存储过程
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
if (object_id( ‘proc_cursor‘ , ‘P‘ ) is not null )
drop proc proc_cursor
go
create proc proc_cursor
@cur cursor varying output
as
set @cur = cursor forward_only static for
select id, name , age from student;
open @cur;
go
--调用
declare @exec_cur cursor ;
declare @id int ,
@ name varchar (20),
@age int ;
exec proc_cursor @cur = @exec_cur output ; --调用存储过程
fetch next from @exec_cur into @id, @ name , @age;
while (<a href= "https://www.baidu.com/s?wd=%40%40fetch_status&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1YYm103n1DYmHfknhD3nWD10ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EPW63nHcdrH6" target= "_blank" class= "baidu-highlight" >@@fetch_status</a> = 0)
begin
fetch next from @exec_cur into @id, @ name , @age;
print ‘id: ‘ + convert ( varchar , @id) + ‘, name: ‘ + @ name + ‘, age: ‘ + convert ( char , @age);
end
close @exec_cur;
deallocate @exec_cur; --删除游标
|
10、 分页存储过程
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
---存储过程、row_number完成分页
if (object_id( ‘pro_page‘ , ‘P‘ ) is not null )
drop proc proc_cursor
go
create proc pro_page
@startIndex int ,
@endIndex int
as
select count (*) from product
;
select * from (
select row_number() over( order by pid) as rowId, * from product
) temp
where temp .rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1, 4
--
--分页存储过程
if (object_id( ‘pro_page‘ , ‘P‘ ) is not null )
drop proc pro_stu
go
create procedure pro_stu(
@pageIndex int ,
@pageSize int
)
as
declare @startRow int , @endRow int
set @startRow = (@pageIndex - 1) * @pageSize +1
set @endRow = @startRow + @pageSize -1
select * from (
select *, row_number() over ( order by id asc ) as number from student
) t
where t.number between @startRow and @endRow;
exec pro_stu 2, 2;
|
在SQL中存储过程的一般语法
标签:exec col objects target not containe syntax dea pst
本文系统来源:http://www.cnblogs.com/tiger95/p/7466149.html
内容总结
以上是互联网集市为您收集整理的在SQL中存储过程的一般语法全部内容,希望文章能够帮你解决在SQL中存储过程的一般语法所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。