oracle – 在事变日截断表的函数

我想编写一个函数来截断一个特定的表,假如一周中的某一天是在礼拜二到礼拜六之间.我实行了下面的代码,可是假如前提不起浸染.有人可以帮帮我吗.

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’)

会这样,你生涯一个动态SQL挪用！

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!