[dianenacario]

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;
/

This post has been edited by baavgai: 12 September 2010 - 03:55 AM
Reason for edit:: tagged

[Ionut]

join those tables in your select clause.

CURSOR emp_cursor (date1 DATE, date2 DATE) IS  
SELECT first_name,  
 last_name,  
 hire_date  
 FROM employees 
  inner join table2 on table2.id = employess.id
  WHERE hire_date BETWEEN date1 AND date2;