--创建类型
CREATE OR REPLACE TYPE COUNTRYLANGUAGE AS OBJECT(
COUNTRY_NAME VARCHAR2(400),
LANGUAGE_NAME VARCHAR2(100),
OFFICIAL_COMMENT CHAR(1));
--创建数组类型
CREATE OR REPLACE TYPE COUNTRY_LANGUAGE_TYPE IS TABLE OF COUNTRYLANGUAGE;
--函数体
create or replace function all_dependent_objects(OBJECT_NAME VARCHAR2)
return DEPENDENT_OBJECT_ARRAY
is
DEPENDENT_ARRAY DEPENDENT_OBJECT_ARRAY:=DEPENDENT_OBJECT_ARRAY();
begin
FOR REC IN(SELECT NAME,TYPE,REFERENCED_NAME,REFERENCED_TYPE
FROM USER_DEPENDENCIES
WHERE NAME=UPPER(OBJECT_NAME)) LOOP
DEPENDENT_ARRAY.EXTEND(1);
DEPENDENT_ARRAY(DEPENDENT_ARRAY.COUNT):=DEPENDENT_ARRAY(null,null,null,null);
DEPENDENT_ARRAY(DEPENDENT_ARRAY.COUNT).NAME:=REC.NAME;
DEPENDENT_ARRAY(DEPENDENT_ARRAY.COUNT).TYPE:=REC.TYPE;
DEPENDENT_ARRAY(DEPENDENT_ARRAY.COUNT).REFERENCED_NAME:=REC.REFERENCED_NAME;
DEPENDENT_ARRAY(DEPENDENT_ARRAY.COUNT).REFERENCED_TYPE:=REC.REFERENCED_TYPE;
END LOOP;
FOR K IN 1..DEPENDENT_ARRAY.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(DEPENDENT_ARRAY(K).REFERENCED_NAME);
END LOOP;
return DEPENDENT_ARRAY;
end ;
红色的地方报错:
PLS-00316:PL/SQL TABLEs must use a single index
不知道这里的单索引是指什么?
改为一个null时,编译ok,
但是执行时说uninitialized composite
CREATE OR REPLACE TYPE COUNTRYLANGUAGE AS OBJECT(
COUNTRY_NAME VARCHAR2(400),
LANGUAGE_NAME VARCHAR2(100),
OFFICIAL_COMMENT CHAR(1));
--创建数组类型
CREATE OR REPLACE TYPE COUNTRY_LANGUAGE_TYPE IS TABLE OF COUNTRYLANGUAGE;
--函数体
create or replace function all_dependent_objects(OBJECT_NAME VARCHAR2)
return DEPENDENT_OBJECT_ARRAY
is
DEPENDENT_ARRAY DEPENDENT_OBJECT_ARRAY:=DEPENDENT_OBJECT_ARRAY();
begin
FOR REC IN(SELECT NAME,TYPE,REFERENCED_NAME,REFERENCED_TYPE
FROM USER_DEPENDENCIES
WHERE NAME=UPPER(OBJECT_NAME)) LOOP
DEPENDENT_ARRAY.EXTEND(1);
DEPENDENT_ARRAY(DEPENDENT_ARRAY.COUNT):=DEPENDENT_ARRAY(null,null,null,null);
DEPENDENT_ARRAY(DEPENDENT_ARRAY.COUNT).NAME:=REC.NAME;
DEPENDENT_ARRAY(DEPENDENT_ARRAY.COUNT).TYPE:=REC.TYPE;
DEPENDENT_ARRAY(DEPENDENT_ARRAY.COUNT).REFERENCED_NAME:=REC.REFERENCED_NAME;
DEPENDENT_ARRAY(DEPENDENT_ARRAY.COUNT).REFERENCED_TYPE:=REC.REFERENCED_TYPE;
END LOOP;
FOR K IN 1..DEPENDENT_ARRAY.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(DEPENDENT_ARRAY(K).REFERENCED_NAME);
END LOOP;
return DEPENDENT_ARRAY;
end ;
红色的地方报错:
PLS-00316:PL/SQL TABLEs must use a single index
不知道这里的单索引是指什么?
改为一个null时,编译ok,
但是执行时说uninitialized composite
解决方案 »
- Oracle 中能否同时取出记录总数和分页数据?
- Linux下的Oracle数据库不能连接问题
- oracle中如何删除一个实例?
- 反向like型的条件怎么弄?
- vpn连接数据库 查询语句报错ORA-03113:通信通道的文件结尾
- oracle 10g drop的垃圾表解决方法
- 高分求教,oracle9i安装的本地文件夹被删除了,怎样才能完全删除它呢?
- 在线急,跪求解,sql语句,都来看看吧╭∩╮(︶︿︶)╭∩╮
- 简单问题在线等啊,
- ORACLE是不是和MYSQL的系统差不多,MYSQL里面CREATE DATABASE DB1;相对应的ORACLE语句是什么啊?
- oracle 视图小问题
- oracle入门问题
http://topic.csdn.net/u/20081006/16/f0a09bd5-a440-4a4c-a3fa-a835a353b40d.html
你用一个数值(比如3或4)代替DEPENDENT_ARRAY.COUNT,试看看
CREATE OR REPLACE TYPE DEPENDENT_ARRAY AS OBJECT(
NAME VARCHAR2(400),
TYPE VARCHAR2(100),
REFERENCED_NAME varchar2(100),
REFERENCED_TYPE VARCHAR2(100)
);
--创建数组类型
CREATE OR REPLACE TYPE DEPENDENT_OBJECT_ARRAY IS TABLE OF DEPENDENT_ARRAY;
--函数体
create or replace function all_dependent_objects(OBJECT_NAME VARCHAR2)
return DEPENDENT_OBJECT_ARRAY
is
a_DEPENDENT_ARRAY DEPENDENT_OBJECT_ARRAY:=DEPENDENT_OBJECT_ARRAY();
x number;
begin
FOR REC IN(SELECT NAME,TYPE,REFERENCED_NAME,REFERENCED_TYPE
FROM USER_DEPENDENCIES
WHERE NAME=UPPER(OBJECT_NAME)) LOOP
a_DEPENDENT_ARRAY.EXTEND;
x := a_DEPENDENT_ARRAY.COUNT;
a_DEPENDENT_ARRAY(x):=DEPENDENT_ARRAY('', '', '', '');
a_DEPENDENT_ARRAY(x).NAME:=REC.NAME;
a_DEPENDENT_ARRAY(x).TYPE:=REC.TYPE;
a_DEPENDENT_ARRAY(x).REFERENCED_NAME:=REC.REFERENCED_NAME;
a_DEPENDENT_ARRAY(x).REFERENCED_TYPE:=REC.REFERENCED_TYPE;
END LOOP;
FOR K IN 1..a_DEPENDENT_ARRAY.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(a_DEPENDENT_ARRAY(K).REFERENCED_NAME);
END LOOP;
return a_DEPENDENT_ARRAY;
end ;