时间戳

首页 > 解决方案 > 如何通过 plsql 函数从表中生成列表？

oracle - 如何通过 plsql 函数从表中生成列表？

问题描述

我需要创建一个函数来通过选择查询从表中生成列表。首先，我创建了一个包和函数来生成一个表，但我得到了错误。

CREATE OR REPLACE PACKAGE test AS
    TYPE date_record IS RECORD(
       list_target VARCHAR2(50));
    TYPE date_table IS TABLE OF date_record;
    FUNCTION get_ups(partition_date_in VARCHAR2)
        RETURN date_table
        PIPELINED;
END;


CREATE OR REPLACE PACKAGE BODY test AS
    FUNCTION get_ups(partition_date_in VARCHAR2)
        RETURN date_table
        PIPELINED IS
        rec            date_record;
    BEGIN
        SELECT ltrim(TO_CHAR(MONTH,'mm-yyyy'),'0') BULK COLLECT AS rec
            FROM 
                (
                select add_months (trunc (to_date('09/01/2019','dd/mm/yyyy'), 'MM'), 1*Level -1)
                Month   FROM Dual
                CONNECT BY Level <= MONTHS_BETWEEN(to_date('09/01/2019','dd/mm/yyyy'), to_date('09/02/2019','dd/mm/yyyy')) + 1
                order by MONTH
                );
        -- you would usually have a cursor and a loop here   
        PIPE ROW (rec);
        RETURN;
    END get_ups;
END;

当我跑

SELECT *  FROM table(test.get_ups('09/01/2019'));

Errore SQL [4063] [72000]: ORA-04063: package body "SYS.TEST" contiene errori

你能帮忙解决这个问题并生成一个列表吗？

标签： oracleplsqlplsql-package

解决方案

您正在处理日期，但还想传递和检索字符串；你为什么想这么做？

如果我明白你想要做什么，那就是将日期参数传递给函数并获取日期列表。由于您从未在代码中使用过该参数，因此我假设您希望获取日期（作为参数传递）和 sysdate 之间的日期。

下面是你如何做到这一点。

当您想从 SQL 中使用它时，您必须在该级别创建类型：

SQL> create or replace type t_my_row is object (val date);
  2  /

Type created.

SQL> create or replace type t_my_tab is table of t_my_row;
  2  /

Type created.

SQL>

包装规格和主体：

SQL> create or replace package test as
  2        function get_ups(par_date in date) return t_my_tab;
  3  end;
  4  /

Package created.

SQL> create or replace package body test as
  2    function get_ups (par_date in date)
  3      return t_my_tab
  4    is
  5      l_tab   t_my_tab := t_my_tab();
  6    begin
  7      select t_my_row(add_months (trunc(par_date, 'MM'), Level - 1)) month
  8        bulk collect into l_tab
  9        from dual
 10       connect by level <= abs(months_between(par_date, sysdate) + 1);
 11
 12      return l_tab;
 13    end get_ups;
 14  end test;
 15  /

Package body created.

SQL>

测试：函数返回 DATES。应用TO_CHAR函数以根据需要对其进行格式化，例如MM-YY

SQL> select to_char(val, 'mm-yy') result
  2    from table(test.get_ups(date '2019-01-09'));

RESULT
------
01-19
02-19
03-19
04-19
05-19
06-19
07-19
08-19
09-19

9 rows selected.

SQL>

如果那是你想要的，那很好。如果没有，请调整函数，以便它计算出您想要的，现在您已经有了大致的想法。

PS哦，是的：你得到的错误（用你的代码）说BULK COLLECT需要INTO（不是AS）。

但是，即使你修复它，你会得到的下一个错误将是

不能在 INTO 列表中的单行和多行 (BULK) 之间混合

等等。如果您有足够的耐心，您可能会走到最后并最终编译包体。如果没有，请查看我的代码。

推荐阅读