handle special characters in PL/SQL procedures.?

问题:

I'm using oracle 10g plsql procedures for inserting and listing, but if we have any special characters like ' (single quote ) and & etc. query fails. How to handle special characters in plsql?

before:

    lquery := 'select count(id) into lCount
                        From
                            dual
                        where
                            name = '||iName||'
                         and Id= '||iId

after:

     select into lCount
                        From
                            dual
                        where
                            Id= iId
                         and name = iName;

after changing the query its working fine. Problem is if we keep variable like name value inside single quotes some times query wont execute for special characters like ' , " etc after changing query its working fine.

回答1:

First how to handle a quote ' and an ampersand &:

SQL@xe> set define off
SQL@xe> select q'(foo's & bar's)' from dual;

Q'(FOO'S&BAR'
-------------
foo's & bar's

SQL@xe>

See also How do I ignore ampersands in a SQL script running from SQL Plus? and Text Literals for details of alternative quoting mechanism q''.

Second don't create SQL statements as strings but instead use PL/SQL Static SQL. Static SQL will handle the quoting automatically for you (and is also SQL injection safe). Like:

declare
  lCount number;
  iName varchar2(20) := q'(foo's & bar's)';
  iId number := 42;
begin
  select count(*) into lCount From dual where name = iName and Id= iId;
end;