MSSQL Cursor 範例


--定義一個cursor 來自指定資料表內容
DECLARE MY_CURSOR Cursor FOR
SELECT cy_closeRate,Currencycode From [Currency_rate]
WHERE cy_closeTime > DATEADD(Day,-2,getdate())
--開啟cursor
Open MY_CURSOR
--定義cursor裡要取出的物件
DECLARE @closeRate FLOAT, @cyID VARCHAR(50)

--進入cursor
Fetch NEXT FROM MY_CURSOR INTO @closeRate, @cyID
--當陳述式失敗，或資料列超出結果集時停止迴圈
While (@@FETCH_STATUS <> -1)
BEGIN
--偵錯或加入要應用的程式碼
--PRINT CONVERT(VARCHAR(100),@closeRate) +'--'+ @cyID
--再到下個指標去
FETCH NEXT FROM MY_CURSOR INTO @closeRate, @cyID
END
--關閉cursor
CLOSE MY_CURSOR
--釋放cursor
DEALLOCATE MY_CURSOR
GO


Using Parameters with an SQL IN Clause

在我前面的文章有提到 WHERE IN () 帶多值的問題，今天看到一篇國外的文章寫到，和大家一起分享

Using parameters can be a bit tricky with an IN clause though, e.g.

SELECT * FROM MyTable WHERE ID IN (3, 10, 17)


As an example, assume you have a ListBox containing possible field values. If the user can select zero, one or more of the items in the list to filter the results you could do this:


SqlConnection connection = new SqlConnection("connection string here");
SqlCommand command = new SqlCommand();
StringBuilder query = new StringBuilder("SELECT * FROM MyTable");

switch (this.listBox1.SelectedItems.Count)
{
case 0:
break;

case 1:
query.Append(" WHERE MyColumn = @MyColumn");
command.Parameters.AddWithValue("@MyColumn", this.listBox1.SelectedItem);
break;

default:
query.Append(" WHERE MyColumn IN (");
string paramName;

for (int index = 0; index < this.listBox1.SelectedItems.Count; index++) { paramName = "@MyColumn" + index.ToString(); if (index > 0)
{
query.Append(", ");
}

query.Append(paramName);
command.Parameters.AddWithValue(paramName, this.listBox1.SelectedItems[index]);
}
query.Append(")");
break;
}

command.CommandText = query.ToString();
command.Connection = connection;


上面是VB的語法，我下一篇會分享實作過的C#語法。

Primary Key 和 Unique Index

1.
Primary Key = Unique Index + Not null,
若只是 Unique Index 欄位, 則可以為 null.

2.
Table 之中, 最多只有一個 Primary Key, 但能擁有多個 Unique Index
NOT NULL = 設定該欄立不可含Null 值
UNIQUE = 設定一個或多個欄位組合，資料內容需唯一不可重覆
PRIMARY = KEY 設定資料表格不可重覆、空白或為Null的主鍵值
FOREIGN = KEY 設定該欄位值的存在必須關連並參照指定資料表格的欄位值

P.S. 基本概念的整理

MSSQL 清除 LOG 並縮減空間的語法

一般時候我們都用以下的語法進行清理

DUMP TRANSACTION {DBName} WITH NO_LOG


可是實體的檔案大小卻不會減少，一樣佔用那麼多的硬碟空間。

SET NOCOUNT ON DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT

USE {DBName ex HRMS} -- 要操作的資料庫名
SELECT
@LogicalFileName = '{Log file name ex HRMS_Log}', -- 日誌文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log. (M)
@NewSize = 1 -- 你想設定的日誌文件的大小(M)

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName

SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles WHERE name = @LogicalFileName

CREATE TABLE DummyTrans (DummyColumn char (8000) not null)

DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(), @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NewSize)

EXEC (@TruncLog)

-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) BEGIN -- update INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans SELECT @Counter = @Counter + 1 END EXEC (@TruncLog) END SELECT 'Final Size of ' + db_name() + ' LOG is ' + CONVERT(VARCHAR(30),size) + ' 8K pages or ' + CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' FROM sysfiles WHERE name = @LogicalFileName DROP TABLE DummyTrans SET NOCOUNT OFF

更簡潔的寫法

USE [@DBName]
BACKUP LOG [@DBName] WITH TRUNCATE_ONLY
DBCC SHRINKFILE (@LogicalFileName, @NewSize)


MSSQL T-SQL迴圈寫法


DECLARE @_i INT
DECLARE @_MAX INT
SET @_i = 0
SET @_MAX = 10 -- 要產生幾筆資料
WHILE (@_i<@_MAX) BEGIN --要迴圈的語法 INSERT INTO #TT VALUES('T') --加1 Set @_i=@_i+1 END