oracle 常用工具类及函数

 j_param                     json;
  jl_keys                     json_list;

 -- 创建json对象j_param
  j_param := json(p_in_str);
-- 校验param域是否缺少必填参数
  jl_keys := json_list('["userId","queryId", "tCardNo","cardNo","cardPwd"]');
if false = json_util_extra.containKeys(j_param, jl_keys) then
    p_out_str := '{"resultCode":"30", "resultMsg":"param域Json缺少必填参数"}';
    return;
  end if;

创建package

create or replace package json_util_extra is
  function containKeys(p_v1  json,
                       p_v2  json_list,
                       exact boolean default false) return boolean;

  function containEmptyValues(p_v1  json,
                              p_v2  json_list,
                              exact boolean default false) return boolean;
end json_util_extra;

创建 package body

create or replace package body json_util_extra is
  function containKeys(p_v1 json, p_v2 json_list, exact boolean)
    return boolean as
  begin
  
    for i in 1 .. p_v2.count loop
      if (not p_v1.exist(p_v2.get(i).get_string)) then
        return false;
      end if;
    end loop;
  
    return true;
  end;

  function containEmptyValues(p_v1 json, p_v2 json_list, exact boolean)
    return boolean as
    v_key varchar2(1024);
  begin
  
    for i in 1 .. p_v2.count loop
      v_key := p_v2.get(i).get_string;
    
      if (not p_v1.exist(v_key)) then
        return false;
      end if;
    
      if (p_v1.get(v_key).value_of is null) then
        return false;
      end if;
    end loop;
  
    return true;
  end;

begin
  -- Initialization
  null;
end json_util_extra;

-- 获取字符串类型
 json_ext.get_string(j_param, 'queryId');
-- 获取数字类型
 json_ext.get_number(j_param, 'userId');　　　　

-- 生成标准md5
CREATE OR REPLACE FUNCTION MD5(V_INPUT_STRING IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN LOWER(UTL_RAW.CAST_TO_RAW(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => V_INPUT_STRING)));
  END MD5;
-- select md5(1) from dual

v_sqltext := 'select e.card_type as "cardType",
                        case
                          when e.card_type=to_char(''100'') then to_char(''获得卡片'')  
                          when e.card_type=to_char(''101'') then to_char(''购买'')
                          when e.card_type=to_char(''102'') then to_char(''查看卡密'') 
                          when e.card_type=to_char(''103'') then to_char(''解绑'') 
                          when e.card_type=to_char(''104'') then to_char(''送出'') 
                          when e.card_type=to_char(''105'') then to_char(''取消赠送'') 
                          when e.card_type=to_char(''106'') then to_char(''赠送退回'') 
                          when e.card_type=to_char(''107'') then to_char(''获赠'') 
                          when e.card_type=to_char(''108'') then to_char(''解绑找回'')
                          when e.card_type=to_char(''109'') then to_char(''提货申请'') 
                          else to_char(''其它'') end "cardTypeName",
                          e.mark as "mark",
                        to_char(e.opt_date,''yyyy-mm-dd hh24:mi:ss'') as "optDate"
                from (select d.*, rownum as rn
                      from (select ubcl.card_type,ubcl.mark,ubcl.opt_date
                            from xshe_user_band_card_log ubcl
                            where ubcl.user_id =' || v_user_id || '
                                 and ubcl.card_no =''' ||v_card_no ||''' 
                                 and ubcl.status = 1 order by ubcl.opt_date asc,ubcl.id asc
                            ) d
                      where rownum <= ' || v_end_rownum || ') e
                 where rn > ' || v_begin_rownum;
                   -- update  huzhiyang 2019-10-14 10:38:47 ，卡号为字符串
   -- 执行SQL语句并返回josn_list数据
  jl_card_list := json_dyn.executeList(v_sqltext);
-- 拼接字符串时注意引号数量

-- 拼接json
p_out_str := '{"resultCode":"' || v_ret ||
                '", "resultMsg":"' || v_des || 
                '","page":"' || v_page ||
               '","pageSize":"' || v_page_size || 
               '","totalPage":"' ||v_total_page ||
               '","totalCount":"' ||v_total_count ||
               '",' || v_single_info || '}'; 

-- 打印输出错误信息
 DBMS_OUTPUT.put_line('sqlcode : ' ||sqlcode);
 DBMS_OUTPUT.put_line('sqlerrm : ' ||sqlerrm);

-- 创建数组
type card_no_tab is table of varchar2(2000) index by BINARY_INTEGER;
  v_card_no_tab_info card_no_tab;

 v_card_no_tab_info(vv_count) := '{"cardNo":"' || r.cardNo ||
                                    '","background":"' ||r.background || '"}';
for k in 1 .. vv_count loop
    if k < vv_count then
      v_single_info := v_single_info || v_card_no_tab_info(k) || ',';
    else
      v_single_info := v_single_info || v_card_no_tab_info(k);
    end if;
  end loop;