老大们
我下面定义了嵌套表,并插入了数据,但是我想有一个相同类型的变量读取嵌套表的数据并输出的时候报错
ORA-06550: line 11, column 35:
PLS-00302: component 'COURSE' must be declared
ORA-06550: line 11, column 5:
PL/SQL: Statement ignored
详见后面程序段,请大家帮我分析下是为什么好吗,谢谢!
create type score_type_object as object(id number,
course varchar2(16),
score number);
create type score_type_table as table of score_type_object;
--create type table_type as table of number;
create table student(id number, name varchar2(16), score score_type_table) nested table score store as student_score;
--create table t2(id table_type) nested table id store as t2_id;
--data oprating of nested table.
insert into student
values
(1,
'sqlee',
score_type_table(score_type_object(1, 'English', 97),
score_type_object(2, 'Chinese', 98)));
insert into student
values
(2,
'thomas',
score_type_table(score_type_object(1, 'English', 99),
score_type_object(2, 'Chinese', 80)));
commit;
SQL> select *from student;
ID NAME SCORE
---------- ---------------- -----
1 sqlee 2 thomas
SQL> desc student
Name Type Nullable Default Comments
----- ---------------- -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(16) Y
SCORE SCORE_TYPE_TABLE Y
SQL> select *from table(select score from student where id=1);
ID COURSE SCORE
---------- ---------------- ----------
1 English 97
2 Chinese 98
SQL> select *from table(select score from student where id=2);
ID COURSE SCORE
---------- ---------------- ----------
1 English 99
2 Chinese 80
SQL>
SQL> declare
2 --type my_object1 is object(id number, course varchar2(16), score number);
3 --type my_object is table of SCORE_TYPE_TABLE;
4 my_score SCORE_TYPE_TABLE;
5
6 begin
7
8 for i in (select id, name, score from student)loop
9
10 my_score := i.score;
11 dbms_output.put_line(my_score.course||my_score.score);
12
13 end loop;
14
15 null;
16 end;
17 /
declare
--type my_object1 is object(id number, course varchar2(16), score number);
--type my_object is table of SCORE_TYPE_TABLE;
my_score SCORE_TYPE_TABLE;
begin
for i in (select id, name, score from student)loop
my_score := i.score;
dbms_output.put_line(my_score.course||my_score.score);
end loop;
null;
end;
ORA-06550: line 11, column 35:
PLS-00302: component 'COURSE' must be declared
ORA-06550: line 11, column 5:
PL/SQL: Statement ignored
我下面定义了嵌套表,并插入了数据,但是我想有一个相同类型的变量读取嵌套表的数据并输出的时候报错
ORA-06550: line 11, column 35:
PLS-00302: component 'COURSE' must be declared
ORA-06550: line 11, column 5:
PL/SQL: Statement ignored
详见后面程序段,请大家帮我分析下是为什么好吗,谢谢!
create type score_type_object as object(id number,
course varchar2(16),
score number);
create type score_type_table as table of score_type_object;
--create type table_type as table of number;
create table student(id number, name varchar2(16), score score_type_table) nested table score store as student_score;
--create table t2(id table_type) nested table id store as t2_id;
--data oprating of nested table.
insert into student
values
(1,
'sqlee',
score_type_table(score_type_object(1, 'English', 97),
score_type_object(2, 'Chinese', 98)));
insert into student
values
(2,
'thomas',
score_type_table(score_type_object(1, 'English', 99),
score_type_object(2, 'Chinese', 80)));
commit;
SQL> select *from student;
ID NAME SCORE
---------- ---------------- -----
1 sqlee 2 thomas
SQL> desc student
Name Type Nullable Default Comments
----- ---------------- -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(16) Y
SCORE SCORE_TYPE_TABLE Y
SQL> select *from table(select score from student where id=1);
ID COURSE SCORE
---------- ---------------- ----------
1 English 97
2 Chinese 98
SQL> select *from table(select score from student where id=2);
ID COURSE SCORE
---------- ---------------- ----------
1 English 99
2 Chinese 80
SQL>
SQL> declare
2 --type my_object1 is object(id number, course varchar2(16), score number);
3 --type my_object is table of SCORE_TYPE_TABLE;
4 my_score SCORE_TYPE_TABLE;
5
6 begin
7
8 for i in (select id, name, score from student)loop
9
10 my_score := i.score;
11 dbms_output.put_line(my_score.course||my_score.score);
12
13 end loop;
14
15 null;
16 end;
17 /
declare
--type my_object1 is object(id number, course varchar2(16), score number);
--type my_object is table of SCORE_TYPE_TABLE;
my_score SCORE_TYPE_TABLE;
begin
for i in (select id, name, score from student)loop
my_score := i.score;
dbms_output.put_line(my_score.course||my_score.score);
end loop;
null;
end;
ORA-06550: line 11, column 35:
PLS-00302: component 'COURSE' must be declared
ORA-06550: line 11, column 5:
PL/SQL: Statement ignored
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12158104/viewspace-555621/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12158104/viewspace-555621/