sql - 如何在 Oracle SQL 的 FOR 循环中创建视图
问题描述
我要做的是根据两个表之间的条件创建视图,并且我希望它遍历所有满足此条件的表。
我一直在做一些研究,发现游标对这类事情很有帮助,但我在第 15 行遇到了“游标超出范围”。
DECLARE
query_str VARCHAR2(32000);
CURSOR all_syn IS
SELECT SYNONYM_NAME, TABLE_NAME
FROM ALL_SYNONYMS
WHERE SYNONYM_NAME LIKE 'S!_AG!_%' ESCAPE '!';
CURSOR our_tables IS
SELECT TABLE_NAME
FROM ALL_TABLES
WHERE TABLE_NAME LIKE 'AG!_%1' ESCAPE '!';
BEGIN
query_str := 'CREATE OR REPLACE VIEW ' || LTRIM(all_syn.SYNONYM_NAME, 'S_') || 'AS
SELECT TO_CHAR(itemnum) itemnum,
TO_CHAR(keywordnum) keywordnum,
TO_CHAR(keysetnum) keysetnum,
MOD_BY_EMPLOYEE,
MOD_BY_PROCESS,
MOD_DATE_EMPLOYEE,
MOD_DATE_PROCESS
FROM all_syn.SYNONYM_NAME,
our_tables.TABLE_NAME
WHERE our_tables.TABLE_NAME = ' || LTRIM(all_syn.SYNONYM_NAME, 'S_');
FOR v_rec IN all_syn LOOP
IF (v_rec.TABLE_NAME LIKE 'KEYXITEM%') THEN
EXECUTE IMMEDIATE query_str;
END IF;
END LOOP;
END;
我这样做的原因是因为我的公司的表没有直接连接到某个第 3 方数据库链接,所以他们让我通过在受影响表的末尾添加 1 来更改表名,为这些创建同义词具有 DB 链接的表,然后使用原始表名查看这些同义词,以便它们现在具有 DB 链接并充当原始表,这样我们就不必更改任何代码。我必须将同义词表与已更改的表连接起来,因为我们添加了一些第 3 方表没有的属性。
如果有人有任何建议或意见,将不胜感激!我是使用动态 sql 和 PL/SQL 的新手,所以请多多包涵。
编辑:
所以我改进了我的代码,我觉得我越来越接近我想要的结果,但是我收到了这个奇怪的错误:
第 28 行,第 52 列:PLS-00357:此上下文中不允许表、视图或序列引用“ALL_TABLES.TABLE_NAME”
这对我来说没有意义,因为我在查询中声明它。
BEGIN
FOR v_rec IN all_syn LOOP
IF (v_rec.TABLE_NAME LIKE 'KEYXITEM%') THEN
query_str := 'CREATE OR REPLACE VIEW ' || LTRIM(v_rec.SYNONYM_NAME, 'S_') || ' AS
SELECT itemnum AS item_num,
keywordnum AS key_word_num,
keysetnum AS key_set_num,
MOD_BY_EMPLOYEE,
MOD_BY_PROCESS,
MOD_DATE_EMPLOYEE,
MOD_DATE_PROCESS,
FROM ( SELECT TABLE_NAME
FROM ALL_TABLES
WHERE TABLE_NAME LIKE ' || '''AG!_%1''' || ' ESCAPE ' || '''!''' || '
AND ' || RTRIM(ALL_TABLES.TABLE_NAME, '1') ||' = ' || LTRIM(v_rec.SYNONYM_NAME, 'S_') || ') our_tables,
' || v_rec.SYNONYM_NAME;
-- EXECUTE IMMEDIATE query_str;
END IF;
dbms_output.put_line(query_str);
END LOOP;
END;
解决方案
你不能像那样引用游标。在 FOR LOOP 中移动 query_str 创建并引用记录变量。
编辑:我试图修复 FROM/WHERE 子句,但您可能在那里缺少连接条件。
DECLARE
query_str VARCHAR2(32000);
CURSOR all_syn IS
SELECT SYNONYM_NAME, TABLE_NAME
FROM ALL_SYNONYMS
WHERE SYNONYM_NAME LIKE 'S!_AG!_%' ESCAPE '!';
CURSOR our_tables IS
SELECT TABLE_NAME
FROM ALL_TABLES
WHERE TABLE_NAME LIKE 'AG!_%1' ESCAPE '!';
BEGIN
FOR v_rec IN all_syn LOOP
IF (v_rec.TABLE_NAME LIKE 'KEYXITEM%') THEN
query_str := 'CREATE OR REPLACE VIEW ' || LTRIM(v_rec.SYNONYM_NAME, 'S_') || 'AS
SELECT TO_CHAR(itemnum) itemnum,
TO_CHAR(keywordnum) keywordnum,
TO_CHAR(keysetnum) keysetnum,
MOD_BY_EMPLOYEE,
MOD_BY_PROCESS,
MOD_DATE_EMPLOYEE,
MOD_DATE_PROCESS
FROM ' || v_rec.SYNONYM_NAME || ',
' || v_rec.TABLE_NAME || '
WHERE ' || v_rec.TABLE_NAME = ' || LTRIM(v_rec.SYNONYM_NAME, 'S_');
EXECUTE IMMEDIATE query_str;
END IF;
END LOOP;
END;
推荐阅读
- sql - 如何使用不同的唯一键存储输出(尝试过 CROSS APPLY STRING_SPLIT)?
- kotlin - 使用元素的子列表过滤嵌套列表
- jmeter - 如何在线程中运行所有 API,这将在一次迭代后从 CSV 中获取第一个值,它应该从 csv 中获取第二个值
- python - VCS 系统使用 Python 跟踪图形和资产文件?
- javascript - 使用 Ajax 调用 php 文件后第一次不显示引导模式
- excel - 系统时间与单元格值匹配时弹出消息框
- python - 无法在 Windows 10 中生成 docker 映像
- list - RestAssured 将项目添加到 List POJO
- python - 如何使用for循环为每种类型的前五名游戏绘制条形图
- javascript - 如何在 React 中通配符