Oracle PL/SQL:如何从各种REF的DEREF?

问题描述

我是Oracle Objects的新手，但遇到了问题.我不知道如何从REF的VARRAY中取消引用项目.下面是一些源代码，可重现我所遇到的问题.错误是:PLS-00306:调用'DEREF'时参数的数量或类型错误

I'm new to Oracle Objects and I have a problem. I don't know how to dereference an item from a VARRAY of REFs. Below is some source code that reproduces the problem that I have.The error is: PLS-00306: Wrong number or types of arguments in call to 'DEREF'

DROP TYPE LOC FORCE
/
DROP TYPE LIST_LOC FORCE
/
DROP TYPE PIZ FORCE
/

CREATE OR REPLACE TYPE LOC AS OBJECT(
  NAME      VARCHAR2(30),
  MEMBER FUNCTION GET_NAME RETURN VARCHAR2
)
/

CREATE OR REPLACE TYPE BODY LOC AS
  MEMBER FUNCTION GET_NAME RETURN VARCHAR2 IS
  BEGIN
    RETURN SELF.NAME;
  END;
END;
/

CREATE OR REPLACE TYPE LIST_LOC AS VARRAY(10) OF REF LOC
/

CREATE OR REPLACE TYPE PIZ AS OBJECT(
  LOCS      LIST_LOC,
  MEMBER PROCEDURE DISPLAY_LOCS
)
/

CREATE OR REPLACE TYPE BODY PIZ AS
  MEMBER PROCEDURE DISPLAY_LOCS IS
  BEGIN
    FOR IDX IN SELF.LOCS.FIRST..SELF.LOCS.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(DEREF(SELF.LOCS(IDX)).GET_NAME()); --this is the line that generates the error
    END LOOP;
  END;
END;
/

当我尝试从LOCS varray获取IDX位置上的REF并对其进行DEREF以获取名称时，该错误出现在DISPLAY_LOCS过程中.

The error appears in DISPLAY_LOCS procedure, when I try to get the REF at position IDX from the LOCS varray, and to DEREF it to get the name.

推荐答案

DEREF必须在SQL语句中: http://download.oracle.com/docs /cd/B28359_01/appdev.111/b28371/adobjbas.htm#i463707

the DEREF must be in a SQL Statement:http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjbas.htm#i463707

这有效

CREATE OR REPLACE TYPE BODY PIZ AS
  MEMBER PROCEDURE DISPLAY_LOCS IS
  x varchar2(30) ;
  BEGIN
    FOR IDX IN SELF.LOCS.FIRST..SELF.LOCS.LAST LOOP
        select DEREF(SELF.LOCS(IDX)).GET_NAME() into x from dual ;
      DBMS_OUTPUT.PUT_LINE(x); --this is the line that generates the error
    END LOOP;
  END;
END;
/

很好的测试用例可以重现！

nice test case to reproduce!

这篇关于Oracle PL/SQL:如何从各种REF的DEREF?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！