SQL里变量的声明以及常用函数举例

来源：互联网发布：centos 7 gitweb 编辑：程序博客网时间：2024/05/27 20:36

知识点：

①声明变量：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 TESTINSERT 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