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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-755073/