SQLServer——SQLServer存储过程与exec简单使⽤
⼀、【存储过程】
存储过程的T-SQL语句编译以后可多次执⾏，由于T-SQL语句不需要重新编译，所以执⾏存储过程可以 提⾼性能。存储过程具有以下特点：
· 存储过程已在服务器上存储
· 存储过程具有安全特性
· 存储过程允许模块化程序设计
· 存储过程可以减少⽹络通信流量
· 存储过程可以提⾼运⾏速度 存储过程分为⽤户存储过程、系统存储过程和扩展存储过程。
存储过程Procedure是⼀组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，⽤户通过指 定存储过程的名称并给出参数来执⾏。 存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果 集以及返回值。 由于存储过程在创建时即在数据库服务器上进⾏了
编译并存储在数据库中，所以存储过程运⾏要⽐单个 的SQL语句块要快。同时由于在调⽤时只需⽤提供存储过程名和必要的参数信息，所以在⼀定程度上也可以 减少⽹络流量、简单⽹络负担。
1、使⽤T-SQL语句创建存储过程
CREATE PROC[ EDURE ] procedure_name [; number ]
[ { @parameter data_type } [VARYING][=default][ OUTPUT ]][,...n ]
[WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ][FOR REPLICATION]AS
[begin] T-SQL语句[end]
2、使⽤存储过程
使⽤T-SQL中的EXECUTE（或EXEC）语句可以执⾏⼀个已定义的存储过程。
3、删除存储过程
使⽤DROP PROCEDEURE 语句删除存储过程。
代码⽰例1
create database test_0425 -- 创建数据库
go
use test_0425
go
-- 建表
create table student
(
stno int primary key,
stsex char(4)not null,
stname char(10)not null,
stspecialty char(20)not null,
stscore int not null
)
go
-- 设置检查约束，性别只能输⼊男或⼥
alter table student
add constraint Check_stsex check(stsex in('男','⼥'))
go
-- 设置默认约束，未输⼊性别时候默认男
alter table student
add constraint Default_stsex_man default'男'for stsex
go
insert into student values(001,'男','赵⼀','数学',99)
insert into student values(002,'男','赵⼆','数学',88)
insert into student values(003,'男','赵三','数学',97)
insert into student values(003,'男','赵三','数学',97)
insert into student values(004,'男','赵四','数学',86)
insert into student values(005,'男','赵五','数学',45)
insert into student values(006,'男','赵六','数学',66)
insert into student values(007,'⼥','赵器','数学',77)
insert into student values(008,'⼥','赵把','语⽂',99)
insert into student values(009,'⼥','赵久','语⽂',100)
insert into student values(010,'⼥','赵时','语⽂',85)
insert into student values(011,'⼥','赵事宜','语⽂',75)
sql存储过程实例insert into student values(013,'⼥','赵⼗⼆','语⽂',66)
insert into student values(015,'⼥','赵⼗三','语⽂',55)
insert into student values(018,'⼥','赵⼗四','语⽂',32)
insert into student values(020,'⼥','赵失误','语⽂',0)
insert into student (stno,stsex,stname,stspecialty,stscore)
values(017,'⼥','赵时期','语⽂',86)
select*from student
if exists(select*from sysobjects where name='porc_fun_avge'and type='p')
drop procedure porc_fun_avge
go
-
- 设计计算专业平均值的存储过程
create proc porc_fun_avge
(
@fun_stspecial char(20)
)
as
select@fun_stspecial as'学科',AVG(stscore)as'平均分'from student where stspecialty=@fun_stspecial
exec porc_fun_avge '数学'
exec porc_fun_avge '语⽂'
-- 设计分段存储过程
if exists(select*from sysobjects where name='porc_fun_avge'and type='p')
drop procedure proc_subsection_stscore
go
create proc proc_subsection_stscore
(
@score int,
@name char(10)out
)
as
if@score>60
select stname as'姓名',stscore as'分数','及格'as'评价'from student
else
select stname as'姓名',stscore as'分数','不及格'as'评价'from student
go
-- 调⽤/执⾏存储过程
declare@stscore int,
@stsname char(10)
set@stscore=65
exec proc_subsection_stscore @stscore,@stsname out
代码⽰例2
exec有两个语法：第⼀个，执⾏存储过程：如果 EXEC SP_XXX第⼆个，执⾏组合的命令字符串----------------------------------------------------------------------------------------------
-- 常⽤系统存储过程
-- 查询数据库
use master
exec sp_databases
-- 查看数据库中的表
use sixstardb
exec sp_tables
-- 查看数据表中的字段
exec sp_columns student
-- 查看索引
exec sp_helpindex student
-- 查看约束
exec sp_helpconstraint student
-
- 查看数据库相关信息
exec sp_helpdb sixstardb
-- 通过存储过程对表进⾏重命名
exec sp_rename 'cous','course'
----------------------------------------------------------------------------------------------
-- ⾃定义存储过程
select*from student
-- 1、创建不带参数的存储过程
use sixstardb
go
if exists(select*from sysobjects where name='proc_getstudent'and type='p')
drop procedure proc_getstudent
go
create proc proc_getstudent
as
select*from student
-- 调⽤/执⾏存储过程
exec proc_getstudent
-- 2、创建带有参数的存储过程
if exists(select*from sysobjects where name='proc_Findstno_student'and type='p') drop procedure proc_Findstno_student
go
create proc proc_Findstno_student(@fstno char(4))
as
select*from student where stno=@fstno
go
-- 调⽤/执⾏存储过程
exec proc_Findstno_student 1007
-- 3、创建带通配符参数存储过程
if exists(select*from sysobjects where name='proc_FindBystname_student'and type='p') drop procedure proc_FindBystname_student
go
create proc proc_FindBystname_student(@name char(8)='%⼩%')
as
as
select*from student where stname like@name
go
-- 调⽤/执⾏存储过程
exec proc_FindBystname_student '%⼩%'
-- 4、创建带输⼊输出参数的存储过程
if exists(select*from sysobjects where name='proc_FindBystname_student'and type='p') drop procedure proc_FindBystname_student
go
create proc proc_Inputsno_getInfo_student
(
@id char(4),-- 默认为输⼊参数
@name char(8)out,-- 输出参数
@speciality char(10)out,-- 输出参数
@sum int out-- 输出参数
)
as
select@name=stname,@speciality=stspeciality,@sum=stsum
from student
where stno=@id
go
select*from student
-- 调⽤/执⾏存储过程
declare@stid char(4),
@stsname char(8),
@stspeciality char(10),
@stsum int
set@stid=1002
exec proc_Inputsno_getInfo_student @stid,@stsname out,@stspeciality out,@stsum out select@stsname as'姓名',@stspeciality as'专业',@stsum as'分数'
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论