SQL*Plus中使用DATE类型的绑定变量

共计 1496 个字符，预计需要花费 4 分钟才能阅读完成。

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 的设置，设置不正确会导致隐性转换的失败。

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2018-02/150856.htm