《sqlserver数据库应用与维护教学课件作者翁正秋第9讲存储过程》由会员分享，可在线阅读，更多相关《sqlserver数据库应用与维护教学课件作者翁正秋第9讲存储过程（33页珍藏版）》请在金锄头文库上搜索。
1、第9讲 存储过程,SQL Server数据库应用与维护,SQL Server 2008为例,2,1.SQL Server 2008的安全机制（重点） 2.创建登录账户（重点） 3.创建数据库用户（重点） 4.角色与权限（重难点）,上节回顾,上节:第8讲：用户管理,REVOKE DELETE, INSERT, UPDATE ON 学生表 FROM 用户A CASCADE,抢答：加了CASCADE有什么效果。请举例！,3,课程引入,小老师：“存储过程是什么？”,4,存储过程,可读性？ 修改？,大老师提问1：“编写100行T-SQL语句访问数据库”？,5,存储过程,怎么办？,大老师提问2：“黑客攻击
2、-SQL注入”,6,存储过程,大老师：“存储过程到底是什么？！”,存储过程（Stored Procedure）：是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数来执行它，以实现某个任务。,7,2.1 任务演示 2.2 创建存储过程（重难点） 2.3 执行存储过程（重点） 2.4 存储过程实训任务（重难点） 2.5 删除存储过程（重点） 2.6 浏览存储过程 2.7 修改存储过程（重点） 2.8 存储过程提高（难点）,存储过程的管理,8,学生表,选课表,2.1 任务演示,任务演示： 由于学生的信息变化或学生转学等原因，学生的
3、数据需要进行相应的处理。教务系统决定定期处理一批无效学生记录。 【例如】：学号为“2013020001”的孙雷同学办理退学手续，系统要求删除该学生记录的同时，删除其选课记录。,解决方法： 你能想到什么方案呢？,9,1、直接对学生表和选课表进行修改。（好与坏？） 方法：删除某学生记录的同时，删除其选课记录 分两步： delete from sc where sno=2013020001 delete from student where sno=2013020001 2、创建一个存储过程，以实现对上述的修改。（好与坏？） 假如已经建好了存储过程：del_student 调用：exec del_s
4、tudent 2013020001,假如学生相关的数据表有100多张， 怎么办！,2.1 任务演示,假如有10位学生的数据要删除， 怎么办！,10,2.2 创建存储过程,Create Procedure del_student sno nvarchar(50) as BEGIN delete from sc where sno=sno delete from student where sno=sno END,Create Procedure as ,【例】：学号为“2013020001”的孙雷同学办理退学手续，系统要求删除该学生记录的同时，删除其选课记录。,Create Procedure
5、del_student as BEGIN delete from sc where sno=2013020001 delete from student where sno=2013020001 END,11,执行存储过程：要执行存储过程，只要调用存储过程名即可。 如果过程不是批命令中的第一条语句，则要在过程名前面加上关键字EXEC。这是因为存储过程名不是关键字，当语句在执行过程中遇到存储过程名时，会将其作为上一条语句的一部分处理，因此导致执行出错。 【例】：执行存储过程del_student ： del_student Select * from student del_student Se
6、lect * from student exec del_student,错，因为del_student不是SQL定义的关键字，所以分析器将其解释成了student表的别名，从而导致执行结果出错。,修改为,2.3执行存储过程,执行存储过程 execute ,对,12,中场小结1,存储过程（Stored Procedure）：是在大型数据库系统中，一组为了完成特定功能的SQL 语句集。,1、创建存储过程 Create Procedure AS 2、执行存储过程 Execute ,预编译,指定名称、参数可选,创建存储过程,执行存储过程,完成任务,13,2.4 存储过程实训任务,创建存储过程 Cre
7、ate Procedure 存储过程名 参数1 数据类型 = 默认值 OUTPUT, 参数n 数据类型 = 默认值 OUTPUT AS SQL语句,任务1：没有参数的存储过程,任务2：有输入参数的存储过程,任务3：有参数默认值的存储过程,任务4：有输出参数的存储过程的创建,任务5：有输出参数的存储过程的执行,14,【例】创建一个没有参数的存储过程 create proc test1 as begin -SQL代码省略 end 执行该存储过程 exec test1,任务1：没有参数的存储过程,【上机任务1-1】创建一个没有参数的存储过程StuScoreInfo, 查询以下信息:学号,姓名,性别,
8、课程名称,考试成绩。,15,【例】创建一个有参数的存储过程 create proc test2 value1 int, value2 int as select * from table1 where value between value1 and value2 假设要显示value在1000到2000之间的信息： exec test2 1000，2000,任务2：有输入参数的存储过程,【上机任务1-2】创建一个有参数的存储过程stu_info, 该存储过程根据传入的学生编号在student表中查询此学生的信息.,16,16,【例】设置参数的默认值 Create Proc test3 val
9、ue int=100 as begin -SQL代码省略 end,任务3：有参数默认值的存储过程,【上机任务1-3】创建一个有参数、有默认值的存储过程stu_info_bySex,该存储过程根据传入的学生的性别（默认为：男）查询学生的基本信息。,17,17,如果调用批命令将变量作为参数传入存储过程，而参数在存储过程中被修改，则修改不会传给调用该存储过程的命令，除非生成和执行存储过程时对参数指定OUTPUT关键字 【例】有输出参数的存储过程 Create proc test4 value varchar(50), value_out varchar(50) OUTPUT as select va
10、lue_out =valueX from table1 where value=value return,任务4：有输出参数的存储过程的创建,【上机任务1-4】创建一个有输出参数的存储过程stu_info_out, 该存储过程根据传入的学生学号查询学生的专业，并将学生的所在专业输出（通过speciality参数）。,18,18,调用存储过程要声明一个存储返回值的变量，执行语句还要包括OUTPUT关键字，否则修改无法在调用中反映出来 Declare value_save varchar(50) Exec test4 value=myValue, value_out=value_save OUTP
11、UT Print value_save 【注意】 参数名（这里是value_out ）在表达式左边列出， 而本地变量（ value_save ）则设置为等于输出参数的值，在表达式右边列出。,任务5：有输出参数的存储过程的执行,【上机任务1-5】调用存储过程stu_info_out，查询学号为2013020002的学生的所在专业。并声明一个变量speciality_save,用来存储输出的专业信息。,19,存储过程是什么？ 使用存储过程有什么好处？ 请写出以下存储过程的执行命令，要求num1使用默认值，num2使用65： create procedure test5 num1 int =60,
12、num2 int =70 as ,中场小结2,20,代码阅读:指出下列语句的错误 -定义存储过程 CREATE PROC proc_stuInfo stuName VARCHAR(10), m INT OUTPUT, stuAge=18 INT AS .代码略 -调用存储过程 DECLARE s INT EXEC proc_stuInfo 张三,s,30 EXEC proc_stuInfo 张三,s EXEC proc_stuInfo stuAge=22,stuName=李四,m=s OUTPUT,stuAge INT=18,EXEC proc_stuInfo 张三,s OUTPUT,30,E
13、XEC proc_stuInfo 张三,s OUTPUT,正确,考一考,21,21,格式： Drop Proc Procedure_name 【例】：删除存储过程stu_info Drop Proc stu_info,2.5 删除存储过程,22,22,存储过程源代码保存在系统表syscomments的text列中，可以直接查询该列获取信息。也可以使用系统存储过程sp_helptext获取存储过程的源代码。 【例如】exec sp_helptext stu_info 如果开发人员不让用户浏览源代码，则可以在生成存储过程时加上With Encryption选项，则源代码经过加密处理。 注意：SQL
14、 Server可以内部解密源代码，但该方法使得用户或任何最终用户工具都无法解密源代码,2.6 浏览存储过程,23,23,修改存储过程：Alter Proc的语法和Create Proc语法相同，但其优点是不必删除存储过程再重建，因此，也不必重新指定权限 【例】修改存储过程 Alter Proc stu_info AS begin -SQL代码省略 end,2.7 修改存储过程,24,24,返回过程状态 大多数编程语言能够向调用者返回过程状态，0通常表示成功，SQl Server也不例外。 【例】：返回某本书到目前为止的销量，如果该书不存在，则返回状态值101 Create Proc test6
15、 title varchar(80) as if not exists(select * from titles where title=title) return -101 select ytd_sales from titles where title=title return,如果某本书的书名不存在，则返回 -101,2.8 存储过程提高,25,25,返回过程状态 【例】：执行存储过程test6 Declare status int exec status= test6 title=Life without Fear if status=-101 Print No title with
16、that name found. ,2.8 存储过程提高,26,26,SQL Server 的内部状态码。如果存储过程异常终止，则返回一个状态码，SQL Server保留了1到99的值。,2.8 存储过程提高,27,实现数据插入 插入一个新作者 Create Proc insertauthor (au_id varchar(11), au_lname varchar(40), au_fname varchar(20), phone char(12), contract bit) as insert into authors(au_id, au_lname, au_fname, phone, contract) values(au_id,au_lname,au_fname,phone,
