Oracle 嵌套表(转)

Oracle 嵌套表

2006-9-3 11:15:00 By:xling

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

[@more@]Oracle 嵌套表(转)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9279504/viewspace-997129/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9279504/viewspace-997129/