用户需求，要求月份、日期不到10号时不带0，oracle自带的to_char没找到实现方法，
另外oracle的to_char在转换带中文的年月日格式时，必须蛋疼的加双引号，很不爽。
模仿小爱的MYSQL例子，写了一个oracle版的日期转换函数。
- SQL code
-- =============================================-- Author: tfwin2-- Create date: 2012-04-08-- Description: 用个性化格式显示日期/时间数据 -- P_DATE: 合法的日期-- P_FORMAT: 规定日期/时间的输出格式-- =============================================-- =============================================-- 工具包定义 COMMONUTIL-- 包函数定义 TO_CHAR-- =============================================CREATE OR REPLACE PACKAGE COMMONUTILIS FUNCTION TO_CHAR(P_DATE DATE,P_FORMAT VARCHAR2 ) RETURN VARCHAR2;END COMMONUTIL;/-- =============================================-- 包体函数实现 TO_CHAR-- =============================================CREATE OR REPLACE PACKAGE BODY COMMONUTILIS 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 dualUNION ALLSELECT COMMONUTIL.TO_CHAR(SYSDATE,'yy年M月D日 HH24:MI:SS') FROM dual;