Oracle使用包中定义的类型作为记录添加到已定义类型的表中(Oracle use defined type in package as record added to table of define

Oracle使用包中定义的类型作为记录添加到已定义类型的表中(Oracle use defined type in package as record added to table of defined type)

我有以下软件包。

CREATE OR REPLACE PACKAGE offer_actions AS TYPE opis_oferty_type IS RECORD (nazwa varchar(11), rocznik number(4), cena decimal(10,2), imie_klienta varchar(32), nazwisko_klienta varchar(32)); TYPE opis_ofert_table IS TABLE OF varchar(100); komis_id komisy.idk%TYPE :=0; CURSOR c_oferty RETURN opis_oferty_type; FUNCTION find_oferrs(komis_id number) RETURN opis_ofert_table; END offer_actions; / CREATE OR REPLACE PACKAGE BODY offer_actions AS CURSOR c_oferty RETURN opis_oferty_type IS SELECT mar.nazwa, sze.rok_produkcji, ofe.cena_aktualna, kli.imie, kli.nazwisko FROM oferty ofe, szczegoly_oferty sze, modele modd, marki mar, klienci kli WHERE ofe.klient_id = kli.idk AND sze.oferta_id = ofe.idk AND ofe.model_id = modd.idk AND modd.marka_id = mar.idk AND ofe.komis_id = komis_id; FUNCTION find_oferrs(komis_id number) RETURN opis_ofert_table IS l_offers opis_ofert_table := opis_ofert_table(); BEGIN FOR i in c_oferty LOOP l_offers.EXTEND; l_offers(l_offers.COUNT) := i.nazwa; END LOOP; RETURN l_offers; END find_oferrs; END offer_actions;

当我运行脚本时，出现以下错误。

PLS-00222: no function with name 'OPIS_OFERTY_TYPE' exists in this scope

如何定义opis_oferty_type以允许在操作中添加新记录

l_offers(l_offers.COUNT) := (opis_oferty_type(i.nazwa,i.rocznik,i.cena,i.imie_klienta, i.nazwisko_klienta));

声明的类型。

CREATE OR REPLACE TYPE opis_oferty_type AS OBJECT (nazwa varchar(11), rocznik number(4), cena decimal(10,2), imie_klienta varchar(32), nazwisko_klienta varchar(32) ); / CREATE OR REPLACE TYPE opis_ofert_table AS TABLE OF opis_oferty_type;

I have the following package.

CREATE OR REPLACE PACKAGE offer_actions AS TYPE opis_oferty_type IS RECORD (nazwa varchar(11), rocznik number(4), cena decimal(10,2), imie_klienta varchar(32), nazwisko_klienta varchar(32)); TYPE opis_ofert_table IS TABLE OF varchar(100); komis_id komisy.idk%TYPE :=0; CURSOR c_oferty RETURN opis_oferty_type; FUNCTION find_oferrs(komis_id number) RETURN opis_ofert_table; END offer_actions; / CREATE OR REPLACE PACKAGE BODY offer_actions AS CURSOR c_oferty RETURN opis_oferty_type IS SELECT mar.nazwa, sze.rok_produkcji, ofe.cena_aktualna, kli.imie, kli.nazwisko FROM oferty ofe, szczegoly_oferty sze, modele modd, marki mar, klienci kli WHERE ofe.klient_id = kli.idk AND sze.oferta_id = ofe.idk AND ofe.model_id = modd.idk AND modd.marka_id = mar.idk AND ofe.komis_id = komis_id; FUNCTION find_oferrs(komis_id number) RETURN opis_ofert_table IS l_offers opis_ofert_table := opis_ofert_table(); BEGIN FOR i in c_oferty LOOP l_offers.EXTEND; l_offers(l_offers.COUNT) := i.nazwa; END LOOP; RETURN l_offers; END find_oferrs; END offer_actions;

When I run script I get the following error.

PLS-00222: no function with name 'OPIS_OFERTY_TYPE' exists in this scope

How can I define opis_oferty_type to allow adding new record in operation

l_offers(l_offers.COUNT) := (opis_oferty_type(i.nazwa,i.rocznik,i.cena,i.imie_klienta, i.nazwisko_klienta));

Declared types.

CREATE OR REPLACE TYPE opis_oferty_type AS OBJECT (nazwa varchar(11), rocznik number(4), cena decimal(10,2), imie_klienta varchar(32), nazwisko_klienta varchar(32) ); / CREATE OR REPLACE TYPE opis_ofert_table AS TABLE OF opis_oferty_type;

最满意答案

您正尝试使用记录类型，如对象类型。 不可能。 仅在SQL级别声明对象类型，仅在PL / SQL级别记录类型。 要么使用create type ... as object在SQL中创建类型opis_oferty_type ，要么继续使用记录类型，而是初始化记录的每个字段，如下所示：

Declare l_v opis_oferty_type; ... Begin ... l_v.nazwa:= ...; l_v.rocznik:= ...; ... l_offers(l_offers.COUNT) := l_v;

以下是一对供参考的链接： 声明对象类型 ， 记录变量的初始值

You are trying to use a record type like an object type. It's impossible. Object types declared only at SQL level, record types only at PL/SQL level. Either create type opis_oferty_type in SQL using create type ... as object, or continue to use a record type, but initialize every field of a record, like that:

Declare l_v opis_oferty_type; ... Begin ... l_v.nazwa:= ...; l_v.rocznik:= ...; ... l_offers(l_offers.COUNT) := l_v;

Here's a pair of links for reference: Declaring Object Types, Initial Values of Record Variables