SQL Server/T-SQL Tutorial/Cursor/Cursor — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Текущая версия на 10:22, 26 мая 2010
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>