插入"&"特殊字符的几种思考

一位铁杆朋友，今天问了个问题，写了一个Python程序，从文件读取数据，其中可能包含“&”这种特殊字符，为了让其能插入Oracle，需要做什么处理？

首先，我想问的问题，"&"字符在插入数据库的时候有什么特别之处？

在sqlplus下执行如下SQL，向表A插入记录“a&a”，他会提示我们“Enter value for a:”，输入变量a的值，此处输入空，则实际存储的，就是"a"，

SQL> create table a (id number, a varchar2(25));
Table created.


SQL> insert into a values(1, 'a&a');
Enter value for a:
old   1: insert into a values(1, 'a&a')
new   1: insert into a values(1, 'a')


1 row created.


SQL> select * from a;
       ID   A
---------- -------------------------         
        1   a

执 行show all，可以看到这行define "&" (hex 26)，其实这个就是Oracle中用来识别自定义变量的设置，当SQL中包含"&"时，Oracle就会认为你要输入变量了，就像上面显示的 “Enter value for a:”，

SQL> show all
...
define "&" (hex 26)

在sqlplus下，可以设置set define off，此时就关闭了自定义变量，他会将"&"看作是普通字符，直接使用"a&a"，不会提示“Enter value for a:”，

SQL> set define off


SQL> insert into a values(1, 'a&a');
1 row created.

但是在python程序中，使用cx_Oracle连接Oracle数据库，并不支持执行set define off，原因就是cx_Oracle只支持标准SQL语句，这个set define off只是在sqlplus下才能执行的语句，

The command "set define off" is not a SQL statement, but a SQLPlus statement. As such, it can only be executed in SQLPlus. Since cx_Oracle only processes SQL statements, this command is unnecessary in any case! If you are reading from a file that contains SQL statements that are normally run by SQL*Plus you'll need to filter these statements out.

当然，你可以使用shell脚本，执行sqlplus就可以用set define off了，但是受限于实际，不能将Python程序转为shell。

除此之外，还可以怎么做？

我们能得到"&"的ASCII码是38，

SQL> select ascii('&') from dual;
ASCII('&')
----------
    38

我们使用chr(38)，代替"&"，通过拼接字符串，就可以插入了，

SQL> insert into a values(1, 'a'||chr(38)||'a');
1 row created.


SQL> select * from a;
        ID A
---------- -------------------------
         1 a&a

但是问题来了，Python程序可以读取行数据的时候，将"&"替换为'||chr(38)||'，但是这只是一般的情况，当出现"&&"，或者多个"&"的时候，可能要特殊的判断逻辑，才可以替换正确，有些复杂。

有其他的方法么？

我们换种思维，"&"字符直接入库，Oracle会将其认为是有特殊含义的，如果插入的不是"&"，就可以解决了？

步骤如下，

1.Python读取行数据时，将字符串中"&"替换为其他的字符，例如"#"，或者不可见字符(避免待替换字符就存在于字符串中)。

2.插入Oracle时，将"#"，replace替换为"&"，如下所示，

SQL> insert into a values(1, replace('a#c', '#', chr(38)));
1 row created.


SQL> select * from a;
        ID A
---------- -------------------------
         1 a&c

这个方案就比较耐看了，一方面，是无需关注原始字符串中，到底存在几个连续的"&"，要特殊的判断，另一方面，插入的时候，replace函数能通用，无需针对不同的输入选择特殊的逻辑，一个词概括，就是“通用”。

参考：

https://stackoverflow.com/questions/57470276/unable-to-set-define-off-in-cx-oracle

http://blog.itpub.net/20750200/viewspace-706760/

https://blog.csdn.net/dreary001/article/details/84355504