SQL*Plus 是 DBA 必不可少的工具, 按照大神 TK 的话说: 不会使用 SQL*Plus 的 DBA 不是合格的 DBA 当然原话不是这么说的, 但差不多就是这个意思, 可见 SQL*Plus 的重要性
SQL*Plus 很好很强大, 但也有一些局限, 比如在 SQL*Plus 中使用绑定变量时, DATE 类型就无法使用
环境准备:
- SQL> CREATE TABLE T1(ID NUMBER, NAME VARCHAR2(30), MAKEDATE DATE);
- Table created.
- SQL> INSERT INTO T1 VALUES(1, 'Emma',TO_DATE('20180206','YYYYMMDD'));
- 1 row created.
- SQL> INSERT INTO T1 VALUES(2, 'Kevin', TO_DATE('20170101','YYYYMMDD'));
- 1 row created.
- SQL> COMMIT;
- Commit complete.
现在我们需要用 MAKEDATE 作为条件进行查询:
SELECT * FROM T1 WHERE MAKEDATE := :MD;
我们使用 VAR 来声明变量:
- SQL> VAR MD DATE
- Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
- VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
- NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
- REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]
可以看到, 声明 DATE 类型的变量无法通过, SQL*Plus 反馈了一个 VAR 的使用方法从使用方法中我们也可以看到确实没有 DATE 类型那怎么使用 DATE 类型的绑定变量呢? 可以通过一种变通的方法, 如下:
- SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD';
- Session altered.
- SQL> VAR MD VARCHAR2(8)
- SQL> BEGIN :MD := '20180206'; END;
- 2 /
- PL/SQL procedure successfully completed.
- SQL> SELECT * FROM T1 WHERE MAKEDATE = :MD;
- ID NAME MAKEDATE
- ---------- ------------------------------ --------
- 1 Emma 20180206
这里是用 VARCHAR2 类型代替 DATE 类型, 使用 Oracle 的隐性转换间接实现了 DATE 类型的使用 (根据隐性转换规则, VARCHAR2 会转换成 DATE 类型, 具体可参考: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Data-Type-Comparison-Rules.html#GUID-98BE3A78-6E33-4181-B5CB-D96FD9DC1694)
这里需要注意的是 NLS_DATE_FORMAT 的设置, 设置不正确会导致隐性转换的失败
来源: http://www.linuxidc.com/Linux/2018-02/150856.htm