在SELECT语句中调用存储过程及获取输出参数的方法

嘿，这个问题我之前帮同事处理过，咱们一步步说清楚哈！

首先得明确核心点：直接在SELECT语句里调用存储过程（PROCEDURE）是行不通的。因为存储过程的设计目标是执行批量操作、修改数据这类任务，它本身不会返回值，而SELECT语句需要的是能返回单个/集合值的对象（比如函数FUNCTION）。那要获取存储过程的P_direct_balance和P_indirect_balance输出参数，咱们有几个实用的方案：

方案1：用函数封装存储过程逻辑（最推荐） #

如果你的存储过程核心就是计算这两个余额值，最优雅的方式是把它的逻辑封装成一个函数，返回包含两个余额的自定义类型，这样就能直接在SELECT里调用了。

步骤分解： #

1. 先定义一个自定义对象类型，用来装两个余额：

CREATE OR REPLACE TYPE balance_obj AS OBJECT (
    direct_balance NUMBER,
    indirect_balance NUMBER
);
/

2. 在你的包里面新增一个函数，调用原存储过程并返回这个对象：

CREATE OR REPLACE PACKAGE your_package AS
    -- 假设你的原存储过程是这样的
    PROCEDURE get_balances(p_input_id IN NUMBER, p_direct_balance OUT NUMBER, p_indirect_balance OUT NUMBER);
    
    -- 新增封装函数
    FUNCTION get_balances_func(p_input_id IN NUMBER) RETURN balance_obj;
END your_package;
/

CREATE OR REPLACE PACKAGE BODY your_package AS
    PROCEDURE get_balances(p_input_id IN NUMBER, p_direct_balance OUT NUMBER, p_indirect_balance OUT NUMBER) IS
    BEGIN
        -- 这里是你原存储过程的逻辑，比如计算两个余额
        p_direct_balance := 1500; -- 示例值
        p_indirect_balance := 3200; -- 示例值
    END get_balances;
    
    FUNCTION get_balances_func(p_input_id IN NUMBER) RETURN balance_obj IS
        v_direct NUMBER;
        v_indirect NUMBER;
        v_balance balance_obj;
    BEGIN
        -- 调用原存储过程获取输出参数
        get_balances(p_input_id, v_direct, v_indirect);
        -- 把值装进自定义对象
        v_balance := balance_obj(v_direct, v_indirect);
        RETURN v_balance;
    END get_balances_func;
END your_package;
/

3. 现在就可以在SELECT里直接调用这个函数，提取两个余额了：

SELECT 
    t.id,
    -- 直接访问对象的属性
    your_package.get_balances_func(t.id).direct_balance AS direct_bal,
    your_package.get_balances_func(t.id).indirect_balance AS indirect_bal
FROM your_target_table t;

注意：如果表数据量很大，上面的写法会对每一行调用两次函数，影响性能。可以用Oracle 12c+支持的CROSS APPLY来优化，只调用一次函数：

-- 先定义一个集合类型装balance_obj
CREATE OR REPLACE TYPE balance_obj_table AS TABLE OF balance_obj;
/

-- 优化后的查询
SELECT 
    t.id,
    b.direct_balance,
    b.indirect_balance
FROM your_target_table t
CROSS APPLY (
    SELECT your_package.get_balances_func(t.id) AS bal FROM dual
) x
CROSS JOIN TABLE(CAST(MULTISET(SELECT x.bal) AS balance_obj_table)) b;

方案2：用管道函数返回批量结果集 #

如果需要一次性处理多条记录的余额，比如给一批ID计算余额，管道函数是个不错的选择，它可以返回多行记录。

步骤分解： #

1. 在包里定义记录类型和表类型：

CREATE OR REPLACE PACKAGE your_package AS
    -- 定义单条余额记录的类型
    TYPE balance_rec IS RECORD (
        input_id NUMBER,
        direct_balance NUMBER,
        indirect_balance NUMBER
    );
    -- 定义装多条记录的表类型
    TYPE balance_tab IS TABLE OF balance_rec;
    
    -- 管道函数，接收ID列表，返回余额表
    FUNCTION pipe_balances(p_input_ids IN sys.odcinumberlist) RETURN balance_tab PIPELINED;
END your_package;
/

2. 实现管道函数：

CREATE OR REPLACE PACKAGE BODY your_package AS
    FUNCTION pipe_balances(p_input_ids IN sys.odcinumberlist) RETURN balance_tab PIPELINED IS
        v_direct NUMBER;
        v_indirect NUMBER;
        v_rec balance_rec;
    BEGIN
        -- 遍历传入的ID列表
        FOR i IN 1..p_input_ids.COUNT LOOP
            -- 调用原存储过程获取输出参数
            get_balances(p_input_ids(i), v_direct, v_indirect);
            -- 赋值给记录
            v_rec.input_id := p_input_ids(i);
            v_rec.direct_balance := v_direct;
            v_rec.indirect_balance := v_indirect;
            -- 把记录管道输出
            PIPE ROW(v_rec);
        END LOOP;
        RETURN;
    END pipe_balances;
END your_package;
/

3. 调用方式：

-- 直接传入ID列表查询
SELECT * FROM TABLE(your_package.pipe_balances(sys.odcinumberlist(101,102,103)));

-- 和现有表关联查询
SELECT t.id, b.direct_balance, b.indirect_balance
FROM your_target_table t
JOIN TABLE(your_package.pipe_balances(sys.odcinumberlist(t.id))) b ON t.id = b.input_id;

方案3：用临时表中转（不改存储过程的妥协方案） #

如果不想修改现有包和存储过程，那可以通过PL/SQL块调用存储过程，把输出参数插入临时表，再查询临时表的数据。

步骤分解： #

1. 创建临时表：

CREATE GLOBAL TEMPORARY TABLE temp_balances (
    input_id NUMBER,
    direct_balance NUMBER,
    indirect_balance NUMBER
) ON COMMIT DELETE ROWS; -- 提交后自动清空数据，也可以选ON COMMIT PRESERVE ROWS

2. 用PL/SQL块调用存储过程并插入临时表：

DECLARE
    -- 游标获取需要处理的ID
    CURSOR c_target_ids IS SELECT id FROM your_target_table;
    v_direct NUMBER;
    v_indirect NUMBER;
BEGIN
    -- 遍历每个ID，调用存储过程并插入临时表
    FOR rec IN c_target_ids LOOP
        your_package.get_balances(rec.id, v_direct, v_indirect);
        INSERT INTO temp_balances VALUES (rec.id, v_direct, v_indirect);
    END LOOP;
END;
/

3. 查询临时表获取结果：

SELECT * FROM temp_balances;

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