从多个表中提取数据的游标使用方法
P粉141035089
2023-08-16 13:12:27
<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>
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
如果这是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;您需要一个存储过程，因为这是在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;