如何使用游标从多个表中获取信息？ [英] How get information from multiple tables using cursor?

问题描述

我有一个查询，该查询返回多个表，如下所示：

I have a query, that returns multiple tables, something like that:

SELECT TableName, DatabaseName +'.'+ TableName, ColumnName
FROM DBC.Columns
WHERE ColumnName = 'id'

而且我需要通过查看存储在这些表中的信息来遍历这些表，以便仅获取特定的表。

And I need to loop through these tables by looking to the information stored in these tables, in order to get only specific tables.

我尝试了以下类似的代码，使用 LOOP和光标，但表示查询无效（代码摘自此处）：

I tried something like code below, using 'LOOP' and cursor, but it says that Query is invalid (code have been taken from here):

DECLARE cursor_Tables CURSOR FOR     
    SELECT DatabaseName || '.' || TableName
    FROM   DBC.Columns
    WHERE  ColumnName  ='id'; 

OPEN cursor_Tables; 
    label1: 
    LOOP    
        FETCH  cursor_Tables into tbName;
        IF (SQLSTATE ='02000') THEN
            LEAVE label1;
        END IF;

        CASE WHEN (  
            SELECT COUNT(*)
            FROM prd3_db_tmd.K_PTY_NK01
            WHERE id = 0 ) > 0
             THEN tbName
        END 
    END LOOP label1;
CLOSE cursor_Tables;
END;

我该如何实际处理此问题？我还需要使用程序吗？ DBMS是Teradata

How can I actually deal with this problem? Do I need to use procedure in addition? DBMS is Teradata

推荐答案

您需要一个存储过程，因为这是您可以在Teradata中使用游标的唯一位置。

You need a Stored Procedure because this is the only place where you can use a cursor in Teradata.

REPLACE PROCEDURE testproc()
DYNAMIC RESULT SETS 1
BEGIN
   DECLARE tbName VARCHAR(257);
   DECLARE SqlStr VARCHAR(500);

   -- temporary table to store the result set
   CREATE VOLATILE TABLE _vt_(tbName VARCHAR(257)) ON COMMIT PRESERVE ROWS;

   -- your existing query to return the table name
   -- Better use ColumnsV instead of Columns
   FOR cursor_Tables AS    
       SELECT DatabaseName || '.' || TABLENAME AS tbName
       FROM   DBC.ColumnsV
       WHERE  ColumnName  ='id'
   DO -- prepare the dynamic SQL ...
      SET SqlStr = 
         'insert into _vt_
          select ''' || cursor_tables.tbName || ''' 
          from ' || cursor_tables.tbName || '
          where id = 0 
          having count(*) > 0;
          ';
      -- ... and run it
      EXECUTE IMMEDIATE SqlStr;
   END FOR;

   BEGIN -- return the result set
      DECLARE resultset CURSOR WITH RETURN ONLY FOR S1;
      SET SqlStr = 'SELECT * FROM _vt_;';
      PREPARE S1 FROM SqlStr;
      OPEN resultset;
   END;

   DROP TABLE vt;
END;

这篇关于如何使用游标从多个表中获取信息？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！