转载注明出处 ，原文地址 ：http://hweilei.blog.hexun.com/33999277_d.html
如不同意转载，请告之。
单引号双重角色——字符串引用与转义小结
在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博客 ” ，链接：https://blog.itpub.net/10754174/viewspace-610780/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
