《项目数据库存储过程课件》由会员分享，可在线阅读，更多相关《项目数据库存储过程课件（42页珍藏版）》请在金锄头文库上搜索。
1、项目七 公司管理数据库系统中存储过程的应用,促成教学目标： 1、能正确理解存储过程的概念 2、会使用企业管理器和Transact-SQL语句管理存储过程 3、会使用存储过程传递参数,工作任务,通过编写存储过程，获取指定雇员的个人信息并将之输出显示，包括姓名、性别、出生年月以及雇佣日期，以及该雇员所接收的订单明细表，并统计该雇员接收的订单数目以及订单总金额。,一、存储过程的定义（1）,SQL Server的存储过程类似于编程语言中的过程。将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程（Procedure
2、）就叫做存储过程。 存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。,存储过程的定义（2）,存储过程定义包含两个主要组成部分： 过程名称及其参数的说明，包括所有的输入参数以及传给调用者的输出参数。 过程的主体：执行过程的针对数据库的操作语句（Transact-SQL 语句），包括调用其它存储过程的语句。,二、存储过程的特点,允许模块化的程序设计 更快的执行速度 有效降低网络流量 较好的安全机制,三、存储过程的分类,存储过程分为三类： 系统提供的存储过程： 系统提供的存储过程系统存储过程的名字都以“sp_”为前缀 例：sp_bindefault ，s
3、p_bindrule ，sp_help ， sp_helpdb, sp_helpindex 等 用户定义的存储过程： 用户定义的存储过程是由用户为完成某一特定功能而编写的存储过程。存储在创建时的数据库中。 扩展存储过程： 扩展存储过程是用来调用操作系统提供的功能。,四、创建与管理存储过程,使用三种方法创建存储过程 ： 使用创建存储过程向导创建存储过程。 利用SQL Server 企业管理器创建存储过程。 使用CREATE PROCEDURE命令创建存储过程。,1. 使用向导创建存储过程,在企业管理器中，选择工具菜单中的向导选项，选择“创建存储过程向导” ，则出现欢迎使用创建存储过程向导对话框，
4、根据图提示可完成创建存储过程。,欢迎使用创建存储过程向导对话框,选择数据库对话框,选择数据库对象对话框,完成创建存储过程向导对话框,2. 使用企业管理器创建存储过程,（1）在企业管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程”选项， （2）在文本框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。,选择新建存储过程对话框（1）,创建存储过程（1）,新建存储过程对话框,创建存储过程（2）,创建存储过程（3）,3.利用TransactSQL
5、语句创建和理存储过程,CREATE PROCEDURE语句用于从已测试过的SQL语句来实际创建一个存储过程。 语法: CREATE PROC EDURE procedure_name parameter data_type = default OUTPUT ,.n WITH ENCRYPTION AS sql_statement .n ,【例1】创建所有的雇员的接收订单的明细信息的存储过程。,create proc listOrder as select 姓名,产品名,数量,单价,数量*单价 as 金额 from employee join p_order on employee.雇员ID=p
6、_order.雇员ID join product on product.产品ID=p_order.产品id order by 订货日期,创建带输入参数的存储过程,输入参数是指由调用程序向存储过程传递的参数。它们在创建存储过程语句中被定义，而在执行该存储过程中给出相应的变量值。 语法: parameter data_type = default 参数： parameter ：参数名，必须以符号为前缀。 data_type：参数的数据类型说明。 default：如果执行存储过程时未提供该参数值，则使用默认值。,【例2】创建指定姓名的雇员接收订单的明细信息的存储过程。,create procedur
7、e name_list_order name varchar(8) as select 姓名,产品名,数量,单价,数量*单价 as 金额 from employee join p_order on employee.雇员ID=p_order.雇员ID join product on product.产品ID=p_order.产品id Where 姓名=name order by 订货日期,【例3】如果在employee表中查找符合性别和薪水条件的雇员的详细信息。,CREATE PROCEDURE listEmployee sex varchar(2), salary money as sele
8、ct * from employee where 性别=sex and 薪水salary,【例4】在公司管理数据库中创建存储过程proc_cu，要求实现如下功能：输入公司的名称，查询该公司的订货情况，如果该公司没有订货，则显示“此公司没有订单！”，否则显示“此公司已下订单！”。,CREATE PROCEDURE proc_pu customer_name varchar(20) AS if exists(select * from p_order where 客户ID=(select 客户ID from customer where 公司名称=customer_name) ) print 此公
9、司已下订单！ else print 此公司没有下订单！,【例5】在公司管理数据库中创建存储过程product_order ，要求实现如下功能：根据产品名称，查询该产品的订货情况，如果该产品没有订货，则显示“此产品目前没有订单！”，否则显示订购该产品的订购明细表。,【例5】程序清单,CREATE PROCEDURE product_order (productname varchar(20) AS declare 产品id_1 int select 产品id_1=产品id from product where 产品名=productname if exists(select * from p_o
10、rder where 产品id=产品id_1) begin SELECT product.产品名, p_order.数量, product.单价, product.库存量,p_order.订货日期 FROM p_order JOIN product ON p_order.产品ID =product.产品ID where product.产品名=productname end else print productname+ 没有订单！,【例6】阅读下列程序,CREATE PROCEDURE customer_order customername varchar(20) AS SELECT cus
11、tomer.公司名称, customer.联系人姓名, product.产品名, product.单价,p_order.数量, p_order.订货日期 FROM customer JOIN p_order ON customer.客户ID =p_order.客户ID JOIN product ON p_order.产品ID = product.产品ID where customer.公司名称=customername,创建带输出参数的存储过程,从存储过程中返回一个或多个值。 语法： 参数名 数据类型=默认值 OUTPUT 参数名：存储过程的输出参数名，必须以符号为前缀。 数据类型：该参数的数
12、据类型说明。 OUTPUT：指明输出参数。注意，输出参数必须位于所有输入参数说明之后。,【例7】创建一个指定产品的接受订单的总金额的存储过程。,Create PROCEDURE product_order_sum (productname varchar(20),sum_pro int output) AS declare 产品id_1 int select 产品id_1=产品id from product where 产品名=productname if exists(select * from p_order where 产品id=产品id_1) begin SELECT sum_pro=
13、sum(p_order.数量*product.单价) FROM p_order JOIN product ON p_order.产品ID =product.产品ID where product.产品名=productname end else select sum_pro=0 return sum_pro,使用 WITH ENCRYPTION 选项,对用户隐藏存储过程的文本。 【例8】创建加密过程，使用 sp_helptext 系统存储过程获取关于加密过程的信息，然后尝试直接从 syscomments 表中获取关于该过程的信息。 USE pubs GO CREATE PROCEDURE pro
14、duct_order_sum 。 WITH ENCRYPTION AS 。 GO exec sp_helptext product_order_sum 消息： 对象备注已加密。,工作任务,通过编写存储过程，雇员姓名由参数传递给该存储过程，自动获相应某雇员的个人信息并将之输出显示，包括姓名、性别、出生年月以及雇佣日期，以及该雇员所做的订单明细表，统计出该雇员所做的订单数目以及订单总金额，并由输出参数返回其值。,程序清单（1）,CREATE PROCEDURE user_雇员订单信息 name varchar(10), order_count int output, total_money dec
15、imal output AS -定义SQL server的变量，必须以开头命名变量，用DECLARE定义变量 DECLARE emp_Id int DECLARE sex char(2) DECLARE date1 datetime DECLARE date2 datetime,程序清单（2）,-判断是否存在此雇员 if exists(SELECT * FROM employee WHERE 姓名=name) begin -通过查询语句将字段的值赋值给变量 SELECT emp_Id=雇员ID,sex=性别, date1=出生年月,date2=雇佣日期 FROM employee WHERE
16、姓名=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)+日,程序清单（3）,-判断此雇员是否有订单 if exists(select * from P_order where P_order.雇员ID= emp_Id) begin -输出name参数对应的雇员完成的订单明细 SELECT customer.公司名称,custo
