我想编写一个函数来截断一个特定的表,如果一周中的某一天是在星期二到星期六之间.我尝试了下面的代码,但是如果条件不起作用.有人可以帮帮我吗.
create or replace FUNCTION TRUNCATE_TABLE RETURN NUMBER
IS
--DECLARE VARIABLES
l_day_of_week VARCHAR2(15);
l_table_name CONSTANT VARCHAR2(40) := 'ABC';
BEGIN
EXECUTE IMMEDIATE 'select (to_char(sysdate,''DaY'')) from dual '
INTO l_day_of_week ;
IF l_day_of_week in ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday')
THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE ABC' ;
dbms_output.put_line(l_day_of_week);
END IF;
RETURN 0;
END;
解决方法
IF TRIM(l_day_of_week) in ('Monday','Saturday')
默认返回类型类似于CHAR(9).. 9是一周中某一天的最大长度(星期四)
所以星期一会附加空格!!除非你使用FMDAY
因此,TRIM()将删除所有填充的尾随空格！
您可以使用’FM'(Format Mask)试用下面的演示
随着FM
sql> select to_char(sysdate+level,'Day'),length(to_char(sysdate+level,'Day')) as length from dual 2 connect by level <= 7; TO_CHAR(SYSDATE+LEVEL,'DAY') LENGTH ------------------------------------ ---------- Tuesday 9 Wednesday 9 Thursday 9 Friday 9 Saturday 9 Sunday 9 Monday 9 7 rows selected.
随着FM
sql> select to_char(sysdate+level,'FMDAY'),'FMDaY')) as length from dual 2 connect by level <= 7; TO_CHAR(SYSDATE+LEVEL,'FMDAY') LENGTH ------------------------------------ ---------- TUESDAY 7 WEDNESDAY 9 THURSDAY 8 FRIDAY 6 SATURDAY 8 SUNDAY 6 MONDAY 6 7 rows selected.
最后,
l_day_of_week = to_char(sysdate,’DaY’)
