retrieving records from two or more tables using cursor

how can i retrieve two or more tables using cursor

Page 1 of 1

1 Replies - 1726 Views - Last Post: 12 September 2010 - 02:49 PM Rate Topic: -----

#1 dianenacario  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 08-August 07

retrieving records from two or more tables using cursor

Posted 12 September 2010 - 03:48 AM

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


Is This A Good Question/Topic? 0
  • +

Replies To: retrieving records from two or more tables using cursor

#2 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: retrieving records from two or more tables using cursor

Posted 12 September 2010 - 02:49 PM

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;  



Was This Post Helpful? 0
  • +
  • -

Page 1 of 1