SQLServer2005数据库任务化教程 sql语句.doc

任务一 分销系统数据库的设计与生成任务1-2：创建一个student数据库。CREATE DATABASE student任务1-3：创建一个CUSTOMER数据库，该数据库的主数据文件的逻辑名称是CUSTOMER_DATA，操作系统文件是CUSTOME_MDF，大小是15MB，最大是30MB,以20%的速度增加；该数据库的日志文件的逻辑名称是CUSTOMER_LOG，操作系统文件是CUSTOMER.LDF，大小是3MB，最大是10MB,以1MB的速度增加。CREATE DATABASE customerON PRIMARY (NAME = customer_data, FILENAME=e:yxlcustomer_data.mdf, SIZE = 15MB, MAXSIZE = 30MB, FILEGROWTH=20%) LOG ON (NAME = customer_log, FILENAME = e:yxlcustomer_log.ldf, SIZE = 3MB, MAXSIZE = 10MB, FILEGROWTH = 1MB)任务二 数据表的创建与维护任务2-4：用Transact_SQL创建客户资料表。use 分销系统goCREATE TABLE 客户资料(客户编码 varchar(20) NOT NULL PRIMARY KEY,客户名称 varchar(100) NOT NULL,联系人 varchar(20) NOT NULL,电话 varchar(50) NOT NULL,传真 varchar(50),地址 varchar(200),送货地址 varchar(200)go任务2-5：用Transact_SQL创建客户资料表USE 分销系统GOCREATE TABLE 销售订单(销售订单号 varchar(20) NOT NULL PRIMARY KEY,日期 datetime NOT NULL,客户编码 varchar(20) NOT NULL FOREIGN KEY REFERENCES 客户资料(客户编码),客户名称 varchar(100) NOT NULL,联系人 varchar(20) NOT NULL,联系电话 varchar(50) NOT NULL,送货地址 varchar(200) NOT NULL,总金额 numeric(12,4) NOT NULL,备注 varchar(500) NULL,)go任务2-6：用Transact_SQL创建销售订单明细表。USE 分销系统GOCREATE TABLE 销售订单明细表(销售订单号 varchar(20) NOT NULL,序号 int IDENTITY(1,1) NOT NULL,商品编码 varchar(20) NOT NULL,商品名称 varchar(50) NOT NULL,规格型号 varchar(100) NOT NULL,单位 varchar(8) NOT NULL,数量 numeric(12, 2) NOT NULL,单价 numeric(12, 2) NOT NULL,金额 numeric(12, 2) NOT NULL,备注 varchar(500) NULL,PRIMARY KEY (销售订单号,序号),FOREIGN KEY (销售订单号) REFERENCES 销售订单(销售订单号)go任务2-7：修改销售订单明细表中单价字段的数据类型为numeric(12,4)。ALTER TABLE 销售订单明细表 ALTER COLUMN 单价 numeric(12,4) NOT NULL任务2-8：给客户资料表新增一个名为“登记日期”的datetime类型的字段，然后再将该新增字段删除。ALTER TABLE 客户资料ADD 登记日期 datetime NULLALTER TABLE 客户资料drop column 登记日期任务2-9：用ALTER TABLE语句来达到取消销售订单明细表中序号字段的标识列定义的目的。ALTER TABLE 销售订单明细表drop column 序号goALTER TABLE 销售订单明细表ADD 序号 int NOT NULL任务2-10：为分销系统数据库创建规则，规则名称为rule1，它将限定使用了该规则的列的值都必须大于0。use 分销系统gocreate rule rule1 as value0任务2-11：将规则rule1绑定到销售订单明细表的单价列中。sp_bindrule rule1,销售订单明细表.单价任务2-12：为销售订单明细表的单价列解除规则绑定。sp_unbindrule 销售订单明细表.单价任务2-13：在确认规则rule1已不被绑定到任何对象上后，删除规则rule1。DROP RULE rule1任务2-14：创建供应商资料表，其中【供应商编码】为主键。use 分销系统gocreate table 供应商资料( 供应商编码 varchar(20) constraint PK_GYSBM primary key, 供应商名称 varchar(100) not null, 联系人 varchar(20) NOT NULL, 电话 varchar(50) NOT NULL, 传真 varchar(50) NOT NULL, 地址 varchar(200) NOT NULL )go任务2-15：创建采购订单明细表（未考虑外键约束）。USE 分销系统GOCREATE TABLE 采购订单明细表(采购订单号 varchar(20) NOT NULL,序号 int NOT NULL,商品编码 varchar(20) NOT NULL,商品名称 varchar(50) NOT NULL,规格型号 varchar(100) NOT NULL,单位 varchar(8) NOT NULL,数量 numeric(12,2) NOT NULL,单价 numeric(12,4) NOT NULL,金额 numeric(12, 4) NOT NULL,备注 varchar(500) NULL,constraint PK_CGDDH_XH PRIMARY KEY (采购订单号,序号)go任务2-16：创建采购订单表。USE 分销系统GOCREATE TABLE 采购订单(采购订单号 varchar(20) PRIMARY KEY,日期 datetime NOT NULL,供应商编码 varchar(20) FOREIGN KEY REFERENCES 供应商资料(供应商编码),供应商名称 varchar(100) NOT NULL,联系人 varchar(20) NOT NULL,联系电话 varchar(50) NOT NULL,总金额 numeric(12,4) NOT NULL,备注 varchar(500) NULL)go任务2-17：创建仓库资料表。use 分销系统gocreate table 仓库资料( 仓库编码 varchar(20) primary key, 仓库名称 varchar(50) NOT NULL, 仓库位置 varchar(200) NOT NULL , 备注 varchar(500) NOT NULL , constraint UK_CCMC UNIQUE (仓库名称)go任务2-18：创建期初库存表，为其中的期初单价创建检查约束（期初单价0）。use 分销系统gocreate table 期初库存( 序号 int IDENTITY(1,1) Primary Key, 仓库编号 varchar(20), 仓位编号 varchar(20), 商品编码 varchar(20), 商品名称 varchar(50), 规格型号 varchar(100), 单位 varchar(8), 期初数量 numeric(12,2), 期初单价 numeric(12,4), 期初金额 numeric(12,4), constraint CK_QCDJ CHECK (期初单价0)go任务2-19：创建调拨单表，为其中的调拨日期定义一个默认值为当天日期时间。use 分销系统gocreate table 调拨单( 调拨单号 varchar(20) Primary Key, 调拨日期 datetime DEFAULT (GetDate(), 调拨人 varchar(20) NOT NULL, 调出仓库编号 varchar(20) NOT NULL, 调出仓位编号 varchar(20) NOT NULL, 调入仓库编号 varchar(20) NOT NULL, 调入仓位编号 varchar(20) NOT NULL, 备注 varchar(500)go任务2-23：使用Transact-SQL语句实现任务2-19的操作。use 分销系统goinsert into 仓库资料values (JK01,窖口仓库,常青路221号,易燃物)insert 仓库资料values (HP02,黄埔仓库,桥东路12号,NULL)insert 仓库资料 (仓库编码,仓库名称, 仓库位置) values (DC03,东涌仓库,新港东路241号)go任务2-24：给付款单添加如表2-7所示的两条记录。use 分销系统goinsert 付款单values (F200709190106,2007-09-19,李铭,P0001,万业货贸,10000,10000,NULL)insert 付款单values (F200801230058,2008/01/23,张晓涛,P0002,众星贸易,4000,4000,NULL)go任务2-25：使用Transact-SQL语句实现任务2-20的操作。use 分销系统goupdate 仓库资料 set 仓库位置=长青路号 where 仓库编码=JK01go任务2-26：将表2-7中付款单号为“F200709190106”的记录的付款日期修改为“2007-09-20”、付款金额修改为9000。use 分销系统goupdate 付款单 set 付款日期=2007-09-20, 付款金额=9000 where 付款单号=F200709190106go任务2-27：使用Transact-SQL语句实现任务2-21的操作。use 分销系统godelete from 仓库资料 where 仓库编码=DC03go任务2-28：在付款单删除张晓涛在2008-01-23的付款记录。use 分销系统godelete 付款单 where 付款日期=2008-01-23 and 付款人=张晓涛go任务三 对分销系统数据库进行查询操作任务3-1：查询分销系统数据库的客户资料表中的所有客户的客户名称、联系人和电话。use 分销系统goselect 客户名称,联系人,电话from 客户资料go任务3-2：查询客户资料表中所有客户的所有列的信息。use 分销系统goselect * from 客户资料go任务3-4：查询销售订单明细表中所有记录的商品名称、单价，消除查询结果中的重复行。use 分销系统goselect DISTINCT 商品名称,单价from 销售订单明细表go任务3-5：查询销售订单表中所有记录的客户名称、联系人、联系电话，只返回查询结果的前3行。use 分销系统goselect TOP 3 客户名称,联系人,联系电话from 销售订单go任务3-6：查询销售订单明细表中所有记录的所有列，只返回查询结果的前20%行。use 分销系统goselect TOP 20 PERCENT *from 销售订单明细表go任务3-7：查询销售订单表中所有记录的所有列，要求在查询结果中多加一个标题为运费的列，运费等于总金额的5%。use 分销系统goselect * , 总金额*0.05 as 运费from 销售订单go任务3-8：查询销售订单表中客户编码为C0001的记录的所有列。use 分销系统goselect * from 销售订单where 客户编码=C0001go任务3-9：查询销售订单表中总金额大于等于10000的记录的客户名称、联系电话和总金额。use 分销系统goselect 客户名称,联系电话,总金额 from 销售订单where 总金额=10000go任务3-10：查询销售订单表中客户编码为C0001，同时总金额大于等于10000的记录的所有列。use 分销系统goselect * from 销售订单where 客户编码=C0001 AND 总金额=10000go任务3-11：查询销售订单表中客户编码为C0001或总金额大于等于10000的记录的所有列。use 分销系统goselect * from 销售订单where 客户编码=C0001 OR 总金额=10000go任务3-12：查询销售订单表中总金额在5000到10000之间的记录的所有列。use 分销系统goselect * from 销售订单where 总金额 BETWEEN 5000 AND 10000go任务3-13：查询销售订单表中总金额在5000到10000之外的记录的所有列。use 分销系统goselect * from 销售订单where 总金额 NOT BETWEEN 5000 AND 10000go任务3-14：查询销售订单表中客户编码C0001、C0002和C0003的记录的所有列。use 分销系统goselect * from 销售订单where 客户编码 IN (C0001,C0002,C0003)go任务3-15：查询客户资料表中联系人姓“王”的记录的所有列。use 分销系统goselect * from 客户资料where 联系人 LIKE 王%go任务3-16：查询客户资料表中联系人姓“王”，名字的第二字为“成”或“诚”的记录的所有列。use 分销系统goselect * from 客户资料where 联系人 LIKE 王成诚%go任务3-17：查询客户资料表中未录入传真号码的记录的所有列。use 分销系统goselect * from 客户资料where 传真 IS NULLgo任务3-18：将销售订单表中所有记录按照总金额由大到小的顺序排序输出。在SQLQuery窗口中执行如下命令：use 分销系统goselect * from 销售订单order by 总金额 descgo任务3-19：将销售订单表中所有记录按照客户编码由小到大、客户编码相同时总金额由大到小的顺序排序输出。use 分销系统goselect * from 销售订单order by 客户编码 asc,总金额 descgo任务3-20：将销售订单明细表中商品编码为“G0003”的记录按照金额由大到小的顺序排序输出。use 分销系统goselect * from 销售订单明细表where 商品编码=G0003order by 金额 descgo任务3-21：查询客户资料表中的记录数。use 分销系统goselect count(*) as 客户数from 客户资料go任务3-22：查询2008-01-01以来的销售订单的总金额之和。use 分销系统goselect SUM(总金额)from 销售订单where 日期=2008-01-01go任务3-23：查询属于当日的销售订单的总金额之平均值。use 分销系统goselect AVG(总金额)from 销售订单where 日期=GetDate()go任务3-24：查询当日的销售订单的总金额的最大值和最小值。use 分销系统goselect MAX(总金额) as 最大单, MIN(总金额) as 最小单from 销售订单where 日期=GetDate()go任务3-25：在销售订单中查询每个客户的订单数。use 分销系统goselect 客户编码, count(*) as 订单数from 销售订单group by 客户编码go任务3-26：在销售订单中统计每个客户各自的订单总金额之和。use 分销系统goselect 客户编码, SUM(总金额) as 订单总金额from 销售订单group by 客户编码go任务3-27：在销售订单中统计每个客户各自的订单总金额之和，从中筛选出订单总金额之和大于20000的。use 分销系统goselect 客户编码, SUM(总金额) as 订单总金额from 销售订单group by 客户编码having SUM(总金额)20000go任务3-28：在销售订单中统计2007-01-01以来每个客户各自的订单总金额之和，从中筛选出订单总金额之和大于20000的。use 分销系统goselect 客户编码, SUM(总金额) as 订单总金额from 销售订单where 日期=2007-01-01group by 客户编码having SUM(总金额)20000go任务3-29：查询销售订单中2008-01-01以来的订单的销售订单号、客户编码、联系人、联系电话，并产生一个销售订单总数行。use 分销系统goselect 销售订单号,客户编码,联系人,联系电话from 销售订单where 日期=2008-01-01compute count(销售订单号)go任务3-30：将销售订单按客户编码排序，并汇总各客户的订单总数和订单总金额之和。use 分销系统goselect 销售订单号,日期,客户编码,联系人,联系电话,总金额from 销售订单 order by 客户编码compute count(销售订单号),sum(总金额) by 客户编码go任务3-31：查询出订单明细中有名称为“运动水壶”的客户的客户名称、联系人和联系电话。use 分销系统goselect 客户名称,联系人,联系电话from 销售订单where 销售订单号 IN (select distinct 销售订单号 from 销售订单明细表 where 商品名称=运动水壶)go任务3-32：查询出订单明细中没有名称为“运动水壶”的客户的客户名称、联系人和联系电话，销售员将把这些客户作为业务拓展对象。use 分销系统goselect 客户名称,联系人,电话from 客户资料where 客户编码 NOT IN (select distinct 客户编码 from 销售订单 where 销售订单号 IN (select distinct 销售订单号 from 销售订单明细表 where 商品名称=运动水壶)go任务3-33：查询全部订单总金额之和最高的1个客户的客户名称、联系人和电话，业务员将邀请这个客户出席公司年会。use 分销系统goselect 客户名称,联系人,电话from 客户资料where 客户编码 IN (select TOP 1 客户编码 from 销售订单group by 客户编码 order by SUM(总金额) desc)go任务3-34：在销售订单表中查询总金额大于销售订单号为“200801010001”的总金额的所有销售订单的所有列。use 分销系统goselect * from 销售订单where 总金额(select 总金额 from 销售订单 WHERE 销售订单号=200801010001)go任务3-35：在销售订单表中查询总金额大于客户编码为“C0002”每张销售订单中的总金额的所有销售订单。use 分销系统goselect * from 销售订单where 总金额ALL(select 总金额 from 销售订单 WHERE 客户编码=C0002)go任务3-36：在销售订单表中查询总金额大于客户编码为“C0002”每张销售订单中的总金额的最小值的所有销售订单。use 分销系统goselect * from 销售订单where 总金额ANY(select 总金额 from 销售订单 WHERE 客户编码=C0002)go任务3-37：查询仓位资料表的详细信息，要求仓库编码、仓位编码、仓位名称、仓库位置4个列。use 分销系统goselect 仓位资料.仓库编码,仓位资料.仓位编码,仓位资料.仓位名称,仓库位置from 仓位资料,仓库资料where 仓位资料.仓库编码=仓库资料.仓库编码go任务3-38：销售订单表的销售订单号与销售订单明细表的销售订单号的等值连接。use 分销系统goselect a.销售订单号,a.日期,a.客户编码,a.送货地址,b.序号,b.商品编码,b.商品名称,b.金额from 销售订单 a INNER JOIN 销售订单明细表 b ON a.销售订单号=b.销售订单号go任务3-39：列出销售订单表中总金额大于订单号为200801010001中总金额的销售订单号、客户名称、联系电话、总金额。use 分销系统goselect a.销售订单号,a.客户名称,a.联系电话,a.总金额from 销售订单 a INNER JOIN 销售订单 b ON a.总金额b.总金额where b.销售订单号=200801010001go任务3-41：客户资料表左外连接销售订单表。use 分销系统goselect a.客户编码,a.客户名称,a.联系人,b.* from 客户资料 a LEFT OUTER JOIN 销售订单 b ON a.客户名称=b.客户名称go任务3-42：客户资料表右外连接销售订单表。use 分销系统goselect a.客户编码,a.客户名称,a.联系人,b.* from 客户资料 a RIGHT OUTER JOIN 销售订单 b ON a.客户名称=b.客户名称go任务3-43：客户资料表全外连接销售订单表。use 分销系统goselect a.客户编码,a.客户名称,a.联系人,b.* from 客户资料 a FULL OUTER JOIN 销售订单 b ON a.客户名称=b.客户名称go任务3-44：客户资料表与销售订单表使用交叉连接。use 分销系统goselect a.客户编码,a.客户名称,a.联系人,b.* from 客户资料 a CROSS JOIN 销售订单 b go任务3-45：在销售订单表中查询2007-05-01以来客户编码为“C0001”的订单的订单号、客户编码、客户名称、联系电话、送货地址。use 分销系统goselect a.客户编码,a.客户名称,b.联系电话,b.送货地址from 销售订单 a INNER JOIN 销售订单 b ON a.销售订单号=b.销售订单号where a.日期2007-05-01 AND b.客户编码=C0001go任务3-46：将客户资料表的客户编码、客户名称、地址和仓库资料表的仓库编码、仓库名称、仓库位置列出来。use 分销系统goselect 客户编码 as 编码,客户名称 as 客户或仓库名,地址 from 客户资料UNIONselect 仓库编码,仓库名称,仓库位置 from 仓库资料go任务3-47：查询收款人或付款人为“李铭”的收款单和付款单的信息。use 分销系统goselect * from 收款单where 收款人=李铭UNION ALLselect * from 付款单where 付款人=李铭l go任务四 分销系统数据库索引和视图的设计任务4-2：用Transact-SQL语句完成任务4-1。USE 分销系统GOCREATE UNIQUE NONCLUSTERED INDEX index_KHMC ON 客户资料(客户名称)go任务4-3：使用系统存储过程Sp_helpindex也可查看客户资料表上的索引信息。在SQLQuery窗口中执行如下命令：USE 分销系统GOexec Sp_helpindex 客户资料go任务4-4：用Transact-SQL语句删除客户资料表的索引index_KHMC。use 分销系统goDrop Index 客户资料.Index_KHMCgo任务4-5：用Transact-SQL语句给销售订单表的总金额列创建索引Index_ZJE。USE 分销系统GOCREATE NONCLUSTERED INDEX index_ZJE ON 销售订单(总金额)go任务4-6：用Transact-SQL语句给销售订单表的日期列创建索引Index_RQ。USE 分销系统GOCREATE NONCLUSTERED INDEX index_RQ ON 销售订单(日期)go任务4-8：用Transact-SQL语句完成任务4-7。在SQLQuery窗口中执行如下命令：USE 分销系统GOcreate view XSMX_viewasSELECT 日期,销售订单明细表.销售订单号,序号,商品名称,规格型号,数量,单价,金额 FROM 销售订单 INNER JOIN 销售订单明细表ON 销售订单.销售订单号= 销售订单明细表.销售订单号go任务4-9：修改视图XSMX_view，去掉视图中的“金额”列，新增一个由数量与单价的乘积得到的“计算金额”列。USE 分销系统GOalter view XSMX_viewasSELECT 销售订单.日期,销售订单明细表.销售订单号,序号,商品名称,规格型号,数量,单价,数量*单价 as 计算金额 FROM 销售订单 INNER JOIN 销售订单明细表 ON 销售订单.销售订单号= 销售订单明细表.销售订单号go任务4-10：写出删除视图XSMX_view的语句。USE 分销系统GODrop view XSMX_viewGo任务4-11：查看视图XSMX_view的基本信息。USE 分销系统GOSP_HELP XSMX_viewGo任务4-11：查看视图XSMX_view的文本信息。USE 分销系统GOSP_HELPTEXT XSMX_viewGo任务4-12：通过视图XSMX_view查询销售订单号为“200710230018”的订单明细信息的销售订单号、商品名称、计算金额。USE 分销系统goselect 销售订单号,商品名称,计算金额 from XSMX_view where 销售订单号=200710230018go任务4-13：创建视图FKD_view，该视图包含付款单表中的所有记录的非空列（付款单号,付款日期,付款人,付款金额），然后往该视图添加一条记录。USE 分销系统gocreate view FKD_viewas select 付款单号,付款日期,付款人,付款金额 from 付款单goinsert FKD_view values (F200804020023,2008-04-02,章艺谋,11000)go任务4-14：修改视图FKD_view中付款单号为“F200804020023”的付款金额为10000。USE 分销系统goUPDATE FKD_view set 付款金额=10000 where 付款单号=F200804020023go任务4-15：删除视图FKD_view中付款人为“章艺谋”的付款记录。USE 分销系统godelete from FKD_view where 付款人=章艺谋go任务4-16：为商品核对人员创建一个视图，命为CCHD_view。USE 分销系统GOcreate view CCHD_viewasSELECT 销售订单号,序号,商品编号,商品名称,规格型号,单位,数量 FROM 销售订单明细表 go 任务五 数据库存储过程的规划与设计任务5-1：创建不带参数的存储过程Proc_kcxx1，查询仓库编码为“0001”的期初库存信息。USE 分销系统GOCREATE PROCEDURE Proc_kcxx1 As select 仓库编码,仓位编码,商品编码,商品名称,单位,期初数量,期初金额 from 期初库存 where 仓库编码=0001任务5-2：执行存储过程Proc_kcxx1。EXEC Proc_kcxx1任务5-3：创建带参数的存储过程。对上面的存储过程进行改造，改成带一个参数，仓库编码，可以指定需要查询的仓库编码的期初库存信息。CREATE PROCEDURE Proc_kcxx2 ckbm varchar(20)As Select 仓库编码,仓位编码,商品编码,商品名称,单位,期初数量,期初金额 from 期初库存 where 仓库编码=ckbm任务5-4：执行此存储过程,查询仓库编码为“0001”的期初库存信息。EXEC Proc_kcxx2 0001任务5-5：创建带有多个输入参数并有默认值的存储过程。对上面的存储过程进行改造，改成带两个参数，仓库编码和商品名称，可以指定需要查询的仓库编码和商品名称的期初库存信息，其中仓库编码默认值为“0001”。CREATE PROCEDURE Proc_kcxx3 ckbm varchar(20)= 0001 , spmc varchar(50)As Select 仓库编码,仓位编码,商品编码,商品名称,单位,期初数量,期初金额 from 期初库存 where 仓库编码=ckbm and 商品名称=spmc任务5-6：执行此存储过程,查询仓库编码为“0002”,商品名称为“杯子”的期初库存信息。EXEC Proc_kcxx3 0002 ,杯子任务5-7：创建存储过程，把期初库存中指定的记录更新为：期初金额期初数量期初单价。CREATE PROCEDURE Proc_kcxx_update ckbm varchar(20)= 0001 , spmc varchar(50)As Update 期初库存 set期初金额期初数量期初单价 where 仓库编码=ckbm and 商品名称=spmc任务5-8：执行此存储过程，将所有商品名称为“杯子”且仓库编码为“0003”的期初库存信息中期初金额重新计算。EXEC Proc_kcxx_update 0003 ,杯子任务5-9：在存储过程中，可以对多个表进行操作，并且可以使用游标。下面的例子将指定供应商供应的商品的出库信息里面的单价统一更新为某个指定价格。为了说明游标的使用，在这个例子中特意用了游标，当然，不用游标也可以实现这个功能。注意的是，在存储过程里面可以使用注释，其中开头的那一行都是注释，以后在存储过程中如果有必要，都会写些简单的注释帮助读者理解。CREATE PROCEDURE Proc_ckdj_update gysbm varchar(20), dj numeric(12,2)-参数gysbm是指定的供应商编码，dj是指定的单价，为2位小数的数字类型。As declare spbm varchar(20)-定义一个变量名spbm用来从游标里面读出数据DECLARE crusor_spbm CURSOR FOR SELECT distinct 商品编码 FROM 入库单主表 a left outer join 入库单明细表 b on a.入库单号 = b.入库单号where a.供应商编码=gysbm-这里定义一个名字为crusor_spbm的游标，并且从入库单里面找到该指定供应商供应的所有商品编码，存进游标里面。Distinct关键字是用来去掉重复的商品编码值。 open crusor_spbm-打开游标crusor_spbm fetch next from crusor_spbm into spbm- 从游标crusor_spbm里面读出一个商品编码，并赋值给变量spbm while fetch_status=0 -建立一个while循环，当fetch_status0时，即游标还没有游到最后一行数据时，继续循环体的执行。 BEGIN- 循环体开始 update 出库单明细表 set 单价=dj where 商品编码=spbm-将商品编码spbm的单价设置为指定单价。 Fetch next from crusor_spbm into spbm-将游标里面下一个商品编码值赋值给spbm，并且游标往下游一步。 END-循环结束CLOSE crusor_spbm-关闭游标crusor_spbmDEALLOCATE crusor_spbm-释放游标crusor_spbm任务5-10：执行此存储过程，将供应商编码为“0001”所供应的所有商品的出库信息单价强行更新为12.3元。EXEC Proc_ckdj_update 0001 ,12.3任务5-11：创建存储过程，更新指定入库单号的指定商品编码的单价为指定价格，并更新金额，同时也要更新该入库单主表的总金额。CREATE PROCEDURE Proc_rkje_update rkdh varchar(20) ，spbm varchar(20), dj numeric(12,2)-参数rkdh是指定的供应商编码，spbm 为指定的商品编码，dj是指定的单价，为2位小数的数字类型。Asbegin transaction-定义事务开始update 入库单明细表 set 单价=dj where 入库单号=rkdh and 商品编码=spbmupdate 入库单明细表 set 金额=数量*单价 where 入库单号=rkdh and 商品编码=spbmupdate 入库单主表 set 总金额=(select sum(金额) as zje from 入库单明细表 where 入库单号=rkdh) where 入库单号=rkdhIf Error 0 -当Error 0，即上面三个update语句有出错时，回滚事务到初始阶段。BEGINROLLBACK TRANSACTION ENDELSE-否则，即上面三个update语句都成功执行时，提交事务完成所有操作。 BEGINCOMMIT TRANSACTION END任务5-12：执行此存储过程，将入库单号为“0001”，商品编码为“00001”的单价改为12.3元并相应更改金额和总金额。EXEC Proc_ckdj_update 0001 ,0001,12.35.2.2 库存计算存储过程设计实例create proc pro_kcjs(ckbh varchar(100) =%,cw varchar(100)= %, spbm varchar(100)=%, spmc varchar(100)= % ) as create table #kc(ckbh varchar(100), cw varchar(100), spbm varchar(100), spmc varchar(100), sl numeric(12,2),)declare wherestr as varchar(5000)declare sqlstr as varchar(9000)-产生where条件set wherestr = where ckbh like & ckbh & and cw like & cw & and spbm like & spbm & and spmc like & spmc & -将期初库存插入临时表-产生字符串set sqlstr = insert into #kc(ckbh,cw,spbm,spmc,sl) select 仓库编号,仓位编号,商品编码,商品名称,期初数量 from 期初库存 & wherestr-执行字符串exec (sqlstr)-将期初库存插入临时表结束-将入库单插入临时表-产生字符串set sqlstr = insert into #kc(ckbh,cw,spbm,spmc,sl) select 仓库编号,仓位编号,商品编码,商品名称,数量 from 入库单明细表 & wherestr-执行字符串exec (sqlstr)-将入库单插入临时表结束-将出库单插入临时表-产生字符串set sqlstr = insert into #kc(ckbh,cw,spbm,spmc,sl) select 仓库编号,仓位编号,商品编码,商品名称,-数量 as 数量 from 出库单明细表 & wherestr-执行字符串exec (sqlstr)-将出库单插入临时表结束-将盘点表插入临时表-产生字符串set sqlstr = insert into #kc(ckbh,cw,spbm,spmc,sl) select 仓库编号,仓位编号,商品编码,商品名称,盈亏数量 from 盘点表明细表 & wherestr-执行字符串exec (sqlstr)-将盘点表插入临时表结束-将调拨单插入临时表-产生字符串set sqlstr = insert into #kc(ckbh,cw,spbm,spmc,sl) select 仓库编号,仓位编号,商品编码,商品名称,数量 from (select 调出仓库编号 as 仓库编号,调出仓位编号 as 仓位编号,商品编码,商品名称,-调拨数量 as 数量 from 调拨单 dbd left outer join 调拨单明细表 dbdmxb on dbd.调拨单号=dbdmxb. 调拨单号 union all select 调入出仓库编号 as 仓库编号,调出仓位编号 as 仓位编号,商品编码,商品名称,调拨数量 as 数量 from 调拨单 dbd left outer join 调拨单明细表 dbdmxb on dbd.调拨单号=dbdmxb. 调拨单号) a & wherestr-执行字符串exec (sqlstr)-将调拨单插入临时表结束-将报废单插入临时表-产生字符串set sqlstr = insert into #kc(ckbh,cw,spbm,spmc,sl) select 仓库编号,仓位编号,商品编码,商品名称,-报废数量 as 数量 from 报废单明细表 & wherestr-