PL/SQL编程方法大全

一、动态SQL的拼接(使用replace)

注意使用replace替换达到动态SQL拼接的效果,v_psql可做变量传递

  1  declare
  2  a number:=2;
  3  v_sql varchar2(4000):='select ename from #';
  4  v_psql varchar2(2000):='emp where rownum='||2||' ';
  5  begin
  6  dbms_output.put_line(v_psql);
  7  v_sql:=replace(v_sql,'#',v_psql);
  8  dbms_output.put_line(v_sql);
  9* end;
SQL> /
emp where rownum=2
select ename from emp where rownum=2

PL/SQL 过程已成功完成。

二、通用的分隔函数(VARCHAR2版本)

create or replace function split

(

   p_string varchar2,

   p_sep varchar2 := ','  --默认按逗号分隔

)  return type_split pipelined --pipelined关键字指定返回结果集使用管道输出,输出为表类型

 is

   v_l  pls_integer;

   v_string  varchar2(30000) := p_string;

begin

   loop
      v_l := instr(v_string,p_sep);

      if v_l > 0 then

          pipe row(substr(v_string,1,v_l-1)); --使用管道函数

          v_string := substr(v_string,v_l+length(p_sep));

      else

          pipe row(v_string);

          exit;

      end if;

   end loop;

   return;

end split;

用之前记得创建一个PL/SQL表类型用来保存输出的结果：

create or replace type type_split is table of varchar2(1000)

用法:


select * from table(split('12,aa,qwwq,',','));

结果

COLUMN_VALUE

12

aa

qwwq

NULL

通用的分隔函数(CLOB版本):

CREATE OR REPLACE FUNCTION splitCLOB(p_string    IN CLOB,
                                     p_delimiter IN VARCHAR2)
  RETURN type_split
  PIPELINED AS
  v_length NUMBER;
  v_start  NUMBER := 1;
  v_index  NUMBER;
BEGIN
  v_length := dbms_lob.getlength(p_string);
  WHILE (v_start <= v_length) LOOP
    v_index := dbms_lob.instr(p_string, p_delimiter, v_start);
  
    IF v_index = 0 THEN
      PIPE ROW(dbms_lob.substr(p_string, v_length - 1, v_start));
      v_start := v_length + 1;
    ELSE
      PIPE ROW(dbms_lob.substr(p_string, v_index - v_start, v_start));
      v_start := v_index + 1;
    END IF;
  END LOOP;

  RETURN;
END splitCLOB;

用法同上,唯一要注意的是要建立CLOB数组来保存输出：

create or replace type type_split is table of CLOB

三、取得当前函数或存储过程的名称(一般用于错误处理)

CREATE OR REPLACE FUNCTION f_getprocname RETURN VARCHAR2
--获取函数或者存储过程自身的名称及调用者
 IS
  l_owner  VARCHAR2(30);
  l_name   VARCHAR2(30);
  l_lineno NUMBER;
  l_type   VARCHAR2(30);
BEGIN
  OWA_UTIL.who_called_me(l_owner, l_name, l_lineno, l_type);
  RETURN l_owner || '.' || l_name;
END;

用法

create or replace procedure p_test

as

EX_P01 EXCEPTION;

...

begin

...

EXCEPTION
  WHEN EX_P01 THEN

  RAISE_APPLICATION_ERROR(-20001,
                            '在' || f_getprocname || '中出现错误:('||sqlcode||')--'||sqlerrm||',请检查!');

...

end  p_test;