# 编写函数
CREATE OR REPLACE FUNCTION FUNC_DATE_FORMAT (p_date IN VARCHAR2,
p_date_type IN VARCHAR2)
RETURN VARCHAR2
IS
V_date_val VARCHAR2 (50);
--声明变量
BEGIN
--年格式化
IF (UPPER (p_date_type) = UPPER ('yyyy'))
THEN
SELECT CHR (65 + TO_NUMBER (TO_CHAR (SYSDATE, p_date_type) - 2018))
INTO V_date_val
FROM DUAL;
ELSE
--月、日格式化
SELECT (CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, p_date_type)) > 9
THEN
CHR (
65 + (TO_NUMBER (TO_CHAR (SYSDATE, p_date_type)) - 10))
ELSE
REPLACE (TO_CHAR (SYSDATE, p_date_type), '0', '')
END)
INTO V_date_val
FROM DUAL;
END IF;
RETURN V_date_val;
END FUNC_DATE_FORMAT;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 测试
-- 当前时间 2020-08-19
select FUNC_DATE_FORMAT(sysdate,'yyyy') || FUNC_DATE_FORMAT(sysdate,'mm')
|| FUNC_DATE_FORMAT(sysdate,'dd') DATE_FORMAT from dual;
1
2
3
2
3
- 结果
DATE_FORMAT |
---|
C8J |