Oracle（六）Oracle 常见问题

问题种类：

• literal does not match format string
• 字段内容显示 NA
• 查看表中所有的列
• ORA-00904: invalid identifier标识符无效
• (full) year must be between -4713 and 9999, and not be 0
• 判断oracle不为空
• date format picture ends before converting entire input

需求：将日期作为条件进行查询

示例所用表和内容如下：

DROP TABLE "QSACA"."TE_DATE";
CREATE TABLE "QSACA"."TE_DATE" (
  "ID" NUMBER VISIBLE NOT NULL ,
  "DATESTR" VARCHAR2(50 BYTE) VISIBLE ,
  "DATESTR1" DATE VISIBLE 
)
TABLESPACE "QSACA"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;

-- ----------------------------
-- Records of TE_DATE
-- ----------------------------
INSERT INTO "QSACA"."TE_DATE" VALUES ('1', '2016-01-121', TO_DATE('2016-01-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('2', '2016-01-14 2', TO_DATE('2022-01-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('3', '2016-01-21', TO_DATE('2014-01-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('4', '2016-01-19', TO_DATE('2016-03-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('5', '2016-01-15', TO_DATE('2018-01-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('6', 'NA', TO_DATE('2016-07-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('7', '2016-01-08', TO_DATE('2013-01-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('8', '2016-01-18', TO_DATE('2016-01-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('9', NULL, TO_DATE('2016-01-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('10', '2016-01-11', TO_DATE('2019-07-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('11', '2016-01-06', TO_DATE('2020-11-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));

上面的表 TE_DATE 中的 DATESTR 字段类型为字符串，DATESTR1 字段类型为 日期。

将日期作为查询条件的sql语句正常写法如下：

SELECT * FROM "TE_DATE" WHERE DATESTR1 > TO_DATE('2016-01-19', 'YYYY-MM-DD');

但是若作为查询条件的字段类型并非日期类型，而且其它类型时，如字符串类型（DATESTR），则会存在一些问题。

若还是使用上面的sql语句进行查询：

SELECT * FROM "TE_DATE" WHERE DATESTR1 > TO_DATE('2016-01-19', 'YYYY-MM-DD')

问题一：ORA-01861: literal does not match format string

即会提示错误：ORA-01861: literal does not match format string

意思是：文本与格式字符串不匹配。

原因：

查询条件中DATESTR1为字符串，其无法与 日期 进行比较，需要转换为相同类型才可以进行比较。

解决：

将查询条件所用到的字段，格式转换为日期后，再与 日期条件 进行比较即可，如下：

SELECT * FROM "TE_DATE" WHERE TO_DATE(DATESTR, 'YYYY-MM-DD') > TO_DATE('2016-01-19', 'YYYY-MM-DD')

问题二： ORA-01830: date format picture ends before converting entire input string

意思：日期格式化 在转换整个字符串之前 结束。

个人理解：即 将字符串格式化为日期时，没有转换完整个字符串即结束了，说明字符串的长度 大于 格式化日期 所用到的 “格式化文本”。

原因：

数据表中所存储的字段字符串的长度大于 格式化文本 （YYYY-MM-DD）的长度。如"2022-01-21 13:49:14"。

解决：

我们需要先截取符合我们 格式化日期 的字符串部分，然后再进行格式化操作。如下：

SELECT * FROM "TE_DATE" WHERE TO_DATE(SUBSTR(DATESTR, 0, 10), 'YYYY-MM-DD') > TO_DATE('2016-01-19', 'YYYY-MM-DD');

问题三：ORA-01840: input value not long enough for date format

意思：输入值的长度小于 日期格式化 的长度。

原因：

查看数据库可以发现，数据表中有的字段内容长度 不够我们所要格式化的字符长度。

解决：

我们需要把长度不符合要求的字符串过滤掉，即可避免出错。如下：

SELECT * FROM "TE_DATE" WHERE LENGTH(datestr) >= 10 AND TO_DATE(SUBSTR(DATESTR, 0, 10), 'YYYY-MM-DD') > TO_DATE('2016-01-19', 'YYYY-MM-DD');

问题四：ORA-01841: (full) year must be between -4713 and +9999, and not be 0

意思：年份 必须介于 -4713 和 +9999 之间，而且不能为0。

原因：

数据库中存在 格式不为日期 的字符串。如下：

解决：

我们需要对字符串进行过滤，排除所有格式不为 日期 的字符串，如下：

SELECT * FROM
(
	SELECT * from TE_DATE WHERE REGEXP_LIKE ( DATESTR, '^[0-9]{4}-[0-9]{2}-[0-9]{2}' )
)WHERE TO_DATE(SUBSTR(DATESTR, 0, 10), 'YYYY-MM-DD') > TO_DATE('2016-01-19', 'YYYY-MM-DD');

注：若字符串中的日期格式存在 2016:09:12 格式，也可以用如下过滤：

SELECT id FROM DB WHERE  REGEXP_LIKE(time, '^[0-9]{4}:[0-9]{2}:[0-9]{2}')

问题五：ORA-00904: invalid identifier

意思：无效的标志符。

问题：

这个问题的原因可能有如下几种。

情景一：

sql语句中存在语法问题，查询 表 中不存在的属性。如：

TE_DATE 中不存在 A 字段。

SELECT A FROM "TE_DATE";

即会提示：ORA-00904: “A”: invalid identifier。

情景二：

创建表时字段用双引号包裹了起来，如

create table  TTest
(
   "TESTID"     VARCHAR2(36)          not null,
   "NAME"        VARCHAR2(36)         not null,
   "ADDRESS"     VARCHAR2(36)         not null,
   constraint PK_TEST primary key (TESTID)
);

情景三：

没有定义的变量，而在接下来的sql语句中引用了该变量。

情景四：

创建表时，字段被设置为小写，查询的时候即会报错。

Oracle 建表的时候应该统一用大些，包括表名和所有的字段名。

问题六：如何判断某字段是否为空

通过is 和 is not来判断。

示例：

SELECT id, datestr FROM "TE_DATE" WHERE DATESTR IS NOT NULL

问题七：数据表中的NA值是什么

NA = not applicable 不适用，或 = not available 不可用，可做不必要的，不需要的解释。

意思就是此项数据不需要录入或是空白项。

我们如何过滤 NA 数据呢？

SELECT * FROM TBL_RYXW WHERE CRBM != 'NA'