参考网址: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