数据库编程存储过程的创建.ppt

1 第八章 数据库编程 2 应用程序和数据库之间的主要编程接口： SQL语言 1.在客户端存储代码，并创建编写访问数据库服务器发送 SQL语句（比如在 C#、 Java等客户端编程语言中嵌入 访问数据库的 SQL语句） 2.将 sql语句存储在数据库服务器端，然后后应用程序调 用执行这些 SQL语句。 3 第 8章 存储过程 8.1 存储过程概述 8.2 创建管理无参存储过程 8.3 创建管理带参的存储过程 4 8.1 存储过程概述 1.存储过程概念 存储过程是为了实现某个特定任务，由一组预先编译好的 SQL语句组成，将其放在服务器上，由用户通过指定存储过程 的名字来执行的一种数据库对象。 2.存储过程类型 系统存储过程 扩展存储过程 用户自定义存储过程 系统存储过程存储以 SP_为前缀 ,是由 SQL Server2005自己创建、管理和使用的一种 特殊的存储过程，不能对其进行修改或删除。 如 Sp_helpdb、 Sp_renamedb等。 由用户自行创建的存储过程，可以输入参数、 向客户端返回表格或结果、消息等，也可以 返回输出函数。 5 8.2 无参 存储过程的创建 使用 SQL语句 创建存储过程 1） 语法格式如下： CREATE PROC EDURE procedure_name AS sql_statement .n 2） 语法注释： procedure_name：新建存储过程的名称 ， 其名称必须符 合标识符命名规则 ， 且对于数据库及其所有者必须唯一 。 sql_statement ：指存储过程中的任意数目和类型的 Transact-SQL语句 。 6 8.2无参 存储过程的创建 例 1：返回 200030000041号学生的成绩 ， 不使用参数 。 代码如下： CREATE PROCEDURE student_info AS SELECT * FROM 学生信息 WHERE 学号 =200030000041 7 创建存储过程的注意事项 只能在当前数据库中创建存储过程 。 数据库的所有者可以创建存储过程 ， 也可以授权其他用 户创建存储过程 。 存储过程是数据库对象 ， 其名称必须遵守标识符命名规 则 。 不能将 CREATE PROCEDURE 语句与其它 SQL 语句 组合到单个批处理中 。 创建存储过程时 ， 应指定所有输入参数和向调用过程或 批处理返回的输出参数 、 执行数据库操作的编程语句和返 回至调用过程或批处理以表明成功或失败的状态值 。 8 8.2 无参 存储过程的执行 对存储在服务器上的存储过程 ， 可以使用 EXECUTE 命令或其名称执行它 ， 其语法格式如下： EXEC UTE procedure_name 对上例的存储过程 pr_searchorddate的执行语句如下： EXEC student_info 9 8.3创建 带参 存储过程的语法 1） 语法格式如下： CREATE PROCDURE procedure_name parameter data_type =defaultOUTPUT,n WITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION AS Sql_statement 10 8.3创建 带参 存储过程的语法 2） 语法注释 ： parameter：存储过程中的输入和输出参数 。 data_type：参数的数据类型 。 DEFAULT：表示参数的默认值 。 如果定义了默认值 ， 则在调用存储过程时 ， 可以省略该参数的值 。 OUTPUT：表明参数是返回参数 。 该选项的值可以返回 给 EXECUTE。 11 带参数的存储过程 例子：查询某个指定系学生的考试情况，列出学生的姓名、 所在系、课程名和考试成绩。 create procedure p_StudentGrade1 dept char(20) as select sname,sdept,cname,grade from student s inner join sc on s.sno=sc.sno inner join course c o=o where sdept=dept 12 带多个输入参数并有默认值的存储过程 例子 1：查询某个学生某门课程的考试成绩，课程的默认 值为 VB” Create proc p_StudentGrade2 sname char(10),cname char(20)=VB As select sname,cname,grade from student s inner join sc on s.sno=sc.sno inner join course c o=o where sname=sname and cname=cname 13 例 2： CREATE PROC deletestu 学号 char(4) AS DELETE 学生信息 WHERE 学号 = 学号 存储过程的执行如下： Exec deletestu 200030000041 14 8.3.2 创建 /执行带输入参数的存储过程 执行带输入参数的存储过程 按位臵传递参数值 在执行存储过程的语句中，直接给出参数的值。当有多个参数时， 给出的参数的顺序与创建执行存储过程的语句中的参数的顺序一致， 即参数传递的顺序就是参数定义的顺序。 例： exec p_StudentGrade2 吴宾，高等数学 通过参数名传递参数值 在执行存储过程的语句中，使用 【 参数名 =参数值 】 的形式给出参 数值。其优点是参数可以以任意顺序给出。 例：例： exec p_StudentGrade2 cname=高等数学 ， sname=吴宾 15 8.3.2 创建 /执行带输入参数的存储过程 在输入参数中使用默认值 在执行存储过程时，如果没有指定参数，则系统运行就 会出错；此时如果希望在执行时不给出参数也能正确运行， 则在创建存储过程时给输入参数指定默认值。 例： exec p_StudentGrade2 吴宾 相当于执行： exec p_StudentGrade2 吴宾， VB 16 8.3.3 存储过程的返回值 存储过程在执行后都会返回一个整型值。如果执行成功， 则返回 0；否则返回 -1到 -99之间的随机数，也可以使用 RETURN语句来指定一个存储过程的返回值。 17 8.3.3 存储过程的返回值 例：在 northwind数据库创建一个存储过程，返回产品表 中的所有产品的库存量。 create proc pr_lier as begin declare fanhuizhi int select fanhuizhi=sum(unitsinstock) from products return fanhuizhi end 18 8.3.3 存储过程的返回值 接受这个返回值必须要用变量来接收 declare jieshouzhi int exec jieshouzhi=pr_lier print jieshouzhi 19 8.3.3 存储过程的返回值 上例中返回的值也可以用 output参数来返回，可以将上 例的存储过程改写为： create proc pr_lier_2 fanhuizhi int output as begin select fanhuizhi=sum(unitsinstock) from products end 20 8.3.3 存储过程的返回值 接收 output的返回值也必须要用变量，如： declare jieshouzhi int exec pr_lier_2 jieshouzhi output print jieshouzhi 注意： 1) return返回的是整数， output可以返回任何数据。 2)如果让 return返回非整数值，在创建存储过程时不会 出错，但是运行存储过程时将会出错。 21 例：统计全体学生人数，并将统计结果用输出参数返回 Create proc p_count total int output As select total=count(*) from student 执行此存储过程 : Declare res int Exec p_count res output Print res 其中 : declare 为变量声明语句 语法格式： declare 局部变量名 数据类型 res :变量名。 print：输出语句，表示将后边变量的值显示在屏幕上 语法格式： print ASCII文本字符串 |局部变量名 |字符串表达 式 22 8.4 查看存储过程 1.使用对象资源管理器查看存储过程 2.使用系统存储过程查看存储过程信息 在 SQL Server 中 ， 可 以 使 用 sp_helptext 、 sp_depends、 sp_help等系统存储过程来查看存储过程的 不同信息 。 1） 使用 sp_helptext查看存储过程的文本信息 . 其语法格式为： sp_helptext 存储过程名 2） 使用 sp_depends查看存储过程的相关性 . 其语法格式为： sp_depends 存储过程名 3） 使用 sp_help查看存储过程的一般信息 . 其语法格式为： sp_help 存储过程名 23 8.5修改存储过程 修改存储过程语法格式为： ALTER PROCDURE procedure_name parameter data_type =defaultOUTPUT,n WITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION AS Sql_statement 24 8.5 修改存储过程 例：修改存储过程 student_info,查询学生信息表中 某个指定学生信息。 其程序清单如下： ALTER PROC student_info 学号 varchar(14) As Select * From 学生信息 Where 学号 =学号 GO 25 8.6 编译存储过程 在我们使用了一次存储过程后，可能会因为某些原因，必须向表中 新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结构。这时， 需要对存储过程进行重新编译， SQL Server提供三种重新编译存储过程的 方法 ： 1、在建立存储过程时设定重新编译 语法格式： CREATE PROCEDURE procedure_name WITH RECOMPILE AS sql_statement 2、在执行存储过程时设定重编译 语法格式： EXECUTE procedure_name WITH RECOMPILE 3、 通过使用系统存储过程设定重编译 语法格式为： EXEC sp_recompile OBJECT（ procedure_name ） 26 8.7 删除存储过程 1.使用 SSMS对象资源管理器删除存储过程 2.使用 DROP PROCEDURE语句删除存储过程 DROP PROCEDURE语句可以一次从当前数据库中将一 个或多个存储过程或过程组删除 ， 其语法格式如下： DROP PROCEDURE 存储过程名称 ,n 例： 删除存储过程 student_info DROP PROCEDURE student_info 27 小结 掌握 1、 掌握存储过程的创建和执行过程； 2、掌握存储过程的输入和输出参数的应用； 3、 掌握管理存储过程的方法 。 理解 理解存储过程的概念；