loops - 遍历游标
问题描述
我想使用另一个游标的行作为参数从选择语句创建游标或表。在我的示例中,我得到了一个包含一列和多行的时态表。这些行应该是下一个选择的条目并显示(或保存)结果。
REPLACE PROCEDURE test (IN name_db VARCHAR(25), IN name_table VARCHAR(25), IN n INT)
DYNAMIC RESULT SETS 1
BEGIN
DECLARE a VARCHAR(32000);
DECLARE b VARCHAR(32000);
DECLARE qry VARCHAR(32000);
DECLARE prindx VARCHAR(32000);
DECLARE i INT;
DECLARE p VARCHAR(32000);
DECLARE idx VARCHAR(32000);
DECLARE diff VARCHAR(32000);
DECLARE csr1 CURSOR WITH RETURN FOR stmt1;
SET a = 'SELECT * FROM (SELECT ColumnName AS col1 FROM DBC.Columns WHERE DatabaseName=''' ||name_db || ''' AND TableName=''' || name_table || ''') A1 ';
SET b = 'WHERE ';
SET i=1;
WHILE i <=N DO
BEGIN
CASE i
WHEN 1 THEN
SET qry = a;
SET prindx = 'SELECT (col1';
WHEN 2 THEN
SET a = a || 'CROSS JOIN (SELECT ColumnName AS col' || CAST(i AS VARCHAR(2)) || ' FROM DBC.Columns WHERE DatabaseName='''||name_db || ''' AND TableName=''' || name_table || ''') A' || CAST(i AS VARCHAR(2)) || ' ';
SET b = b || 'A' || CAST((i-1) AS VARCHAR(2)) || '.col' || CAST((i-1) AS VARCHAR(2)) || ' < A' || CAST(i AS VARCHAR(2)) || '.col' || CAST(i AS VARCHAR(2));
SET qry a || b;
SET prindx = prindx || ' || '','' || col' || CAST(i AS VARCHAR(2));
ELSE
SET a = a || 'CROSS JOIN (SELECT ColumnName AS col' || CAST(i AS VARCHAR(2)) || ' FROM DBC.Columns WHERE DatabaseName='''||name_db || ''' AND TableName=''' || name_table || ''') A' || CAST(i AS VARCHAR(2)) || ' ';
SET b = b || ' AND A' || CAST((i-1) AS VARCHAR(2)) || '.col' || CAST((i-1) AS VARCHAR(2)) || ' < A' || CAST(i AS VARCHAR(2)) || '.col' || CAST(i AS VARCHAR(2));
SET qry a || b;
SET prindx = prindx || ' || '','' || col' || CAST(i AS VARCHAR(2));
END CASE;
SET i = i + 1;
END;
END WHILE;
BEGIN
DECLARE exit HANDLER FOR SQLEXCEPTION
BEGIN
IF SQLCODE <> 3807 THEN RESIGNAL; END IF;
END;
SET p = name_db || '.tbl1 ';
DROP TABLE p;
END;
SET p = 'CREATE TABLE ' || name_db || '.tbl1 AS (' || qry || ') WITH DATA';
EXECUTE IMMEDIATE p;
SET prindx = prindx || ') AS PRYINDX FROM ' || name_db || '.tbl1 ';
PREPARE stmt1 FROM prindx;
OPEN csr1;
--------------------------------------------------------------------------------
FETCH csr1 INTO idx;
SET diff = 'SELECT (MAX(A.TOTAL) - MIN(A.TOTAL)) AS DIFF
FROM (SELECT HASHAMP(HASHBUCKET(HASHROW(' || idx || '))) CODHASH, CAST(COUNT(*) AS DECIMAL(18,0) TOTAL
FROM ' || name_db || '.' || name_table || ' GROUP BY 1) A';
我想在上面的 Select 语句中使用“csr1”的值并获取所有可能的结果。现在,我只能拿最后一个。
解决方案
推荐阅读
- rust - Rust:将两个 Vec 映射到复合结构的第三个 Vec
- opencv - 如何将opencv图像与以mm为单位的尺寸相关联?
- regex - 使用 Excel VBA 提供的 RegEx 仅从 Word doc 中提取第一个匹配项
- google-apps-script - 以编程方式为容器绑定表启用特定的 appscript
- python - Django - 使用电子邮件或用户名登录不起作用
- azure-devops - 在 Azure DevOps Server 2019(本地)发布管道中,一个阶段如何将文件传递到另一个阶段?
- python-3.x - 如何使用带有 Appium 和 Python 的 Android 键盘键入文本?
- java - 这个 SpingMVC 和 Hibernate 事务有什么问题?
- angular - 如何在产品构建中包含 tsconfig.json?
- c - 当用 C 语言写在单独的行上时,反斜杠 \ 如何连接 printf 字符串?