关于Oracle中使用pljson及中文乱码解决方案

一、创建生成json的sql优化函数：

create or replace function sql_to_json_clob(i_sql varchar2,i_batch_rownum number default 100) return clob is
  FunctionResult clob;
    l_clob         CLOB; --最终结果
    l_sql2         VARCHAR2(30000); --排序SQL
    l_col_name_str VARCHAR2(10000); --字段名串
    l_sql3         VARCHAR2(30000); --分页SQL
    l_row_count    NUMBER; --已经处理的行数
    l_sum_count    NUMBER; --总行数
    l_clob_tmp     CLOB; --缓存
    l_curid        INTEGER;
    l_cnt          NUMBER;
    l_desctab      dbms_sql.desc_tab;
begin
   --统计行数
    EXECUTE IMMEDIATE 'select count(1) from (' || i_sql || ')'
      INTO l_sum_count;
    --如果没数据，返回空LIST
    IF l_sum_count = 0
    THEN
      RETURN '[]';
    END IF;
  
    --开始取字段名称
    l_curid := dbms_sql.open_cursor();
    dbms_sql.parse(l_curid, i_sql, dbms_sql.native);
    dbms_sql.describe_columns(l_curid, l_cnt, l_desctab);
    FOR i IN 1 .. l_desctab.count LOOP
      l_col_name_str := l_col_name_str || CASE
                          WHEN l_col_name_str IS NULL THEN
                           NULL
                          ELSE
                           ','
                        END || '"' || l_desctab(i).col_name || '"';
    END LOOP;
    --取字段名称结束
  
    --组装排序SQL
    l_sql2 := 'select t.*,row_number() over(order by ' || l_col_name_str ||
              ') rn from (' || i_sql || ') t order by  ' ||
              to_char(l_desctab.count + 1);
    --关闭游标          
    dbms_sql.close_cursor(l_curid);
    -- dbms_output.put_line(l_sql2);
  
    --初始化处理记录数
    l_row_count := 0;
    --初始化CLOB对象
    l_clob := empty_clob();
    dbms_lob.createtemporary(l_clob, TRUE);
  
    --开始进行分页处理
    LOOP
      --组装分页SQL
      l_sql3 := 'select ' || l_col_name_str || ' from (' || l_sql2 ||
                ') where rn > ' || to_char(l_row_count) || ' and rn<=' ||
                to_char(l_row_count + i_batch_rownum);
      -- dbms_output.put_line(l_sql3);
      --初始化缓存对象
      l_clob_tmp := empty_clob();
      dbms_lob.createtemporary(l_clob_tmp, TRUE);
     
      --SQL转换成JSONLIST再转换成CLOB，存入缓存
      pljson_list.to_clob(SELF       => pljson_util_pkg.sql_to_json(l_sql3,
                                                                    i_batch_rownum,
                                                                    0),
                          buf        => l_clob_tmp,
                          erase_clob => TRUE);
    
      --将缓存复制到CLOB对象
      dbms_lob.copy(dest_lob    => l_clob,
                    src_lob     => l_clob_tmp,
                    amount      => dbms_lob.getlength(l_clob_tmp),
                    dest_offset => dbms_lob.getlength(l_clob) + 1,
                    src_offset  => CASE
                                     WHEN dbms_lob.getlength(l_clob) = 0 THEN
                                      1
                                     ELSE
                                      2
                                   END);
    
      --已处理行数变大
      l_row_count := l_row_count + i_batch_rownum;
    
      --如果已处理行数小于总行数，把CLOB内的最后一个字符，由"]"变成"," ,否则退出循环
      IF l_row_count < l_sum_count
      THEN
        dbms_lob.write(lob_loc => l_clob,
                       amount  => 1,
                       offset  => dbms_lob.getlength(l_clob),
                       buffer  => ',');
      ELSE
        EXIT;
      END IF;
    
    END LOOP;
    --返回最终结果
    RETURN l_clob;
  EXCEPTION
    WHEN OTHERS THEN
      raise_application_error(-20001, SQLERRM);
  --return(FunctionResult);
end sql_to_json_clob;

通过plsql的command Window安装对应的pljosn包：

SQL> @C:/pljson/pljson-master/install.sql

使用pljosn过程中，会出现中文乱码，可以使用如下解决方案：

在程序包体中找到PLJSON_PRINTER,如下进行注释，添加else null,重新编译。

 

 注：本人在重新编译之后，执行函数 sql_to_json_clob无效异常，关闭PLSQL,重新登录plsql即可解决。

 二、通过pljosn包json_dyn.executeList，可将指定表转换成json数组对象：

declare 
  ret json_list;
begin
  ret := json_dyn.executeList('select bns_type,workflow_name from base_workflow group by bns_type,workflow_name');
  ret.print;
end;

输出结果为：

[{
  "BNS_TYPE" : "500",
  "WORKFLOW_NAME" : "评估机构注销"
}, {
  "BNS_TYPE" : "100",
  "WORKFLOW_NAME" : "协会品牌新建"
}, {
  "BNS_TYPE" : "100",
  "WORKFLOW_NAME" : "经纪机构新建"
}, {
  "BNS_TYPE" : "100",
  "WORKFLOW_NAME" : "行业检查新建1"
}, {
  "BNS_TYPE" : "500",
  "WORKFLOW_NAME" : "品牌注销"
}, {
  "BNS_TYPE" : "500",
  "WORKFLOW_NAME" : "窗口网签合同注销"
}, {
  "BNS_TYPE" : "200",
  "WORKFLOW_NAME" : "荣誉申报变更"
}, {
  "BNS_TYPE" : "100",
  "WORKFLOW_NAME" : "机构网签合同签订"
}, {
  "BNS_TYPE" : "100",
  "WORKFLOW_NAME" : "协会推送机构新建"
}, {
  "BNS_TYPE" : "200",
  "WORKFLOW_NAME" : "协会品牌变更"

通过json_dyn.executeObjec转换成json对象：

declare 
  ret json;
begin
  ret := json_dyn.executeObject('select bns_type,workflow_name from base_workflow group by bns_type,workflow_name');
  ret.print;
end;

输出结果为：

{
  "names" : ["BNS_TYPE", "WORKFLOW_NAME"],
  "data" : [["500", "评估机构注销"], ["100", "协会品牌新建"], ["100", "经纪机构新建"], ["100", "行业检查新建1"], ["500", "品牌注销"], ["500", "窗口网签合同注销"], ["200", "荣誉申报变更"], ["100", "机构网签合同签订"], ["100", "协会推送机构新建"], ["200", "协会品牌变更"], ["100", "卖房委托新建（机构）"], ["100", "机构审批部门申请房源核验码"], ["500", "经纪机构注销"], ["200", "协会经纪机构变更"], ["500", "交易中心代办购房资格注销"], ["100", "行业检查新建"], ["100", "个人申请房源核验码"], ["200", "委卖协议变更-机构"], ["500", "网签合同注销"], ["100", "评估机构新建"], ["100", "123123123"], ["200", "经纪机构变更"], ["100", "荣誉申报新增"], ["200", "委买协议变更"], ["500", "卖房委托取消（机构）"], ["100", "个人用户新建投诉"], ["200", "协会经纪人变更"], ["100", "购房资格申请"], ["200", "评估人员变更"], ["100", "协会经纪人新建"], ["100", "交易中心代办购房资格新建"], ["500", "手拉手网签合同注销"], ["100", "租赁处投诉新建"], ["100", "经纪机构网签合同新建"], ["100", "评估人员新增"], ["100", "经纪机构审核密钥代办购房资格申请"], ["100", "窗口网签合同"], ["100", "经纪人新建"], ["100", "委卖协议新建"], ["200", "行业检查变更"], ["200", "委卖协议变更-卖方"], ["500", "协会经纪人注销"], ["500", "协会品牌注销"], ["100", "委买协议新建"], ["100", "品牌新建"], ["100", "经纪机构录入密钥代办购房资格新建"], ["100", "房源核验码申请"], ["500", "协会经纪机构注销"], ["100", "手拉手网签合同"], ["200", "品牌变更"], ["200", "经纪人变更"], ["500", "经纪人注销02"], ["500", "经纪人注销1"], ["100", "个人申请购房资格新建"], ["100", "测试流程"], ["200", "评估机构变更"], ["500", "评估人员注销"], ["500", "卖房委托取消（个人）"], ["500", "经纪人注销"], ["100", "机构录入部门申请核验码"], ["500", "个人注销购房资格申请"], ["500", "委卖协议注销"], ["500", "委买协议注销"], ["100", "卖房委托新建（个人）"]]
}

构建json：

declare 
  json_obj json;
  json_lst_100 json_list;
  json_lst_500 json_list;
  obj json;
begin
  obj:=json();
  --obj.put('新建',100);
  json_obj := json_dyn.executeObject('select bns_type,workflow_name from base_workflow group by bns_type,workflow_name');
  json_lst_100:=json_dyn.executeList('select bns_type,workflow_name from base_workflow where bns_type=100 group by bns_type,workflow_name ');
  json_lst_500:=json_dyn.executeList('select bns_type,workflow_name from base_workflow  where bns_type=500 group by bns_type,workflow_name');
  --ret.put('children',ret);
  --obj.put('children',json_lst_100);
 obj.put('注销',500);
  obj.put('children',json_lst_500);
  obj.print;
  --ret.get_values().print;
  --ret.get_keys().print;
end;