-- =============================================
-- 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_