2nd SQL w/in Procedure Using Result of 1st Query

[obpetr]

In a procedure which uses two cursors, I fetch a value from Query#1 and place it to a VARCHAR
variable. In my 2nd query in the predicate, I want to place the tablecolumn = the variable
value.

I have tried simply using the variable after the '=' sign in the SQL query; my query returns
no values. (I am surprised this compiled since the SQL column is CHAR and I would expect use
of single quotes.)

I have tried preceding the variable with the '&', and the compile fails.

Can you advise?
In the second SQL query, goto the two occurrences of var_player_nme.
I've done an extensive search, and cannot find an example.
Thank you.

Here is my code:

Code: Select all

create or replace procedure "PROC_TEST_CALC_3"
is
  var_player_nme  VARCHAR2(75);
  pr_player_nme   score.player_nme%TYPE;
  pr_CALC_nbr     score.tot_score%TYPE;
  CALC            NUMBER(2):= 0;
  CURSOR get_player_crsr is
   SELECT DISTINCT player_nme
                               FROM score
                               WHERE player_nme = 'Doe_John';
/* The above query is modified to produce one row for development */
  CURSOR fig_player_CALC_crsr is
     SELECT (ROUND(AVG(tot_score),0) - 72) as CALC
     FROM  (
              SELECT *
              from     (SELECT player_nme, yr, rnd_nbr, tot_score
                           FROM    (SELECT *
                                         FROM  ( SELECT player_nme, yr, rnd_nbr, tot_score
                                                     FROM score
                                                    WHERE player_nme = var_player_nme
                                                               and yr <= 2011
                                                    ORDER BY yr desc, rnd_nbr desc )
                                         WHERE rownum <= 20 )
                           WHERE player_nme = var_player_nme
                           ORDER BY tot_score )
     WHERE rownum <= 10
               )
     GROUP BY player_nme;
begin
OPEN get_player_crsr;
OPEN fig_player_CALC_crsr;
LOOP
  DBMS_OUTPUT.PUT_LINE('Before Fetching Player Name');
  FETCH get_player_crsr into pr_player_nme;
  EXIT WHEN get_player_crsr%NOTFOUND;
  var_player_nme := pr_player_nme;
  DBMS_OUTPUT.PUT_LINE('Fetching Player Name');
  DBMS_OUTPUT.PUT_LINE('DISPLAYING VAR_PLAYER_NME:  ' || var_player_nme);
  DBMS_OUTPUT.PUT_LINE('BeforeFetching Player CALC');
  FETCH fig_player_CALC_crsr into  pr_CALC_nbr;
  DBMS_OUTPUT.PUT_LINE('PR_CALC_NBR:  ' || pr_CALC_nbr);
  DBMS_OUTPUT.PUT_LINE ('------------------');
  DBMS_OUTPUT.PUT_LINE(pr_player_nme);
  DBMS_OUTPUT.PUT_LINE(pr_CALC_nbr);
END LOOP;
end;


===The Submission Output======================

Code: Select all

SQL> exec proc_test_hdcp_3
Before Fetching Player Name
Fetching Player Name
DISPLAYING VAR_PLAYER_NME:  Doe_John
BeforeFetching Player CALC
PR_CALC_NBR:
------------------
Doe_John
Before Fetching Player Name

PL/SQL procedure successfully completed.

[Tim...]

Hi.

First, please use the CODE tags around formatted text. Your code was pretty much unreadable before I put the tags around it.

You seem to misunderstand how to use cursors. You have opened both cursor immediately. At the time you opened the second cursor, the value of the variable was NULL, so that is what the cursor will have used.

Using explicit cursors, like you've done, is actually slower, and helps lead to this sort of confusion. I'm assuming that for each row returned from the first cursor you want to return a row from the second cursor. I would do this as follows.

Code: Select all

create or replace procedure "PROC_TEST_CALC_3"
is
  pr_CALC_nbr     score.tot_score%TYPE;
begin
  FOR cur_player IN (SELECT DISTINCT player_nme
                    FROM   score
                    WHERE  player_nme = 'Doe_John')
  LOOP
   DBMS_OUTPUT.PUT_LINE('DISPLAYING VAR_PLAYER_NME:  ' || cur_player.player_nme);
   DBMS_OUTPUT.PUT_LINE('BeforeFetching Player CALC');

   SELECT (ROUND(AVG(tot_score),0) - 72) as CALC
   INTO   pr_CALC_nbr
   FROM  (
            SELECT *
            from     (SELECT player_nme, yr, rnd_nbr, tot_score
                         FROM    (SELECT *
                                       FROM  ( SELECT player_nme, yr, rnd_nbr, tot_score
                                                   FROM score
                                                  WHERE player_nme = var_player_nme
                                                             and yr <= 2011
                                                  ORDER BY yr desc, rnd_nbr desc )
                                       WHERE rownum <= 20 )
                         WHERE player_nme = var_player_nme
                         ORDER BY tot_score )
   WHERE rownum <= 10
             )
   GROUP BY player_nme;
 
   DBMS_OUTPUT.PUT_LINE('PR_CALC_NBR:  ' || pr_CALC_nbr);
 END LOOP;
end;

Having said that, it's clear from your comments that your code is not going to stay this way and the inner loop will eventually return multiple rows. In that case, I would take this approach.

Code: Select all

-- Loop through the outer query.
FOR outer_rec IN (SELECT id, ....)
LOOP

  -- I now have access to the values of the select list of the outer
  -- query using the look index. outer_rec.id

  -- Loop through the inner query.
  FOR inner_rec IN (SELECT value1...
                    ...
                    WHERE parent_id = outer_rec.id)
  LOOP
 
    -- Now I have access to the current row of the outer loop
    -- outer_rec.id
   
    -- And the current row of the inner loop.
    -- inner_rec.value1
  END LOOP;
END LOOP;

That's much easier to read in my opinion.

Cheers

Tim...