REPLACEF

参考网址:http://www.oracle-developer.net/content/utilities/replacef.sql

A function to simplify string building and debugging by replacing multiple placeholders from a collection of inputs.

定义集合的类型:

1 CREATE OR REPLACE TYPE replacef_ntt AS TABLE OF VARCHAR2(4000);

函数:

 1 CREATE OR REPLACE FUNCTION replacef(p_msg  IN VARCHAR2, --SQL语句
 2                                     p_args IN replacef_ntt DEFAULT replacef_ntt(), --要替换的参数集合
 3                                     p_plc  IN VARCHAR2 DEFAULT '%s') --默认的替代符
 4  RETURN VARCHAR2 IS
 5 
 6   v_msg  VARCHAR2(32767) := p_msg; --SQL语句
 7   v_args PLS_INTEGER := least((length(v_msg) -
 8                               length(REPLACE(v_msg, p_plc))) *
 9                               length(p_plc),
10                               p_args.count);
11   --替代符的个数:需要根据SQL语句和传递的集合计算出具体的需要替换的个数，
12   --取她们中的小的，作为替换的个数
13   /**
14    与least函数相对应的含有greaste函数
15   **/
16   v_pos PLS_INTEGER; --位置变量
17 
18 BEGIN
19 
20   FOR i IN 1 .. v_args LOOP
21     v_pos := instr(v_msg, p_plc);
22     --dbms_output.put_line(v_pos);
23     --拆分为两半:前一半进行替换后和后一半拼接上去
24     v_msg := REPLACE(substr(v_msg, 1, v_pos + length(p_plc) - 1),
25                      p_plc,
26                      p_args(i)) || substr(v_msg, v_pos + length(p_plc));
27     --dbms_output.put_line(substr(v_msg, 1, v_pos + length(p_plc) - 1));
28     --dbms_output.put_line(p_args(i) || substr(v_msg, v_pos + length(p_plc)));
29     --dbms_output.put_line(v_msg);
30   END LOOP;
31 
32   RETURN v_msg;
33 
34 END replacef;

测试1:

1 DECLARE
2   v_sql   VARCHAR2(128);
3   nt_args replacef_ntt := replacef_ntt();
4 BEGIN
5   v_sql   := 'ALTER TABLE %s.%s TRUNCATE PARTITION %s';
6   nt_args := replacef_ntt('table_owner', 'table_name', 'partition_name');
7   dbms_output.put_line(replacef(v_sql, nt_args));
8 END;

结果:ALTER TABLE table_owner.table_name TRUNCATE PARTITION partition_name

测试2:

1 SELECT replacef('ALTER TABLE %s.%s TRUNCATE PARTITION %s',
2                 replacef_ntt('table_owner', 'table_name', 'partition_name')) results
3   FROM dual

测试:3

1 DECLARE
2   v_sql   VARCHAR2(128);
3   nt_args replacef_ntt := replacef_ntt();
4 BEGIN
5   v_sql   := 'ALTER TABLE %s.%s TRUNCATE PARTITION %s';
6   nt_args := replacef_ntt('table_owner', 'table_name', 'partition_name');
7   v_sql   := replacef(v_sql, nt_args);
8   EXECUTE IMMEDIATE v_sql;--动态执行结果
9 END;

--end