Oracle 巢狀表(轉)

Oracle中有兩種集合，分別是巢狀表和VARRAY
它們在儲存的資料型別方面具有相似之處，因為它們都可以包含物件，兩者的區別為：
巢狀表
．尺寸沒有限制。
．本質上是無序的
VARRAY
．尺寸必須固定，所有的例項尺寸相同。
．在過程化語言中可以作為有序陣列進行檢索但在Oracle內部看成單個不能分割的單元。
．儲存效率高。

--------------------------------------------------
--巢狀表
CREATE TYPE SCORE_NESTED AS TABLE OF NUMBER;

-------------------------------------------------
--在表中使用巢狀表　
CREATE TABLE PLAYER(
GUID NUMBER,
NAME VARCHAR2(20),
SCORE SCORE_NESTED
)
NESTED TABLE SCORE STORE AS PLAYER_SCORE;

CREATE OR REPLACE TRIGGER PLAYER_T_I1
BEFORE INSERT ON PLAYER REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
:NEW.GUID := XLING_PKG_TOOLS.F_GET_NEXTVAL('PLAYER');
END;

CREATE OR REPLACE TRIGGER PLAYER_T_D1
BEFORE UPDATE ON PLAYER REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
XLING_PKG_TOOLS.P_DELETE_GUID(:OLD.GUID);
END;

-------------------------------------------------
--向巢狀表中插入資料．
INSERT INTO PLAYER (NAME,SCORE) VALUES ('xling',SCORE_NESTED(100,100,98,105,90))
INSERT INTO PLAYER (NAME,SCORE) VALUES ('snow',SCORE_NESTED(105,98,90,100,90));

SELECT * FROM PLAYER
-------------------------------------------------
--追加資料
INSERT INTO TABLE(SELECT SCORE FROM PLAYER WHERE NAME = 'xling') VALUES (110);
--INSERT INTO TABLE(SELECT SCORE FROM PLAYER WHERE NAME = 'snow') VALUES (SCORE_NESTED(100,100));
--ORA-00932: 資料型別不一致: 應為 NUMBER, 但卻獲得 XLING.SCORE_NESTED
-------------------------------------------------
--選出套表資料
SELECT * FROM TABLE(SELECT SCORE FROM PLAYER WHERE NAME = 'xling')--SELECT * FROM TABLE(SELECT SCORE FROM PLAYER) 錯誤：ORA-01427:單行子查詢返回多行
-------------------------------------------------
--更新套表
UPDATE PLAYER SET SCORE = SCORE_NESTED(100,200) WHERE NAME = 'xling'
SELECT * FROM TABLE(SELECT SCORE FROM PLAYER WHERE NAME = 'xling');
-------------------------------------------------
--刪除套表
UPDATE PLAYER SET SCORE = NULL WHERE NAME = 'xling'SELECT * FROM TABLE(SELECT SCORE FROM PLAYER WHERE NAME = 'xling');
--INSERT INTO TABLE(SELECT SCORE FROM PLAYER WHERE NAME = 'xling') VALUES (200)
--由於以把記錄中的套表刪除了，所以會引發：ORA-22908: NULL 表值的參考
-------------------------------------------------
DROP TABLE PLAYER
DROP TYPE SCORE_NESTED

CREATE TYPE SCORE_TYPE AS OBJECT(
NO NUMBER,
ITEM VARCHAR2(60),
SCORE NUMBER
)

CREATE TYPE SCORE_NESTED AS TABLE OF SCORE_TYPE;

CREATE TABLE PLAYER(
GUID NUMBER,
NAME VARCHAR2(20),
SCORE SCORE_NESTED
)
NESTED TABLE SCORE STORE AS SCORE_NESTED_TABLE

CREATE OR REPLACE TRIGGER PLAYER_T_I1
BEFORE INSERT ON PLAYER REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
:NEW.GUID := XLING_PKG_TOOLS.F_GET_NEXTVAL('PLAYER');
END;

CREATE OR REPLACE TRIGGER PLAYER_T_D1
BEFORE UPDATE ON PLAYER REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
XLING_PKG_TOOLS.P_DELETE_GUID(:OLD.GUID);
END;

INSERT INTO PLAYER (NAME,SCORE) VALUES ('xling',SCORE_NESTED(
SCORE_TYPE(1,'上山',100),
SCORE_TYPE(2,'下海',100),
SCORE_TYPE(3,'摸魚',90),
SCORE_TYPE(4,'騎豬',80)
))
INSERT INTO PLAYER (NAME,SCORE) VALUES ('werewi',SCORE_NESTED(
SCORE_TYPE(1,'泡妞',100),
SCORE_TYPE(2,'考研',150),
SCORE_TYPE(3,'摸魚',90)
))

SELECT * FROM TABLE(SELECT SCORE FROM PLAYER WHERE NAME = 'xling');
SELECT * FROM TABLE(SELECT SCORE FROM PLAYER WHERE NAME = 'werewi');

SELECT 'xling',S.ITEM,S.SCORE FROM TABLE(SELECT SCORE FROM PLAYER WHERE NAME = 'xling') S
UNION ALL
SELECT 'werewi',S.ITEM,S.SCORE FROM TABLE(SELECT SCORE FROM PLAYER WHERE NAME = 'werewi') S

---------------------------------------------------------------------------------------------------
/*
SET SERVEROUTPUT ON
DECLARE
V_NAME VARCHAR2(20);
V_SQL VARCHAR2(32767);
BEGIN
V_SQL := '';
FOR CC IN (SELECT NAME FROM PLAYER) LOOP
V_SQL := V_SQL || 'SELECT ''' || CC.NAME || ''',S.ITEM,S.SCORE FROM TABLE(SELECT SCORE FROM PLAYER WHERE NAME = '''|| CC.NAME ||''') S UNION ALL ';
END LOOP;
V_SQL := V_SQL || 'SELECT NULL,NULL,NULL FROM DUAL';
DBMS_OUTPUT.PUT_LINE(V_SQL);

EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;
*/
----------------------------------------------------------------------------------------------------
-- 選出所有記錄
SELECT
GUID,NAME,S.*
FROM
PLAYER P,
TABLE(P.SCORE) S