单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,SQL Server 2005,数据库应用技术,SQL Server 2005,数据库应用技术,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,第,09,章 销售管理数据库中存储过程的应用,SQL Server 2005,数据库应用技术,技能目标,理解存储过程概念以及存储过程的作用；,学会创建、删除、修改存储过程；,学会根据实际需要设计销售管理数据库中的存储过程。,SQL Server 2005,数据库应用技术,知识目标,理解存储过程的作用；,了解系统存储过程和扩展存储过程；,掌握存储过程的基本类型；,掌握创建、删除、修改和加密存储过程；,掌握执行各类存储过程,SQL Server 2005,数据库应用技术,存储过程的概念,存储过程（,Stored Procedure,）是一组完成特定功能的,Transact-SQL,语句集,，经,编译后存储在数据库,中，用户调用过程名和给出参数来调用它们,.,特点：,完成特定功能,多条语句组成的程序,存放在数据库中（服务器）,由客户机调用,可以带参数，也可以不带参数,可以有返回值，也可以没有,SQL Server 2005,数据库应用技术,为什么要建立存储过程？,数据库服务器,客户机,实现相同功能的,SQL,语句集,客户机,实现相同功能的,SQL,语句集,缺点：,1.,相同的,SQL,语句要重复书写,2.,传输网络面临较大压力,3.,执行效率低,4.,存在安全隐患,SQL Server 2005,数据库应用技术,为什么要建立存储过程？,数据库服务器,存储过程,客户机,调用存储过程实现特定功能,客户机,优点：,1.,直接调用特定存储过程实现某种功能，不需要用户自己书写,SQL,语句,2.,存储过程存放在服务器上，不需网络传输,3.,存储过程经过编译和优化，执行效率高,4.,安全性高,调用存储过程实现特定功能,SQL Server 2005,数据库应用技术,存储过程的优点,允许模块化程序设计,存储过程由有数据库专长的技术人员编写。可以无限次调用,存储过程独立与程序源代码，维护方便,执行速度快,存储过程经过编译,存储过程经过优化,批处理的,SQL,语句每次均需要编译和优化,有效降低网络流量,只需通过网络发送一条调用存储过程的语句，不需要传递大量的,SQL,语句代码,提高数据库的安全性,存储过程具有安全性和所有权连接,存储过程可以附加安全证书,SQL Server 2005,数据库应用技术,存储过程的分类,系统存储过程,用户自定义存储过程,扩展存储过程,SQL Server 2005,数据库应用技术,存储过程的分类,(,续,),系统存储过程,完成,SQL Server2005,的许多过来活动,以,sp_,开头,物理上存储在数据库的,resource,中，逻辑上存在在每个数据库的,SYS,框架中,SQL Server 2005,数据库应用技术,存储过程的分类（续）,用户自定义存储过程,用户自定义存储过程是指封装的由用户创建、能完成特定功能的、可重用代码的模块或例程。,扩展存储过程,扩展存储过程是指使用编程语言（例如,C,）创建自己的外部例程，是指,Microsoft SQL Server,的实例可以动态加载和运行的,DLL,。,SQL Server 2005,数据库应用技术,存储过程语法格式,语法格式：,CREATE PROC|PROCEDURE,存储过程名,参数名称 参数数据类型,=,参数的默认值,OUTPUT ,.n,WITH ENCRYPTION,WITH RECOMPILE,AS,sql_statement,参数：,参数名称：存储过程可以没有参数。也可以声明一个或多个参数，参数名称必须,作为第一个字符。参数后面带,OUTPUT,，表示为输出参数。,WITH ENCRYPTION,：对存储过程加密，其他用户无法查看存储过程的定义。,WITH RECOMPILE,：每次执行该存储过程都重新进行编译。,sql_statemen,：该存储过程中定义的编程语句。,SQL Server 2005,数据库应用技术,存储过程的组成,存储过程的定义中包含如下的两个主要组成部分。,（,1,）过程名称及其参数的说明：包括所有的输入参数以及传给调用者的输出参数。,（,2,）过程的主体：也称为过程体，针对数据库的操作语句（,Transact-SQL,语句），包括调用其它存储过程的语句。,SQL Server 2005,数据库应用技术,不带参数的存储过程,创建不带参数的存储过程,语法格式：,CREATE PROC|PROCEDURE,存储过程名,WITH ENCRYPTION WITH RECOMPILE,AS,sql_statement,SQL Server 2005,数据库应用技术,不带参数的存储过程,【,例,9.1】,创建一个名为,Cu_information,的存储过程，用于查询客户的信息。,use student,CREATE PROCEDURE Cu_information,AS,SELECT*FROM Customer,SQL Server 2005,数据库应用技术,不带参数的存储过程（续）,执行不带参数的存储过程,语法结构如下,:,EXEC,存储过程名,【,例,9.3】,执行创建的,Cu_Information,存储过程。,EXEC Cu_Information,SQL Server 2005,数据库应用技术,创建存储过程的步骤,实现过程体的功能,构建实现特定功能的,SQL,语句,创建存储过程,CREATE PROCEDURE,AS,SQL,语句集,验证准确性,EXEC,SQL Server 2005,数据库应用技术,带输入参数的存储过程,创建带输入参数的存储过程,CREATE PROC|PROCEDURE,存储过程名,参数名称 参数数据类型,=,参数的默认值,.n,WITH ENCRYPTION,WITH RECOMPILE,AS,sql_statement,SQL Server 2005,数据库应用技术,带输入参数的存储过程,【,例,9.4】,创建一个存储过程，实现根据订单号获取该订单的信息的功能。,CREATE PROCEDURE OrderDetail,OrderID INT,AS,SELECT*FROM Sell_Order,WHERE SellOrderId=OrderID,SQL Server 2005,数据库应用技术,带输入参数的存储过程,【,例,9.6】,创建名为,listEmployee,的存储过程，其功能为：在员工表,employee,中查找符合性别和超过指定工资条件的员工详细信息。,CREATE PROCEDURE listEmployee,sex varchar(2),salary money,AS,SELECT*,FROM employee,WHERE sex=sex and salarysalary,SQL Server 2005,数据库应用技术,带输入参数的存储过程（续）,执行输入参数的存储过程两种方法：,使用参数名传递参数值,EXEC,存储过程名,参数名,=,参数值,DEFAULT,，,n,按位置传递参数值,EXEC,存储过程名,参数值,1,，参数值,2,，,SQL Server 2005,数据库应用技术,带输入参数的存储过程（续）,【,例,9.7】,使用,【,例,9.5】,中创建的存储过程,customer_order,，获取“三川实业有限公司”的信息，包括联系人姓名、联系方式以及该公司订购产品的明细表。,代码如下：,EXEC customer_order,customername=,三川实业有限公司,SQL Server 2005,数据库应用技术,带输入参数的存储过程（续）,【,例,9.8】,利用存储过程,listEmployee,，查找工资超过,4000,元的的男员工和工资超过,3000,元女员工的详细信息。,代码如下：,EXEC listEmployee sex=,男,salary=4000,EXEC listEmployee salary=3500,sex=,女,SQL Server 2005,数据库应用技术,带输入参数的存储过程（续）,【,例,9.9】,按位置传递执行存储过程,listEmployee,，查找工资超过,4000,元的的男员工和工资超过,3000,元女员工的详细信息。,代码如下：,EXEC listEmployee ,男,4000,EXEC listEmployee ,女,3500,SQL Server 2005,数据库应用技术,带输入参数的存储过程（续）,【,例,9.11】,利用,Name_Employee,存储过程查询所有员工信息和姓王的员工信息,EXEC Name_Employee,查询所有员工信息,EXEC Name_Employee,王,%,查询姓王的员工信息,SQL Server 2005,数据库应用技术,带输出参数的存储过程,【,例,9.12】,创建带返回参数的存储过程求两个整数的和。,代码如下：,CREATE PROCEDURE PRO_SUM,N1 INT,N2 INT,RESULT INT OUTPUT,AS,SET RESULT=N1+N2,SQL Server 2005,数据库应用技术,带输出参数的存储过程,【,例,9.13】,执行,【,例,9.11】,创建的,PRO_SUM,存储过程。,代码如下：,DECLARE ANSWER INT,EXEC PRO_SUM 20,69,ANSWER OUTPUT,select ANSWER,结果,SQL Server 2005,数据库应用技术,查看存储过程,sp_help,用于显示存储过程的参数及其数据类型，语法格式如下：,sp_help objname=,存储过程名,sp_helptext,用于显示存储过程的源代码，语法格式如下：,sp_helptext objname=,存储过程,SQL Server 2005,数据库应用技术,查看存储过程,【,例,9.14】,查看,customer_order,存储过程的参数和数据类型。,代码如下：,USE CompanySales,GO,sp_help customer_order,SQL Server 2005,数据库应用技术,查看存储过程,【,例,9.15】,查看,customer_order,存储过程的源代码。,代码如下：,USE CompanySales,GO,sp_helptext customer_order,SQL Server 2005,数据库应用技术,删除用户存储过程,删除用户存储过程可以使用,DROP,命令，语法格式如下：,DROP PROC|PROCEDURE,存储过程名,.n,SQL Server 2005,数据库应用技术,删除用户存储过程,【,例,9.16】,删除,PRO_SUM,存储过程。,代码如下：,USE CompanySales,GO,DROP PROCEDURE PRO_SUM,SQL Server 2005,数据库应用技术,修改存储过程,ALTER PROCEDURE,语句的语法格式如下：,ALTER PROC|PROCEDURE,存储过程名,参数名称 参数数据类型,=,参数的默认值,OUTPUT ,.n,WITH ENCRYPTION,WITH RECOMPILE,AS,sql_statement,SQL Server 2005,数据库应用技术,系统存储过程,常用系统存储过程：,sp_tables,：返回可在当前环境中查询的对象列表。这代表可在,FROM,子句中出现的任何对象。,sp_stored_procedures,：返回当前环境中的存储过程列表。,sp_rename,：在当前数据库中更改用户创建对象的名称。此对象可以是表、索引、列、别名数据类型。,sp_rena