使用游标检索两个或多个表 [英] retrieving two or more tables using cursor

问题描述

如何使用光标检索两个或多个表?

How can i retrieve two or more tables using cursor?

DECLARE
	date1 DATE := ''&first_date''; 
	date2 DATE := ''&second_date''; 
	CURSOR emp_cursor (date1 DATE, date2 DATE) IS
	SELECT first_name,
		last_name,
		hire_date
	FROM employees
	WHERE hire_date BETWEEN date1 AND date2;
		TYPE fname IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
		TYPE lname IS TABLE OF employees.last_name%TYPE INDEX BY PLS_INTEGER;
		TYPE hdate IS TABLE OF employees.hire_date%TYPE INDEX BY PLS_INTEGER;
		f fname;
		l lname;
		h hdate;
BEGIN
	OPEN emp_cursor(date1,date2);
		LOOP
	FETCH emp_cursor BULK COLLECT INTO f, l, h;
		FOR ctr IN 1 .. f.COUNT()
		LOOP
			DBMS_OUTPUT.PUT_LINE(''Name: ''|| f(ctr) || '' '' || l(ctr));
			DBMS_OUTPUT.PUT_LINE(''Date Hired: ''|| h(ctr));
			DBMS_OUTPUT.PUT_LINE(''------------------------------------'');
		END LOOP;
			DBMS_OUTPUT.PUT_LINE(emp_cursor%ROWCOUNT || '' number of rows retrieved'');
		EXIT WHEN emp_cursor%NOTFOUND;
		END LOOP;
	CLOSE emp_cursor;
END;

推荐答案

可以在这些表上使用JOIN吗?
如果它们具有相似的结构，则可以执行UNION并获取所有记录.

Can you use JOIN on those tables?

If they have a similar structures you could do a UNION and get all records.

这篇关于使用游标检索两个或多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！