Oracle中如何插入&

Before I give the answers, I’ll explain the problem fully. By default, SQL*Plus will scan each line of input and look for an & character. Upon finding it, SQL*Plus will scan the characters after the ampersand and use those as a variable name (the variable name in the above example is Vivek). SQL*Plus will then prompt the user for a value for Vivek—like this:



SQL> insert into test (name)
values ('&Vivek');
Enter value for vivek: Hello World
old   1: insert into test (name)
values ('&Vivek')
new   1: insert into test (name)
values ('Hello World')

1 row created.



Here you can see how SQL*Plus turned &Vivek into Hello World. Now the question is how to stop it from doing that. The easiest method is simply to issue the SQL*Plus set define off command:



SQL> set define off
SQL> insert into test (name)
values ('&Vivek');
1 row created.

SQL> select * from test;

NAME
———————————————————————
Hello World
&Vivek



That prevents SQL*Plus from scanning the input to try to find the substitution character. Another approach is to use a different substitution character:



SQL> set define @

SQL> insert into test (name)
values ( '&Vivek @X' );
Enter value for x: this was x
old   1: insert into test (name)
values ( '&Vivek @X' )
new   1: insert into test (name)
values ( '&Vivek this was x' )

1 row created.



In this case, the @ character is doing what the & used to do.

There are other approaches, such as avoiding the & character in your SQL:



SQL> insert into test
values (chr(38)||'Vivek  xxx');

1 row created.

SQL> select * from test
where name like '% xxx';

NAME
—————————————————————————
&Vivek  xxx



Although that approach works, I’m not a fan of it, because you have to change your SQL statement.

Yet another approach is to use a zero-length substitution variable name, which will make SQL*Plus just leave that & character alone:



SQL> insert into test
values ('&'||'Vivek yyy');

1 row created.

SQL> select * from test
where name like '% yyy';

NAME
—————————————————————————
&Vivek yyy

转自: