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