SQL*Plus中使用DATE類型的綁定變數

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類型，具體可參考：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的設定，設定不正確會導致隱性轉換的失敗。