讲义15：存储过程

--上课内容：第七章 存储过程及触发器-- 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 { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]     [ { @parameter [ type_schema_name. ] data_type }         [ VARYING ] [ = default ] [ [ OUT [ PUT ]     ] [ ,...n ] [ WITH <procedure_option> [ ,...n ][ FOR REPLICATION ] AS { <sql_statement> [;][ ...n ] | <method_specifier> }[;]<procedure_option> ::=     [ ENCRYPTION ]    [ RECOMPILE ]    [ EXECUTE_AS_Clause ]<sql_statement> ::= { [ BEGIN ] statements [ END ] }<method_specifier> ::=EXTERNAL NAME assembly_name.class_name.method_name    -- 7.1.2.1 创建不带参数的简单的存储过程CREATE PROCEDURE stu_pro1 AS select * from exam where t_grade >80-- 执行该存储过程stu_pro1exec stu_pro1execute stu_pro1   -- 7.1.2.2 创建带参数的简单的存储过程   -- 例1：输入学生学号，输出该学生的考试信息CREATE PROCEDURE stu_pro2 @tnum char(8) AS select * from exam where t_number=@tnum-- 执行该存储过程stu_pro2 '20040301'exec stu_pro2 '20040301'exec stu_pro3 @tnum='20040301'   -- 例2：输入学生学号，输出该学生的考试信息CREATE PROCEDURE stu_pro3 @tnum char(8),@cnum char(6) AS select * from exam where t_number=@tnum  and c_number=@cnum-- 执行该存储过程stu_pro3 '20040301','100101'exec stu_pro3 '20040301','100101'exec stu_pro3 @tnum='20040301',@cnum='100101'    -- 7.1.2.3 创建带参数的特定信息的存储过程select * from examCREATE PROCEDURE stu_pro4 @tnum char(8)  = '2004%'AS select * from exam where t_number like @tnum-- 执行该存储过程stu_pro4 exec stu_pro4 '2004%'use northwindgoselect * from employeesCREATE PROCEDURE usp_GetEmployees2     @lastname varchar(40) = 'D%',     @firstname varchar(20) = '%'AS     SELECT LastName, FirstName, Title, HireDate    FROM dbo.Employees    WHERE FirstName LIKE @firstname         AND LastName LIKE @lastname;EXECUTE usp_GetEmployees2;-- OrEXECUTE usp_GetEmployees2 'Ca%';-- OrEXECUTE usp_GetEmployees2 @firstname = '%';-- OrEXECUTE usp_GetEmployees2 'Davolio', 'Nancy';EXECUTE usp_GetEmployees2 'Fuller', 'Andrew';-- OrEXECUTE usp_GetEmployees2 'D%', 'N%';    -- 7.1.2.4 尝试在存储过程中写入除了select以外的语句    -- 例：建立存储过程 add_stu,输入学生的学号、姓名、性别、出生年月select * from stu_infoCREATE PROCEDURE add_stu @tnum char(8),@tname varchar(10),@t_gender char(2),@t_birthday datetime asinsert into stu_info values(@tnum,@tname,@t_gender,@t_birthday)add_stu '20040306','张三','男','19850112'/* 以下不要求 -- 我们经常把需要复杂计算的工作交给计算机来做，但做法是要你预先写好，编译好的    -- 7.1.2.5 创建带参数的存储过程,并根据执行结果返回不同的值     -- 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=100101execute stu_pro4 @tnum,@cnum,@grade outputselect @tnum as '学号',@cnum as '课程号', @grade as '成绩'-- 我们还可以返回一个存储过程在执行过程中是否出错的信息-- 例：带多个参数，有返回值，又可以返回程序执行成功与否的存储过程CREATE PROCEDURE stu_pro5 @tnum char(8),@cnum char(6),@grade char(10) output  AS declare @errorvalue intset @errorvalue=0select  @grade=t_grade from exam where t_number=@tnum and c_number=@cnumif (@@ERROR<>0)  set @errorvalue=@@ERRORreturn @errorvalue-- 执行declare @returnvalue int,@tnum char(8),@cnum char(6),@grade char(10)select @tnum=20040301,@cnum=100101execute @returnvalue=stu_pro5 @tnum,@cnum,@grade outputselect @returnvalue as '返回是否出错'select @grade as '学号为20040301，课程号为100101的成绩'*/  -- 7.1.3 查看stu_pro3信息  -- 查看存储过程信息sp_help  stu_pro1  -- 查看stu_pro1定义文本sp_helptext stu_pro1  -- WITH ENCRYPTION 参数CREATE PROCEDURE stu_pro1 AS select * from examALTER 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-- 使用存储过程自动生成数据练习：-- 创建表t_cardcreate table t_card(card_id bigint not null,--卡号 起始值223310001001 卡号连续card_password int,      --卡密码 随机六位数字card_status char(1),    --卡状态 0 为未使用expire_date datetime,   --截止日期 建卡3年后batchid int             --批号 起始值 1001)