17,082
社区成员
发帖
与我相关
我的任务
分享
-- =============================================
-- 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;
-- =============================================
-- 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;