sqlplus中的變數定義和簡單使用.txt

在sqlplus中，如果執行以下命令：
select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual;

其實可以看做是 define report_type:=手工輸入內容的小寫形式。

比如我們輸入txt，然後執行define命令，結果為：

SQL> define
DEFINE _SQLPLUS_RELEASE = "000000000" (CHAR)
DEFINE _EDITOR          = "PLSQLDev" (CHAR)
DEFINE _DATE            = "2013-3-1" (CHAR)
DEFINE _PRIVILEGE       = "" (CHAR)
DEFINE _O_VERSION       = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 " (CHAR)
DEFINE _O_RELEASE       = "000000000" (CHAR)
DEFINE _USER            = "SCOTT" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "TEST" (CHAR)
DEFINE a                = "" (CHAR)
DEFINE report_type      = "txt" (CHAR)

用此值可以作為以後spool輸出時名稱的一部分。

如何輸入變數，比如當前的時間呢？

select to_char(sysdate,'yyyy-mm-dd-hh24-mi-ss') spool_name from dual;

 

SQL> column spool_name new_value spool_name
SQL> select to_char(sysdate,'yyyy-mm-dd-hh24-mi-ss') spool_name from dual;

SPOOL_NAME
-------------------
2013-03-01-17-51-32

SQL> define
DEFINE _SQLPLUS_RELEASE = "000000000" (CHAR)
DEFINE _EDITOR          = "PLSQLDev" (CHAR)
DEFINE _DATE            = "2013-3-1" (CHAR)
DEFINE _PRIVILEGE       = "" (CHAR)
DEFINE _O_VERSION       = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 " (CHAR)
DEFINE _O_RELEASE       = "000000000" (CHAR)
DEFINE _USER            = "SCOTT" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "TEST" (CHAR)
DEFINE SPOOL_NAME       = "2013-03-01-17-51-32" (CHAR)

這時如果覺得已經定義的名稱不合理，可以重新等義：
SQL> select to_char(sysdate,'yyyy-mm-dd-hh24-mi-ss')||'.log' spool_name from dual;

SPOOL_NAME
-----------------------
2013-03-01-17-52-45.log

SQL> define
DEFINE _SQLPLUS_RELEASE = "000000000" (CHAR)
DEFINE _EDITOR          = "PLSQLDev" (CHAR)
DEFINE _DATE            = "2013-3-1" (CHAR)
DEFINE _PRIVILEGE       = "" (CHAR)
DEFINE _O_VERSION       = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 " (CHAR)
DEFINE _O_RELEASE       = "000000000" (CHAR)
DEFINE _USER            = "SCOTT" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "TEST" (CHAR)
DEFINE SPOOL_NAME       = "2013-03-01-17-52-45.log" (CHAR)

用下面的“路徑+引數”的方式不能正常識別
SQL> spool c:\&spool_name;
Started spooling to c:\program files\plsql developer\&spool_name.lst

SQL> spool off;
Stopped spooling to c:\program files\plsql developer\&spool_name.lst

去掉路徑的情況下可以正常識別
SQL> spool &spool_name;
Started spooling to c:\program files\plsql developer\2013-03-01-17-52-45.log

SQL> spool off;
Stopped spooling to c:\program files\plsql developer\2013-03-01-17-52-45.log

使用完畢後需要undefine spool_name，避免對以後的操作造成影響。
SQL> undefine spool_name
SQL> define
DEFINE _SQLPLUS_RELEASE = "000000000" (CHAR)
DEFINE _EDITOR          = "PLSQLDev" (CHAR)
DEFINE _DATE            = "2013-3-1" (CHAR)
DEFINE _PRIVILEGE       = "" (CHAR)
DEFINE _O_VERSION       = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 " (CHAR)
DEFINE _O_RELEASE       = "000000000" (CHAR)
DEFINE _USER            = "SCOTT" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "TEST" (CHAR)

該引數只在當前視窗中有效，其他會話或視窗中都無效。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-755073/，如需轉載，請註明出處，否則將追究法律責任。