摘别人的sqlserver知识-C++-编程技术-六狼论坛-IT论坛-计算机论坛

zhuyx808 发表于 2013-1-27 05:42:50

摘别人的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'

页: [1]

六狼论坛's Archiver

摘别人的sqlserver知识