PL/SQL Web表单模块化优化求助：减少参数传递的优雅实现方案

阿华AIGC实验室

2026-3-31

嘿，我完全懂你这种参数列表越拉越长的痛苦——每次维护都要翻半天，新增参数还要改所有调用的地方，太折腾了！之前尝试用owa_utils上下文没跑通对吧？别担心，我给你整理几个适合PL/SQL Web表单场景的模块化方案，直接对着你的代码改就行：

方案1：自定义应用上下文（替代owa_utils，更可控）

可能你之前用owa_utils的时候没正确配置上下文，其实Oracle的自定义上下文更灵活，专门用来在会话间/模块间共享数据，不需要传一堆参数。

步骤1：先创建上下文和对应的包（仅需执行一次）

-- 创建自定义上下文
CREATE OR REPLACE CONTEXT web_form_ctx USING web_form_ctx_pkg;

-- 上下文操作包
CREATE OR REPLACE PACKAGE web_form_ctx_pkg AS
  -- 设置单个参数
  PROCEDURE set_param(p_name VARCHAR2, p_value VARCHAR2);
  -- 设置数组类型参数（用逗号分隔字符串存储，后续拆分）
  PROCEDURE set_arr_param(p_name VARCHAR2, p_arr T_ARR);
  -- 获取单个参数
  FUNCTION get_param(p_name VARCHAR2) RETURN VARCHAR2;
  -- 获取数组参数
  FUNCTION get_arr_param(p_name VARCHAR2) RETURN T_ARR;
  -- 清除当前会话的上下文
  PROCEDURE clear_ctx;
END web_form_ctx_pkg;
/

CREATE OR REPLACE PACKAGE BODY web_form_ctx_pkg AS
  PROCEDURE set_param(p_name VARCHAR2, p_value VARCHAR2) IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT('web_form_ctx', p_name, p_value);
  END;

  PROCEDURE set_arr_param(p_name VARCHAR2, p_arr T_ARR) IS
    l_str VARCHAR2(4000);
  BEGIN
    IF p_arr IS NOT NULL AND p_arr.COUNT > 0 THEN
      FOR i IN p_arr.FIRST..p_arr.LAST LOOP
        l_str := l_str || ',' || p_arr(i);
      END LOOP;
      l_str := LTRIM(l_str, ',');
      DBMS_SESSION.SET_CONTEXT('web_form_ctx', p_name, l_str);
    END IF;
  END;

  FUNCTION get_param(p_name VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN SYS_CONTEXT('web_form_ctx', p_name);
  END;

  FUNCTION get_arr_param(p_name VARCHAR2) RETURN T_ARR IS
    l_str VARCHAR2(4000) := SYS_CONTEXT('web_form_ctx', p_name);
    l_arr T_ARR := T_ARR();
    l_pos PLS_INTEGER;
  BEGIN
    IF l_str IS NOT NULL THEN
      WHILE l_str IS NOT NULL LOOP
        l_pos := INSTR(l_str, ',');
        IF l_pos > 0 THEN
          l_arr.EXTEND;
          l_arr(l_arr.COUNT) := SUBSTR(l_str, 1, l_pos-1);
          l_str := SUBSTR(l_str, l_pos+1);
        ELSE
          l_arr.EXTEND;
          l_arr(l_arr.COUNT) := l_str;
          l_str := NULL;
        END IF;
      END LOOP;
    END IF;
    RETURN l_arr;
  END;

  PROCEDURE clear_ctx IS
  BEGIN
    DBMS_SESSION.CLEAR_CONTEXT('web_form_ctx');
  END;
END web_form_ctx_pkg;
/

步骤2：改造你的`test_page`过程（参数直接从上下文取）

CREATE OR REPLACE PROCEDURE test_page(
  -- 仅保留触发动作的p_choice，其他参数从上下文获取
  p_choice in VARCHAR2 default null
) AS
  refcur      SYS_REFCURSOR;
  g_page_link VARCHAR2(4000) default 'my_site/my_schema/my_package';
  l_strsql    VARCHAR2(4000);
  l_cnt       INT;
  cur_id      VARCHAR2(30);
  cur_colour  VARCHAR2(300);
  -- 从上下文读取原参数
  p_user_name VARCHAR2(30) := web_form_ctx_pkg.get_param('p_user_name');
  a_id        T_ARR := web_form_ctx_pkg.get_arr_param('a_id');
  a_colour    T_ARR := web_form_ctx_pkg.get_arr_param('a_colour');
  a_rating    T_ARR := web_form_ctx_pkg.get_arr_param('a_rating');
BEGIN
  -- 上下文初始化逻辑
  IF p_choice IS NULL THEN
    web_form_ctx_pkg.clear_ctx; -- 首次进入清空上下文
  ELSIF p_choice = 'rate' THEN
    -- 提交筛选时，将用户选择存入上下文
    web_form_ctx_pkg.set_param('p_user_name', apex_application.g_post('p_user_name'));
  ELSIF p_choice = 'done' THEN
    -- 提交评分时，将数组参数存入上下文
    web_form_ctx_pkg.set_arr_param('a_id', apex_application.g_post_arr('a_id'));
    web_form_ctx_pkg.set_arr_param('a_colour', apex_application.g_post_arr('a_colour'));
    web_form_ctx_pkg.set_arr_param('a_rating', apex_application.g_post_arr('a_rating'));
  END IF;

  -- 以下HTML生成逻辑与原代码基本一致，无需传递参数到内部模块
  htp.htmlopen;
  htp.headopen;
  htp.title('Test page');
  htp.headclose;
  htp.bodyopen;
  
  htp.print('<h1 align="center">Test page</h1>');
  
  IF p_choice IS NULL THEN
    htp.print('<form action="' || g_page_link || '.test_page"  method="post">');
    htp.print('<div>');
    htp.print('<table>');
    htp.print('<tr> <th colspan="2">Filter</th></tr>');
    htp.print('<tr> <td>User</td>' ||
              '<td> <select name="p_user_name" size="1">  ' ||
              '<option selected value="Peter">Peter</option>' ||
              '<option  value="Tom">Tom</option>' ||
              '</td></tr>');
    htp.print('<tr><td colspan="2"><button type="submit" name="p_choice" value="rate"> Use Filter </button> </td></tr>');
    htp.print('</table>');
    htp.print('</div>');
    htp.print('</form>');
  END IF;
  
  IF p_choice = 'rate' THEN
    htp.print('<form action="' || g_page_link || '.test_page"  method="post">');
    htp.print('<table><thead><tr>');
    htp.print('<th id="col">Colour</th>');
    htp.print('<th id="rat">Rating</th>');
    htp.print('</tr></thead><tbody>');
    l_strsql :=
            '
            select 1 as id, ''blue'' as colour from dual
                union all
            select 2 as id, ''red'' as colour from dual
                union all
            select 3 as id, ''green'' as colour from dual';
    l_cnt := 0;
    OPEN refcur FOR l_strsql;
    LOOP
      FETCH refcur INTO cur_id, cur_colour;
      EXIT WHEN refcur%NOTFOUND OR l_cnt >= 1000;
      l_cnt := l_cnt + 1;
      
      htp.print('<input type="hidden" name="a_id" value="' || cur_id || '">');
      htp.print('<input type="hidden" name="a_colour" value="' || cur_colour || '">');
      htp.print('<tr>' ||
                '<td axis="sstring" headers="col" >' || cur_colour || '</td>' ||
                '<td axis="number"  headers="rat"><input type="text" name="a_rating" value="' || 0 || '"></td>' ||
                '</tr>'
          );
    END LOOP;
    CLOSE refcur;
    htp.print('</tbody></table>');
    htp.print(' <p> <button type="submit" name="p_choice" value="done">Rate now </button></p>');
    htp.print('</form>');
  END IF;
  
  IF p_choice = 'done' THEN
    htp.print('<form action="' || g_page_link || '.test_page"  method="post">');
    htp.print('<div>');
    htp.print('<table>');
    htp.print('<tr> <th colspan="3">Result</th></tr>');
    htp.print('<tr> <td>User</td> <td value="' || p_user_name || '">' || p_user_name || '</td> <td> &nbsp;</td> </tr>');
    
    FOR i IN a_id.FIRST .. a_id.LAST LOOP
      htp.print('<tr> <td value="' || a_id(i) || '">' || a_id(i) || '</td><td value="' || a_colour(i) || '">' || a_colour(i) || '</td> <td value="' || a_rating(i) || '">' || a_rating(i) || '</td> </tr> ');
    END LOOP;
    htp.print('<tr><td colspan="2"><button type="submit" name="p_choice" value=""> Finish rating </button> </td></tr>');
    htp.print('</table>');
    htp.print('</div>');
    htp.print('</form>');
  END IF;
  
  htp.bodyclose;
  htp.htmlclose;
EXCEPTION
  WHEN OTHERS THEN
    htp.print('<h1 align="center">Error</h1>');
    htp.print('<p align="center"> ' || SQLERRM || '</p>');
END test_page;
/

方案2：用记录类型打包参数（结构清晰）

如果不想用上下文，把所有相关参数打包成一个自定义记录类型，过程只需要传一个参数就能搞定：

步骤1：定义包含数组的记录类型

-- 全局定义记录类型（或在包头部定义）
CREATE OR REPLACE TYPE web_form_params_rec IS RECORD(
  p_choice    VARCHAR2(30),
  p_user_name VARCHAR2(30),
  a_id        T_ARR,
  a_colour    T_ARR,
  a_rating    T_ARR
);
/

步骤2：改造`test_page`过程

CREATE OR REPLACE PROCEDURE test_page(
  p_params in web_form_params_rec default null
) AS
  refcur      SYS_REFCURSOR;
  g_page_link VARCHAR2(4000) default 'my_site/my_schema/my_package';
  l_strsql    VARCHAR2(4000);
  l_cnt       INT;
  cur_id      VARCHAR2(30);
  cur_colour  VARCHAR2(300);
  -- 处理默认参数，避免空指针
  l_params web_form_params_rec := NVL(p_params, web_form_params_rec(NULL, NULL, NULL, NULL, NULL));
BEGIN
  -- 表单提交时的参数组装逻辑（入口处统一处理）
  IF l_params.p_choice = 'rate' THEN
    l_params.p_user_name := apex_application.g_post('p_user_name');
  ELSIF l_params.p_choice = 'done' THEN
    l_params.a_id := apex_application.g_post_arr('a_id');
    l_params.a_colour := apex_application.g_post_arr('a_colour');
    l_params.a_rating := apex_application.g_post_arr('a_rating');
  END IF;

  -- 后续HTML生成逻辑直接从l_params中读取参数
  htp.htmlopen;
  htp.headopen;
  htp.title('Test page');
  htp.headclose;
  htp.bodyopen;
  
  htp.print('<h1 align="center">Test page</h1>');
  
  IF l_params.p_choice IS NULL THEN
    -- 表单提交时仍需单独传递字段，入口处组装为记录
    htp.print('<form action="' || g_page_link || '.test_page"  method="post">');
    htp.print('<div>');
    htp.print('<table>');
    htp.print('<tr> <th colspan="2">Filter</th></tr>');
    htp.print('<tr> <td>User</td>' ||
              '<td> <select name="p_user_name" size="1">  ' ||
              '<option selected value="Peter">Peter</option>' ||
              '<option  value="Tom">Tom</option>' ||
              '</td></tr>');
    htp.print('<tr><td colspan="2"><button type="submit" name="p_choice" value="rate"> Use Filter </button> </td></tr>');
    htp.print('</table>');
    htp.print('</div>');
    htp.print('</form>');
  END IF;

  -- 剩余HTML逻辑与原代码一致，仅需将参数替换为l_params.xxx
  -- ... 省略重复代码 ...
  
  htp.bodyclose;
  htp.htmlclose;
EXCEPTION
  WHEN OTHERS THEN
    htp.print('<h1 align="center">Error</h1>');
    htp.print('<p align="center"> ' || SQLERRM || '</p>');
END test_page;
/

方案3：包级全局变量（最简单，适合单会话场景）

如果你的Web表单是在同一个会话内完成的（比如mod_plsql或Oracle APEX会话），直接在包内定义全局变量最省事：

-- 包头部定义全局变量
CREATE OR REPLACE PACKAGE my_package AS
  g_p_user_name VARCHAR2(30);
  g_a_id        T_ARR;
  g_a_colour    T_ARR;
  g_a_rating    T_ARR;
  
  PROCEDURE test_page(p_choice in VARCHAR2 default null);
END my_package;
/

CREATE OR REPLACE PACKAGE BODY my_package AS
  PROCEDURE test_page(p_choice in VARCHAR2 default null) AS
    refcur      SYS_REFCURSOR;
    g_page_link VARCHAR2(4000) default 'my_site/my_schema/my_package';
    l_strsql    VARCHAR2(4000);
    l_cnt       INT;
    cur_id      VARCHAR2(30);
    cur_colour  VARCHAR2(300);
  BEGIN
    -- 从请求中赋值全局变量
    IF p_choice = 'rate' THEN
      g_p_user_name := apex_application.g_post('p_user_name');
    ELSIF p_choice = 'done' THEN
      g_a_id := apex_application.g_post_arr('a_id');
      g_a_colour := apex_application.g_post_arr('a_colour');
      g_a_rating := apex_application.g_post_arr('a_rating');
    ELSIF p_choice IS NULL THEN
      -- 首次进入清空全局变量
      g_p_user_name := NULL;
      g_a_id := NULL;
      g_a_colour := NULL;
      g_a_rating := NULL;
    END IF;

    -- HTML逻辑直接使用全局变量，无需参数传递
    htp.htmlopen;
    htp.headopen;
    htp.title('Test page');
    htp.headclose;
    htp.bodyopen;
    
    -- ... 剩余逻辑与原代码一致，参数替换为g_xxx ...
    
    htp.bodyclose;
    htp.htmlclose;
  EXCEPTION
    WHEN OTHERS THEN
      htp.print('<h1 align="center">Error</h1>');
      htp.print('<p align="center"> ' || SQLERRM || '</p>');
  END test_page;
END my_package;
/

方案对比与选型建议

方案	优点	缺点	适用场景
自定义上下文	会话安全、跨模块共享、支持多用户	需额外创建上下文和操作包	复杂多模块Web应用，需跨包共享参数
记录类型打包	参数结构清晰、符合面向对象思想	表单提交时需拆分/组装记录	模块间参数传递频繁、结构固定的场景
包级全局变量	实现最简单、代码改动最小	仅会话内有效，多会话易冲突	单会话完成的简单表单、快速迭代场景