ÔÚSqlServer´æ´¢¹ý³ÌÖÐʹÓÃCursor£¨Óα꣩²Ù×÷¼Ç¼
1. ΪºÎʹÓÃÓα꣺
ʹÓÃÓαê(cursor)µÄÒ»¸öÖ÷ÒªµÄÔÒò¾ÍÊǰѼ¯ºÏ²Ù×÷ת»»³Éµ¥¸ö¼Ç¼´¦Àí·½Ê½¡£ÓÃSQLÓïÑÔ´ÓÊý¾Ý¿âÖмìË÷Êý¾Ýºó£¬½á¹û·ÅÔÚÄÚ´æµÄÒ»¿éÇøÓòÖУ¬ÇÒ½á¹û
ÍùÍùÊÇÒ»¸öº¬Óжà¸ö¼Ç¼µÄ¼¯ºÏ¡£Óαê»úÖÆÔÊÐíÓû§ÔÚSQL serverÄÚÖðÐеطÃÎÊÕâЩ¼Ç¼£¬°´ÕÕÓû§×Ô¼ºµÄÒâÔ¸À´ÏÔʾºÍ´¦ÀíÕâЩ¼Ç¼¡£
2. ÈçºÎʹÓÃÓα꣺
Ò»°ãµØ£¬Ê¹ÓÃÓα궼×ñÑÏÂÁеij£¹æ²½Ö裺
(1) ÉùÃ÷Óαꡣ°ÑÓαêÓëT-SQLÓï¾äµÄ½á¹û¼¯ÁªÏµÆðÀ´¡£
(2) ´ò¿ªÓαꡣ
(3) ʹÓÃÓαê²Ù×÷Êý¾Ý¡£
(4) ¹Ø±ÕÓαꡣ
2.1. ÉùÃ÷Óαê
DECLARE CURSORÓï¾äSQL-92±ê×¼Óï·¨¸ñʽ£º
DECLARE ÓαêÃû [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR sql-statement
Eg:
Declare MycrsrVar Cursor
FOR Select * from tbMyData
2.2 ´ò¿ªÓαê
OPEN MycrsrVar
µ±Óα걻´ò¿ªÊ±£¬ÐÐÖ¸Õ뽫ָÏò¸ÃÓα꼯µÚ1ÐÐ֮ǰ£¬Èç¹ûÒª¶ÁÈ¡Óα꼯ÖеĵÚ1ÐÐÊý¾Ý£¬±ØÐëÒÆ¶¯ÐÐÖ¸ÕëʹÆäÖ¸ÏòµÚ1ÐС£¾Í±¾Àý¶øÑÔ£¬¿ÉÒÔʹÓÃÏÂÁвÙ×÷¶ÁÈ¡µÚ1ÐÐÊý¾Ý£º
FETCH FIRST from E1cursor
»ò FETCH NEXT from E1cursor
2.3 ʹÓÃÓαê²Ù×÷Êý¾Ý
ÏÂÃæµÄʾÀýÓÃ@@FETCH_STATUS¿ØÖÆÔÚÒ»¸öWHILEÑ»·ÖеÄÓαê»î¶¯
/* ʹÓÃÓαê¶ÁÈ¡Êý¾ÝµÄ²Ù×÷ÈçÏ¡£*/
DECLARE E1cursor cursor /* ÉùÃ÷Óα꣬ĬÈÏΪFORWARD_ONLYÓαê */
FOR SELECT * from c_example
OPEN E1cursor /* ´ò¿ªÓαê */
FETCH NEXT from E1cursor /* ¶ÁÈ¡µÚ1ÐÐÊý¾Ý*/
WHILE @@FETCH_STATUS = 0 /* ÓÃWHILEÑ»·¿ØÖÆÓαê»î¶¯ */
BEGIN
FETCH NEXT from E1cursor /* ÔÚÑ»·ÌåÄÚ½«¶ÁÈ¡ÆäÓàÐÐÊý¾Ý */
END
CLOSE E1cursor /* ¹Ø±ÕÓαê */
DEALLOCATE E1cursor &
Ïà¹ØÎĵµ£º
´æ´¢¹ý³ÌµÄ´´½¨¼°Ê¹ÓÃ(sqlserver 2005)
´´½¨´æ´¢¹ý³Ì¡£´æ´¢¹ý³ÌÊÇÒѱ£´æµÄ Transact-SQL Óï¾ä¼¯ºÏ£¬»ò¶Ô Microsoft .NET Framework ¹«¹²ÓïÑÔÔËÐÐʱ (CLR) ·½·¨µÄÒýÓ㬿ɽÓÊÕ²¢·µ»ØÓû§ÌṩµÄ²ÎÊý¡£¿ÉÒÔ´´½¨¹ý³Ì¹©ÓÀ¾ÃʹÓ㬻òÔÚÒ»¸ö»á»°£¨¾Ö²¿ÁÙʱ¹ý³Ì£©ÖÐÁÙʱʹÓ㬻òÔÚËùÓлỰ£¨È«¾ÖÁÙʱ¹ý³Ì£©ÖÐÁÙʱʹÓá£
Æô¶¯ SQL ......
ÔÚÓëÊý¾Ý¿â´ò½»µÀµÄ¹¤×÷ÖÐʱ³£»áÅöµ½Ò»Ð©Êý¾Ý¿âµÄ´íÎó£¬Õâ¾ÍÉæ¼°µ½ÁËÐÞ¸´µÄ¹ý³Ì£¬ÒÔÏÂ֪ʶµã¶¼ÊÇ´ÓÍøÉÏÊÕ¼¯¶øÀ´:
1¡¢dbcc checkdb
Õâ¸öÃüÁî¿ÖÅÂÊÇÎÒÃÇ×î³£ÓõÄÁË£¬´ø¼ì²éºÍÐÞ¸´¹¦ÄÜ
Óï·¨¸ñʽ£º
DBCC CHECKDB
[
[ ( database_name | database_id | 0
[ , NOINDEX
| , { REPAIR_ALLOW_DATA_LOS ......
ÊÂÎñµÄ¹¦ÄÜÔÚsqlserverÖÐÓÉÀ´ÒѾã¬ÒòΪ×î½üÔÚ×öÒ»¸öÊý¾Ýͬ²½·½°¸£¬ËùÒÔÓлú»áÔÙ´ÎÑо¿Ò»ÏÂËüÒÔ¼°¿ìÕյȣ¬·¢ÏÖ»¹ÊÇÓкܶ಻´íµÄ¹¦Äܺ͸ĽøµÄ¡£ÕâÀïÒÔsqlserver2008µÄÊÂÎñ·¢²¼¹¦ÄÜΪÀý£¬¶Ô·¢²¼¶©Ôĵķ½Ê½¼òÒª½éÉÜһϲÙ×÷Á÷³Ì£¬Ò»·½Ãæ×ö¸ö×ܽᱸ·Ý£¬Ò»·½ÃæÓë´ó¼Ò½øÐÐһϷÖÏíºÍ½»Á÷¡£·Ñ»°¾Í² ......
create database db
use db
go
create table course
(
sno varchar(20),
cno int ,
Gmark int
)
insert into course values('20071513115',1,80)
insert into course values('20071513114',2,80)
insert into course values('20071513113',3,80)
insert into course values('20071513 ......
SQL Server 2000
Installing SQL Server 2000 (EÎÄ)
http://msdn.microsoft.com/en-us/library/aa299042(SQL.80).aspx
SQL Server 2000²¹¶¡
Microsoft SQL Server 2000 Service Pack 3a
http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID=90DCD52C-0488-4E46-AFBF-ACACE536 ......