前言

• 如果要在存储过程中执行一个SELECT语句并处理其结果，你不能直接使用EXECUTE IMMEDIATE，因为EXECUTE IMMEDIATE主要用于执行那些不返回行的语句（如INSERT、UPDATE、DELETE等）或者那些你不需要处理返回结果的SELECT语句。

• 对于需要处理返回结果的SELECT语句，你应该使用游标（CURSOR）。以下是一个存储过程的示例，它接受一个SQL查询字符串作为输入，使用游标执行该查询，并遍历结果集来打印每一行的数据（在实际应用中，你可能会对结果集进行其他处理）。

创建表

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    hire_date DATE,
    job_id VARCHAR2(10),
    salary NUMBER(8, 2),
    department_id NUMBER
);

插入数据

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id) VALUES
(1, 'John', 'Doe', 'john.doe@example.com', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 'IT_PROG', 6000, 10),
(2, 'Jane', 'Smith', 'jane.smith@example.com', TO_DATE('2022-07-22', 'YYYY-MM-DD'), 'ST_CLERK', 4500, 20),
(3, 'Michael', 'Johnson', 'michael.johnson@example.com', TO_DATE('2021-11-30', 'YYYY-MM-DD'), 'SA_REP', 7000, 30);

COMMIT;

查询表中数据

SELECT * FROM EMPLOYEES;

EMPLOYEE_ID		FIRST_NAME	LAST_NAME	EMAIL							HIRE_DATE		JOB_ID		SALARY		DEPARTMENT_ID
1				'John'		'Doe'		'john.doe@example.com'			'2023-01-15'	'IT_PROG'	6000.00		10
2				'Jane'		'Smith'		'jane.smith@example.com'		'2022-07-22'	'ST_CLERK'	4500.00		20
3				'Michael'	'Johnson'	'michael.johnson@example.com'	'2021-11-30'	'SA_REP'	7000.00		30

创建存储过程

CREATE OR REPLACE PROCEDURE sp_dynamic_select(
    p_sql IN VARCHAR2
) AS
    -- 定义一个游标变量
    c SYS_REFCURSOR;
    -- 定义变量来存储从游标中检索到的数据
    v_employee_id   NUMBER;
    v_first_name    VARCHAR2(50);
    v_last_name     VARCHAR2(50);
    v_email         VARCHAR2(100);
    v_hire_date     DATE;
    v_job_id        VARCHAR2(10);
    v_salary        NUMBER(8, 2);
    v_department_id NUMBER;
BEGIN
    -- 打开游标并传递SQL查询字符串
    OPEN c FOR TO_CHAR(p_sql); -- 注意：这里使用TO_CHAR是为了确保p_sql被当作字符串处理，尽管在这个例子中它已经是字符串了

    -- 循环遍历游标中的每一行
    LOOP
        -- 从游标中检索数据到变量中
        FETCH c INTO v_employee_id, v_first_name, v_last_name, v_email, v_hire_date, v_job_id, v_salary, v_department_id;
        
        -- 检查是否到达游标的末尾
        EXIT WHEN c%NOTFOUND;
        
        -- 打印检索到的数据（在实际应用中，你可以对数据进行其他处理）
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id ||
                             ', First Name: ' || v_first_name ||
                             ', Last Name: ' || v_last_name ||
                             ', Email: ' || v_email ||
                             ', Hire Date: ' || TO_CHAR(v_hire_date, 'YYYY-MM-DD') ||
                             ', Job ID: ' || v_job_id ||
                             ', Salary: ' || TO_CHAR(v_salary) ||
                             ', Department ID: ' || v_department_id);
    END LOOP;
    
    -- 关闭游标
    CLOSE c;
EXCEPTION
    WHEN OTHERS THEN
        -- 捕获并处理异常
        DBMS_OUTPUT.PUT_LINE('Error executing query: ' || SQLERRM);
        -- 如果游标已经打开，则关闭它（尽管在异常处理中这通常不是必需的，因为游标会在过程结束时自动关闭）
        IF c%ISOPEN THEN
            CLOSE c;
        END IF;
END;
/

打开dbms_output包输出开关

dbms_output.enable;

调用存储过程

BEGIN
    -- 调用存储过程，并传递一个SELECT查询字符串作为参数
    sp_dynamic_select('SELECT employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 10');
END;
/