logo

English

이곳의 프로그래밍관련 정보와 소스는 마음대로 활용하셔도 좋습니다. 다만 쓰시기 전에 통보 정도는 해주시는 것이 예의 일것 같습니다. 질문이나 오류 수정은 siseong@gmail.com 으로 주세요. 감사합니다.

[Oracle] SQL*Plus 개요,각종 명령어들

by digipine posted Oct 28, 2017
?

Shortcut

PrevPrev Article

NextNext Article

Larger Font Smaller Font Up Down Go comment Print
?

Shortcut

PrevPrev Article

NextNext Article

Larger Font Smaller Font Up Down Go comment Print

SQL*Plus는 간단히 말해 SQL및 PL/SQL문장을 인식하고 이를 실행시켜주는 Oracle Client 툴이다. 즉 Oracle 데이터베이스 와 사용자가 상호작용을 위한 사용자 인터페이스 인 것이다.
Oracle9i에서는 SQL*Plus를 이용하여 기본적인 데이터베이스 관리자의 역할 뿐 아니라 인스턴스의 시작, 종료 등과 같은 역할도 수행이 가능하다. Oracle9i 이전 버전에서는 대부분의 데이터베이스 관리업무(administration tasks)를 Server Manager Interface를 통해 수행 하였으며 SQL*Plus는 일부의 데이터베이스 관리업무에만 이용되었던 것이다. 물론 DBA(DataBase Administrator)들은 SQL*Plus를 통해 DataBase의 시작과 종료를 수행 할 수는 없었다. 결국 DBA들은 Server Manager 와  SQL*Plus등 2개의 유틸리티를 사용하여 데이터베이스 관리업무를 수행 했으나 Oracle9i에서는 Server Manager Interface는 더 이상 이용되지 않으며 SQL*Plus만 사용되어 짐으로서 이전 버전보다 Oracle9i에서 더 중요하게 되었다.

3.2 SQL*Plus 실행 및 종료
GUI 형태로 SQL*Plus를 시작하기 위해서는 시작 메뉴  프로그램  Oracle-Orahome92  Application Development SQL Plus를  클릭하여 SQL*Plus를 실행 하며, SQL*Plus를 콘솔형태로 실행 하기 위해서는 명령 프롬프트드을  실행 후 sqlplus 라고 입력을 하면 된다. 사용자 ID는 scott,  Password는 tiger로 접속을 하기 바란다.(scott계정은 Oracle 실습을 위한 계정으로서 Oracle9i 설치시 자동으로 생성되는 계정으로 데모용 Table등이 존재한다.) 아래는 SQL*Plus 최초 접속화면 이다. SQL*Plus 종료 시 에는 quit또는 exit를 입력하면 된다.

3.3 SQL*Plus 기본 명령어

SQL*Plus의 명령어를 사용하여 주로 Table의 구조를 표시, SQL명령어를 실행, SQL문장을 편집, SQL문장들을 파일에 저장, 저장된 SQL 파일을 실행, SQL파일을 버퍼에 저장, 보고서 출력 등의 일을 할 수 있다.
SQL*Plus에는 SQL 버퍼라는 임시 기억장소가 있어 최근 수행한 명령이나 명령어의 블록을 기억하고 있으며 편집이나 파일로 저장 등이 가능하다.
이제부터의 모든 실습의 scott계정에서 이루어 질것이므로 독자들은 scott계정의 테이블들은 친숙히 봐 두어야 할 것이다. 실습내용은 우선 그대로 따라 해본 후 나중에 본인의 입맛에 맞게 이리저리 수정해 보면 많은 도움이 될 것이다.

주요 명령어

Desc[ribe] Table명[View명|Synonym명] : 테이블의 컬럼 리스트를 나타내며 컬럼에 대한 설명을 나타낸다. Select 하고자 하는 테이블의 컬럼 명이 모호한 경우에 주로 이용된다.    예)Desc Table명[View명|Synonym명]        
EXIT : SQL*Plus를 종료
Conn[ect] uid/pwd[@alias] : 데이터베이스에 대한 연결을 한다. Alias는 원격 데이터베이스에 대한 별명을 나타낸다. 데이터베이스가 로컬에 설치 되어 있는 경우 생략한다.       예)Conn scott/tiger
Spo[ol] [filename.ext|OFF] : 기술한 파일에 SQL 문장 및 그 결과 등을 저장, OFF는 스풀 파일을 닫는다.
HOST : SQL*Plus를 종료하지 않고 운영체제 명령을 실행 한다.
Run, ‘/’(슬래쉬) : 버퍼에 저장된 SQL문장을 실행
‘;’(세미콜론) : 버퍼에 입력과 동시에 실행

C:\>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on 금 Aug 8 23:09:46 2003
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
사용자명 입력: scott
암호 입력:
다음에 접속됨:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production
SQL> show user
USER은 "SCOTT"입니다
SQL> select * from tab;
TNAME           TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS           TABLE
DEPT             TABLE
EMP              TABLE
SALGRADE       TABLE
SQL> /
TNAME   TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS     TABLE
DEPT       TABLE
EMP        TABLE
SALGRADE  TABLE
SQL> desc emp
이름                    널?      유형
----------------------------------------- -------- -------------------
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> connect / as sysdba
연결되었습니다.
SQL> connect scott/tiger
연결되었습니다.
SQL> spool test.sql
SQL> select ename, sal from emp
  2  where ename like '%SMITH%';
ENAME   SAL
---------- ----------
SMITH    800
SQL> spool off


C:\ 아래에서 SQL*Plus를 실행 했으므로 C:\ 아래에 test.sql 파일이 존재하며 내용은 다음과 같다. 만약 시작 메뉴를 통해 SQL*Plus를 실행했다면 test.sql 파일은 C:\oracle \ora92\bin에 생성된다. (Oracle Home Directory가 c:\oracle 인 경우)

SQL> select ename, sal from emp
  2  where ename like '%SMITH%';
ENAME   SAL                                                          
---------- ----------                                                          
SMITH    800                                                          
SQL> spool off


편집 명령어

버퍼에 입력된 내용을 편집하기 위해 사용하는 명령들 이다.

I[NPUT] text : 새로운 라인을 삽입
D[EL] : 현재 버퍼 라인의 내용을 삭제
D[EL] n : 버퍼에 있는 n번째 라인 삭제
D[EL] n1 n2: 버퍼에 있는 n1 부터 n2까지의 라인 삭제
L[IST] : 버퍼에 있는 모든 내용을 화면에 표시
L[IST] n : 버퍼의 라인 중 n라인의 내용을 화면에 표시
L[IST] n1 n2 : 버퍼의 라인 중 n1 라인부터 n2 라인까지를 화면에 표시
C[HANGE] : 현재 버퍼 라인의 내용을 교체(형식 : C/old문자열/new문자열)
N text : 라인n의 내용을 text로 변경
0 text : 첫번째 라인 앞에 한 라인을 삽입
Cl[ear] Buff[er] : 버퍼의 내용을 전부 지움

SQL> select ename as 사원명 , sal as 급여
  2  from emp
  3  where ename = 'SMITH';
사원명           급여
---------- ----------
SMITH             800
SQL> run
  1  select ename as 사원명 , sal as 급여
  2  from emp
  3* where ename = 'SMITH'
사원명           급여
---------- ----------
SMITH             800
SQL> list
  1  select ename as 사원명 , sal as 급여
  2  from emp
3* where ename = 'SMITH'
SQL> list 1
  1* select ename as 사원명 , sal as 급여
SQL> list 1 2
  1  select ename as 사원명 , sal as 급여
2* from emp
SQL> list 3
  3* where ename = 'SMITH'
SQL> i or ename = 'WARD'
SQL> /
사원명           급여
---------- ----------
SMITH             800
WARD             1250
SQL> list
  1  select ename as 사원명 , sal as 급여
  2  from emp
  3  where ename = 'SMITH'
4* or ename = 'WARD'
SQL> list 4
  4* or ename = 'WARD'
SQL> c/WARD/FORD
  4* or ename = 'FORD'
SQL> /
사원명             급여
----------           ----------
SMITH             800
FORD             3000
SQL> list
  1  select ename as 사원명 , sal as 급여
  2  from emp
  3  where ename = 'SMITH'
  4* or ename = 'FORD'
SQL> del 4
SQL> list
  1  select ename as 사원명 , sal as 급여
  2  from emp
  3* where ename = 'SMITH'
SQL> /
사원명             급여
----------           ----------
SMITH             800
SQL> 3 where sal > 2000
SQL> /
사원명            급여
----------           ----------
JONES            2975
BLAKE            2850
CLARK            2450
SCOTT            3000
……
SQL> list
  1  select ename as 사원명 , sal as 급여
  2  from emp
  3* where sal > 2000
SQL> del
SQL> list
  1  select ename as 사원명 , sal as 급여
  2* from emp
SQL> list 1
  1* select ename as 사원명 , sal as 급여
SQL> del
SQL> list
1* from emp
SQL> clear buffer
buffer 소거되었습니다.
SQL> list
SP2-0223: SQL 버퍼에 줄이 없습니다.


파일 관련 명령어

파일과 관련된 SQL*Plus 명령어들 이다. 사용법을 확인 후 실습을 통해 이해하자.

SAVE test : 버퍼의 내용을 test.sql로 저장
SAVE test RE[LACE]  : 버퍼의 내용을 기존 파일인 test.sql에 덮어 쓴다.
APP[END] test : 버퍼의 내용을 test.sql에 추가 한다.
GET test : test.sql의 내용을 버퍼로 불러오며 화면에 출력한다.
EDIT test : test.sql의 내용을 버퍼로 불러오지 않으며 편집을 위해 로딩한다.
EDIT : 현재 버퍼의 내용을 편집을 위해 로딩 한다.
START test : test.sql의 내용을 실행 한다.
@test : test.sql의 내용을 실행 한다.

SQL> select ename as "사원명" , sal as "급여"
  2  from emp
  3  where ename = 'SMITH'
  4  /
사원명           급여
---------- ----------
SMITH             800
SQL> save test
file test.sql(이)가 생성되었습니다
SQL> select count(*) from emp;
  COUNT(*)
----------
     14

SQL> list
  1* select count(*) from emp
SQL> get test
  1  select ename as "사원명" , sal as "급여"
  2  from emp
  3* where ename = 'SMITH'
SQL> list
  1  select ename as "사원명" , sal as "급여"
  2  from emp
  3* where ename = 'SMITH'
SQL> edit test
[실습3-3]

edit test 명령 후 새 창에서 test.sql 파일의 내용이 로딩되며 파일의 형태는 다음과 같다.

select ename as "사원명" , sal as "급여"
from emp
where ename = 'SMITH'
/
[test.sql 의 내용]

Test.sql의 3번째줄의 내용을 where ename=’WARD’로 바꾼 후 저장, 종료하자.

SQL> edit test
SQL> @test
사원명           급여
----------         ----------
WARD           1250
SQL> start test
사원명           급여
----------         ----------
WARD           1250
SQL> run
  1  select ename as "사원명" , sal as "급여"
  2  from emp
  3* where ename = 'WARD'
사원명           급여
----------         ----------
WARD           1250
SQL> run test
  1  select ename as "사원명" , sal as "급여"
  2  from emp
  3* where ename = 'WARD'
사원명           급여
----------         ----------
WARD           1250


Set 명령어

Set명령은 현재 SQL*Plus의 세션환경을 제어하며 모든 Set 변수 값을 보기 위해서는 Show All명령을 이용하면 된다. 물론 시작 메뉴  프로그램  Oracle-Orahome92  Application Development SQL Plus를 이용하여 실행(GUI 형태로 실행) 하였다면 옵션  환경에서 설정 값들을 확인 할 수 있다. 여기서는 모든 SQL*Plus 환경 변수를 살펴 보지 않을 것이며 필요한 몇 가지를 예제를 통해 이해하자.


SET AUTO[COMMIT] ON/OFF : 자동으로 DML의 실행 결과를 데이터베이스에 반영 할 지의 여부를 결정, 기본적으로 OFF로 되어 있어 Insert/Update/Delete등의 DML(Data Manupulation Language)을 사용시 Commit이라고 기술 해야 한다. 만약 AutoCommit이 ON으로 되어 있다면 트랜잭션을 취소 하는 RollBack은 불가능 하다.

SQL> create table addrbook (
  2  name varchar2(10) not null,
  3  addr varchar2(50),
  4  tel varchar2(20));
테이블이 생성되었습니다.
SQL>  show autocommit
autocommit OFF
SQL>  insert into addrbook values ('홍길동','서울 강남구 역삼동 11', '02-222-3333’);
1 개의 행이 만들어졌습니다.


여기까지 수행했다는 가정 하에 다른 사용자가 SQL*Plus를 scott계정으로 똑 같이 접속을 해서 “홍길동”이라는 데이터가 입력 되었는지 확인을 해 보도록 하자.
SQL> select * from addrbook;

선택된 레코드가 없습니다.

데이터가 한건도 없는 것은 당연하다. 실습3-5에서 수행한 DML인 Insert문장의 결과가 반영되지 않았기 때문이다. 다른 사용자에서도 “홍길동”이라는 데이터가 보이게 하기 위해서는 Commit을 해 주면 된다. 실습3-5의 마지막 부분에 Commit이라는 명령을 기술하고 다시 별도의 SQL*Plus를 띄운 후 scott 계정으로 접속을 하여 결과를 확인 해 보라. 다음과 같이 나타날 것 이다.
SQL> select * from addrbook;
NAME       ADDR
---------- --------------------------------------------------
TEL
--------------------
홍길동     서울 강남구 역삼동 11
02-222-3333

SET PAU[SE] ON/OFF : Query의 결과가 한 화면을 넘어가는 경우 한화면씩 보이게 할 건지의 여부를 결정한다. ON인 경우 Enter Key를 입력하면 결과를 한 화면씩 보인다.
SQL> show pause
PAUSE는 OFF
SQL> select object_name, object_type from user_objects;
SQL>-- 결과가 여러 화면을 넘어가므로 마지막 화면만 여러분의 눈에 보일 것이다.
SQL>set pause on
SQL> select object_name, object_type from user_objects;
SQL> -- Enter Key를 입력하여야 한 단계씩 다음으로 진행 된다.


SET HEA[DING] ON/OFF : 컬럼 명을 나타낼 지의 여부를 결정한다. 기본값은 ON이며 컬럼 명을 나타낸다.
SQL> show heading
heading ON
SQL> set heading off
SQL> select ename, sal from emp
  2  where ename = 'SMITH';
SMITH            


SET ARRAY[SIZE] ON/OFF : 데이터베이스로부터 한번에 처리 되어 지는 행의 수를 지정
SET ECHO ON/OFF : SQL 파일 등을 실행 시 파일의 SQL 문장을 보일 건지의 여부를 지정 , 기본값은 OFF로서 파일의 SQL 문은 화면에 보이지 않는다.
SQL> show echo
echo OFF
SQL> @test
사원명           급여
----------         ----------
SMITH           800
SQL> set echo on
SQL> @test
SQL> select ename as "사원명" , sal as "급여"
  2  from emp
  3  where ename = 'SMITH'
  4  /
사원명             급여
----------          ----------
SMITH             800


SET EDITFILE 파일이름 : edit 명령등으로 현재 버퍼의 내용을  default Editor로 부를때의 기본 이름을 지정. 지정하지 않으면 afiedt.buf로 자동 지정된다.
SQL> set editfile firstSQL.sql
SQL> edit
file firstSQL (이)가 기록되었습니다
  1  select ename as "사원명" , sal as "급여"
  2  from emp
  3* where ename = 'SMITH'

SET LONG n : 한 컬럼의 데이터 크기가 2G Bytes에 이르는 가변길이 데이터형인 LONG, 4G Bytes 까지의 character를 저장 할 수 있는 CLOB, NCLOB등의 데이터가 화면에 표시되는 최대 Character수를 지정, 기본값은 80
SQL> create table LongTest (
  2  title varchar2(50) not null,
  3  content long);
테이블이 생성되었습니다.
SQL> insert into LongTest values ('첫번째 게시물...','반갑습니다... 여러분들 뵙게 되어 영광 입니다.');
1 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> select content from LongTest;
CONTENT
-------------------------------------------------------------------------------
반갑습니다... 여러분들 뵙게 되어 영광 입니다.
SQL> set long 10
SQL> select content from LongTest;
CONTENT
----------
반갑습니다


SET TIME ON/OFF : SQL*Plus 프롬프트상에 시각 표시 여부를 지정, 기본적으로 OFF이며 시각은 표시 되지 않는다.
SET TIMING ON/OFF : SQL문의 실행 때부터 실행 결과가 응답될 때까지의 시간을 파악하여 출력할 지의 여부를 지정, 기본적으로 OFF이며 응답시각은 표시 되지 않는다.

SQL> show time
time OFF
SQL> set time on
23:09:36 SQL> show timing
timing OFF
23:09:43 SQL> set timing on
23:10:17 SQL> select ename, sal from emp
23:10:25   2  where ename like 'S%';
ENAME           SAL
----------          ----------
SMITH             800
SCOTT            3000

경   과: 00:00:00.00

TAG •

List of Articles
No. Subject Author Date Views
22 Apache Tomcat 설치후 관리자 모드 실행 되지 않을때 문제 해결방법 digipine 2017.11.01 3281
21 Choosing a free database - DB2 digipine 2017.10.29 1472
20 Docker를 통한 MySQL 설치하기 lizard2019 2021.04.15 871
19 Eclipse, JSP, Beans 실습하기 digipine 2017.10.29 3313
18 Filter를 이용한 웹 프로그래밍 Filter 란 무엇인가? file siseong@naver.com 2017.10.31 4902
17 HTTP Multi Part Request Sample Format digipine 2017.11.01 912
16 MS Access MDB를 위한 DB Connection String digipine 2017.10.29 2831
15 MySQL 5.7.x 버전 root 및 유저 암호(Password) 변경 digipine 2017.11.02 875
14 MySQL Data Backup - Database Dump 방법 및 복구 방법 엉뚱도마뱀 2017.11.18 1050
13 MySQL root 비밀번호 변경하기 (MySQL 5.7버전 이상) lizard2019 2019.06.10 3543
12 MySQL 시작할때 No directory, logging in with HOME=/ 에러 해결 방법, 홈 디렉토리 변경 digipine 2021.05.06 1133
11 SQLite 활용 팁 1 - 트리거 활용하기 digipine 2017.11.01 611
10 SQLite 활용 팁 2 - 트리거 실전 사용하기 digipine 2017.11.01 573
9 SQLite 활용 팁 2 - 트리거 실전 사용하기 digipine 2017.11.01 674
8 SQLite 활용 팁 3 - 다른 DB 테이블에 트리거 사용하기 digipine 2017.11.01 948
7 SyBase 사용법 요약 digipine 2017.10.28 1232
6 SyBase 와 JSP 연동 digipine 2017.10.28 1126
5 Ubuntu mySql 완전 삭제하기 lizard2019 2021.04.15 1168
4 Ubuntu에서 sqlite3 빌드 방법 digipine 2017.11.01 926
» [Oracle] SQL*Plus 개요,각종 명령어들 digipine 2017.10.28 1484
Board Pagination Prev 1 2 Next
/ 2