公司管理数据库系统中存储过程的应用
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
公司管理数据库系统 中存储过程的应用
促成教学目标： 1、能正确理解存储过程的概念 2、会使用企业管理器和Transact-SQL语句 创建存储过程 3、会使用带参数的存储过程
一、存储过程的定义（1）
存储过程是在服务器上的预编译好的SQL语句集。 “预编译好”指的是存储过程在创建时就被编译和优 化，调用一次以后，相关信息就保存在内存中，下次调用 时可以直接执行。 “SQL语句集”指的是declare语句、set语句、select 语等 存储过程类似于编程语言中的函数。将某些需要多次 调用实现某个特定任务的代码段编写成一个过程，将其保 存在数据库中，并由SQL Server服务器通过过程名来调用 这些过程（Procedure）。
系统存储过程sp_helptext，将返回所有与存 储过程有关的代码。 语法： sp_helptext <存储过程名>
【例8】 sp_helptext
product_order_sum
2、修改存储过程
使用企业管理器 用ALTER PROCEDURE语句。 语法: ALTER PROCEDURE <存储过程名称> {<@参数变量> <数据类型>}[VARYING][=默认值] [OUTPUT]] [,...n] [WITH {RECOMPILE| ENCRYPTION| RECOMPILE, ENCRYPTION}] AS Sql语句
任务：
1、在公司管理数据库中创建存储过程 product_order ，
要求实现如下功能：根据产品名称，查询该产品的订货情 况，显示所订产品的产品名、数量、单价。
2 、 在公司管理数据库中创建存储过程 product_employee
要求实现如下功能：根据雇员id，查询该雇员的姓名、性 别、出生日期。
（1）利用SQL语句创建无参数的 存储过程
【例1】创建所有的雇员的接受订单的明细 信息的存储过程。
create proc listOrder as select 姓名,产品名,数量,单价,数量*单价 as 金额 from employee ,p_order where employee.雇员ID=p_order.雇员ID And product.产品ID=p_order.产品ID
Create procedure mathtuor @m1 smallint, @m2 smallint, @result int output As Select @result=@m1*m2
【例5】创建一个指定产品名，返回已订该产 品的所有订单的总金额的存储过程。
Create PROCEDURE product_order_sum @productname varchar(20), @sum_pro int output AS SELECT @sum_pro=sum(p_order.数量*product.单价) FROM p_order, product where p_order.产品ID =product.产品ID
【例2】创建指定姓名的雇员接受订单的明细 信息的存储过程。
create procedure name_list_order @name varchar(8) as select 姓名,产品名,数量,单价,数量*单价 as 金额 from employee ,p_order where employee.雇员ID=p_order.雇员ID And product.产品ID=p_order.产品ID And 姓名=@name
选择新建存储过程对话框（ 创建存储过程（1） 1）
新建存储过程对话框 创建存储过程（2）
创建存储过程（3）
任务：
1、创建名为”项目7_1”的存储过程，其功能是：在 p_order表中，查找订货数量大于50的订单的 所 有信息。 2、创建名为”项目7_2”的存储过程，其功能是：在 employees表中，查找所有女雇员的信息。 3、创建名为”项目7_3”的存储过程，其功能是：在 product表中，查找单价大于2.5元，且库存量大 于300的产品的产品名、单价、库存量信息。 4、创建名为”项目7_4”的存储过程，其功能是：在 custom有信息。
四、执行存储过程
语法： [EXEC[UTE]] { {过程名| @存储过程变量 } [[@参数 =] {参数值 | @变量 [OUTPUT] | [默认值]] [,…n]
【例8】 exec count_employee
1、传递参数两种方法：
按位置传送 通过参数名传送
（1）按位置传送
在执行存储过程语句中，直接给出参数的传 递值。参数传递的顺序就是参数定义的顺序。 语法： [EXECUTE] 存储过程名 [参数值...]
3、使用 WITH ENCRYPTION 选项
对用户隐藏存储过程的文本。
【例6】如果在employee表中查找符合性别和薪水条 件的雇员的详细信息。
CREATE PROCEDURE list @sex varchar(2), @salary money WITH ENCRYPTION as select * from employee where 性别=@sex and 薪水>@salary go exec sp_helptext list 消息： 对象备注已加密。
从存储过程中返回一个或多个值。 语法： @参数名 数据类型[=默认值] OUTPUT @参数名：存储过程的输出参数名，必须以@符号为前缀。 数据类型：该参数的数据类型说明。 OUTPUT：指明输出参数。注意，输出参数必须位于所有输 入参数说明之后。
【例4】创建一个完成乘法运算的的存储过程。
declare @sum int exec product_order_sum '牛奶',@sum output print '牛奶的订单的总金额为：'+cast(@sum as char(8))
结果显示如下： 结果是: 牛奶的订单的总金额为：920
五、管理存储过程
1、显示存储过程 （1）使用企业管理器 （2）使用系统存储过程
创建存储过程的方法：
① 利用SQL Server 企业管理器创建存储过程。
②使用CREATE PROCEDURE命令创建存储过程。
1. 使用企业管理器创建存储过程
（1）在企业管理器中，选择指定的服务器和 数据库，用右键单击要创建存储过程的数据库， 在弹出的快捷菜单中选择“新建”选项，再选择 下一级菜单中的“存储过程…”选项， （ 2 ）在文本框中可以输入创建存储过程的 T_SQL语句，单击“检查语法”，则可以检查语 法是否正确；单击“确定”按钮，即可保存该存 储过程。
一、存储过程的定义（2）
存储过程包含两个主要组成部分：
①过程名称及其参数的说明，包括所有的输入参 数以及传给调用者的输出参数。 ②过程的主体：执行过程的针对数据库的操作语 句（Transact-SQL 语句）。
二、存储过程的特点
有效降低网络流量 增强代码的重用性和共享性 加快运行速度
三、创建存储过程
3、删除存储过程
使用企业管理器 删除存储过程是指删除由用户创建的存储过程。 可以使用DROP PROC命令删除存储过程。 语法： DROP PROC <存储过程名>
【例9】 DROP PROC mathtuto
工作任务
通过编写存储过程，雇员姓名由参数传递给该 存储过程，自动获相应某雇员的个人信息并将之 输出显示，包括姓名、性别、出生年月以及雇佣 日期，以及该雇员所做的订单明细表，统计出该 雇员所做的订单数目以及订单总金额，并由输出 参数返回其值。
【例3】如果在employee表中查找符合性别 和薪水条件的雇员的详细信息。
CREATE PROCEDURE listEmployee @sex varchar(2), @salary money=3000 as select * from employee where 性别=@sex and 薪水>@salary
3、在公司管理数据库中创建存储过程product_order1 ，
要求实现如下功能：根据订单id，查询该订单所订产品的 产品名、数量。
4、在公司管理数据库中创建存储过程product_order1 ，
要求实现如下功能：根据产品id，查询该产品的产品名、 单价、库存量。
（3）创建带输出参数的存储过程
程序清单（1）
CREATE PROCEDURE user_雇员订单信息 @name varchar(10), @order_count int output, @total_money decimal output AS --定义SQL server的变量，必须以@开头命名变量，用 DECLARE定义变量 DECLARE @emp_Id int DECLARE @sex char(2) DECLARE @date1 datetime DECLARE @date2 datetime
2.利用Transact—SQL语句创建存储过程
这个选项用于指定特定参数的缺省值。如果过程被执行 语法: 的时候这个参数没有赋值，将使用本缺省值来取代。
CREATE PROC [ EDURE ] 存储过程名称 procedure_name [ { @parameter data_type } [ = default ] [ OUTPUT ] 用于指定该参数是输出参数。当 参数变量 数据类型 过程执行完成后，该参数值能被 [ ,...n ] 返回到调用程序。 [ WITH ENCRYPTION ] 指定存储过程执行的操作 。 对存储过程文本进行加密。使用户 组成存储过程的 SQL语句。 AS 不会获得存储过程的原始代码。 sql_statement [ ...n ]
（2）利用SQL语句创建创建带输入参数 的存储过程
输入参数是指由调用程序向存储过程传递 的参数。它们在创建存储过程语句中被定义， 而在执行该存储过程中给出相应的变量值。 语法:
{@parameter data_type } [ = default ]
参数： @parameter ：参数名，必须以@符号为前缀。 data_type：参数的数据类型说明。 default：如果执行存储过程时未提供该参数值，则使 用默认值。
程序清单（2）
--判断是否存在此雇员 if exists(SELECT * FROM employee WHERE 姓名=@name) begin --通过查询语句将字段的值赋值给变量 SELECT @emp_Id=雇员ID,@sex=性别, @date1=出生年 月,@date2=雇佣日期 FROM employee WHERE 姓名=@name --用PRINT语句@name参数对应的雇员信息 PRINT '姓名:'+@name+' 性别：'+@sex +' 出生年月：'+convert(char(4),year(@date1))+'年' +convert(char(2),month(@date1))+'月' +convert(char(2),day(@date1))+'日' +' 雇用日期：'+convert(char(4),year(@date2))+'年' +convert(char(2),month(@date2))+'月' +convert(char(2),day(@date2))+'日'
and product.产品名=@productname
任务：
1、创建一个指定雇员姓名，返回该雇员所接受订单数 目的存储过程。 2、创建一个指定雇员id，返回该雇员姓名和性别的存 储过程。 3、创建一个指定订单id，返回该订单所订产品名和数 量的存储过程。 4、创建一个指定产品id，返回该产品名的存储过程。
【例9】 execute listEmployee '男',4000 用于显示性别为男，薪水大于4000的雇员信息。
（2） 通过参数名传送
指出创建该存储过程语句中的参数名字和传 递给它的值。 语法： [EXECUTE] 存储过程名 [@参数=值] 【例10】 exec listEmployee @salary=4000, @sex='男'
2、接收存储过程的返回值
在调用该存储过程的程序中，也必须声明y 用来接收输出参数的参数。这个参数声明为局 部变量，用来存放输出参数的值。 语法： [[EXECUTE] {存储过程名}[[@参数名=]{参数值|@变量 [OUTPUT]|[默认值]}][,...n]
【例7】执行存储过程product_order_sum。
促成教学目标： 1、能正确理解存储过程的概念 2、会使用企业管理器和Transact-SQL语句 创建存储过程 3、会使用带参数的存储过程
一、存储过程的定义（1）
存储过程是在服务器上的预编译好的SQL语句集。 “预编译好”指的是存储过程在创建时就被编译和优 化，调用一次以后，相关信息就保存在内存中，下次调用 时可以直接执行。 “SQL语句集”指的是declare语句、set语句、select 语等 存储过程类似于编程语言中的函数。将某些需要多次 调用实现某个特定任务的代码段编写成一个过程，将其保 存在数据库中，并由SQL Server服务器通过过程名来调用 这些过程（Procedure）。
系统存储过程sp_helptext，将返回所有与存 储过程有关的代码。 语法： sp_helptext <存储过程名>
【例8】 sp_helptext
product_order_sum
2、修改存储过程
使用企业管理器 用ALTER PROCEDURE语句。 语法: ALTER PROCEDURE <存储过程名称> {<@参数变量> <数据类型>}[VARYING][=默认值] [OUTPUT]] [,...n] [WITH {RECOMPILE| ENCRYPTION| RECOMPILE, ENCRYPTION}] AS Sql语句
任务：
1、在公司管理数据库中创建存储过程 product_order ，
要求实现如下功能：根据产品名称，查询该产品的订货情 况，显示所订产品的产品名、数量、单价。
2 、 在公司管理数据库中创建存储过程 product_employee
要求实现如下功能：根据雇员id，查询该雇员的姓名、性 别、出生日期。
（1）利用SQL语句创建无参数的 存储过程
【例1】创建所有的雇员的接受订单的明细 信息的存储过程。
create proc listOrder as select 姓名,产品名,数量,单价,数量*单价 as 金额 from employee ,p_order where employee.雇员ID=p_order.雇员ID And product.产品ID=p_order.产品ID
Create procedure mathtuor @m1 smallint, @m2 smallint, @result int output As Select @result=@m1*m2
【例5】创建一个指定产品名，返回已订该产 品的所有订单的总金额的存储过程。
Create PROCEDURE product_order_sum @productname varchar(20), @sum_pro int output AS SELECT @sum_pro=sum(p_order.数量*product.单价) FROM p_order, product where p_order.产品ID =product.产品ID
【例2】创建指定姓名的雇员接受订单的明细 信息的存储过程。
create procedure name_list_order @name varchar(8) as select 姓名,产品名,数量,单价,数量*单价 as 金额 from employee ,p_order where employee.雇员ID=p_order.雇员ID And product.产品ID=p_order.产品ID And 姓名=@name
选择新建存储过程对话框（ 创建存储过程（1） 1）
新建存储过程对话框 创建存储过程（2）
创建存储过程（3）
任务：
1、创建名为”项目7_1”的存储过程，其功能是：在 p_order表中，查找订货数量大于50的订单的 所 有信息。 2、创建名为”项目7_2”的存储过程，其功能是：在 employees表中，查找所有女雇员的信息。 3、创建名为”项目7_3”的存储过程，其功能是：在 product表中，查找单价大于2.5元，且库存量大 于300的产品的产品名、单价、库存量信息。 4、创建名为”项目7_4”的存储过程，其功能是：在 custom有信息。
四、执行存储过程
语法： [EXEC[UTE]] { {过程名| @存储过程变量 } [[@参数 =] {参数值 | @变量 [OUTPUT] | [默认值]] [,…n]
【例8】 exec count_employee
1、传递参数两种方法：
按位置传送 通过参数名传送
（1）按位置传送
在执行存储过程语句中，直接给出参数的传 递值。参数传递的顺序就是参数定义的顺序。 语法： [EXECUTE] 存储过程名 [参数值...]
3、使用 WITH ENCRYPTION 选项
对用户隐藏存储过程的文本。
【例6】如果在employee表中查找符合性别和薪水条 件的雇员的详细信息。
CREATE PROCEDURE list @sex varchar(2), @salary money WITH ENCRYPTION as select * from employee where 性别=@sex and 薪水>@salary go exec sp_helptext list 消息： 对象备注已加密。
从存储过程中返回一个或多个值。 语法： @参数名 数据类型[=默认值] OUTPUT @参数名：存储过程的输出参数名，必须以@符号为前缀。 数据类型：该参数的数据类型说明。 OUTPUT：指明输出参数。注意，输出参数必须位于所有输 入参数说明之后。
【例4】创建一个完成乘法运算的的存储过程。
declare @sum int exec product_order_sum '牛奶',@sum output print '牛奶的订单的总金额为：'+cast(@sum as char(8))
结果显示如下： 结果是: 牛奶的订单的总金额为：920
五、管理存储过程
1、显示存储过程 （1）使用企业管理器 （2）使用系统存储过程
创建存储过程的方法：
① 利用SQL Server 企业管理器创建存储过程。
②使用CREATE PROCEDURE命令创建存储过程。
1. 使用企业管理器创建存储过程
（1）在企业管理器中，选择指定的服务器和 数据库，用右键单击要创建存储过程的数据库， 在弹出的快捷菜单中选择“新建”选项，再选择 下一级菜单中的“存储过程…”选项， （ 2 ）在文本框中可以输入创建存储过程的 T_SQL语句，单击“检查语法”，则可以检查语 法是否正确；单击“确定”按钮，即可保存该存 储过程。
一、存储过程的定义（2）
存储过程包含两个主要组成部分：
①过程名称及其参数的说明，包括所有的输入参 数以及传给调用者的输出参数。 ②过程的主体：执行过程的针对数据库的操作语 句（Transact-SQL 语句）。
二、存储过程的特点
有效降低网络流量 增强代码的重用性和共享性 加快运行速度
三、创建存储过程
3、删除存储过程
使用企业管理器 删除存储过程是指删除由用户创建的存储过程。 可以使用DROP PROC命令删除存储过程。 语法： DROP PROC <存储过程名>
【例9】 DROP PROC mathtuto
工作任务
通过编写存储过程，雇员姓名由参数传递给该 存储过程，自动获相应某雇员的个人信息并将之 输出显示，包括姓名、性别、出生年月以及雇佣 日期，以及该雇员所做的订单明细表，统计出该 雇员所做的订单数目以及订单总金额，并由输出 参数返回其值。
【例3】如果在employee表中查找符合性别 和薪水条件的雇员的详细信息。
CREATE PROCEDURE listEmployee @sex varchar(2), @salary money=3000 as select * from employee where 性别=@sex and 薪水>@salary
3、在公司管理数据库中创建存储过程product_order1 ，
要求实现如下功能：根据订单id，查询该订单所订产品的 产品名、数量。
4、在公司管理数据库中创建存储过程product_order1 ，
要求实现如下功能：根据产品id，查询该产品的产品名、 单价、库存量。
（3）创建带输出参数的存储过程
程序清单（1）
CREATE PROCEDURE user_雇员订单信息 @name varchar(10), @order_count int output, @total_money decimal output AS --定义SQL server的变量，必须以@开头命名变量，用 DECLARE定义变量 DECLARE @emp_Id int DECLARE @sex char(2) DECLARE @date1 datetime DECLARE @date2 datetime
2.利用Transact—SQL语句创建存储过程
这个选项用于指定特定参数的缺省值。如果过程被执行 语法: 的时候这个参数没有赋值，将使用本缺省值来取代。
CREATE PROC [ EDURE ] 存储过程名称 procedure_name [ { @parameter data_type } [ = default ] [ OUTPUT ] 用于指定该参数是输出参数。当 参数变量 数据类型 过程执行完成后，该参数值能被 [ ,...n ] 返回到调用程序。 [ WITH ENCRYPTION ] 指定存储过程执行的操作 。 对存储过程文本进行加密。使用户 组成存储过程的 SQL语句。 AS 不会获得存储过程的原始代码。 sql_statement [ ...n ]
（2）利用SQL语句创建创建带输入参数 的存储过程
输入参数是指由调用程序向存储过程传递 的参数。它们在创建存储过程语句中被定义， 而在执行该存储过程中给出相应的变量值。 语法:
{@parameter data_type } [ = default ]
参数： @parameter ：参数名，必须以@符号为前缀。 data_type：参数的数据类型说明。 default：如果执行存储过程时未提供该参数值，则使 用默认值。
程序清单（2）
--判断是否存在此雇员 if exists(SELECT * FROM employee WHERE 姓名=@name) begin --通过查询语句将字段的值赋值给变量 SELECT @emp_Id=雇员ID,@sex=性别, @date1=出生年 月,@date2=雇佣日期 FROM employee WHERE 姓名=@name --用PRINT语句@name参数对应的雇员信息 PRINT '姓名:'+@name+' 性别：'+@sex +' 出生年月：'+convert(char(4),year(@date1))+'年' +convert(char(2),month(@date1))+'月' +convert(char(2),day(@date1))+'日' +' 雇用日期：'+convert(char(4),year(@date2))+'年' +convert(char(2),month(@date2))+'月' +convert(char(2),day(@date2))+'日'
and product.产品名=@productname
任务：
1、创建一个指定雇员姓名，返回该雇员所接受订单数 目的存储过程。 2、创建一个指定雇员id，返回该雇员姓名和性别的存 储过程。 3、创建一个指定订单id，返回该订单所订产品名和数 量的存储过程。 4、创建一个指定产品id，返回该产品名的存储过程。
【例9】 execute listEmployee '男',4000 用于显示性别为男，薪水大于4000的雇员信息。
（2） 通过参数名传送
指出创建该存储过程语句中的参数名字和传 递给它的值。 语法： [EXECUTE] 存储过程名 [@参数=值] 【例10】 exec listEmployee @salary=4000, @sex='男'
2、接收存储过程的返回值
在调用该存储过程的程序中，也必须声明y 用来接收输出参数的参数。这个参数声明为局 部变量，用来存放输出参数的值。 语法： [[EXECUTE] {存储过程名}[[@参数名=]{参数值|@变量 [OUTPUT]|[默认值]}][,...n]
【例7】执行存储过程product_order_sum。