oracle oracle函数将日期列表作为对象返回
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4336163/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle function to return list of dates as object
提问by kayhan yüksel
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.
我正在尝试返回要在其他函数中使用的日期和工作日列表。下面的代码编译没有错误。但它应该给出 15 天的输出（通过 V_MAX_DAYS 变量）和那一周的天数。
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,
我尝试过这样实现，但无法使用 DBMS_OUTPUT 获得输出。我想测试它，但在运行时出现 ORA-06532 错误。我的目标是将值返回到 asp.net 应用程序，就像我们使用 SYS_REFCURSOR 所做的那样。我怎样才能做到这一点？谢谢你的关心，
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;
/
回答by Justin Cave
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.
- 如果您想要一个从 SYSDATE 算起 X 天的 DATE (V_CALCULATED_DATE)，并且时间组件设置为午夜，这似乎是您在这里的意图，您需要类似
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');. - 但是，我倾向于怀疑使用流水线表函数会更好地为您服务。
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.
