PLSQL TO TSQL , RECORE ,%TYPE,%ROWTYPE

 

SET NOCOUNT ON
GO
IF OBJECT_ID('TT') IS NOT NULL
    DROP TABLE tt
GO
CREATE TABLE TT
(
	f1     NVARCHAR(100) DEFAULT 'VAL',
	f2     NVARCHAR(100) DEFAULT 'VAL',
	f3     NVARCHAR(100) DEFAULT 'VAL',
	f4     NVARCHAR(100) DEFAULT 'VAL',
	f5     NVARCHAR(100) DEFAULT 'VAL',
	f6     NVARCHAR(100) DEFAULT 'VAL',
	f7     NVARCHAR(100) DEFAULT 'VAL',
	f8     NVARCHAR(100) DEFAULT 'VAL'
)

 GO
 	
DECLARE @I INT = 0
WHILE @I < 100
BEGIN
    INSERT INTO TT DEFAULT VALUES
    SET @I += 1
END
 
 
 
--SELECT *
--FROM   TT AS t 
GO
--方法1 用游标
DECLARE @f1     NVARCHAR(100),
        @f2     NVARCHAR(100),
        @f3     NVARCHAR(100),
        @f4     NVARCHAR(100),
        @f5     NVARCHAR(100),
        @f6     NVARCHAR(100),
        @f7     NVARCHAR(100),
        @f8     NVARCHAR(100)
 
DECLARE CUR CURSOR  
FOR
    SELECT F1,
           f2,
           f3,
           f4,
           f5,
           f6,
           f7,
           f8
    FROM   TT

OPEN CUR
BEGIN
	FETCH NEXT FROM CUR INTO @f1
	,@f2
	,@f3
	,@f4
	,@f5
	,@f6
	,@f7
	,@f8
	WHILE @@FETCH_STATUS = 0
	BEGIN
	    PRINT '@f1=' + @f1
	    PRINT '@f2=' + @f2
	    PRINT '@f3=' + @f3
	    PRINT '@f4=' + @f4
	    PRINT '@f5=' + @f5
	    PRINT '@f6=' + @f6
	    PRINT '@f7=' + @f7
	    PRINT '@f8=' + @f8 
	    FETCH NEXT FROM CUR INTO @f1
	    ,@f2
	    ,@f3
	    ,@f4
	    ,@f5
	    ,@f6
	    ,@f7
	    ,@f8
	END
END
CLOSE CUR
DEALLOCATE CUR

--方法2 用user define table type 
GO
IF EXISTS(
       SELECT 1
       FROM   SYS.types AS t
       WHERE  t.name = 'dealer_province_TABLE_TYPE'
   )
    DROP TYPE DBO.dealer_province_TABLE_TYPE
GO
CREATE TYPE dbo.dealer_province_TABLE_TYPE AS    TABLE --%TYPE
(
    ID INT,
    f1 NVARCHAR(100) DEFAULT 'VAL',
    f2 NVARCHAR(100) DEFAULT 'VAL',
    f3 NVARCHAR(100) DEFAULT 'VAL',
    f4 NVARCHAR(100) DEFAULT 'VAL',
    f5 NVARCHAR(100) DEFAULT 'VAL',
    f6 NVARCHAR(100) DEFAULT 'VAL',
    f7 NVARCHAR(100) DEFAULT 'VAL',
    f8 NVARCHAR(100) DEFAULT 'VAL'
    PRIMARY KEY CLUSTERED(ID)
)
GO
DECLARE @cursor dealer_province_TABLE_TYPE
DECLARE @RECORD dealer_province_TABLE_TYPE<span style="font-family: Arial, Helvetica, sans-serif;">--%ROWTYPE</span>

DECLARE @end INT = 0
DECLARE @begin INT = 1
INSERT INTO @cursor
SELECT ROW_NUMBER()OVER(ORDER BY f1)  AS ID,
       *
FROM   tt                             AS a

SELECT @end = MAX(id)
FROM   @cursor

WHILE @begin <= @end
BEGIN
    DELETE 
    FROM   @RECORD
    
    DELETE 
    FROM   @cursor OUTPUT DELETED.* INTO @RECORD--省去每个赋值
    WHERE  ID = @begin
    
    IF EXISTS (
           SELECT 1
           FROM   @RECORD
           WHERE  f1 = 'val'
       )
       AND @begin = 10
        SELECT *
        FROM   @RECORD
    
    SET @begin += 1
END