SQL Server/T-SQL Tutorial/Cursor/Cursor

Материал из SQL эксперт
Версия от 10:22, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

FAST_FORWARD cursor

14>
15> SELECT TABLE_NAME AS TableName, COLUMN_NAME AS ColumnName, DATA_TYPE AS Type
16> INTO #TableSummary
17> FROM INFORMATION_SCHEMA.COLUMNS
18> WHERE TABLE_NAME IN
19>      (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
20>       WHERE (TABLE_TYPE = "BASE TABLE" AND
21>              TABLE_NAME NOT IN ("dtproperties", "TableSummary")))
22>
23> DECLARE @TableNameVar varchar(128), @ExecVar varchar(1000)
24> DECLARE TableSummary_Cursor CURSOR
25> FAST_FORWARD
26> FOR
27>     SELECT DISTINCT TableName
28>     FROM #TableSummary
29>
30> OPEN TableSummary_Cursor
31> FETCH NEXT FROM TableSummary_Cursor INTO @TableNameVar
32> WHILE @@FETCH_STATUS = 0
33> BEGIN
34>     SET @ExecVar = "DECLARE @CountVar int " + "SELECT @CountVar = COUNT(*) "
35>     SET @ExecVar = @ExecVar + "FROM " + @TableNameVar + " "
36>     SET @ExecVar = @ExecVar + "INSERT #TableSummary "
37>     SET @ExecVar = @ExecVar + "VALUES (""" + @TableNameVar + ""","
38>     SET @ExecVar = @ExecVar + """*Row Count*""," + " @CountVar)"
39>     EXEC (@ExecVar)
40>     FETCH NEXT FROM TableSummary_Cursor INTO @TableNameVar
41> END
42> CLOSE TableSummary_Cursor
43> DEALLOCATE TableSummary_Cursor
44> GO
(89 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT * FROM #TableSummary
3> ORDER BY TableName, ColumnName
4> GO
TableName                                                                                                                        ColumnName
                                                          Type
-------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------
--------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
Customers                                                                                                                        *Row Count*
                                                          9
Customers                                                                                                                        Address
                                                          nvarchar
Customers                                                                                                                        City
                                                          nvarchar
Customers                                                                                                                        CompanyName
                                                          nvarchar
Customers                                                                                                                        ContactName
                                                          nvarchar
Customers                                                                                                                        ContactTitle
                                                          nvarchar
Customers                                                                                                                        Country
                                                          nvarchar
Customers                                                                                                                        CustomerID
                                                          nchar
Customers                                                                                                                        Fax
                                                          nvarchar
Customers                                                                                                                        Phone
                                                          nvarchar
Customers                                                                                                                        PostalCode
                                                          nvarchar
Customers                                                                                                                        Region
                                                          nvarchar
BillingCopy                                                                                                                      *Row Count*
                                                          0
BillingCopy                                                                                                                      CreditTotal
                                                          int
BillingCopy                                                                                                                      BillingDate
                                                          datetime
BillingCopy                                                                                                                      BillingDueDate
                                                          datetime
BillingCopy                                                                                                                      BillingNumber
                                                          int
BillingCopy                                                                                                                      BillingTotal
                                                          int
BillingCopy                                                                                                                      PaymentTotal
                                                          int
BillingCopy                                                                                                                      TermsID
                                                          int
BillingCopy                                                                                                                      BankerID
                                                          int
MSreplication_options                                                                                                            *Row Count*
                                                          3
MSreplication_options                                                                                                            install_failures
                                                          int
MSreplication_options                                                                                                            major_version
                                                          int
MSreplication_options                                                                                                            minor_version
                                                          int
MSreplication_options                                                                                                            optname
                                                          nvarchar
MSreplication_options                                                                                                            revision
                                                          int
MSreplication_options                                                                                                            value
                                                          bit
OldBillings                                                                                                                      *Row Count*
                                                          0
OldBillings                                                                                                                      CreditTotal
                                                          int
OldBillings                                                                                                                      BillingDate
                                                          datetime
OldBillings                                                                                                                      BillingDueDate
                                                          datetime
OldBillings                                                                                                                      BillingNumber
                                                          int
OldBillings                                                                                                                      BillingTotal
                                                          int
OldBillings                                                                                                                      PaymentTotal
                                                          int
OldBillings                                                                                                                      TermsID
                                                          int
OldBillings                                                                                                                      BankerID
                                                          int
department_pivot                                                                                                               *Row Count*
                                                          11
department_pivot                                                                                                               budget
                                                          float
department_pivot                                                                                                               date_month
                                                          datetime
department_pivot                                                                                                               dept_name
                                                          char
department_pivot                                                                                                               emp_cnt
                                                          int
department_pivot                                                                                                               month
                                                          int
department_pivot                                                                                                               year
                                                          int
SalesMw                                                                                                                          *Row Count*
                                                          13
SalesMw                                                                                                                          CD_ID
                                                          int
SalesMw                                                                                                                          QtySold
                                                          int
SalesMw                                                                                                                          SalesDate
                                                          datetime
SalesMw                                                                                                                          StoreID
                                                          int
spt_fallback_db                                                                                                                  *Row Count*
                                                          0
spt_fallback_db                                                                                                                  dbid
                                                          smallint
spt_fallback_db                                                                                                                  name
                                                          varchar
spt_fallback_db                                                                                                                  status
                                                          smallint
spt_fallback_db                                                                                                                  version
                                                          smallint
spt_fallback_db                                                                                                                  xdttm_ins
                                                          datetime
spt_fallback_db                                                                                                                  xdttm_last_ins_upd
                                                          datetime
spt_fallback_db                                                                                                                  xfallback_dbid
                                                          smallint
spt_fallback_db                                                                                                                  xserver_name
                                                          varchar
spt_fallback_dev                                                                                                                 *Row Count*
                                                          0
spt_fallback_dev                                                                                                                 high
                                                          int
spt_fallback_dev                                                                                                                 low
                                                          int
spt_fallback_dev                                                                                                                 name
                                                          varchar
spt_fallback_dev                                                                                                                 phyname
                                                          varchar
spt_fallback_dev                                                                                                                 status
                                                          smallint
spt_fallback_dev                                                                                                                 xdttm_ins
                                                          datetime
spt_fallback_dev                                                                                                                 xdttm_last_ins_upd
                                                          datetime
spt_fallback_dev                                                                                                                 xfallback_drive
                                                          char
spt_fallback_dev                                                                                                                 xfallback_low
                                                          int
spt_fallback_dev                                                                                                                 xserver_name
                                                          varchar
spt_fallback_usg                                                                                                                 *Row Count*
                                                          0
spt_fallback_usg                                                                                                                 dbid
                                                          smallint
spt_fallback_usg                                                                                                                 lstart
                                                          int
spt_fallback_usg                                                                                                                 segmap
                                                          int
spt_fallback_usg                                                                                                                 sizepg
                                                          int
spt_fallback_usg                                                                                                                 vstart
                                                          int
spt_fallback_usg                                                                                                                 xdttm_ins
                                                          datetime
spt_fallback_usg                                                                                                                 xdttm_last_ins_upd
                                                          datetime
spt_fallback_usg                                                                                                                 xfallback_vstart
                                                          int
spt_fallback_usg                                                                                                                 xserver_name
                                                          varchar
spt_monitor                                                                                                                      *Row Count*
                                                          1
spt_monitor                                                                                                                      connections
                                                          int
spt_monitor                                                                                                                      cpu_busy
                                                          int
spt_monitor                                                                                                                      idle
                                                          int
spt_monitor                                                                                                                      io_busy
                                                          int
spt_monitor                                                                                                                      lastrun
                                                          datetime
spt_monitor                                                                                                                      pack_errors
                                                          int
spt_monitor                                                                                                                      pack_received
                                                          int
spt_monitor                                                                                                                      pack_sent
                                                          int
spt_monitor                                                                                                                      total_errors
                                                          int
spt_monitor                                                                                                                      total_read
                                                          int
spt_monitor                                                                                                                      total_write
                                                          int
spt_values                                                                                                                       *Row Count*
                                                          2346
spt_values                                                                                                                       high
                                                          int
spt_values                                                                                                                       low
                                                          int
spt_values                                                                                                                       name
                                                          nvarchar
spt_values                                                                                                                       number
                                                          int
spt_values                                                                                                                       status
                                                          int
spt_values                                                                                                                       type
                                                          nchar
BankerBalances                                                                                                                   *Row Count*
                                                          10
BankerBalances                                                                                                                   SumOfBillings
                                                          int
BankerBalances                                                                                                                   BankerID
                                                          int
(101 rows affected)
1> DROP TABLE #TableSummary
2> GO


The SQL statements for cursor processing

Statement      Description
DECLARE        Defines a new cursor.
OPEN           Opens and populates the cursor by executing the SELECT statement defined by the cursor.
FETCH          Retrieves a row from the cursor.
CLOSE          Closes the cursor.
DEALLOCATE     Deletes the cursor definition and releases all system resources associated with the cursor.


Three stored procedures that manage a global cursor

4>
5> create table Billings (
6>     BankerID           INTEGER,
7>     BillingNumber      INTEGER,
8>     BillingDate        datetime,
9>     BillingTotal       INTEGER,
10>     TermsID            INTEGER,
11>     BillingDueDate     datetime ,
12>     PaymentTotal       INTEGER,
13>     CreditTotal        INTEGER
14>
15> );
16> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2> CREATE PROC spOpenBillings
3> AS
4>     DECLARE Billings_Cursor CURSOR
5>     GLOBAL SCROLL DYNAMIC
6>     FOR
7>         SELECT *
8>         FROM Billings WHERE BillingTotal - CreditTotal - PaymentTotal > 0
9>     OPEN Billings_Cursor
10> GO
1>
2> CREATE PROC spGetBilling
3>   @BillingID      int           OUTPUT, @BankerID     int           OUTPUT,
4>   @BillingNumber  varchar(50)   OUTPUT, @BillingDate  smalldatetime OUTPUT,
5>   @BillingTotal   money         OUTPUT, @PaymentTotal money         OUTPUT,
6>   @CreditTotal    money         OUTPUT, @TermsID      int           OUTPUT,
7>   @BillingDueDate smalldatetime OUTPUT, @PaymentDate  smalldatetime OUTPUT
8> AS
9>     FETCH NEXT FROM Billings_Cursor INTO
10>       @BillingID, @BankerID, @BillingNumber, @BillingDate, @BillingTotal,
11>       @PaymentTotal, @CreditTotal, @TermsID, @BillingDueDate, @PaymentDate
12>     RETURN @@FETCH_STATUS
13> GO
1>
2> CREATE PROC spCloseBillings
3> AS
4>     CLOSE Billings_Cursor
5>     DEALLOCATE Billings_Cursor
6>
7> drop PROC spOpenBillings;
8> drop PROC spGetBilling;
9> drop PROC spCloseBillings;
10>
11>
12> drop table Billings;
13> GO
1>
2>