蛋疼的时候也写了一个蛋疼的函数

Question

用户需求，要求月份、日期不到10号时不带0，oracle自带的to_char没找到实现方法，
另外oracle的to_char在转换带中文的年月日格式时，必须蛋疼的加双引号，很不爽。
模仿小爱的MYSQL例子，写了一个oracle版的日期转换函数。
-- =============================================
-- Author:      tfwin2
-- Create date: 2012-04-08
-- Description: 用个性化格式显示日期/时间数据 
-- P_DATE:       合法的日期
-- P_FORMAT:     规定日期/时间的输出格式
-- =============================================-- =============================================
-- 工具包定义 COMMONUTIL
-- 包函数定义 TO_CHAR
-- =============================================
CREATE OR REPLACE PACKAGE COMMONUTIL
IS
    FUNCTION TO_CHAR(P_DATE DATE,P_FORMAT VARCHAR2 )
    RETURN  VARCHAR2;
END COMMONUTIL;
/
-- =============================================
-- 包体函数实现 TO_CHAR
-- =============================================
CREATE OR REPLACE PACKAGE BODY COMMONUTIL
IS
    FUNCTION TO_CHAR(P_DATE DATE,P_FORMAT VARCHAR2)
    RETURN VARCHAR2
    IS
       --返回值
       V_CHAR VARCHAR2(50);
       --转换值集合，按顺序排列，由大到小
       CURSOR C1 IS        SELECT 11 ID,'yyyy' CODE, to_char(P_DATE,'yyyy')              VALUE FROM dual
              UNION ALL    SELECT 13 ID,'yy'   CODE ,to_char(P_DATE,'yy')                VALUE FROM dual
              UNION ALL    SELECT 12 ID,'YYYY' CODE,to_char(P_DATE,'yyyy')               VALUE FROM dual
              UNION ALL    SELECT 14 ID,'YY'   CODE,to_char(P_DATE,'yy')                 VALUE FROM dual
              UNION ALL    SELECT 21 ID,'MM'   CODE,to_char(P_DATE,'mm')                 VALUE FROM dual
              UNION ALL    SELECT 22 ID,'M'    CODE,to_number(to_char(P_DATE,'mm'))||''      VALUE FROM dual
              UNION ALL    SELECT 23 ID,'mm'   CODE,to_char(P_DATE,'mm')                 VALUE FROM dual
              --UNION ALL    SELECT 24,'m'     CODE,to_number(to_char(P_DATE,'mm'))      VALUE FROM dual--无法支持，会与分钟冲突
              UNION ALL    SELECT 31 ID,'DD'   CODE,to_char(P_DATE,'dd')                 VALUE FROM dual
              UNION ALL    SELECT 32 ID,'D'    CODE,to_number(to_char(P_DATE,'dd'))||''      VALUE FROM dual
              UNION ALL    SELECT 33 ID,'dd'   CODE,to_char(P_DATE,'dd')                 VALUE FROM dual
              UNION ALL    SELECT 34 ID,'d'    CODE,to_number(to_char(P_DATE,'dd'))||''      VALUE FROM dual
              UNION ALL    SELECT 41 ID,'HH24' CODE,to_char(P_DATE,'hh24')               VALUE FROM dual
              UNION ALL    SELECT 42 ID,'hh24' CODE,to_number(to_char(P_DATE,'hh24'))||''    VALUE FROM dual
              UNION ALL    SELECT 43 ID,'HH'   CODE,to_char(P_DATE,'hh24')               VALUE FROM dual
              UNION ALL    SELECT 44 ID,'hh'   CODE,to_number(to_char(P_DATE,'hh24'))||''    VALUE FROM dual
              UNION ALL    SELECT 45 ID,'H'    CODE,to_char(P_DATE,'hh24')               VALUE FROM dual
              UNION ALL    SELECT 46 ID,'h'    CODE,to_number(to_char(P_DATE,'hh24'))||''    VALUE FROM dual
              UNION ALL    SELECT 51 ID,'MI'   CODE,to_char(P_DATE,'mi')                 VALUE FROM dual
              UNION ALL    SELECT 52 ID,'mi'   CODE,to_number(to_char(P_DATE,'mi'))||''      VALUE FROM dual
              UNION ALL    SELECT 61 ID,'SS'   CODE,to_char(P_DATE,'ss')                 VALUE FROM dual
              UNION ALL    SELECT 62 ID,'s'    CODE,to_number(to_char(P_DATE,'ss'))||''      VALUE FROM dual
              UNION ALL    SELECT 63 ID,'S'    CODE,to_char(P_DATE,'ss')                 VALUE FROM dual
              UNION ALL    SELECT 64 ID,'s'    CODE,to_number(to_char(P_DATE,'ss')) ||''     VALUE FROM dual;
        --行记录对象
        V_TEMP c1%rowtype;
    BEGIN
       V_CHAR:=P_FORMAT;
       -- ================================================
       -- 循环替换，生成日期字符串 
       -- ================================================
       FOR V_TEMP IN C1 LOOP
           SELECT REPLACE(V_CHAR,V_TEMP.CODE,V_TEMP.VALUE) INTO V_CHAR FROM DUAL;
       END LOOP;
       RETURN V_CHAR;
    END TO_CHAR;
END COMMONUTIL;
/--使用方式
SELECT COMMONUTIL.TO_CHAR(SYSDATE,'yy年MM月DD日 HH24:MI:SS') FROM dual
UNION ALL
SELECT COMMONUTIL.TO_CHAR(SYSDATE,'yy年M月D日 HH24:MI:SS') FROM dual;

Answer 1

你这个是有问题的？比如
SQL> SELECT COMMONUTIL.TO_CHAR(SYSDATE,'yy年MM月DD日 HH24:MI:SS') FROM dual
  2  UNION ALL
  3  SELECT COMMONUTIL.TO_CHAR(SYSDATE,'yy年M月D日 HH24:MI:SS') FROM dual;
 
COMMONUTIL.TO_CHAR(SYSDATE,'YY
--------------------------------------------------------------------------------
12年04月09日 12:4I:48
12年4月9日 12:4I:48分钟成4I了，原因是你的替换只是简单替换，如遇到有MM,M这种情况的时候，替换会有问题

Answer 2

你这个是有问题的？比如
SQL> SELECT COMMONUTIL.TO_CHAR(SYSDATE,'yy年MM月DD日 HH24:MI:SS') FROM dual
  2  UNION ALL
  3  SELECT COMMONUTIL.TO_CHAR(SYSDATE,'yy年M月D日 HH24:MI:SS') FROM dual;
 
COMMONUTIL.TO_CHAR(SYSDATE,'YY
--------------------------------------------------------------------------------
12年04月09日 12:4I:48
12年4月9日 12:4I:48分钟成4I了，原因是你的替换只是简单替换，如遇到有MM,M这种情况的时候，替换会有问题

Answer 3

Thank you very much!
思虑不周，请问有什么好的解决办法吗？

Answer 4

谢谢三楼的回复，发现了这个BUG。
目前解决办法是将MI的替换提前了。即分钟优先替换。
因为思路比较狭隘，想不到更好的办法了！
附上新CODE-- =============================================
-- Author:      tfwin2
-- Create date: 2012-04-08
-- Description: 用个性化格式显示日期/时间数据 
-- P_DATE:       合法的日期
-- P_FORMAT:     规定日期/时间的输出格式
-- =============================================-- =============================================
-- 工具包定义 COMMONUTIL
-- 包函数定义 TO_CHAR
-- =============================================
CREATE OR REPLACE PACKAGE COMMONUTIL
IS
    FUNCTION TO_CHAR(P_DATE DATE,P_FORMAT VARCHAR2 )
    RETURN  VARCHAR2;
END COMMONUTIL;
/
-- =============================================
-- 包体函数实现 TO_CHAR
-- =============================================
CREATE OR REPLACE PACKAGE BODY COMMONUTIL
IS
    FUNCTION TO_CHAR(P_DATE DATE,P_FORMAT VARCHAR2)
    RETURN VARCHAR2
    IS
       --返回值
       V_CHAR VARCHAR2(50);
       --转换值集合，按顺序排列，由大到小
       CURSOR C1 IS        SELECT 11 ID,'yyyy' CODE, to_char(P_DATE,'yyyy')              VALUE FROM dual
              UNION ALL    SELECT 13 ID,'yy'   CODE ,to_char(P_DATE,'yy')                VALUE FROM dual
              UNION ALL    SELECT 12 ID,'YYYY' CODE,to_char(P_DATE,'yyyy')               VALUE FROM dual
              UNION ALL    SELECT 14 ID,'YY'   CODE,to_char(P_DATE,'yy')                 VALUE FROM dual
              UNION ALL    SELECT 51 ID,'MI'   CODE,to_char(P_DATE,'mi')                 VALUE FROM dual
              UNION ALL    SELECT 52 ID,'mi'   CODE,to_number(to_char(P_DATE,'mi'))||''      VALUE FROM dual              UNION ALL    SELECT 21 ID,'MM'   CODE,to_char(P_DATE,'mm')                 VALUE FROM dual
              UNION ALL    SELECT 22 ID,'M'    CODE,to_number(to_char(P_DATE,'mm'))||''      VALUE FROM dual
              UNION ALL    SELECT 23 ID,'mm'   CODE,to_char(P_DATE,'mm')                 VALUE FROM dual
              --UNION ALL    SELECT 24,'m'     CODE,to_number(to_char(P_DATE,'mm'))      VALUE FROM dual--无法支持，会与分钟冲突
              UNION ALL    SELECT 31 ID,'DD'   CODE,to_char(P_DATE,'dd')                 VALUE FROM dual
              UNION ALL    SELECT 32 ID,'D'    CODE,to_number(to_char(P_DATE,'dd'))||''      VALUE FROM dual
              UNION ALL    SELECT 33 ID,'dd'   CODE,to_char(P_DATE,'dd')                 VALUE FROM dual
              UNION ALL    SELECT 34 ID,'d'    CODE,to_number(to_char(P_DATE,'dd'))||''      VALUE FROM dual
              UNION ALL    SELECT 41 ID,'HH24' CODE,to_char(P_DATE,'hh24')               VALUE FROM dual
              UNION ALL    SELECT 42 ID,'hh24' CODE,to_number(to_char(P_DATE,'hh24'))||''    VALUE FROM dual
              UNION ALL    SELECT 43 ID,'HH'   CODE,to_char(P_DATE,'hh24')               VALUE FROM dual
              UNION ALL    SELECT 44 ID,'hh'   CODE,to_number(to_char(P_DATE,'hh24'))||''    VALUE FROM dual
              UNION ALL    SELECT 45 ID,'H'    CODE,to_char(P_DATE,'hh24')               VALUE FROM dual
              UNION ALL    SELECT 46 ID,'h'    CODE,to_number(to_char(P_DATE,'hh24'))||''    VALUE FROM dual
              UNION ALL    SELECT 61 ID,'SS'   CODE,to_char(P_DATE,'ss')                 VALUE FROM dual
              UNION ALL    SELECT 62 ID,'s'    CODE,to_number(to_char(P_DATE,'ss'))||''      VALUE FROM dual
              UNION ALL    SELECT 63 ID,'S'    CODE,to_char(P_DATE,'ss')                 VALUE FROM dual
              UNION ALL    SELECT 64 ID,'s'    CODE,to_number(to_char(P_DATE,'ss')) ||''     VALUE FROM dual;
        --行记录对象
        V_TEMP c1%rowtype;
    BEGIN
       V_CHAR:=P_FORMAT;
       -- ================================================
       -- 循环替换，生成日期字符串 
       -- ================================================
       FOR V_TEMP IN C1 LOOP
           SELECT REPLACE(V_CHAR,V_TEMP.CODE,V_TEMP.VALUE) INTO V_CHAR FROM DUAL;
       END LOOP;
       RETURN V_CHAR;
    END TO_CHAR;
END COMMONUTIL;
/--使用方式
SELECT COMMONUTIL.TO_CHAR(SYSDATE,'yy年MM月DD日 HH24:MI:SS') FROM dual
UNION ALL
SELECT COMMONUTIL.TO_CHAR(SYSDATE,'yy年M月D日 HH24:MI:SS') FROM dual;

Answer 5

谁说ORACLE的to_char()函数不能把不到10的月份日期转成不带0的格式！
试试 select to_char(sysdate,'yyyy-fmmm-dd') from dual; 吧骚年。

Answer 6

非常感谢，不知道format_mask还可以这么用，
以前都是在数值里用的。
明天结贴。
这里做个知识点记录吧：
the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed。
不只支持数值，还支持日期
所以
select to_char(sysdate,'yyyy-fmmm-dd') from dual;
2012-4-9
select to_char(sysdate,'yyyy-mm-fmdd') from dual;
2012-04-9