泄洪闸 1、 创建语法
create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output], {@参数数据类型} [=默认值] [output],
....
]
as
SQL_statements
2、 创建不带参数存储过程
--创建存储过程沉没度
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、 修改存储过程
--修改存储过程
alter proc proc_get_student
as
select * from student;
4、 带参存储过程
--带参存储过程
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、 带通配符参数存储过程
--带通配符参数存储过程
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、 带输出参数存储过程
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、 不缓存存储过程
--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、 加密存储过程
--加密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 sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';
9、 带游标参数存储过程
if (object_id(卡片式u盘'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;
--调用
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 (@@fetch_status = 0)
begin
fetch next from @exec_cur into @id, @name, @age;
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议