Oracle 在系统级触发器上反映元组以动态替换感兴趣的字符

Question

我正在尝试编写一个通用触发器，可以检查要插入数据库的每个元组。在此检查中，我想替换我感兴趣的字符，例如用正则引号 (") 替换大引号（MS word）。因为我不想为每列编写两个替换调用，所以存储用户提供的数据，出于多种伪代码的原因，我也不想在应用程序中执行此操作，如下所示：

create or replace trigger changeChars
before insert  
on @tableName  
reference new as var
for each row  
Begin 
loop  
:var.@column  := replace(:var.@column,'”', '"');   
end 

Answer 1

为什么要替换这些角色？许多人对 Microsoft 大引号之类的字符有问题，因为他们的数据库字符集不支持这些字符，默认情况下，这会导致字符集转换将这些字符替换为“？”等替换字符。如果这就是您要解决的问题，数据库代码无济于事，因为字符集转换发生在数据到达数据库之前的网络层。您可能可以使用 Oracle Locale Builder 为您的客户端计算机构建自定义区域设置，从而允许您指定不同的替换字符（即双引号而不是 Microsoft 弯引号）。

如果您的问题与字符集无关，则无法创建这种动态触发器。您可以编写一些动态 SQL，为每个表创建一个触发器，并在表中的每个 VARCHAR2 列上调用 REPLACE。当然，每次添加额外的 VARCHAR2 列时，您都需要维护触发器（通过更改触发器或重新运行 PL/SQL 块）。

未经测试，我确信生成的 DDL 不正确，但您想要类似的东西

DECLARE
  l_sql_stmt VARCHAR2(4000);
BEGIN
  FOR tbl IN (SELECT * FROM user_tables)
  LOOP
    l_sql_stmt := 'CREATE OR REPLACE TRIGGER ' || tbl.table_name || '_changeChars ' ||
                  '  BEFORE INSERT ON ' || tbl.table_name ||
                  '  FOR EACH ROW ' ||
                  'BEGIN ';
    FOR col IN (SELECT * FROM user_tab_cols WHERE table_name = tbl.table_name)
    LOOP
      l_sql_stmt := l_sql_stmt ||
                    ' :new.' || col.column_name || 
                        ' := replace( :new.' || col.column_name || ',''"'', ''"''');';
    END LOOP;
    l_sql_stmt := l_sql_stmt || 
                    'END; ';
    dbms_output.put_line( 'SQL statement = ' || l_sql_stmt );
    EXECUTE IMMEDIATE l_sql_stmt;
  END LOOP;
END;

Answer 2

好的，最简单的就是构建一个函数来进行替换。

create or replace function strip_quotes
    (p_str in varchar2)
    return varchar2
is
begin
    return translate(p_str, chr(145)||chr(146)||chr(147)||chr(148), chr(39)||chr(39)||chr(34)||chr(34));
end;
/

145 到 148 是 MS 智能引号（单引号和双引号）的扩展 ASCII 值。我们可以这样使用它：

SQL> select strip_quotes('ôHiö said Jon.') from dual
  2  /

STRIP_QUOTES('ôHIöSAIDJON.')
-------------------------------------------------------------------------------
"Hi" said Jon.

SQL>

更难的是反射。我同意贾斯汀的观点，为每个表生成一个触发器是最好的解决方案。