SQL 语句中不允许使用本地集合类型

2021-12-24 00:00:00 sql select collections oracle plsql

我有一个关于在 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;

问题出在函数_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 作为 type_record_1 的表；/

CREATE OR REPLACE TYPE type_table_1 AS TABLE OF type_record_1; /

Oracle 不允许将包中声明的类型转换为表.一直到 11 才谈 Oracle，还没查 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 :(.

相关文章