Assistance with PL/SQL cursor

Question

I have 2 tables, plus a third that takes the data and inputs it into it.

tables are:

SQL> select *
 2  from paydata1;

IDNO NAME                      J     SALARY      PAYHR
---- ------------------------- - ---------- ----------
1111 Ann French                S      75000          0
2222 Robert Costa              H          0         45
3333 Linda Ames                H          0         50
4444 Scott Brooks              S      78000          0
5555 Susan Ash                 S      57000          0
6666 James Smith               S      55000          0
7777 Mary Jones                H          0         36
8888 John Morse                H          0         39

AND

SQL> select *
 2  from paytran1;

IDNO PRO    HOURSWK
---- --- ----------
1111 123         20
1111 456         10
1111 789         15
2222 123         17
2222 456         24
2222 789         20
3333 123         20
3333 789         20
4444 123         10
4444 456         28
4444 789          5
5555 456         40
6666 456         44
7777 456         30
7777 789         15
8888 123         10
8888 456         25
8888 789          5

The question is: write the PL/SQL program to read the files using cursors and write out the total hours worked by each employee.

My pl/sql code is:

SET SERVEROUTPUT ON
DECLARE
  v_idno      paydata1.idno%TYPE;
  v_name      paydata1.name%TYPE;
  v_idno1     paytran1.idno%TYPE;
  v_hourswk   paytran1.hourswk%TYPE;  
  CURSOR paydata1_cursor IS
     SELECT idno, name FROM paydata1
     ORDER BY idno;
  CURSOR paytran1_cursor IS
     SELECT idno, hourswk FROM paytran1 
     WHERE v_idno1 = idno
     order BY idno;
  BEGIN
     OPEN paydata1_cursor;
     LOOP
       FETCH paydata1_cursor INTO v_idno, v_name;
       EXIT WHEN paydata1_cursor%NOTFOUND;
       IF paytran1_cursor%ISOPEN THEN
        CLOSE paytran1_cursor;
       END IF;
       OPEN paytran1_cursor;
       LOOP
         FETCH paytran1_cursor INTO v_idno1, v_hourswk;
         EXIT WHEN paytran1_cursor%NOTFOUND;
         END LOOP;
       INSERT into allinfo 
          VALUES(v_idno, v_name, v_hourswk);
      CLOSE paytran1_cursor;
     END LOOP;
     CLOSE paydata1_cursor;
END;
/
SET SERVEROUTPUT OFF

My allinfo table results are:

SQL> select *
  2  from allinfo;

IDNO EMPLOYEE             HOURS
---- --------------- ----------
1111 Ann French
2222 Robert Costa
3333 Linda Ames
4444 Scott Brooks
5555 Susan Ash
6666 James Smith
7777 Mary Jones
8888 John Morse

Why isnt the hourswk being combined and displayed on the allinfo table with the idno and name?

Answer 1

By looking into the code. I think the better way is to make the approach purely sql rather then using PLSQL approach. I have replicated the scenario in such a way that you can implement it. Mey be this will help you out.

INSERT INTO <TABLE>
SELECT A.IDNO,
  SUM(A.PSUM+B.HSWR)
FROM
  (SELECT PAYHR.IDNO,
    SUM(PAYHR.PAYHR) PSUM
  FROM
    (SELECT 111 AS IDNO,23 AS PAYHR FROM DUAL
    UNION ALL
    SELECT 111 AS IDNO,30 AS PAYHR FROM DUAL
    UNION ALL
    SELECT 112 AS IDNO,10 AS PAYHR FROM DUAL
    UNION ALL
    SELECT 112 AS IDNO,8 AS PAYHR FROM DUAL
    UNION ALL
    SELECT 112 AS IDNO,15 AS PAYHR FROM DUAL
    UNION ALL
    SELECT 113 AS IDNO,25 AS PAYHR FROM DUAL
    )PAYHR
  GROUP BY PAYHR.IDNO
  )A,
  (SELECT HSWRK.IDNO,
    SUM(HSWRK.HSWR) HSWR
  FROM
    ( SELECT 111 AS IDNO,10 HSWR FROM DUAL
    UNION ALL
    SELECT 111 AS IDNO,30 HSWR FROM DUAL
    UNION ALL
    SELECT 111 AS IDNO,15 HSWR FROM DUAL
    UNION ALL
    SELECT 111 AS IDNO,25 HSWR FROM DUAL
    UNION ALL
    SELECT 112 AS IDNO,10 HSWR FROM DUAL
    UNION ALL
    SELECT 112 AS IDNO,17 HSWR FROM DUAL
    UNION ALL
    SELECT 112 AS IDNO,40 HSWR FROM DUAL
    UNION ALL
    SELECT 113 AS IDNO,16 HSWR FROM DUAL
    )HSWRK
  GROUP BY HSWRK.IDNO
  )B
WHERE A.IDNO = B.IDNO
GROUP BY A.IDNO;

Answer 2

Try making paytran1_cursor a parameterized cursor:

CURSOR paytran1_cursor (p_idno1 paydata1.idno%TYPE) IS
     SELECT SUM(hourswk) FROM paytran1 -- already have idno; just sum hours
     WHERE p_idno1 = idno -- don't need to order by one value
[...]

-- sum() will return one row, so no need to loop
OPEN paytran1_cursor (v_idno1);
FETCH paytran1_cursor INTO v_hourswk;

(I think that's the Oracle syntax.)

There are several problems here. The main one is that your cursor query is run as written when the cursor is opened. That means that when you opened paytran1_cursor, you were querying with the then-current value of v_idno1, which was NULL. Using a parameterized cursor here will

1. ensure that the value you pass into your WHERE clause is in scope, and
2. make it clear to the reader that you are conditioning the cursor query on data coming from outside the cursor.

Hope that helps.