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
[ 本帖最后由 DVD 于 2006-12-14 17:08 编辑 ]
作者: DVD 时间: 2006-12-14 16:36
/* 功能：返回某一表的所有字段、存储过程、函数的参数信息 设计：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：
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' SET @strWhere=' WHERE '+@TName+'='+'@'+@TName END ELSE SET @strUpdate=@strUpdate+@TName+'='+'@'+@TName +',' --构造删除数据部分 FETCH NEXT FROM Obj_Cursor INTO @TName,@TypeName,@TypeLength,@Colstat END CLOSE Obj_Cursor DEALLOCATE Obj_Cursor
SET @strParameter=LEFT(@strParameter,LEN(@strParameter)-1) --去掉最右边的逗号 SET @strUpdate=LEFT(@strUpdate,LEN(@strUpdate)-1) SET @strInsert=LEFT(@strInsert,LEN(@strInsert)-1)
-- ====================================== SET @Year='2006' --请在这里输入年份 -- ====================================== --设置日历上边的标题格式 SET @strTop= '日'+char(9)+'一'+char(9)+'二' +char(9)+'三'++char(9)+'四'++char(9)+'五'++char(9)+'六' +char(13)+ '───────────────────────────' --设置星期列表 SET @strWeekDayList='日一二三四五六' SET @ForYear=1 WHILE @ForYear<=12 --1月份至12月份 BEGIN --取当月格式 SET @YearMonth=@Year + '-' +CAST( @ForYear AS nvarchar(2)) --取当月的最大日期 SET @MaxDay=DAY(DATEADD(Day,-1,DATEADD(Month,1,@YearMonth+'-01'))) --找出1号的开始位置 SET @RowX=CHARINDEX(RIGHT(DATENAME(WeekDay,@YearMonth+'-01'),1),@strWeekDayList)-1 SET @strPrint='' SET @ForI=1 WHILE @ForI<=@RowX --构造1号的位置，并绘画空白处 BEGIN SET @strPrint=@strPrint+CHAR(9) SET @ForI=@ForI+1 END SET @ForI=1 WHILE @ForI<=@MaxDay --构造2号到月底的位置，并绘画 BEGIN SET @strPrint=@strPrint+CAST(@ForI AS nvarchar(2)) +Char(9) SET @RowX=@RowX+1 SET @ForI=@ForI+1 IF (@RowX%7=0) --满一个星期就换行 BEGIN SET @RowX=0 SET @strPrint=@strPrint+CHAR(13) END END SET @ForYear=@ForYear+1 -- 打印输出一个月的结果 PRINT '━━━━━━━━━━━━━━━━━━━━━━━━━━━' PRINT +Char(9)++Char(9)+' '+@YearMonth+CHAR(10) PRINT @strTop PRINT @strPrint +CHAR(10) END 执行结果：
CREATE TABLE #Temp(ID int,ReferID int,ReferCount int) INSERT INTO #Temp SELECT 1, 0, 2 UNION ALL SELECT 2, 1, NULL UNION ALL SELECT 3, 2, NULL UNION ALL SELECT 4, 3, 1 DECLARE @t1 int DECLARE @t2 int DECLARE @t3 int DECLARE @t4 int --根据ID的唯一性来查询 SELECT @t1=CASE ID WHEN 1 THEN ReferCount ELSE @t1 END ,@t2=CASE ID WHEN 2 THEN ReferCount ELSE @t2 END ,@t3=CASE ID WHEN 3 THEN ReferCount ELSE @t3 END ,@t4=CASE ID WHEN 4 THEN ReferCount ELSE @t4 END FROM #Temp SELECT @t1 AS '@t1',@t2 AS '@t2',@t3 AS '@t3',@t4 AS '@t4' DROP TABLE #Temp /* 运行结果： @t1|@t2|@t3|@t4 -------------------- 2 |NULL|1 |NULL */
作者: DVD 时间: 2006-12-14 16:52
SET DATEFIRST 7 --设置每周的第一天 SET @StartDate='2006-01-01' --统计的开始日期 SET @EndDate='2006-12-31' --统计的结束日期 SET @WeekDay=2 --根据实际的@@DATEFIRST而定，一般默认是7，如 @StartDate='2006-01-01'时候， @WeekDay=3表示星期二 SET @i=DATEPART(weekday,@StartDate) PRINT '每周的第1天设置@@DATEFIRST: '+CAST(@@DATEFIRST AS nvarchar(1)) PRINT '开始日期对应一周的第几天: '+CAST(@i AS nvarchar(1)) IF(@i<=@WeekDay AND @i<7) SET @i=@WeekDay-@i ELSE IF(@i<=@WeekDay AND @i=7) SET @i=@i-@WeekDay ELSE SET @i=@@DATEFIRST-@i+@WeekDay
SET @Date=DATEADD(day,@i,@StartDate) WHILE @Date<=@EndDate BEGIN IF(@StartDate<=@Date) PRINT CONVERT(nvarchar(10),@Date,121) SET @Date=DATEADD(Week,1,@Date) END GO
/* 功能：实现字符串的分割，达到批量提交数据参数和分批处理功能。 设计：wgh 时间：2006-09 */ declare @str1 nvarchar(100) --要分割的字符串 declare @str2 nvarchar(100) --分割得的子字符串 declare @split nvarchar(40) --分割字符or字符串 declare @patIndex int --分割字符or字符串第一次出现的起始位置 set @split='★■->' set @str1='334,345,''dfd'',★■->select * from Employee★■->45654DFG★■->452897★■->97887657★■->123445' set @str2='' --从左至右分割 while @str1<>@str2 begin set @patIndex=patindex('%'+@split+'%',@str1) --patindex函数返回@str1中@split第一次出现的起始位置 if @patIndex>0 set @str2=left(@str1,@patIndex-1) --截取@str1中以@split为分割的左边的字符串 else set @str2=@str1 print @str2 --调试 /* 执行过程 .... */ if @patIndex>0 set @str1=right(@str1,len(@str1)-len(@str2)-len(@split)) --截断字符串，为的是下一次循环能取得，@str1中@split第一次出现的起始位置 end
作者: DVD 时间: 2006-12-14 16:54
查找某一数据库没有主鍵的所有用户表
/* 功能：查找所有(某一)数据库没有主鍵的所有用户表 设计：wgh 时间：2006-06 */
/* 设计方法：使用游标的方法，从系统表sysobjects、syscolumns、sysindexes提取表的相关信息。 */ DECLARE @DatabaseName nvarchar(20) DECLARE @Execute_Sql nvarchar(4000) SET @DatabaseName='ydhr' --要是要检查所有数据库就SET @DatabaseName='' CREATE table #Temp(DatabaseName nvarchar(20),TableName nvarchar(50)) IF ISNULL(@DatabaseName,'')='' DECLARE cursor_Sql CURSOR FOR SELECT name FROM master..sysdatabases WHERE dbid>=7 --dbid>=7 都是属于用户建立的数据库 ELSE DECLARE cursor_Sql CURSOR FOR SELECT name FROM master..sysdatabases WHERE dbid>=7 AND name=@DatabaseName OPEN cursor_Sql FETCH NEXT FROM cursor_Sql INTO @DatabaseName WHILE @@FETCH_STATUS=0 BEGIN SET @Execute_Sql='INSERT INTO #Temp SELECT '''+@DatabaseName+''',A.name FROM '+@DatabaseName+'..sysobjects AS A WHERE OBJECTPROPERTY(A.id, N''IsUserTable'') = 1 AND NOT EXISTS( SELECT 1 FROM '+@DatabaseName+'..sysindexes AS i INNER JOIN '+@DatabaseName+'..sysindexkeys k ON i.id = k.id AND i.indid = k.indid INNER JOIN '+@DatabaseName+'..sysobjects AS o ON i.id = o.id INNER JOIN '+@DatabaseName+'..syscolumns c on i.id=c.id and k.colid = c.colid WHERE o.xtype = ''U'' AND A.id=i.id AND EXISTS(SELECT 1 FROM '+@DatabaseName+'..sysobjects WHERE xtype = ''PK'' AND name = i.name) )' /* OBJECTPROPERTY（）返回当前数据库中对象的有关信息,OBJECTPROPERTY(对象id, N''IsUserTable'') 表示是否为用户表 xtype='U' 在系统表sysobjects中表示查询的对象属于用户表，与OBJECTPROPERTY(对象id, N''IsUserTable'')功能一致，只是用法格式不同 xtype='PK' PRIMARY KEY 约束（类型是 K） */ EXECUTE(@Execute_Sql) PRINT @Execute_Sql --为了能更清楚语句的含义，我这里可以使用PRINT查看整条执行语句 FETCH NEXT FROM cursor_Sql INTO @DatabaseName END CLOSE cursor_Sql DEALLOCATE cursor_Sql SELECT * FROM #Temp DROP TABLE #Temp
--重新命名数据库 IF EXISTS(SELECT 1 FROM master..sysdatabases WHERE name='BBS') EXECUTE sp_rename @objname='BBS',@newname='BBS1',@objtype='DATABASE'
--重新命名表名 IF EXISTS(SELECT 1 FROM sysobjects WHERE name='bbs_log' AND OBJECTPROPERTY(id,'IsUserTable')=1) EXECUTE sp_rename @objname='bbs_log',@newname='bbs_log1',@objtype='OBJECT'
--重新命名列名 IF EXISTS(SELECT 1 FROM syscolumns WHERE id=OBJECT_ID('bbs_log') AND name='ColumnName' AND OBJECTPROPERTY(id,'IsUserTable')=1) EXECUTE sp_rename @objname='bbs_log.[ColumnName]',@newname='NewColumnName',@objtype='COLUMN'
作者: DVD 时间: 2006-12-14 16:56
格式化字符串 ，如输入01 自动生成 0000001
/*=================================================== 功能：格式化字符串 ，如输入01 自动生成 0000001 备注：当位数达到10以上,要另写算法,否则出错 设计：weiguohao 日期：2006-03-11 ===================================================*/ CREATE FUNCTION FORMAT_STR (@strX Nvarchar(20),@FormatLength INT) RETURNS NVARCHAR(20) AS BEGIN DECLARE @Return NVARCHAR(20) DECLARE @Length INT SET @Length=LEN(@strX) IF @Length>=@FormatLength SET @Return=@strX ELSE BEGIN -- 格式化，只要是调用到数学函数POWER，其他就不再说明，太简单了 SET @Return=CAST(RIGHT(POWER(10,@FormatLength),@FormatLength-@Length) AS NVARCHAR(20))+@strX END RETURN @Return END
作者: DVD 时间: 2006-12-14 16:57
一个厂家与销售商的查询问题问题来来自itpub论坛上的一位朋友，如下： 多个厂家多个销售商每个厂家对任一销售商都免费前10次的货款有厂家表，销售表，销售商表想求得销售商三个表拼合一起的视图视图中有个字段标出免费的销售记录？ 厂家表：ID ，名称销售表：ID，销售单号 厂家：ID，销售商ID 销售商：ID，销售商名称预得视图：销售单ID，厂家ID，销售商ID，是否免费谢谢哥哥姐姐了。帮忙写写…… 解答： CREATE TABLE #Suppliers(SupplierID int,CompanyName nvarchar(40)) --厂家表 CREATE TABLE #Sell(SellID int,SupplierID int,CustomerID int) --销售表 CREATE TABLE #Customers(CustomerID int,CompanyName nvarchar(40)) --销售商、客户表 INSERT INTO #Suppliers SELECT 1,N'Supplier1' UNION ALL SELECT 2,N'Supplier2' INSERT INTO #Customers SELECT 1,N'Customer1' UNION ALL SELECT 2,N'Customer2' INSERT INTO #Sell SELECT 1,1,1 UNION ALL SELECT 2,1,1 UNION ALL SELECT 3,1,1 UNION ALL SELECT 4,1,1 UNION ALL SELECT 5,1,1 UNION ALL SELECT 6,1,1 UNION ALL SELECT 7,1,1 UNION ALL SELECT 8,1,2 UNION ALL SELECT 9,1,2 UNION ALL SELECT 10,1,2 UNION ALL SELECT 11,1,2 UNION ALL SELECT 12,1,2 UNION ALL SELECT 13,1,2 UNION ALL SELECT 14,1,2 UNION ALL SELECT 15,2,1 UNION ALL SELECT 16,2,1 UNION ALL SELECT 17,2,2 UNION ALL SELECT 18,2,2 UNION ALL SELECT 18,2,2 --每个厂家对任一销售商都免费前3次的货款（根据实际修改提前多少次是免费的货款） --主要是使用到一个GROUP BY ...HAVING ...在对数据进行分组和聚合后，就会用到 HAVING 子句中的条件 SELECT A.*,(CASE WHEN EXISTS(SELECT 1 FROM #Sell AS B WHERE A.SupplierID=B.SupplierID AND A.CustomerID=B.CustomerID AND B.SellID<=A.SellID GROUP BY B.CustomerID HAVING COUNT(1)<=3 ) THEN '是' ELSE '否' END) AS '是否免费' FROM #Sell AS A DROP TABLE #Suppliers,#Sell,#Customers
如果不用游标有办法实现吗？ 解决方法： CREATE TABLE #T1(aid int ,bid int) INSERT INTO #T1 SELECT 1,10 UNION ALL SELECT 1,11 UNION ALL SELECT 1,12 UNION ALL SELECT 2,10 UNION ALL SELECT 2,13 CREATE TABLE #T2(bid int,bname nvarchar(10),bprice int) INSERT INTO #T2 SELECT 10,'aaa',1 UNION ALL SELECT 11,'bbb',2 UNION ALL SELECT 12,'ccc',3 UNION ALL SELECT 13,'ddd',4 SELECT A.aid,B.bname,B.bprice INTO #T3 FROM #T1 AS A LEFT OUTER JOIN #T2 AS B ON A.bid=B.bid DECLARE @aid int ,@lastaid int UPDATE #T3 SET @aid=(CASE WHEN ISNULL(@lastaid,'') =aid THEN NULL ELSE aid END),aid=@aid,@lastaid=aid SELECT * FROM #T3 DROP TABLE #T1,#T2,#T3 结果： /* 1 aaa 1 NULL bbb 2 NULL ccc 3 2 aaa 1 NULL ddd 4 */
作者: DVD 时间: 2006-12-14 17:02
一个数据替换问题
原来的问题： 我要將([4000]+[3900])/([3100]+[3200]+[3900])*[3900]替换成 (0+0)/(0+0+0)*0 就是把[4000]樣的替換成０ 解决方法如下， 建立一个自定义函数： CREATE FUNCTION CreateMathExpression (@ColumnName nvarchar(500)) RETURNS nvarchar(1000) AS BEGIN DECLARE @FieldValue nvarchar(20) DECLARE @Return nvarchar(1000) DECLARE @Sql nvarchar(1000) DECLARE @Index int DECLARE @End bit SET @End=0 SET @Return='' --SET @ColumnName='[3900]/222.8*([3200]/[3900])' SET @Index=CHARINDEX('[',@ColumnName) WHILE @Index<>0 BEGIN IF @End=0 BEGIN SET @Index=CHARINDEX('[',@ColumnName) IF @Index=0 GOTO Exit_While SET @Return=@Return+LEFT(@ColumnName,@Index-1) SET @End=1 END ELSE BEGIN SET @Index=CHARINDEX(']',@ColumnName) SET @FieldValue=LEFT(@ColumnName,@Index-1)
SET @Return=@Return+ '0' SET @End=0 END SET @ColumnName=RIGHT(@ColumnName,len(@ColumnName)-@Index) END Exit_While: SET @Return=@Return+@ColumnName RETURN @Return END --------------------------------------------------------------------例子:DECLARE @N nvarchar(200) SET @N='([4000]+[3900])/([3100]+[3200]+[3900])*[3900]' SELECT @N SELECT dbo.CreateMathExpression(@N) -------------------------结果: (0+0)/(0+0+0)*0 备注：函数中的一些代码是别的用途,可以忽略。作者: DVD 时间: 2006-12-14 17:04
向表中一列插入1-100的连续数
在BBS上看到一朋友问到这样的一个问题： 求助：向表中一列插入1-100的连续数
table A 中有BH字段（INT），怎么连续给BH赋值1-100,象下面的一样
BH ------------------------------------------------ 1 2 . . . 100 根据常规的方法，我们会想到使用While来实现以上的功能。如，一朋友所写的这样： CREATE TABLE TestTable(BH INT) GO SET NOCOUNT ON /* SET NOCOUNT { ON | OFF } 当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。当 SET NOCOUNT 为 OFF 时，返回计数 */ GO DECLARE @MyCounter INT SET @MyCounter = 1 WHILE (@MyCounter < 101) BEGIN INSERT INTO TestTable VALUES
(@MyCounter )
SET @MyCounter = @MyCounter + 1 END GO SET NOCOUNT OFF GO select * from TestTable --drop table TestTable 我看一下，突然想到一个使用变量的方法来实现，如下： DECLARE @i int CREATE TABLE #Temp ([id] int, C1 nvarchar(20),BH int) SET @i=400 WHILE @i<=500 BEGIN INSERT INTO #Temp SELECT @i,CAST(@i+rand() as nvarchar(20)),null SET @i=@i+1 END SELECT * FROM #Temp --更新字段BH,更新行数为前100行 SET @i=0 --@i相当于For循环中的i,每更新一条记录设置加@i=@i+1 UPDATE #Temp SET @i=@i+1 ,BH=@i WHERE [id] IN(SELECT TOP 100 [id] FROM #Temp) SELECT * FROM #Temp DROP TABLE #Temp
附件: 绘制结果图.jpg (2006-12-14 16:47, 32.08 K) / 该附件被下载次数 1