oracle插入特殊字符

今天在导入一批数据到Oracle时，碰到了一个问题：Toad提示要给一个自定义变量target赋值，一开始我很纳闷，数据是一系列的Insert语句，怎么会有自定义变量呢？后来搜索了一下关键字target发现，原来是因为在插入数据中有一个字段的内容如下：

http://124.207.254.169:8080/m400/initRfAdd.action?rf.category=&target=blk;[e-mail];[e-mail]1

Oracle把这里的URL的参数连接符⌖当成是一个自定义变量了，所以要求我给变量target赋值。怎么办呢？方法有三：

·方法一：在要插入的SQL语句前加上Set define off;与原SQL语句一起批量执行

  我们在SQL*PLUS下执行 SQL> show define;命令时，显示下列结果：
  define "&" (hex 26)
  这个是Oracle里面用来识别自定义变量的设置，现在我们在SQL*PLUS下将其关闭：

  SQL> set define OFF;

  然后再在SQL*PLUS执行导入脚本，OK！问题搞定。

  执行完毕之后，重新设置set define ON。

·方法二：在SQL语句中将'&'替换成chr(38)，因为chr(38)是‘&’的ASCII码

   SQL> Select 'Tom' || chr(38) || 'Jerry' from dual;

·方法三：分拆原来的字符串

   SQL> Select 'Tom' || '&' || 'Jerry' from dual;


我们可以看到，方法一最为简便，而且效率也最高。方法二因为有一个调用函数的过程，所以性能稍差。方法三需要两次连接字符串，效率最差！


那么如果字段的内容中包含了单引号要怎么插入呢？例如：It's fine。方法同样有三

·方法一：使用转义字符

   SQL > Select 'test' || '''' from dual;

   注意：这里的''''四个单引号是什么意思呢？首先第一个和最后一个都是Oracle中的字符串连接符，这个没有异议。那么第二个'和第三'又表示什么意思呢？第二个'是一个转义字符
           第三个'才是我们真正的内容

·方法二：同样是使用转义字符，只不过方式不同而已

   SQL > Select 'test ''' from dual;

   注意：这里的第二个，第三个'就是我们上面方法一中提到的转义符和真正的内容

·方法三：在SQL中将'替换成chr(39)，因为chr(39)是'的ASCII码

   SQL > Select 'It' || chr(39) || 'fine' from dual;

查找字母对应的ASCII码：
select ascii('&') from dual;
select chr(38)    from dual;

也可以改变自定义变量的连接符：
set define '*';

在对define进行设置完成插入时，也可以使用escape来实现。
SQL> show  escape;
escape OFF
SQL> set escape on;
SQL> show escape;
escape "\" (hex 5c)
SQL> insert into t values(1,'this is a \&test');

已创建 1 行。

SQL> select * from t;

        T1 T2
---------- -----------------
         1 this is a &test

评论

1 楼 txlaijava 2010-06-04  

感谢分享咯. ~