-- 第一种方式，临时输入
select * from emp where ename=&vname;
-- 第二种方式，预定义
def vname='SMITH'
select * from emp where ename='&vname';
-- 注意：在使用char类型的变量时，要加上'',数值型不加''
-- 查看及解除已定义的变量
def vname
def
undefine vname

-- &&定义一次，全session可用；&一次使用
-- 不显示新旧值的提示 set verify off
-- 设置与显示session中的变量
SET system_variable value
SHOW ECHO
show all

-- sql*plus 中的Format Commands
COLUMN [column option]
TTITLE [text | OFF | ON] -- top title
BTITLE [text | OFF | ON] -- bottom title
BREAK [ON report_element]

col referred null 'not ref' --将介绍人为空的地方显示为'not ref'
col referred hea '介绍人' --将列头置为'介绍人'
col referred nopri --不显示此列
-- 显示某列的设置
SQL> col referred
COLUMN referred ON
HEADING '介绍人'
NULL 'not ref'

-- 不显示重复的值
BREAK ON DEPARTMENT_ID SKIP 1
SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000
ORDER BY DEPARTMENT_ID;

break on row skip 1 --在每行后插入一空行

-- 分组计算
break on job skip 1 nodup
compute sum of sal on job
select * from emp order by job；

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- ---------- --------- -------------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 $3,000.00 20
7902 FORD 7566 03-DEC-81 $3,000.00 50
********* --------------
sum $6,000.00

7369 SMITH CLERK 7902 17-DEC-80 $800.00 20
7876 ADAMS 7788 12-JAN-83 $1,100.00 20
7934 MILLER 7782 23-JAN-82 $1,300.00 10
7900 JAMES 7698 03-DEC-81 $950.00 30
********* --------------
sum $4,150.00
Example 7-14 Computing and Printing a Grand Total

BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF SALARY ON REPORT

SELECT LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';

LAST_NAME SALARY
------------------------- ----------
Russell 14000
Partners 13500
Errazuriz 12000
Cambrault 11000
Zlotkey 10500
----------
TOTAL 61000

-- 设置报表的表头与表尾 |表示换行,heading中也可以使用|
TTITLE 'Salary|Report'
BTITLE 'Confidential'