apex和pl/sql学习记录2

最新推荐文章于 2024-06-24 19:52:33 发布

王小小鸭

最新推荐文章于 2024-06-24 19:52:33 发布

阅读量276

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/clover_oreo/article/details/132468539

oracle apex 同时被 3 个专栏收录

55 篇文章

订阅专栏

PL/SQL

48 篇文章

订阅专栏

错误记录

34 篇文章

订阅专栏

该博客围绕Oracle数据库展开，介绍页面权限管理。包括数据库表设计、创建管理页面、授权方案、页面和导航授权控制等操作。同时记录了运行中遇到的错误，如ORA - 00054、PLS - 00201等，并给出部分解决方案，还提及查看日志和动态菜单参考样例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

验证后过程函数代码插眼儿

--     登录后验证过程3
create or replace PROCEDURE TEST_USER_WXX3_PRO AS
    V_USER_ID    NUMBER(20);
    V_ROLE_ID    NUMBER(20);
    V_PERM_ID    NUMBER(20);
    V_DEPT_ID    NUMBER(20);
    V_USER_NAME     	NVARCHAR2(64);
    V_JOB_NUMBER 	NVARCHAR2(32);
    V_MOBILE    NVARCHAR2(32);
    V_EMAIL     NVARCHAR2(32);
    V_COUNT     NUMBER(10);
    V_ID        NUMBER(10);
    V_DEPT_NAME NVARCHAR2(64);
    V_PAGE_ID NUMBER(20);

begin
    -- 获取用户基础信息
      SELECT DEPT_ID,
             ROLE_ID,
             PERM_ID,
             USER_ID,
             USER_NAME,
             JOB_NUMBER,
             MOBILE,
             EMAIL
    INTO V_DEPT_ID,V_ROLE_ID,V_PERM_ID,V_USER_ID, V_USER_NAME, V_JOB_NUMBER, V_MOBILE, V_EMAIL
    FROM  TEST_USER_WXX_V
  where USER_NAME = upper(V('P9999_USERNAME'));

     -- 获取用户角色权限
SELECT COUNT(1)
    INTO V_COUNT
    FROM TEST_USER_WXX_V A
             LEFT JOIN TEST_ROLE_PERM_WXX_V B
                       ON A.ROLE_ID = B.ROLE_ID
    WHERE A.USER_ID = V_USER_ID;


    SELECT nvl(max(ROLE_ID), 0), nvl(max(PERM_ID), 0)
    INTO V_ID, V_PAGE_ID
    FROM TEST_ROLE_PERM_WXX_V
    WHERE IS_ENABLE = 1;

    IF V_COUNT > 0 THEN
        SELECT A.ROLE_ID, B.PERM_ID
        INTO V_ID, V_PAGE_ID
        FROM ROLE_USER_TEST_WXX A
                 LEFT JOIN TEST_ROLE_PERM_WXX_V B
                           ON A.ROLE_ID = B.ROLE_ID
        WHERE A.USER_ID = V_USER_ID
          AND ROWNUM = 1;
    ELSE
        INSERT INTO ROLE_USER_TEST_WXX (USER_ID, ROLE_ID) VALUES (V_USER_ID, V_ID);
        COMMIT;
    END IF;
 -- 设置USER
    APEX_CUSTOM_AUTH.SET_USER(V_USER_NAME);

 -- 将用户登入录信息注入session state
    APEX_UTIL.SET_SESSION_STATE('ROLE_ID', V_ID);
    APEX_UTIL.SET_SESSION_STATE('DEPT_ID', V_DEPT_ID);
    APEX_UTIL.SET_SESSION_STATE('USER_NAME', V_USER_NAME);
    APEX_UTIL.SET_SESSION_STATE('JOB_NUMBER', V_JOB_NUMBER);
    APEX_UTIL.SET_SESSION_STATE('MOBILE', V_MOBILE);
    APEX_UTIL.SET_SESSION_STATE('MAIL', V_EMAIL);
    APEX_UTIL.SET_SESSION_STATE('USER_ID', V_USER_ID);
    APEX_UTIL.SET_SESSION_STATE('DEPT_NAME', V_DEPT_NAME);
--     APEX_UTIL.SET_SESSION_STATE('USERID', V_USERID);
END;
/

到角色范例去看

发现里边的列（创建/更新者、创建/更新时间）的默认值都是从数据库pl/sql表达式获取

或许是该重新建一个新的了

【小改进】

将之前更新日期从可见变成不可见，方法如下所示↓

改之前

改之后

默认值由“-选择-”变成“表达式”，语言 pl/sql，表达式 sysdate，重复复制现有值打开

隐藏之前

隐藏之后

参考学习：

09技术太卷我学APEX-定制页面及导航菜单权限_白龙马5217的博客-优快云博客https://blog.youkuaiyun.com/html5builder/article/details/128816236?spm=1001.2014.3001.5501

全新的开始

1.数据库表设计

需要创建4个系统表，保存用户、角色及页面之间的关系。

角色表（角色ID，角色名称）
角色页面表（角色ID，页面ID）
用户表（用户ID，用户姓名，密码）
用户角色表（用户ID，角色ID）

-- 全新的数据表 新的开始
--角色表
CREATE TABLE  "SYS_ROLE_WXX"
   (	"P_ID" NUMBER(18,0) NOT NULL ENABLE,
	"ROLE_NAME" VARCHAR2(18) NOT NULL ENABLE,
	"S_NOTE" VARCHAR2(64),
	 CONSTRAINT "SYS_ROLE_PK" PRIMARY KEY ("P_ID")
  USING INDEX  ENABLE,
	 CONSTRAINT "SYS_ROLE_NAME" UNIQUE ("ROLE_NAME")
  USING INDEX  ENABLE
   );

drop table SYS_ROLE_WXX;
select * from SYS_ROLE_WXX;

--角色页面表
CREATE TABLE  "SYS_ROLE_PAGE_WXX"
   (	"P_ID" NUMBER(18,0) NOT NULL ENABLE,
	"ROLE_NAME" VARCHAR2(18) NOT NULL ENABLE,
	"PAGE_ID" NUMBER(18,0) NOT NULL ENABLE,
	"S_NOTE" VARCHAR2(64),
	 CONSTRAINT "SYS_ROLE_PAGE_PK" PRIMARY KEY ("P_ID")
  USING INDEX  ENABLE,
	 CONSTRAINT "SYS_ROLE_PAGE_UK1" UNIQUE ("ROLE_NAME", "PAGE_ID")
  USING INDEX  ENABLE
   );

--用户表
CREATE TABLE  "SYS_USER_WXX"
   (	"P_ID" NUMBER(18,0) NOT NULL ENABLE,
	"USER_NAME" VARCHAR2(18) NOT NULL ENABLE,
	"PASS_WORD" VARCHAR2(18) NOT NULL ENABLE,
	"DEPT_NAME" VARCHAR2(18),
	"S_MOBILE" VARCHAR2(18),
	"S_EMAIL" NVARCHAR2(64),
	"NICK_NAME" VARCHAR2(18),
	 CONSTRAINT "SYS_USER_PK" PRIMARY KEY ("P_ID")
  USING INDEX  ENABLE,
	 CONSTRAINT "SYS_USER_UK1_USER_NAME" UNIQUE ("USER_NAME")
  USING INDEX  ENABLE,
	 CONSTRAINT "SYS_USER_UK2_S_MOBILE" UNIQUE ("S_MOBILE")
  USING INDEX  ENABLE,
	 CONSTRAINT "SYS_USER_UK3_S_EMAIL" UNIQUE ("S_EMAIL")
  USING INDEX  ENABLE
   );

--用户角色表
CREATE TABLE  "SYS_USER_ROLE_WXX"
   (	"P_ID" NUMBER(17,0) NOT NULL ENABLE,
	"USER_NAME" VARCHAR2(17) NOT NULL ENABLE,
	"ROLE_NAME" VARCHAR2(17) NOT NULL ENABLE,
	"S_NOTE" VARCHAR2(52),
	 CONSTRAINT "SYS_USER_ROLE_PK" PRIMARY KEY ("P_ID")
  USING INDEX  ENABLE,
	 CONSTRAINT "SYS_USER_ROLE_UK1" UNIQUE ("USER_NAME", "ROLE_NAME")
  USING INDEX  ENABLE
   );

2.创建五个管理页面

2.1 角色role

交互网格带编辑

2.2角色页面管理

2.2.1交互网格带编辑，同上

2.2.2创建角色值列表

select role_name from sys_role_wxx

2.2.3 创建页面值列表

这里用到了APEX的视图APEX_APPLICATION_PAGES

select t.page_alias,t.page_id,t.page_name,t.page_title,t.page_mode,t.page_function
from APEX_APPLICATION_PAGES t 
where application_id = :APP_ID

两个值列表（角色、页面）配置到角色和页面ID列。

2.3 用户user管理

交互式网格+表单（这里我一起创建，事实证明是错的，后来删了重新创建，p44）

2.4 用户角色管理

2.4.1交互式网格加编辑

2.4.2创建用户值列表

select user_name from sys_user_wxx

用户和角色值列表配置到用户和角色。

2.5 系统管理

以上4个页面做一个系统管理导航卡列表系统管理_导航Card

创建导航卡 “系统管理_导航卡_Demo”_王小小鸭的博客-优快云博客https://blog.youkuaiyun.com/clover_oreo/article/details/132473008?spm=1001.2014.3001.5502

3 创建授权方案

3.1 创建一个授权控制函数F_CONTROL2

3.1.1先建视图：用户/页面/角色视图 sys_user_role_page_wxx_v

-- 用户 页面 角色视图 sys_user_role_page_wxx_v
create view SYS_USER_ROLE_PAGE_WXX_V as
select distinct UR.P_ID AS USER_ROLE_ID,
                UR.USER_NAME AS USER_NAME,
                UR.ROLE_NAME AS ROLE_NAME,
                UR.S_NOTE AS USER_ROLE_NOTE,
                U.P_ID AS USER_ID,
                PASS_WORD,
                DEPT_NAME,
                S_MOBILE,
                S_EMAIL,
                NICK_NAME,
                R.P_ID AS ROLE_ID,
                R.S_NOTE AS ROLE_NOTE,
                RP.P_ID AS ROLE_PAGE_ID,
                PAGE_ID,
                RP.S_NOTE AS ROLE_PAGE_NOTE
from SYS_USER_ROLE_WXX UR
    LEFT JOIN SYS_USER_WXX U on U.USER_NAME = UR.USER_NAME
    LEFT JOIN SYS_ROLE_WXX R on R.ROLE_NAME = UR.ROLE_NAME
    LEFT JOIN SYS_ROLE_PAGE_WXX RP on RP.ROLE_NAME = UR.ROLE_NAME;

3.3.2然后是授权控制函数 F_CONTROL2

create or replace function "F_CONTROL2"
(p_username in VARCHAR2,
 p_pageid in number)
return boolean
is
    b_ret boolean :=false;
    c_1 number;
begin
    select count(1) into c_1 from sys_user_role_page_wxx_v
        where user_name = p_username and page_id = p_pageid;
    if (c_1 >= 1) then 
        b_ret := true;
    else
        b_ret := false;
    end if;   
    return b_ret;
   
    exception when others then
        return false; 
end;

3.2 创建授权方案用户角色页面

名称：随便自己输入
方案类型：返回布尔值的PL/SQL函数
PL/SQL函数体：传入当前登录用户和当前页面
求值点：每次页访问一次

4 页面授权控制

设置需求授权的页面设置安全性如下图：

这样设置后，就是直接在IP地址栏输入页面也需求授权许可。

需要授权的都设置上授权方案：

选中页→安全性→授权方案 “用户角色页面”

其他页面都是上述操作

5 导航授权控制

设计是这样没有，根据登录帐户，没有授权的页面链接就不在导航菜单上显示了。目前为静态导航菜单，需要引用静态导航菜单的数据创建一个动态的导航菜单。

5.1 创建静态导航菜单的视图

创建失败

在DataGrip上创建成功，查询结果如下

这里用到APEX的系统视图，代码如下：

CREATE OR REPLACE FORCE EDITIONABLE VIEW "MENU_WXX_V" ("PARENT_ENTRY_TEXT", "DISPLAY_SEQUENCE", "ENTRY_TEXT", "PAGE_ID", "ENTRY_TARGET") AS 
  select parent_entry_text,display_sequence,entry_text,regexp_replace(entry_target,'[^0-9]') as page_id,entry_target
    --这里用到Oracle的正则表达式替换函数提取出目标的页面id：regexp_replace(entry_target,‘[^0-9]’) as page_id
from apex_application_list_entries t
where application_id = 273
--and list_id = 38247996304963868908
order by t.display_sequence
/

终于可以了

5.2 创建动态导航菜单视图

-- 创建动态导航菜单的视图
CREATE OR REPLACE FORCE EDITIONABLE VIEW "CK_MENU_V" ("LEVEL_VALUE", "LABEL_VALUE", "TARGET_VALUE", "IS_CURRENT", "IMAGE_VALUE", "IMAGE_ATTR_VALUE", "IMAGE_ALT_VALUE", "DISPLAY_ORDER", "PAGE_ID") AS
  select 1 as LEVEL_VALUE,entry_text as LABEL_VALUE,entry_target as TARGET_VALUE, '' as IS_CURRENT,
	ENTRY_IMAGE as IMAGE_VALUE,'' as IMAGE_ATTR_VALUE,'' as IMAGE_ALT_VALUE, display_sequence as DISPLAY_ORDER,
	regexp_replace(entry_target,'[^0-9]') as page_id
from apex_application_list_entries
where application_id = 273  and parent_entry_text is null
union all
select 2 as LEVEL_VALUE,entry_text as LABEL_VALUE,entry_target as TARGET_VALUE, '' as IS_CURRENT,
	ENTRY_IMAGE as IMAGE_VALUE,'' as IMAGE_ATTR_VALUE,'' as IMAGE_ALT_VALUE, display_sequence as DISPLAY_ORDER,
	regexp_replace(entry_target,'[^0-9]') as page_id
from apex_application_list_entries
where application_id = 273 and parent_entry_text is not null
order by DISPLAY_ORDER;

数据如下：

5.3 创建一个动态列表并设置为导航菜单

创建动态列表：CKU_MENU_WXX

动态列表sql代码

select * from ck_menu_v 
where page_id in (
	select distinct page_id from SYS_USER_ROLE_PAGE_WXX_V where user_name = :APP_USER
)

设置以上动态列表为导航菜单：

更改前

更改后

应用和问题

【错误记录】ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效

当我试图运行程序的时候发现用户、角色、页面表都没有数据，以运行就报没有授权的错，好家伙，紧急还原，发现主键无法自增，想重新删表然后建表使主键自增，结果就报错

[61000][54] ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效 Position: 11

找到的解决方案，试试

ORA-00054 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效_ora00054 资源正忙要求指定nowait_1silence的博客-优快云博客https://blog.youkuaiyun.com/GeorgeChan_/article/details/126056063 过程： -- 估计是锁表，找出哪个会话锁住了哪张表： session_id 为会话 ID。object_name 表名。

-- 删表失败报错[61000][54] ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效 Position: 11
-- 估计是锁表，找出哪个会话锁住了哪张表： session_id 为会话 ID。object_name 表名。
select l.SESSION_ID,o.owner,o.object_name
from v$locked_object l,dba_objects o
where l.object_id=o.object_id;

可以看到会话92锁住了表SYS_ROLE_WXX

select s.username,s.sid,s.serial#,s.logon_time
from v$locked_object l,v$session s
where l.session_id=s.sid
order by s.logon_time;
其中的 serial# 是我们需要用到的会话序列号

sid为92，serial#为11376

杀掉会话
命令格式为：alter system kill session 'sid,serial#'

执行命令为：alter system kill session '92,11376';

然后就可以删表了

--删除表
drop table SYS_ROLE_WXX;

删表之后再建表就可以了，但是因为限制了主键，所以还是没有用。

登录不上去才发现登录验证用的还是老版的，改成新的就好了

新的验证函数F_LOGIN2

--新的验证函数sys_user_wxx 
create or replace function "F_LOGIN2"
(p_username in VARCHAR2,
 p_password in VARCHAR2)
return boolean
is
    b_ret boolean :=false;
    c_1 number;
begin
    select count(1) into c_1 from sys_user_wxx 
        where user_name = p_username and pass_word = p_password;
    if (c_1 = 1) then 
        b_ret := true;
    else
        b_ret := false;
    end if;   
    return b_ret;
   
    exception when others then
        return false; 
end;

Error: 插件处理期间引发的 PLSQL 代码中出错。

改回F_Login后登录后验证出错 TEST_USER_WXX3_PRO

-	component: APEX_APPLICATION_AUTH logon (208665947166996685)

报错

-- 登录验证3
create function F_Login3(p_username in nvarchar2, p_password in nvarchar2)
    return boolean
as
    -- 登录验证
    -- return true:验证通过 false:验证未通过
    v_job_number nvarchar2(32);
    v_password   nvarchar2(32);
    v_content    varchar2(512);
    v_err_msg    varchar2(2000);
begin
    -- 登录信息
    v_content := 'p_username:' || p_username || chr(13) || 'p_password:' || p_password;
    select JOB_NUMBER, PASSWORD
    into v_job_number,v_password
    from USER_TEST_WXX2
    where IS_LEAVE = 0
      and JOB_NUMBER = upper(p_username);
    if p_password <> v_password then
        apex_error.add_error(
                p_message => '密码错误，请检查后输⼊',
                p_additional_info=>'附加消息-01',
                p_display_location => apex_error.c_inline_in_notification);
        return false;
    else
        if p_password = v_password then
            return true;
        end if;
    end if;
    return false;
exception
    when no_data_found then
        apex_error.add_error(
                p_message => '该账号不存在，请检查后输⼊',
                p_ignore_ora_error=> true,-- 忽略oracle异常消息，使⽤⾃定义错误消息
                p_display_location => apex_error.c_inline_in_notification);
        WRITE_LOG(GET_FN_NAME(), 'error', v_content || chr(13) || v_err_msg, null, -1);
        return false;
    when others then
        apex_error.add_error(
                p_message => '系统异常，请联系管理员',
                p_ignore_ora_error=> true,-- 忽略oracle异常消息，使⽤⾃定义错误消息
                p_display_location => apex_error.c_inline_in_notification);
        -- ⽇志记录
        v_err_msg := sqlerrm || chr(13) || dbms_utility.format_error_backtrace;
        WRITE_LOG(GET_FN_NAME(), 'error', v_content || chr(13) || v_err_msg, null, -1);
        return false;
end;

技术信息（仅提供给开发人员使用）

is_internal_error: false
component.type: APEX_APPLICATION_AUTH
component.id: 208665947166996685
component.name: logon

检查了一下

查看会话可以得知是设置授权方案的时候还延续着

begin apex_authentication.login(
    p_username => :P9999_USERNAME,
    p_password => :P9999_PASSWORD,
    p_uppercase_username => false);
end;

细节

占位符统一

页设计→项→外观值占位符：请输入xx

错误：

【错误记录】PLS-00201: 必须声明标识符 'LOGIN_SUCCESS_WXX_PRO'

登录后验证存储过程LOGIN_SUCCESS_WXX_PRO 未被创建成功

要实现菜单页面的显示/隐藏，需要在

共享组件→ 列表→导航菜单→列表详细资料→创建/编辑→列表条目→条件选择“返回布尔值的函数体”（即只有返回true才会显示菜单，false则会隐藏菜单）

【错误记录】 Error: ERR-1002 在应用程序 "273" 中未找到项 "USER_JOB_NUMBER" 的项 ID

授权方案查验

会话	1988285613145
用户	李四
工作区	2204375772170718
浏览器语言	zh-cn

之前

return f_control(
p_username => :APP_NAME,
p_pageid => :APP_PAGE_ID
);

之后

return f_control(
p_username => :USER_NAME,
p_pageid => :PAGE_ID
);

【错误记录】ora_sqlerrm: ORA-01422: 实际返回的行数超出请求的行数

【错误记录】ora_sqlerrm: ORA-06550: 第 1 行, 第 27 列: PLS-00302: 必须声明 'LOGIN2' 组件

全是问题，盖亚！

查看日志

select * from E_LOGS order by CREATED_DATE desc;

登录后验证：TEST_USER_WXX_PRO

create PROCEDURE TEST_USER_WXX_PRO AS
    V_USER_ID    NUMBER(20);
    V_ROLE_ID    NUMBER(20);
    V_PERM_ID    NUMBER(20);
    V_USER_NAME  NVARCHAR2(64);
    V_JOB_NUMBER NVARCHAR2(32);
    V_MOBILE     NVARCHAR2(32);
    V_EMAIL      NVARCHAR2(32);
    V_COUNT      NUMBER(10);
    V_ID         NUMBER(10);
    V_DEPT_NAME  NVARCHAR2(64);

begin
    -- 获取用户基础信息
    SELECT
           ROLE_ID,
           PERM_ID,
           USER_ID,
           USER_NAME,
           JOB_NUMBER,
           MOBILE,
           EMAIL
    INTO V_ROLE_ID,V_PERM_ID,V_USER_ID, V_USER_NAME, V_JOB_NUMBER, V_MOBILE, V_EMAIL
    FROM TEST_USER_WXX_V
    where USER_NAME = upper(V('P9999_USERNAME')) and rownum=1;

    -- 获取用户角色权限
    select COUNT(USER_ID)
    into V_COUNT
    from ROLE_USER_TEST_WXX ru
             join ROLE_TEST_WXX2 r
                  on r.ROLE_ID = ru.ROLE_ID
    where USER_ID = V_USER_ID;

    if V_COUNT > 0 then
        select r.ROLE_ID
        into V_ROLE_ID
        from ROLE_USER_TEST_WXX ru
                 join ROLE_TEST_WXX2 r
                      on r.ROLE_ID = ru.ROLE_ID
        where USER_ID = V_USER_ID
          and rownum = 1;

    else
        select ROLE_ID
        into V_id
        from ROLE_TEST_WXX2
        where IS_ENABLE = 1
          and rownum = 1;
        insert into ROLE_USER_TEST_WXX(USER_ID, ROLE_ID)
        values (V_USER_ID, V_id);
        commit;
    end if;

    -- 设置USER
    apex_custom_auth.set_user(V_USER_NAME);

    -- 将用户登入录信息注入session state
    apex_util.set_session_state('ROLE_ID', V_ROLE_ID);
    apex_util.set_session_state('DEPT_NAME', V_DEPT_NAME);
--     apex_util.set_session_state('USER_NAME', V_NAME);
    apex_util.set_session_state('USER_JOB_NUMBER', V_JOB_NUMBER);
end;
/

成功啦！

参考样例

新的思路

return is_have_permission_wxx(V('WXX_USER_ID'), 2,V('WXX_ROLE_ID'));

动态菜单

select
level,
m1.NAME label,
'f?p=&'||'APP_ID.:'||m1.PAGE_ID||':&'||'SESSION.::&'||'DEBUG.::::' target,
null IS_CURRENT,
m1.ICON image
from AUTH_MENU m1
where m1.APP_CODE = V('APP_CODE') and m1.TENANT_ID = :USERTENANT and m1.DEL_FLAG = 0 and m1.IS_ACTIVE = 'Y' and m1.MENU_ID in (
select m2.PARENT_ID as MENU_ID from AUTH_ROLE_MENU_ASSO rma1
join AUTH_MENU m2
on rma1.MENU_ID = m2.MENU_ID
where rma1.ROLE_ID = :ROLEID and m2.PARENT_ID is not null
union
select rma2.MENU_ID from AUTH_ROLE_MENU_ASSO rma2 where rma2.ROLE_ID = :ROLEID
)
start with m1.PARENT_ID is null
connect by prior m1.MENU_ID = m1.PARENT_ID
order siblings by m1.SORT_NO