oracle 函数返回日期列表作为对象

发布于 2024-10-05 20:06:54 字数 946 浏览 3 评论 0原文

我正在尝试

返回要在其他函数中使用的日期和工作日列表。下面的代码编译没有错误。但它应该给出 15 天的输出（通过 V_MAX_DAYS 变量）以及该周的天数。

我尝试像这样实现，但无法使用 DBMS_OUTPUT 获得输出。我想测试它，但运行时出现 ORA-06532 错误。我的目标是将值返回到 ASP.NET 应用程序，就像我们使用 SYS_REFCURSOR 所做的那样。我怎样才能做到这一点？感谢您的关注，

脚本如下：

CREATE OR REPLACE TYPE DATE_ROW AS OBJECT
(
  WEEKDAY_VALUE DATE,
  DATE_IN_LIST VARCHAR2(5)
)
/

CREATE OR REPLACE TYPE DATE_TABLE as table of DATE_ROW
/



CREATE OR REPLACE FUNCTION FN_LISTDATES
   RETURN DATE_TABLE
IS
   V_DATE_TABLE        DATE_TABLE    := DATE_TABLE ();
   V_MAX_DAYS          NUMBER        := 15;
   V_CALCULATED_DATE   DATE;
   V_WEEKDAY           VARCHAR2 (5);
BEGIN
   FOR X IN -2 .. V_MAX_DAYS
   LOOP
      SELECT TO_DATE (TO_CHAR (SYSDATE + X, 'DD.MM.YYYY'))
        INTO V_CALCULATED_DATE
        FROM DUAL;
      V_DATE_TABLE.EXTEND;
      V_DATE_TABLE(X) := DATE_ROW(V_CALCULATED_DATE, 'Test');
   END LOOP;
   RETURN V_DATE_TABLE;
END;
/

原文

To whom it may respond to ,

I am trying to return list of dates and weekdays to be used in other functions. Code below is compiled without error. But it should give output of 15 days (via V_MAX_DAYS variable) and number of the day in that week.

I have tried to implement like this, but cannot get output using DBMS_OUTPUT. I want to test it but got ORA-06532 error at when running .
My aim is to return values to asp.net application as we have done using SYS_REFCURSOR.
How can I achieve that?
Thank you for your concern,

The script is as below :

CREATE OR REPLACE TYPE DATE_ROW AS OBJECT
(
  WEEKDAY_VALUE DATE,
  DATE_IN_LIST VARCHAR2(5)
)
/

CREATE OR REPLACE TYPE DATE_TABLE as table of DATE_ROW
/



CREATE OR REPLACE FUNCTION FN_LISTDATES
   RETURN DATE_TABLE
IS
   V_DATE_TABLE        DATE_TABLE    := DATE_TABLE ();
   V_MAX_DAYS          NUMBER        := 15;
   V_CALCULATED_DATE   DATE;
   V_WEEKDAY           VARCHAR2 (5);
BEGIN
   FOR X IN -2 .. V_MAX_DAYS
   LOOP
      SELECT TO_DATE (TO_CHAR (SYSDATE + X, 'DD.MM.YYYY'))
        INTO V_CALCULATED_DATE
        FROM DUAL;
      V_DATE_TABLE.EXTEND;
      V_DATE_TABLE(X) := DATE_ROW(V_CALCULATED_DATE, 'Test');
   END LOOP;
   RETURN V_DATE_TABLE;
END;
/

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风筝有风，海豚有海 2024-10-12 20:06:54

几点。

如果您想要一个距离 SYSDATE X 天的日期 (V_CALCULATED_DATE)，并且时间部分设置为午夜（这似乎是您的意图），您可能需要类似 v_calculated_date := TRUNC(sysdate) + x;v_calculated_date := TRUNC(sysdate) + x;.如果未来会话的 NLS_DATE_FORMAT 碰巧不是 DD.MM.YYYY，则没有显式格式掩码的 TO_DATE 将会产生问题。
如果您确实想返回这样的集合，则集合索引需要从 1 开始，而不是 -2 。您可以通过执行 v_date_table(x+3) := DATE_ROW(v_calculated_date, 'Test'); 来实现这一点。
但是，我倾向于怀疑这里使用管道表函数会更好。

流水线表函数看起来像

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE FUNCTION FN_LISTDATES
  2     RETURN DATE_TABLE
  3     PIPELINED
  4  IS
  5     V_MAX_DAYS          NUMBER        := 15;
  6     V_CALCULATED_DATE   DATE;
  7     V_WEEKDAY           VARCHAR2 (5);
  8  BEGIN
  9     FOR X IN -2 .. V_MAX_DAYS
 10     LOOP
 11        v_calculated_date := trunc(sysdate) + x;
 12        PIPE ROW( DATE_ROW(v_calculated_date,'Test') );
 13     END LOOP;
 14     RETURN;
 15* END;
SQL> /

Function created.

SQL> select * from table( fn_listDates );

WEEKDAY_V DATE_
--------- -----
30-NOV-10 Test
01-DEC-10 Test
02-DEC-10 Test
03-DEC-10 Test
04-DEC-10 Test
05-DEC-10 Test
06-DEC-10 Test
07-DEC-10 Test
08-DEC-10 Test
09-DEC-10 Test
10-DEC-10 Test

WEEKDAY_V DATE_
--------- -----
11-DEC-10 Test
12-DEC-10 Test
13-DEC-10 Test
14-DEC-10 Test
15-DEC-10 Test
16-DEC-10 Test
17-DEC-10 Test

18 rows selected.

A few points.

If you want a DATE (V_CALCULATED_DATE) that is X days from SYSDATE with the time component set to midnight, which appears to be your intent here, you would want something like v_calculated_date := TRUNC(sysdate) + x;. A TO_DATE without an explicit format mask is going to create issues if a future session's NLS_DATE_FORMAT happens not to be DD.MM.YYYY
If you really want to return a collection like this, your collection indexes would need to start with 1, not -2. You could accomplish that by doing v_date_table(x+3) := DATE_ROW(v_calculated_date, 'Test');.
However, I would tend to suspect that you would be better served here with a pipelined table function.

The pipelined table function would look something like

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE FUNCTION FN_LISTDATES
  2     RETURN DATE_TABLE
  3     PIPELINED
  4  IS
  5     V_MAX_DAYS          NUMBER        := 15;
  6     V_CALCULATED_DATE   DATE;
  7     V_WEEKDAY           VARCHAR2 (5);
  8  BEGIN
  9     FOR X IN -2 .. V_MAX_DAYS
 10     LOOP
 11        v_calculated_date := trunc(sysdate) + x;
 12        PIPE ROW( DATE_ROW(v_calculated_date,'Test') );
 13     END LOOP;
 14     RETURN;
 15* END;
SQL> /

Function created.

SQL> select * from table( fn_listDates );

WEEKDAY_V DATE_
--------- -----
30-NOV-10 Test
01-DEC-10 Test
02-DEC-10 Test
03-DEC-10 Test
04-DEC-10 Test
05-DEC-10 Test
06-DEC-10 Test
07-DEC-10 Test
08-DEC-10 Test
09-DEC-10 Test
10-DEC-10 Test

WEEKDAY_V DATE_
--------- -----
11-DEC-10 Test
12-DEC-10 Test
13-DEC-10 Test
14-DEC-10 Test
15-DEC-10 Test
16-DEC-10 Test
17-DEC-10 Test

18 rows selected.

回复收藏 0 原文

~没有更多了~