[Oracle] Sqlplus spool 명령어 정리

[Oracle] Sqlplus spool 명령어 정리

▼spool 이란?

spool은 명령들을 일괄로 처리해 주는 명령어 로써 

spool 명령에 파일명을 파라미터로 지정하면 spool에서 실행된 모든 명령 들이 파일(txt, csv , etc) 로 저장된다. 

spool 파일명          // ex) /user/test/a.txt

-- spool 관련 명령어 . . .

ex) set echo off;

명령 구문.....     // 쿼리 또는   @쿼리파일명    ex)  /user/test/a.sql

 

spool off

 

 

▼spool 관련 명령어

set heading off    :   헤더가 표시되지 않고 데이터만 표시한다.

set pagesize 0    :    기본값은 14이며 공백을 없애기 위해 0으로 세팅, pagesize가 0이어도 컬럼명이 출력되지 않는다. 50000 (max)

set echo off     :  명령이 표시되지 않게 off한다.

set term off      :  조회 결과를 표시되지 않게 한다.

set trimspool on    :   linesize 이하로 행이 잛을 때 나머지부분을 자른다. 줄 끝 공백제거

set linesize 300    :   

set feedback off    :   결과에 대한 결과 건수와 시간이 나타나지 않는다.

set colsep ','   : 컬럼 구분자

 

set autocommit on | off | n    : 자동으로 커밋되거나 n개의 dml문 후에 자동커밋되도록 설정한다.

set feedback on | off | n   :  결과 행수를 온오프한다. n개의 이상의 데이터만 표시되게 설정한다.

set linesize 100    : 행의 사이즈

set pagesize 20   :  한 페이지에 출력될 행의 갯수

set pause on | off     :  한페이지마다 멈추게 한다.

set time on | off       :   프록프트에 시간나오기

set timing on | off     :    명령어의 실행시간을 표시해 준다.

show 환경변수명 | all     :  현재 설정되어있는 환경변수를 보기

column name heading 'student | name'     :    name 이라는 컬럼을 student|name으로바꾼다.

column sal format 0,000,000     :    포멧변경  ex) 0,035,823

column sal format 9,999,999     :    포맷변경  ex)     90,000

column [name]      :   컬럼설정을 모두 출력하거나 지정된 컬럼설정을 출력한다.

column [name] clear     :  지정된 컬럼설정을 삭제한다.

define_editor = notepad    :버퍼에 들어있는 내용을 해당에디터를 수정가능

spool filename |off | out    :  결과를 파일로 저장하기, out는 프린터로 내보내기

get filename     :  파일에 데이터를 버퍼로 올리기

save filename   :   버퍼의 내용을 파일로 저장하기

 

▼SQLPLUS 설정 

SHOW ALL      :  시스템 변수의 현재 설정 확인

HELP SET      : 설정 도움말

HELP INDEX : HELP로 볼 수 있는 명령어 모음

SET SPOOL ON 또는 SET SPOOL PATH+파일명 : 쿼리 결과를 파일로 저장함

SHOW USER : 현재 자기가 접속한 유저를 보여줌

SHOW PARAMETER OPTI : OPTI로 시작하는 설정된 파라메터를 다 보여줌

COLSEP (TEXT )   : 칼럼이 표시될때 칼럼간의 구별문자, 기본값은 공백

COL(UMN) 칼럼이나 ALIAS 옵션

CLE(AR)    : 칼럼에 지정된 형식을 지움 

FOR(MAT)    : 형식 칼럼에 대한 표시 형식과 폭을 바꿈 

CLEAR COLUMNS  : 지정된 형식을 다 지우고 싶은 경우

SET AUTOCOMMINT (ON|OFF)  : 커밋 온, 오프

SET HEADING (ON|OFF)      : 칼럼에 대한 HEADING를 표시함, 기본 값은 ON

SET FEEDBACK (ON|OFF)     : 선택된 행이 몇행인지를 표시함, 기본값은 6행 이상인 경우에 ON

SET LINESIZE (N )         : 한 라인당 표시되는 문자의 수, 기본값은 80 

SET PAGES (N )            : 한 페이지당 표시되는 라인수, 기본값은 24 

SET TIMING (ON|OFF)       : 명령문을 실행하는데 소요된 시간

SET TIME (ON|OFF)         : 현재 시간 표시

SET PAUSE (ON|OFF)        : 한 페이지씩 보기

SET UNDERLINE "="         :  SELECT 문을 실행할때 헤더의  언더라인 모양을 지정

SET UNDERLINE (ON|OFF)    : 언더라인 표시 유무

SET ECHO (ON|OFF) 

SET VERIFY (ON|OFF)  

SET TERM (ON|OFF) 

SET SERVEROUTPUT (ON|OFF) 

SET SHOWMODE (ON|OFF) 

 

▼SQL PLUS 명령어

REMARK : 보통 "REM 문자"의 형식으로 REM 다음에 나오는 문자는 주석으로 처리된다.

SET HEADSEP : HEADSEP은 HEAD SEPARATOR의 약자로, "SET HEADSEP |"일 경우, |이 두 문자를 두 라인에 걸쳐 나누어 출력되도록 하는 명령어이다.

TTITLE : 각 리포트의 위첨자를 설정한다.

BTITLE : 각 리포트의 아래첨자를 설정한다.

COLUMN : 컬럼의 목록명과 포맷을 설정한다.

BREAK ON : 각 행의 섹션을 나누고, 합을 구할 컬럼 사이를 나누는 명령어이다.

COMPUTE SUM : BREAK 명령으로 나누어진 컬럼의 데이터 행의 부분 합을 계산하는 명령어이다.

SPOOL/SPOOL OFF : 출력의 파일 저장을 실행/중지하며 저장 실행시에 출력 파일명을 "SPOOL 파일명"의 형식으로 지정한다.

/* */ : REM과 같이 주석을 사용할 때 쓰이며 "/* 주석 */"의 형식을 갖는다.

- : SQL문 안에 주석을 지정할 때 사용한다.

SAVE : 실행한 SQL문을 파일로 저장한다.

HOST : 호스트 OS로 명령을 보내며, 호스트와 오라클 사이의 INTERFACE를 제공한다. HOST 명령어 대신 !를 이용할 수 있다.

START : 파일에 저장한 SQL문 또는 PL/SQL문을 실행한다. START 명령어 대신 @을 이용할 수 있다.

EDIT : SQL 명령 창을 잠시 나가 메모장이나 VI 에디터와 같은 설정된 에디터 창을 실행한다.

DEFINE_EDITOR : EDIT 명령어로 실행시킬 에디터를 설정한다.

SAVE 파일명    : SQL 버퍼에 있는 현재의 내용을 파일로 저장한다.

GET 파일명      : 현재 디렉토리에 저장된 파일의 내용을 버퍼로 불러낸다.

START 파일명     : 이전에 저장한 명령 파일을 실행한다.

@파일명          : 이전에 저장한 명령 파일을 실행한다.( START와 동일 )

EDIT                : 편집기를 실행시켜 버퍼내용을 AFIDET.BUF로 불리는 파일에 저장한다.

EDIT 파일명      : 저장된 파일의 내용을 편집하기 위해 편집기를 실행한다.

EXIT                : SQL*PLUS를 중단한다.

!유닉스명령어   : UNIX에서 사용하는 명령어를 실행시킬수 있다.

HOST               : UNIX 형태에서 작업할수 있고, EXIT 를 치면 다시 돌아온다.

SPOOL 파일명    : RETRIEVE DATA를 파일명.LST로 저장한다.

SPOOL OFF          : SPOOL 을 끝낸다.

SPOOL OUT         : RETRIEVE DATA를 SYSTEM PRINTER로 출력하라.

▼BUFFER 편집 명령어

A(PPEND)    : TEXT LINE의 끝에 TEXT를 추가함 

C(HANGE)/OLD/NEW  : OLD를 NEW로 바꿈 

DEL N     : N LINE을 지움 

I(NPUT)    : TEXT 다음 LINE에 TEXT를 추가함 

L(IST)     : 전체 문장을 보여줌 

N TEXT     : N LINE전체를 TEXT로 바꿈 

R(UN)     : BUFFER에 있는 문장을 실행함(/ 와 같음) 

EDIT     : BUFFER에 있는 문장을 파일로 부름(AFIEDT.BUF) 

SAVE A    : BUFFER에 있는 내용을 A.SQL 파일로 저장 

GET A     : 파일 A에 있는 내용을 BUFFER로 부름 

START A  : 파일 A를 실행함 

!      : UNIX SHELL로 나들이 

!VI A.SQL    : 파일 A.SQL을 VI편집기로 부름 

▼사용 예제

ex) 1. spoolselect.sql

------------------------------------------------

set pagesize 0

set linesize 400

set echo off

set term off

set colsep '|'

set feedback off

set timing off

set trimspool on

spool ./data.txt                      

select * from dual;

spool off;

exit;

2. 호출

sqlplus 계정  @./spoolselect.sql