巢狀表和VARRAY (DML 十五)

※巢狀表

巢狀表就是欄位模擬成table，所以不用join，可以提升效能，Oracle8以上才有，但其他資料庫沒有，所以斟酌使用

※宣告一個巢狀表並使用

CREATE OR REPLACE TYPE xxx_nested IS TABLE OF VARCHAR2(10) NOT NULL;
    
DROP TABLE testnested PURGE;
CREATE TABLE testnested(
    PK NUMBER,
    NNAME VARCHAR2(10) NOT NULL,
    NEST xxx_nested,
    CONSTRAINT TESTNESTED_PK PRIMARY KEY(PK)
) NESTED TABLE NEST STORE AS PROJECTS_NESTED_TABLE;
    
SELECT * FROM testnested;

※IS TABLE OF或AS都可以，但有些地方不能使用AS

※巢狀表的增刪改查

INSERT INTO testnested(PK, NNAME, NEST)VALUES(
    1, 'fruit', xxx_nested('banana', 'apple', 'blueberry')
);
    
INSERT INTO testnested VALUES(
    2, 'animal', xxx_nested('tiger', 'duck', 'elephant')
);
    
SELECT * FROM TABLE (
    SELECT nest FROM testnested WHERE pk = 2
);
    
UPDATE TABLE (
    SELECT nest FROM testnested WHERE pk = 2
) tn 
SET VALUE(tn) = 'cat'
WHERE tn.COLUMN_VALUE = 'tiger';
    
DELETE FROM TABLE(
    SELECT nest FROM testnested WHERE pk = 2
) tn 
WHERE tn.COLUMN_VALUE = 'cat';

※SELECT nest FROM testnested WHERE pk = 2沒辦法一個欄位一個值
Oracle SQL Developer看到的畫面是集合的值，不是一個欄位一個值

PL/SQL Developer看到的畫面是這樣

必須點「…」，才會跑出上面的畫面，所以使用上面的程式碼，可以直接顯示上面的畫面

※巢狀表自定型態

CREATE OR REPLACE TYPE many_nested AS OBJECT(
    mid number,
    mname varchar2(5),
    mdate date
);
    
CREATE OR REPLACE TYPE xxx_nested IS TABLE OF many_nested NOT NULL;
    
DROP TABLE testnested PURGE;
CREATE TABLE testnested(
    PK NUMBER,
    NNAME VARCHAR2(10) NOT NULL,
    NEST xxx_nested,
    CONSTRAINT TESTNESTED_PK PRIMARY KEY(PK)
) NESTED TABLE NEST STORE AS PROJECTS_NESTED_TABLE;

※自定一個many_nested，然後使用，create table 都和上面一樣

※巢狀表自定型態的增刪改查

INSERT INTO testnested(PK, NNAME, NEST)VALUES(
    1, 'fruit',
    xxx_nested(
        many_nested(10, 'A1', TO_DATE('1950-01-01', 'YYYY-MM-DD')),
        many_nested(20, 'A2', TO_DATE('1960-12-31', 'YYYY-MM-DD'))
    )
);
    
INSERT INTO testnested VALUES(
    2, 'animal',
    xxx_nested(
        many_nested(30, 'duck', TO_DATE('1980-01-01', 'YYYY-MM-DD')),
        many_nested(40, 'tiger', TO_DATE('1990-12-31', 'YYYY-MM-DD'))
    )
);
    
UPDATE TABLE (
    SELECT nest FROM testnested WHERE pk = 2
) tn
SET VALUE(tn) = many_nested(40, 'cat', TO_DATE('1990-12-31', 'YYYY-MM-DD'))
WHERE tn.MNAME = 'tiger';
    
DELETE FROM TABLE(
    SELECT nest FROM testnested WHERE pk = 2
) tn
WHERE tn.MNAME = 'cat';

※修改其中一個值還是要全部都打出來，比較麻煩，如果用如下的語法只有MNAME有值

UPDATE TABLE (
    SELECT nest FROM testnested WHERE pk = 2
) tn
SET VALUE(tn) = many_nested(
    (
        SELECT mid FROM TABLE (
            SELECT nest FROM testnested WHERE pk = 2
        ) where mname = 'cat'
    ),
    'cat',
    (
        SELECT mdate FROM TABLE (
            SELECT nest FROM testnested WHERE pk = 2
        ) where mname = 'cat'
    )
)
WHERE tn.MNAME = 'tiger';

※MID和MDATE莫名的變成空的

※在PL/SQL操作巢狀表和VARRAY看這篇

※VARRAY

和巢狀表差不多，但可以限制長度

※VARRAY的宣告

CREATE OR REPLACE TYPE xxx_varray IS VARRAY(2) OF VARCHAR2(10);
    
CREATE TABLE TEST_VARRAY(
    pk NUMBER,
    vname VARCHAR(10),
    xxx xxx_varray,
    CONSTRAINT VARRAY_PK PRIMARY KEY (pk)
);

※宣告一個長度為2的VARRAY

※VARRAY的增刪改查

INSERT INTO test_varray VALUES (1, 'animal', xxx_varray('duck', 'tiger'));
INSERT INTO test_varray VALUES (2, 'fruit', xxx_varray('apple', 'banana'));
    
SELECT * FROM TABLE(
    SELECT xxx FROM test_varray WHERE pk = 2
);
    
UPDATE test_varray SET vname = 'car', xxx = xxx_varray('benz') WHERE pk = 2;
    
DELETE FROM test_varray WHERE pk = 2;