在PL/SQL块中的SQL中使用嵌套表变量/集合

问题描述：

我首先创建一个address_type对象

CREATE TYPE address_type AS OBJECT
( line1                         VARCHAR2(100)
, line2                         VARCHAR2(100)
, line3                         VARCHAR2(100)
, city                          VARCHAR2(50)
, state                         VARCHAR2(50)
, country                       VARCHAR2(50)
, zip                           VARCHAR2(10)
);
/

我创建上述对象的嵌套表类型.

I create a nested table type of the above object.

CREATE TYPE address_table AS TABLE OF ADDRESS_TYPE;
/

然后我创建另一个对象，如下所示:

I then create another object as follows:

CREATE TYPE telephone_number_type AS OBJECT
( country_code                  VARCHAR2(4)
, area_code                     VARCHAR2(10)
, phone_number                  VARCHAR2(10)
, extension                     VARCHAR2(10)
, number_type                   VARCHAR2(10)
);
/

然后我创建一个嵌套表类型，如下所示:

And then I create a nested table type as follows:

CREATE TYPE telephone_number_table AS TABLE OF TELEPHONE_NUMBER_TYPE;
/

现在，我创建一个名为person的表.除了telephone_numbers列是嵌套表telephone_number_table类型之外，许多其他列在此问题中用处不大.

Now I create a table named person. Many of whose columns are not much useful in this question, except for the telephone_numbers column which is of nested table telephone_number_table type.

CREATE TABLE person
( personid                       INTEGER          PRIMARY KEY
, fname                          VARCHAR2(50)     NOT NULL
, mname                          VARCHAR2(50)
, lname                          VARCHAR2(50)     NOT NULL
, email                          VARCHAR2(255)    UNIQUE
, password                       VARCHAR2(255)    NOT NULL
, birthdate                      DATE
, billing_address                ADDRESS_TABLE
, delivery_address               ADDRESS_TABLE
, telephone_numbers              TELEPHONE_NUMBER_TABLE
, display_pic                    BLOB
, ts_registration                TIMESTAMP
, ts_verification                TIMESTAMP
, ts_last_updated                TIMESTAMP
) NESTED TABLE billing_address STORE AS nt_billing_address
, NESTED TABLE delivery_address STORE AS nt_delivery_address
, NESTED TABLE telephone_numbers STORE AS nt_telephone_numbers
, LOB(display_pic) STORE AS SECUREFILE (
  TABLESPACE users
  ENABLE STORAGE IN ROW
  CHUNK 4096
  PCTVERSION 20
  NOCACHE
  NOLOGGING
  COMPRESS HIGH
)
;

然后我为此创建一个序列:

I then create a sequence for this:

CREATE SEQUENCE sq_personid;

要将值插入到person表中，我使用一个匿名块，如下所示:

To insert values into the person table I use an anonymous block as follows:

DECLARE
  v_fname person.fname%TYPE := 'Yogeshwar';
  v_mname person.mname%TYPE := '';
  v_lname person.lname%TYPE := 'Rachcha';
  v_email person.email%TYPE := 'yogeshrachcha@gmail.com';
  v_password person.password%TYPE := 'mail_123';
  v_birthdate person.birthdate%TYPE := TO_DATE('28-03-1987', 'DD-MM-YYYY');
  v_telephone_numbers TELEPHONE_NUMBER_TABLE;
  v_billing_address   ADDRESS_TABLE;
  v_delivery_address  ADDRESS_TABLE;
BEGIN
  v_telephone_numbers := TELEPHONE_NUMBER_TABLE
                           ( TELEPHONE_NUMBER_TYPE('+91','22','123456','','Residence')
                                                   , TELEPHONE_NUMBER_TYPE('+91','22','456798','123','Office')
                                                   , TELEPHONE_NUMBER_TYPE('+91','','1234567890','','Mobile'));

  v_billing_address := ADDRESS_TABLE (ADDRESS_TYPE ( 'Line 1', 'Line 2', 'Line 3', 'Mumbai', 'Maharashtra', 'India', '123456'));
  v_delivery_address := ADDRESS_TABLE (ADDRESS_TYPE ( 'Line 1', 'Line 2', 'Line 3', 'Mumbai', 'Maharashtra', 'India', '123456'));
  -- billing and delivery addresses are the same. These are not much importance in this question.

  INSERT INTO person VALUES
  ( sq_personid.nextval
  , v_fname
  , v_mname
  , v_lname
  , v_email
  , v_password
  , v_birthdate
  , v_billing_address
  , v_delivery_address
  , v_telephone_numbers
  , NULL
  , sysdate
  , sysdate
  , sysdate);

END;

一切到此为止都是绝对完美的.然后，在如下所示的匿名块中，我尝试创建一个嵌套表变量，并在SQL查询中使用它:

Everything runs absolutely perfect till this point. Then in an anonymous block like the following, I try to create a nested table variable and use it in an SQL query:

DECLARE
  TYPE t_country_codes IS TABLE OF VARCHAR2(4);
  country_codes T_COUNTRY_CODES := T_COUNTRY_CODES('+1', '+44', '+91');
  cc VARCHAR2(4);
BEGIN
  FOR i IN (SELECT t.country_code
                          FROM person p
                          CROSS JOIN TABLE(p.telephone_numbers) t
                         WHERE t.country_code IN (SELECT COLUMN_VALUE -- I doubt the problem is with this SELECT statement.
                                                    FROM TABLE(country_codes))) LOOP
        dbms_output.put_line(i.country_code);
  END LOOP;
END;
/

我收到此错误:

ORA-06550: line 8, column 70:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 8, column 64:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item

答

嵌套表类型可以在SQL中声明(通过CREATE TYPE语句，就像对telephone_number_table类型所做的那样)或在PL/SQL中声明(通过DECLARE块上的TYPE声明).如果在PL/SQL中声明类型，则不能在SQL中使用该类型的实例.您需要在SQL中声明该类型，才能在SQL中使用该类型的实例.

A nested table type can be declared either in SQL (via the CREATE TYPE statement like you did with the telephone_number_table type) or in PL/SQL (via the TYPE declaration on the DECLARE block). If you declare the type in PL/SQL, you cannot use an instance of the type in SQL. You would need to declare the type in SQL in order to use an instance of the type in SQL.

在PL/SQL块中的SQL中使用嵌套表变量/集合

相关推荐