开发记录02——MERGE USING和RESTfull服务 的使用

一个方便的语法，根据与源表联接的结果，对目标表执行插入、更新或删除操作

merge using (转) - Sam Jin - 博客园 (cnblogs.com)https://www.cnblogs.com/chinajch/p/3200198.html在例子中可以看到，只需要一个语法操作就同时涵盖了三种不同情况：

• 如果匹配到了，就更新掉目标表
• 如果匹配不到，就插入
• 如果来源表无法匹配到，就删除

高效又快捷

declare
    v_err_msg nvarchar2(2000);
    row_count number(20) := 1;
begin

    update CHECK_OBJECT
    set EXT_ORG_ID=:P20_EXT_ORG_ID,
        CATEGORY_FIRST_CODE=:P20_CATEGORY_FIRST_CODE,
        UPDATED_BY=:USER_ID,
        UPDATED_DATE=sysdate
    where OBJECT_ID = :P20_OBJECT_ID;

    -- 删除本次取消选中的表单
    delete CHECK_OBJECT_FORM_ASSO s
    where s.OBJECT_ID = :P20_OBJECT_ID
      and not exists(
            select 1
            from JA_UTILS_PKG.SPLIT_STR(:P20_FORM_ID, ':') A
            where s.FORM_ID = A.data_val
        );

    MERGE INTO CHECK_OBJECT_FORM_ASSO A
    USING (
        select data_val form_id, :P20_OBJECT_ID object_id
        from JA_UTILS_PKG.SPLIT_STR(:P20_FORM_ID, ':')
    ) B
    ON (A.OBJECT_ID = B.object_id and A.FORM_ID = B.form_id)
    WHEN NOT MATCHED THEN
        insert (created_by, updated_by, object_id, form_id)
        values (:USER_ID, :USER_ID, B.object_id, B.form_id);

    commit;
    apex_util.set_session_state('P20_ROW_COUNT', row_count);

exception
    when others then
        rollback;
        apex_util.set_session_state('P20_ROW_COUNT', 0);
        v_err_msg := sqlerrm || chr(13) || dbms_utility.format_error_backtrace;
        JA_WRITE_LOG('P' || :APP_PAGE_ID || ':' || :APP_PAGE_ALIAS, 'error', v_err_msg, :USER_ID, :USERTENANT,
                     :APP_NAME || ':' || :APP_ID);
end;

RESTful数据服务

​编辑

get代码

declare
    v_err_msg  nvarchar2(2000);
    v_clob     clob;
begin

    with rel_hier(checked, ORIGINAL_DEPT_ID, PARENT_ORIGINAL_DEPT_ID, name, lvl) as (
        select (case when :p_third_dept_id is not null and :p_dept_id > 0 then
                case when (
                        select count(MAPPING_ID)
                           from BASIC_DEPT_MAPPING t2
                           where t2.THIRD_DEPT_ID = dept.ORIGINAL_DEPT_ID
                             and t2.DEPT_ID = (select DEPT_ID
                                          from BASIC_DEPT o
                                          where o.DEPT_ID = :p_dept_id
                                            and o.DEL_FLAG = 0
                                            and DEPT_LEVEL = 1
                                            and o.TENANT_ID = :p_tenant_id)
                             and t2.TENANT_ID = :p_tenant_id
                    ) > 0 then 1 else 0 end
             else 0 end)       as       checked,
               dept.ORIGINAL_DEPT_ID,
               dept.PARENT_ORIGINAL_DEPT_ID,
               dept.ORIGINAL_DEPT_NAME name,
               1                lvl
        from (select distinct
                     ORIGINAL_DEPT_ID,
                     PARENT_ORIGINAL_DEPT_ID,
                     ORIGINAL_DEPT_NAME,
                     TENANT_ID
              from BASIC_SYSTEM_USER_ORIGINAL
              where LOGIN_USER_ID = :p_basic_login_user_id
                and ROLE_ID = :p_basic_role_id
                and SYSTEM_ID is null
                and IS_DATA_ISOLATION = 0
                and TENANT_ID = :p_tenant_id) dept
        where dept.PARENT_ORIGINAL_DEPT_ID = :p_third_dept_id
        union all
        select (case when :p_third_dept_id is not null and :p_dept_id > 0 then
                case when (
                        select count(MAPPING_ID)
                           from BASIC_DEPT_MAPPING t2
                           where t2.THIRD_DEPT_ID = n.ORIGINAL_DEPT_ID
                             and t2.DEPT_ID = (select DEPT_ID
                                          from BASIC_DEPT o
                                          where o.DEPT_ID = :p_dept_id
                                            and o.DEL_FLAG = 0
                                            and DEPT_LEVEL = 1
                                            and o.TENANT_ID = :p_tenant_id)
                             and t2.TENANT_ID = :p_tenant_id
                    ) > 0 then 1 else 0 end
             else 0 end) as    checked,
               n.ORIGINAL_DEPT_ID,
               n.PARENT_ORIGINAL_DEPT_ID,
               n.ORIGINAL_DEPT_NAME name,
               h.lvl + 1
        from rel_hier h
                 join (select distinct
                              ORIGINAL_DEPT_ID,
                              PARENT_ORIGINAL_DEPT_ID,
                              ORIGINAL_DEPT_NAME,
                              TENANT_ID
                       from BASIC_SYSTEM_USER_ORIGINAL
                       where LOGIN_USER_ID = :p_basic_login_user_id
                         and ROLE_ID = :p_basic_role_id
                         and SYSTEM_ID is null
                         and IS_DATA_ISOLATION = 0
                         and TENANT_ID = :p_tenant_id) n
                      on n.PARENT_ORIGINAL_DEPT_ID = h.ORIGINAL_DEPT_ID and n.TENANT_ID = :p_tenant_id
    ) search depth first by lvl set rn
       , rel_hier_with_leadlag as (
        select r.*,
               lag(lvl) over (order by rn)        as lag_lvl,
               lead(lvl, 1, 1) over (order by rn) as lead_lvl,
               json_object(
                       key 'id' value ORIGINAL_DEPT_ID,
                       key 'title' value name,
                       key 'field' value name,
                       key 'spread' value (case when lvl=1 then 'true' else 'false' end),
                       key 'level' value lvl,
                       key 'parent_id' value PARENT_ORIGINAL_DEPT_ID,
                       key 'checked' value checked,
                       key 'radioChecked' value checked,
                       key 'disabled' value decode( lvl,1,1,0) 
                   )                                 jso
        from rel_hier r
    )
    select json_query(
                   xmlcast(
                           xmlagg(
                                   xmlelement(e,
                                              case
                                                  when lvl - lag_lvl = 1
                                                      then ',"children":['
                                                  when lvl > 1 then ','
                                                  end ||
                                              substr(jso, 1, length(jso) - 1) ||
                                              case
                                                  when lvl >= lead_lvl then
                                                          '}' ||
                                                          rpad(' ', (lvl - lead_lvl) * 2 + 1, ']}')
                                                  end
                                       )
                                   order by rn
                               )
                       as clob
                       )
               , '$' returning clob pretty)
    into v_clob
    from rel_hier_with_leadlag;

    apex_json.open_object;
    apex_json.write('code', '200');
    apex_json.write('msg', 'success');
    apex_json.write('data', v_clob);
    apex_json.close_all;

exception
    when others then
        v_err_msg := sqlerrm || chr(13) || dbms_utility.format_error_backtrace;
        WRITE_LOG(GET_FN_NAME(), 'error',
                  v_err_msg, -1, 1);
        apex_json.open_object;
        apex_json.write('code', '500');
        apex_json.write('msg', 'error');
        apex_json.write('data', 'null');
        apex_json.close_all;
end;

申请RESTful服务，定义ORDS模板

get方法获取数据

最重要的是源-PL/SQL的内容

树形列表的展开都与之有关

BASIC_DEPT_MAPPING