存储过程与游标应用实例(代20130423)

1、存储过程与游标应用实例20130423技术点：（1） 表类型的创建与使用（2） 带参数的存储过程的创建与运行（3） 游标的创建与使用（4） SQL编程（变量、循环、分支的应用）数据库关系图管理信息系统数据库SQL语句表与约束的SQL语句use master;gocreate database MyDB;go-use MyDB;gocreate table 岗位(岗位ID char(2) primary key,岗位名称 nvarchar(6) not null)go-一个员工只能一个岗位，一个岗位可以有多个员工create table 员工 (员工ID char(3) primary key,

2、员工姓名 nvarchar(6) not null,性别 nchar(1) not null, -男 或 女员工岗位ID char(2)go-use MyDB;go-增加外键约束（书P150 例8-11）alter table 员工 add constraint fk_员工_员工岗位ID foreign key(员工岗位ID) references 岗位(岗位ID);go-增加检查约束，保证员工的性别只能是男或女alter table 员工 add constraint chk_员工_性别 CHECK(性别 in(男,女);go-insert into 岗位 values(W0,经理);ins

3、ert into 岗位 values(W1,收银员);insert into 岗位 values(W2,采购员);goinsert into 员工 values(Y01,王峰,男,W0);insert into 员工 values(Y02,李慧,女,W1);insert into 员工 values(Y03,吴猛,男,W2);go-use MyDB;gocreate table 客户(客户ID varchar(6) primary key,客户名称 nvarchar(6) not null)create table 供应商(供应商ID varchar(3) primary key,供应商名称

4、nvarchar(6) not null)go-insert into 客户 values(000000,匿名);insert into 客户 values(000001,张三);insert into 客户 values(000002,李四);insert into 供应商 values(001,伊利);insert into 供应商 values(002,蒙牛);go-use MyDB;gocreate table 商品(商品条码 varchar(6) primary key,商品名称 nvarchar(30) not null,计价单位 nchar(1) not null,当前数量 in

5、t null,采购均价 smallmoney null,销售价格 smallmoney null,是否参与促销 nchar(1) null,-是 或 否促销价格 smallmoney null,定价员工ID char(3) null,定价日期 datetime null)go-增加检查约束，保证是否参与促销只能是是或否alter table 商品 add constraint chk_商品_是否参与促销 CHECK(是否参与促销 in(是,否);go-增加外键约束（书P150 例8-11）alter table 商品 add constraint fk_商品_定价员工ID foreign ke

6、y(定价员工ID) references 员工(员工ID);go-use MyDB;gocreate table 销售信息(销售ID varchar(6) primary key,收银员ID char(3) not null constraint fk_销售信息_员工ID foreign key references 员工(员工ID),客户ID varchar(6) not null constraint fk_销售信息_客户ID foreign key references 客户(客户ID),销售日期 datetime not null)gocreate table 销售明细(明细ID in

7、t identity(1,1) primary key,商品条码 varchar(6) not null constraint fk_销售明细_商品条码 foreign key references 商品(商品条码),销售数量 float not null,销售价格 smallmoney not null,销售ID varchar(6) not null constraint fk_销售明细_销售ID foreign key references 销售信息(销售ID),)go-use MyDB;gocreate table 采购信息(采购ID varchar(6) primary key,采购

8、员ID char(3) not null constraint fk_采购信息_采购员ID foreign key references 员工(员工ID),供应商ID varchar(3) not null constraint fk_采购信息_供应商ID foreign key references 供应商(供应商ID),采购日期 datetime not null)gocreate table 采购明细(明细ID int identity(1,1) primary key,商品条码 varchar(6) not null constraint fk_采购明细_商品条码 foreign ke

9、y references 商品(商品条码),商品名称 nvarchar(30) not null,计价单位 nchar(1) not null,采购数量 float not null,采购价格 smallmoney not null,采购ID varchar(6) not null constraint fk_采购明细_采购ID foreign key references 采购信息(采购ID),)-基础数据创建的SQL语句use MyDB;goinsert into 岗位 values(W0,经理);insert into 岗位 values(W1,收银员);insert into 岗位 v

10、alues(W2,采购员);goinsert into 员工 values(Y01,王峰,男,W0);insert into 员工 values(Y02,李慧,女,W1);insert into 员工 values(Y03,吴猛,男,W2);go-insert into 客户 values(000000,匿名);insert into 客户 values(000001,张三);insert into 客户 values(000002,李四);insert into 供应商 values(001,伊利);insert into 供应商 values(002,蒙牛);go-存储过程采购存储过程us

11、e MyDB;godrop type 采购明细临时表;gocreate type dbo.采购明细临时表 as Table ( 商品条码 varchar(6) not null, 商品名称 nvarchar(30) not null,计价单位 nchar(1) not null,采购数量 float not null,采购价格 smallmoney not null) Gouse MyDB;gocreate procedure Purchase采购ID varchar(6),采购员ID char(3),供应商ID varchar(3),采购日期 datetime,采购明细记录 as 采购明细临

12、时表 readonlyas begin declare 商品条码 varchar(6); declare 商品名称 nvarchar(30); declare 计价单位 nchar(1); declare 采购数量 float; declare 采购价格 smallmoney; declare 当前商品数量 float; declare 当前采购价格 smallmoney; DECLARE TmpCur CURSOR LOCAL SCROLL FOR SELECT 商品条码,商品名称,计价单位,采购数量,采购价格 FROM 采购明细记录 OPEN TmpCur INSERT INTO 采购信息

13、 VALUES(采购ID,采购员ID,供应商ID,采购日期)FETCH NEXT FROM TmpCur INTO 商品条码,商品名称,计价单位,采购数量,采购价格 WHILE FETCH_STATUS=0 BEGIN IF EXISTS(SELECT * from 商品 where 商品条码=商品条码) begin SELECT 当前商品数量=当前数量,当前采购价格=采购均价 from 商品 where 商品条码=商品条码 set 当前采购价格=(当前商品数量*当前采购价格+采购数量*采购价格)/(当前商品数量+采购数量) set 当前商品数量=当前商品数量+采购数量 update 商品 s

14、et 当前数量=当前商品数量,采购均价=当前采购价格 where 商品条码=商品条码 end else begin insert into 商品 VALUES(商品条码,商品名称,计价单位,采购数量,采购价格,null,null,null,null,null) end INSERT INTO 采购明细 VALUES(商品条码,商品名称,计价单位,采购数量,采购价格,采购ID) FETCH NEXT FROM TmpCur INTO 商品条码,商品名称,计价单位,采购数量,采购价格 ENDCLOSE TmpCurDEALLOCATE TmpCur endgouse MyDB;goDECLARE

15、采购明细记录 as 采购明细临时表;INSERT INTO 采购明细记录 VALUES(m0001,250ML伊利牛奶,盒,100,$1.8);INSERT INTO 采购明细记录 VALUES(m0002,1000ML伊利牛奶,盒,200,$4.8);exec Purchase Buy01,Y03,001,02/13/2013,采购明细记录;go采购存储过程运行结果运行命令：use MyDB;goDECLARE 采购明细记录 as 采购明细临时表;INSERT INTO 采购明细记录 VALUES(m0001,250ML伊利牛奶,盒,100,$1.8);INSERT INTO 采购明细记录

16、VALUES(m0002,1000ML伊利牛奶,盒,200,$4.8);exec Purchase Buy01,Y03,001,02/13/2013,采购明细记录;go运行结果：use MyDB;goDECLARE 采购明细记录 as 采购明细临时表;INSERT INTO 采购明细记录 VALUES(m0001,250ML伊利牛奶,盒,150,$1.8);INSERT INTO 采购明细记录 VALUES(m0002,1000ML伊利牛奶,盒,250,$4.8);exec Purchase Buy02,Y03,001,02/15/2013,采购明细记录;go销售存储过程商品定价存储过程附录1

17、2 动态SQL语句这个用动态语句： create proc aa a varchar(10) as begindeclare sql as varchar(100) set a=a set sql=select * from +a exec(sql)end如何使用GUID填充IDGUID（Global unique identifier）全局唯一标识符，它是由网卡上的标识数字(每个网卡都有唯一的标识号)以及 CPU 时钟的唯一数字生成的的一个 16 字节的二进制值。GUID 的格式为“xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx”，其中每个 x 是 0-9 或 a-

18、f 范围内的一个十六进制的数字。例如：6F9619FF-8B86-D011-B42D-00C04FC964FF 即为有效的 GUID 值。世界上的任何两台计算机都不会生成重复的 GUID 值。GUID 主要用于在拥有多个节点、多台计算机的网络或系统中，分配必须具有唯一性的标识符。在 Windows 平台上，GUID 应用非常广泛：注册表、类及接口标识、数据库、甚至自动生成的机器名、目录名等。1.自增量字段 自增量字段每次都会按顺序递增，可以保证在一个表里的主键不重复。除非超出了自增字段类型的最大值并从头递增，但这几乎不可能。使用自增量字段来做主键是非常简单的，一般只需在建表时声明自增属性即可。

19、 自增量的值都是需要在系统中维护一个全局的数据值，每次插入数据时即对此次值进行增量取值。当在当量产生唯一标识的并发环境中，每次的增量取值都必须最此全局值加锁解锁以保证增量的唯一性。这可能是一个并发的瓶颈，会牵扯一些性能问题。在数据库迁移或者导入数据的时候自增量字段有可能会出现重复,这无疑是一场恶梦(本人已经深受其害). 如果要搞分布式数据库的话，这自增量字段就有问题了。因为，在分布式数据库中，不同数据库的同名的表可能需要进行同步复制。一个数据库表的自增量值，就很可能与另一数据库相同表的自增量值重复了。2.uniqueidentifier(Guid)字段 在MS Sql 数据库中可以在建立表结构

20、是指定字段类型为uniqueidentifier,并且其默认值可以使用NewID()来生成唯一的Guid(全局唯一标识符).使用NewID生成的比较随机,如果是SQL 2005可以使用NewSequentialid()来顺序生成,在此为了兼顾使用SQL 2000使用了NewID().Guid:指在一台机器上生成的数字，它保证对在同一时空中的所有机器都是唯一的，其算法是通过以太网卡地址、纳秒级时间、芯片ID码和许多可能的数字生成。其格式为:04755396-9A29-4B8C-A38D-00042C1B9028. Guid的优点就是生成的id比较唯一,不管是导出数据还是做分步开发都不会出现问题.

21、然而它生成的id比较长,占用的数据库空间也比较多,随着外存价格的下降,这个也无需考虑.另外Guid不便于记忆,在这方面不如自动增量字段,在作调试程序的时候不太方便。insert into t_table (id) values (newid()newid() 可以得到 guidid这个字段类型必须是 uniqueidentifier 类型的。use MyDB;godeclare myid uniqueidentifier;set myid=newid();print myid=+Convert(varchar(1000),myid)go注意:函数newid()可以单用,但是newsequent

22、ialid()只能作为缺省值用在表里用(见下面实例)use MyDB;goCREATE TABLE MyUniqueTableB(UniqueColumn UNIQUEIDENTIFIER DEFAULT newsequentialid(), Characters VARCHAR(10) ) GO INSERT INTO MyUniqueTableB(Characters) VALUES (uiok1) INSERT INTO MyUniqueTableB(Characters) VALUES (uiok2)INSERT INTO MyUniqueTableB(Characters) VALU

23、ES (uiok3) INSERT INTO MyUniqueTableB(Characters) VALUES (uiok4)GO select * from MyUniqueTableB;gouse MyDB;goCREATE TABLE MyUniqueTableA(UniqueColumn UNIQUEIDENTIFIER DEFAULT newid(), Characters VARCHAR(10) ) GO INSERT INTO MyUniqueTableA(Characters) VALUES (uiok1) INSERT INTO MyUniqueTableA(Charact

24、ers) VALUES (uiok2)INSERT INTO MyUniqueTableA(Characters) VALUES (uiok3) INSERT INTO MyUniqueTableA(Characters) VALUES (uiok4)GO select * from MyUniqueTableA;goCREATE TABLE Globally_Unique_Data (guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWID(), Employee_Name varchar(60), CONSTRAINT Guid

25、_PK PRIMARY KEY (Guid) )1.测试环境操作系统:windows server 2003 R2 Enterprise Edition Service Pack 2数据库:MS SQL 2005CPU:Intel(R) Pentium(R) 4 CPU 3.40GHz内存:DDR 667 1G硬盘:WD 80G2.数据库脚本 -自增量字段表 CREATE TABLE dbo.Table_Id( Id int IDENTITY(1,1) NOT NULL, Value varchar(50) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT

26、PK_Table_Id PRIMARY KEY CLUSTERED ( Id ASC )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY ) ON PRIMARY GO -Guid字段表 CREATE TABLE dbo.Table_Guid( Guid uniqueidentifier NOT NULL CONSTRAINT DF_Table_Guid_Guid DEFAULT (newid(), Value varchar(50) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT PK_Table_Guid PRIMARY K

27、EY CLUSTERED ( Guid ASC )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY ) ON PRIMARY GO总结:使用Guid作主键速度并不是很慢,它反而要比使用自动增长型的增量速度还要快.uniqueidentifier 数据类型(转) 想要产生这种唯一标识的格式的数据： 6F9619FF-8B86-D011-B42D-00C04FC964FF 应该怎么做呢？ = 答： uniqueidentifier 数据类型可存储 16 字节的二进制值，其作用与全局唯一标识符 (GUID) 一样。GUID 是唯一的二进制数；世界上的任何两台计算机都不会生

28、成重复的 GUID 值。GUID 主要用于在拥有多个节点、多台计算机的网络中，分配必须具有唯一性的标识符。 uniqueidentifier 列的 GUID 值通常通过下列方式之一获取： 在 Transact-SQL 语句、批处理或脚本中调用 NEWID 函数。 在应用程序代码中，调用返回 GUID 的应用程序 API 函数或方法。 Transact-SQL NEWID 函数以及应用程序 API 函数和方法用它们的网卡的标识号加上 CPU 时钟的唯一编号来生成新的 uniqueidentifier 值。每个网卡都有唯一的标识号。NEWID 返回的 uniqueidentifier 值是通过使用

29、服务器上的网卡而生成的。应用程序 API 函数和方法返回的 uniqueidentifier 值是通过使用客户端中的网卡而生成的。 uniqueidentifier 值通常不定义为常量。您可以按下列方式指定 uniqueidentifier 常量： 字符串格式：6F9619FF-8B86-D011-B42D-00C04FC964FF 二进制格式：0xff19966f868b11d0b42d00c04fc964ff uniqueidentifier 数据类型不会按照 IDENTITY 属性的方式为插入的行自动生成新的 ID。例如，若要获取新的 uniqueidentifier 值，则表必须具有指

30、定 NEWID 函数或 NEWSEQUENTIALID 函数的 DEFAULT 子句，或 INSERT 语句必须使用 NEWID 函数。 CREATE TABLE MyUniqueTable (UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID(), Characters VARCHAR(10) ) GO INSERT INTO MyUniqueTable(Characters) VALUES (uiok) INSERT INTO MyUniqueTable VALUES (NEWID(), uiok) GO 注意： 您可以使用 NEWSEQUENTIALI

31、D 生成 GUID 以减少叶级别索引上的页争用。NEWSEQUENTIALID 只能与 uniqueidentifier 类型的表列的 DEFAULT 约束一起使用。 uniqueidentifier 列中可以多次出现某个 uniqueidentifier 值，除非对该列也指定了 UNIQUE 或 PRIMARY KEY 约束。当多个行引用源表中的同一个主键时，引用其他表中 uniqueidentifier 主键的外键列中可以多次出现各个 uniqueidentifier 值。 一个表可以有多个 uniqueidentifier 列。每个表中可以指定一个具有 ROWGUIDCOL 属性的 un

32、iqueidentifier 列。ROWGUIDCOL 属性指明此列的 uniqueidentifier 值可唯一地标识表中的行。但是，属性不会执行任何强制实现唯一性的操作。必须使用其他机制强制实现唯一性，例如指定列的 PRIMARY KEY 约束。ROWGUIDCOL 属性主要用于 Microsoft SQL Server 2005 复制。具有更新订阅的合并复制和事务复制使用 uniqueidentifier 列来确保在表的多个副本中唯一地标识行。 uniqueidentifier 数据类型具有下列缺点： 值长且难懂。这使用户难以正确键入它们，并且更难记住。 这些值是随机的，而且它们不支持任

33、何使其对用户更有意义的模式。 也没有任何方式可以决定生成 uniqueidentifier 值的顺序。它们不适用于那些依赖递增的键值的现有应用程序。 当 uniqueidentifier 为 16 字节时，其数据类型比其他数据类型（例如 4 字节的整数）大。这意味着使用 uniqueidentifier 键生成索引的速度相对慢于使用 int 键生成索引的速度。 在不要求全局唯一性或首选使用按序列增加的键时，请考虑使用 IDENTITY 属性。uniqueidentifier 与 IDENTITYuniqueidentifier 根据 NEWID 产生的值是唯一的，可以作为主键，IDENTITY

34、 表示标识，也是自增的，可以作为主键，那么用哪个好呢。一、uniqueidentifier 不适用于 Access 数据库。二、uniqueidentifier 长度为 16，如此长的值很难让人记住；IDENTITY 一般用 int 类型即长度为 4，且为数字较好记。三、uniqueidentifier 不能像 IDENTITY 一样判断记录插入的先后顺序。四、uniqueidentifier索引占用的空间更大，所以其效率比int索引的效率低一些。五、uniqueidentifier主要用于在拥有多个节点、多台计算机的网络，必须分配具有唯一性的标识符，因为世界上的任何两台计算机都不会生成重复的

35、GUID值。所以有人建议用 uniqueidentifier 代替 IDENTITY 是缺乏根据的，是片面的。uniqueidentifier数据类型在系统设计中的应用一 什么是uniqueidentifier？Uniqqueidentifier 是全局唯一的标识二 UniqueIdentifier 数据类型的列如何赋值？ 1 使用 NewID（）函数 来实现 2 直接将字符串的常量转化成这样的格式 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx 举例：6F9619FF-8B86-D011-B42D-00C04FC964FF 为有效的UniqueIdentifier数

36、据 3 直接赋于32位的十六位数据 举例 0xffffffff00000000ffffffff00000000三 UniqueIdentifier 数据类型 数据实际是怎么在数据库中保存的？UniqueIdentifier 数据类型存储实际的数据是16个字节的二进制值，UniQueIdentifier 可以转化成实际的字符串型和二进制数据类型四 NewID（）函数是如何生成唯一的UniqueIdentifier 值的呢？NewID（）函数是从他们的网卡上的标识数字和CPU时钟的唯一的数字生成新的UniqueIdentifier数据 ，这个数据和GUID是一样的每台计算机能生成全球唯一的值,这样在多台计算机和多网络之间生成具有唯一性的标识符五 使用 Uniqueidentifier数据类型的主要的优点 Uniqu