SQL游标学习游标一般格式：5 Y- d) H4 y5 O0 }- H5 a
DECLARE 游标名称 CURSOR FOR SELECT 字段1,字段2,字段3,... FROM 表名 WHERE ...8 C( o" t0 m- _: a
OPEN 游标名称
FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...0 u/ f, f p: b" I2 B7 o- ~" ~2 F
WHILE @@FETCH_STATUS=0 j: X, C: N' q6 _/ V/ j
BEGIN% h& M9 D$ w$ V; s- K5 I
SQL语句执行过程... ...
FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...% R7 }' N0 s2 v/ H
END
CLOSE 游标名称& Q; V2 y! i* L! C" r, }) g
DEALLOCATE 游标名称
例子：
/*
功能：数据库表格tbl_users数据6 k1 G% l1 q% j( F6 A% }
deptid userid username
1 100 a
1 101 b
2 102 c
要求用一个sql语句输出下面结果
deptid username
1 ab1 e" d' X6 f' ?. z
2 c
[要求用游标实现]2 b2 x; ]" l7 F4 e0 _1 `- _
设计： OK_008" Q2 m% m/ m& ~' U. ~. V
时间： 2006-05
备注：无6 Z S: q/ v: W; r- K1 b1 }7 r
*/
create table #Temp1(deptid int,userid int,username varchar(20)) --待测试的数据表6 C" F/ b2 D( O+ R
create table #Temp2(deptid int,username varchar(20)) --结果表: D* p( E; r3 F& L; h
--先把一些待测试的数据插入到待测试表#Temp1中
insert into #Temp16 l* @$ J, s" z
select 1,100,'a' union all: k: D0 ^8 [" Z' V; M
select 1,101,'b' union all
select 1,131,'d' union all
select 1,201,'f' union all
select 2,302,'c' union all ) z' h' M) M& F5 R# g8 X$ X& |
select 2,202,'a' union all
select 2,221,'e' union all
select 3,102,'y' union all ; f+ x0 x; T& ~+ w1 u* D1 K
select 3,302,'e' union all
select 3,121,'t'
--( o$ X2 y7 O' @0 @; ?
declare @deptid int,@username varchar(20)" g& P: @, /8 /1 }
--定义游标
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 语句执行的最后游标的状态' W' c+ u# ?$ w& f- r
/*$ l0 N9 A# W e! Y* k: N' G
@@FETCH_STATUS =0 FETCH 语句成功
@@FETCH_STATUS =-1 FETCH 语句失败或此行不在结果集中+ m& K. y" e7 ?" ^9 /$ F _
@@FETCH_STATUS =-2 被提取的行不存在
*/& u! y6 Y3 X' L' q- [
begin
--当表#Temp2列deptid存在相同的数据时，就直接在列username上追加@username值 r( v! P+ A3 Y" N
if(exists(select * from #Temp2 where deptid=@deptid )) ! B% j& Y8 X2 ~) C+ {# ?5 K
update #Temp2 set username=username +@username where deptid=@deptid
else
--插入新数据
insert into #Temp2 select @deptid,@username4 y& j- X" L( w5 m# p% n8 b
fetch next from Select_cursor into @deptid,@username. Z8 c) ?- ^, J) ], }
end& h% ^# c7 F) u+ R% y9 c
close Select_cursor 0 P# I; B9 O: A I; F' ?7 z7 k% L( K
deallocate Select_cursor
select * from #Temp2 --测试结果6 ~ _: {) g% X( S: m3 q( K
Drop table #Temp1,#Temp2
/ a( q. {2 r8 `" C8 a) b8 e
[ 本帖最后由 DVD 于 2006-12-14 17:08 编辑 ]
作者: DVD 时间: 2006-12-14 16:36
自动生成表的更新数据的存储过程
设计原因：在数据库设计中，有时候建立了很多表，每个表都有Insert、Update、Delete结构基本相同的存储，要是能有个自动生成表的更新数据的存储过程，就方便了我们不必浪费时间去写每一张表的Insert、Update、Delete存储过程。
设计方法：先提取表的各字段信息，包含字段的数据类型、数据定义长度、是否主键等。再根据提取出来的信息构造成表的更新数据的存储过程。下面的方法是有一个用户自定义函数FN_GetObjColInfo和一个存储过程SP_CreateProcdure来实现。8 i+ g$ k* U5 T
用户自定义函数FN_GetObjColInfo：) L' a1 C! S# g9 q4 x* u k! {
/*9 t) E7 v) r c. ]
功能：返回某一表的所有字段、存储过程、函数的参数信息
设计：OK_0081 P: U9 z/ s9 |: x! ?! d$ t' F( u
时间：2006-05
*/
CREATE FUNCTION FN_GetObjColInfo: O6 T( t" b) /
(@ObjName varchar(50))
RETURNS @Return_Table TABLE(9 _" @, q* }4 ~- C! s. L3 D" T
TName nvarchar(50),
TypeName nvarchar(50),1 `( ]$ w7 [6 x! C( H$ P# K6 R; ~
TypeLength nvarchar(50)," ~7 w H4 Z! B0 F$ k
Colstat Bit
) ' @1 J9 e: R/ e% ~( W2 b
AS - E" @' f7 L9 W: |4 t3 `. U) }9 f
BEGIN $ |" ]* X( ?! x$ t
INSERT @Return_Table ' A0 _) c/ `( S9 Z0 H0 _) _3 f
/*9 f) o( A6 s* u! i, v) d! G
主要是从系统表中提取表(对象)的各字段信息。( `1 h# v8 A0 i7 Z# K
sysobjects: 在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）在表中占一行
syscolumns:每个表和视图中的每列在表中占一行，存储过程中的每个参数在表中也占一行; J# f. J, R# f
systypes: 保存数据类型和用户定义数据类型/ u2 O. u. e. w
*/. E" }& W. m# v& B
select b.name as 字段名,c.name as 字段类型,b.length/2 as 字段长度,b.colstat as 是否自动增长2 y+ {* A; o: ` O) K% O, k! J- z
from sysobjects a
inner join syscolumns b on a.id=b.id ! e1 J& F' m3 r
inner join systypes c on c.xusertype=b.xtype
where a.name =@ObjName : h% o _, m! U8 @
order by B.ColID/ Z; q+ H0 y, a/ j( b
RETURN2 I: o) O. j6 N) }! o# s8 _7 ]0 f. f
END; b0 {3 H2 j5 }: d6 l# U3 H% f2 /, O
GO
存储过程SP_CreateProcdure：0 b8 u8 e) W+ ?" ?6 z4 t. /" |
9 d/ x& x$ E4 l5 N$ E! X
3 F3 k/ h+ S3 k5 H
CREATE PROCEDURE SP_CreateProcdure
@TableName nvarchar(50)- T. N. y0 p; /) D+ G7 `# A0 [, C
AS
/*5 x; t* _! D! @ @; T, D$ q
功能： 自动生成表的更新数据的存储过程
如：当建立表MyTable后，执行SP_CreateProcdure ，生成表MyTable的数据更/ c. x9 R2 L+ t8 |
新的存储过程UP_MyTable
设计： OK_008) u4 z u2 ?$ J
时间： 2006-05
备注：
1、请在查询分析器上执行：EXEC SP_CreateProcdure TableName" P; k. E$ W9 }$ ` M) K4 {; @
2、由于生成的字符串长度合计很多时候存在>4000以上，所有只使用Print输出，
再Copy即可。1 ~8 /4 o0 O; p0 t4 d
3、该方法能生成一般表的更新数据的存储过程，其中更新格式可以根据实际2 Z; t2 J& F9 i: X& e
情况修改。! A( p0 ?7 k8 d/ N7 ^
设计方法：; ^- ]6 t4 l3 V( b1 V$ {$ h' ^
1、提取表的各个字段信息7 {( I ?0 G3 b+ d
2、 ──┰─ 构造更新数据过程
├─ 构造存储过程参数部分
├─ 构造新增数据部分 m8 c& Q0 f; w: X3 l" @' L
├─ 构造更新数据部分8 {7 X% f; K/ y( N
├─ 构造删除数据部分
3、分段PRINT
4、把输出来的结果复制到新建立存储过程界面中即可使用。
*/
DECLARE @strParameter nvarchar(3000)$ a3 G# _+ X4 a6 d( z
DECLARE @strInsert nvarchar(3000); B- _$ n" c2 o
DECLARE @strUpdate nvarchar(3000)# L! G0 v7 i3 o) Z3 ~2 G6 p8 r
DECLARE @strDelete nvarchar(500)
DECLARE @strWhere nvarchar(100)
DECLARE @strNewID nvarchar(100)' V' a$ x2 A8 K: K) p
DECLARE @SQL_CreateProc nvarchar(4000)' s) d6 C. h z# ]8 F: r
SET @SQL_CreateProc='CREATE PROCEDURE UP_'+@TableName +char(13)+'@INTUpdateID int,' +' /* -1 删除 0 修改 1新增 */'
SET @strParameter=''. j/ n! Z. _" {
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)7 J, O$ d7 a9 c6 U/ P; D# i* i2 i* j
OPEN Obj_Cursor: Y$ _/ M$ @$ Z/ W. J& k3 [, G# X
FETCH NEXT FROM Obj_Cursor INTO @TName,@TypeName,@TypeLength,@Colstat2 p( e, l0 {: v! J% b
WHILE @@FETCH_STATUS=0
BEGIN! H6 N, u3 l2 ~1 I
--构造存储过程参数部分# O- x4 x3 W+ x7 ], g& _) y8 S
SET @strParameter=@strParameter +CHAR(13)+'@'+ @TName + ' ' +@TypeName+
(CASE , /; U4 @5 ?) H3 t) M" j) E! n
WHEN @TypeName='nvarchar' THEN '('+@TypeLength+')'
ELSE ''2 n5 U0 r) t4 C# |
END)+','
--构造新增数据部分. l4 B! t( j+ k2 G) G
IF @Colstat=0 SET @strInsert=@strInsert + '@'+ @TName +','
--构造更新数据部分
IF (@strWhere='')
BEGIN) ]1 q5 I0 H" m1 K$ [
IF @Colstat=0 SET @strNewID='SET @'+@TName+'=(Select ISNULL(MAX('+@TName+'),0) From '+@TableName+')+1 --取新的ID'9 e) U! H( I( J
SET @strWhere=' WHERE '+@TName+'='+'@'+@TName; k8 n/ r- `% y1 a+ j; _
END
ELSE0 e, c$ s0 V2 s5 s
SET @strUpdate=@strUpdate+@TName+'='+'@'+@TName +','8 d: Y& o- b2 _5 b9 a/ m- W0 K, f$ D
--构造删除数据部分 & [' ^- C+ r+ s
FETCH NEXT FROM Obj_Cursor INTO @TName,@TypeName,@TypeLength,@Colstat
END$ P |" M9 Q6 q* R
CLOSE Obj_Cursor
DEALLOCATE Obj_Cursor7 O6 A' |- {* F5 u6 Z
SET @strParameter=LEFT(@strParameter,LEN(@strParameter)-1) --去掉最右边的逗号
SET @strUpdate=LEFT(@strUpdate,LEN(@strUpdate)-1) ! d: D. h' e( O R
SET @strInsert=LEFT(@strInsert,LEN(@strInsert)-1)
--存储过程名、参数6 l) ?1 A$ }- k& g' e" m! F& Q
PRINT @SQL_CreateProc+@strParameter +CHAR(13)+'AS'
--修改
PRINT 'IF (@INTUpdateID=0)'
PRINT' BEGIN'+CHAR(13)1 M; J$ K3 d' W$ K7 j. c5 e5 O
PRINT CHAR(9)+'UPDATE '+@TableName+' SET '+@strUpdate+CHAR(13)+CHAR(9)+@strWhere
PRINT ' END'
--增加
PRINT 'ELSE IF (@INTUpdateID=1)'- o! @7 @, c5 M0 q* v5 M
PRINT ' BEGIN'% m: x+ x. f+ ?3 u/ r5 W; K
PRINT CHAR(9)+@strNewID3 r$ |: }: m! t- j! l% {6 J% x
PRINT CHAR(9)+'INSERT INTO '+@TableName+' ('+REPLACE(@strInsert,'@','') +') VALUES ( '+@strInsert +')'6 i5 S; z. x) S y. }6 _' `
PRINT ' END'1 |9 J1 m3 q2 a# } L$ B: j
--删除
PRINT 'ELSE'
PRINT ' BEGIN'1 U3 T% n1 O) k! N- r/ G; `
PRINT CHAR(9)+'DELETE FROM '+@TableName +@strWhere
PRINT ' END'% A& E0 D: y1 a' A5 d9 U6 X% G
PRINT 'GO'* f7 u' L, @0 ?. `4 b$ s
GO# p& U1 O6 l$ l! k- E: O4 C1 s* y
[ 本帖最后由 DVD 于 2006-12-14 16:39 编辑 ]
作者: DVD 时间: 2006-12-14 16:40
绘画日历
设计原因：记得那时，周末没事干随便想到的，只是为了学习。% m; F" N: G* m( ^: w
设计方法：先构造一个月的日历图，再使用循环绘画1-12月的日历图。: [( S4 f: ^; l& }' L
/*
功能：绘画日历
设计：OK_008
时间：2006-05
*/* p) k h# Y. t; e( K
DECLARE @Year nvarchar(4)" Y( ] |8 K1 ^# C# l
DECLARE @YearMonth nvarchar(7) --月份
DECLARE @strTop nvarchar(200)
DECLARE @ForI INT,@ForYear INT ,@MaxDay INT
DECLARE @RowX INT --行位置
DECLARE @strWeekDayList nvarchar(20)7 n) @% z0 x$ ?; X
DECLARE @strPrint nvarchar(300)$ B6 H0 t( A. Y: T1 f- z
" c4 N" V1 R% Q* n$ ~! ]2 ?
-- ======================================
SET @Year='2006' --请在这里输入年份
-- ======================================
--设置日历上边的标题格式
SET @strTop= '日'+char(9)+'一'+char(9)+'二' +char(9)+'三'++char(9)+'四'++char(9)+'五'++char(9)+'六' +char(13)+
'───────────────────────────'- X, l! U h5 ]' r: A; Z6 O! b
--设置星期列表9 i) b* G0 K2 G% B) [; Z) i4 P
SET @strWeekDayList='日一二三四五六'4 x! S, E. ^* Z% V' o
SET @ForYear=1- b! D* ~* |! |7 S
WHILE @ForYear<=12 --1月份至12月份
BEGIN
--取当月格式9 e/ T9 r# l3 y# j8 m
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, h' X! I4 h* c1 i: [% b
SET @strPrint=''
SET @ForI=1
WHILE @ForI<=@RowX --构造1号的位置，并绘画空白处$ I9 r8 G l) ~- X. u5 r+ a
BEGIN5 G+ V: [7 `% V1 X2 @& d7 q
SET @strPrint=@strPrint+CHAR(9)* H0 F/ t# ?, n$ x
SET @ForI=@ForI+1
END
SET @ForI=12 @$ U4 b! H% {, ?6 ], u" ]7 ?# J
WHILE @ForI<=@MaxDay --构造2号到月底的位置，并绘画, q. g* f, g. C, B1 p8 p& B
BEGIN$ B/ I1 g6 a }# F. j
SET @strPrint=@strPrint+CAST(@ForI AS nvarchar(2)) +Char(9)
SET @RowX=@RowX+1
SET @ForI=@ForI+1
IF (@RowX%7=0) --满一个星期就换行. C) ^# g; Q% t9 T; g0 S
BEGIN/ d' k8 n0 z: Y9 `3 ^# ?
SET @RowX=0
SET @strPrint=@strPrint+CHAR(13)/ @8 ^ x0 r5 R+ {2 E5 [0 q
END& R$ a( @( b- ~4 V$ b) V8 L
END
SET @ForYear=@ForYear+1
-- 打印输出一个月的结果- Q. _+ p4 a5 V, r! Y% n+ I
PRINT '━━━━━━━━━━━━━━━━━━━━━━━━━━━'
PRINT +Char(9)++Char(9)+' '+@YearMonth+CHAR(10)2 M, ?$ W. s$ e9 p* K" w1 e
PRINT @strTop4 c! Y) g3 z. O# Y6 ~# _ }
PRINT @strPrint +CHAR(10)
END
执行结果：1 k- M& c/ j. L) o" [
8 _" J8 d8 W; p. q
[ 本帖最后由 DVD 于 2006-12-14 16:48 编辑 ]
作者: DVD 时间: 2006-12-14 16:49
一个金额转化的问题
设计原因：记得是有一客户的特别要求，开始有点晕，后来还是能做好。
/*
功能：把Money类型转换成nvarchar类型,保留三位小数，而且把数值后面多余的0去掉。
如money类型的数值2.59100，转化成nvarchar(20)类型时，要求的结果是2.591
如money类型的数值89.7800，转化成nvarchar(20)类型时，要求的结果是89.789 `% n7 a* t2 s
设计：OK_008
时间：2006-07
*/" ]5 N4 h) k" N9 a [5 g
CREATE TABLE #Temp([ID] int ,F1 nvarchar(20),F2 nvarchar(20),SMoney money,Dmoney money)
INSERT INTO #Temp SELECT 1,'F1','F2',45.895,23.89000: A7 x4 A, K7 j+ A& W1 k
UNION SELECT 2,'F2','F3',45.895,234.67. |" w" V: h1 b2 b
UNION SELECT 3,'F3','F4',25.835,32.1237 o$ S7 B1 {# R9 [; I
UNION SELECT 4,'F4','F5',13.7600,31.6754
UNION SELECT 5,'F5','F6',34.783,78.345
/*
要求的结果如下：1 ~* B- q2 ^; v1 V$ v1 X
SMoney +'X'+ Dmoney
------------------------------------------------------
45.895X23.89. r% m+ v0 b5 E: B
45.895X234.672 w6 T4 h* `7 u% E7 T% s9 F- F h
25.835X32.123- f. g4 x' w4 E: m* ], s
13.76X31.675- G5 ?9 U1 W6 A, w
34.783X78.345$ k6 l6 g4 Y+ W6 w/ n
*/
SELECT *,cast(SMoney as nvarchar(20)) FROM #Temp
/*- j8 |* I4 l+ P. k
经过3次的数据才可以把money类型的数据后边多余的零给去掉，
money先转换成decimal,再转换成float,然后是nvarchar
*/+ |9 @% L5 |" w- m1 M! D7 o
SELECT CAST(CAST(CAST(SmoneyAS decimal(20,3)) AS float) as nvarchar)+'X'+
CAST(CAST(CAST(DmoneyAS decimal(20,3)) AS float)AS nvarchar)
FROM #Temp
DROP TABLE #Temp& d% y T# M/ R. R. L, P/ y- z8 w$ i
作者: DVD 时间: 2006-12-14 16:50
多个变量赋值问题.
设计原因：记得在itpub上有一朋友问到这个问题，感觉顶有意思的，顺便拿过来。3 F- w; A F& B. f3 u6 L5 n/ J
/*" i+ M2 U3 A9 ]: s# e- H
=================================9 }$ c' t9 }# L
原来的问题是这样的：
=================================' Y, b! _) E' b! q4 G3 `, D$ F; I
现在的表结构是这样的
ID ReferID ReferCount: C) ~4 /9 B/ ^0 r. s% E1 c
1 0 21 j; P$ _8 K& I# |7 u
2 1 NULL, t. e" d6 |' o5 k7 a: [
3 2 NULL, E" g- / [. o( _" z
4 3 16 s2 A% m9 c6 S, m5 d
然后声明四个变量
declare @t1 int 1 Q7 J% S: b1 B/ d2 e
declare @t2 int
declare @t3 int, _% l# C& E7 h% G
declare @t4 int
/ ~$ g! w# j" Y9 }9 N9 C/ q
然后使用四条记录中的ReferCount为值四个变量赋值) N5 m! {6 H g8 ?, o* b8 S
问题是能不能使用一条sql语句完成，用case行不行的6 X) o; h1 @/ V, I3 j7 V
请高手们指教
*/) ~" W- X# g7 w& L
--解决方法：
: R& N; K+ E* F. }7 o4 `% l8 c
CREATE TABLE #Temp(ID int,ReferID int,ReferCount int)0 f) y" P+ {5 }& O* S
INSERT INTO #Temp
SELECT 1, 0, 2 UNION ALL! d. /% ?$ n& c0 ]" Z4 Z4 _( L
SELECT 2, 1, NULL UNION ALL
SELECT 3, 2, NULL UNION ALL7 j$ Y$ q" w( d2 `. m$ M+ ?+ j
SELECT 4, 3, 1$ J$ ~+ `! M; W V* ^- n9 Q" @
DECLARE @t1 int # d/ y$ T2 y8 v1 t* h
DECLARE @t2 int% d: n1 D! X- @
DECLARE @t3 int/ I9 C! U" o" Y- A$ h7 o
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 q8 H8 t# Z* Y! d3 @1 n: ]
FROM #Temp
SELECT @t1 AS '@t1',@t2 AS '@t2',@t3 AS '@t3',@t4 AS '@t4'
DROP TABLE #Temp
/*
运行结果：
@t1|@t2|@t3|@t48 x2 H1 H( U* D$ w! r
--------------------
2 |NULL|1 |NULL' R+ x7 y4 Q3 f4 @% / U% i( r* M
*/
作者: DVD 时间: 2006-12-14 16:52
计算在一段时间内某周几（如星期一）的所有日期
设计方法：先提取第1个星期一，然后就是循环+7。难点就是怎么样提取第1个星期一。; @5 y) u; q+ P9 b8 C& Z
/*
功能: 计算在某一段时间内某周几（如星期一）的所有日期
设计：OK_008
时间：2006-10& C; {# t+ /) `. ^0 y
*/* c- w: U5 T& `! [0 j
DECLARE @Date datetime
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @WeekDay int
DECLARE @i int
SET DATEFIRST 7 --设置每周的第一天 4 A0 h- S3 S3 l; | ~" I
SET @StartDate='2006-01-01' --统计的开始日期3 |; g4 J" [) c! _+ q
SET @EndDate='2006-12-31' --统计的结束日期% `1 v0 o9 c* G( d: y& J4 M( L
SET @WeekDay=2 --根据实际的@@DATEFIRST而定，一般默认是7，如 @StartDate='2006-01-01'时候， @WeekDay=3表示星期二
SET @i=DATEPART(weekday,@StartDate), h; {+ I/ {. ^5 V1 K
PRINT '每周的第1天设置@@DATEFIRST: '+CAST(@@DATEFIRST AS nvarchar(1))# K" U* V7 v1 [& B' |
PRINT '开始日期对应一周的第几天: '+CAST(@i AS nvarchar(1))7 |; `7 y+ t* }/ e" ^+ u% i
IF(@i<=@WeekDay AND @i<7)8 s" F5 X) R% v! f. J
SET @i=@WeekDay-@i
ELSE IF(@i<=@WeekDay AND @i=7)
SET @i=@i-@WeekDay+ O8 @1 F: p$ K
ELSE
SET @i=@@DATEFIRST-@i+@WeekDay) F, ]2 W8 R2 n3 _
SET @Date=DATEADD(day,@i,@StartDate); g$ G9 o% |# y) Q# p* w# c, ?
WHILE @Date<=@EndDate
BEGIN
IF(@StartDate<=@Date) PRINT CONVERT(nvarchar(10),@Date,121)( c8 I6 q; h x# b- S2 f
SET @Date=DATEADD(Week,1,@Date)- p( b$ K1 U, W
END
GO5 }2 T1 r" z- ^& S/ K
/* ==============运行结果================*/' g3 d7 B# o/ [* g
/*
每周的第1天设置@@DATEFIRST: 7
开始日期对应一周的第几天: 1
2006-01-02, R9 Y; m4 ~8 C
2006-01-09
... ...! B: w: c# h* o
2006-12-18
2006-12-25( @) S* p2 x- T$ B2 o2 z
*/
作者: DVD 时间: 2006-12-14 16:52
字符串的分割
/*
功能：实现字符串的分割，达到批量提交数据参数和分批处理功能。
设计：wgh& }! L5 K$ u9 l+ Y& {
时间：2006-09% K: s/ ]. _ E' Y3 o) N+ f
*/
declare @str1 nvarchar(100) --要分割的字符串
declare @str2 nvarchar(100) --分割得的子字符串 u: a+ m1 ` p3 Z' |
declare @split nvarchar(40) --分割字符or字符串
declare @patIndex int --分割字符or字符串第一次出现的起始位置. x& e+ ^2 A9 L% X! R$ P m
set @split='★■->'
set @str1='334,345,''dfd'',★■->select * from Employee★■->45654DFG★■->452897★■->97887657★■->123445'( b) B6 _7 h" J
set @str2=''
--从左至右分割
while @str1<>@str2 6 u k" v2 O) /2 V i1 j
begin+ w* O4 t$ z V0 ~
set @patIndex=patindex('%'+@split+'%',@str1) --patindex函数返回@str1中@split第一次出现的起始位置# C% b6 z$ i, P4 [1 R) e
if @patIndex>0 . Q) {& G4 /+ b9 Y
set @str2=left(@str1,@patIndex-1) --截取@str1中以@split为分割的左边的字符串
else0 C& E) v& u: ?4 /2 c- ]
set @str2=@str18 y8 R7 c. f3 q D7 h9 Q+ t9 s
print @str2 --调试
/*
执行过程 ...., p- a6 p3 X. G/ B
*/6 v" M+ [2 ?: |
if @patIndex>0 set @str1=right(@str1,len(@str1)-len(@str2)-len(@split)) --截断字符串，为的是下一次循环能取得，@str1中@split第一次出现的起始位置
end
作者: DVD 时间: 2006-12-14 16:54
查找某一数据库没有主鍵的所有用户表
/ _: F. }+ e( M0 Q( ?
/*! c- a% D; X5 D' v- @
功能：查找所有(某一)数据库没有主鍵的所有用户表- ?$ a+ A5 P2 J8 y9 o
设计：wgh
时间：2006-06
*/# j) |! q. `' @- ~
/ m& Z0 d' y; } _% u( d: ?
/*
设计方法：使用游标的方法，从系统表sysobjects、syscolumns、sysindexes提取表的相关信息。8 Y9 k. W7 G( j' Z ~* h: O% W
*/
DECLARE @DatabaseName nvarchar(20)4 k8 H9 ?$ {" {2 p5 I
DECLARE @Execute_Sql nvarchar(4000)& j# P9 [1 p) B4 M3 L
SET @DatabaseName='ydhr' --要是要检查所有数据库就SET @DatabaseName=''$ F: D7 w. Q/ a& t* x3 v
CREATE table #Temp(DatabaseName nvarchar(20),TableName nvarchar(50))0 A; }0 l) |7 e! F9 A ?; p
IF ISNULL(@DatabaseName,'')=''
DECLARE cursor_Sql CURSOR FOR! ^( y: S& Z" I" ~( ]/ N4 X
SELECT name FROM master..sysdatabases WHERE dbid>=7 --dbid>=7 都是属于用户建立的数据库( r8 R, W8 }" @! C
ELSE* l! |" M0 |7 G
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$ }4 K* }; p6 O2 ~) d C
BEGIN( V. |- _7 n/ X1 m/ b6 G
SET @Execute_Sql='INSERT INTO #Temp + S2 / A; x$ I9 G$ d4 J/ {
SELECT '''+@DatabaseName+''',A.name FROM '+@DatabaseName+'..sysobjects AS A WHERE OBJECTPROPERTY(A.id, N''IsUserTable'') = 1 AND NOT EXISTS(. M o! i R1 `1 I, d
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/ c% a: n( Y4 N* k6 W
AND EXISTS(SELECT 1 FROM '+@DatabaseName+'..sysobjects WHERE xtype = ''PK'' AND name = i.name)) x z; /+ _- F9 y8 X) [
)'
/*& L- ~: y. [( I* X* _
OBJECTPROPERTY（）返回当前数据库中对象的有关信息,OBJECTPROPERTY(对象id, N''IsUserTable'') 表示是否为用户表8 G; j- }1 f( ?/ y) a* `
xtype='U' 在系统表sysobjects中表示查询的对象属于用户表，与OBJECTPROPERTY(对象id, N''IsUserTable'')功能一致，只是用法格式不同$ R" /9 e. q6 J5 z# E+ R Y& s t5 D
xtype='PK' PRIMARY KEY 约束（类型是 K）) U6 Z; R- f# ?. ]2 z
*/8 c, N2 f0 c" z& W8 e
EXECUTE(@Execute_Sql)8 ?9 Y- p& t$ W# H1 Y* ]
PRINT @Execute_Sql --为了能更清楚语句的含义，我这里可以使用PRINT查看整条执行语句4 ]3 Y# _ u4 g/ V0 [+ R
FETCH NEXT FROM cursor_Sql INTO @DatabaseName
END
CLOSE cursor_Sql% t; Y" x- P" ?9 a% z% k
DEALLOCATE cursor_Sql* M8 o e M3 Z" o' E: Q
SELECT * FROM #Temp# g9 f7 g7 W3 O. /
DROP TABLE #Temp, n4 { r- q. x0 H0 C" }: u
作者: DVD 时间: 2006-12-14 16:55
重命名数据库、表、列
重命名数据库、表、列名，使用系统存储过程sp_rename是最简单的了。
我们先来看看帮助文档的说明：" A+ k+ l0 v3 J. `. J
sp_rename+ w% U- F$ n j' c
更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。
语法
sp_rename [ @objname = ] 'object_name' ,
[ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]5 P, F1 k7 K/ @( X7 d
@objname 对象名- {3 Z J4 X; K
@objtype 对象类型0 w2 b9 S0 S' k. f2 |6 T$ o1 G
| 值 | 描述 |
| COLUMN) }9 T3 ^; q& X8 g$ B | 要重命名的列。 |
| DATABASE | 用户定义的数据库。要重命名数据库时需用此选项。 |
| INDEX) `+ v% U2 c( E9 F9 s | 用户定义的索引。 |
| OBJECT, /# l. a% b$ |/ } | 在 sysobjects 中跟踪的类型的项目。例如，OBJECT 可用来重命名约束（CHECK、FOREIGN KEY、PRIMARY/UNIQUE KEY）、用户表、视图、存储过程、触发器和规则等对象。 |
| USERDATATYPE | 通过执行 sp_addtype 而添加的用户定义数据类型。 |
/* * Q6 {, Q- }7 q4 J: }2 f
功能：重命名数据库、表、列
编写： wgh
时间： 2006-11
*/ 5 g& Y& L9 d/ _! Q }, y/ I/ |7 c
-- 重新命名数据库
IF EXISTS(SELECT 1 FROM master..sysdatabases WHERE name='BBS')
EXECUTE sp_rename @objname='BBS',@newname='BBS1',@objtype='DATABASE' / f9 H1 s* m4 w$ d- q% i! g
-- 重新命名表名
IF EXISTS(SELECT 1 FROM sysobjects WHERE name='bbs_log' AND OBJECTPROPERTY(id,'IsUserTable')=1) 2 X/ ?" i: H# S$ X1 q4 d. _
EXECUTE sp_rename @objname='bbs_log',@newname='bbs_log1',@objtype='OBJECT' ) u# v; Y O3 g! f+ Y
-- 重新命名列名 9 e6 }( b! g0 `0 {
IF EXISTS(SELECT 1 FROM syscolumns WHERE id=OBJECT_ID('bbs_log') AND name='ColumnName' AND OBJECTPROPERTY(id,'IsUserTable')=1) & O7 Q1 T9 K) v& n+ D, m8 H3 k- N
EXECUTE sp_rename @objname='bbs_log.[ColumnName]',@newname='NewColumnName',@objtype='COLUMN'
作者: DVD 时间: 2006-12-14 16:56
格式化字符串 ，如输入01 自动生成 0000001
/*===================================================$ d- /- v( ]* g( B1 Y- b
功能：格式化字符串 ，如输入01 自动生成 0000001 # Q, N. @# q3 [2 L- B, b
备注：当位数达到10以上,要另写算法,否则出错
设计：weiguohao
日期：2006-03-11
===================================================*/
CREATE FUNCTION FORMAT_STR
(@strX Nvarchar(20),@FormatLength INT)
RETURNS NVARCHAR(20) AS / x0 g& _3 Q4 S( t8 Y1 s% n
BEGIN 1 T q9 U/ B! c# z& R/ g; l4 A0 z
DECLARE @Return NVARCHAR(20)* d* {6 ~+ M: A" s! Y3 S
DECLARE @Length INT. Q. D2 l2 _! ^
SET @Length=LEN(@strX)
IF @Length>=@FormatLength; S& |: v$ y! Z o1 g
SET @Return=@strX* @' a! D( A5 Z$ I
ELSE. ?* a( T3 X3 J
BEGIN. r, X1 O: M# G9 E& e- n6 ^
-- 格式化，只要是调用到数学函数POWER，其他就不再说明，太简单了
SET @Return=CAST(RIGHT(POWER(10,@FormatLength),@FormatLength-@Length) AS NVARCHAR(20))+@strX
END% }7 s3 D0 E8 e3 k7 h) s2 x: f1 _; C0 [& G
RETURN @Return
END
作者: DVD 时间: 2006-12-14 16:57
一个厂家与销售商的查询问题问题来来自itpub论坛上的一位朋友，如下：' B" q. W4 w6 w
多个厂家多个销售商每个厂家对任一销售商都免费前10次的货款有厂家表，销售表，销售商表想求得销售商三个表拼合一起的视图视图中有个字段标出免费的销售记录？
厂家表：ID ，名称销售表：ID，销售单号# X% a& p, z: y# X9 @. r
厂家：ID，销售商ID) p& n$ V% J; ], w& j+ C, n9 G5 c0 m
销售商：ID，销售商名称预得视图：销售单ID，厂家ID，销售商ID，是否免费谢谢哥哥姐姐了。帮忙写写…… * [% i5 i0 S( Q; ]* a: z% ^
解答：
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)) --销售商、客户表% _; L4 P% o* x' j' p- F. X! y8 Z8 Y
INSERT INTO #Suppliers) p8 R$ V5 X: Q2 w4 x/ e3 ~
SELECT 1,N'Supplier1' UNION ALL
SELECT 2,N'Supplier2' / _/ w# C# q. L! R/ ]- I
INSERT INTO #Customers
SELECT 1,N'Customer1' UNION ALL9 l* ?$ m8 B1 K- _' r
SELECT 2,N'Customer2' . o2 ]5 q, r* {
INSERT INTO #Sell7 T. r) h, r0 T, I" i" D
SELECT 1,1,1 UNION ALL
SELECT 2,1,1 UNION ALL) v/ @ P/ w( p
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$ Z- `& y/ T) S$ N0 w( K* r9 ^
SELECT 8,1,2 UNION ALL: f! _( I& l% J( s8 G
SELECT 9,1,2 UNION ALL
SELECT 10,1,2 UNION ALL2 |& X' Q/ O' P1 @$ k1 J
SELECT 11,1,2 UNION ALL7 `' V6 M! L2 @3 /% _: m* X
SELECT 12,1,2 UNION ALL! }7 L' u! ~* i. W8 w! d
SELECT 13,1,2 UNION ALL& q/ t$ A+ x7 G, M8 ^
SELECT 14,1,2 UNION ALL
SELECT 15,2,1 UNION ALL# w: ?/ w+ K5 L" q$ ^
SELECT 16,2,1 UNION ALL
SELECT 17,2,2 UNION ALL
SELECT 18,2,2 UNION ALL
SELECT 18,2,2 , t8 n1 P+ @7 n
--每个厂家对任一销售商都免费前3次的货款（根据实际修改提前多少次是免费的货款）
--主要是使用到一个GROUP BY ...HAVING ...在对数据进行分组和聚合后，就会用到 HAVING 子句中的条件) N" m2 z. y2 H3 i& y Y8 ~
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
5 i" `+ ]$ F1 O2 H5 |" q
结果. H% ?' E" t; h) Z7 P* P/ _0 s c
[ 本帖最后由 DVD 于 2006-12-14 17:01 编辑 ]
作者: 绿竹居 时间: 2006-12-14 16:59
俺的天。好多。俺要好好学习。。谢谢DVD
作者: DVD 时间: 2006-12-14 17:01
不用游标就可以实现的一个问题
原来问题如下：
如果有2个表T1（aid,bid），T2（bid,bname,bprice）
T1的字段表示顾客ID和商品ID，一个顾客可以买多种商品现在T1有记录:9 Z' G* ~4 k9 U
1,10
1,11
1,12/ Z* G6 a4 E' Y5 l8 e- z1 Q$ i/ D
2,101 b. c4 ]$ N' A3 c* D0 u, l- d
2,131 u3 N% ?5 ` [/ /- X+ v B# v
T2的字段表示商品ID，商品名称，商品价格现在T2有记录：
10,aaa,1( T8 ?4 Y9 t9 W, U, /* V7 W' V/ F+ R
11,bbb,2# g ^+ Z/ k/ i" M- e& k
12,ccc,3( v6 g4 q$ j- d, d3 k- x
13,ddd,4 [- y. A$ Y/ ^' U1 }" I g
现在要实现显示顾客买的商品名称和价钱即字段：
aid,bname,bprice8 j$ {; {' _& I! ]: M( l) u9 y
但是同一个顾客只保留一个ID，其他为null请问怎么实现？即将上面的记录实现为：9 f0 q! D' x, C0 L
1,aaa,1
,bbb,28 ]" v( Y. A$ v' J: F# f
,ccc,3
2,aaa,1
,ddd,4
如果不用游标有办法实现吗？. t/ E5 L$ z q% T* F# p8 a- y
解决方法：" `: t6 G2 T/ v% v; t3 X' T
CREATE TABLE #T1(aid int ,bid int)
INSERT INTO #T1# _2 j S5 k4 w( D" Z! O6 U
SELECT 1,10 UNION ALL
SELECT 1,11 UNION ALL
SELECT 1,12 UNION ALL* W3 H) x; c2 X4 a
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, o4 l& x r% c8 q
SELECT 13,'ddd',4. P( z; E# K# a4 j
SELECT A.aid,B.bname,B.bprice INTO #T3 FROM #T1 AS A LEFT OUTER JOIN #T2 AS B ON A.bid=B.bid+ z) S. N. Q4 x' t% u- P7 O
DECLARE @aid int ,@lastaid int
UPDATE #T3 SET @aid=(CASE WHEN ISNULL(@lastaid,'') =aid THEN NULL ELSE aid END),aid=@aid,@lastaid=aid% R' u5 w1 L$ b& R3 u" A2 ]$ k8 W6 G
SELECT * FROM #T3+ e' v! y M5 l5 O) F( k1 r# G% i
DROP TABLE #T1,#T2,#T3
结果：
/*
1 aaa 1& B8 z9 q% ^" e/ /% b u
NULL bbb 2
NULL ccc 3
2 aaa 1/ Z; C& t# H" Z8 @. `6 L
NULL ddd 4
*/
作者: DVD 时间: 2006-12-14 17:02
一个数据替换问题
原来的问题：
我要將([4000]+[3900])/([3100]+[3200]+[3900])*[3900]替换成
(0+0)/(0+0+0)*0
就是把[4000]樣的替換成０
解决方法如下，' R# s1 m( P9 m! d2 x* /6 I
建立一个自定义函数：
CREATE FUNCTION CreateMathExpression$ b% Y t$ p! d
(@ColumnName nvarchar(500))4 F d' L5 C$ H4 Z* p' Q2 ^
RETURNS nvarchar(1000) AS
BEGIN - W1 J$ h" S$ b+ t* D4 Z9 /
DECLARE @FieldValue nvarchar(20)& U% ^) n1 e8 w: /
DECLARE @Return nvarchar(1000)9 B* v( M2 t9 E7 k% H4 u
DECLARE @Sql nvarchar(1000)
DECLARE @Index int
DECLARE @End bit
SET @End=0
SET @Return='': M ^4 /; ]' B
--SET @ColumnName='[3900]/222.8*([3200]/[3900])'5 b0 w0 C* |% U" P
SET @Index=CHARINDEX('[',@ColumnName)/ E" h4 W T& D, u
WHILE @Index<>09 M) x" r6 A9 l) @" L5 ?$ c
BEGIN
IF @End=0; [/ _8 F2 u5 _5 `( P, j
BEGIN. b! R9 Q$ I& `! N4 z6 i
SET @Index=CHARINDEX('[',@ColumnName)* z, F- a6 x5 g+ Z9 B6 S
IF @Index=0 GOTO Exit_While- t% p/ {. G: o% @: W" /7 w
SET @Return=@Return+LEFT(@ColumnName,@Index-1)
SET @End=1
END
ELSE
BEGIN3 Q; z' G2 A a
SET @Index=CHARINDEX(']',@ColumnName)
SET @FieldValue=LEFT(@ColumnName,@Index-1)
SET @Return=@Return+ '0'8 n S9 o! j% }; ?3 K r+ O
SET @End=0
END+ X- Q. x P! R5 ~+ I5 Z3 a
SET @ColumnName=RIGHT(@ColumnName,len(@ColumnName)-@Index)6 e- } o! j2 I3 H
END
Exit_While:* `* O( z4 T e) {9 W& O4 y: H
SET @Return=@Return+@ColumnName
RETURN @Return
END- T! Q, Y4 |# r* ^1 F
--------------------------------------------------------------------例子:DECLARE @N nvarchar(200)) O/ e/ U& B& j5 A
SET @N='([4000]+[3900])/([3100]+[3200]+[3900])*[3900]'
SELECT @N
SELECT dbo.CreateMathExpression(@N)& D0 I% T3 l# }2 e/ K/ |
-------------------------结果:' K, S$ s+ w9 e. E9 r+ Q* c
(0+0)/(0+0+0)*0
备注：函数中的一些代码是别的用途,可以忽略。
作者: DVD 时间: 2006-12-14 17:04
向表中一列插入1-100的连续数
在BBS上看到一朋友问到这样的一个问题：
求助：向表中一列插入1-100的连续数
. I* c# l# U! j1 T! W
table A 中有BH字段（INT），怎么连续给BH赋值1-100,象下面的一样
" q+ s$ ^& q7 x
BH % M, w3 l6 A0 n
------------------------------------------------
1
28 B5 ^) h0 ?$ `3 _. b: ^" I
., B. o0 ~5 [ U
.
. j+ P& j! { x( U( R# n
100 f7 j9 s( {" J1 u
根据常规的方法，我们会想到使用While来实现以上的功能。如，一朋友所写的这样：& G& c; j$ w& F& H* d
CREATE TABLE TestTable(BH INT)( Y) r! o8 v# _4 x
GO
SET NOCOUNT ON- P' R, f% h/ ^& v7 p1 L
/*' B1 O% _: D3 G- S6 ]! K9 f
SET NOCOUNT { ON | OFF } 当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。当 SET NOCOUNT 为 OFF 时，返回计数
*/7 @3 i- a/ x9 E: W$ v; R
GO
DECLARE @MyCounter INT, U/ m: H/ w5 U, S& |8 b
SET @MyCounter = 15 o$ E# e; E6 e" E; B
WHILE (@MyCounter < 101)! J /4 W9 Z# h, P, Y/ o. [
BEGIN
INSERT INTO TestTable VALUES8 r% l! D, Z' C2 G! |2 V
(@MyCounter
)! H: K& I& ]* I" I9 e' _" [( w6 a
K7 l1 |4 a( w9 q0 W' [/ ~+ @7 {
SET @MyCounter = @MyCounter + 1, /# ]: N4 J- |
END
GO
SET NOCOUNT OFF
GO
select * from TestTable
--drop table TestTable# n+ p1 z/ o9 H% e7 |5 u. u" M
我看一下，突然想到一个使用变量的方法来实现，如下：
DECLARE @i int! {1 f, s2 Z9 i4 n
CREATE TABLE #Temp ([id] int, C1 nvarchar(20),BH int)
SET @i=400
WHILE @i<=500# I% K5 {7 Y4 V0 G6 l, f6 j( N
BEGIN: z9 t u% m. r# f' v' A
INSERT INTO #Temp SELECT @i,CAST(@i+rand() as nvarchar(20)),null5 x& |! E _3 | s( ?
SET @i=@i+14 ]+ Y8 P: }" B. R8 m; m! I3 Q
END
SELECT * FROM #Temp3 ^; T. W" @& y
--更新字段BH,更新行数为前100行
SET @i=0
--@i相当于For循环中的i,每更新一条记录设置加@i=@i+1" Z" G- J2 F" R
UPDATE #Temp SET @i=@i+1 ,BH=@i WHERE [id] IN(SELECT TOP 100 [id] FROM #Temp)7 {, p# h# f3 ~- R) o
SELECT * FROM #Temp
DROP TABLE #Temp
13万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
