oracle嵌套表测试记录

在数据库表,个别字段设置为嵌套表,实际使用的不多,看到SF上提问做的测试,先记下来备用。

-- 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);

Comments are closed.