Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1354169
  • 博文数量: 239
  • 博客积分: 5909
  • 博客等级: 大校
  • 技术积分: 2715
  • 用 户 组: 普通用户
  • 注册时间: 2010-07-24 20:19
文章分类

全部博文(239)

文章存档

2014年(4)

2013年(22)

2012年(140)

2011年(14)

2010年(59)

我的朋友

分类: Oracle

2012-11-07 18:17:14


点击(此处)折叠或打开

  1. 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:

  2.  

  3. SQL> insert into test (name)
  4. values ('&Vivek');
  5. Enter value for vivek: Hello World
  6. old 1: insert into test (name)
  7. values ('&Vivek')
  8. new 1: insert into test (name)
  9. values ('Hello World')

  10. 1 row created.

  11.  

  12. 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:

  13.  

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

  18. SQL> select * from test;

  19. NAME
  20. ———————————————————————
  21. Hello World
  22. &Vivek

  23.  

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

  25.  

  26. SQL> set define @

  27. SQL> insert into test (name)
  28. values ( '&Vivek @X' );
  29. Enter value for x: this was x
  30. old 1: insert into test (name)
  31. values ( '&Vivek @X' )
  32. new 1: insert into test (name)
  33. values ( '&Vivek this was x' )

  34. 1 row created.

  35.  

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

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

  38.  

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

  41. 1 row created.

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

  44. NAME
  45. —————————————————————————
  46. &Vivek xxx

  47.  

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

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

  50.  

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

  53. 1 row created.

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

  56. NAME
  57. —————————————————————————
  58. &Vivek yyy
转自:
阅读(1268) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~