一个PL/SQl程序，用来判断给定的日期是否是周末

Question

set serveroutput on
DECLARE
b VARCHAR2(10);
CURSOR a IS
SELECT
hiredate
FROM
emp;
BEGIN
FOR i IN a LOOP
b := to_char(i.hiredate, 'DAY');
dbms_output.put_line(to_char(i.hiredate, 'DAY'));
IF b in ('SATURDAY','SUNDAY') THEN
dbms_output.put_line('It is a weekend');
END IF;
END LOOP;
END;
/

我试图运行此代码，但我不知道日期是否是周末。请帮我找出错误并解释其背后的逻辑。

下面是输出

WEDNESDAY
FRIDAY   
SUNDAY   
THURSDAY 
MONDAY   
FRIDAY   
TUESDAY  
THURSDAY 
TUESDAY  
TUESDAY  
WEDNESDAY
THURSDAY 
THURSDAY 
SATURDAY 
PL/SQL procedure successfully completed.
WEDNESDAY
FRIDAY   
SUNDAY
It is a weekend   
THURSDAY 
MONDAY   
FRIDAY   
TUESDAY  
THURSDAY 
TUESDAY  
TUESDAY  
WEDNESDAY
THURSDAY 
THURSDAY 
SATURDAY 
It is a weekend
PL/SQL procedure successfully completed.

Answer 1

由于格式模型错误，您没有。DAY用空格右填充结果，直到一周中最长的一天。换句话说，这就是你得到的:

SQL> select '#' || to_char(sysdate, 'DAY') || '#' day from dual;
DAY
--------------------------------------
#SUNDAY   #

看到这些空格了吗?你应该修剪它们，或者-更好-使用正确的格式模型:

SQL> select '#' || to_char(sysdate, 'fmDAY') || '#' day from dual;
DAY
--------------------------------------
#SUNDAY#
SQL>

---

对于您的问题，最好创建一个函数然后你可以从任何你想要的地方打电话。

SQL> create or replace function f_weekend (par_date in date) return varchar2 is
2  begin
3    return case when to_char(par_date, 'fmDAY', 'nls_date_language = english') in
4                     ('SATURDAY', 'SUNDAY')
5                then
6                     ' is a weekend'
7                else null
8           end;
9  end;
10  /
Function created.

测试:

SQL> select ename, to_char(hiredate, 'dd.mm.yyyy, fmDay') hiredate,
2         f_weekend(hiredate) weekend
3  from emp;
ENAME      HIREDATE                       WEEKEND
---------- ------------------------------ --------------------
SMITH      17.12.1980, Wednesday
ALLEN      20.02.1981, Friday
WARD       22.02.1981, Sunday              is a weekend
JONES      02.04.1981, Thursday
MARTIN     28.09.1981, Monday
BLAKE      01.05.1981, Friday
CLARK      09.06.1981, Tuesday
SCOTT      09.12.1982, Thursday
KING       17.11.1981, Tuesday
TURNER     08.09.1981, Tuesday
ADAMS      12.01.1983, Wednesday
JAMES      03.12.1981, Thursday
FORD       03.12.1981, Thursday
MILLER     23.01.1982, Saturday            is a weekend
14 rows selected.
SQL>