oracle %Rowtype 在 SQL Server 中等效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4022460/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
%Rowtype equivalent in SQL Server
提问by Luca Martini
I'm about to convert a stored procedure from pl/sql to SQL Server. The procedure uses a cursor to loop through the results of a selectquery. Is there a SQL Server equivalent to the ORACLE rowtypeconstruct?
我即将将存储过程从 pl/sql 转换为 SQL Server。该过程使用游标来循环select查询的结果。是否有与 ORACLErowtype结构等效的 SQL Server ？
回答by HarveyFrench
This is a massive reason to dislike SQL Server when compared to oracle.
与 oracle 相比，这是不喜欢 SQL Server 的一个重要原因。
I am so disappointed that SS doesn't have %TYPE and %ROWTYPE. These save hours of work when initially writing code and in the event that the type of a column needs to change, it saves having to go back and re-work all the code.
我很失望 SS 没有 %TYPE 和 %ROWTYPE。这些可以在最初编写代码时节省数小时的工作，并且在需要更改列类型的情况下，无需返回并重新编写所有代码。
In Oracle I used to write something like:
在 Oracle 中，我曾经写过类似的东西：
DECLARE @MyRowVar AS ATable%ROWTYPE;
声明 @MyRowVar 作为 ATable%ROWTYPE;
In SQL Server, I just had to write this this:
在 SQL Server 中，我只需要这样写：
DECLARE @External_ID AS BIGINT = NULL;
DECLARE @PlatformID AS INT = NULL;
DECLARE @ActorIDOfReseller AS INT = NULL;
DECLARE @ActorIDOfClient AS INT = NULL;
DECLARE @ActorIDOfExtension AS INT = NULL;
DECLARE @CallType AS NCHAR (10) = NULL;
DECLARE @CallInitiatedDate AS DATE = NULL;
DECLARE @CallInitiatedTimeHH24MI AS TIME (0) = NULL;
DECLARE @TimePeriodID AS INT = NULL;
DECLARE @CallAnswered AS DATETIME = NULL;
DECLARE @CallAnsweredYN AS BIT = NULL;
DECLARE @CallDispositionID AS INT = NULL;
DECLARE @CountryID AS INT = NULL;
DECLARE @CallPrefixID AS INT = NULL;
DECLARE @FromNumber AS VARCHAR (32) = NULL;
DECLARE @ToNumber AS VARCHAR (80) = NULL;
DECLARE @CallDuration AS INT = NULL;
DECLARE @CallCostToExtension AS DECIMAL (10, 6) = NULL;
DECLARE @CallCostToClient AS DECIMAL (10, 6) = NULL;
DECLARE @CallCostToReseller AS DECIMAL (10, 6) = NULL;
DECLARE @CallCostToAdmin AS DECIMAL (10, 6) = NULL;
DECLARE @Flow AS VARCHAR (3) = NULL;
DECLARE @CallStart AS DATETIME = NULL;
DECLARE @MoneyUnit AS VARCHAR (32) = NULL;
DECLARE @Prefix AS VARCHAR (32) = NULL;
DECLARE @External_CallID AS VARCHAR (255) = NULL;
This is making me very sad.
这让我非常难过。
Harvey
哈维
回答by keith
The SQL way of using a cursor is shown below
SQL使用游标的方式如下图
DECLARE @colA varchar(50), @colB varchar(50)
DECLARE myCursor CURSOR FOR
Select columnA, columnB From table
OPEN myCursor
FETCH NEXT FROM myCursor INTO @colA, @colB
WHILE @@FETCH_STATUS = 0
BEGIN
--do something with @colA and @colB
FETCH NEXT FROM myCursor INTO @colA, @colB
END
CLOSE myCursor
DEALLOCATE myCursor
回答by Piotr Grudzień
As @HarveyFrenchresponded there is no @rowtype. To generate all columns variables You can use this select and copy paste from results:
作为@HarveyFrench回应，没有@rowtype。生成所有列变量您可以使用此选择并从结果中复制粘贴：
SELECT 'DECLARE @customPrefix' + COLUMN_NAME + ' ' + DATA_TYPE + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Your_table'
