ITPub博客

首页 > 数据库 > Oracle > Oracle 嵌套表(转)

Oracle 嵌套表(转)

原创 Oracle 作者:zzh481899 时间:2008-01-11 19:05:07 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: PLSQL计算质数(转)
下一篇: oracle嵌套表
请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    11
  • 访问量
    427422