ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [转]oracle 单引号双重角色

[转]oracle 单引号双重角色

原创 Linux操作系统 作者:dowsonvx 时间:2009-07-30 08:22:02 0 删除 编辑

转载注明出处 ,原文地址 :http://hweilei.blog.hexun.com/33999277_d.html

如不同意转载,请告之。

单引号双重角色——字符串引用与转义小结

本人在做项目维护的时候,遇到了开发人员遗留下来的大量动态SQL,很是发了一段时间来弄明白单引号的双重角色。现做如下总结,即使在各位高手面前班门弄斧,由希望能给与我同样被单引号双重角色迷惑的同仁们有所帮助!
  在oracle中,字符串是由单引号引用的,而转义也是由单引号来完成的,这对于许多初学者而言是件头疼的事情,尤其是开始学习动态SQL的人。如果就单纯的单引号而言,很容易让人理解:它总是找与它最近的一个单引号配对。也就是所谓的就近原则;但是在单引号充当转义角色的时候,情况就显得有些让人费解了。(为方便起见,红色单引号都是充当转义角色)
  1、从第二个单引号开始被视为转义符,如果第二个单引号后面还有单引号(哪怕只有一个)。 
PHP code:



[/COLOR]


SQL> select '''' from dual;





''


--



为了证实结果是被第二个单引号转义的第三个单引号(既:select '''' from dual;),我们做如下两个测试: 
PHP code:



SQL> select '' from dual;


ERROR:


ORA-01756: 括号内的字
相信大家对这样的错误不陌生吧 
PHP code:



SQL> select sdlf from dual;


ERROR:


ORA-01756: 括号内的字
也就是说,当第二个单引号充当转义角色,第三个单引号被转义,(既:select '' ' from dual;)自然就缺少与第一个单引号匹配的单引号了,出现了孤立的单引号
下面的两个实验就更加支持了上面的结论。 
PHP code:



SQL> select ' '' ' from dual;





''''


------


 '





SQL> select '' '' from dual;


select '' ' from dual


  *


ERROR 位于第 1 行:


ORA-00923: 未找到预期 
对于第一个:select ' '' ' from dual;
对于第二个:不存在转义。

上面的这两个实验其实也是单引号转义与非转义的一个性质:转义是密集的,也就是说,如果单引号出现在转义的位置上,而该单引号后面紧跟(紧跟的定义为:两个单引号之间是零距离的)的不是单引号,这个时候单引号就不在充当转义的角色,而是与它前面的配对。

2、连接符‘||’导致了新一轮的转义:连接符号‘||’左右的单引号没有任何的关系,除非‘||’是作为字符串的一部分(这在动态SQL中很常见)。
PHP code:



SQL> select 'exit'||'''' from dual;





'EXIT'||''


----------


exit'





SQL> select 'exit''''' from dual;





'EXIT'''''


------------


exit'
对于第一个,前两个单引号配对,后面四个单引号按照上面的第一条原则分配,既:select 'exit'||'''' from dual;
对于第二个,由于第二个单引号后面存在单引号,所以就不与第一个配对,而是充当了转义的角色。既:select 'exit''''' from dual;

有了上面的两条原则,我们就可以从容的面对复杂的动态SQL了。
以下为举例:
1) 
declare
 v_sql varchar2(1000);
 v_sqlnum number;
 v_name varchar2(100);
begin
 v_name:='小胡';
 dbms_output.put_line('Testing is beging !');
 v_sql := 'update vv set name= ''小义''where name='''||v_name||'''';---注意引号的打法
 execute immediate v_sql;
 if sql%notfound then 
 dbms_output.put_line('This is question:' ||sql%rowcount);
end if ;
commit;
end ;
2) 
declare
 v_sql varchar2(1000);
 v_sqlnum number;
 v_name varchar2(100);
 v_id integer;
begin
 v_id:=1;
 dbms_output.put_line('Testing is beging !');
 v_sql := 'update vv set name= ''小李''where id='||v_id;---注意引号的打法
 execute immediate v_sql;
 if sql%notfound then 
 dbms_output.put_line('This is question:' ||sql%rowcount);
end if ;
commit;
end ;
3)
Declare
v_sql Varchar2(1000);
v_id v.id%Type;
v_names v.Names%Type;
v_age v.Age%Type;
v_sal v.Sal%Type;
v_sall v.Sall%Type;
v_rowtype v%Rowtype;
Begin 
 v_names:='小江';
 v_sql:='Select age From v Where names='''||v_names||'''';----注意理解单引号!
 Execute Immediate v_sql Into v_age;
 Dbms_Output.put_line('this is test :'||v_age);
End;
4)

create or replace procedure UPDATE_STAR_TYPE is
  cnt number;
  table_name varchar2(50);
  table_cols varchar2(20);
  v_val varchar2(2);
  sql_str varchar2(1000);
---select *From v;
  cursor table_cdr is
  select distinct T.TABLE_NAME, T.COLUMN_NAME
  from user_tab_cols t
  where t.column_name = 'HWEILEI';

begin
  open table_cdr;
  loop
  fetch table_cdr
  into table_name, table_cols;
   
  sql_str := 'select count(1) from ' || table_name || ' where ' || ------注意理解单引号!
  table_cols || ' in (''lei'',''hu'')';
  execute immediate sql_str
  into cnt;
  if cnt > 0 then
  sql_str := 'update ' || table_name || ' set ' || table_cols ||
  '=decode(' || table_cols ||
  ',''lei'',''1'',''2'') where ' || table_cols ||
  ' in (''lei'',''hu'')';
  execute immediate sql_str;
  commit;
  end if;

  exit when table_cdr%NOTFOUND;
  end loop;
  close table_cdr;
end;
  

特别举例:
1):
Declare 
  v_number Number ;
  v_test Number;
  Begin 
  v_number:=2;
Select decode(v_number,age,3,age) Into v_test From v Where Id=3 ;---注意变量 v_number
Dbms_Output.put_line('this is test :'||v_test);
End ;

2):
Declare 
  v_number Number ;
  v_test Number;
  v_sql Varchar2(1000);
  Begin 
  v_number:=2;
  v_sql:='Select decode('||v_number||',age,3,age) From v Where Id=3';---注意变量 v_number
  Execute Immediate v_sql Into v_test;
Dbms_Output.put_line('this is test :'||v_test);
End ;

3):

第一种写法:

declare 
 v_sql varchar2(1000);
 v_pname varchar2(100);
 v_age number;
 v_sqlnum number;
begin
 select age into v_age from vv where name='小胡';
  v_sqlnum:=sql%rowcount;
 dbms_output.put_line('This is question:'||v_sqlnum);
  dbms_output.put_line('This is question:'||v_age);
end;
第二种写法:

declare 
 v_sql varchar2(1000);
 v_pname varchar2(100);
 v_age number;
 v_sqlnum number;
begin
 v_pname:='小胡';
 dbms_output.put_line('Testing is beging !');
  v_sql:='select age from vv where name=:v_pname';
 execute immediate v_sql into v_age using v_pname;
 v_sqlnum:=sql%rowcount;
 dbms_output.put_line('This is question:'||v_sqlnum);
  dbms_output.put_line('This is question:'||v_age);
end;
第三种:

declare 
 v_sql varchar2(1000);
 v_pname varchar2(100);
 v_age number;
 v_sqlnum number;
begin
 dbms_output.put_line('Testing is beging !');
 v_sql:='select sal from cume where name =''小代''';---注意单引号符号!
 execute immediate v_sql into v_age;
 v_sqlnum:=sql%rowcount;
 dbms_output.put_line('This is question:'||v_sqlnum);
  dbms_output.put_line('This is question:'||v_age);
end;

报错的例子:

declare 
 v_sql varchar2(1000);
 v_pname varchar2(100);
 v_age number;
 v_sqlnum number;
begin
 v_pname:='小胡';
 dbms_output.put_line('Testing is beging !');
 v_sql:='select age from vv where name='||'''v_pname''';----如果这样写了,会报错!

 v_sql:='select age from vv where name=||'''v_pname||'''';
 execute immediate v_sql into v_age; 
 v_sqlnum:=sql%rowcount;
 dbms_output.put_line('This is question:'||v_sqlnum);
  dbms_output.put_line('This is question:'||v_age);
end;

针对报错的,再举一个例:

Declare
v_sql Varchar2(1000);
v_id v.id%Type;
v_names v.Names%Type;
v_age v.Age%Type;
v_sal v.Sal%Type;
v_sall v.Sall%Type;
v_rowtype v%Rowtype;
Begin 
 v_names:='小江';
 v_sql:='Select age From v Where names='''||v_names||'''';
 Execute Immediate v_sql Into v_age;
 Dbms_Output.put_line('this is test :'||v_age);
End;

再一例:

Declare
v_sql Varchar2(1000);
v_id v.id%Type;
v_names v.Names%Type;
v_age v.Age%Type;
v_sal v.Sal%Type;
v_sall v.Sall%Type;
v_rowtype v%Rowtype;
Begin 
 v_sql:='Select age From v Where Id=7';
 Execute Immediate v_sql Into v_age;
 Dbms_Output.put_line('this is test :'||v_age);
End;

 

特别举例:
1):
Declare 
  v_number Number ;
  v_test Number;
  Begin 
  v_number:=2;
Select decode(v_number,age,3,age) Into v_test From v Where Id=3 ;---注意变量 v_number
Dbms_Output.put_line('this is test :'||v_test);
End ;

2):
Declare 
  v_number Number ;
  v_test Number;
  v_sql Varchar2(1000);
  Begin 
  v_number:=2;
  v_sql:='Select decode('||v_number||',age,3,age) From v Where Id=3';---注意变量 v_number
  Execute Immediate v_sql Into v_test;
Dbms_Output.put_line('this is test :'||v_test);
End ;

3):

Declare 
  v_number Number ;
  v_test Number;
  Begin 
  v_number:=2;
Select v_number Into v_test From v Where Id=3 ;
Dbms_Output.put_line('this is test :'||v_test);
End ;

 

第一种写法:

declare 
 v_sql varchar2(1000);
 v_pname varchar2(100);
 v_age number;
 v_sqlnum number;
begin
 select age into v_age from vv where name='小胡';
  v_sqlnum:=sql%rowcount;
 dbms_output.put_line('This is question:'||v_sqlnum);
  dbms_output.put_line('This is question:'||v_age);
end;
第二种写法:

declare 
 v_sql varchar2(1000);
 v_pname varchar2(100);
 v_age number;
 v_sqlnum number;
begin
 v_pname:='小胡';
 dbms_output.put_line('Testing is beging !');
  v_sql:='select age from vv where name=:v_pname';
 execute immediate v_sql into v_age using v_pname;
 v_sqlnum:=sql%rowcount;
 dbms_output.put_line('This is question:'||v_sqlnum);
  dbms_output.put_line('This is question:'||v_age);
end;
第三种:

declare 
 v_sql varchar2(1000);
 v_pname varchar2(100);
 v_age number;
 v_sqlnum number;
begin
 dbms_output.put_line('Testing is beging !');
 v_sql:='select sal from cume where name =''小代''';---注意单引号符号!
 execute immediate v_sql into v_age;
 v_sqlnum:=sql%rowcount;
 dbms_output.put_line('This is question:'||v_sqlnum);
  dbms_output.put_line('This is question:'||v_age);
end;

报错的例子:

declare 
 v_sql varchar2(1000);
 v_pname varchar2(100);
 v_age number;
 v_sqlnum number;
begin
 v_pname:='小胡';
 dbms_output.put_line('Testing is beging !');
 v_sql:='select age from vv where name='||'''v_pname''';----如果这样写了,会报错!

 v_sql:='select age from vv where name=||'''v_pname||'''';
 execute immediate v_sql into v_age; 
 v_sqlnum:=sql%rowcount;
 dbms_output.put_line('This is question:'||v_sqlnum);
  dbms_output.put_line('This is question:'||v_age);
end;

针对报错的,再举一个例:

Declare
v_sql Varchar2(1000);
v_id v.id%Type;
v_names v.Names%Type;
v_age v.Age%Type;
v_sal v.Sal%Type;
v_sall v.Sall%Type;
v_rowtype v%Rowtype;
Begin 
 v_names:='小江';
 v_sql:='Select age From v Where names='''||v_names||'''';
 Execute Immediate v_sql Into v_age;
 Dbms_Output.put_line('this is test :'||v_age);
End;

再一例:

Declare
v_sql Varchar2(1000);
v_id v.id%Type;
v_names v.Names%Type;
v_age v.Age%Type;
v_sal v.Sal%Type;
v_sall v.Sall%Type;
v_rowtype v%Rowtype;
Begin 
 v_sql:='Select age From v Where Id=7';
 Execute Immediate v_sql Into v_age;
 Dbms_Output.put_line('this is test :'||v_age);
End;

 

特别举例:
1):
Declare 
  v_number Number ;
  v_test Number;
  Begin 
  v_number:=2;
Select decode(v_number,age,3,age) Into v_test From v Where Id=3 ;---注意变量 v_number
Dbms_Output.put_line('this is test :'||v_test);
End ;

2):
Declare 
  v_number Number ;
  v_test Number;
  v_sql Varchar2(1000);
  Begin 
  v_number:=2;
  v_sql:='Select decode('||v_number||',age,3,age) From v Where Id=3';---注意变量 v_number
  Execute Immediate v_sql Into v_test;
Dbms_Output.put_line('this is test :'||v_test);
End ;

3):

Declare 
  v_number Number ;
  v_test Number;
  Begin 
  v_number:=2;
Select v_number Into v_test From v Where Id=3 ;
Dbms_Output.put_line('this is test :'||v_test);
End ;

4):
Declare 
  v_number Varchar2(10) ;
  v_test Varchar2(10);
  Begin 
  v_number:='你好';
Select v_number Into v_test From v Where Rownum<2;---- Where Id=3 ;
Dbms_Output.put_line('this is test :'||v_test);
End ;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10754174/viewspace-610780/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-04-01

  • 博文量
    55
  • 访问量
    67346