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;

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;

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('''') then to_char(''获得卡片'')

                          when e.card_type=to_char('''') then to_char(''购买'')

                          when e.card_type=to_char('''') then to_char(''查看卡密'')

                          when e.card_type=to_char('''') then to_char(''解绑'')

                          when e.card_type=to_char('''') then to_char(''送出'')

                          when e.card_type=to_char('''') then to_char(''取消赠送'')

                          when e.card_type=to_char('''') then to_char(''赠送退回'')

                          when e.card_type=to_char('''') then to_char(''获赠'')

                          when e.card_type=to_char('''') then to_char(''解绑找回'')

                          when e.card_type=to_char('''') 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;