从多个表中提取数据的游标使用方法
P粉141035089
P粉141035089 2023-08-16 13:12:27
0
2
562
<p>我有一个查询,返回多个表,类似这样:</p> <pre class="brush:php;toolbar:false;">SELECT TableName, DatabaseName +'.'+ TableName, ColumnName FROM DBC.Columns WHERE ColumnName = 'id'</pre> <p>我需要通过查看存储在这些表中的信息来循环遍历这些表,以便只获取特定的表。</p> <p>我尝试了下面的代码,使用了'LOOP'和游标,但它显示<code>查询无效</code>(代码来自这里):</p> <pre class="brush:php;toolbar:false;">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;</pre> <p>我应该如何解决这个问题?我需要额外使用存储过程吗?DBMS是Teradata。</p>
P粉141035089
P粉141035089

全部回复(2)
P粉221046425

如果这是SQL Server,您可以查看以下SQL游标,我编辑了游标声明和其中的代码 虽然它们可能与您的要求不同,但我认为您可以轻松修改

declare @sql nvarchar(max)
declare @tablename nvarchar(100)

DECLARE cursor_Tables CURSOR FOR     
    SELECT s.name + '.' + o.name 
        --s.name [schema], o.name [table]
    FROM   sys.Columns c
    inner join sys.objects o on c.object_id = o.object_id
    inner join sys.schemas s on s.schema_id = o.schema_id
    WHERE  c.Name  ='id' and o.type = 'U'

/*
SELECT TableName, DatabaseName +'.'+ TableName, ColumnName
FROM DBC.Columns
WHERE ColumnName = 'id'
*/
OPEN cursor_Tables; 

FETCH NEXT FROM cursor_Tables INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN

--  print @tablename
set @sql = 'select case when count(*) > 0 then ''' + @tablename + ''' else '''' end from ' + @tablename
exec sp_executesql @sql

 FETCH NEXT FROM cursor_Tables INTO @tablename
END

CLOSE cursor_Tables;
DEALLOCATE cursor_Tables;
P粉752479467

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

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

   -- 临时表来存储结果集
   CREATE VOLATILE TABLE _vt_(tbName VARCHAR(257)) ON COMMIT PRESERVE ROWS;

   -- 您现有的查询来返回表名
   -- 最好使用ColumnsV而不是Columns
   FOR cursor_Tables AS    
       SELECT DatabaseName || '.' || TABLENAME AS tbName
       FROM   DBC.ColumnsV
       WHERE  ColumnName  ='id'
   DO -- 准备动态SQL ...
      SET SqlStr = 
         'insert into _vt_
          select ''' || cursor_tables.tbName || ''' 
          from ' || cursor_tables.tbName || '
          where id = 0 
          having count(*) > 0;
          ';
      -- ... 并执行它
      EXECUTE IMMEDIATE SqlStr;
   END FOR;

   BEGIN -- 返回结果集
      DECLARE resultset CURSOR WITH RETURN ONLY FOR S1;
      SET SqlStr = 'SELECT * FROM _vt_;';
      PREPARE S1 FROM SqlStr;
      OPEN resultset;
   END;

   DROP TABLE vt;
END;
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板