处理PL/SQL过程中的特殊字符. [英] handle special characters in PL/SQL procedures.?

问题描述

我正在使用oracle 10g plsql过程进行插入和列出，但是如果我们有任何特殊字符，例如'(单引号)和&等，则查询失败.如何在plsql中处理特殊字符?

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?

之前:

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

之后:

     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.

推荐答案

首先，如何处理引号'和&符号&:

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>

另请参见如何在从SQL Plus运行的SQL脚本中忽略与号?和

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''.

第二，不要将SQL语句创建为字符串，而是使用 PL/SQL静态SQL .静态SQL将自动为您处理报价(并且SQL注入安全).喜欢:

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;

这篇关于处理PL/SQL过程中的特殊字符.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！