【达梦数据库】存储过程调用实践案例-select

前言

• 如果要在存储过程中执行一个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', '[e-mail]', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 'IT_PROG', 6000, 10),
(2, 'Jane', 'Smith', '[e-mail]', TO_DATE('2022-07-22', 'YYYY-MM-DD'), 'ST_CLERK', 4500, 20),
(3, 'Michael', 'Johnson', '[e-mail]', 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'		'[e-mail]'			'2023-01-15'	'IT_PROG'	6000.00		10
2				'Jane'		'Smith'		'[e-mail]'		'2022-07-22'	'ST_CLERK'	4500.00		20
3				'Michael'	'Johnson'	'[e-mail]'	'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;
EXCEPTIONWHEN OTHERS THEN-- 捕获并处理异常DBMS_OUTPUT.PUT_LINE('Error executing query: ' || SQLERRM);-- 如果游标已经打开，则关闭它（尽管在异常处理中这通常不是必需的，因为游标会在过程结束时自动关闭）IF c%ISOPEN THENCLOSE 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;
/