带有游标和行类型的PL/SQL过程

Question

我在一个游戏数据库工作。我想创建一个程序，其中显示两个日期之间创建的游戏。我使用游标和行类型如下:

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

但是当我运行它时，错误是

PLS-00302:组件'V_GAMES'必须声明。

我应该以其他方式声明它吗?

Answer 1

不完全是这样。

• 你不需要声明游标变量因为你使用游标FOR循环
• 在声明游标时不选择INTO;如果你使用不同的方法，你将FETCH进入(见下面的例子)
样本表:

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.

您的程序，稍作修改:

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.

测试:

SQL> set serveroutput on
SQL> exec p_games(date '2022-01-01', date '2022-12-31');
1
PL/SQL procedure successfully completed.
SQL>

---

不同的方法;正如您所注意到的，游标FOR循环更简单，因为Oracle为您完成了大多数脏工作(打开游标，从中获取，注意退出循环，关闭游标):

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>