【开发问题解决方法记录】04.dian 权限表单优化

权限表单优化方向：

父级权限从晶点权限表获取做成列表下拉选中

权限名称和编码一行两列

页面id从

select *
from APEX_APPLICATION_PAGES where APPLICATION_ID=304;

中获取

【遇到的问题1】

DG可以获取到页面信息，但是表和应用程序无法获取到

【问题原因】走了弯路，直接去查对应数据表就好了，不用专门建表再查询

【问题解决】直接在值列表使用SQL查询：

select PAGE_NAME , PAGE_ID
from APEX_APPLICATION_PAGES where APPLICATION_ID=:APP_ID;

【遇到问题2】

在权限编码框中写入自动编码的代码，但是点击自动生成无效

先后执行顺序：若是用户未手动输入，点击新增按钮，先自动生成CODE再执行新增操作。

报错：

Access to undefined Per Request (Memory Only) variable P33_PERMISSION_ID

访问未定义的每个请求 (仅内存) 变量P33_PERMISSION_ID

【问题原因】

说明不能笼统卸载一起，因为P33_PERMISSION_ID在自动生成CODE的代码中是作为判断的的输入项，而在新增执行代码中是自增的，在按下“新增”按钮前都无法获取，所以会报错

【解决方法】

将自动生成CODE的代码和新增代码分开并校验

【代码】

自动生成权限CODE并校验

declare
    v_err_msg        nvarchar2(2000);--错误提示
    v_permission_code nvarchar2(32);  --权限编码
    row_count        number(20) := 1;
    v_count          number(20) := 0;
begin
  /**
     * CREATE BY: xiaoxiao
     * CREATE DATE: 2023-12-05 18:54
     * MODIFY BY:
     * MODIFY DATE:
     * DESCRIBE:自动生成权限CODE并校验
     */
    if :P33_PERMISSION_CODE is null then
        select 'JD-' || to_char(sysdate, 'yymmddhh24misssss') into v_permission_code from dual;
    else
        v_permission_code := :P33_PERMISSION_CODE;
    end if;
    if :P33_PERMISSION_ID is null then   --新增
        select count(1) --查询是否有重名情况
        into v_count
        from BASIC_SYSTEM_PERMISSION
        where PERMISSION_NAME = :P33_PERMISSION_NAME
          and TENANT_ID = :USER_TENANT
          and DEL_FLAG = 0;
        if v_count = 0 then --没有重名情况
            select count(1)--查询CODE是否有重复
            into v_count
            from BASIC_SYSTEM_PERMISSION
            where TENANT_ID = :USER_TENANT
              and PERMISSION_CODE = :P33_PERMISSION_CODE
              and DEL_FLAG = 0;
            if v_count > 0 then  --CODE重复
                row_count := -1;
                apex_util.set_session_state('P33_MESSAGE', '权限CODE重复');
            end if;
        else  --重名
            row_count := -1;
            apex_util.set_session_state('P33_MESSAGE', '权限名称重复');
        end if;
    else  --修改
        select count(1)
        into v_count
        from BASIC_SYSTEM_PERMISSION
        where TENANT_ID = :USER_TENANT
          and DEL_FLAG = 0
          and PERMISSION_ID != :P33_PERMISSION_ID;
        if v_count = 0 then
            select count(1)
            into v_count
            from BASIC_SYSTEM_PERMISSION
            where TENANT_ID = :USER_TENANT
              and permission_code = :P33_PERMISSION_CODE
              and DEL_FLAG = 0
              and PERMISSION_ID != :P33_PERMISSION_ID;
            if v_count > 0 then
                row_count := -1;
                apex_util.set_session_state('P33_MESSAGE', '权限CODE重复');
            end if;
        else
            row_count := -1;
            apex_util.set_session_state('P33_MESSAGE', '权限名称重复');
        end if;
    end if;

    apex_util.set_session_state('P33_ROW_COUNT', row_count);
exception
    when others then
        apex_util.set_session_state('P33_ROW_COUNT', 0);
        apex_util.set_session_state('P33_MESSAGE', '数据异常，请联系管理员');
        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;

执行新增

-- 新增权限
declare
    v_err_msg        nvarchar2(2000);
    v_permission_code nvarchar2(32);
    v_permission_id        number(20) ;
    row_count        number(20) := 0;
     /**
     * create by: xiaoxian
     * create date:2023/12/6 10:09
     * modify by:
     * modify date:
     * describe:权限新增
     */
begin
    if :P33_PERMISSION_CODE is null then  --用户未手动输入，自动生成CODE
        select 'JD-' || to_char(sysdate, 'yymmddhh24misssss') into v_permission_code from dual;
    else  --用户手动输入CODE
        v_permission_code := :P33_PERMISSION_CODE;  --自动生成的CODE赋值
    end if;
    -- insert or update
    if :P33_permission_id is null then  --新增
        insert into BASIC_SYSTEM_PERMISSION(
       PARENT_PERMISSION_ID,
       PERMISSION_CODE,
       PERMISSION_NAME,
       PERMISSION_TYPE,
       PERMISSION_URL,
       PERMISSION_LEVEL,
       PAGE_ID,
       SORT,
       REMARK,
       IS_ENABLE,
       DEL_FLAG,
       TENANT_ID,
       CREATED_BY,
       CREATION_DATE )
        values (:P33_PARENT_PERMISSION_ID, v_permission_code,:P33_PERMISSION_NAME, 'MENU',
                :P33_PERMISSION_URL,:P33_PERMISSION_LEVEL,:P33_PAGE_ID,:P33_SORT,:P33_REMARK,
                1,0,:USER_TENANT,:USER_ID,sysdate)
        returning permission_id into v_permission_id;
        row_count := SQL%ROWCOUNT;
        -- apex_util.set_session_state('P33_permission_id', v_permission_id);
    else   --修改
        update BASIC_SYSTEM_PERMISSION
        set UPDATED_BY=:USER_ID,
            UPDATE_DATE=sysdate,
            PARENT_PERMISSION_ID =:P33_PARENT_PERMISSION_ID,
            permission_code = v_permission_code,
            PERMISSION_NAME = :P33_PERMISSION_NAME,
            PERMISSION_URL = :P33_PERMISSION_URL,
            PERMISSION_LEVEL = :P33_PERMISSION_LEVEL,
            PAGE_ID = :P33_PAGE_ID,
            SORT = :P33_SORT,
            REMARK = :P33_REMARK
        where PERMISSION_ID = :P33_PERMISSION_ID;
        row_count := SQL%ROWCOUNT;
        v_permission_id :=:p33_PERMISSION_ID;
    end if;

    apex_util.set_session_state('P33_ROW_COUNT', row_count);
    apex_util.set_session_state('P33_PERMISSION_ID', v_permission_id);
    apex_util.set_session_state('P33_PERMISSION_CODE', v_permission_code);

exception
    when others then
        apex_util.set_session_state('P33_ROW_COUNT', 0);
        v_err_msg := sqlerrm || chr(13) || dbms_utility.format_error_backtrace;
       JA_WRITE_LOG('P' || :APP_PAGE_ID || '-系统设置-权限管理-新增', 'ERROR', V_ERR_MSG, :USER_ID, :USER_TENANT,
                     :APP_NAME || ':' || :APP_ID);
end;

效果：