oracle 日期类型处理

发表于 更新于 阅读次数：

建表语句

1
2
3
4
5
6
7
CREATE TABLE "ORDERS" 
   (	"ORDER_ID" NUMBER(12,0) NOT NULL ENABLE, 
	"ORDER_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"ORDER_MODE" VARCHAR2(8 BYTE), 
	"CUSTOMER_ID" NUMBER(6,0) NOT NULL ENABLE, 
	"ORDER_TOTAL" NUMBER(8,2)
   ) ;

SQL

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
-- SQL 错误: ORA-01843: 无效的月份
INSERT INTO ORDERS VALUES(1, '09-mar-2007', 'online', '', 1000);
-- SQL 错误: ORA-01400: 无法将 NULL 插入 ("SCOTT"."ORDERS"."ORDER_DATE")
INSERT INTO ORDERS VALUES(2, TO_DATE(NULL), 'online', '1', 1000);
-- SQL 错误: ORA-01722: 无效数字
INSERT INTO ORDERS VALUES(3, TO_DATE(SYSDATE), 'online', ' ', 1000);
-- SQL 错误: ORA-01400: 无法将 NULL 插入 ("SCOTT"."ORDERS"."CUSTOMER_ID")
INSERT INTO ORDERS VALUES(3, TO_DATE(SYSDATE), 'online', '', 1000);
--SUCCESS varchar2默认转number
INSERT INTO ORDERS VALUES(4, TO_DATE(SYSDATE), 'online', '1', 1000);

-- DEFAULT是默认值,非空字段可使用
INSERT INTO ORDERS VALUES(5, TO_DATE(SYSDATE), DEFAULT, 101, DEFAULT);

INSERT INTO ORDERS VALUES(6, to_date('2019-07-02','yyyy-mm-dd,hh24:mi:ss'), DEFAULT, 101, DEFAULT);
-- 存储时分秒
INSERT INTO ORDERS VALUES(15, to_timestamp('2005-12-25,13:25:59','yyyy-mm-dd,hh24:mi:ss'), DEFAULT, 101, DEFAULT);

--时间戳/日期/字符串转换
SELECT TO_DATE('2005-12-25,13:25:59','yyyy-mm-dd,hh24:mi:ss') from DUAL ;
SELECT TO_TIMESTAMP('2005-12-25,13:25:59','yyyy-mm-dd,hh24:mi:ss') from dual ;
SELECT to_date('2019-07-02','yyyy-mm-dd,hh24:mi:ss') from dual ;

SELECT TO_CHAR(ORDER_DATE, 'yyyy-mm-dd hh24:mi:ss') from ORDERS ;

-- 浮点型数值计算
SELECT SUM(ORDER_TOTAL) FROM ORDERS;

sqldeveloper配置