functions 示例

示例1：  “，”字符串截取

CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2, p_INDEX IN NUMBER)
    RETURN varchar2
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
    v_ret VARCHAR2(20);
    v_count NUMBER := 1;
BEGIN
    WHILE(v_start <= v_length)
    LOOP
        v_index := INSTR(p_string, p_delimiter, v_start);
        v_ret := substr(p_string,v_start,v_index-1);
        v_start :=  v_index+1;
        IF v_count = p_index THEN
         RETURN v_ret;
        END IF;
        v_count := v_count + 1;
    END LOOP;

    RETURN v_ret;
END splitstr;

示例2：解析分组函数

create or replace function resolve_group_sql(v_sql varchar2,in_flowCode varchar2,
in_nodesCode varchar2, in_workId varchar2, in_userId varchar2) return varchar2 is
/******************************************
       解析分组函数
***************************************/
  Result varchar2(3000);
      v_start  INT;
      v_start2  INT;
      v_count  INT;
      v_count2  INT;
      i_count  INT;
      v_tm_sql varchar2(3000);
      v_tm_str varchar2(3000);
      v_tm_code varchar2(200);
      v_tm_code2 varchar2(200);
      v_value varchar2(30);
begin
      v_tm_sql:=v_sql;
/*      i_count:=0;
      while (instr(v_tm_sql,'{')>0) loop
            v_start:=instr(v_tm_sql,'{')+1;
            v_count:=instr(v_tm_sql,'}')- v_start;
            v_tm_code:=substr(v_tm_sql,v_start,v_count);
            i_count:=i_count+1;

            v_tm_str:=v_tm_code;
            while (instr(v_tm_str,'%[')>0) loop
              v_start2:=instr(v_tm_str,'%[')+2;
              v_count2:=instr(v_tm_str,']')- v_start2;
              v_tm_code2:=substr(v_tm_str,v_start2,v_count2);
              v_value:=get_constant_val(v_tm_code2);
              v_tm_str:=replace(v_tm_str,'%['||v_tm_code2||']',v_value);
            end loop;
            select  v_tm_str into v_value from dual;

           v_tm_sql:=replace(v_tm_sql,'{'||v_tm_code||'}',v_value);

      end loop;*/

        --解析常量
      i_count:=0;
      while (instr(v_tm_sql,'%[')>0) loop
            v_start:=instr(v_tm_sql,'%[')+2;
            v_count:=get_end_pos(v_tm_sql,v_start)- v_start;
            v_tm_code:=substr(v_tm_sql,v_start,v_count);
            v_value:=get_constant_val(v_tm_code);
           v_tm_sql:=replace(v_tm_sql,'%['||v_tm_code||']',v_value);
      end loop;

         --解析接口参数
      i_count:=0;
      while (instr(v_tm_sql,'&[')>0) loop
            v_start:=instr(v_tm_sql,'&[')+2;
            v_count:=get_end_pos(v_tm_sql,v_start)- v_start;
            v_tm_code:=substr(v_tm_sql,v_start,v_count);
            v_value:=get_param_val(v_tm_code,in_flowCode,in_nodesCode,in_workId,in_userId);
           v_tm_sql:=replace(v_tm_sql,'&['||v_tm_code||']',v_value);
      end loop;

      --去掉{}符号
      v_tm_sql:=replace(v_tm_sql,'{','');
      v_tm_sql:=replace(v_tm_sql,'}','');

      Result:=v_tm_sql;
  return(Result);
end resolve_group_sql;

示例3：获取主键id

CREATE OR REPLACE FUNCTION NEXTID(NOID_ IN VARCHAR2) RETURN NUMBER AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  RESULT   NUMBER; --返回结果
  IS_EXIST NUMBER; --是否存在，0 不尊在 1 存在
BEGIN
  -- 判断非空
  IF NOID_ IS NULL OR NOID_ = '' THEN
    RETURN NULL;
  END IF;
  --判断是否存在序列 NOID

  SELECT COUNT(*) INTO IS_EXIST FROM PWP_NO WHERE NOID = NOID_;
  IF IS_EXIST = 1 THEN
    -- 存在主表信息
    SELECT COUNT(*) INTO IS_EXIST FROM PWP_NODTL WHERE NOID = NOID_;
    IF IS_EXIST = 1 THEN
      -- 存在明细表信息，查询需要的结果
      SELECT NEXTID INTO RESULT FROM PWP_NODTL WHERE NOID = NOID_;
      -- 更新nextid为下一个id
      UPDATE PWP_NODTL SET NEXTID = NEXTID + 1 WHERE NOID = NOID_;
    ELSIF IS_EXIST = 0 THEN
      --不存在明细表信息，明细表插入数据，nextid设置为2，返回1
      INSERT INTO PWP_NODTL
        (NOID,
         PREFIX,
         NODAY,
         NEXTID,
         POSTFIX,
         NOINCREMENT,
         NOYEAR,
         NOMONTH)
      VALUES
        (NOID_,
         NULL,
         0,
         2,
         NULL,
         1,
         TO_CHAR(SYSDATE, 'yyyy'),
         TO_CHAR(SYSDATE, 'MM'));

      RESULT := 1;
    END IF;
  END IF;

  -- 提交
  COMMIT;
  --返回
  RETURN(RESULT);
END NEXTID;

示例4：拼接字符串“，”

create or replace function GET_YKJF_YKJHID( v_zbz_bal_id in  number,
                                            v_gk_ykjhsphz_id in number)
  return varchar2 is
  result varchar2(4000);
begin

  if v_zbz_bal_id is not null  and v_gk_ykjhsphz_id is not null  then
    for temp_cursor in (select distinct zc.id
                           from gk_ykjh zc, gk_ykjh_mx zcm,gk_ykjh_sphz_mx zsm
                         where zc.id = zcm.gk_ykjh_id
                           and zsm.gk_ykjh_id=zc.id
                           and zcm.zbz_bal_id = v_zbz_bal_id
                           and zsm.gk_ykjhsphz_id = v_gk_ykjhsphz_id
                         order by zc.id) loop
      result := result || temp_cursor.id || ',';
    end loop;
    result := rtrim(result, ',');


  -----------------------------------------------------------------------------------------------

  else
    result := '';
  end if;
   dbms_output.put_line(result);
  return result;
end;

create or replace function get_var_val(in_code varchar2, in_pid VARCHAR2, v_sql varchar2, in_group_id varchar2,
vartype in number, null_value in varchar2,in_flowCode varchar2, in_nodesCode varchar2,
 in_workId varchar2, in_userId varchar2) return varchar2 is
/******************************************
       获取取数函数
***************************************/
  Result varchar2(200);
      type emp_array is Table OF varchar2(200) INDEX BY BINARY_INTEGER;
      TYPE CURSOR_TYPE IS REF CURSOR; --声明动态游标
      my_cursor CURSOR_TYPE; --声明游标变量
      a_field_params emp_array; -- 取数sql中的分组字段数组
      a_group_name emp_array; -- 取数sql中的分组字段数组
      a_group_type emp_array; -- 取数sql中的分组字段类型
      a_group_value emp_array; -- 取数sql中的分组字段数组
      a_var_params emp_array; --取数代码数组
      a_var_values emp_array; --取数代码值数组
      cursor_handle INTEGER;
      tmp_return  INTEGER;
      v_start  INT;
      v_count  INT;
      i_count  INT;
      v_tm_sql varchar2(1000);
      v_tm_code varchar2(30);
      v_value varchar2(200);
      v_error_msg varchar2(4000);
begin
      v_tm_sql:=v_sql;
      i_count:=0;
       while (instr(v_tm_sql,'#[')>0) loop
          v_start:=instr(v_tm_sql,'#[')+2;
          v_count:=get_end_pos(v_tm_sql,v_start)- v_start;
          v_tm_code:=substr(v_tm_sql,v_start,v_count);

/*          open my_cursor for 'SELECT T.GVALUE  FROM  zz_tmp_variable T WHERE T.group_id = '''||in_group_id||'''' ||' and T.CODE = '''||v_tm_code||'''';
             loop
                 fetch my_cursor into v_value;
                 exit when my_cursor%notfound;
             end loop;
          close my_cursor;*/

          SELECT T.GVALUE into v_value FROM  zz_tmp_variable T
          WHERE T.group_id =in_group_id
            and T.CODE = v_tm_code and rownum=1
            AND T.PID=in_pid ;

          if v_value='null' then
             Result:='null';
             return(Result);
          end if;
           v_tm_sql:=replace(v_tm_sql,'#['||v_tm_code||']',':'||v_tm_code);
           i_count:=i_count+1;
           a_var_params(i_count):=v_tm_code;
           a_var_values(i_count):=v_value;
       end loop;

    --解析SQL中的分组字段start
 --去掉'.'符号，若不去掉绑定变量的时候会找不到变量start
      i_count:=0;
      while (instr(v_tm_sql,'$[')>0) loop
            v_start:=instr(v_tm_sql,'$[')+2;
            v_count:=get_end_pos(v_tm_sql,v_start)- v_start;
            v_tm_code:=substr(v_tm_sql,v_start,v_count);
            i_count:=i_count+1;
           v_tm_sql:=replace(v_tm_sql,'$['||v_tm_code||']',':'||replace(v_tm_code,'.',''));
           a_field_params(i_count):=replace(v_tm_code,'.','');
      end loop;
       --去掉'.'符号，若不去掉绑定变量的时候会找不到变量end

       select t.value1,t.value2,t.value3,t.value4,t.value5,t.value6,t.value7,t.value8,t.value9,t.value10
       into a_group_value(1),a_group_value(2),a_group_value(3),a_group_value(4),a_group_value(5)
       ,a_group_value(6),a_group_value(7),a_group_value(8),a_group_value(9),a_group_value(10)
        from zz_tmp_group t where t.id=in_group_id and t.pid=in_pid;

       select t.name1,t.name2 ,t.name3,t.name4,t.name5,t.name6,t.name7,t.name8,t.name9,t.name10
       into a_group_name(1),a_group_name(2),a_group_name(3),a_group_name(4),a_group_name(5)
       ,a_group_name(6),a_group_name(7),a_group_name(8),a_group_name(9),a_group_name(10)
        from zz_tmp_group t where t.id=in_group_id and t.pid=in_pid;

       select t.type1,t.type2 ,t.type3,t.type4,t.type5,t.type6,t.type7,t.type8,t.type9,t.type10
       into a_group_type(1),a_group_type(2),a_group_type(3),a_group_type(4),a_group_type(5)
       ,a_group_type(6),a_group_type(7),a_group_type(8),a_group_type(9),a_group_type(10)
        from zz_tmp_group t where t.id=in_group_id and t.pid=in_pid;

  --解析SQL中的分组字段end

  --解析常量
      i_count:=0;
      while (instr(v_tm_sql,'%[')>0) loop
            v_start:=instr(v_tm_sql,'%[')+2;
            v_count:=get_end_pos(v_tm_sql,v_start)- v_start;
            v_tm_code:=substr(v_tm_sql,v_start,v_count);
            v_value:=get_constant_val(v_tm_code);
           v_tm_sql:=replace(v_tm_sql,'%['||v_tm_code||']',v_value);
      end loop;

    --去掉{}符号
  v_tm_sql:=replace(v_tm_sql,'{','');
  v_tm_sql:=replace(v_tm_sql,'}','');

     --解析接口参数
  i_count:=0;
  while (instr(v_tm_sql,'&[')>0) loop
        v_start:=instr(v_tm_sql,'&[')+2;
        v_count:=get_end_pos(v_tm_sql,v_start)- v_start;
        v_tm_code:=substr(v_tm_sql,v_start,v_count);
        v_value:=get_param_val(v_tm_code,in_flowCode,in_nodesCode,in_workId,in_userId);
       v_tm_sql:=replace(v_tm_sql,'&['||v_tm_code||']',v_value);
  end loop;

  --执行取数sql开始
    cursor_handle := DBMS_SQL.OPEN_CURSOR;
  dbms_sql.parse(cursor_handle,v_tm_sql,dbms_sql.native);

  --替换绑定取数值start
  for i in 1..a_var_params.count loop
        dbms_sql.bind_variable(cursor_handle,a_var_params(i),a_var_values(i)) ;
  end loop;
    --替换绑定取数值end

    i_count:=0;
    for i in 1..a_group_name.count loop
          if a_group_name(i) is not null then
             i_count:=i_count+1;
          end if;
    end loop;

      --绑定分组字段start
  for i in 1..a_field_params.count loop
          for j in 1..i_count loop
              if a_field_params(i)=replace(a_group_name(j),'.','') then
                 if a_group_type(j)='date' then
                   dbms_sql.bind_variable(cursor_handle,a_field_params(i),to_date(a_group_value(j)));
                 else
                   dbms_sql.bind_variable(cursor_handle,a_field_params(i),a_group_value(j));
                 end if;

              end if;
         end loop;
  end loop;
    --绑定分组字段end



    --绑定常量值start
/*  for i in 1..a_constant.count loop
        dbms_sql.bind_variable(cursor_handle,a_constant(i),get_constant_val(a_constant(i),v_constant_type)) ;
  end loop;*/
    --绑定常量值end

  dbms_sql.define_column(cursor_handle, 1, Result,200);
  tmp_return :=dbms_sql.EXECUTE(cursor_handle);
  if dbms_sql.fetch_rows(cursor_handle)>0 then
        dbms_sql.column_value(cursor_handle,1,Result);
  end if;
  if Result is null then
        if null_value='1' then
           Result:='null';
        elsif null_value='2' then
           Result:='0';
        elsif null_value='3' then
           Result:='';
        end if;
  end if;
  if (vartype=3 ) then Result:=to_char(to_date(Result),'YYYY-MM-DD'); end if;
  Result:=TO_CHAR(Result);
 dbms_sql.close_cursor(cursor_handle);
 --执行取数sql结束
  return(Result);
  exception when others then
     v_error_msg:='取数编码:'||in_code||'解析出错 sqlerrm:'||substr(sqlerrm,1,3000);
     DBMS_OUTPUT.PUT_LINE(v_error_msg);
     Result:='null';
     return result;

end get_var_val;

示例5：blob  clob 之间的转化

CREATE OR REPLACE FUNCTION blob_to_varchar(
    blob_in IN BLOB)
  RETURN VARCHAR2
IS
  v_varchar VARCHAR2(4000);
  v_start Pls_Integer  := 1;
  v_buffer Pls_Integer := 4000;
BEGIN
  IF Dbms_Lob.Getlength(blob_in) IS NULL THEN
    RETURN '';
  END IF;
FOR I IN 1..Ceil(Dbms_Lob.Getlength(blob_in) / V_Buffer)
LOOP
  --当转换出来的字符串乱码时，可尝试用注释掉的函数
  --v_varchar := Utl_Raw.Cast_To_Varchar2(Utl_Raw.Convert(Dbms_Lob.Substr(blob_in, v_buffer, v_start),'SIMPLIFIED CHINESE_CHINA.ZHS16GBK', 'AMERICAN_THE NETHERLANDS.UTF8'));
    v_varchar := Utl_Raw.Cast_To_Varchar2(Dbms_Lob.Substr(blob_in, v_buffer, v_start));
  v_start := v_start + v_buffer;
END LOOP;
RETURN v_varchar;
END ;

CREATE OR REPLACE FUNCTION B2C(P_BLOB BLOB) RETURN CLOB AS
  L_CLOB       CLOB;
  AMOUNT       NUMBER;
  DEST_OFFSET  NUMBER;
  SRC_OFFSET   NUMBER;
  BLOB_CSID    NUMBER;
  LANG_CONTEXT NUMBER;
  WARNING      NUMBER;
BEGIN
  DBMS_LOB.CREATETEMPORARY(L_CLOB, TRUE);
  AMOUNT       := DBMS_LOB.GETLENGTH(P_BLOB);
  DEST_OFFSET  := 1;
  SRC_OFFSET   := 1;
  BLOB_CSID    := NLS_CHARSET_ID('UTF8');
  LANG_CONTEXT := DBMS_LOB.DEFAULT_LANG_CTX;
  DBMS_LOB.CONVERTTOCLOB(L_CLOB,
                         P_BLOB,
                         AMOUNT,
                         DEST_OFFSET,
                         SRC_OFFSET,
                         BLOB_CSID,
                         LANG_CONTEXT,
                         WARNING);
  RETURN L_CLOB;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;

CREATE OR REPLACE FUNCTION C2B(L_CLOB CLOB) RETURN BLOB IS
  P_BLOB       BLOB;
  AMOUNT       NUMBER;
  DEST_OFFSET  NUMBER;
  SRC_OFFSET   NUMBER;
  BLOB_CSID    NUMBER;
  LANG_CONTEXT NUMBER;
  WARNING      NUMBER;
BEGIN

  DBMS_LOB.CREATETEMPORARY(P_BLOB, TRUE);
  AMOUNT       := DBMS_LOB.GETLENGTH(L_CLOB);
  DEST_OFFSET  := 1;
  SRC_OFFSET   := 1;
  BLOB_CSID    := NLS_CHARSET_ID('UTF8');
  LANG_CONTEXT := DBMS_LOB.DEFAULT_LANG_CTX;
  DBMS_LOB.CONVERTTOBLOB(P_BLOB,
                         L_CLOB,
                         AMOUNT,
                         DEST_OFFSET,
                         SRC_OFFSET,
                         BLOB_CSID,
                         LANG_CONTEXT,
                         WARNING);
  RETURN P_BLOB;
END C2B;