--上课内容：第七章 存储过程及触发器
-- 7.1 存储过程的创建和使用
-- 7.1.1 存储过程的概念
-- 存储过程是一种数据库对象
-- 存储过程创建时就被编译和优化，调用一次以后，就保存在内存，下次调用直接执行，执行起来比单个语句快
-- 可以将某个特定任务的代码段写在存储过程里，通过用户定义的存储过程名进行多次调用。
-- 特点：
-- 1. 可以在一个存储过程里执行一系列 SQL 语句
-- 2. 存储过程之间可以相互调用
-- 3. 存储过程可以接受输入参数并以输出参数的形式将多个值返回至调用过程或批处理
-- 4. 存储过程向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）
-- 功能：
-- 1. 通过存储过程的使用，可以简化复杂 SQL 语句
-- 2. 存储过程可以被多个用户共享和重用
-- 3. 可以加快程序的运行速度
-- 4. 可以提高数据库的安全性
-- 创建存储过程的原则：
-- 1. 只能在当前数据库中创建存储过程。
-- 2. 数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。
-- 3. 存储过程是数据库对象，其名称必须遵守标识符命名规则。
-- 4. 存储过程可以根据表、视图来创建
-- 5. 不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。
-- 6. 创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值
-- 分类：
-- 1. 系统存储过程：系统自动创建的，存储在master数据库里
-- 2. 用户自定义的存储过程
-- 3. 临时存储过程：
-- 7.1.2 存储过程的分类和创建方法
-- 语法：
-- 简单存储过程：
CREATE PROCEDURE 存储过程名 AS 查询语句
-- 带参数的存储过程
CREATE PROCEDURE 存储过程名 [@局部变量名 数据类型] AS 查询语句(要使用局部变量)
-- 带返回值的存储过程
CREATE PROCEDURE 存储过程名 [@局部变量名 数据类型] [OUTPUT] AS 查询语句(要使用局部变量)
-- 7.1.2.1 创建不带参数的存储过程
CREATE PROCEDURE stu_pro1 AS select * from exam
-- 执行该存储过程
stu_pro1
execute stu_pro1
-- 修改存储过程
ALTER PROCEDURE stu_pro1 AS select * from exam where t_number='20040301'
-- 7.1.2.2 创建带参数简单的存储过程
CREATE PROCEDURE stu_pro3 @tnum char(8) AS select * from exam where t_number=@tnum
-- 执行该存储过程
stu_pro3 '20040302'
execute stu_pro3 '20040301'
execute stu_pro3 @tnum='20040301'
-- 我们经常把需要复杂计算的工作交给计算机来做，但做法是要你预先写好，编译好的
-- 7.1.2.3 创建带参数的存储过程,并根据执行结果返回不同的值
-- OUTPUT 返回参数，将信息返回给调用过程
CREATE PROCEDURE stu_pro4 @tnum char(8),@cnum char(6),@grade char(10) output
AS
select @grade=t_grade from exam where t_number=@tnum and c_number=@cnum
-- 执行
declare @tnum char(8),@cnum char(6),@grade char(10)
select @tnum=20040301,@cnum=100101
execute stu_pro4 @tnum,@cnum,@grade output
select @tnum as '学号',@cnum as '课程号', @grade as '成绩'
-- 我们还可以返回一个存储过程在执行过程中是否出错的信息
-- 例：带多个参数，有返回值，又可以返回程序执行成功与否的存储过程
CREATE PROCEDURE stu_pro5 @tnum char(8),@cnum char(6),@grade char(10) output
AS
declare @errorvalue int
set @errorvalue=0
select @grade=t_grade from exam where t_number=@tnum and c_number=@cnum
if (@@ERROR0)
set @errorvalue=@@ERROR
return @errorvalue
-- 执行
declare @returnvalue int,@tnum char(8),@cnum char(6),@grade char(10)
select @tnum=20040301,@cnum=100101
execute @returnvalue=stu_pro5 @tnum,@cnum,@grade output
select @returnvalue as '返回是否出错'
select @grade as '学号为20040301，课程号为100101的成绩'
-- 7.1.3 查看stu_pro3信息sp_help stu_pro3
-- 查看存储过程信息
sp_help stu_pro1
-- 查看stu_pro1定义文本
sp_helptext stu_pro1
-- WITH ENCRYPTION 参数
CREATE PROCEDURE stu_pro1 AS select * from exam
ALTER PROCEDURE stu_pro1 WITH ENCRYPTION AS select * from exam
-- 注意：WITH ENCRYPTION 和视图的一样，在修改时没写就是默认可以让他人看到你存储过程的定义
-- 查看存储过程目录信息
sp_stored_procedures stu_pro3
-- 查看存储过程相关性
sp_depends stu_pro3
-- 7.1.4 重命名存储过程
sp_rename 旧存储过程名，新存储过程名
sp_rename stu_pro1,stu_pro
-- 7.1.5 删除存储过程
Drop procedure stu_pro
-- 准备工作：
create table exam
(
t_number char(8),
c_number char(6),
t_grade decimal(5,2)
)
insert into exam values('20040301','100101',79)
insert into exam values('20040301','100102',88)
insert into exam values('20040302','100101',90)
insert into exam values('20040302','100103',75)
insert into exam values('20040303','100101',79)
insert into exam values('20040303','100102',75)
insert into exam values('20040303','100103',95)
insert into exam values('20040304','100102',43)
insert into exam values('20040304','100103',68)
insert into exam values('20040305','100101',64)
insert into exam values('20040305','100102',87)
insert into exam values('20040305','100103',92)
-- 练习：
-- 1. 创建存储过程pro_exam，显示exam表中的所有记录
-- 2. 执行存储过程pro_exam
-- 3. 查栏存储过程pro_exam的定义文本
-- 4. 修改存储过程pro_exam，加密定义文本，并验证
-- 5. 查看存储过程pro_exam的相关性信息
-- 6. 查看存储过程pro_exam的目录信息
-- 7. 重命名存储过程pro_exam 为 pro_exam1,并查看
-- 8. 删除存储过程pro_exam1
-- 9. 创建一个带有参数的存储过程pro_exam2，该存储过程根据传入的学生编号、课程号显示该学生的考试成绩
-- 10. 执行存储过程pro_exam2,查看学号为'20040301'课程号为'100101'的成绩
-- 11. 使用pubs 数据库authors表，创建一个带有参数的存储过程pro_au，该存储过程传入作者所在的州，显示所有这个州的所有作者信息
-- 12. 执行存储过程pro_au,查看所有state 为 'CA'州的所有作者信息
-- 13. 使用northwind 数据库order details表，创建一个带参数的存储过程pro_sale
-- 该存储过程根据传入的订单号、产品号，计算销售额（=unitprice*quantity*(1-diacount)）
-- 根据程序执行结果返回不同的值，程序成功返回0，程序失败返回错误号
select * from [order details]
-- 14. 执行存储过程pro_sale，计算订单号为10248产品号为11的销售额
-- 练习参考答案：
-- 1. 创建存储过程pro_exam，显示exam表中的所有记录
create procedure pro_exam as select * from exam
-- 2. 执行存储过程pro_exam
pro_exam
-- 或
execute pro_exam
-- 3. 查栏存储过程pro_exam的定义文本
sp_helptext pro_exam
-- 4. 修改存储过程pro_exam，加密定义文本，并验证
alter procedure pro_exam with encryption as select * from exam
sp_helptext pro_exam
-- 5. 查看存储过程pro_exam的相关性信息
sp_depends pro_exam
-- 6. 查看存储过程pro_exam的目录信息
sp_stored_procedures pro_exam
-- 7. 重命名存储过程pro_exam 为 pro_exam1,并查看
sp_rename pro_exam,pro_exam1
sp_help pro_exam1
-- 8. 删除存储过程pro_exam1
drop procedure pro_exam1
-- 9. 创建一个带有参数的存储过程pro_exam2，该存储过程根据传入的学生编号、课程号显示该学生的考试成绩
create procedure pro_exam2 @tnum char(8),@cnum char(6) as select * from exam where t_number=@tnum and c_number=@cnum
-- 10. 执行存储过程pro_exam2,查看学号为'20040301'课程号为'100101'的成绩
pro_exam2 '20040301','100101'
--或
execute pro_exam2 '20040301','100101'
--或
execute pro_exam2 @tnum='20040301',@cnum='100101'
-- 11. 使用pubs 数据库authors表，创建一个带有参数的存储过程pro_au，该存储过程传入作者所在的州，显示所有这个州的所有作者信息
create procedure pro_au @state char(2) as select * from authors where state=@state
-- 12. 执行存储过程pro_au,查看所有state 为 'CA'州的所有作者信息
pro_au 'CA'
--或
execute pro_au 'CA'
--或
execute pro_au @tnum='CA'
-- 13. 使用northwind 数据库order details表，创建一个带参数的存储过程pro_sale
-- 该存储过程根据传入的订单号、产品号，计算销售额（=unitprice*quantity*(1-discount)）
-- 根据程序执行结果返回不同的值，程序成功返回0，程序失败返回错误号
select * from [order details]
CREATE
alter PROCEDURE pro_sale @oid int,@pid int ,@sale decimal(10,2)output
AS
declare @errorvalue int
set @errorvalue=0
select @sale=unitprice*quantity*(1-discount) from [order details] where @oid=orderid and @pid=productid
if (@@ERROR0)
set @errorvalue=@@ERROR
return @errorvalue
-- 14. 执行存储过程pro_sale，计算订单号为10248产品号为11的销售额
declare @returnvalue int,@oid int,@pid int,@sale decimal(10,2)
select @oid=10248,@pid=11
execute @returnvalue=pro_sale @oid,@pid,@sale output
select @returnvalue as '返回是否出错'
select @sale as '销售额'
