PLSQL:Oracle SQL 分隔符技巧

Oracle SQL 分隔符技巧
很久没有发表真正具有技术价值的文章了，这篇勉强合格。

经典报表类型：返回 SQL 查询的函数
Oracle 有个很棒的经典报表类型 "Function Returning a SQL Query"，允许你编写返回 SELECT 语句的代码块。这个 SELECT 语句就是报表的基础。我经常用它来简化 WHERE 子句的构建。看起来可能像这样：

declare
   l_q varchar2(32767); -- 这是返回变量，它将保存一个 SELECT 语句
begin
   l_q := 'select d.name department, e.name employee
   from dept d, emp e
   where d.deptno = e.deptno ';
   if :P1_DEPTNO is not null then
      l_q := l_q || ' and d.deptno = :P1_DEPTNO ';
   end if;
   -- 插入更多像上面这样的 if 语句
   return l_q;
end;
虽然交互式报表的出现使这种情况减少，但仍然会用到。

更复杂的查询案例
有时候查询会更复杂一些，像这样：

begin
   l_q := 'select d.name department, e.name employee,
   to_date(to_char(e.hiredate,''yyyymm'' ||''01''),''yyyymmdd'') first_day_of_month
   from dept d, emp e
   where d.deptno = e.deptno
   and e.status in (''NEW'', ''PENDING'', ''FOO'')
   and d.status = ''NEW'' ';
   if :P1_DEPTNO is not null then
      l_q := l_q || ' and d.deptno = :P1_DEPTNO ';
   end if;
   -- 插入更多像上面这样的 if 语句
   return l_q;
end;
可以看到，有很多字符串和双引号（''）。不过有个更简单的方法：自定义 SQL 分隔符。

自定义 SQL 分隔符
通常单引号（'）表示字符串的开始。如果你在里面使用引号，需要用两个引号（''）来转义（见上面的代码）。但是你可以通过自定义分隔符来避免这种情况。

你只需要用 q' 来开启它，就是字母 q 后面加一个引号。关键在于接下来第一个字符就是分隔符，有几个特殊字符需要注意。用分隔符加引号来结束语句。举例最容易理解，以下是几个例子：

l_q := q'! I don't need to escape the ' in the word don't !';
在这个例子中，! 是特殊字符。q'! 开始字符串，!' 结束它。

l_q = q'{ I don't need to escape the ' in the word don't }';
这个有点不同。如果你的特殊字符是 {，那么就用 }' 来关闭它。比较明显的特殊字符对有：[], {}, <>。

所以上面的代码可以改写为：

begin
   l_q := q'{ select d.name department, e.name employee,
   to_date(to_char(e.hiredate,'yyyymm' ||'01'),'yyyymmdd') first_day_of_month
   from dept d, emp e
   where d.deptno = e.deptno
   and e.status in ('NEW', 'PENDING', 'FOO')
   and d.status = 'NEW' }';
   if :P1_DEPTNO is not null then
      l_q := l_q || ' and d.deptno = :P1_DEPTNO ';
   end if;
   -- 插入更多像上面这样的 if 语句
   return l_q;
end;
你可以在 SQL 或 PL/SQL 中几乎任何需要字符串的地方使用这个方法。

　　测试环境： oracle database 19c 测试通过 。

oracle database 19c 测试通过:
-- Created on 2026/4/16 by  Author
declare
-- Local variables here
i integer;
V_SQL VARCHAR2(32767);
begin
-- Test statements here
V_SQL := q'! select 'quota() ' as qstr ,
2026 as "YEAR"
from dual !';
dbms_output.put_line(v_sql );
end;

 

来源于网络文摘