Oracle：消除错误

发布于 2024-10-11 08:01:28 字数 726 浏览 4 评论 0原文

我有一个假期表，其描述

   Name                                      Null?    Type

 HOLIDAYDATE                                        DATE
 DESCRIPTION                                        VARCHAR2(20)

包含数据，

HOLIDAYDA DESCRIPTION
--------- --------------------
19-JAN-11 to
17-JAN-11 to
10-JAN-11 a

这里是我的代码：

DECLARE a date;
SELECT holidaydate 
  INTO a 
  FROM holiday 
 WHERE holidaydate = SYSDATE;
 DBMS_OUTPUT.PUT_LINE(a);

我收到这样的错误。

第 2 行出现错误： ORA-06550: 第 2 行，第 1 列： PLS-00103：在预期以下情况之一时遇到符号“SELECT”：开始函数包编译指示过程子类型类型使用形式当前光标

谁能告诉我我的代码有什么问题吗？

原文

I have the holiday table whose description are

   Name                                      Null?    Type

 HOLIDAYDATE                                        DATE
 DESCRIPTION                                        VARCHAR2(20)

which contains data are

HOLIDAYDA DESCRIPTION
--------- --------------------
19-JAN-11 to
17-JAN-11 to
10-JAN-11 a

here is my code :

DECLARE a date;
SELECT holidaydate 
  INTO a 
  FROM holiday 
 WHERE holidaydate = SYSDATE;
 DBMS_OUTPUT.PUT_LINE(a);

i am receiving error like this.

ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
begin function package pragma procedure subtype type use
form
current cursor

Can anyone tell me what is the problem in my code?

分享到QQ

分享到微博

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

发布评论

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

弄潮 2024-10-18 08:01:28

declare 
  a date;
begin
  select holidaydate into a from holiday where holidaydate = sysdate;
  dbms_output.put_line(a);
end;

但这可能会给您带来新的错误（未找到数据），因为可能没有在 Holidaydate 中输入 Sysdate 的记录，因为 sysdate 也包含时间。

所以试试这个：

declare 
  a date;
begin
  select 
    (select holidaydate from holiday where holidaydate = trunc(sysdate))
  into a 
  from dual;

  dbms_output.put_line(a);
end;

这将切断时间部分，检查整个日期。如果表中也有完整日期，则更有可能给出结果。如果不是，则来自 Dual 的 select 将捕获该信息，并使 a 返回 NULL。

您现在可能遇到的唯一错误是当有多个记录包含相同日期时。

declare 
  a date;
begin
  select holidaydate into a from holiday where holidaydate = sysdate;
  dbms_output.put_line(a);
end;

But this will probably give you a new error (no data found), because there probably is no record that has Sysdate entered in Holidaydate, because sysdate includes the time as well.

So try this:

declare 
  a date;
begin
  select 
    (select holidaydate from holiday where holidaydate = trunc(sysdate))
  into a 
  from dual;

  dbms_output.put_line(a);
end;

That will cut off the time part, checking for whole dates. If there are whole dates in your table as well, this will more likely give a result. If not, the select from dual will capture that, and make a return NULL.

The only error you may get now, is when there are more than one records containing the same date.

回复收藏 0 原文

~没有更多了~