摘别人的sqlserver知识

SQL游标学习

游标一般格式：

DECLARE 游标名称 CURSOR FOR SELECT 字段1,字段2,字段3,... FROM 表名 WHERE ...

OPEN 游标名称

FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...

WHILE @@FETCH_STATUS=0

         BEGIN

                   SQL语句执行过程... ...

                   FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...

         END

CLOSE 游标名称

DEALLOCATE 游标名称

例子：

/*
功能：数据库表格tbl_users数据
deptid userid username
1          100      a
1       101      b
2       102      c




要求用一个sql语句输出下面结果
deptid username

1        ab
2         c
[

要求用游标实现]


设计： OK_008
时间： 2006-05
备注：无
*/

create table #Temp1(deptid int,userid int,username varchar(20)) --待测试的数据表

create table #Temp2(deptid int,username varchar(20))                 --结果表

--先把一些待测试的数据插入到待测试表#Temp1中

insert into #Temp1

select 1,100,'a' union all

select 1,101,'b' union all

select 1,131,'d' union all

select 1,201,'f' union all

select 2,302,'c' union all

select 2,202,'a' union all

select 2,221,'e' union all

select 3,102,'y' union all

select 3,302,'e' union all

select 3,121,'t'

--

declare @deptid int,@username varchar(20)

--定义游标

declare Select_cursor cursor for

         select deptid,username from #Temp1

open Select_cursor

fetch next from Select_cursor into @deptid,@username     --提取操作的列数据放到局部变量中

while @@fetch_status=0      --返回被 FETCH 语句执行的最后游标的状态

/*

@@FETCH_STATUS =0           FETCH 语句成功

@@FETCH_STATUS =-1 FETCH 语句失败或此行不在结果集中

@@FETCH_STATUS =-2 被提取的行不存在

*/

         begin

                   --当表#Temp2列deptid存在相同的数据时，就直接在列username上追加@username值

                  if(exists(select * from #Temp2 where deptid=@deptid ))

                            update #Temp2 set username=username +@username where deptid=@deptid

                   else

                   --插入新数据

                            insert into #Temp2 select @deptid,@username

                   fetch next from Select_cursor into @deptid,@username

         end

close Select_cursor      

deallocate Select_cursor

select * from #Temp2 --测试结果

Drop table #Temp1,#Temp2

 

自动生成表的更新数据的存储过程

设计原因：在数据库设计中，有时候建立了很多表，每个表都有Insert、Update、Delete结构基本相同的存储，要是能有个自动生成表的更新数据的存储过程，就方便了我们不必浪费时间去写每一张表的Insert、Update、Delete存储过程。

设计方法：先提取表的各字段信息，包含字段的数据类型、数据定义长度、是否主键等。再根据提取出来的信息构造成表的更新数据的存储过程。下面的方法是有一个用户自定义函数FN_GetObjColInfo和一个存储过程SP_CreateProcdure来实现。

 

用户自定义函数FN_GetObjColInfo：

 

/*

功能：返回某一表的所有字段、存储过程、函数的参数信息

设计：OK_008

时间：2006-05

*/

CREATE FUNCTION FN_GetObjColInfo

(@ObjName varchar(50))

RETURNS  @Return_Table TABLE(

                   TName nvarchar(50),

                   TypeName nvarchar(50),

                   TypeLength nvarchar(50),

                   Colstat       Bit

                   ) 

AS 

BEGIN

         INSERT  @Return_Table

                   /*

                   主要是从系统表中提取表(对象)的各字段信息。

                   sysobjects: 在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）在表中占一行

                   syscolumns:每个表和视图中的每列在表中占一行，存储过程中的每个参数在表中也占一行

                   systypes: 保存数据类型和用户定义数据类型

                   */

                   select b.name as 字段名,c.name as 字段类型,b.length/2 as 字段长度,b.colstat as 是否自动增长

                   from sysobjects a

                   inner join syscolumns b on a.id=b.id

                   inner join systypes c on c.xusertype=b.xtype

                   where a.name =@ObjName

                   order by  B.ColID

         RETURN

END

GO

存储过程SP_CreateProcdure：

 

 

 

 

CREATE PROCEDURE SP_CreateProcdure

@TableName nvarchar(50)

 AS

/*

功能： 自动生成表的更新数据的存储过程

              如：当建立表MyTable后，执行SP_CreateProcdure ，生成表MyTable的数据更

                 新的存储过程UP_MyTable

设计： OK_008

时间： 2006-05

备注：

         1、请在查询分析器上执行：EXEC SP_CreateProcdure TableName

         2、由于生成的字符串长度合计很多时候存在>4000以上，所有只使用Print输出，

               再Copy即可。

         3、该方法能生成一般表的更新数据的存储过程，其中更新格式可以根据实际

               情况修改。

设计方法：

         1、提取表的各个字段信息

         2、 ──┰─ 构造更新数据过程

                 ├─ 构造存储过程参数部分

                 ├─ 构造新增数据部分

           ├─ 构造更新数据部分

                 ├─ 构造删除数据部分

         3、分段PRINT

         4、把输出来的结果复制到新建立存储过程界面中即可使用。

*/

DECLARE @strParameter nvarchar(3000)

DECLARE @strInsert nvarchar(3000)

DECLARE @strUpdate nvarchar(3000)

DECLARE @strDelete nvarchar(500)

DECLARE @strWhere  nvarchar(100)

DECLARE @strNewID  nvarchar(100)

DECLARE @SQL_CreateProc nvarchar(4000)

 

SET @SQL_CreateProc='CREATE PROCEDURE UP_'+@TableName +char(13)+'@INTUpdateID int,' +' /* -1 删除  0 修改  1新增 */'

SET @strParameter=''

SET @strInsert=''

SET @strUpdate=''

SET @strWhere=''

 

DECLARE @TName nvarchar(50),@TypeName nvarchar(50),@TypeLength nvarchar(50),@Colstat bit

DECLARE Obj_Cursor CURSOR FOR

SELECT * FROM  FN_GetObjColInfo(@TableName)

OPEN Obj_Cursor

FETCH NEXT FROM Obj_Cursor INTO @TName,@TypeName,@TypeLength,@Colstat

WHILE @@FETCH_STATUS=0

  BEGIN

         --构造存储过程参数部分

         SET @strParameter=@strParameter +CHAR(13)+'@'+ @TName + ' ' +@TypeName+

                                     (CASE

                                     WHEN @TypeName='nvarchar' THEN '('+@TypeLength+')'

                                     ELSE ''

                                     END)+','

         --构造新增数据部分

         IF @Colstat=0 SET @strInsert=@strInsert + '@'+ @TName  +','

         --构造更新数据部分

         IF (@strWhere='')

            BEGIN

                   IF @Colstat=0 SET @strNewID='SET @'+@TName+'=(Select ISNULL(MAX('+@TName+'),0) From '+@TableName+')+1    --取新的ID'