545
技術社區[雲棲]
SQL裏變量的聲明以及常用函數舉例
知識點：
①聲明變量：DECLARE 關鍵字
②循環語句：WHILE ...
BEGIN ...
END
③數據類型轉換：CAST()函數
應用舉例：
初始化腳本：
CREATE TABLE [dbo].[TEST]( [ID] [int] IDENTITY(1,1) NOT NULL, [TID] [int] NULL, [Discription] [nvarchar](200) NULL ) --測試數據 DELETE FROM TEST INSERT INTO TEST (TID,Discription)VALUES (1,'記錄1-1') INSERT INTO TEST (TID,Discription)VALUES (1,'記錄1-2') INSERT INTO TEST (TID,Discription)VALUES (1,'記錄1-3') INSERT INTO TEST (TID,Discription)VALUES (1,'記錄1-4') INSERT INTO TEST (TID,Discription)VALUES (1,'記錄1-5') INSERT INTO TEST (TID,Discription)VALUES (2,'記錄2-1') INSERT INTO TEST (TID,Discription)VALUES (2,'記錄2-2') INSERT INTO TEST (TID,Discription)VALUES (2,'記錄2-3') SELECT * FROM TEST
演練腳本：
DECLARE @startIndex INT,@endIndex INT,@discription NVARCHAR(100);
SELECT @startIndex= MIN(ID)FROM TEST;--最小記錄ID
SELECT @endIndex= MAX(ID)FROM TEST;--最大記錄ID
WHILE @startIndex <@endIndex
BEGIN
Select @discription=('第'+CAST(ID as NVARCHAR(100))+'行記錄：'+CAST(TID as NVARCHAR(100))+'.'+Discription) FROM TEST WHERE ID=@startIndex;
PRINT(@discription);
SELECT @startIndex=@startIndex+1--當前記錄加1
END
最後更新：2017-04-02 06:52:22