前两天刚刚用过嵌套表,理解的不是很多,妄言两句吧。
嵌套表是集合数据类型的一种,oracle提供嵌套表这样的数据结构,可能是弥补了数据库原理上的关系数据模型的不足。嵌套表可以被存储在数据库中。
定义嵌套表:CREATE type student as object
( vno VARCHAR2(10),
vname VARCHAR2(10),
vsex VARCHAR2(2),
vaddress VARCHAR2(40)
);
/
CREATE TYPE studentlist AS TABLE OF student;
/
CREATE TABLE nestedtable_student
( vid VARCHAR2(10),
vbj VARCHAR2(14),
students studentlist
)
nested table students store as students_tab;
使用嵌套表(赋值):
SET SERVEROUTPUT ON;
DECLARE
--声明并初始化一个studentlist类型的嵌套表
v_student studentlist:=studentlist(student('200101','赵二麻子','男','广州大街'),student('200102','王三胖子','女','沈阳大街'));
BEGIN
--DBMS_OUTPUT.PUT_LINE(v_student.vname);
/*用嵌套表变量v_student给数据库表赋值*/
INSERT INTO nestedtable_student(vid,vbj,students) VALUES('01','计算机软件',v_student);
/*也可以这样赋值*/
INSERT INTO nestedtable_student(vid,vbj,students)
VALUES('02','网络工程',studentlist(student('200103','张三子','女','北京大街'),student('200104','李小子','男','潮阳大街')));
COMMIT;
END;
显示带有嵌套表的数据库表内容:
SET SERVEROUTPUT ON;
DECLARE
v_vid nestedtable_student.vid%type;
v_vbj nestedtable_student.vbj%type;
v_student nestedtable_student.students%type;
CURSOR mycursor IS SELECT vid,vbj,students FROM nestedtable_student;
BEGIN
OPEN mycursor;
LOOP
FETCH mycursor INTO v_vid,v_vbj,v_student;
EXIT WHEN mycursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_vid||v_vbj);
FOR key_num IN 1..v_student.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_student(key_num).vno||v_student(key_num).vname||v_student(key_num).vsex||v_student(key_num).vaddress);
END LOOP;
END LOOP;
CLOSE MYCURSOR;
END;
嵌套表是集合数据类型的一种,oracle提供嵌套表这样的数据结构,可能是弥补了数据库原理上的关系数据模型的不足。嵌套表可以被存储在数据库中。
定义嵌套表:CREATE type student as object
( vno VARCHAR2(10),
vname VARCHAR2(10),
vsex VARCHAR2(2),
vaddress VARCHAR2(40)
);
/
CREATE TYPE studentlist AS TABLE OF student;
/
CREATE TABLE nestedtable_student
( vid VARCHAR2(10),
vbj VARCHAR2(14),
students studentlist
)
nested table students store as students_tab;
使用嵌套表(赋值):
SET SERVEROUTPUT ON;
DECLARE
--声明并初始化一个studentlist类型的嵌套表
v_student studentlist:=studentlist(student('200101','赵二麻子','男','广州大街'),student('200102','王三胖子','女','沈阳大街'));
BEGIN
--DBMS_OUTPUT.PUT_LINE(v_student.vname);
/*用嵌套表变量v_student给数据库表赋值*/
INSERT INTO nestedtable_student(vid,vbj,students) VALUES('01','计算机软件',v_student);
/*也可以这样赋值*/
INSERT INTO nestedtable_student(vid,vbj,students)
VALUES('02','网络工程',studentlist(student('200103','张三子','女','北京大街'),student('200104','李小子','男','潮阳大街')));
COMMIT;
END;
显示带有嵌套表的数据库表内容:
SET SERVEROUTPUT ON;
DECLARE
v_vid nestedtable_student.vid%type;
v_vbj nestedtable_student.vbj%type;
v_student nestedtable_student.students%type;
CURSOR mycursor IS SELECT vid,vbj,students FROM nestedtable_student;
BEGIN
OPEN mycursor;
LOOP
FETCH mycursor INTO v_vid,v_vbj,v_student;
EXIT WHEN mycursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_vid||v_vbj);
FOR key_num IN 1..v_student.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_student(key_num).vno||v_student(key_num).vname||v_student(key_num).vsex||v_student(key_num).vaddress);
END LOOP;
END LOOP;
CLOSE MYCURSOR;
END;
解决方案 »
- 为什么会报(full) year must be between -4713 and +9999,and not be 0
- 求解,在package body中取得值。
- plsql devloper连接远程的oracle
- 关于substr函数
- 查询子句中索引有顺序吗?
- 一个对我来说很难的问题????真的很难
- 请问:在oracle中关于建表的脚本语言!
- 急!请教一个sql语句
- 怎么样查数据库当前在处理以及将要处理的语句?
- 动态执行sql 报错CODE:-911 ERROR:ORA-00911: invalid character
- 请问,安装oracle10g客户端时没有图形界面的pro*c了吗?
- 从oracle读出clob时遇到的问题,请教高人!
select *
from the(select students from nestedtable_student where vid='01')
where vno='200101';