oracle - 在 PL/SQL block 内的 SQL 中使用嵌套表变量/集合

Question

1. 我首先创建一个 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)
   );
   /
   

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

   CREATE TYPE address_table AS TABLE OF ADDRESS_TYPE;
   /
   

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

   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)
   );
   /
   

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

   CREATE TYPE telephone_number_table AS TABLE OF TELEPHONE_NUMBER_TYPE;
   /
   

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

   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
   )
   ;
   

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

   CREATE SEQUENCE sq_personid;
   

7. 要将值插入到 person 表中，我使用匿名 block ，如下所示:

   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;
   

到目前为止，一切都运行得绝对完美。然后，在如下所示的匿名 block 中，我尝试创建一个嵌套表变量并在 SQL 查询中使用它:

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

Answer 1

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