SQL>select ename,deptno from emp where deptno=&p; Enter valuefor p: 10 old1: select ename,deptno from emp where deptno=&p new1: select ename,deptno from emp where deptno=10
ENAME DEPTNO ---------- ---------- CLARK 10 KING 10 MILLER 10
SQL>select ename,deptno from emp where deptno=&p; Enter valuefor p: 20 old1: select ename,deptno from emp where deptno=&p new1: select ename,deptno from emp where deptno=20
ENAME DEPTNO ---------- ---------- SMITH 20 JONES 20 SCOTT 20 ADAMS 20 FORD 20
实践2-select 字句中包含替代变量
查看雇员姓名和变量c的值
SQL>select ename,&c from emp; Enter valuefor c: deptno old1: select ename,&c from emp new1: select ename,deptno from emp
ENAME DEPTNO ---------- ---------- SMITH 20 ALLEN 30 WARD 30 JONES 20 MARTIN 30 BLAKE 30 CLARK 10 SCOTT 20 KING 10 TURNER 30 ADAMS 20
ENAME DEPTNO ---------- ---------- JAMES 30 FORD 20 MILLER 10
14rows selected.
SQL>select ename,&c from emp; Enter valuefor c: sal old1: select ename,&c from emp new1: select ename,sal from emp
ENAME SAL ---------- ---------- SMITH 800 ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100
ENAME SAL ---------- ---------- JAMES 950 FORD 3000 MILLER 1300
14rows selected.
实践3-from 字句中包含替代变量
查看变量p所代表的表中的前5行
SQL>select*from&t where rownum <6; Enter valuefor t: salgrade old1: select*from&t where rownum <6 new1: select*from salgrade where rownum <6
SQL>select*from&t where rownum <6; Enter valuefor t: dept old1: select*from&t where rownum <6 new1: select*from dept where rownum <6
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
实践4-如果替代变量的类型为字符串，需要加单引号
查询emp表中雇员姓名为某个值的
SQL>select*from emp where ename=&e; Enter valuefor e: scott old1: select*from emp where ename=&e new1: select*from emp where ename=scott select*from emp where ename=SCOTT * ERROR at line 1: ORA-00904: "SCOTT": invalid identifier
SQL>select*from emp where ename='&e'; Enter valuefor e: SCOTT old1: select*from emp where ename='&e' new1: select*from emp where ename='SCOTT'
EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7788 SCOTT ANALYST 756619-APR-873000 20
实践5-分页查询:每页5行
按照工资高低排序，并分页显示，每页5行
1 1 5 2 6 10 3 11 15 n n*5-4 n*5
SQL>select b.*from (select rownum rn,a.*from (select ename,sal from emp orderby sal desc ) a ) b where rn between&p*5-4and&p*5; Enter valuefor p: 1 Enter valuefor p: 1 old1: select b.*from (select rownum rn,a.*from (select ename,sal from emp orderby sal desc ) a ) b where rn between&p*5-4and&p*5 new1: select b.*from (select rownum rn,a.*from (select ename,sal from emp orderby sal desc ) a ) b where rn between1*5-4and1*5
RN ENAME SAL ---------- ---------- ---------- 1 KING 5000 2 FORD 3000 3 SCOTT 3000 4 JONES 2975 5 BLAKE 2850
SQL>/ Enter valuefor p: 2 Enter valuefor p: 2 old1: select b.*from (select rownum rn,a.*from (select ename,sal from emp orderby sal desc ) a ) b where rn between&p*5-4and&p*5 new1: select b.*from (select rownum rn,a.*from (select ename,sal from emp orderby sal desc ) a ) b where rn between2*5-4and2*5
RN ENAME SAL ---------- ---------- ---------- 6 CLARK 2450 7 ALLEN 1600 8 TURNER 1500 9 MILLER 1300 10 WARD 1250
SQL>/ Enter valuefor p: 3 Enter valuefor p: 3 old1: select b.*from (select rownum rn,a.*from (select ename,sal from emp orderby sal desc ) a ) b where rn between&p*5-4and&p*5 new1: select b.*from (select rownum rn,a.*from (select ename,sal from emp orderby sal desc ) a ) b where rn between3*5-4and3*5
RN ENAME SAL ---------- ---------- ---------- 11 MARTIN 1250 12 ADAMS 1100 13 JAMES 950 14 SMITH 800
SQL>define DEFINE _DATE = "31-JUL-17" (CHAR) DEFINE _CONNECT_IDENTIFIER = "orcl" (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 COL = "sal" (CHAR)
SQL>select ename,&col from emp; old1: select ename,&col from emp new1: select ename,sal from emp
ENAME SAL ---------- ---------- SMITH 800 ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100
ENAME SAL ---------- ---------- JAMES 950 FORD 3000 MILLER 1300
SQL> undefine col SQL>define DEFINE _DATE = "31-JUL-17" (CHAR) DEFINE _CONNECT_IDENTIFIER = "orcl" (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) SQL>select'&col'from dual; Enter valuefor col: xx old1: select'&col'from dual new1: select'xx'from dual
'X' -- xx
实践7-&&的作用
SQL>select b.*from (select rownum rn,a.*from (select ename,sal from emp orderby sal desc ) a ) b where rn between&&p*5-4and&p*5; Enter valuefor p: 1 old1: select b.*from (select rownum rn,a.*from (select ename,sal from emp orderby sal desc ) a ) b where rn between&&p*5-4and&p*5 new1: select b.*from (select rownum rn,a.*from (select ename,sal from emp orderby sal desc ) a ) b where rn between1*5-4and1*5
RN ENAME SAL ---------- ---------- ---------- 1 KING 5000 2 FORD 3000 3 SCOTT 3000 4 JONES 2975 5 BLAKE 2850
SQL> definle SP2-0042: unknown command "definle" - rest of line ignored. SQL>define DEFINE _DATE = "31-JUL-17" (CHAR) DEFINE _CONNECT_IDENTIFIER = "orcl" (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 P = "1" (CHAR)
SQL>select b.*from (select rownum rn,a.*from (select ename,sal from emp orderby sal desc ) a ) b where rn between&&p*5-4and&p*5; old1: select b.*from (select rownum rn,a.*from (select ename,sal from emp orderby sal desc ) a ) b where rn between&&p*5-4and&p*5 new1: select b.*from (select rownum rn,a.*from (select ename,sal from emp orderby sal desc ) a ) b where rn between1*5-4and1*5
RN ENAME SAL ---------- ---------- ---------- 1 KING 5000 2 FORD 3000 3 SCOTT 3000 4 JONES 2975 5 BLAKE 2850
优点是第二个&p不需要手动输入了
缺点在于下一次如果需要修改p的值，还得undefine
SQLPlus环境变量
SET 变量和值
描述
备注
VERIFY {OFF\
ON}
新老两行的比较是否显示
默认开启
ECHO {OFF\
ON}
执行sql脚本时，是否显示脚本中的sql语句
默认关闭
ARRAY[SIZE] {20\
n}
设置数据库取回数据的大小（行）
默认为15，范围为1～5000
FEED[BACK] {6\
n\
OFF\
ON}
当查询选择了n行后,显示查询返回记录的数量
HEA[DING] {OFF\
ON}
决定是否列标题显示在报表中
默认开启
LONG {80\
n}
设置显示 LONG 值时的最大宽度
默认80个字符
获取更加可读性的报表,你可以通过使用下面的命令控制报表栏
命令
描述
COL[UMN]
控制列格式
TTI[TLE] [text\
OFF\
ON]
指定每页报表顶部显示的标题
BTI[TLE] [text\
OFF\
ON]
指定每页报表底部显示的脚注
BRE[AK] [ON report_element]
限制重复的值和使用连接符将数据行分成几部分
实践1- verify新老语句的对比开关
show命令查看环境变量verify
SQL>show verify verify ON
设置该环境变量为off状态
SQL>set verify off
SQL>show verify verify OFF
SQL>select ename,sal from emp where sal>&p; Enter valuefor p: 2000
ENAME SAL ---------- ---------- JONES 2975 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 FORD 3000
6rows selected.
实践2- echo控制脚本执行时是否显示命令
SQL>show echo echo OFF SQL> list 1*select ename,sal from emp where sal>&p SQL> save 1.sql Created file 1.sql SQL>get1.sql 1*select ename,sal from emp where sal>&p SQL>@1 Enter valuefor p: 2000 old1: select ename,sal from emp where sal>&p new1: select ename,sal from emp where sal>2000
ENAME SAL ---------- ---------- JONES 2975 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 FORD 3000
6rows selected.
SQL>set echo on SQL>@1.sql SQL>select ename,sal from emp where sal>&p 2/ Enter valuefor p: 2000 old1: select ename,sal from emp where sal>&p new1: select ename,sal from emp where sal>2000
ENAME SAL ---------- ---------- JONES 2975 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 FORD 3000
SQL> col ename heading 'first|name' SQL>select ename,sal from emp;
first name SAL ---------- ---------- SMITH 800 ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300
14rows selected.
实践2-给工资列加美元前缀
SQL> col sal for $99,999.99 SQL>select ename,sal from emp;
first name SAL ---------- ----------- SMITH $800.00 ALLEN $1,600.00 WARD $1,250.00 JONES $2,975.00 MARTIN $1,250.00 BLAKE $2,850.00 CLARK $2,450.00 SCOTT $3,000.00 KING $5,000.00 TURNER $1,500.00 ADAMS $1,100.00 JAMES $950.00 FORD $3,000.00 MILLER $1,300.00
14rows selected.
实践3-给sal和comm列设置美元前缀，列名居中显示
SQL> col sal justify c for $99,999.99 SQL> col comm justify c for $99,999.99
SQL>select ename,sal,comm from emp;
first name SAL COMM ---------- ----------- ----------- SMITH $800.00 ALLEN $1,600.00 $300.00 WARD $1,250.00 $500.00 JONES $2,975.00 MARTIN $1,250.00 $1,400.00 BLAKE $2,850.00 CLARK $2,450.00 SCOTT $3,000.00 KING $5,000.00 TURNER $1,500.00 $.00 ADAMS $1,100.00 JAMES $950.00 FORD $3,000.00 MILLER $1,300.00
14rows selected.
实践4-清空格式
SQL> col ename clear SQL> col sal clear SQL> col comm clear SQL>select ename,sal,comm from emp;
ENAME SAL COMM ---------- ---------- ---------- SMITH 800 ALLEN 1600300 WARD 1250500 JONES 2975 MARTIN 12501400 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 15000 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300
14rows selected.
实践5-在上一条sql的基础上进行排序，再次调用用/
SQL>select deptno,ename from emp;
DEPTNO ENAME ---------- ---------- 20 SMITH 30 ALLEN 30 WARD 20 JONES 30 MARTIN 30 BLAKE 10 CLARK 20 SCOTT 10 KING 30 TURNER 20 ADAMS 30 JAMES 20 FORD 10 MILLER
14rows selected.
SQL> a orderby deptno 1*select deptno,ename from emp orderby deptno SQL>/
DEPTNO ENAME ---------- ---------- 10 CLARK 10 KING 10 MILLER 20 JONES 20 FORD 20 ADAMS 20 SMITH 20 SCOTT 30 WARD 30 TURNER 30 ALLEN 30 JAMES 30 BLAKE 30 MARTIN
14rows selected.
实践6-BREAK 命令将行分成部分并限制重复的值
SQL>select deptno,ename from emp;
DEPTNO ENAME ---------- ---------- 20 SMITH 30 ALLEN 30 WARD 20 JONES 30 MARTIN 30 BLAKE 10 CLARK 20 SCOTT 10 KING 30 TURNER 20 ADAMS 30 JAMES 20 FORD 10 MILLER
14rows selected.
SQL> break on deptno SQL>/
DEPTNO ENAME ---------- ---------- 20 SMITH 30 ALLEN WARD 20 JONES 30 MARTIN BLAKE 10 CLARK 20 SCOTT 10 KING 30 TURNER 20 ADAMS 30 JAMES 20 FORD 10 MILLER
14rows selected.
SQL>select deptno,ename from emp orderby deptno;
DEPTNO ENAME ---------- ---------- 10 CLARK KING MILLER 20 JONES FORD ADAMS SMITH SCOTT 30 WARD TURNER ALLEN JAMES BLAKE MARTIN
14rows selected.
清除break
SQL> clear break breaks cleared
实践7-将结果集以报表的形式打印bti
SQL>show tti ttitle OFF andis the first few characters of the next SELECT statement SQL>show bti btitle OFF andis the following 10 characters: End report SQL> tti 'Start Booboo' SQL> bti 'End Booboo' SQL>set pagesize 25 SQL>select ename,sal,deptno from emp;
Mon Jul 31 page 1 Start Booboo
ENAME SAL DEPTNO ---------- ---------- ---------- SMITH 80020 ALLEN 160030 WARD 125030 JONES 297520 MARTIN 125030 BLAKE 285030 CLARK 245010 SCOTT 300020 KING 500010 TURNER 150030 ADAMS 110020 JAMES 95030 FORD 300020 MILLER 130010
End Booboo
14rows selected.
注意：以上和sql语句无关，都是sqlplus带来的一些特性
SQLPlus的spool脱机模式
spool 1.txt 开始脱机
spool off 结束脱机存盘
spool 1.txt append 追加脱机
spool off 结束脱机存盘
SQL> spool 1.txt SQL>select* fro salgrade; select* fro salgrade * ERROR at line 1: ORA-00923: FROM keyword not found where expected
FOR i IN1 .. l_colcnt LOOP dbms_sql.define_column (l_thecursor, i, l_columnvalue, 4000); END LOOP;
l_status := dbms_sql.EXECUTE(l_thecursor);
WHILE ( dbms_sql.Fetch_rows(l_thecursor) >0 ) LOOP FOR i IN1 .. l_colcnt LOOP dbms_sql.column_value (l_thecursor, i, l_columnvalue);
dbms_output.Put_line (RPAD(L_desctbl(i).col_name, 30) ||': ' || l_columnvalue); END LOOP;
dbms_output.put_line('-----------------'); END LOOP;
EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-rr'' '; EXCEPTION WHEN OTHERS THEN EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-rr'' ';