处理游标中结构未知的行 [英] Process a row with unknown structure in a cursor

问题描述

我不熟悉使用游标在一组行中循环。但是到目前为止，我已经对要阅读哪些专栏有所了解。

I am new to using cursors for looping through a set of rows. But so far I had prior knowledge of which columns I am about to read.

例如

DECLARE db_cursor FOR 
SELECT Column1, Column2
FROM MyTable

DECLARE @ColumnOne VARCHAR(50), @ColumnTwo VARCHAR(50)

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @ColumnOne, @ColumnTwo
...

但是我要读入的表/ value表没有特定的结构，我应该能够一次处理它们。

But the tables I am about to read into my key/value table have no specific structure and I should be able to process them one row at a time. How, using a nested cursor, can I loop through all the columns of the fetched row and process them according to their type and name?

推荐答案

如何使用嵌套游标在获取的行的所有列中进行循环并根据它们的类型和名称进行处理？

SQL在处理集合方面不是很好。在大多数情况下，您必须事先知道列名，数据类型等等。但是有XQuery。您可以相当容易地将任何 SELECT 转换为XML，并使用强大的功能来处理那里的通用结构。我不建议这样做，但可能值得尝试：

SQL is not very good in dealing with sets generically. In most cases you must know column names, data types and much more in advance. But there is XQuery. You can transform any SELECT into XML rather easily and use the mighty abilities to deal with generic structures there. I would not recommend this, but it might be worth a try:

CREATE PROCEDURE dbo.Get_EAV_FROM_SELECT
(
     @SELECT NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @tmptbl TABLE(TheContent XML);
    DECLARE @cmd NVARCHAR(MAX)= N'SELECT (' + @SELECT + N' FOR XML RAW, ELEMENTS XSINIL);';
    INSERT INTO @tmptbl EXEC(@cmd);

    SELECT r.value('*[1]/text()[1]','nvarchar(max)') AS RowID
          ,c.value('local-name(.)','nvarchar(max)') AS ColumnKey
          ,c.value('text()[1]','nvarchar(max)') AS ColumnValue
    FROM @tmptbl t
    CROSS APPLY t.TheContent.nodes('/row') A(r)
    CROSS APPLY A.r.nodes('*[position()>1]') B(c)
END;
GO

EXEC Get_EAV_FROM_SELECT @SELECT='SELECT TOP 10 o.object_id,o.* FROM sys.objects o';
GO
--Clean-Up for test purpose
DROP PROCEDURE Get_EAV_FROM_SELECT;

简而言之

• 选择将作为字符串传递到过程中。使用SP，我们可以动态创建一条语句并从中创建XML。


• 第一列被视为行的ID （如果不是）（例如 sys.objects ）我们可以编写SELECT并以这种方式强制执行。


• 内部的 SELECT 将读取每一行并返回经典的EAV-列表。

• The select is passed into the procedure as string. With the SP we create a statement dynamically and create XML from it.
• The very first column is considered to be the Row's ID, if not (like in sys.objects) we can write the SELECT and force it that way.
• The inner SELECT will read each row and return a classical EAV-list.

这篇关于处理游标中结构未知的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！