第六章存储过程总结

最新推荐文章于 2021-01-27 11:38:37 发布

原创最新推荐文章于 2021-01-27 11:38:37 发布 · 224 阅读

0 ·

CC 4.0 BY-SA版权

本文详细介绍了存储过程的概念、优点及分类，包括系统存储过程和自定义存储过程的创建、调用方法，并提供了丰富的示例代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

第六章存储过程

定义：

存储过程类似于C#中的函数或JAVA中的方法，主要用来执行管理任务或，应用复杂的业务规则，不仅可以带参数还可以返回结果，它可以包含数据操纵语句，变量，逻辑控制语句等。

优点：

1.允许模块化程序设计，一次创建多次使用，并可独立于程序源代码而单独修改。

2.执行速度更快

3.减少网络流通量，一个需要数百行的T-SQL代码的操作可以由一条存储过程实现。

4.提高系统安全性，存储过程的定义文本可被加密，使用户不能查看其内容，可将存储过程作为用户取存数据的管道，取代原有数据表操作。

分类：

系统存储过程：

EXEC sp_databases 列出当前系统中的数据库
EXEC sp_renamedb 'student','students' 修改数据库的名称(单用户访问)
USE students 修改当前数据库
GO
EXEC sp_tables 当前数据库中查询的对象的列表
EXEC sp_columns stuInfo 返回某个表的列的信息
EXEC sp_help stuInfo 查看表stuInfo的信息
EXEC sp_helpconstraint stuInfo 查看表stuInfo的约束
EXEC sp_helpindex stuMarks 查看表stuMarks的索引
EXEC sp_helptext 'view_stuInfo_stuMarks' 查看视图的语句文本
EXEC sp_stored_procedures 查看当前数据库中的存储过程

解决错误(2008数据库cmd不能执行时候解决方案)
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;

EXEC xp_cmdshell 'mkdir d:\Back',NO_OUTPUT 创建文件夹
EXEC xp_cmdshell 'dir d:\Back' 显示文件夹信息
EXEC xp_cmdshell 'del d:\Back' 删除一个或数个文件

自定义存储过程：

语法：

create proc[存储过程名]

[@参数数据类型[=默认值 output],

@参数n 数据类型[=默认值 output]]

T-SQL语句

调用：

exec/execute 存储过程名 [参数]

创建不带参数的存储过程：

IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_GetAllStuMark')
       DROP PROC Proc_GetAllStuMark
GO

CREATE PROC Proc_GetAllStuMark
AS
       SELECT a.stuid,a.StuName, b.Subject, b.Score
       FROM StuInfo a, StuMarks b
       WHERE a.StuID=b.StuID
GO

执行存储过程
EXEC Proc_GetAllStuMark
EXECUTE Proc_GetAllStuMark

创建带有参数的存储过程：

输入参数：用于向存储过程传入值，类似C#语言的按值传递；

输出参数：用于在调用存储过程后，返回结果，类似C#语言的按引用传递；

示例：

根据学生姓名,获得学生成绩
IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_GetStuMarkByStuName')
DROP PROC Proc_GetStuMarkByStuName
GO

CREATE PROC Proc_GetStuMarkByStuName @stuname VARCHAR(20)
AS
        SELECT a.StuName, b.Subject, b.Score
       FROM StuInfo a, StuMarks b
       WHERE a.StuID=b.StuID
       AND a.StuName=@stuname
GO

EXEC Proc_GetStuMarkByStuName '李四'

创建参数有默认值的存储过程：

添加学生信息表的数据
IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_InsertStuInfo')
DROP PROC Proc_InsertStuInfo
GO
CREATE PROC Proc_InsertStuInfo @stuname varchar(20),@stusex char(2) = '男'
AS
       INSERT INTO StuInfo (StuName, StuSex)
       VALUES (@stuname, @stusex)
GO

调用参数有默认值的存储过程
EXEC Proc_InsertStuInfo '唐僧'
EXEC Proc_InsertStuInfo '白骨精','女'

创建带输出类型参数的存储过程：

注意：

输出参数，必须使用变量。

如果要获得输出参数的值，那么在调用时，必须说明该参数为输出参数。

输出参数同时也是输入参数，调用时，也可以给参数赋值。

如果参数中包含有输出参数，其他参数将不能使用默认值，调用时必须赋值。

示例：

根据学生姓名查找学员SQL分数
IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_GetCMarkByStuName')
DROP PROC Proc_GetCMarkByStuName
GO
CREATE PROC Proc_GetCMarkByStuName @stuname VARCHAR(20),@cmark int OUTPUT
AS
       SELECT @cmark = b.Score FROM StuInfo a, StuMarks b
       WHERE a.StuID = b.StuID
       AND b.Subject = 'SQL' AND a.StuName = @stuname
GO

调用该存储过程
DECLARE @cmark INT
EXEC Proc_GetCMarkByStuName '李四', @cmark OUTPUT
PRINT '李四的SQL分数为:' + CONVERT(VARCHAR, @cmark)

异常：

raiserror语句：

可指定严重级别；

设置系统变量@@error；

记录所发生的错误等。

语法：

raiserror (错误消息, 严重级别(0—18), 状态(1—127))

示例：

IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_Devide')
DROP PROC Proc_Devide
GO

CREATE PROC Proc_Devide @a int, @b int
AS
DECLARE @c INT
IF (@b = 0)
BEGIN
           RAISERROR ('以零作除数错误', 15, 2)
           RETURN
END
SET @c = @a / @b
GO

EXEC Proc_Devide 10, 0

print '错误编号' + CONVERT(VARCHAR, @@ERROR)