SQL语句（十八）—— 存储过程

存储过程

1. 系统存储过程

2. 自定义存储过程

3. 扩展存储过程

一、创建存储过程

1. 创建存储过程

--例1
USE SU
GO
Create Procedure SelProc
AS
Select * 
From SC
GO

--例2
USE SU
GO
Create Procedure CountProc
AS
Select Count(*) AS 总数 
From Course
Go

　2. 存储过程调用

USE SU
GO
Exec SelProc
Exec CountProc

　3. 创建带输入参数的存储过程

--例1 创建 带输入参数 的存储过程
USE SU
GO
Create Procedure QueryById @Sno varchar(10)
AS
Select *
from SC
Where Sno = @Sno
GO
--执行
GO
Execute QueryById '1513032087'
Execute QueryById @Sno = '1513032087'

--创建 带默认参数 的存储过程
USE SU
GO
create Procedure QueryById2 @Sno varchar(10) = '1513032087'
AS
Select *
From SC
where Sno = @Sno
GO
--执行(可以不带参数, 因为有默认参数)
EXecute QueryById2

4. 创建带输出参数的存储过程（执行时候，需要声明！！！！！！）

--例4
Use SU
GO
Create Procedure QueryById3 
@Sno varchar(10) = '1513032087',
@Cnocount INT OUTPUT   --输出参数,Sno学号选的课程数
AS
Select @Cnocount = Count(SC.Cno)
From SC, Student
where SC.Sno = Student.Sno and SC.Sno = @Sno
GO
--执行
--必须先声明！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
Declare @Sno varchar(10) = '1513032087';
Declare @Cnocount int;                     --声明之后再执行
EXEC QueryById3 @Sno, @Cnocount OUTPUT
Select '学号为' + @Sno + '选了' + Ltrim(STR(@Cnocount)) + '门课'  --字符串还需要处理一下
GO

二、管理存储过程

1. 修改存储过程

------------管理存储过程------------------
--修改存储过程
exec CountProc   --查询
GO
Alter Procedure CountProc
AS
select Sno, COUNT(*) AS 总数
From SC
Group By Sno

--查看属性
GO
select OBJECT_DEFINITION(OBJECT_ID('QueryById'));  --select OBJECT_DEFINITION(OBJECT_ID('存储过程名'));
Exec sp_help QueryById      
Exec sp_helptext QueryById


--删除操作
GO
DROP procedure 存储过程

三、扩展存储过程

可以将参赛传递过扩展存储过程，扩展存储过程也可以返回结果和状态

--查看系统版本信息
EXEC xp_msver

四、自定义函数(允许模块化程序设计)

1. 创建标量函数

--创建一个标量函数，指定学生id值，返回该学生姓名
GO
Create Procedure Snotosname @Sno varchar(10) = '1513032087'
AS
Begin
Declare @Sname varchar(40)
Select @Sname = (
                Select Sname 
                From Student
                where Sno = @Sno)
RETURN @Sname
End

2. 创建表值函数

--例1
GO
Create Function getStuRecordBySex(@Ssex char(4))
Returns Table
Return
(
    select Sno, Sname, Ssex, (Sage + 2) as newAge
    From Student
    where Ssex = @Ssex
)
--执行这个函数
--执行函数
Select *
From getStuRecordBySex('男')

3. 删除函数

--删除函数
Drop Function getStuRecordBySex