牛肉烧板黎

好好写SQL

0%

聊聊PL/SQL条件编译

发表于 分类于

      说说PL/SQL一个比较冷门但是很强大的东西——条件编译，顾名思义，条件编译就是根据不同的条件编译PL/SQL应用而无需移除源码中的文本。比如，根据不同的数据库版本，编译不同的源码；在开发环境中用于debug的语句，当在生产环境部署时不编译该部分语句。

预处理控制指令

      预处理控制指令的基本模式为$plsql_identitier，用于在PL/SQL编译之前处理PL/SQL源码。其指令如下：

  • $IF
  • $THEN
  • $ELSE
  • $ELIF
  • $ERROR

通过这些指令我们就可以完成编译的选择以及错误指令，基本语法如下：

1
2
3
4
5
6
7
8
9
10
$IF boolean_static_expression $THEN
   text
[ $ELSIF boolean_static_expression $THEN
   text
   $ERROR 'unsupported database release' $END
]...
[ $ELSE
   text
$END
]

查询指令

      既然是条件编译，那么很关键的一点就是如何获取编译环境的信息，这就是查询指令的作用。

预定义查询指令

      查询指令的基本语法为：$$name。预定义的name如下：

  • $$PLSQL_LINE：值为其出现的PL/SQL单元的行数。
  • $$PLSQL_UNIT：值为该PL/SQL单元的名字。
  • $$PLSQL_UNIT_OWNER：值为该编译单元所属用户，对于匿名PL/SQL块，值为NULL。
  • $$PLSQL_UNIT_TYPE：值为该PL/SQL单元的类型，ANONYMOUS BLOCK, FUNCTION, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, 或者 TYPE BODY。
  • $$plsql_compilation_parameter： PL/SQL编译参数，PLSCOPE_SETTINGS、PLSQL_CCFLAGS、PLSQL_CODE_TYPE、PLSQL_OPTIMIZE_LEVEL等。

需要注意的是查询指令只能与NULL进行比较。下面是一个简单的示例：

1
2
3
4
5
6
7
8
9
10
11
SQL> CREATE OR REPLACE PROCEDURE p IS
  2    i PLS_INTEGER;
  3  BEGIN
  4    i := $$PLSQL_LINE;
  5    DBMS_OUTPUT.PUT_LINE('i = ' || i);
  6    DBMS_OUTPUT.PUT_LINE('$$PLSQL_LINE = ' || $$PLSQL_LINE);
  7    DBMS_OUTPUT.PUT_LINE('$$PLSQL_UNIT = ' || $$PLSQL_UNIT);
  8    DBMS_OUTPUT.PUT_LINE('$$PLSQL_UNIT_OWNER = ' || $$PLSQL_UNIT_OWNER);
  9    DBMS_OUTPUT.PUT_LINE('$$PLSQL_UNIT_TYPE = ' || $$PLSQL_UNIT_TYPE);
 10  END;
 11  /

输出：

$$PLSQL_LINE = 4
$$PLSQL_UNIT = 6
$$PLSQL_UNIT_OWNER = CENZHGL
$$PLSQL_UNIT_TYPE = PROCEDURE

为查询指令赋值

      我们可以通过PLSQL_CCFLAGS编译参数为查询指令赋值，其基本模式为key:value，value的值只能是BOOLEAN或者PLS_INTEGER。如下：

1
ALTER SESSION SET PLSQL_CCFLAGS = 'name1:value1, name2:value2, ... namen:valuen'

PLSQL_CCFLAGS可以为ORACLE预定义的查询指令赋值，但是ORACLE并不建议这么做，这样容易让人迷惑。

示例-为不同的数据库版本编译不同的代码

      ORACLE提供了DBMS_DB_VERSION用于在控制指令中处理数据库版本问题。其提供了以下静态常量：

  • VERSION：值为PLS_INTEGER类型，标识当前数据库版本。
  • RELEASE: 值为PLS_INTEGER类型, 标识当前数据库的RELEASE NUMBER。
  • VER_LE_v：一个BOOLEAN的常数值，判断数据库版本是否比v小。
  • VER_LE_v_r： BOOLEAN的常数值，判断数据库版本号是否比v小，release number是否比r小。

oracle列转行中有两个函数：WM_CONCAT和LISTAGG。12c开始，ORACLE摒弃了WM_CONCAT(可通过解锁wmsys用户，然后创建包、包体、函数和同义词使其在12c中正常使用)，推荐使用LISTAGG。LISTAGG是在11G2中才开始提供了。那么我们为了在11g2之后使用LISTAGG，之前的版本使用WM_CONCAT,条件编译就可以帮助我们同一份代码在不同的数据库版本中有不同的实现。

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE PROCEDURE Comp_dif_db_version IS
BEGIN
	WITH TMP AS
	(SELECT regexp_substr('A,B,C,D,E,F,G','[^,]+',1,LEVEL) cr FROM DUAL
		CONNECT BY REGEXP_INSTR('A,B,C,D,E,F,G','[^,]+',1,LEVEL)>0);
		
	$IF DBMS_DB_VERSION.VER_LE_11_2 $THEN
		SELECT WM_CONCAT(CR) FROM TMP;
	$ELSE
		SELECT LISTAGG(CR,',') WITHIN GROUP(ORDER BY 1) FROM TMP;
	$END
END;
/

执行CALL DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE('PROCEDURE','CENYLFW','COMP_DIF_DB_VERSION');命令可以看到结果如下：

1
2
3
4
5
6
7
8
9
10
PROCEDURE Comp_dif_db_version IS
   RESULT_STR VARCHAR2(500);
BEGIN
  WITH TMP AS
   (SELECT regexp_substr('A,B,C,D,E,F,G', '[^,]+', 1, LEVEL) cr
      FROM DUAL
    CONNECT BY REGEXP_INSTR('A,B,C,D,E,F,G', '[^,]+', 1, LEVEL) > 0)
  SELECT LISTAGG(CR, ',') WITHIN GROUP(ORDER BY 1) INTO RESULT_STR FROM TMP;
 
END;

示例-区分开发环境与生产环境

      在做PL/SQL程序开发的时候，有时候我们会在程序中使用DBMS_OUTPUT.PUT_LINE等语句来调试追踪程序的执行，但是在生产环境中不屏蔽这些语句有可能会导致一些问题，手动去删除源码不仅麻烦还不能保证所有都删除。这时候我们可以使用条件编译，分别在生产环境和开发环境编译不同代码。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
ALTER SESSION SET PLSQL_CCFLAGS = 'dev_env:TRUE';

create or replace procedure PRC_UPDATE_BRWYID is
  sql_script clob;
  I_LOG      INTEGER;
begin
  FOR TABLES IN (SELECT TABLE_NAME
                   FROM USER_TAB_COLS A
                  WHERE A.column_name = 'BRWYID') LOOP
    begin
      sql_script := 'UPDATE CENYLFW.' || TABLES.TABLE_NAME ||
                    ' A SET BRWYID=NVL((SELECT BRWYID FROM CENYLFW.TB_YLFW_BRWYID_UPDATE B WHERE A.GRBSH = B.KH AND A.GRBSLX = B.KLX AND A.YLJGDM=B.YLJGDM),''-1'')';
					
	  $IF $$dev_env $THEN
		DBMS_OUTPUT.PUT_LINE(sql_script);
	  $END
	  
      EXECUTE IMMEDIATE SQL_SCRIPT;
      COMMIT;
      I_LOG := CENZHGL.fun_write_job_log('finish update ' ||
                                         TABLES.TABLE_NAME || '.BRWYID!');
    exception
      when others then
        I_LOG := CENZHGL.fun_write_job_log('update ' || TABLES.TABLE_NAME ||
                                           ' BRWYID faild!:->' || Sqlcode || '->' ||
                                           substrb(Sqlerrm, 0, 500));
    end;
  END LOOP;
end PRC_UPDATE_BRWYID;
/

执行CALL DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE('PROCEDURE','CENYLFW','COMP_DIF_DB_VERSION');即可看到编译后的结果。有了条件编译，我们就不需要在开发的时候添加调试代码，在开发环境的时候又去部署。

总结

      合理利用条件编译可以为我们生产开发带来很大的方便。它可以让我们方便的在新版本的数据库中使用新的特性，在旧的数据库中屏蔽这些新的特性，只需要在编码时多加几句控制指令即可，避免了去增删源码而可能产生的BUG。我们也可以使用PLSQL_CCFLAGS编译参数来为查询指令赋值，用来区分开发环境和生产环境，在生产环境中屏蔽开发环境中的调试语句。