集合-巢狀表和VARRAY (PL/SQL 六)

※巢狀表

在DML的巢狀表和VARRAY看這篇，這裡是在PL/SQL裡操作巢狀表

※使用迴圈取值

DECLARE
    TYPE xxx_nested IS TABLE OF VARCHAR2(10) NOT NULL;
    xxx xxx_nested := xxx_nested('banana', 'apple', 'blueberry');
BEGIN
    -- FOR x IN 1..xxx.COUNT LOOP
    -- FOR x IN xxx.FIRST..xxx.COUNT LOOP
    FOR i IN xxx.FIRST..xxx.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(xxx(i));
    END LOOP;
END;

※結果：
banana
apple
blueberry

※可以使用FIRST開始，LAST結束，而結束的數量也等於COUNT數

※增加到資料庫

CREATE OR REPLACE TYPE xxx_nested IS TABLE OF VARCHAR2(10) NOT NULL;
    
DECLARE
    xxx xxx_nested := xxx_nested('banana', 'apple', 'blueberry');
BEGIN
    FOR x IN xxx.FIRST..xxx.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(xxx(x));
    END LOOP;
END;

※上面的declare版是測試用，正式版就要變成CREATE或CREATE OR REPLACE才會增加到資料庫裡，多OR REPLACE差在如果資料庫已有會覆蓋，軟體的畫面要在如下的畫面看：

如果要從資料庫刪除，可下「DROP TYPE type名稱」，或按右鍵DROP就可，但要確保Table裡和其他Type等，都沒用到才不會出錯誤訊息

※巢狀表的增刪改查

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;
    
    
DECLARE
    ooo testnested%ROWTYPE;
BEGIN
    ooo.pk := 10;
    ooo.nname := 'fruit';
    ooo.nest := xxx_nested('banana', 'apple', 'blueberry');
    
    INSERT INTO testnested VALUES ooo;
    DBMS_OUTPUT.PUT_LINE('新增成功！');
END;
    
    
--查詢
SELECT * FROM TABLE (
    SELECT nest FROM testnested WHERE pk = 10
);
    
    
DECLARE
    ooo xxx_nested := xxx_nested('strawberry', 'apple');
BEGIN
    UPDATE testnested SET nest = ooo WHERE pk = 10;
    DBMS_OUTPUT.PUT_LINE('修改成功！');
END;
    
    
DECLARE
    ooo xxx_nested := xxx_nested('strawberry', 'apple');
BEGIN
    DELETE FROM testnested where pk = 10;
    DBMS_OUTPUT.PUT_LINE('刪除成功！');
END;

※修改和DML不一樣，可以針對想修改的部分操作即可

※巢狀表自定型態

CREATE OR REPLACE TYPE many_nested AS OBJECT(
    mid number,
    mname varchar2(5),
    mdate date
);
    
DECLARE
    TYPE xxx_nested IS TABLE OF many_nested NOT NULL;
    ooo xxx_nested := 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'))
    );
BEGIN
    FOR i IN ooo.FIRST..ooo.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('mid=' || ooo(i).mid);
        DBMS_OUTPUT.PUT_LINE('mname=' || ooo(i).mname);
        DBMS_OUTPUT.PUT_LINE('mdate=' || ooo(i).mdate || CHR(10));
    END LOOP;
END;

※自定一個many_nested，然後使用

※增加到資料庫

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;
    
CREATE OR REPLACE TYPE xxx_nested IS TABLE OF many_nested NOT NULL;

※先建一張表，然後增加到資料庫

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

DECLARE
    xxx testnested%ROWTYPE;
BEGIN
    xxx.pk := 22;
    xxx.nname := 'animal';
    xxx.nest := 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'))
    );
    
    INSERT INTO testnested VALUES xxx;
    DBMS_OUTPUT.PUT_LINE('新增成功！');
END;
    
    
--查詢
SELECT * FROM TABLE (
    SELECT nest FROM testnested WHERE pk = 22
);
    
    
DECLARE
    ooo xxx_nested := xxx_nested(
        many_nested(30, 'lion', TO_DATE('2000-10-10', 'YYYY-MM-DD')),
        many_nested(40, 'tiger', TO_DATE('1990-12-31', 'YYYY-MM-DD'))
    );
BEGIN
    UPDATE testnested SET nname = 'zoo', nest = ooo WHERE PK = 22;
    DBMS_OUTPUT.PUT_LINE('修改成功！');
END;
    
    
DECLARE
BEGIN
    DELETE FROM testnested WHERE PK = 22;
    DBMS_OUTPUT.PUT_LINE('刪除成功！');
END;

※VARRAY

※宣告VARRAY

DECLARE 
    TYPE xxx_varray IS VARRAY(2) OF VARCHAR2(10); 
    xxx xxx_varray := xxx_varray('cat', 'dog');
BEGIN
    xxx(1) := 'elephant';
    
    FOR i IN xxx.FIRST..xxx.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(xxx(i));
    END LOOP;
END;

※結果：
elephant
dog

※VARRAY的增刪改查

DECLARE 
    TYPE xxx_varray IS VARRAY(2) OF many_nested; 
    xxx xxx_varray := xxx_varray(
        many_nested(1, 'fruit', to_date('1990-10-10', 'yyyy-mm-dd')),
        many_nested(2, 'zoo', to_date('1999-05-07', 'yyyy-mm-dd'))
    );
BEGIN
    FOR i IN xxx.FIRST..xxx.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(xxx(i).mid);
        DBMS_OUTPUT.PUT_LINE(xxx(i).mname);
        DBMS_OUTPUT.PUT_LINE(xxx(i).mdate || CHR(10));
    END LOOP;
END;

※結果：
1
fruit
10-OCT-90

2
zoo
07-MAY-99