Oracle：在PLSQL中获取引用

我试过这样做.

类型声明：

CREATE TYPE Virus_ObjType AS OBJECT (
    name        VARCHAR2(50),description VARCHAR2(700)
);
/

CREATE TYPE Paper_ObjType AS OBJECT (
    id         NUMBER,title      VARCHAR(100),magazine   VARCHAR2(100),vol        NUMBER,num        NUMBER,year       NUMBER(4)
);
/

CREATE TYPE Virus_NestTabType AS TABLE OF REF Virus_ObjType;
/
CREATE TYPE Paper_NestTabType AS TABLE OF REF Paper_ObjType;
/

ALTER TYPE Virus_ObjType ADD ATTRIBUTE papers Paper_NestTabType CASCADE;
/
ALTER TYPE Paper_ObjType ADD ATTRIBUTE virus Virus_NestTabType CASCADE;
/

表格声明：

CREATE TABLE Virus_ObjTable OF Virus_ObjType (
    PRIMARY KEY(name))
NESTED TABLE papers STORE AS papersVirus_NestTable;

CREATE TABLE Paper_ObjTable OF Paper_ObjType (
    PRIMARY KEY (id))
NESTED TABLE virus STORE AS virus_NestTable;

CREATE GLOBAL TEMPORARY TABLE virus_aux (
    type varchar2(1),virus REF Virus_ObjType,paper REF Paper_ObjType)
ON COMMIT DELETE ROWS;
/

触发声明：

create or replace 
trigger Virus_Trigger_Before BEFORE INSERT ON Virus_ObjTable 
FOR EACH ROW 
DECLARE
    paper REF Paper_ObjType;
    virus REF Virus_ObjType;
BEGIN
    IF inserting THEN
        IF :new.papers IS NOT NULL THEN
            FOR i IN 1..:new.papers.COUNT LOOP
                -- getting the pointer of the papers
                SELECT refe INTO paper
                    FROM (SELECT REF(a) as refe FROM Paper_ObjTable a)
                    WHERE refe=:new.papers(i);

                SELECT REF(:NEW.OBJECT_ID) INTO virus FROM dual;
                INSERT INTO virus_aux VALUES ('i',virus,paper);
            END LOOP;
        END IF;
    END IF;
END;

如果有人想尝试使用数据：

INSERT INTO paper_objtable (id,title,magazine,vol,num,year) VALUES (1,'Que mala es la Gripe','Fuzzy Sets',1,1993);
INSERT INTO paper_objtable (id,year) VALUES (3,'La Gripe ataca de nuevo','Information Sciences',1996);
INSERT INTO virus_objtable
    (SELECT 'Gripe','Virus de la Gripe',CAST(COLLECT(REF(a)) AS Paper_NestTabType)
     FROM paper_objtable a);

但我得到了下一个错误：

ORA-01747: invalid user.table.column,table.column,or columns specification

谁能帮我？

SELECT MAKE_REF(virus_objtable,:new.object_id) INTO virus FROM dual;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!