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

1   REPLACE  splitstr(p_string  2, p_delimiter  2, p_INDEX  NUMBER)  2      2  3   4     v_length   NUMBER := LENGTH(p_string);  5     v_start    NUMBER := 1;  6     v_index    NUMBER;  7     v_ret 2(20);  8     v_count NUMBER := 1;  9  10     (v_start <= v_length) 11     LOOP 12         v_index := INSTR(p_string, p_delimiter, v_start); 13         v_ret := substr(p_string,v_start,v_index-1); 14         v_start :=  v_index+1; 15          v_count = p_index  16           v_ret; 17          ; 18         v_count := v_count + 1; 19      LOOP; 20  21      v_ret; 22  splitstr;

示例2：解析分组函数

1   replace  resolve_group_sql(v_sql 2,in_flowCode 2,  2 in_nodesCode 2, in_workId 2, in_userId 2)  2   3 /******************************************  4        解析分组函数  5 ***************************************/  6   Result 2(3000);  7       v_start  ;  8       v_start2  ;  9       v_count  ; 10       v_count2  ; 11       i_count  ; 12       v_tm_sql 2(3000); 13       v_tm_str 2(3000); 14       v_tm_code 2(200); 15       v_tm_code2 2(200); 16       v_value 2(30); 17  18       v_tm_sql:=v_sql; 19 /*      i_count:=0; 20       while (instr(v_tm_sql,'{')>0) loop 21             v_start:=instr(v_tm_sql,'{')+1; 22             v_count:=instr(v_tm_sql,'}')- v_start; 23             v_tm_code:=substr(v_tm_sql,v_start,v_count); 24             i_count:=i_count+1; 25  26             v_tm_str:=v_tm_code; 27             while (instr(v_tm_str,'%[')>0) loop 28               v_start2:=instr(v_tm_str,'%[')+2; 29               v_count2:=instr(v_tm_str,']')- v_start2; 30               v_tm_code2:=substr(v_tm_str,v_start2,v_count2); 31               v_value:=get_constant_val(v_tm_code2); 32               v_tm_str:=replace(v_tm_str,'%['||v_tm_code2||']',v_value); 33             end loop; 34             select  v_tm_str into v_value from dual; 35  36            v_tm_sql:=replace(v_tm_sql,'{'||v_tm_code||'}',v_value); 37  38       end loop;*/ 39  40         --解析常量 41       i_count:=0; 42        (instr(v_tm_sql,'%[')>0) loop 43             v_start:=instr(v_tm_sql,'%[')+2; 44             v_count:=get_end_pos(v_tm_sql,v_start)- v_start; 45             v_tm_code:=substr(v_tm_sql,v_start,v_count); 46             v_value:=get_constant_val(v_tm_code); 47            v_tm_sql:=replace(v_tm_sql,'%['||v_tm_code||']',v_value); 48        loop; 49  50          --解析接口参数 51       i_count:=0; 52        (instr(v_tm_sql,'&[')>0) loop 53             v_start:=instr(v_tm_sql,'&[')+2; 54             v_count:=get_end_pos(v_tm_sql,v_start)- v_start; 55             v_tm_code:=substr(v_tm_sql,v_start,v_count); 56             v_value:=get_param_val(v_tm_code,in_flowCode,in_nodesCode,in_workId,in_userId); 57            v_tm_sql:=replace(v_tm_sql,'&['||v_tm_code||']',v_value); 58        loop; 59  60       --去掉{}符号 61       v_tm_sql:=replace(v_tm_sql,'{
    ',''); 62       v_tm_sql:=replace(v_tm_sql,'}',''); 63  64       Result:=v_tm_sql; 65   (Result); 66  resolve_group_sql; 67

示例3：获取主键id

1   REPLACE  NEXTID(NOID_  2)  NUMBER   2   PRAGMA AUTONOMOUS_TRANSACTION;  3   RESULT   NUMBER; --返回结果  4   IS_EXIST NUMBER; --是否存在，0 不尊在 1 存在  5   6   -- 判断非空  7    NOID_    NOID_ = ''   8      ;  9    ; 10   --判断是否存在序列 NOID 11  12    (*)  IS_EXIST  PWP_NO  NOID = NOID_; 13    IS_EXIST = 1  14     -- 存在主表信息 15      (*)  IS_EXIST  PWP_NODTL  NOID = NOID_; 16      IS_EXIST = 1  17       -- 存在明细表信息，查询需要的结果 18        NEXTID  RESULT  PWP_NODTL  NOID = NOID_; 19       -- 更新nextid为下一个id 20        PWP_NODTL  NEXTID = NEXTID + 1  NOID = NOID_; 21     ELSIF IS_EXIST = 0  22       --不存在明细表信息，明细表插入数据，nextid设置为2，返回1 23         PWP_NODTL 24         (NOID, 25          PREFIX, 26          NODAY, 27          NEXTID, 28          POSTFIX, 29          NOINCREMENT, 30          NOYEAR, 31          NOMONTH) 32        33         (NOID_, 34          , 35          0, 36          2, 37          , 38          1, 39          TO_CHAR(SYSDATE, 'yyyy'), 40          TO_CHAR(SYSDATE, 'MM')); 41  42       RESULT := 1; 43      ; 44    ; 45  46   -- 提交 47   ; 48   --返回 49   (RESULT); 50  NEXTID; 51

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

1   replace  GET_YKJF_YKJHID( v_zbz_bal_id   number,  2                                             v_gk_ykjhsphz_id  number)  3    2   4   result 2(4000);  5   6   7    v_zbz_bal_id      v_gk_ykjhsphz_id       8      temp_cursor  (  zc.id  9                             gk_ykjh zc, gk_ykjh_mx zcm,gk_ykjh_sphz_mx zsm 10                           zc.id = zcm.gk_ykjh_id 11                             zsm.gk_ykjh_id=zc.id 12                             zcm.zbz_bal_id = v_zbz_bal_id 13                             zsm.gk_ykjhsphz_id = v_gk_ykjhsphz_id 14                            zc.id) loop 15       result := result || temp_cursor.id || ','; 16      loop; 17     result := rtrim(result, ','); 18  19  20   ----------------------------------------------------------------------------------------------- 21  22    23     result := ''; 24    ; 25    dbms_output.put_line(result); 26    result; 27 ; 28

1   replace  get_var_val(in_code 2, in_pid 2, v_sql 2, in_group_id 2,  2 vartype  number, null_value  2,in_flowCode 2, in_nodesCode 2,  3  in_workId 2, in_userId 2)  2   4 /******************************************  5        获取取数函数  6 ***************************************/  7   Result 2(200);  8       type emp_array    2(200)   BINARY_INTEGER;  9       TYPE CURSOR_TYPE  REF ; --声明动态游标 10       my_cursor CURSOR_TYPE; --声明游标变量 11       a_field_params emp_array; -- 取数sql中的分组字段数组 12       a_group_name emp_array; -- 取数sql中的分组字段数组 13       a_group_type emp_array; -- 取数sql中的分组字段类型 14       a_group_value emp_array; -- 取数sql中的分组字段数组 15       a_var_params emp_array; --取数代码数组 16       a_var_values emp_array; --取数代码值数组 17       cursor_handle ; 18       tmp_return  ; 19       v_start  ; 20       v_count  ; 21       i_count  ; 22       v_tm_sql 2(1000); 23       v_tm_code 2(30); 24       v_value 2(200); 25       v_error_msg 2(4000); 26  27       v_tm_sql:=v_sql; 28       i_count:=0; 29         (instr(v_tm_sql,'#[')>0) loop 30           v_start:=instr(v_tm_sql,'#[')+2; 31           v_count:=get_end_pos(v_tm_sql,v_start)- v_start; 32           v_tm_code:=substr(v_tm_sql,v_start,v_count); 33  34 /*          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||''''; 35              loop 36                  fetch my_cursor into v_value; 37                  exit when my_cursor%notfound; 38              end loop; 39           close my_cursor;*/ 40  41            T.GVALUE  v_value   zz_tmp_variable T 42            T.group_id =in_group_id 43              T.CODE = v_tm_code  rownum=1 44              T.PID=in_pid ; 45  46            v_value='null'  47              Result:='null'; 48              (Result); 49            ; 50            v_tm_sql:=replace(v_tm_sql,'#['||v_tm_code||']',':'||v_tm_code); 51            i_count:=i_count+1; 52            a_var_params(i_count):=v_tm_code; 53            a_var_values(i_count):=v_value; 54         loop; 55  56     --解析SQL中的分组字段start 57  --去掉'.'符号，若不去掉绑定变量的时候会找不到变量start 58       i_count:=0; 59        (instr(v_tm_sql,'$[')>0) loop 60             v_start:=instr(v_tm_sql,'$[')+2; 61             v_count:=get_end_pos(v_tm_sql,v_start)- v_start; 62             v_tm_code:=substr(v_tm_sql,v_start,v_count); 63             i_count:=i_count+1; 64            v_tm_sql:=replace(v_tm_sql,'$['||v_tm_code||']',':'||replace(v_tm_code,'.','')); 65            a_field_params(i_count):=replace(v_tm_code,'.',''); 66        loop; 67        --去掉'.'符号，若不去掉绑定变量的时候会找不到变量end 68  69         t.1,t.2,t.3,t.4,t.5,t.6,t.7,t.8,t.9,t.10 70         a_group_value(1),a_group_value(2),a_group_value(3),a_group_value(4),a_group_value(5) 71        ,a_group_value(6),a_group_value(7),a_group_value(8),a_group_value(9),a_group_value(10) 72          zz_tmp_group t  t.id=in_group_id  t.pid=in_pid; 73  74         t.name1,t.name2 ,t.name3,t.name4,t.name5,t.name6,t.name7,t.name8,t.name9,t.name10 75         a_group_name(1),a_group_name(2),a_group_name(3),a_group_name(4),a_group_name(5) 76        ,a_group_name(6),a_group_name(7),a_group_name(8),a_group_name(9),a_group_name(10) 77          zz_tmp_group t  t.id=in_group_id  t.pid=in_pid; 78  79         t.type1,t.type2 ,t.type3,t.type4,t.type5,t.type6,t.type7,t.type8,t.type9,t.type10 80         a_group_type(1),a_group_type(2),a_group_type(3),a_group_type(4),a_group_type(5) 81        ,a_group_type(6),a_group_type(7),a_group_type(8),a_group_type(9),a_group_type(10) 82          zz_tmp_group t  t.id=in_group_id  t.pid=in_pid; 83  84   --解析SQL中的分组字段end 85  86   --解析常量 87       i_count:=0; 88        (instr(v_tm_sql,'%[')>0) loop 89             v_start:=instr(v_tm_sql,'%[')+2; 90             v_count:=get_end_pos(v_tm_sql,v_start)- v_start; 91             v_tm_code:=substr(v_tm_sql,v_start,v_count); 92             v_value:=get_constant_val(v_tm_code); 93            v_tm_sql:=replace(v_tm_sql,'%['||v_tm_code||']',v_value); 94        loop; 95  96     --去掉{}符号 97   v_tm_sql:=replace(v_tm_sql,'{
    ',''); 98   v_tm_sql:=replace(v_tm_sql,'}',''); 99 100      --解析接口参数101   i_count:=0;102    (instr(v_tm_sql,'&[')>0) loop103         v_start:=instr(v_tm_sql,'&[')+2;104         v_count:=get_end_pos(v_tm_sql,v_start)- v_start;105         v_tm_code:=substr(v_tm_sql,v_start,v_count);106         v_value:=get_param_val(v_tm_code,in_flowCode,in_nodesCode,in_workId,in_userId);107        v_tm_sql:=replace(v_tm_sql,'&['||v_tm_code||']',v_value);108    loop;109 110   --执行取数sql开始111     cursor_handle := DBMS_SQL.OPEN_CURSOR;112   dbms_sql.parse(cursor_handle,v_tm_sql,dbms_sql.native);113 114   --替换绑定取数值start115    i  1..a_var_params. loop116         dbms_sql.bind_variable(cursor_handle,a_var_params(i),a_var_values(i)) ;117    loop;118     --替换绑定取数值end119 120     i_count:=0;121      i  1..a_group_name. loop122            a_group_name(i)    123              i_count:=i_count+1;124            ;125      loop;126 127       --绑定分组字段start128    i  1..a_field_params. loop129            j  1..i_count loop130                a_field_params(i)=replace(a_group_name(j),'.','') 131                   a_group_type(j)='date' 132                    dbms_sql.bind_variable(cursor_handle,a_field_params(i),to_date(a_group_value(j)));133                  134                    dbms_sql.bind_variable(cursor_handle,a_field_params(i),a_group_value(j));135                   ;136 137                ;138           loop;139    loop;140     --绑定分组字段end141 142 143 144     --绑定常量值start145 /*  for i in 1..a_constant.count loop146         dbms_sql.bind_variable(cursor_handle,a_constant(i),get_constant_val(a_constant(i),v_constant_type)) ;147   end loop;*/148     --绑定常量值end149 150   dbms_sql.define_column(cursor_handle, 1, Result,200);151   tmp_return :=dbms_sql.(cursor_handle);152    dbms_sql.fetch_rows(cursor_handle)>0 153         dbms_sql.column_value(cursor_handle,1,Result);154    ;155    Result   156          null_value='1' 157            Result:='null';158         elsif null_value='2' 159            Result:='0';160         elsif null_value='3' 161            Result:='';162          ;163    ;164    (vartype=3 )  Result:=to_char(to_date(Result),'YYYY-MM-DD');  ;165   Result:=TO_CHAR(Result);166  dbms_sql.close_cursor(cursor_handle);167  --执行取数sql结束168   (Result);169     others 170      v_error_msg:='取数编码:'||in_code||'解析出错 sqlerrm:'||substr(sqlerrm,1,3000);171      DBMS_OUTPUT.PUT_LINE(v_error_msg);172      Result:='null';173       result;174 175  get_var_val;176

示例5：blob  clob 之间的转化

1   REPLACE  blob_to_varchar(  2     blob_in  BLOB)  3    2  4   5   v_varchar 2(4000);  6   v_start Pls_Integer  := 1;  7   v_buffer Pls_Integer := 4000;  8   9    Dbms_Lob.Getlength(blob_in)    10      ''; 11    ; 12  I  1..Ceil(Dbms_Lob.Getlength(blob_in) / V_Buffer) 13 LOOP 14   --当转换出来的字符串乱码时，可尝试用注释掉的函数 15   --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')); 16     v_varchar := Utl_Raw.Cast_To_Varchar2(Dbms_Lob.Substr(blob_in, v_buffer, v_start)); 17   v_start := v_start + v_buffer; 18  LOOP; 19  v_varchar; 20  ; 21

1   REPLACE  B2C(P_BLOB BLOB)  CLOB   2   L_CLOB       CLOB;  3   AMOUNT       NUMBER;  4   DEST_OFFSET  NUMBER;  5   SRC_OFFSET   NUMBER;  6   BLOB_CSID    NUMBER;  7   LANG_CONTEXT NUMBER;  8   WARNING      NUMBER;  9  10   DBMS_LOB.CREATETEMPORARY(L_CLOB, ); 11   AMOUNT       := DBMS_LOB.GETLENGTH(P_BLOB); 12   DEST_OFFSET  := 1; 13   SRC_OFFSET   := 1; 14   BLOB_CSID    := NLS_CHARSET_ID('UTF8'); 15   LANG_CONTEXT := DBMS_LOB.DEFAULT_LANG_CTX; 16   DBMS_LOB.CONVERTTOCLOB(L_CLOB, 17                          P_BLOB, 18                          AMOUNT, 19                          DEST_OFFSET, 20                          SRC_OFFSET, 21                          BLOB_CSID, 22                          LANG_CONTEXT, 23                          WARNING); 24    L_CLOB; 25  26    OTHERS  27      ; 28 ; 29

1   REPLACE  C2B(L_CLOB CLOB)  BLOB   2   P_BLOB       BLOB;  3   AMOUNT       NUMBER;  4   DEST_OFFSET  NUMBER;  5   SRC_OFFSET   NUMBER;  6   BLOB_CSID    NUMBER;  7   LANG_CONTEXT NUMBER;  8   WARNING      NUMBER;  9  10  11   DBMS_LOB.CREATETEMPORARY(P_BLOB, ); 12   AMOUNT       := DBMS_LOB.GETLENGTH(L_CLOB); 13   DEST_OFFSET  := 1; 14   SRC_OFFSET   := 1; 15   BLOB_CSID    := NLS_CHARSET_ID('UTF8'); 16   LANG_CONTEXT := DBMS_LOB.DEFAULT_LANG_CTX; 17   DBMS_LOB.CONVERTTOBLOB(P_BLOB, 18                          L_CLOB, 19                          AMOUNT, 20                          DEST_OFFSET, 21                          SRC_OFFSET, 22                          BLOB_CSID, 23                          LANG_CONTEXT, 24                          WARNING); 25    P_BLOB; 26  C2B; 27