create table realhot(
hotid number(10) not null primary key, /*主键,自动增加*/
address array ///此列为array类型,类似[1000,1001,1002]
);
下面是一个oracle嵌套表的具体例子,注意如何操作和java没什么关系,只要能按照要求生成sql语句即可。
-- 1. 创建自定义数据类型
create or replace type obj_tag as object(
tag_name varchar2(30)
);
CREATE TYPE list_tag IS TABLE OF obj_tag;
-- 2. 创建包含嵌套表的表对象
CREATE TABLE t_test_user(
user_id INTEGER,
user_name VARCHAR2(30),
user_tags list_tag
)
NESTED TABLE user_tags STORE AS user_tags_tab;
-- 3. 插入整条记录
INSERT INTO t_test_user VALUES(
1,
'adam',
list_tag(obj_tag('宅男'), obj_tag('驴友'), obj_tag('高颜值'))
);
INSERT INTO t_test_user VALUES(
2,
'jack',
list_tag(obj_tag('阳光男孩'), obj_tag('吐槽派'))
);
commit;
-- 4. 嵌套表整体插入
INSERT INTO THE(SELECT user_tags FROM t_test_user WHERE user_id = 1)
VALUES ('IT女强人');
INSERT INTO THE(SELECT user_tags FROM t_test_user WHERE user_id = 2)
VALUES ('游戏发烧友');
commit;
-- 5. 嵌套表部分内容删除
DELETE FROM THE(SELECT user_tags FROM t_test_user WHERE user_id = 1)
WHERE tag_name = '宅男';
COMMIT;
-- 6. 嵌套表的查询
SELECT t_user.user_id, t_user.user_name, t_tags.tag_name
FROM t_test_user t_user, TABLE(user_tags) t_tags;
-- 只查询嵌套表,注意the只查询只能返回一条记录,否则报错
SELECT *
FROM THE(SELECT user_tags FROM t_test_user WHERE user_id = 2);