SQL语句中不允许使用本地集合类型 [英] local collection types not allowed in SQL statements
问题描述
我对在Oracle SQL函数中使用集合有疑问.
I have a question regarding using of collections in Oracle SQL functions.
包装中有类型的定义:
/* Types of package*/
create or replace PACKAGE "test" AS
TYPE type_record_1 IS record ( id_num NUMBER , timestamp_num NUMBER,value NUMBER);
TYPE type_table_1 IS TABLE OF type_record_1;
TYPE type_record_2 IS record ( id_num NUMBER , timestamp_num NUMBER,pValue NUMBER);
TYPE type_table_2 IS TABLE OF type_record_2;
END test;
问题出在functions_2中. function_2使用来自function_1的输出. 当我尝试在function_2中进行选择时,会出现错误消息. 错误消息"SQL语句中不允许使用本地集合类型".
Problem is in functions_2. function_2 uses output from function_1. The error message occurs when I try select in function_2. Error message "local collection types not allowed in SQL statements".
能请你帮忙吗?在函数中使用集合有什么问题?
Could you please help? What is wrong with using of collections in functions?
/*function 1*/
FUNCTION function_1
RETURN type_table_1
IS
table_1 type_table_1;
BEGIN
-- select values from
SELECT id_num, timestamp_num, value --type_record_1 (id_num, timestamp_num, value)
BULK COLLECT INTO table_1
FROM (
SELECT
l.id_num,
EXTRACT(hour from end_time) * 60 + EXTRACT(minute from end_time) as timestamp_num,
l.value
FROM INTERVAL_F l
WHERE id_num IN (SELECT id_num FROM table_rev)
);
RETURN table_1;
END function_1;
/*function 2*/
FUNCTION function_2
(
table_1 IN type_table_1
)
RETURN type_table_2
IS
table_2 type_table_2;
BEGIN
SELECT type_record_2(id_num , timestamp_num , pValue)
BULK COLLECT INTO table_2 FROM (
SELECT id_num
, timestamp_num
, value as pValue
FROM table(table_1) -- ERROR IS HERE
);
RETURN table_2;
END function_2;
推荐答案
为达到此目的,您应该使用类似以下内容的东西:
To achive that you should use something like:
创建或替换类型type_record_1 ... /
CREATE OR REPLACE TYPE type_record_1... /
创建或替换类型type_table_1,与TABLE OF type_record_1相同; /
CREATE OR REPLACE TYPE type_table_1 AS TABLE OF type_record_1; /
Oracle不允许将软件包中声明的类型强制转换为表. 我谈论Oracle直到11日,仍然不检查12c的新功能:(.
Oracle does not allow types declared in package to be casted as table. I talk about Oracle until 11, still not check 12c new features :(.
这篇关于SQL语句中不允许使用本地集合类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!