在PL/SQL块中的SQL中使用嵌套表变量/集合 [英] Using Nested Table variables / Collections in SQL inside PL/SQL blocks
问题描述
-
我首先创建一个
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中使用嵌套表变量/集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
