[SQL] Oracle SQL*Plus 명령어 정리

 

▶ SQL *Plus 명령어와 SQL문 구분하기

┌──────────────┬─────────────────────┬────────┐
│구분 │ 설명 │ 예 │
├──────────────┼─────────────────────┼────────┤
│DML │데이터를 조회하거나 변경 │insert,update │
│(Data Manipulation Language)│ │delete,select │
│ │ │commit,rollback │
├──────────────┼─────────────────────┼────────┤
│DDL │데이터의 구조를 정의 │create,drop │
│(Data Definition Language) │ │alter,rename, │
│ │ │truncate │
├──────────────┼─────────────────────┼────────┤
│DCL │데이터베이스 사용자에게 부여된 권한을 정의│grant,revoke │
│(Data Control Language) │ │ │
└──────────────┴─────────────────────┴────────┘

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ACCOUNT TABLE
BONUS TABLE
DEPARTMENT TABLE
DEPT TABLE
EMP TABLE
EMPLOYEE TABLE
RECEIPT TABLE
SALGRADE TABLE

SQL> desc emp -- desc(ribe)는 테이블 정의를 나타내는 명령어

이름 널? 유형
----------------------------------------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)


▶ 명령어 버퍼
-- SQL *Plus는 명령어 버퍼(command buffer)에서 현재의 SQL문을 보관한다.
-- 이 명령어 버퍼의 각각의 줄은 번호가 부여되어 있다.
-- 사용자는 명령어 버퍼의 내용을 편집하고, 호출하고 저장할 수 있으며,
-- SQL*Plus의 프롬프트에 list나 그의 축약표현인 L을 입력하여 버퍼의 내용을 나타내게 할 수 있다.


--------------------------------------------------------------------------------
명령어 설명
--------------------------------------------------------------------------------
L(LIST) SQL명령어 버퍼의 내용을 나열
L(LIST) n SQL명령어 버퍼의 내용중 n번째줄만 나열
L(LIST) m n SQL명령어 버퍼의 내용중 m번째 부터 n번째줄까지 나열
R(RUN) SQL명령어 버퍼에 있는 SQL문을 실행시킴
N(N은 숫자) N을 편집될 현재의 줄로 만듦
I(INSERT) 현재의 줄 다음에 새로운 줄을 삽입
새로운 줄이 현재의 줄이 됨
DEL(DELETE) 현재의 줄을 삭제
DEL n n번째 줄을 삭제
DEL m n m번째 부터 n번째 까지 줄을 삭제
A(APPEND) text 현재의 줄에 텍스트를 첨가
C(CHANGE)/string1/string2/ 문자열1(string1)의 내용을 문자열2(string2)로 변경함
CL(CLEAR) BUFF(BUFFER) SQL명령어 버퍼에 있는 SQL문을 삭제
CL(CLEAR) SCR(SCREEN) SQL*Plus 에디터 화면을 지움
--------------------------------------------------------------------------------

SQL> select empno,ename
2 from emp
3 where sal > (select avg(sal) from emp);

EMPNO ENAME
---------- ----------
7566 JONES
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7902 FORD

6 개의 행이 선택되었습니다.


1. 방금했던 질의의 내용을 다시 나타내게 하고 싶다면 즉, 현재 버퍼에 있는 내용을 나타내 보는것

SQL> l
1 select empno,ename
2 from emp
3* where sal > (select avg(sal) from emp)


2. 이번에는 평균 급여보다 적게 받는 사원들을 알고 싶어서 3번째 줄을 편집하고자 한다면 SQL프롬프트에서 3을 입력한다.

SQL> 3
3* where sal > (select avg(sal) from emp)

3. 3번째 줄이 현재의 줄이 되었고(라인 번호 옆에 * 표시가 있는 곳이 현재의 줄이다), '>'을 '<'로 변경하기 위해서
change명령(축약 c)을 사용한다.
SQL> c/>/<
3* where sal < (select avg(sal) from emp)

4. 다시 현재 버퍼의 내용을 확인해본다.
SQL> l
1 select empno,ename
2 from emp
3* where sal < (select avg(sal) from emp)

5. 이 질의 결과가 사원들의 이름순으로 정렬되기를 원한다. 그래서 현재 버퍼의 내용의 끝에 새로운 줄을 입력하기 위해서
input명령(축약어 i)을 사용한다.
SQL> i
4 order by ename
5

6. 다시 현재 버퍼의 내용을 확인한다.
SQL> l
1 select empno,ename
2 from emp
3 where sal < (select avg(sal) from emp)
4* order by ename

7. 그런데, 이들의 구체적인 급여도 알고 싶어서 첫번째 줄의 뒤에 찾는 컬럼 sal을 추가해야 한다.
먼저 위의 버퍼 내용에서는 현재의 줄이 4번째 줄이므로, 첫번째 줄로 현재의 줄을 바꾸기 위해서 1을 입력한다.
SQL> 1
1* select empno,ename

8. ,sal을 이 줄의 뒤에 추가시키기 위해 append명령(축약 a)을 사용한다.
SQL> a ,sal
1* select empno,ename,sal

9. 다시 현재 버퍼의 내용을 확인한다.
SQL> l
1 select empno,ename,sal
2 from emp
3 where sal < (select avg(sal) from emp)
4* order by ename

10. 현재 버퍼에 있는 SQL문을 run 이나 /을 입력하여 실행시킨다.
SQL> /

EMPNO ENAME SAL
---------- ---------- ----------
7876 ADAMS 1100
7499 ALLEN 1600
7900 JAMES 950
7654 MARTIN 1250
7934 MILLER 1300
7369 SMITH 800
7844 TURNER 1500
7521 WARD 1250

8 개의 행이 선택되었습니다.


11. 편집명령어 ==> ed

SQL> select * from 사원
2 where 부서펀호=20;
where 부서펀호=20
*
2행에 오류:
ORA-00904: 열명이 부적합합니다


SQL> ed

select * from 사원
where 부서번호=20 ==> 메모장에서 펀을 번으로 수정한 후 alt+f , x , 엔터
/

file afiedt.buf(이)가 기록되었습니다

1 select * from 사원
2* where 부서번호=20

SQL> / ==> /으로 실행함



▶------ SQL문을 파일로 저장하기 -------◀

★★★
1. save 명령어를 사용하여 명령어 버퍼의 현재 내용만을 사용자가 명시하는 파일로 저장한다.
기본적으로 save 명령어는 .sql 이라는 확장자를 사용한다. 사용자는 다른 파일 확장자를 사용할 수 있다.
save 명령어는 자동적으로 덮어쓰기를 하지 않으므로
기존의 파일 위에 덮어쓰기를 하려면 replace 라는 옵션을 사용해야 한다.

SQL> l
1 select empno,ename,sal
2 from emp
3 where sal < (select avg(sal) from emp)
4* order by ename

SQL> save c:\test\lowsal
file c:\test\lowsal(이)가 생성되었습니다

SQL> save c:\test\lowsal
SP2-0540: "c:\test\lowsal.sql" 파일은 이미 존재합니다.
"SAVE 파일명[.ext] REPLACE"을 사용합니다.

SQL> save c:\test\lowsal replace
file c:\test\lowsal(이)가 기록되었습니다

★★★
2. get을 사용하여 save 명령어를 사용하여 파일로 저장된 명령어 버퍼의 내용을 조회하고, r을 입력하여 SQL문을 실행시킨다.

SQL> get c:\test\lowsal
1 select empno,ename,sal
2 from emp
3 where sal < (select avg(sal) from emp)
4* order by ename
SQL> r
1 select empno,ename,sal
2 from emp
3 where sal < (select avg(sal) from emp)
4* order by ename

EMPNO ENAME SAL
---------- ---------- ----------
7876 ADAMS 1100
7499 ALLEN 1600
7900 JAMES 950
7654 MARTIN 1250
7934 MILLER 1300
7369 SMITH 800
7844 TURNER 1500
7521 WARD 1250

8 개의 행이 선택되었습니다.


★★★
3. 출력을 파일로 저장하기
spool 명령어는 질의 결과를 파일로 저장해주는 것이다.
spool off 명령어는 스풀링을 중단하는 SQL *Plus 명령어이다.

SQL> spool c:\test\spool_1
SQL> select empno,ename,sal
2 from emp
3 where sal < (select avg(sal) from emp)
4 order by ename;

EMPNO ENAME SAL
---------- ---------- ----------
7876 ADAMS 1100
7499 ALLEN 1600
7900 JAMES 950
7654 MARTIN 1250
7934 MILLER 1300
7369 SMITH 800
7844 TURNER 1500
7521 WARD 1250

8 개의 행이 선택되었습니다.

SQL> select empno,ename,sal
2 from emp
3 where sal = (select max(sal) from emp);

EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000

SQL> spool off

============================================================
c:\test\spool_1.lst 파일을 메모장에서 열어 보았을 때의 내용
SQL> select empno,ename,sal
2 from emp
3 where sal < (select avg(sal) from emp)
4 order by ename;

EMPNO ENAME SAL
---------- ---------- ----------
7876 ADAMS 1100
7499 ALLEN 1600
7900 JAMES 950
7654 MARTIN 1250
7934 MILLER 1300
7369 SMITH 800
7844 TURNER 1500
7521 WARD 1250

8 개의 행이 선택되었습니다.

SQL> select empno,ename,sal
2 from emp
3 where sal = (select max(sal) from emp);

EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000

SQL> spool off
============================================================

★★★
4. 스크립트를 실행하기

c:\test\script1.sql 이라는 파일로 아래의 내용을 저장한다.

select empno,ename,job,sal
from emp
where job='CLERK';

SQL> @ c:\test\script1.sql

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7934 MILLER CLERK 1300

SQL> start c:\test\script1.sql

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7934 MILLER CLERK 1300


★★★
5. 출력의 포맷팅
SQL *Plus 명령어를 통해서 사용자가 형식, 총계와 소계를 명시할 수 있고 반복되는 값을 통제할 수 있다.
포맷팅 명령어는 임시적이다. 이것들은 동일한 SQL *Plus 세션 기간중 처리되는 SQL문에 대해서만 그 효력을 유지한다.

※ SQL*Plus 세션이란?
-- SQL *Plus에 연결할 때부터 SQL *Plus에서 연결을 끊을 때까지 발생하는 명령어와 그에 대한 반응을 말한다.

------------------------------------------------------------------------------------------------
명령어 목적
------------------------------------------------------------------------------------------------
col[umn] 컬럼의 옵션 컬럼의 형식을 바꾼다.
tti[tle] [문장|off|on] 보고서 각 페이지 상단의 머릿글을 나타낸다.
bti[tle] [문장|off|on] 보고서 각 페이지 하단의 머릿글을 나타낸다.
bre[ak] [on 반복을 배제할 리스트] 라인을 넘겨 중복값을 통제한다.
comp[ute] [함수 of{컬럼명} on {컬럼명}] 합계를 계산한다.
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
포맷 지정 요소 내용
------------------------------------------------------------------------------------------------
An (n은 폭을 나타내는 숫자) 문자와 날짜 컬럼에 대해서 n폭 만큼 출력 설정
9 자리수 표현
0 앞에 0을 붙임
$ 달러 사인 표시
L 국내 통화 표시
. 소숫점의 위치
, 천 단위 구분자
------------------------------------------------------------------------------------------------

1). emp 테이블에서, 사원들의 급여를 세 자리마다 콤마를 찍고 앞에 달러 사인($)을 표시하고 싶다면 다음과 같이 한다.

SQL> col sal format $9,999
SQL> select empno, ename, sal
2 from emp;

EMPNO ENAME SAL
---------- ---------- -------
7369 SMITH $800
7499 ALLEN $1,600
7521 WARD $1,250
7566 JONES $2,975
7654 MARTIN $1,250
7698 BLAKE $2,850
7782 CLARK $2,450
7788 SCOTT $3,000
7839 KING $5,000
7844 TURNER $1,500
7876 ADAMS $1,100

EMPNO ENAME SAL
---------- ---------- -------
7900 JAMES $950
7902 FORD $3,000
7934 MILLER $1,300

14 개의 행이 선택되었습니다.


2). 문자 컬럼의 경우, 사용자는 Format 명령어를 사용하여 컬럼의 넓이를 명시할 수 있다.
부서명을 한 줄에 다섯글자만 표현하고 다음줄에 나타내는 보고서를 만든다면 다음과 같이 한다.

SQL> col dname format a5
SQL> select * from dept;

DEPTNO DNAME LOC
---------- ----- -------------
10 ACCOU NEW YORK
NTING

20 RESEA DALLAS
RCH

30 SALES CHICAGO
40 OPERA BOSTON
TIONS

3). 위의 결과를 보기에 좋지 않은 형식이다. 그래서 포맷지정을 다시 원상복귀 시키기 위해서
현재의 포맷지정을 clear 옵션을 사용하여 지운다.
SQL> col dname clear
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


4). clear 옵션을 사용하지 않으면, SQL*Plus 세션을 끝낼때 까지 지정한 포맷형식이 계속 출력에 이용된다.
이번에는 부서의 지역명을 7자리의 포맷으로 맞추어 본다.

SQL> col loc format a7
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------
10 ACCOUNTING NEW YOR
K

20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


5). 사용자는 NEW YOR와 K처럼 단어 중간이 잘리는 것을 원치 않을 수도 있다.
단어가 토막나는 것을 막기 위하여, column명령어에 word_wrap을 추가한다.

SQL> col loc format a7 word_wrap
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------
10 ACCOUNTING NEW
YORK

20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


6). 워드래핑(Wrapping) 대신에, 사용자는 한 컬럼의 내용 끝을 잘라버리기 위하여
trunc 명령어를 사용할 수 있다.

SQL> col loc format a7 trunc
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------
10 ACCOUNTING NEW YOR
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

※ column 명령어로 컬럼의 형식을 설정한다면, SQL*Plus는 컬럼들이 다른 테이블에 있을지라도,
동일한 이름을 가진 모든 컬럼에 대하여 그 형식을 사용하게 된다.
사용자는 각각의 select문 앞에 다른 형식으로 column명령어를 재실행하거나,
Alias를 명시하여 각각의 컬럼명이 유일하게 할 수 있다.

7). 각 사원의 연봉을 구하기 위한 다음의 예는 컬럼 수식 sal*12에 대해 annual_salary라는 별칭을 사용한다.

SQL> select empno,ename,
2 sal*12 annual_salary
3 from emp;

EMPNO ENAME ANNUAL_SALARY
---------- ---------- -------------
7369 SMITH 9600
7499 ALLEN 19200
7521 WARD 15000
7566 JONES 35700
7654 MARTIN 15000
7698 BLAKE 34200
7782 CLARK 29400
7788 SCOTT 36000
7839 KING 60000
7844 TURNER 18000
7876 ADAMS 13200

EMPNO ENAME ANNUAL_SALARY
---------- ---------- -------------
7900 JAMES 11400
7902 FORD 36000
7934 MILLER 15600

14 개의 행이 선택되었습니다.

8). 사용자가 컬럼의 표제로 한 단어 이상을 사용하거나 대소문자를 반드시 구분하고자 한다면,
컬럼의 표제를 " "로 묶어야 한다. 이와 같은 경우에, Annual 과 Salary라는 단어 사이에 _를 사용하지 않아도 된다.

SQL> select empno,ename,
2 sal*12 "Annual Salary"
3 from emp;

EMPNO ENAME Annual Salary
---------- ---------- -------------
7369 SMITH 9600
7499 ALLEN 19200
7521 WARD 15000
7566 JONES 35700
7654 MARTIN 15000
7698 BLAKE 34200
7782 CLARK 29400
7788 SCOTT 36000
7839 KING 60000
7844 TURNER 18000
7876 ADAMS 13200

EMPNO ENAME Annual Salary
---------- ---------- -------------
7900 JAMES 11400
7902 FORD 36000
7934 MILLER 15600

14 개의 행이 선택되었습니다.


9). format 명령어로 컬럼의 표제를 변경하기 위해서 HEADING 옵션을 사용하는 방법이다.

SQL> col dname format a15 heading "Department Name"
SQL> select * from dept;

DEPTNO Department Name LOC
---------- --------------- -------
10 ACCOUNTING NEW YOR
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

※ format 명령어로 컬럼 헤딩을 변경할 때 변경하려는 헤딩의 길이를 충분히 포함할 정도의 폭 넓이를 지정해 줘야 한다.
지정 폭이 충분치 않으면 변경이 제대로 되지 않고, 자릿수에 맞춰 짤리게 된다.
즉, 아래의 예에서는 "Department Name"이라는 컬럼 헤딩이 a10 자릿수 때문에 "Department"까지만 나타난다.

SQL> col dname format a10 heading "Department Name"
SQL> select * from dept;

DEPTNO Department LOC
---------- ---------- -------
10 ACCOUNTING NEW YOR
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

★★★
10). 총계와 소계
오라클 자체내에서도 총계를 계산하기 위하여 sum함수를 내장하고 있지만,
SQL*Plus도 소계, 평균과 총계를 포함하는 요약 줄을 출력하기 위한 break 와 compute 명령어를 제공하고 있다.

comp[ute] 함수(avg,count,max,min,std,sum,var) [lab[el] 텍스트]...
of 계산을 수행할 대상 컬럼명
on 구분이 되는 (break에 사용되는) 컬럼명, 식, 별칭
report

compute 명령에 사용되는 함수
----------------------------------------------
함수 설명
----------------------------------------------
avg 평균값
cou[ut] Null이 아닌 값의 갯수
max[imum] 최대값
min[imum] 최소값
std 표준편차
sum 값의 합계
var[iance] 분산값
----------------------------------------------

emp 테이블의 부서별로 급여 합계를 구하고, 보고서 끝에는 전체 합계를 구하는 보고서를 작성하고 싶다.
부서번호와 사원번호 순서로 정렬을 한다. 부서별 합계를 구한 후에 한줄을 건너뛰게 하고,
부서번호가 중복해서 인쇄되는 것을 막고자 한다.

10-1. 먼저 deptno별로 한 줄을 건너 뛰게 하기 위해서 다음과 같이 한다.
SQL> break on deptno skip 1 on report
10-2. 부서별 급여 합계를 구하고 라벨을 'Subtotal'로 하기 위해선 다음과 같이 한다.
SQL> compute sum label 'Subtotal' of sal on deptno
10-3. 보고서 끝 부분에 한 번 전체 합계를 구해서 라벨을 "Grand Total"로 하기 위해선 다음과 같이 한다.
SQL> compute sum label 'Grand Total' of sal on report
10-4. 그리고, 앞에서 지정한 급여 sal의 포맷 '$9,999'는 합계액에는 부족하므로 늘리도록 한다.
SQL> col sal format $999,999
10-5. 보고서 형식 준비가 끝났으므로, 적당한 질의를 실행시켜 본다.
SQL> select deptno, empno, ename, sal
2 from emp
3 order by deptno, empno;

DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ---------
10 7782 CLARK $2,450
7839 KING $5,000
7934 MILLER $1,300
********** ---------
Subtotal $8,750

20 7369 SMITH $800
7566 JONES $2,975
7788 SCOTT $3,000
7876 ADAMS $1,100
7902 FORD $3,000

DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ---------
********** ---------
Subtotal $10,875

30 7499 ALLEN $1,600
7521 WARD $1,250
7654 MARTIN $1,250
7698 BLAKE $2,850
7844 TURNER $1,500
7900 JAMES $950
********** ---------
Subtotal $9,400

DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ---------

---------
Grand Tota $29,025

14 개의 행이 선택되었습니다.


11). SQL*Plus 출력에 제목달기
---- 다른 포맷팅 명령어와 관련되어, SQL*Plus는 제목을 만들기 위해 두개의 명령어를 제공한다.
ttitle은 각 페이지의 상위에 제목을 명시하기 위한 것이며, btitle은 각 페이지의 하단 부분에
제목을 명시하기 위한 명령어이다.
다음의 예에서, ttitle과 btitle은 제목, 날짜와 현재의 페이지 번호를 나타낸다.

11-1. 우선 보기 좋은 출력을 위해서 적당한 페이지 사이즈와 라인 사이즈를 지정한다.

SQL> set pages 35 ==> pagesize(축약어 pages)는 한 페이지에서의 줄의 수
SQL> set line 50 ==> linesize(축약어 line)는 출력의 한줄에 나타나는 문자의 최대수
SQL> ttitle left '2001-12-17' center 'EMP table'|'Salary Report' - 공백-(하이픈) : SQL*Plus에서 긴 명령을 다음 줄에 쓰기 위해서
> right 'Page' format 99 sql.pno ==> sql.pno는 현재의 페이지번호를 나타내는 SQL*Plus 변수
SQL> btitle '예제' ==> ttile과 btitle에서 위치를 따로 지정하지 않으면 기본적으로 가운데(center)에 위치하게 된다.
SQL> select deptno,empno,ename,sal
2 from emp
3 order by deptno,empno;


2001-12-17 EMP table|Salary Report> Page 1
DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ---------
10 7782 CLARK $2,450
7839 KING $5,000
7934 MILLER $1,300
********** ---------
Subtotal $8,750

20 7369 SMITH $800
7566 JONES $2,975
7788 SCOTT $3,000
7876 ADAMS $1,100
7902 FORD $3,000
********** ---------
Subtotal $10,875

30 7499 ALLEN $1,600
7521 WARD $1,250
7654 MARTIN $1,250
7698 BLAKE $2,850
7844 TURNER $1,500
7900 JAMES $950
********** ---------
Subtotal $9,400

---------
Grand Tota $29,025





예제

14 개의 행이 선택되었습니다.


★★★ SQL*Plus 시스템 변수 ★★★

SQL*Plus는 각각의 SQL*Plus 세션의 특성을 통제하는 많은 시스템 변수를 채택하고 있다.
예를 들어, SET명령어는 특정 값으로 시스템 변수를 설정하며, SHOW명령어는 시스템 변수의
현재 설정을 볼 수 있다. 또한 사용자는 모든 시스템 변수 설정을 보기 위하여 SHOW ALL명령어를 사용한다.

SQL> show all

▶ autocommit
autocommit(축약어 auto)은 SQL*Plus의 트랜잭션을 실행완료(commit)하는 방법을 통제한다.
autocommit이 on으로 설정되어 있으면, SLQ*Plus는 각각의 SQL문이 처리된 후에 실행완료된다.
즉, autocommit이 on으로 설정된 상태에서, 사용자가 실수로 테이블에서 일부 레코드들을 삭제했다면
사용자는 트랜잭션을 rollback 할 수 없다.
사용자는 일반적으로 autocommit을 off로 설정하는 것이 좋다.

SQL> show autocommit
autocommit OFF

▶ echo
SQL*Plus가 처리하는 SQL문을 반복하지 않으려면, echo를 off로 설정한다. 일반적으로 결과물을 만들어낸 SQL문 없이,
원하는 결과문만을 보고싶은 보고서를 준비하는 경우에 off로 설정한다.

SQL> show echo
echo OFF

▶ feedback
시스템 변수 feedback(축약어 feed)은 질의가 반환하는 레코드 수를 SQL*Plus가 나타내는 시기를 통제한다.
feedback의 기본적인 설정은 6이다. 즉, 질의가 6개 이상의 레코드를 조회하면, SQL*Plus는 레코드의 수를 보여준다.
질의가 6개 미만을 반환하면, SQL*Plus는 이 feedback 기능을 제공하지 않는다.
사용자는 feedback을 유발하는 레코드의 수를 설정할 수 있다.

SQL> show feedback
6 또는 그 이상의 행에 대한 FEEDBACK ON
SQL> select * from emp where deptno=10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 2572.5
10

7839 KING PRESIDENT 5500
10

7934 MILLER CLERK 7782 920
10


SQL> set feedback 2
SQL> select * from emp where deptno=10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 2572.5
10

7839 KING PRESIDENT 5500
10

7934 MILLER CLERK 7782 920
10


3 개의 행이 선택되었습니다. <--- feedback이 나타나는 것을 볼 수 있다.

▶ heading
--- heading(축약어 head)은 기본적으로 SQL*Plus는 컬럼 표제를 나타낸다.
표제를 보지 않으려면, heading을 off로 설정하면 된다.

SQL> show heading
heading ON
SQL> set heading off
SQL> select * from dept;

10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

▶ linesize
--- linesize(축약어 line)는 출력의 한 줄에 나타나는 문자의 최대수를 통제한다.

SQL> show line
linesize 80
SQL> set line 150

그러나 사용자가 linesize를 증가시키면, 메뉴바에서 '옵션--환경'을 선택한 후
윈도우 스크린 버퍼의 너비도 늘려야 한다.

SQL> select * from emp where deptno=10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 2572.5 10
7839 KING PRESIDENT 5500 10
7934 MILLER CLERK 7782 920 10

▶ long
사용자가 한 컬럼에 2,000자 이상을 반드시 저장해야 한다면, 사용자는 오라클의 long 데이터형을 사용하여
컬럼을 정의해야 할 것이다. SQL*Plus는 사용자가 long 시스템 변수를 늘리지 않는 한,
long 컬럼의 80자 이상은 나타내지 않을 것이다. 사용자가 예를 들어 5,000자 까지 나타내야 한다면,
long을 5,000으로 설정해야 한다. 이밖에도 사용자는 arraysize(축약어 array)를 1로 줄여야 한다.
arraysize는 데이트베이스의 데이터 인출 크기를 가리키는 환경 변수이다.

SQL> show long
long 80
SQL> show arraysize
arraysize 15

▶ pagesize
pagesize(축약어 pages)는 한 페이지에서의 줄의 수를 정의하며, 컬럼의 표제와 페이지 제목이 표현되어야
할 시기를 결정한다. 사용자가 모든 제목과 컬럼 표제를 없애려면, pagesize를 0으로 설정한다.

SQL> show pagesize
pagesize 14
SQL> set pagesize 0
SQL> select * from dept;
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> set pages 24

▶ pause
사용자가 많은 레코드를 조회하는 질의를 실행하면, SQL*Plus의 기본적인 행동은 스크린에 그 결과를 쉴새 없이 보낸다.
사용자가 pause(축약어 pau)를 on 또는 문자열로 설정하면, SQL*Plus는 사용자가 리턴키를 누른 후에 다음 결과를 스크린에
계속 보내게 된다.

SQL> select * from user_tables;

SQL> show pause
PAUSE는 OFF
SQL> set pause on
SQL> select * from user_tables;

▶ time
사용자는 time(축약어 ti)을 on으로 설정하여, SQL*Plus 프롬프트에 현재 시각을 포함시킬 수 있다.
사용자는 출력을 파일로 spooling 할 때, 이 설정이 유용하다는 것을 알게 될 것이다.

SQL> show time
time OFF
SQL> set time on
12:08:37 SQL> set time off
SQL>

▶ timing
timing(축약어 timi)은 각각의 SQL명령어에 대한 게시(timing) 통계의 표현을 on 또는 off로 설정한다.
SQL> show timing
timing OFF
SQL> set timing on
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

경 과: 00:00:00.10

SQL> set timing off


▶ 대입변수를 사용한 질의 작성하기

-- SQL*Plus 내에서, 사용자는 SQL문에서 대입 변수를 참조할 수 있다.
대입 변수로 사용자는 다른 값으로 동일한 SQL문을 재실행할 수 있다.
한 개 또는 두개의 &로 사용자 변수명 앞에 두어 대입 변수를 표시한다.

SQL> select empno,ename,job,sal
2 from emp
3 where sal > &sal_value;
sal_value의 값을 입력하십시오: 3000
구 3: where sal > &sal_value
신 3: where sal > 3000

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5500
7566 JONES MANAGER 3123.75
7658 CHAN ANALYST 3450

그런데, sal이 4000 보다 큰 사원들도 결과로 얻고 싶다. 이 때에는 질의문을 두 번 작성하지 않고,
대입변수를 사용하면 간단히 해결할 수 있다.

SQL> /
sal_value의 값을 입력하십시오: 4000
구 3: where sal > &sal_value
신 3: where sal > 4000

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5500

<예제2>
SQL> select empno,ename,job,sal
2 from emp
3 where sal > &sal_value
4 and lower(job) like '%&job_value%'; -- 문자열 변수에는 반드시 ' '를 한다.
sal_value의 값을 입력하십시오: 3000
구 3: where sal > &sal_value
신 3: where sal > 3000
job_value의 값을 입력하십시오: manager
구 4: and lower(job) like '%&job_value%'
신 4: and lower(job) like '%manager%'

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7566 JONES MANAGER 3123.75


▶ 사용자 변수를 설정하는 다른 방법
SQL*Plus는 사용자 변수를 설정하는 define과 accept 명령어가 있다.
define 명령어로 사용자는 사용자 변수를 만들고 문자 값을 여기에 부여할 수 있다.
특히, define명령어는 char 데이터형을 정의하고 싶을 때 많이 사용한다.

SQL> define job_value = 'MANAGER'
SQL> select empno,ename,job,sal
2 from emp
3 where upper(job) like '%&job_value%';
구 3: where upper(job) like '%&job_value%'
신 3: where upper(job) like '%MANAGER%'

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7782 CLARK MANAGER 2572.5
7566 JONES MANAGER 3123.75

define으로 정의된 변수는 undefine 명령을 사용할 때까지 그 상태를 유지하게 된다.

SQL> undefine job_value

=============================================================
d:\g\db\oracle\new_mine\대입변수.sql의 내용

accept job_value prompt '직업명을 입력하세요: '

select empno,ename,job,sal
from emp
where lower(job) = '&job_value'
/
=============================================================

SQL> @ d:\g\db\oracle\new_mine\대입변수.sql
직업명을 입력하세요: clerk
구 3: where lower(job) = '&job_value'
신 3: where lower(job) = 'clerk'

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7934 MILLER CLERK 920


원문: http://blog.naver.com/lyjiny/60002865980