Oracle ORDS REST模块：PL/SQL存储过程如何使用可选参数

1. 创建支持可选参数的PL/SQL存储过程 #

先定义带默认值的存储过程，参数默认设为NULL，当URL未传入对应参数时，存储过程自动使用默认值，再通过WHERE子句的短路逻辑实现动态过滤：

CREATE OR REPLACE PROCEDURE get_orders(
    p_status IN VARCHAR2 DEFAULT NULL,
    p_order_id IN NUMBER DEFAULT NULL,
    p_result OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_result FOR
        SELECT order_id, customer_id, order_date, status, total_amount
        FROM orders
        -- 短路逻辑：参数为空时跳过该过滤条件
        WHERE (p_status IS NULL OR status = p_status)
          AND (p_order_id IS NULL OR order_id = p_order_id)
        ORDER BY order_date DESC;
END get_orders;
/

2. 在ORDS中绑定存储过程到GET处理器 #

你可以通过ORDS的Web界面或PL/SQL API创建REST服务，两种方式如下：

方式一：Web界面操作

1. 登录ORDS的SQL Developer Web，进入RESTful Services
2. 创建新模块：名称填Orders，路径前缀设为/Orders
3. 创建新模板：路径填/getOrders，方法选择GET
4. 创建新处理器：
   • 类型选择PL/SQL
   • 源代码中调用存储过程，映射URL参数到存储过程参数：

     DECLARE
         v_result SYS_REFCURSOR;
     BEGIN
         get_orders(
             p_status => :status,
             p_order_id => :orderID,
             p_result => v_result
         );
         ORDS.set_response(v_result);
     END;
     

   • 保存后，ORDS会自动处理参数存在性：未传参数时，对应绑定变量为NULL，匹配存储过程默认值

方式二：PL/SQL API批量创建

如果需要脚本化部署，执行以下代码：

BEGIN
    -- 创建模块
    ORDS.create_module(
        p_module_name    => 'Orders',
        p_base_path      => '/Orders/',
        p_items_per_page => 25
    );

    -- 创建GET请求模板
    ORDS.create_template(
        p_module_name    => 'Orders',
        p_pattern        => 'getOrders',
        p_method         => 'GET'
    );

    -- 绑定存储过程到处理器
    ORDS.create_handler(
        p_module_name    => 'Orders',
        p_pattern        => 'getOrders',
        p_method         => 'GET',
        p_source_type    => ORDS.source_type_plsql,
        p_source         => q'[
            DECLARE
                v_result SYS_REFCURSOR;
            BEGIN
                get_orders(
                    p_status => :status,
                    p_order_id => :orderID,
                    p_result => v_result
                );
                ORDS.set_response(v_result);
            END;
        ]',
        p_items_per_page => 25
    );

    COMMIT;
END;
/

3. 测试不同请求场景 #

• 无参数请求：GET /ords/Orders/getOrders → 返回所有订单
• 仅传status：GET /ords/Orders/getOrders?status=Pending → 返回所有状态为Pending的订单
• 仅传orderID：GET /ords/Orders/getOrders?orderID=1 → 返回订单ID为1的记录
• 传两个参数：GET /ords/Orders/getOrders?status=Pending&orderID=1 → 返回同时满足两个条件的订单

关键注意事项 #

• 存储过程参数必须设DEFAULT NULL，确保ORDS未收到参数时能传递空值
• WHERE子句的短路逻辑是核心，避免参数为空时过滤掉所有数据
• 若URL参数名和存储过程参数名不一致，直接在处理器代码中明确映射即可，比如:orderID对应p_order_id

内容的提问来源于stack exchange，提问作者alexsmt