PL/SQL返回类型的结果集变量或查询不匹配

Question

我想使用GET存储过程/光标来显示我在彩票游戏数据库中创建的彩票游戏表的游戏名称。

下面是代码：

CREATE OR REPLACE PROCEDURE GetLotteryGameName (
    p_lgid IN NUMBER, 
    p_value out VARCHAR2, 
    p_field IN VARCHAR2 
) 
IS 
BEGIN 
    SELECT GAMENAME 
    INTO p_value 
    FROM LOTTERYGAME 
    WHERE LOTTERYGAMEID = p_lgid; 
END GetLotteryGameName; 

CREATE OR REPLACE PACKAGE GETLOTTERYGAMENAMEPKG IS 
    PROCEDURE GetLotteryGameName (
    p_lgid IN NUMBER, 
    p_value out VARCHAR2, 
    p_field IN VARCHAR2 
); 

    TYPE per_ref_cursor IS REF CURSOR; 
    PROCEDURE GetGameName (p_lgid IN NUMBER, p_ref OUT per_ref_cursor); 

END GETLOTTERYGAMENAMEPKG; 

CREATE OR REPLACE PACKAGE BODY GETLOTTERYGAMENAMEPKG IS 
    PROCEDURE GetLotteryGameName (
    p_lgid IN NUMBER, 
    p_value out VARCHAR2, 
    p_field IN VARCHAR2 
) 
    IS 
    BEGIN 
    SELECT GAMENAME 
    INTO p_value 
    FROM LOTTERYGAME 
    WHERE LOTTERYGAMEID = p_lgid; 
    END GetLotteryGameName; 

    PROCEDURE GetName 
    (p_lgid IN NUMBER, 
    p_ref OUT per_ref_cursor) IS 
    BEGIN 
     OPEN p_ref FOR 
     SELECT GAMENAME 
     FROM LOTTERYGAME 
     WHERE LOTTERYGAMEID = p_lgid; 
    END GetName; 

END GETLOTTERYGAMENAMEPKG; 

DECLARE 
    v_cursor GETLOTTERYGAMENAMEPKG.per_ref_cursor; 
    v_lgid  LOTTERYGAME.LOTTERYGAMEID%TYPE; 
    v_gamename LOTTERYGAME.GAMENAME%TYPE; 

BEGIN 
    GETLOTTERYGAMENAMEPKG.GetName (p_lgid = 2, 
           p_ref => v_cursor); 
    LOOP 
    FETCH v_cursor 
    INTO v_lgid, v_gamename; 
    EXIT WHEN v_cursor%NOTFOUND; 
    DBMS_OUTPUT.PUT_LINE(v_lgid || ',' || v_gamename); 
    END LOOP; 
    CLOSE v_cursor; 
END; 

当我运行上面的声明语句，我得到以下错误：

06504. 00000 - "PL/SQL: Return types of Result Set variables or query do not match" 
*Cause: Number and/or types of columns in a query does not match declared 
      return type of a result set variable, or declared types of two Result 
      Set variables do not match. 

我知道有这个代码的某处错误，我只是不知道我在找什么，或者如何设置自己以避免像这样的未来失败。

任何帮助是非常赞赏，

干杯，

FBF

Answer 1

由于错误说，你只SELECT在GAMENAME您SELECT而是试图投入v_lgid, v_gamename。

因此增加LOTTERYGAMEID您CURSOR's选择

或更改您的匿名块。

PROCEDURE GetName 
    (p_lgid IN NUMBER, 
    p_ref OUT per_ref_cursor) IS 
    BEGIN 
     OPEN p_ref FOR 
     SELECT GAMENAME,LOTTERYGAMEID 
     FROM LOTTERYGAME 
     WHERE LOTTERYGAMEID = p_lgid; 
    END GetName; 

Answer 2

你也应该纠正语法错误：

GETLOTTERYGAMENAMEPKG.GetName (p_lgid = 2, 
           p_ref => v_cursor); 

应阅读

GETLOTTERYGAMENAMEPKG.GetName (p_lgid => 2, 
           p_ref => v_cursor); 

分享和享受。