T-SQL 编程之存储过程

存储过程

SQL语句和流程控制语句的预编译集合，它是一个名称存储并作为一个单元处理,应用程序可以通过调用来执行该存储过程。用来执行管理任务或应用复杂的业务规则，存储过程可以带参数，也可以返回结果。

1.1 变量以及流程控制语句

1.1.1 变量

(1) T-SQL种变量分为两种，全局变量和局部变量。

全局变量 @@ 声明, 局部变量用@声明

• 全局变量是由系统提供并预先声明的变量，用户一般只能查看不能修改。
• 局部变量是由用户用以保存单个特定类型的单个数据值的对象，它局部于一个语句批。

(2) 变量的声明和赋值

DECLARE @variable_name DataType

其中 variable_name为局部变量的名称，DataType为数据类型。

• 局部变量的值不能是text 或者 image

// 赋值

SET @variable_name = data | 表达式

// 使用SET语句是对局部变量赋值的首选方法，除此之外也可以使用 SELECT语句

SELECT @variable_name = data | 表达式

---

• 课程表Course

• 学生信息表Student

• 选课表SC

• 小练习

DECLARE @x int, @y int, @z int
SET @x = 10;
SET @y = 20;
SET @z = @x + @y;
PRINT @z

1.1.2 流程控制语句

流程控制语句一般分为三类： 顺序、分支、循环。

BEGIN .. END
BREAK
CONTINUE
GOTO
IF .. ELSE 
RETURN
WHILE

DECLARE @i int, @sum int
@i =  1
@sum = 0
WHILE i <= 100
BEGIN
SET @sum = @sum + @i 
SET @i = @i + 1
END
PRINT @sum

1.2 存储过程

T-SQL : 在访问数据库的应用程序的时候，T-SQL语言是应用程序和SQL SERVER 数据库之间的接口。

什么是存储过程呢？

这些存储在服务器端数据库中供客户端应用程序调用执行的SQL语句就是存储过程。

存储过程优点：　

　　1. 执行速度更快

　　2. 允许模块化程序设计

　　3. 提高系统安全性

　　4. 减少网络流通量

存储过程其实就是存储在服务器数据库上的，由SQL语句和流程控制语句组成的一个预编译的集合，它以一个名字存储并作为一个单元处理执行，可由应用程序调用执行。 存储过程可以接受参数，也可以返回单个或者多个结果。

1.2.1 创建和执行存储过程

// 创建存储过程查询姓名
CREATE PROCEDURE P_s 
AS
BEGIN
    SELECT  Sname from Student
END
GO

// 执行
EXEC P_s

-- 下面是一些基本配置
-- QL-92 标准要求在对空值进行等于 (=) 或不等于 (<>) 比较时取值为 FALSE;
-- 当SET ANSI_NULLS ON 时候，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。
-- 即使column_name 中包含非空值，使用 WHERE column_name < / > NULL 的 SELECT 语句仍会返回零行。
-- 当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵从 SQL-92 标准。
-- 使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。
-- 使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。

-- 当 SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔。
-- 当 SETQUOTED_IDENTIFIER 为 OFF 时，标识符不可加引号，且必须符合所有 Transact-SQL 标识符规则。

-- 这句话的意思： 对空值进行等于 (=) 或不等于 (<>) 比较时取值为 FALSE;
SET ANSI_NULLS ON
GO


-- 这句话的意思： 标识符可以由双引号分隔，而文字必须由单引号分隔。
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Meils
-- Create date: 2018
-- Description:    查询计算机系学生的情况
-- =============================================
CREATE PROCEDURE p_grade 
AS
BEGIN
    SET NOCOUNT ON;
    -- 使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。
    SELECT Sname,Cname,Grade FROM Student s 
    INNER JOIN SC sc ON s.Sno = sc.Sno
    INNER JOIN Course c ON c.Cno = sc.Cno
    WHERE Sdept = '计算机系'
END
GO

// 执行

EXEC p_grade

• 带参数的存储过程

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE p_grade1
    @dept char(20)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT Sname, Cname, Grade, Sdept FROM Student s
    INNER JOIN SC sc ON sc.Sno = s.Sno
    INNER JOIN Course c ON c.Cno = s.Sno
    WHERE Sdept = @dept
END
GO

// 执行
EXEC p_grade '计算机系'
GO

• 带有默认参数的存储过程

CREATE PROCEDURE p_grade3
    @dept char(20) , @cname char(20) = '计算机文化' 
AS
BEGIN
    SELECT Sname,Cname, Grade FROM Student s
    INNER JOIN SC sc ON sc.Sno = s.Sno
    INNER JOIN Course c ON sc.Cno = c.Cno
    WHERE Sdept = @dept
    AND Cname = @cname
END
GO


// 执行
EXEC p_grade3 '信息系', 'Node'
go


EXEC p_grade3 '信息系'
go


// 或者
EXEC p_grade3  @dept = '信息系' , @Cname = '计算机文化'

go

• 多个输入参数，并且都有默认值

CREATE PROCEDURE Student_info 
    @dept char(20) = '计算机系' ,@sex char(2) = '男', @age int = 17
AS
BEGIN
    
    SELECT * FROM Student
    WHERE Sdept = @dept
    AND Ssex = @sex
    AND Sage > @age
END
GO

// 执行可以分为多种情况。
// 如果传入参数，有些情况下只能使用键值对的形式了（按参数名传值）

• 含有输出参数的存储过程

CREATE PROCEDURE outputa3
    @a1 int , @a2 int, @a3 int output
AS

BEGIN
    SET @a3 = @a2 + @a1
END
GO
// 执行

DECLARE @a3 int

EXEC outputa3 3 , 7 ,@a3 output
PRINT @a3

CREATE PROCEDURE avg_grade
    @cn char(20), @avg int output
AS
BEGIN
    SELECT @avg = avg(Grade) FROM SC JOIN Course c ON c.Cno = SC.Cno 
    WHERE Cname = @cn
END
GO
// 执行

DECLARE @avg int
EXEC avg_grade '计算机文化', @avg output
PRINT @avg
go

• 含有多个输出参数

CREATE PROCEDURE avg_grade1
    @cn char(20), @avg int output , @count int output 
AS
BEGIN
    SELECT @avg = avg(Grade) , @count = count(*) FROM SC JOIN Course c ON c.Cno = SC.Cno 
    WHERE Cname = @cn
END
GO

// 执行
DECLARE @avg int , @count int 
EXEC avg_grade1 '计算机文化', @avg output , @count output
SELECT @avg AS 平均成绩 , @count AS 选课人数
go

CREATE PROC UPDATE_GRADE 
@cno char(6), @grade int 
AS 
IF @grade BETWEEN 1 AND 20
    UPDATE Course SET grade @grade
    WHERE Cno = @cno

1.2.2 查看和修改存储过程

• 修改

修改和创建唯一不同的语法结构就是修改是ALTER PROC[EDURE]

ALTER PROCEDURE [dbo].[avg_grade]
    @cn char(20), @avg int output
AS
BEGIN
    SELECT @avg = avg(Grade) FROM SC JOIN Course c ON c.Cno = SC.Cno 
    WHERE Cname = @cn
END

• 删除

DROP PROC p_grade

DROP PROC p_grade , p_grade_1

本实例基于学生信息表、课程表、选课表。感谢您的支持