如何使用另一张表的所有值作为参数执行带参Oracle查询？

根据你的需求，要让接收参数的查询遍历另一张表的所有参数值执行，分几种场景给你可行方案：

一、适配Oracle SQL*Plus的写法（对应你用的&&pnbr变量） #

方法1：生成批量执行脚本 #

先通过查询生成包含所有参数的SQL语句，再执行：

-- 生成脚本到文件
SPOOL run_queries.sql
SELECT 'SELECT ' || personnbr || ', pname FROM PERSON WHERE pno=' || personnbr || ';' FROM (SELECT DISTINCT personnbr FROM CUSTOMER);
SPOOL OFF
-- 执行生成的脚本
@run_queries.sql

方法2：用PL/SQL游标循环执行 #

如果需要更灵活的逻辑（比如处理结果），可以写PL/SQL块：

SET SERVEROUTPUT ON
DECLARE
    CURSOR pnbr_cursor IS SELECT DISTINCT personnbr FROM CUSTOMER;
    v_pnbr PERSON.pno%TYPE;
BEGIN
    OPEN pnbr_cursor;
    LOOP
        FETCH pnbr_cursor INTO v_pnbr;
        EXIT WHEN pnbr_cursor%NOTFOUND;
        -- 动态执行查询
        EXECUTE IMMEDIATE 'SELECT :1, pname FROM PERSON WHERE pno=:1' USING v_pnbr;
        -- 如需打印结果，添加下面的语句
        DBMS_OUTPUT.PUT_LINE('参数: ' || v_pnbr || '，对应姓名: ' || pname);
    END LOOP;
    CLOSE pnbr_cursor;
END;
/

二、SQL Server环境 #

游标循环执行 #

DECLARE @pnbr INT; -- 根据实际字段类型调整
DECLARE pnbr_cursor CURSOR FOR SELECT DISTINCT personnbr FROM CUSTOMER;

OPEN pnbr_cursor;
FETCH NEXT FROM pnbr_cursor INTO @pnbr;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @pnbr, pname FROM PERSON WHERE pno = @pnbr;
    FETCH NEXT FROM pnbr_cursor INTO @pnbr;
END;

CLOSE pnbr_cursor;
DEALLOCATE pnbr_cursor;

表变量循环执行 #

DECLARE @pnbrs TABLE (pnbr INT);
INSERT INTO @pnbrs SELECT DISTINCT personnbr FROM CUSTOMER;

WHILE EXISTS(SELECT 1 FROM @pnbrs)
BEGIN
    DECLARE @current_pnbr INT = (SELECT TOP 1 pnbr FROM @pnbrs);
    SELECT @current_pnbr, pname FROM PERSON WHERE pno = @current_pnbr;
    DELETE FROM @pnbrs WHERE pnbr = @current_pnbr;
END;

三、MySQL环境 #

写存储过程批量执行：

DELIMITER //
CREATE PROCEDURE run_batch_queries()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_pnbr INT;
    DECLARE pnbr_cursor CURSOR FOR SELECT DISTINCT personnbr FROM CUSTOMER;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN pnbr_cursor;
    read_loop: LOOP
        FETCH pnbr_cursor INTO v_pnbr;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SELECT v_pnbr, pname FROM PERSON WHERE pno = v_pnbr;
    END LOOP;
    CLOSE pnbr_cursor;
END //
DELIMITER ;

-- 调用存储过程
CALL run_batch_queries();

更高效的替代方案：直接关联查询 #

如果不需要分开执行每个参数的查询，而是要一次性获取所有结果，直接用JOIN比循环高效得多：

SELECT c.personnbr, p.pname
FROM (SELECT DISTINCT personnbr FROM CUSTOMER) c
LEFT JOIN PERSON p ON c.personnbr = p.pno;

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