oracle 根据表中某两个字段，算出另一个字段值并修改

<think>我们面对的是一个Oracle数据库的查询需求：检查100个表是否存在名为'update'的字段，并且如果存在，则查询该字段是否有触发器。 注意：在Oracle中，'update'是一个关键字，因此如果字段名是'update'，我们需要用双引号括起来（例如："update"）以避免语法错误。 步骤： 1. 检查这些表中是否存在名为'update'的列。 2. 对于存在该列的表，检查是否有触发器定义在该列上（或者更准确地说，触发器是否引用了该列）。 然而，需要注意的是，触发器并不是绑定在某个列上的，而是绑定在表上的，并且触发器可以在其代码中引用特定的列。因此，我们需要检查触发器代码中是否引用了该列。 我们可以分为两步： 第一步：查询哪些表有名为'update'的列。 我们可以从数据字典视图`all_tab_columns`中查询。 第二步：对于这些表，查询是否有触发器在其触发条件或触发体中引用了该列（即包含字符串':update'或'update'，但要注意避免误匹配，因为'update'是一个常见词）。 但是，注意：触发器代码中引用列的方式可能是:new.update或:old.update（如果该列名是保留字，可能需要用双引号，但在触发器代码中通常不用双引号，而是直接使用列名，但如果是保留字，则必须用双引号，但这样容易出错，所以一般不建议使用保留字作为列名）。 因此，我们可能需要检查触发器代码中是否包含字符串':new.update'或':old.update'，或者如果列名用了双引号，则可能是':new."update"'或':old."update"'。但是，由于'update'是关键字，在触发器代码中引用时，如果列名是'update'，则必须使用双引号（因为触发器代码是PL/SQL代码，而PL/SQL中关键字必须用双引号括起来才能作为标识符）。然而，实际情况可能复杂，因为列名可能是大小写敏感的（如果创建时用了双引号）。 但为了简化，我们假设该列在创建时使用了双引号（即列名是"update"），那么我们在触发器代码中查找时，应该查找`:new."update"`或`:old."update"`。 然而，我们也可以从另一个角度考虑：我们只关心触发器是否与这个列有关，即触发器是否在UPDATE OF子句中指定了这个列，或者在触发器代码中修改或引用了这个列。 但是，在Oracle中，我们可以通过以下方式： 1. 查询`all_triggers`视图获取触发器的基本信息。 2. 查询`all_trigger_cols`视图可以知道触发器与哪些列有关（但注意，这个视图只记录了触发器中在UPDATE OF子句中指定的列，对于其他情况，比如触发器代码中引用的列，不会记录在这个视图中）。 因此，我们需要结合两个部分： - 对于在UPDATE OF子句中指定了列的触发器（即行触发器，并且指定了列），我们可以通过`all_trigger_cols`视图来检查。 - 对于触发器代码中引用了该列的情况，我们需要查询`all_triggers`视图中的`trigger_body`字段（或者`all_source`视图），并在代码中搜索该列名的出现。 但是，由于触发器代码可能很长，而且可能有多个触发器，我们需要仔细设计。 鉴于需求是检查100个表，我们可以编写一个PL/SQL脚本或者使用多个SQL查询来实现。 由于在SQL查询中直接处理100个表并检查触发器代码可能比较慢，而且触发器代码可能很长，我们可以考虑分两步： 方案： 1. 先找出100个表中哪些表有名为'update'的列（注意：列名在数据字典中默认是大写的，但如果我们创建时用了双引号，则可能是小写或混合大小写。但这里我们假设列名是大写的'UPDATE'，或者小写的'update'？）。由于Oracle默认存储的是大写，除非创建时用了双引号。因此，我们有两种情况： - 如果列名创建时没有用双引号，那么列名在数据字典中为'UPDATE'。 - 如果用了双引号，那么列名在数据字典中就是'update'（小写）或者指定的其他大小写。 但是，我们的需求是名为'update'，所以可能是小写。所以我们需要检查列名是'update'（小写）的情况，或者可能是'UPDATE'（大写）？这取决于创建表时的写法。 为了准确，我们假设用户要求的是列名是'update'（小写），那么我们在查询数据字典时，需要区分大小写。在Oracle中，如果列名创建时没有用双引号，那么会被转换成大写。所以如果创建时写的是update（没有双引号），那么实际存储为'UPDATE'。如果创建时用了双引号，例如"update"，那么存储为'update'。 因此，我们需要同时检查'update'和'UPDATE'吗？但是需求是名为'update'，所以应该是小写。所以我们可以只检查列名等于'update'（小写）的情况。但是，如果用户创建表时没有用双引号，那么列名就是大写的，所以不会匹配。所以我们需要明确：用户可能指的是列名在应用程序中显示为'update'，但在数据库中可能是大写的。 为了保险，我们可以同时检查两种形式，或者询问用户。但这里，我们按照需求，只检查小写的'update'（因为需求中明确写了'update'，并且是字段名，所以可能是要求小写形式）。因此，我们在查询`all_tab_columns`时，使用`column_name = 'update'`。 2. 对于有该列的表，我们检查触发器： a. 首先，检查`all_trigger_cols`视图，看是否有触发器在UPDATE OF子句中指定了该列（注意：这个视图中的column_name也是区分大小写的，所以我们需要用'update'去匹配）。 b. 其次，检查触发器代码中是否引用了该列。我们可以通过查询`all_triggers`视图的`trigger_body`字段（注意：这个字段是LONG类型，可能不方便处理），或者查询`all_source`视图（触发器代码存储在`all_source`中，类型为'TRIGGER'）中是否包含该列名的引用。但是，在代码中搜索时，我们需要搜索`:new.update`、`:old.update`，但注意，因为列名是保留字，所以实际代码中应该是`:new."update"`和`:old."update"`。另外，也可能在代码中直接写"update"作为字符串，所以我们需要避免误匹配。 因此，我们可以这样设计查询： 第一步：查询有列'update'的表： ```sql SELECT table_name FROM all_tab_columns WHERE column_name = 'update' -- 因为我们的列名是小写，所以这里直接用小写匹配（如果创建时用了双引号） OR column_name = 'UPDATE'; -- 或者没有用双引号，系统自动转成大写 ``` 但是，需求是名为'update'，所以我们可能更关心小写的，但为了全面，我们同时查两种。不过，我们也可以认为，如果用户要求的是小写的，那么创建时一定用了双引号，所以只查小写即可。这里为了保险，我们查两种，然后分别标记。 第二步：对于每个找到的表，我们检查触发器： 我们可以使用以下查询来检查是否有触发器在UPDATE OF子句中指定了该列（注意：只有UPDATE触发器才可能有UPDATE OF子句）： ```sql SELECT DISTINCT atc.table_name, atc.trigger_name FROM all_trigger_cols atc JOIN all_triggers atr ON (atc.trigger_name = atr.trigger_name AND atc.owner = atr.owner) WHERE atc.table_name IN ( ... ) -- 第一步中得到的表名列表 AND atc.column_name = 'update' -- 同样，这里列名是小写（因为该视图中的列名是创建时的实际大小写） AND atr.triggering_event LIKE '%UPDATE%' -- 确保是UPDATE触发器 ``` 另外，检查触发器代码中是否引用了该列（注意：这里我们只关心UPDATE触发器吗？其实INSERT和DELETE也可能引用该列，但通常我们关心的是UPDATE操作对它的修改，但需求是“该字段是否有触发器”，所以任何引用该字段的触发器都应该算。所以我们需要检查所有触发器）： 由于`all_triggers`中的trigger_body是LONG类型，不能直接使用LIKE，我们可以使用`all_source`视图。 对于代码中的引用，我们查找触发器代码中包含`:new."update"`或`:old."update"`的字符串（注意：因为列名是保留字，所以必须用双引号，否则会报错）。同时，也要注意大小写，因为PL/SQL代码中通常不区分大小写，但字符串中的内容区分。不过，在PL/SQL中，如果没有用双引号，标识符会被转换成大写。但是，由于列名是'update'（小写），所以在代码中引用时必须用双引号，并且大小写一致。因此，代码中应该写`:new."update"`。 所以，我们可以这样查询： ```sql SELECT DISTINCT a.owner, a.name AS trigger_name, a.type, a.line FROM all_source a WHERE a.type = 'TRIGGER' AND a.text LIKE '%:new."update"%' -- 注意：这里可能匹配到多行，我们只需要知道有匹配即可 OR a.text LIKE '%:old."update"%' ``` 但是，这个查询会扫描所有触发器的源代码，而且我们只关心那100个表中有'update'列的表上的触发器。所以我们需要先获取这些表上的触发器，然后再检查这些触发器的源代码。 因此，我们可以先获取这些表上的所有触发器名称，然后去`all_source`中查找这些触发器的代码。 综合起来，我们可以编写一个大的查询，或者使用PL/SQL块。 由于我们可能需要处理多个表，而且触发器代码可能很长，这里我们提供分步的SQL查询。 但是，由于100个表可能很多，我们可以先用一个查询找出所有有'update'列的表，然后对于每个表，分别查询其相关的触发器。 为了简化，我们可以写两个查询： 查询1：找出有'update'列的表（同时包含大写和小写的情况）： ```sql SELECT table_name, column_name FROM all_tab_columns WHERE column_name IN ('update', 'UPDATE'); ``` 查询2：对于每个表，找出在UPDATE OF子句中指定了'update'列的触发器（注意：列名匹配区分大小写，所以如果列名是'update'，则这里用'update'；如果是'UPDATE'，则用'UPDATE'）： ```sql SELECT atc.table_name, atc.trigger_name, atc.column_name FROM all_trigger_cols atc WHERE atc.table_name IN (SELECT table_name FROM all_tab_columns WHERE column_name IN ('update','UPDATE')) AND atc.column_name IN ('update','UPDATE'); ``` 查询3：找出这些表上的所有触发器，然后检查这些触发器的源代码中是否包含字符串':new."update"'或':old."update"'（注意：这里假设列名在代码中是用小写和双引号引用的，因为列名是小写的'update'）。但是，如果列名是大写的'UPDATE'，那么在代码中引用时可能不需要双引号（因为不是保留字？注意：'UPDATE'作为列名，虽然与关键字同形，但Oracle可能允许不用双引号，因为关键字不区分大小写，而列名是大写，所以不会冲突？）但实际上，即使列名是大写的，在PL/SQL代码中引用时，也可以不用双引号，直接写:new.UPDATE。所以，我们需要同时考虑两种情况： 对于列名为'update'（小写）的情况，在代码中必须用双引号，所以搜索`:new."update"`或`:old."update"`。 对于列名为'UPDATE'（大写）的情况，在代码中可能写为`:new.UPDATE`或`:old.UPDATE`（没有双引号）。 因此，我们需要分别处理： 对于小写列名，我们搜索：`'%:new."update"%'` 和 `'%:old."update"%'` 对于大写列名，我们搜索：`'%:new.update%'` 和 `'%:old.update%'`（注意：这里没有双引号，并且注意大小写，因为PL/SQL代码在all_source中存储的是原始大小写，但通常关键字和列名可能都是大写的？） 但是，实际情况可能更复杂，因为代码的书写方式各异。所以我们可以用大小写不敏感的搜索，但是Oracle的LIKE是大小写敏感的。我们可以使用`REGEXP_LIKE`来忽略大小写。 不过，由于`all_source.text`是LONG类型，我们不能在WHERE条件中使用普通函数（包括正则函数）来处理它，这会导致问题。因此，我们需要采用其他方法。 替代方案：我们可以使用`DBMS_METADATA.GET_DDL`来获取触发器的DDL，然后检查，但这需要编写PL/SQL过程。 考虑到复杂度，我们可能更倾向于使用PL/SQL块来逐行检查源代码。 但是，由于这个操作可能比较重，而且我们只是检查100个表，如果表不多，我们可以手动逐个检查。但这里我们假设需要自动化。 因此，我们可以这样： 1. 先获取有列'update'或'UPDATE'的表。 2. 获取这些表上的所有触发器的名称（从`all_triggers`）。 3. 对于每个触发器，从`all_source`中获取其源代码，并逐行检查是否包含`:new."update"`、`:old."update"`（小写）或`:new.UPDATE`、`:old.UPDATE`（大写）等字符串（注意：也可能有空格，如`: new . "update"`，但通常不会这样写）。 由于在SQL中直接处理比较困难，我们可以写一个PL/SQL匿名块。 但是，由于用户要求的是SQL查询，我们可以分两步提供： 第一步：查询有列的表和触发器（通过all_trigger_cols）的情况。 第二步：查询触发器代码中引用的情况，我们通过一个PL/SQL块来输出。 由于时间有限，我们这里先提供第一步和第二步中通过all_trigger_cols的查询，然后对于代码引用的情况，我们给出一个示例查询（但可能不完整，因为LONG类型的限制），并建议使用PL/SQL。 因此，最终方案： 查询1：获取有列'update'或'UPDATE'的表 ```sql SELECT table_name, column_name FROM all_tab_columns WHERE column_name IN ('update', 'UPDATE'); ``` 查询2：获取这些表上在UPDATE OF子句中指定了该列的触发器 ```sql SELECT atc.table_name, atc.trigger_name, atc.column_name FROM all_trigger_cols atc WHERE atc.table_name IN (SELECT table_name FROM all_tab_columns WHERE column_name IN ('update','UPDATE')) AND atc.column_name IN ('update','UPDATE'); ``` 查询3：获取这些表上的所有触发器，并检查源代码（这里我们使用一个PL/SQL块来避免LONG类型的处理问题） 由于查询3比较复杂，我们可以这样写一个匿名块： ```plsql SET SERVEROUTPUT ON DECLARE CURSOR c_tables IS SELECT table_name FROM all_tab_columns WHERE column_name IN ('update', 'UPDATE'); CURSOR c_triggers (p_table_name VARCHAR2) IS SELECT trigger_name FROM all_triggers WHERE table_name = p_table_name; v_source_line VARCHAR2(4000); v_found BOOLEAN; BEGIN FOR t IN c_tables LOOP FOR trg IN c_triggers(t.table_name) LOOP v_found := FALSE; -- 检查触发器代码中是否包含引用 FOR src IN (SELECT text FROM all_source WHERE owner = 'YOUR_OWNER' AND name = trg.trigger_name AND type = 'TRIGGER' ORDER BY line) LOOP -- 检查小写列名（带双引号）的引用 IF src.text LIKE '%:new."update"%' OR src.text LIKE '%:old."update"%' THEN v_found := TRUE; EXIT; END IF; -- 检查大写列名（不带双引号）的引用 IF src.text LIKE '%:new.UPDATE%' OR src.text LIKE '%:old.UPDATE%' THEN v_found := TRUE; EXIT; END IF; END LOOP; IF v_found THEN DBMS_OUTPUT.PUT_LINE('表 ' || t.table_name || ' 上的触发器 ' || trg.trigger_name || ' 引用了 update 列'); END IF; END LOOP; END LOOP; END; / ``` 注意：上面的PL/SQL块中，需要替换'YOUR_OWNER'为实际的模式名（或者使用USER_TRIGGERS等视图，但这里我们使用all_triggers，所以需要知道owner）。另外，如果表很多，输出可能会很长。 另外，由于触发器可能属于不同的模式，我们在all_triggers中需要指定owner。但如果我们只关心当前用户下的表，那么我们可以使用user_tab_columns和user_triggers等视图。 考虑到用户可能没有权限访问all_视图，我们可以使用user_视图（如果只检查当前用户下的表）。 因此，如果用户只检查当前用户下的表，我们可以修改为使用user_视图。 由于问题没有说明，我们假设检查当前用户下的表。 修改后的查询： 查询1（当前用户）： ```sql SELECT table_name, column_name FROM user_tab_columns WHERE column_name IN ('update', 'UPDATE'); ``` 查询2（当前用户）： ```sql SELECT utc.table_name, utc.column_name, ut.trigger_name FROM user_trigger_cols utc JOIN user_triggers ut ON (utc.trigger_name = ut.trigger_name) WHERE utc.table_name IN (SELECT table_name FROM user_tab_columns WHERE column_name IN ('update','UPDATE')) AND utc.column_name IN ('update','UPDATE') AND ut.trigger_type LIKE 'AFTER%' OR ut.trigger_type LIKE 'BEFORE%' -- 行触发器 AND ut.triggering_event LIKE '%UPDATE%'; -- 确保是UPDATE触发器 ``` 注意：这里我们通过user_trigger_cols只能获取在UPDATE OF子句中指定的列。对于其他情况，我们仍然需要检查源代码。 查询3的PL/SQL块（当前用户）： ```plsql SET SERVEROUTPUT ON DECLARE CURSOR c_tables IS SELECT table_name FROM user_tab_columns WHERE column_name IN ('update', 'UPDATE'); CURSOR c_triggers (p_table_name VARCHAR2) IS SELECT trigger_name FROM user_triggers WHERE table_name = p_table_name; v_source_line VARCHAR2(4000); v_found BOOLEAN; BEGIN FOR t IN c_tables LOOP FOR trg IN c_triggers(t.table_name) LOOP v_found := FALSE; -- 检查触发器代码中是否包含引用 FOR src IN (SELECT text FROM user_source WHERE name = trg.trigger_name AND type = 'TRIGGER' ORDER BY line) LOOP -- 检查小写列名（带双引号）的引用 IF src.text LIKE '%:new."update"%' OR src.text LIKE '%:old."update"%' THEN v_found := TRUE; EXIT; END IF; -- 检查大写列名（不带双引号）的引用 IF src.text LIKE '%:new.UPDATE%' OR src.text LIKE '%:old.UPDATE%' THEN v_found := TRUE; EXIT; END IF; END LOOP; IF v_found THEN DBMS_OUTPUT.PUT_LINE('表 ' || t.table_name || ' 上的触发器 ' || trg.trigger_name || ' 引用了 update 列'); END IF; END LOOP; END LOOP; END; / ``` 注意：这个PL/SQL块会输出所有引用'update'列的触发器。但是，它可能会比较慢，因为要读取每个触发器的源代码。 另外，我们也可以尝试在查询中使用`DBMS_LOB.INSTR`来搜索，但`user_source.text`是VARCHAR2类型，所以我们可以用LIKE，但是逐行搜索。上面的PL/SQL块就是逐行搜索的。 总结： 1. 运行查询1，得到有'update'列的表。 2. 运行查询2，得到在UPDATE OF子句中指定了'update'列的触发器。 3. 运行PL/SQL块，得到在触发器代码中引用了'update'列的触发器。 这样，我们就完成了需求。 但是，由于'update'是关键字，在创建触发器时可能会遇到问题，所以实际中很少用'update'作为列名。如果可能，建议修改列名。 另外，注意：在PL/SQL块中，我们假设触发器代码中引用列的方式是固定的（如:new."update"），但实际代码可能有不同的格式（如包含空格、换行等），所以我们的LIKE可能漏掉一些情况。我们可以考虑使用更复杂的匹配（如正则表达式），但在PL/SQL中逐行处理正则表达式可能会影响性能。 因此，这是一个基本的解决方案。 由于用户要求检查100个表，我们可以将上述步骤组合成一个脚本。 注意：以上脚本需要在有权限的环境下运行。 如果用户需要一次性查询，我们可以尝试将三个步骤的结果汇总到一个查询中，但触发器代码部分仍然需要PL/SQL。 因此，我们提供分步的解决方案。</think>### 检查 Oracle 数据库中包含 'update' 字段的表及其触发器 #### 步骤 1：检查表是否包含 'update' 字段 由于 'UPDATE' 是 Oracle 关键字，若字段名确为小写 'update'，需用双引号包裹（如 `"update"`）。以下查询检查当前用户模式下 100 个表的字段情况： ```sql SELECT table_name, 'YES' AS has_update_column FROM user_tab_columns WHERE column_name = 'update' -- 检查小写字段名 OR column_name = 'UPDATE' -- 检查大写字段名 AND table_name IN ( SELECT table_name FROM user_tables WHERE ROWNUM <= 100 -- 限制 100 个表 ); ``` #### 步骤 2：检查 'update' 字段的触发器 ##### 方法 1：通过数据字典直接关联（推荐） ```sql SELECT t.table_name, 'YES' AS has_update_column, NVL2(tg.trigger_name, 'YES', 'NO') AS has_trigger FROM ( SELECT table_name, column_name FROM user_tab_columns WHERE column_name IN ('update', 'UPDATE') AND ROWNUM <= 100 ) t LEFT JOIN user_triggers tg ON tg.table_name = t.table_name AND tg.triggering_event LIKE '%UPDATE%' -- 仅检查 UPDATE 触发器 LEFT JOIN user_trigger_cols tc ON tc.trigger_name = tg.trigger_name AND tc.column_name = t.column_name -- 关联到具体字段 WHERE tc.column_name IS NOT NULL OR tg.trigger_name IS NULL; ``` ##### 方法 2：检查触发器代码（精确匹配） 若字段是保留字，触发器代码中会以 `:new."update"` 形式引用： ```sql SELECT table_name, trigger_name, 'REFERENCES update COLUMN' AS trigger_type FROM ( SELECT DISTINCT u.name AS table_name, s.name AS trigger_name FROM user_source s JOIN user_triggers t ON s.name = t.trigger_name JOIN user_tab_columns c ON t.table_name = c.table_name WHERE s.text LIKE '%:new."update"%' -- 检查 NEW 引用 OR s.text LIKE '%:old."update"%' -- 检查 OLD 引用 AND c.column_name IN ('update', 'UPDATE') AND ROWNUM <= 100 ); ``` #### 关键说明： 1. **字段名大小写敏感性**： - 若创建表时使用双引号（如 `"update"`），字典中存储小写 - 若未用双引号（如 `UPDATE`），字典中存储大写 - 查询需同时检查 `'update'` 和 `'UPDATE'` 2. **触发器关联逻辑**： - `user_trigger_cols` 仅记录在 `UPDATE OF` 子句显式指定的字段[^2] - 若触发器在代码中引用字段（如 `:new."update"`），需扫描源代码[^4] 3. **权限要求**： - 需 `SELECT` 权限访问 `USER_TAB_COLUMNS`, `USER_TRIGGERS`, `USER_SOURCE` 等字典视图 - 跨模式检查需替换为 `ALL_`/`DBA_` 视图并添加 `owner` 条件 #### 操作建议： 1. 先运行 **步骤 1 查询** 确认哪些表包含 'update' 字段 2. 对存在字段的表运行 **步骤 2 方法 1** 3. 若需验证隐式引用，对目标表运行 **步骤 2 方法 2** > ⚠️ 注意：使用关键字作为字段名易引发语法冲突，建议修改字段名（如 `last_update`）[^3]。 --- ### 相关问题 1. **如何批量修改包含 'update' 字段的表结构？** （使用 `ALTER TABLE` 重名字段需处理依赖对象） 2. **Oracle 触发器有哪些类型？如何区分 BEFORE/AFTER 触发器？** （行级 vs 语句级触发器的性能影响） 3. **如何导出触发器的完整定义代码？** （使用 `DBMS_METADATA.GET_DDL` 或 SQL Developer 导出） 4. **若字段是加密的 TDE 列，触发器检查有何特殊要求？** （需处理钱包文件和 `sqlnet.ora` 配置[^5]） [^1]: 数据字典视图（如 `USER_TAB_COLUMNS`）用于查询表结构信息 [^2]: `USER_TRIGGER_COLS` 仅记录显式指定的触发字段 [^3]: 使用关键字作为列名需始终用双引号包裹且大小写敏感 [^4]: 触发器代码存储在 `USER_SOURCE` 中，需逐行扫描匹配 [^5]: TDE 加密列需配置 Oracle Wallet 文件

Oracle 12C可以使用DATEDIFF函数来计算两个日期之间的天数。DATEDIFF函数可以接受三个参数，第一个是开始日期，第二个是结束日期，第三个是日期间隔单位（DAY，MONTH，YEAR）。例如，要算出一个月内的天数，可以使用：DATEDIFF(DAY, start_date, end_date, 'MONTH')