【原】SQL Server——SQL Server存储过程与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(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)
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 
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 '分数'