我在游戲資料庫中作業。我想創建一個程式來顯示在兩個日期之間創建的游戲。我正在使用這樣的游標和行型別：
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”。
我應該以其他方式宣告嗎？
uj5u.com熱心網友回復：
不完全一樣。
- 您不必在使用游標
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>
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/478845.html