创建存储过程与触发器.doc

数据库管理系统SQL Server实验报告实验7 创建存储过程与触发器实验日期和时间：2011-11-11实验室：2#206班级：09计本（4）学号：2009810182姓名：周伟实验环境：1. 硬件：1G内存 1.73GHz 2. 软件：SQL server2008 实验原理：创建存储过程，执行存储通过建立触发器实现对数据库的更新。实验任务：此作业成绩得分根据你完成的任务的难度和数量评分，完成后在实验室给老师演示验收，课后提交电子版报告。如额外完成自拟题目应当事先将所拟题目提交给老师或在报告中明确标注题意。假定有某个企业（或公司，或代理商）经销某类产品，需要用一个信息系统对销售业务和库存进行管理。首先，他们得在数据库中存储所有经营过的产品的信息，并建立现有库存的信息表；其次，对于每次销售或者进货，他们都得记录下来以便进行管理，将来对这些信息进行统计或财务管理；再次，在每笔销售记录中需要记录相关客户信息，在进货时需要记录相关供应商的信息，也为了与不同的供应商和客户进行联系，需要分别建立二者的信息表。综上所述，在以上建立的数据库中我们至少需要以下几个基本表：1. 产品表（记录公司曾经经营的所有产品信息）2. 现有库存表（记录公司目前经营的产品的现有库存信息）3. 出库单表（记录产品销售出库时的情况：时间、销售员、客户、商品编码、商品数量等）4. 入库单表（记录公司每次产品进货入库时的信息）5. 供应商表（记录为公司供货的主要供应商信息）6. 客户表（记录公司的所有客户信息） 以下是供参考的表结构的部分信息，同学们可以根据题意自行修改表的结构1. 产品表（记录公司的产品信息）字段名数据类型长度备注产品编号文本主键产品名称文本非空类别文本供应商编号文本外键（来自供应商表）产地文本最新参考单价货币规格文本2. 现有库存表（记录公司的现有库存信息）字段名数据类型长度备注产品编号文本主键、外键（来自产品表）产品名称产品规格类别零售单价货币现有库存量数字最小库存量数字存放地点文本3. 出库单表（记录产品销售出库时的情况）字段名数据类型长度备注出库单号文本主键客户编号文本外键（来自客户表）产品编号文本外键（来自产品表）出库数量数字出库价格货币金额货币出库数量出库价格出库日期日期/时间可以默认为系统时间目的地文本经手人文本4. 入库单表（记录公司每次产品进货入库时的信息）字段名数据类型长度备注入库单号文本主键产品编号文本外键（来自产品表）供应商编号文本外键（来自供应商表）入库数量数字入库价格货币入库日期日期/时间可以默认为系统时间经手人文本5. 供应商表（记录为公司供货的主要供应商信息）字段名数据类型长度备注供应商编号文本主键供应商名称文本非空联系人姓名文本地址文本电话文本传真文本电子邮箱文本6. 客户表（记录公司的所有客户信息）字段名数据类型长度备注客户编号文本主键客户名称文本非空联系人文本城市文本地址文本电话文本传真文本电子邮箱文本要求：1) 设计并创建以上的“库存管理”系统的数据库。注意，建表时，表中的外键的数据类型应当与其所参照的主表中的主键数据类型一致。（至少创建题目所需要的表）2) 创建关系图，建立表之间的联系以保证参照完整性。3) 基本数据录入。可以直接录入，也可将其它格式的表中的数据导入，或查询其它表中可利用的数据并插入到现有的表中。4) 创建存储过程。（任选一题） 创建可以按“产品编号”（参数）进行产品库存信息查询的存储过程。 创建可以按“类别”（参数）进行某类产品库存信息查询的存储过程。 创建可以按产品名称或产品名称打头字符串（参数）进行产品库存信息查询的存储过程。 自拟题5) 创建触发器。（任选一题） 创建“现有库存”表的DELETE触发器，禁止删除库存信息。（只需要现有库存表） 创建“出库单”表的INSERT触发器。在该表中插入出库记录时，能自动生成唯一的出库单号（可设置为自动编号），在填写“产品编号”和“出库数量”时，通过触发器的作用，能判断该产品的现有库存数量是否足够，如果足够满足此次出库数量，则能自动填写出库记录中与该记录“产品编号”对应的：“产品名称”（来自产品表/现有库存表）、“产品规格”（来自产品表/现有库存表）、“出库价格”（来自产品表/现有库存表）、“金额”（能自动计算并填入：=出库价格*出库数量）、“出货日期”（来自系统日期）等字段，并能根据此次出库数量自动减少该产品的现有库存数量值；如果现有库存数量不能满足此次出库数量，则拒绝此记录插入（事务回滚）并报警提示库存不足。（需要现有库存表和出库单表） 创建“出库单”表的INSERT触发器。如果此产品出库后的现有库存量低于最小库存量，则报警提示该产品库存不足需要进货。（需要现有库存表和出库单表） 创建“入库单”表的INSERT触发器。在该表中插入入库记录时，能自动生成唯一的入库单号（可设置为自动编号），在填写“产品编号”和“入库数量”时，通过触发器的作用，能判断在“现有库表中”是否存在该产品库存记录，如果有，则自动更新该产品的现有库存数量，如果现有库存表中不存在该产品的库存记录（有可能是未经营过的新产品），则先在现有库存表中自动插入该产品的库记录；并通过触发器的作用自动填写入库记录中与该记录“产品编号”对应的：“产品名称”（来自产品表/现有库存表）、“产品规格”（来自产品表/现有库存表）、“入库价格”（来自产品表的最新参考单价/现有库存表的零售单价）、“金额”（能自动计算并填入：=出库价格*出库数量）、“出货日期”（来自系统日期）等字段。（需要现有库存表和入库单表）先在第一栏填写自己选择的题目和欲实现的功能，再在其余栏目中分别填写自己的代码以及执行情况、测试方案和数据、测试结果等等。如果选做多个或自拟题，请自己依照格式添加栏目，自拟题请写清题意。 我的选题1：（描述题目和欲实现的功能）1.创建可以按“产品编号”（参数）进行产品库存信息查询的存储过程。原代码：创建存储过程create procedure xinxi_cx 123 nchar(10)as beginselect *from 现有库存量where 产品编号=123end;测试方案及数据：在新建查询中输入：EXECresult = dbo.xinxi_cx 123 = N001观察能否返回产品编号为001的产品现有库存信息测试结果：(文字说明、原代码、结果贴图)2创建可以按“类别”（参数）进行某类产品库存信息查询的存储过程原代码：创建存储过程: create procedure chanpinleibie 123 nchar(10)as begin select * from 产品表 where 类别=123 end;测试方案及数据：在新建查询中输入: exec result=chanpinleibie 123=电 select result=result测试结果：(文字说明、原代码、结果贴图)3.创建可以按产品名称或产品名称打头字符串（参数）进行产品库存信息查询的存储过程。创建存储过程: create procedure chanpinmingcheng 123 nchar(10)as begin select * from 产品表 where 产品名称=123 end;在新建查询中输入: exec result=chanpinmingcheng 123=电 select result=result测试结果：(文字说明、原代码、结果贴图)4.创建可以按“供应商编号”（参数）进行产品库存信息查询的存储过程。创建存储过程: create procedure gongyingshang 123 nchar(10)as begin select * from 产品表 where 供应商编号=123 end;在新建查询中输入: exec result=gongyingshang 123=102 select result=result测试结果：(文字说明、原代码、结果贴图)我的选题2：（描述题目和欲实现的功能）1.创建“现有库存”表的DELETE触发器，禁止删除库存信息原代码：create trigger 库存_信息删除 on 现有库存量 for deleteas declare 123 nchar(10)beginIF EXISTS (SELECT * FROM 现有库存量 WHERE 产品编号=123)PRINT 禁止删除库存信息！ROLLBACK;ROLLBACK TRANSACTION END;use zwgo delete from 现有库存量 where 产品编号=004go测试方案及数据：use zwgo delete from 现有库存量 where 产品编号=004Go测试结果：(文字说明、原代码、结果贴图)2.创建“出库单”表的INSERT触发器。原代码：create trigger 插入出库单表时更新on 出库单表 for insertasdeclare zdbh int,khbh nchar(10),cpbh nchar(10),cksl numeric,ckjg money,je money,ckrq datetime,mdd nchar(10),jsr nchar(10),xykcl numericbeginselect zdbh,cpbhwhere exists(select 现有库存量 from 现有库存量where xykcl=cksl) insert into 出库单表 values(zdbh,khbh,cpbh,cksl,ckjg,je,ckrq,mdd,jsr);end;测试结果：(文字说明、原代码、结果贴图)创建“出库单”表的INSERT触发器源代码：create trigger 插入出库单表on 出库单表 for insertasdeclare zdbh int,khbh nchar(10),cpbh nchar(10),cksl numeric,ckjg money,je money,ckrq datetime,mdd nchar(10),jsr nchar(10),xykcl numericbeginif exists(select * from 现有库存量where 现有库存量cksl)print 库存不足需要补货!ROLLBACK;ROLLBACK TRANSACTION end;测试结果：4.创建“入库单”表的INSERT触发器。原代码：create trigger 插入入库单表on 入库单表 for insertasdeclare zdbh int,cpbh nchar(10),gysbh nchar(10),rksl numeric,rkjg money,rkrq datetime,jsr nchar(10),xykcl numeric,cksl ncharbeginselect zdbh,cpbhwhere exists(select 现有库存量 from 现有库存量where xykcl=cksl) insert into 入库单表 values(zdbh,cpbh,gysbh,rksl,rkjg,rkrq,jsr)end;结果：本实验总结：（不少于100字）书上介绍的内容根本不够啊所以就上网查找一些例题研究 才基本上弄懂了以后得多看看书本以外的知识 增加理解的范围要求：1. 报告格式和内容要求： a. 内容和格式整齐。大标题采用黑体四号字加粗，小标题采用小四号字加粗。正文采用五号宋体，单倍行距。 b. 贴图时请剪裁到适当大小，要保证打印时可以看清，但也不要太大以免“越界”。 c. 不要在报告中写与实验无关的话，内