Quoting string literals in 10g

最新推荐文章于 2024-10-17 18:58:27 发布

cubu8303

最新推荐文章于 2024-10-17 18:58:27 发布

阅读量107

点赞数

文章标签：数据库 php

在插入数据时，用select ……for update操作，报错“ORA-01480: trailing null missing from STR bind value” 仔细观察，发现是插入的数据里面含有 ’ 及 || 等特殊字符需要转义，考虑到文本太长，转义太麻烦，有种简便方法，官方解释如下：

http://www.oracle-developer.net/display.php?id=311

quoting string literals in 10g

This short article introduces Oracle's new quoting mechanism in PL/SQL. This is a new feature of 10g that enables us to embed single-quotes in literal strings without having to resort to double, triple or sometimes quadruple quote characters. This is particularly useful for building dynamic SQL statements that contain quoted literals.

The mechanism is invoked with a simple "q" in PL/SQL only. The syntax is q'[...]', where the "[" and "]" characters can be any of the following as long as they do not already appear in the string.

Note that at the time of writing, the quoting mechanism only appears to work with 10g clients/OCI. If used with any software that uses an older Oracle client it fails with ORA-01756: quoted string not properly terminated (confirmed with sqlplus, TOAD and PL/SQL Developer).

a simple example

The following example demonstrates how simple it is to use this mechanism when building a string that contains single quotes.

SQL> DECLARE
  2     v VARCHAR2(1024);
  3  BEGIN
  4     v := q'[It's a string with embedded quotes...]';
  5     DBMS_OUTPUT.PUT_LINE(v);
  6  END;
  7  /
It's a string with embedded quotes...

PL/SQL procedure successfully completed.

using the quoting mechanism in dynamic sql

Many developers will be familiar with dynamic SQL. While providing a flexible solution to many programming problems, dynamic SQL can also be difficult to build, debug and support. The quoting mechanism doesn't make these problems go away, but it at least makes dynamic SQL a little bit easier to transfer between SQL editors and packages during development.

The following is a contrived example of a dynamic SQL statement that includes literal quotes in the string. The sharp-witted amongst us will notice that this statement doesn't in fact need to be dynamic (and even if it did, it should be using bind variables). However, in the interest of demonstrating the quoting mechanism in as simple and short an example as possible, we'll stick with it.

SQL> DECLARE
  2     v_sql VARCHAR2(1024);
  3     v_cnt PLS_INTEGER;
  4  BEGIN
  5     v_sql := q'[SELECT COUNT(*) FROM user_objects WHERE object_type = 'TABLE']';
  6     EXECUTE IMMEDIATE v_sql INTO v_cnt;
  7     DBMS_OUTPUT.PUT_LINE(
  8        TO_CHAR(v_cnt) || ' tables in USER_OBJECTS.'
  9        );
 10  END;
 11  /
4 tables in USER_OBJECTS.

PL/SQL procedure successfully completed.

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

转载于:http://blog.itpub.net/24387280/viewspace-694015/