SQL*Plus中使用DATE型別的繫結變數

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型別，具體可參考：）。

這裡需要注意的是NLS_DATE_FORMAT的設定，設定不正確會導致隱性轉換的失敗。