oracle 嵌套表 例子,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