PL/SQL procedure with cursor and rowtype

Question

I am working in a games data base. I want to create a procedure which shows the games created between two dates. I am using a cursor and a rowtype like this:

CREATE OR REPLACE procedure p_games(v_date1 games.date%type, v_date2 games.date%type)
AS

    v_games games%rowtype;
   
    CURSOR checkGames IS
    SELECT * INTO v_games
    FROM games
    WHERE date BETWEEN v_date1 AND v_date2;
        
BEGIN
    FOR register IN checkGames   LOOP
        dbms_output.put_line(register.v_games);
    END LOOP;
END;
/

but when I run it the error is

PLS-00302: the component 'V_GAMES' must be declared.

Should I declare it in any other way?

Answer 1

Not exactly like that.

• you don't have to declare cursor variable as you're using a cursor FOR loop
• you don't select INTO while declaring a cursor; you would FETCH into if you used a different approach (see example below)

Sample table:

SQL> create table games
  2  (id     number,
  3   c_date date
  4  );

Table created.

SQL> insert into games (id, c_date) values (1, date '2022-04-25');

1 row created.

Your procedure, slightly modified:

SQL> CREATE OR REPLACE procedure p_games(v_date1 games.c_date%type, v_date2 games.c_date%type)
  2  AS
  3      CURSOR checkGames IS
  4      SELECT *
  5      FROM games
  6      WHERE c_date BETWEEN v_date1 AND v_date2;
  7
  8  BEGIN
  9      FOR register IN checkGames LOOP
 10          dbms_output.put_line(register.id);
 11      END LOOP;
 12  END;
 13  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> exec p_games(date '2022-01-01', date '2022-12-31');
1

PL/SQL procedure successfully completed.

SQL>

---

A different approach; as you can notice, a cursor FOR loop is way simpler as Oracle does most of the dirty job for you (opening the cursor, fetching from it, taking care about exiting the loop, closing the cursor):

SQL> CREATE OR REPLACE procedure p_games(v_date1 games.c_date%type, v_date2 games.c_date%type)
  2  AS
  3      CURSOR checkGames IS
  4      SELECT *
  5      FROM games
  6      WHERE c_date BETWEEN v_date1 AND v_date2;
  7
  8      v_games checkGames%rowtype;
  9  BEGIN
 10    open checkGames;
 11    loop
 12      fetch checkGames into v_games;
 13      exit when checkGames%notfound;
 14
 15      dbms_output.put_line(v_games.id);
 16    END LOOP;
 17    close checkGames;
 18  END;
 19  /

Procedure created.

SQL> set serveroutput on
SQL> exec p_games(date '2022-01-01', date '2022-12-31');
1

PL/SQL procedure successfully completed.

SQL>