数据库笔记11：创建与管理存储过程
【摘要】
/*****************************
第十一单元 创建与管理存储过程
******************************/
/*
知识点学习：参看教材P210.
1、什么叫存储过程？
2、存储过程的分类：用户...
/*****************************
第十一单元 创建与管理存储过程
******************************/
/*
知识点学习：参看教材P210.
1、什么叫存储过程？
2、存储过程的分类：用户自定义存储过程、系统存储过程、扩展存储过程
3、使用存储过程有什么好处？
4、存储过程与视图的比较。
*/
/*
任务一、创建和执行不带参数的存储过程（procedure with no argument）
语法格式：
CREATE PROCEDURE 存储过程名
[WITH ENCRYPTION]
AS
SQL语句
*/
--1. 针对Student表，创建一个存储过程p_student，返回Student表中班级号为20000003的所有记录。
CREATE PROCEDURE p_student
AS
SELECT *
FROM Student
WHERE ClassNo = '20000003' ;
--2. 执行刚才所创建的存储过程p_student。
p_student -- 执行存储过程
--3. 利用SSMS查看存储过程p_student的属性
-- 在对象资源管理器窗口，选中数据库Xk，点开选择“可编程性”，点开存储过程，右击dbo.p_student，选择“修改”命令。
--4. 修改存储过程p_student的属性，字段标题设置为中文
ALTER PROCEDURE p_student
AS
SELECT StuNo 学号 , ClassNo 班级号 , StuName 姓名 , Pwd 选课密码
FROM Student
WHERE ClassNo = '20000003' ;
--5. 执行修改后的存储过程p_student
p_student
--课堂练习：针对Student表、StuCou表与Course表，创建存储过程p_StuCou，显示学生选课情况，包含学号，姓名，课程名三个字段
CREATE PROCEDURE p_StuCou
AS
SELECT Student . StuNo 学号 , StuName 姓名 , CouName 课程名
FROM Student INNER JOIN StuCou INNER JOIN Course
ON StuCou . CouNo = Course . CouNo ON Student . StuNo = StuCou . StuNo ;
--6. 执行存储过程p_StuCou
p_StuCou
/*
为什么要创建带参数的存储过程呢？主要目的是增加灵活性。
任务二、创建和执行带参数的存储过程（procedure with arguments）
语法格式：
CREATE PROCEDURE 存储过程名
@参数名 类型[=缺省值]
[WITH ENCRYPTION]
[WITH RECOMPILE]
AS
SQL语句
*/
--1. 针对Student表，创建存储过程p_StudentByClassNo，可以根据班级号来查询学生信息
CREATE PROCEDURE p_StudentByClassNo
@ ClassNo nvarchar ( 8 )= '20000001' -- 定义形参，设置类型与缺省值
AS
SELECT StuNo 学号 , ClassNo 班级号 , StuName 姓名 , Pwd 选课密码
FROM Student
WHERE ClassNo = @ ClassNo ;
--2. 执行存储过程p_StudentByClassNo，不传参数（实参）
p_StudentByClassNo
--问题：创建一个带参数的存储过程，但是不给参数设置缺省值，调用存储过程时，不传参数，结果会怎样？
--3. 执行存储过程p_StudentByClassNo，传参数（实参）
--（1）使用参数名来传值
p_StudentByClassNo @ ClassNo = '20000002'
--该方式的缺点，必须要知道存储过程的参数名。利用按参数位置来传值就可以避免这个缺点，类似于Java的方法调用。
--（2）按参数位置来传值
p_StudentByClassNo '20000004'
--课堂练习：针对Student表、StuCou表与Course表，创建存储过程p_CourseByStuNoCouNo，
-- 按学号和课程号来查询学生选课情况，只包含学号，姓名，课程名三个字段。
CREATE PROCEDURE p_CourseByStuNoCouNo
@ StuNo nvarchar ( 8 )= '00000001' ,
@ CouNo nvarchar ( 3 )= '001'
AS
SELECT Student . StuNo 学号 , StuName 姓名 , CouName 课程名
FROM Student INNER JOIN StuCou INNER JOIN Course
ON StuCou . CouNo = Course . CouNo ON Student . StuNo = StuCou . StuNo
WHERE Student . StuNo = @ StuNo AND Course . CouNo = @ CouNo ;
--不传参数，调用存储过程p_CourseByStuNoCouNo
p_CourseByStuNoCouNo
--传参数，调用存储过程p_CourseByStuNoCouNo
p_CourseByStuNoCouNo '00000008' , '005'
p_CourseByStuNoCouNo '00200008' , '105'
/*
任务三、创建和执行带输出参数的存储过程
语法格式：
CREATE PROCEDURE 存储过程名
@输出参数名 类型[=缺省值] OUTPUT
AS
SQL语句
*/
--1. 针对Student表，创建存储过程p_StudentNumByClassNo，根据班级号统计该班人数，并将统计人数返回给用户（类似Java有返回值的方法）
CREATE PROCEDURE p_StudentNumByClassNo
@ ClassNo nvarchar ( 8 ), @ StudentNum smallint OUTPUT
AS
SET @ StudentNum =
( SELECT COUNT (*)
FROM Student
WHERE ClassNo = @ ClassNo )
PRINT '班级号为[' + @ ClassNo + ']的学生人数：' + CONVERT ( nvarchar , @ StudentNum )
GO
--2. 执行存储过程p_StudentNumByClassNo，传入参数'20000002'
DECLARE @ ClassNo nvarchar ( 8 ), @ StudentNum smallint
SET @ ClassNo = '20000002'
EXEC p_StudentNumByClassNo @ ClassNo , @ StudentNum OUTPUT
PRINT '班级号为[' + @ ClassNo + ']的学生人数：' + CONVERT ( nvarchar , @ StudentNum )
--说明：实参的名称可以跟形参名称不相同
DECLARE @ x nvarchar ( 8 ), @ y smallint
SET @ x = '20000002'
EXEC p_StudentNumByClassNo @ x , @ y OUTPUT
PRINT '班级号为[' + @ x + ']的学生人数：' + CONVERT ( nvarchar , @ y )
/*
任务四、修改存储过程
可以修改存储过程的属性，比如是否要加密，是否要重新编译
*/
--1. 修改存储过程p_StudentNumByClassNo的属性，设置为加密，去掉输出语句。
ALTER PROCEDURE p_StudentNumByClassNo
@ ClassNo nvarchar ( 8 ), @ StudentNum smallint OUTPUT
WITH ENCRYPTION
AS
SET @ StudentNum =
( SELECT COUNT (*)
FROM Student
WHERE ClassNo = @ ClassNo )
/*
任务五、重命名存储过程
*/
--1. 将存储过程p_StudentNumByClassNo改名为p_StuNum。
sp_rename 'p_StudentNumByClassNo' , 'p_StuNum'
/*
任务六、删除存储过程
*/
--1. 将存储过程p_StuNum删除
DROP PROCEDURE p_StuNum
/*
任务七、重新编译存储过程
*/
--1. 在创建存储过程时设置重编译选项
CREATE PROCEDURE p_Student
WITH RECOMPILE
AS
SELECT *
FROM Student
WHERE StuName LIKE '张%'
EXEC p_Student
--2. 在创建时不设置重编译选项，而在执行时设置重编译选项
CREATE PROCEDURE p_StudentByStuNo
@ StuNo nvarchar ( 8 )= '00000001'
AS
SELECT StuNo 学号 , ClassNo 班级号 , StuName 姓名 , Pwd 选课密码
FROM Student
WHERE StuNo = @ StuNo
EXEC p_StudentByStuNo '00000005' WITH RECOMPILE
--3. 在创建时不设置重编译选项，通过系统存储过程来设定重编译选项
EXEC sp_recompile Student -- Student表的存储过程与触发器会在下次执行时重编译
文章来源: howard2005.blog.csdn.net，作者：howard2005，版权归原作者所有，如需转载，请联系作者。
原文链接：howard2005.blog.csdn.net/article/details/79451291
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）