首页 > 代码库 > 替代变量
替代变量
1.&+标识符 替代变量 类似于其它语言进行变量赋值
scott@TEST>select ename,&aa from emp; Enter value for aa: job old 1: select ename,&aa from emp new 1: select ename,job from emp ENAME JOB -------------------- ------------------ SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK 14 rows selected.
2.define 查看当前的环境变量
define aa=1 定义一个环境变量
undefine aa 取消环境变量的定义
&& aa 定义一个环境变量
该定义只在当前会话有效
set verify off 关闭 新老变量的数值提示
scott@TEST>define DEFINE _DATE = "28-APR-17" (CHAR) DEFINE _CONNECT_IDENTIFIER = "sales" (CHAR) DEFINE _USER = "SCOTT" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR) DEFINE _EDITOR = "vim" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR) DEFINE _O_RELEASE = "1102000400" (CHAR) DEFINE GNAME = "scott@TEST" (CHAR) scott@TEST>define aa=1; scott@TEST>define DEFINE _DATE = "28-APR-17" (CHAR) DEFINE _CONNECT_IDENTIFIER = "sales" (CHAR) DEFINE _USER = "SCOTT" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR) DEFINE _EDITOR = "vim" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR) DEFINE _O_RELEASE = "1102000400" (CHAR) DEFINE GNAME = "scott@TEST" (CHAR) DEFINE AA = "1" (CHAR) scott@TEST>undefine aa scott@TEST>define DEFINE _DATE = "28-APR-17" (CHAR) DEFINE _CONNECT_IDENTIFIER = "sales" (CHAR) DEFINE _USER = "SCOTT" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR) DEFINE _EDITOR = "vim" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR) DEFINE _O_RELEASE = "1102000400" (CHAR) DEFINE GNAME = "scott@TEST" (CHAR) scott@TEST>select &&aa from dual; Enter value for aa: 1 old 1: select &&aa from dual new 1: select 1 from dual 1 ---------- 1 scott@TEST>define DEFINE _DATE = "28-APR-17" (CHAR) DEFINE _CONNECT_IDENTIFIER = "sales" (CHAR) DEFINE _USER = "SCOTT" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR) DEFINE _EDITOR = "vim" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR) DEFINE _O_RELEASE = "1102000400" (CHAR) DEFINE GNAME = "scott@TEST" (CHAR) DEFINE AA = "1" (CHAR)
3.保存命令成脚本
1.list 查看最后一条命令（l）
2.save a.sql 保存命令到a.sql 脚本文件中去
3.get a.sql 查看a.sql 脚本
4.@a.sql 执行脚本
5.set echo on 打开执行脚本时，显示脚本内容
scott@TEST>l 1 select sum(decode(to_char(hiredate,‘yyyy‘),1980,1,0)) "1980", 2 sum(decode(to_char(hiredate,‘yyyy‘),1981,1,0)) "1981", 3 sum(decode(to_char(hiredate,‘yyyy‘),1982,1,0)) "1982", 4 sum(decode(to_char(hiredate,‘yyyy‘),1987,1,0)) "1987" 5* from emp scott@TEST>save 1.sql; Created file 1.sql scott@TEST>get 1.sql 1 select sum(decode(to_char(hiredate,‘yyyy‘),1980,1,0)) "1980", 2 sum(decode(to_char(hiredate,‘yyyy‘),1981,1,0)) "1981", 3 sum(decode(to_char(hiredate,‘yyyy‘),1982,1,0)) "1982", 4 sum(decode(to_char(hiredate,‘yyyy‘),1987,1,0)) "1987" 5* from emp scott@TEST>@ 1.sql 1980 1981 1982 1987 ---------- ---------- ---------- ---------- 1 10 1 2 scott@TEST>set echo on scott@TEST>@ 1.sql scott@TEST>select sum(decode(to_char(hiredate,‘yyyy‘),1980,1,0)) "1980", 2 sum(decode(to_char(hiredate,‘yyyy‘),1981,1,0)) "1981", 3 sum(decode(to_char(hiredate,‘yyyy‘),1982,1,0)) "1982", 4 sum(decode(to_char(hiredate,‘yyyy‘),1987,1,0)) "1987" 5 from emp 6 / 1980 1981 1982 1987 ---------- ---------- ---------- ---------- 1 10 1 2
4.set命令变量
feedback 在select一个表的时候，在最下方显示共有多少行，默认在函数大于6的时候才显示。
一般使用SQL PLUS导出数据时一般使用以下参数就可以了。
set echo off; -- 不显示脚本中的每个sql命令（缺省为on）
set feedback off; -- 禁止回显sql命令处理的记录条数（缺省为on）
set heading off; -- 禁止输出标题（缺省为on）
set pagesize 0; -- 禁止分页输出
set linesize 1000; -- 设置每行的字符输出个数为1000，放置换行（缺省为80 ）
set numwidth 16; -- 设置number类型字段长度为16（缺省为10）
set termout off; -- 禁止显示脚本中命令的执行结果（缺省为on）
set trimout on; -- 去除标准输出每行的行尾空格（缺省为off）
set trimspool on; -- 去除spool输出结果中每行的结尾空格（缺省为off）
sqlplus维护系统变量 也称set变量 利用它可为sqlplus交互建立一个特殊的环境 如:设
置number数据的显示宽度 设置每页的行数 设置列的宽度等。可用set命令改变这些系统变
量 也可用show命令列出它们.
5.host命令 在oracle 下直接调用系统命令
6.假脱机
1）spool 1.txt 开启假脱机 保存到1.txt文件中去
2）spool off 关闭假脱机状态
3）spool 1.txt append 继续追加到1.txt 文件中去
替代变量