盡人事待天命

미디어협동조합 국민TV
Sun Solaris8 (Sun Sparc 플랫폼) 에서 Oracle 8i 설치
글쓴이 : 노상근 sknho@deskpia.com , 추가 배중곤 cgv1004@hanafos.com
=== 배중곤 추가 부분 ===
원본 문서는 intel 플렛폼을 기반으로 하고 있으나 저는 Sun Sparc에서 설치한 경험을 적어놓은 것입니다. 하지만 여러모로 비슷할 것이라 생각합니다. 한번 시도해보시길.. 그리고 인텔의 특징적 사항이 있다면 또한 추가해서 계속 돌리시는것도 좋다고 생각합니다.

Sun Solaris 8 오라클 설치시 먼저 해둘 일 !!!!!

root로 해야하는 작업이 있다. 아래 나오는 파일을 수정해줘야 한다.
/etc/system 파일에 아래를 추가해 쓴다.
vi system

------------------------------------------------------------
#* 오라클용 공유메모리,세마포어 세그먼트,식별자 세그먼트 설정.
#* 우리 회사 Ultra 80 ( 2cpu , 256MB ram , 30GB 스카시하드 ) 모델의 설정값.
set noexec_user_stack=1
set noexec_user_stack_log=1
forceload:sys/shmsys
forceload:sys/semsys
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmni=70
set semsys:seminfo_semmsl=160
set semsys:seminfo_semmns=310
set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767
============================================================================================
설치작업을 할 oracle 이라는 계정을 만들어 준다.
그룹은 그냥 dba 하나로만 한다.
계정이 만들어지면 계정 홈디렉토리 안에있는 프로파일 화일인 " .profile " 에 아래와 같이 추가한다.
아래는 본쉘을 사용하는 것을 기준으로 한다.

ORACLE_SID=ADSVC; export ORACLE_SID
ORACLE_HOME=/export/home/ora817; export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin; export PATH
ORACLE_TERM=vt100; export ORACLE_TERM
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/openwin/lib:/usr/bin:/usr/lib; export LD_LIBRARY_PATH
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data; export ORA_NLS33
ORACLE_OWNER=oracle; export ORACLE_OWNER
#DISPLAY=211.38.1.164:0.0; export DISPLAY
NLS_LANG=American_America.KO16KSC5601; export NLS_LANG
#LANG=en; export LANG

alias ls='ls -p'
alias ll='ls -al'
alias l='ls -l'
alias c='clear'

PS1='[u@H:W]$'

umask 022
============================================================================================


[ 예제 1 ]  Solaris 2.3/2.4    

 1. SQLDBA 에서 :    
  SQLDBA> shutdown    
  SQLDBA> exit    
 
 2. Superuser(root)로 login 하고 :    
  # cd /etc
 
 3. /etc/system file 에 다음을 추가 한다.
(저 위의 값을 추천한다.) 왜 이런 값을 넣어야 하는지에 대한 이유를
정확이 아는바가 없음이 죄송스럽습니당. 아래는 울회사 서버의 값입니다. 그대로 써두 될듯.
set noexec_user_stack=1
set noexec_user_stack_log=1
forceload:sys/shmsys
forceload:sys/semsys
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmni=70
set semsys:seminfo_semmsl=160
set semsys:seminfo_semmns=310
set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767
    
 4. Kernel을 reconfigure 한다:    
  # touch /reconfigure    
    
 5. Machine 을 reboot 한다:    
  #init 6    
    
 6. SQLDBA 에서 :    
   SQLDBA> startup    
   SQLDBA> exit    
    
Oracle의 init.ora Parameters 에는 SGA 의 영향을 주는 Parameters가 있다. OS의 Shared Momory 와 Semaphore Parameter 에 연결된 이 Parameter의 Setting 은 System 과 Oracle의 Performance에 영향을 미친다.   
   

### 다음은 oracle 계정의 생성 및 프로파일 설정..
[#] groupadd -g 5000 dba
[#] useradd oracle -g dba
기존사용자를 바꿀땐 : usermod -g dba oracle



vi .profile 로 아래 부분 추가.
--------------------------------------
ORACLE_SID=원하는SID값; export ORACLE_SID
ORACLE_HOME=/export/home/ora817; export ORACLE_HOME
#ORACLE_BASE=/export/home/ora817; export ORACLE_BASE
PATH=$PATH:$ORACLE_HOME/bin; export PATH
ORACLE_TERM=vt100; export ORACLE_TERM
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/openwin/lib:/usr/bin:/usr/lib; export LD_LIBRARY_PATH
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data; export ORA_NLS33
ORACLE_OWNER=oracle; export ORACLE_OWNER
# DISPLAY=:0.0; export DISPLAY
NLS_LANG=American_America.KO16KSC5601; export NLS_LANG
#임시디렉토리는 오라클 홈 밑에 tmp 디렉토리를 만들어준다.
TMDIR=/export/home/ora817/tmp; export TMDIR
#TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

#아래 언어설정은 혹시나 인스톨시 디비생성을 바로 못하고 스크립트로 남긴 후 수동으로
## dbassist 명령어로 생성하려고 할때엔 언어설정을 풀어서 환경을
## 영어로 재적용(리부팅 등의 방법을 써서~) 해주고 해야 잘 됩니다.
#LANG=en; export LANG

alias ls='ls -p'
alias ll='ls -al'
alias l='ls -l'
alias c='clear'

PS1='[u@H:W]$'
umask 022
#------------------------


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


Sun 솔라리스 8 (intel 플랫폼)을 처음 받았을 때 함께 들어있는 Oracle 8i 데이터베이스 CD를 보노라면 웬지 뿌듯한 느낌을 받게 된다. 전세계 DB시작을 석권한 오라클에서 리눅스용에 이어 선 솔라리스 사용자들을 위한 제품까지 만들어서 서비스를 시작한 것이다. 사실 오라클 DB의 가격은 결코 만만치 않다. 선 엔터프라이즈용 오라클 DB의 가격을 아는 이들이라면 이렇게 운영체제와 함께 제공되는 오라클 CD에 대해 묘한 감정을 느낄 것이다.
동봉된 오라클 DB는 개발자용이다. 그러나 서버용 DB로도 훌륭히 수행된다.
한가지... 처음 DB를 다루는 분들께는 오라클 이외에 다른 제품을 권하고 싶다. 설치부터가 만만한 제품이 아니기 때문이다. 필자의 개인적 경험상 사용하기 무난한 제품으로는 MS의 MS-SQL Server 7.0을 권하고 싶다. 그러나 이미 어느정도 DB에 대한 지식을 쌓은 분이라면, 그래서 SQL문법에 능통한 분이라면 오라클은 결코 지나칠 수 없는 제품이리라.
작년 (1999년) 초, 리눅스용 오라클을 설치했을 때 몇시간을 헤매던 생각이 났다. 잡지에 나온 대로 설정을 시도했으나 몇 번의 실패끝에 결국은 나름대로의 방식대로 설정하여 겨우 DB가 startup 되는 모습을 볼 수 있었다. 이에 반해 NT용 오라클 서버는 그리 까다롭지 않은 설치방식을 제공한다. 운영체제에 따라 설치방식이 극과 극을 달리는 듯 하다.
사실 이번 솔라리스용 오라클 8i 역시 몇 번의 시행착오를 거친 끝에 설치를 할 수 있었다. 특히 환경변수 설정이 무엇보다 중요하다. 유닉스나 리눅스에 대한 기본 지식이 없이는 시도하지 말 것을 당부한다.
자 준비가 되었으면 설정에 들어간다. 아래 글은 읽는 이들이 어느정도 유닉스나 리눅스에 대하여 기본 지식이 있음을 전제로 작성하였다.


1. 설치를 위한 기본 환경
- 메모리 권장 : 128 MB 이상
- HDD : 1 GB정도의 빈 공간.
- CPU : Celeron 366 혹은 펜티엄 II 350 이상 .
위의 사양은 말 그대로 설치를 위한 최소사양이다. 위 내용중 메모리는 256MB를 장착하면 쾌적하게 오라클을 설치하여 사용할 수 있다. 128MB와 256MB의 차이점은 오라클 설치에 필요한 소요시간에서도 명확히 나타난다.
2. ROOT 권한하에서 할 일.
가. ORACLE 계정 등록
- 오라클 설치 및 운영을 위한 USER 계정을 새로 만든다. (admintool을 이용하는 것이 편하다) 아래와 같이 설정해주면 된다.
* 사용자명 : oracle (임의로 설정가능)
* Primary Group : dba
* Secondary Group : oinstall
* 홈 디렉토리 : / OraHome1 (임의로 설정 가능. 단 오라클 사용자 자체의 계정의 홈 디렉토리와는 절대로 일치시키지 말 것. 과거 리눅스용은 오라클 홈 디렉토리로 사용자 홈 디렉토리를 설정하였으나 솔라리스용은 다르다)
* Login Shell : Bourne shell (/bin/sh)
나. 솔라리스 커널 Parameter 설정
/etc/system 파일 맨 밑부분에 아래 내용을 삽입한다.
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=100
set semsys:seminfo_semmns=200
set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767
** 상기 사항을 입력할 때 주의하세요. 오타가 나면 부팅시 에러메시지가 나타납니다.




다. 환경변수 설정
- oracle user의 환경변수를 설정해준다. oracle user의 홈 디렉토리안에 있는 .profile을 수정해주면 된다.
문서 맨 위 .profile 적용사항을 보시고 하세요.!!!
** 주의사항 : oracle 계정은 위에 언급한 대로 Bourne shell을 사용한다. C shell과는 달리 Bourne shell이나 Korn shell에서는 환경변수를 지정한후에는 반드시 export 명령을 주어야만이 변수가 적용된다.
예를 들어 위에서 설정한 ORACLE_HOME=/export/home/OraHome1 의 경우에도 밑줄에 export ORACLE_HOME 을 추가로 설정해 주어야 된다.
여기까지는 oracle을 인스톨하기 전에 만들어준다.
라. oracle 홈디렉토리의 사용권한 설정
- oracle user가 새로이 생성될 오라클 홈디렉토리에 쓰기권한을 가질 수 있도록 해주는 것이다. 만일 오라클 홈디렉토리가 위에 지정한 것처럼 /export/home/OraHome1이라면..
# chown oracle:dba /export/OraHome1
이렇게 지정해주면 된다.




3. oracle 사용자로서 할 일
root 권한하에서 할 일을 마쳤으면 이제 새로 생성한 oracle user로 다시 login하여 다음 작업을 수행해주자.
가. umask 값 확인
- umask라고 쉘에서 입력했을 때 0022값이 나오면 된다. 다른값이 나왔다면 .profile에서 umask 설정을 잘못해준 것이다.
4. oracle 데이터베이스 설치
아래 내용은 모두 oracle 사용자로 로긴하여 수행해야 하는 일이다. 절대 root로 login하지 말기 바란다.
가. 로긴시 언어설정
가장 애를 먹은 부분이다. 이제껏 기본적으로 한글 (Korean) 환경에서 솔라리스를 수행하여 왔을 것이다. 그러나 Oracle database를 설치할 때 만큼은 영어환경에서 설치를 해주도록 하자. 그 이유는.... 한글환경에서 설치를 해보면 안다. Oracle Database Configuration Assistant를 수행할 때 나오는 원인모를 에러.. 그리고 멈춤. 이것 때문에 며칠을 허송세월하였다. 결국 오라클 사용자모임의 게시판에서 해답을 얻을 수 있었다.
나. Oracle 8i CD 삽입
다 아는 내용이겠지만 솔라리스는 CD를 삽입함과 동시에 자동으로 CD를 마운트해준다. 오라클 CD를 넣으면 CD의 루트 디렉토리에 있는 파일들이 파일관리자 화면에 보일 것이다. 여기서 아래에 흰색으로 표시된 runInstaller 파일을 수행시키면 된다. 마우스로 더블클릭해보자. JAVA를 기반으로 한 멋진 GUI환경의 Universal Installer Program이 실행된다.

오라클 CD를 넣었을 때 나타나는 파일매니저


다. Oracle 8i 설치
설치 프로그램 화면을 따라가면서 설치를 수행하자.
설치도중 tmp/OraInstall/orainstRoot.sh 스크립트 파일을 root 권한으로 수행하라고 나온다. (물론 새로 root로 로긴하라는 것이 아니고 잠시 su를 이용 root권한을 가지고 하면 된다)
File location 지정화면은 특별한 이유가 없는 이상 시스템에서 기본적으로 지정하는 위치에 설치하면 된다.
설치하는 유형을 선택하는 메뉴는 다음 3가지로 나뉜다.
1) Oracle Enterprise Edition 8.1.5 : Oracle db server와 관련된 모든 프로그램
2) Oracle 8i Client 8.1.5 : client용 프로그램
3) Oracle Programmer 8.1.5 : 기본적인 클라이언트 프로그램 (Net8, SQL*Pluse) 및 프리 컴파일러, 도움말등.
위에서 자신에게 알맞는 사항을 선택후 Next 버튼을 누르면 설치유형 선택화면이 나온다. 여기서 Custom을 선택하여야만 프로그램 및 데이터베이스에서 사용할 언어를 선택할 수 있다.
다음 화면은 설치가 가능한 각 프로덕트가 박스안에 나열된다. 상단의 'Product Language'를 클릭하면 설치할 언어를 선택할 수 있다. 기본적으로는 'English'로 되어있겠지만 이것을 'Korean'으로 변경한다.
Java Runtime Applicaiton의 경로설정 및 보안인증방법 (Authentification Methods) 설정부분은 그냥 Next 버튼을 누르면 기본값으로 설정된다.
Oracle Home Directory는 기본적으로 export/home/OraHome1으로 되어있을 것이다. 설정된 기본값 그대로 하면 된다.
오라클 소프트웨어를 갱신할 수 있는 UNIX 그룹을 지정하라고 나오면 oinstall을 입력한다.
설치에 필요한파일이 복사되는 과정이 진행된다.
라. Net8 Configuration
네트웍 설정부분이다. 이곳을 click하면 자세한 사항이 나온다.

** 설치중 정해주어야 할 값.
Oracle SID: 4-5자정도의 짧은 알파벳 이름을 지어준다. 이값은 추후 .profile에도 설정해주어야 하므로 잊지 않도록 한다. (Default : ORCL)
DB 명 : 역시 자기마음에 드는 이름을 지어준다

.

마. 데이터베이스 생성
설치 후반부로 가면 자동으로 수행에 필요한 데이터베이스 생성으로 들어간다. (Oracle Database Configuration Assistant) 데이터베이스 생성 옵션으로서는 설치 CD에 있는 데이터베이스 파일 복사를 선택하면 된다. 데이터베이스 생성작업에 소요되는 시간은 시스템 메모리 크기에 따라 차이가 있을 것이다. (메모리가 클수록 소요시간은 짧아진다)



바. root.sh 실행
필요한 파일 설치가 끝나고 링크작업이 완료되면 Oracle Home 디렉토리에 있는 root.sh를 root권한으로 실행하라는 메시지가 나온다. 이것을 실행시켜도 실행이 되지 않을 경우에는 chmod 명령을 사용하여 root.sh의 속성을 실행가능 파일로 바꿔준다.
실행후 ORACLE_OWNER, ORACLE_HOME, ORACLE_SID의 설정값이 나오면 이것을 확인해둔다. local bin 디렉토리의 full path명은 자신의 시스템에 설정된 값으로 고쳐준다. (예. /usr/bin)
모든 설치가 끝나게 되면 아래와 같은 엔딩 화면을 보게된다. 이걸 보기위하여 얼마나 많은 노력을 하였는가.... 라고 생각하면 안된다. 아직 확인해야 할 일이 많다.

5. 설치후 해야할 일
가. 환경변수 추가 설정
- oracle user의 환경변수를 추가로 설정해준다. 설치하기 전과 마찬가지로 oracle user의 홈 디렉토리안에 있는 .profile을 수정해주면 된다.
* ORACLE_SID= 아까 설정한 값을 넣어준다. (예 : ORCL등)
* CLASSPATH=/export/home/jre/1.1.7/bin:$ORACLE_HOME/jlib
* LD_LIBRARY_PATH=$ORACLE_HOME/lib
** 참고로 필자의 oracle 계정에 설정한 .profile값을 공개한다. 여기서 DISPLAY 변수에 나오는 서버명(deskpia)은 여러분의 시스템에 맞게 수정해야 한다. 나머지 사항은 대체로 동일하게 설정해도 되는 사항들이다.

tty istrip
DISPLAY=deskpia:0.0
export DISPLAY
ORACLE_HOME=/export/home/OraHome1
export ORACLE_HOME
PATH=/usr/bin:/usr/ccs/bin:/usr/ucb:/etc:$ORACLE_HOME/bin:/bin:/opt/bin
export PATH
TMDIR=/var/tmp
export TMDIR
umaks=022
export umask
ORACLE_OWNER=oracle
export ORACLE_OWNER
ORACLE_SID=ORCL
export ORACLE_SID
CLASSPATH=/export/home/jre/1.1.7/bin:$ORACLE_HOME/jlib
LD_LIBRARY_PATH=/usr/java/lib:$ORACLE_HOME/lib
이제 시스템을 rebooting후 oracle user로 새로이 login한다.
6. 오라클 구동 확인
가. 이미 path가 지정되어 있으므로 곧바로 아래와 같이 쉘에서 입력해보자.
$ svrmgrl
아래와 같이 화면이 나타나면 1차 성공이다.
Oracle Server Manager Release 3.1.5.0.0 - Production
(c) Copyright 1997. Oracle Coprporation. All Rights Reserved.
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
with the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
SVRMGR>
나. 이제 데이터베이스에 연결해본다.
SVRMGR> connect internal
Connected 라고 나오면 된다. 여기서 Connected라는 메시지와 함께 어떤 error 메시지가 나온다면 오라클 db가 정상적으로 설치되지 못한 것이다.
다. 다음으로는 데이터베이스를 구동시킨다.
SVRMGR> startup
잠시후 아래와 같이 메시지가 나오면 성공적으로 데이터베이스가 구동되고 있는 것이다.
** 아래 수치는 시스템에 따라 다를 수 있음 **
Oracle instance started
Total System Global Area 35028368 bytes
Fixed Size 64912 bytes
Variable Size 18014208 bytes
Database Buffers 16777216 bytes
Redo Buffers 172032 bytes
Database mounted.
Database Opened.
라. 내친김에 데이터베이스에 있는 테이블들을 살펴보자.
SVRMGR> select * from tab;
수많은 테이블들이 주욱 나오면서 마지막에 다음 메시지가 나올 것이다.
1433 rows selected.
이제 오라클 데이터베이스 설치라는 기나긴 여정이 끝났다. 남은 일은 dba로서 해야 할 일이다. 항상 끝마칠 때 shutdown을 잊지 않으면 아무런 문제없이 oracle DB를 사용할 수 있을 것이다.

p.s. http://technet.oracle.com으로 가면 오라클 데이터베이스에 관한 아주 많은 자료들이 체계적으로 정리되어 있다. 이것을 이용하려면 회원으로 가입하면 된다. 회비는 무료이다. 물론 모든 자료는 영어로 되어 있다.


[출처]http://kin.naver.com/knowhow/entry.php?d1id=8&dir_id=8&eid=JEXGjcF3YFsEf1k1cHqlokd5P8FgsN6t&qb=b3JhY2xl
Posted by 톰켓 Trackback 0 Comment 0

오라클 세션정보 보기


SELECT   

         s.status "Status", s.serial# "Serial#", s.TYPE "Type",
         s.username "DB User", s.osuser "Client User", s.server "Server",
         s.machine "Machine", s.module "Module", s.client_info "Client Info",
         s.terminal "Terminal", s.program "Program", p.program "O.S. Program",
         s.logon_time "Connect Time", lockwait "Lock Wait",
         si.physical_reads "Physical Reads", si.block_gets "Block Gets",
         si.consistent_gets "Consistent Gets",
         si.block_changes "Block Changes",
         si.consistent_changes "Consistent Changes", s.process "Process",
         p.spid, p.pid, si.sid, s.audsid, s.sql_address "Address",
         s.sql_hash_value "Sql Hash", s.action
    FROM v$session s, v$process p, sys.v_$sess_io si
   WHERE s.paddr = p.addr(+)
     AND si.sid(+) = s.sid
     AND (s.username IS NOT NULL)
     AND (NVL (s.osuser, 'x') <> 'SYSTEM')
     AND (s.TYPE <> 'BACKGROUND')
ORDER BY 1


세션 죽이기

alter system kill session 'sid, serial#';


시스템 테이블 접근 권한이 있어야 합니다

Posted by 톰켓 Trackback 0 Comment 1

--nvl(comm,0) --> comm이 널이면 0으로 대체
--nvl2(comm, 수식1, 수식2) comm이 널이 아니면 수식1을, 널이면 수식2를 반환
--coalesce(수식1, 수식2, 수식3, ...) 수식1이 널이 아닐 경우에 반환하고 널이면 수식2를, 수식2도 널이면 수식3을.... 반환
--|| --> 두 컬럼을 한컬럼으로 출력 (문자/숫자 구분없이 결합함)
--distinct --> 중복열을 제거하여 오름정렬 후 출력
--chr(65) --> 아스키65값을 문자A로 표시
--ascii('A') --> 문자 A 를 아스키값 65로 표시]
 
alter session set nls_date_format=   -- sssss : 오늘 하루를 초로 변환하여 나타냄...
'yyyy-mm-dd hh:mi:ss sssss am';      -- hh : 12시간제, hh24 : 24시간제로 나타냄... (현재 세션에서만 적용됨)

select value from v$parameter where name='db_block_size'; --환경정보확인(블럭사이즈)

show parameter db_block_size; -- 기본환경정보 확인 (블럭사이즈)
 
***문자처리함수***
--upper(' ') --> 대문자로 변환
--lower(' ') --> 소문자로 변환
--initcap(' ') --> 첫 문자만 대문자로 변환, 나머지는 소문자로 변환
--to_char(sysdate, 'yyyy-mm-dd') --> 날짜를 포맷형식으로 출력하라...
--sysdate --> 현재 시간/날짜
--current_date --> sysdate와 같다...
--like에서 _는 글자 하나, %는 모든것..
--substr('abcde',3,2) --> 문자열중 3번째부터 2글자 출력 (한글도 1을 1글자로 인식)
--substr('abcde',3) --> 문자열 중에서 3번째부터 끝까지...
--concat('ab','cd') --> 두 문자열을 결합함...
--length('aa') 또는 langthb('aa') --> 문자열의 길이 또는 바이트 수 구하기
--instr('abcdabcd','c',1,2) --> 문자열에서 c가 첫문자부터 세어서 두번째 나오는 위치값 출력
--lpad('korea', 10, '*') --> 전체 10자리로 맞추고 빈칸은 왼쪽으로 *로 채운다.
--rpad('korea', 10, '-') --> 전체 10자리로 맞추고 빈칸은 오른쪽으로 -로 채운다.
--ltrim('aaaabaaba','a') --> 왼쪽에서 반복되는 문자열을 지워라... (최초로 반복되는 문자열까지만...) 지울 문자열을 생략하면 공백을 지운다.
--rtrim('aaaabaaba','a') --> 오른쪽에서 반복되는 문자열을 지워라... (최초로 반복되는 문자열까지만...) 지울 문자열을 생략하면 공백을 지운다.
--translate('hallp','ap','eo') --> a를 e로, p를 o로 변환하여 hello를 출력
--replace('orahome','ora','오라') --> ora를 오라로 변환 대치하여 출력...
--reverse('oracle') --> 거꾸로 출력함....
 
***숫자처리함수***
--floor(2.9) --> 실수에서 작은 정수값 취함
--ceil(2.9) ---> 실수에서 큰 정수값 취함
--mod(5, 2) ---> 5/2에서 나머지값을 취함 
--round(321.123, 2) --> 소수 3자리에서 반올림하여 2자리로 표시함
--truncate(12.34, 1) --> 소수 첫째자리만 나타냄(버림)
--power(2,3) --> 2의 3승 값
--sqrt(3) --> 루트3의 값

***날짜처리함수***
--months_between(sysdate, sysdate) --> 날짜 차이를 월단위로 구함...
--add_months(sysdate, 4) --> 4개월을 더한 날짜를 출력...
--next_day(sysdate,'월요일') --> 현재날짜로부터 가장 빠른 월요일 날짜를 구하기...
--last_day(sysdate) --> 현재달의 마지막 날을 구함...
--to_char(sysdate,'yyyy') --> 연도 네자리만 출력함 mm, dd, hh, mi, ss 등 가능함...
--to_date('2005-12-12', 'yyyy-mm-dd') 문자열을 날짜및 시간형식으로 변환
--extract(year from sysdate) --> sysdate에서 년도/월/일 만 추출함...
--extract(month from sysdate)
--extract(day from sysdate)
***to_char 변환옵션들...***
 q : 분기표시 1 2 3 4
 ww: 올해 1월1일부터 계산해서 몇주째인지..
 w : 현재 달에서 몇주째인지..
 d : 현재 몇요일인지 일(1), 월(2), ... , 토(7)
 dd: 현재 달에서 몇일인지...
ddd: 올해 1월1일부터 현재 몇일째인지..
day: 몇요일인지...
 dy: 몇요일인지.. 약자로..
mon:
month:
year:
 

***변환형함수***
----------------------------------------|
--     to_date <------ to_char          |
--날짜 <-------> 문자 <--------> 숫자 |
--     to_char -------> to_number       |
----------------------------------------|
--to_char(hiredate, 'fmyyyy*mmfm*dd') --> fm~fm: 숫자앞에0을 빼버림
--to_char(hiredate, 'year-mmsp-ddspth') --> 모두 영문으로 표기함..
--to_char(sal, 'L9,999') -->통화기호 및 컴마 표시
--to_dsinterval('007 14:30:10') --> 일 시:분:초 (sysdate와의 계산식에 사용)
--to_yminterval('01-03') --> 년-월 ( " )
 
***rownum***
결과에 로우컬럼을 추가하여 하나씩 증가시킴...

***case***
select case 3 when 5-2 then 'a'
        when 2-2 then 'b'
        else '몰라'
  end "case 연습"
from dual;

***case 부등호 조건 쓰기***
case when sal > 5000 then .....

***decode***
select decode(2, 5-2, '5-2',
        2-2, '2-2',
       '몰라')
  from dual;
***inline view*****
select T.*      -- select 문 자체를 뷰화 하여 쿼리함..
from (select * from emp) T;

*** ***
select deptno, job, sum(sal)
from emp
group by rollup(deptno, job)


--rank() over(order by sal desc) --> sal컬럼에 대한 석차구하기 (공동등수일때 다음 등수 건너뛰기)
--rank() over(partition by deptno order by sal desc) --> deptno에 따라서 sal컬럼에 대한 석차 구하기
--dense_rank() over(order by sal desc) --> 공동석차일 경우 다음 순위에 공동순위만큼 등수 건너뛰지 않음

***조인***
--> 조인시 from절에서 로우값이 많은 테이블을 먼저 쓸것.
--> from에서 가장 끝에 기술 된 테이블이 구동테이블로 잡히고 무조건 테이블 full-scan되어진다.
    (로우값이 작은 테이블을 구동시키고 로우가 큰 테이블을 참조시킴)
--> 한쪽만 인덱스가 있다면 from절에서 위치 상관 없이 인덱스 없는쪽이 자동으로 구동테이블이 된다.
(기본 블럭사이즈 = 8KB)
block -> extent -> segment(table/index) -> tablespace -> database -> dataware-house

--equi join
inner join과 같음.... 일반적인 쪼인....
 
--non equi join
select E.ename, E.sal, G.grade, G.losal, G.hisal
from emp E, salgrade G
where E.sal between G.losal and G.hisal;

--left outer join
select D.department_id, E.first_name, E.last_name, E.salary
from hr.employees E, hr.departments D
where E.department_id=D.department_id(+);

--right outer join
select D.department_id, E.first_name, E.last_name, E.salary
from hr.employees E right join hr.departments D
on E.department_id = D.department_id;

--full outer join
select D.department_id, E.first_name, E.last_name, E.salary
from hr.employees E full join hr.departments D
on E.department_id = D.department_id;

--self join
select A.empno, A.ename, A.job, A.mgr, B.ename, B.job
from emp A, emp B
where A.mgr = B.empno(+);


***union / intersect / minus***
--union : 두 쿼리문을 같은 컬럼으로 로우붙이기 하여 이어서 보여줌...
select * from jumunold
union
select * from jumun;
--차이점           ( union / union all )
--첫번째컬럼으로 자동 asc sort ( O / X )
--결합시 중복되는 로우는 제거  ( O / X )

--intersect : 교집합
--minus     : 차집합

*** rank (row number) ***


*** 쿼리출력을 새로운 테이블로...***
--oracle
create table jumunold
as select * from jumun

--ms sql
select * into jumunold
from jumun
 
*** exists & 상관서브쿼리 ***
in()을 사용하는것 보다 처리 속도가 빠름...

------------------------------------
------------------------------------
------------------------------------
select * from user_users --계정정보(디폴트테이블스페이스)
select * from user_tablespaces --테이블스페이스정보
select * from USER_SYS_PRIVS  --시스템 권한 부여 확인
select * from USER_TABLES  --해당 계정 테이블에 대한 정보 확인
select * from USER_ROLE_PRiVS --자기 롤 확인
select * from USER_TS_QUOTAS  --자기 테이블스페이스 쿼타 확인
select * from user_free_space --사용 할 수 있는 빈 공간 테이블 스페이스 크기
select * FROM user_SEGMENTS --자기 세그먼트들을 확인(테이블/인덱스)
SELECT * FROM USER_CONS_COLUMNS --제약조건들 목록을 확인
SELECT * FROM USER_INDEXES -- 자기 인덱스 확인하기....
SELECT * FROM USER_IND_COLUMNS -- 자기 인덱스 확인하기....
SELECT * from user_tab_columns --테이블 컬럼 구조 확인 ..
SELECT * FROM USER_CONSTRAINTS --제약조건 검색 
select * from user_tab_comments --현재 계정의 모든 테이블에 주석문을 보여준다...
select * from user_col_comments --컬럼 주석문 검색하기...
select * from role_sys_privs --현재 사용자에게 적용된 시스템 role 검색
select * from dba_roles --시스템 기본 role 검색하기....
select * from dba_data_files --sys계정이 database files 정보 확인하기
select * from dict_columns where table_name = 'DBA_DATA_FILES' --dba_data_files 테이블의 정보 검색
select * from user_catalog --자신의 소유의 존재하는 테이블 및 뷰 검색하기....
SELECT * FROM SYSTEM_PRIVILEGE_MAP --SYSTEM PRIVILEGE 종류 검색....
SELECT * FROM TABLE_PRIVILEGE_MAP --TABLE PRIVILEGE 종류 검색...
--오브젝트 검색할 수 있는 테이블 ---> user_tables < user_catalog = tab = cat < user_objects

--현재 사용중인 테이블스페이스 파일 공간 확인하기....
select a.tablespace_name,
    a.file_name,
    a.bytes,
    b."free byte",
    (a.bytes - b."free byte") "사용중인 공간"
from dba_data_files a, (select tablespace_name, sum(bytes) "free byte"
          from dba_free_space
      group by tablespace_name
      ) b
where a.tablespace_name = b.tablespace_name
---------------------
--제약조건 추가.....
---------------------
--제약조건 검색하기...
SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='BUSEO'

--디폴트옵션도 조회하여 모두 복사함....
SELECT COLUMN_NAME, DATA_DEFAULT
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'BUSEO'

-- primary key 부여하기....
ALTER TABLE BUSEO
ADD CONSTRAINT BUSEO_BUNO_PK PRIMARY KEY(BUNO)

--참조키 해제하기...
ALTER TABLE SAWON  --테이블을 드롭하기 위하여 참조키를 해제함
DROP CONSTRAINT SAWON_DEPTNO_FK
 
-- NOT NULL 해제하기....
ALTER TABLE BUSEO
DROP CONSTRAINT SYS_C003051 --> constraint 이름
--또는
ALTER TABLE BUSEO
MODIFY ZIPCODE VARCHAR2(7) NULL
 

-- DEFAULT 부여하기....
ALTER TABLE BUSEO
MODIFY ZIPCODE DEFAULT ' '

-- NOT NULL 부여하기
ALTER TABLE BUSEO
MODIFY BUNO CONSTRAINT BUSEO_BUNO_NN NOT NULL


--컬럼 추가하기...
alter table sawon    --8i 이상에서는 이렇게 쉽게 가능함..
add email varchar2(30);
--또는
ALTER TABLE BUSEO  --컬럼 추가하기 ( NOT NULL 옵션 동시에 걸어주기.)....
ADD ZIPCODE VARCHAR2(7) DEFAULT ' ' CONSTRAINT BUSEO_ZIPCODE_NN NOT NULL

--컬럼 이름 변경하기....
alter table sawon
rename column addr to juso

--컬럼 삭제하기....
alter table sawon
drop (email)

--컬럼 수정하기....
alter table  high_tax
modify ( tax number(10,2)  ) ;


--컬럼 UNUSED 하기
ALTER TABLE SAWON SET UNUSED(EMAIL)
--또는
ALTER TABLE SAWON SET UNUSED COLUMN EMAIL

--UNUSED상태에 있는 컬럼명 조회
SELECT *
FROM USER_UNUSED_COL_TABS

--실제로 UNUSED 상태의 컬럼을 삭제하기.....
ALTER TABLE SAWON
DROP UNUSED COLUMNS

--테이블 이름 변경
RENAME BUSEOCP TO BUSEO --BUSEOCP --> BUSEO 이름 변경

--주석문 달기
comment on table sawon is '우리회사 사원들의 테이부르'
comment on column buseo.jitel is '지역전화번호'
select * from user_tab_comments
select * from user_col_comments

--dept 테이블을 다른 스페이스(users)로 옮기기
ALTER TABLE SCOTT.DEPT MOVE TABLESPACE USERS --하나하나 옮기기
-- 일반 사용자가 만든 세그먼트들 검색....
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE 
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'SYSTEM'
   AND OWNER IN ( SELECT USERNAME --USER_ID가 60이상이면 일반사용자임...
             FROM DBA_USERS
      WHERE USER_ID > 60 OR USERNAME = 'SCOTT' )
 
--------------------------------------

--권한부여의 종류와 특정 사용자에게 권한 부여하기
select * from table_privilege_map
grant select on sawon to scott

-----------------------------------------------------------
--사용자 계정 생성하기.....
--계정생성 순서
-> 계정생성 -> 세션(로긴)허용 -> 해당테이블스페이스 쿼터 할당 
-----------------------------------------------------------
CREATE USER ORAUSER2 IDENTIFIED BY TPDLF
DEFAULT TABLESPACE SALESTBS
TEMPORARY TABLESPACE TEMP
QUOTA 5M ON SALESTBS  --SALESTBS 테이블스페이스는 5MB 할당
QUOTA UNLIMITED ON TEMP  --임시 테이블스페이스는 모두 할당

GRANT CREATE SESSION TO ORAUSER2  --로긴 권한 부여
GRANT CREATE TABLE TO ORAUSER2  --테이블 생성 권한 부여


----------------------------------------------------------------------
--사용자 계정 삭제하기...(해당 계정소유의 오브젝트가 있다면 바로 삭제가 불가능함..)
----------------------------------------------------------------------
DROP USER ORAUSER1
DESC DBA_OBJECTS

SELECT *    --삭제할 계정소유의 오브젝트 확인하기...
FROM DBA_OBJECTS
WHERE OWNER='ORAUSER1'

DROP TABLE ORAUSER1.MYTAB --삭제할 계정소유의 테이블 먼저 삭제함...

--또는 삭제할 계정을 삭제할 때 그 소유의 오브젝트를 모두 지우고 삭제함..
DROP USER ORAUSER1 CASCADE


-----------------------------
--뷰생성 --view---
-----------------------------
create or replace view v_emp10 --새로 생성하거나 기존의 뷰를 재정의 할 때 쓰여짐.....
as
select empno, ename, sal, deptno
from emp
where deptno = 10

--create or replace trigger
--create or replace procedure

--truncate 는 DDL문이므로 rollback 이 불가함.... 
truncate table sawon --> 사원테이블의 모든 입력값을 지우고 high water mark 까지도 초기화함...
delete table_name -----> 이 역시 모든 입력값이 지워지지만 H.W.M 는 초기화되지 못한다.


** commit 은 DML문에서만 가능하고 그 이외의 명령문은 auto commit 이다.
** DML이외의 명령문이 샐행 되어지면 commit이 실행되므로 그 이전에 dml문까지도 commit이 실행되어져 rollback 불가함

------------------------------------------------------------------
**MS-SQL에서는 BEGIN TRAN <-----> ROLLBACK TRAN 사이에 넣은 문들은
  DML,DDL,DCL등 관계없이 모두 ROLLBACK 되어짐......
------------------------------------------------------------------

--암호재정의
alter user user_ identified by password_
 
 

-----------------------------
--external table 생성하기....
-----------------------------

--My-Sql 에서 데이타베이스 내용 추출하기...
C:\> bcp pubs.dbo.stores out c:\ext_data\stores.txt -c -t"," -r\n -Usa -P****** -Sclass-b108
 
--오라클에서 external table로 사용 할 디렉토리 정의
create or replace directory stores_dir AS 'c:\ext_data\'

--external table 생성
--------------------------------------------------------------------------------
create table scott.stores_ext     --external 테이블 정의
    ( stor_id varchar2(4)     --external 테이블 정의
    , stor_name varchar2(40)    --external 테이블 정의
    , stor_address varchar2(40)   --external 테이블 정의
    , city varchar2(20)     --external 테이블 정의
    , state varchar2(2)     --external 테이블 정의
    , zip varchar2(5)      --external 테이블 정의
    )    
organization external          --위에서 정의 한 테이블을 external로 정의
( type oracle_loader
 default directory stores_dir    --생성되는 external 테이블 디렉토리
 access parameters
         ( records delimited by newline --개행문자'\n'에 의한 레코드 구분 정의
          badfile 'stores_bad.bad' --데이타형식이 틀려서 입력 안된 로우가 기록되는 파일
          logfile 'stores_log.log' --기본 로그파일 정의
          fields terminated by ',' --컬럼 구분자를 정의
                   ( stor_id char  -- 컬럼 구조 정의
                   , stor_name char  -- 컬럼 구조 정의
                   , stor_address char -- 컬럼 구조 정의
                   , city char   -- 컬럼 구조 정의
                   , state char   -- 컬럼 구조 정의
                   , zip char)   -- 컬럼 구조 정의
               )
 location('stores.txt') --외부에서 추출한 데이타파일
 )
reject limit unlimited --bad파일 로우 제한 설정
---------------------------------------------------------------------------------
desc scott.stores_ext
select * from scott.stores_ext
SELECT * FROM DICT WHERE UPPER(COMMENTS) LIKE '%EXTERNAL%'
USER_EXTERNAL_TABLES
USER_EXTERNAL_LOCATIONS

Posted by 톰켓 Trackback 0 Comment 0

 
문제) 오라클 10g : export 실행할때나 토드로 접속할때 아래의 에러발생.
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized

조치1) sysdba로 로긴하여 아래 구문 실행

SQL> @ $ORACLE_HOME/javavm/install/rmjvm.sql
SQL> @ $ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> @ $ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @ $ORACLE_HOME/rdbms/admin/catproc.sql

==> 문제 해결 안됨.


원인) 아래 쿼리를 날렸을때 데이터 타입이 중복되어서 나옴

쿼리)

select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112);

결과)

CHARACTERSET  TYPES_USED_IN
----------------- -------------
WE8ISO8859P1      VARCHAR2
KO16KSC5601        VARCHAR2
KO16KSC5601        CHAR
WE8ISO8859P1       CHAR
AL16UTF16            NCLOB
KO16KSC5601        CLOB
AL16UTF16            NVARCHAR2
KO16KSC5601        NVARCHAR2
WE8ISO8859P1       CLOB
AL16UTF16            NCHAR


조치2) sysdba로 아래 구문들 실행

주의사항)

a) Make sure the parallel_server parameter in INIT.ORA is set to false or it is not set at all.
b) Run the next script in SQLPLUS connected "as sysdba"


SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> COL VALUE NEW_VALUE CHARSET
         SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
SQL> COL VALUE NEW_VALUE NCHARSET
         SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
SQL> ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
-- yes, 2 times startup/shutdown . This is not a typo
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;


실행중 오류발생)

ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE KO16KSC5601
==> ORA-12714: invalid national character set specified

원인)

NATIONAL CHARACTER SET 은 Oracle9i 이후에는 UTF8과 AL16UTF16 만 제공
확인)  

SELECT VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER in ('NLS_NCHAR_CHARACTERSET','NLS_CHARACTERSET')
결과)

PARAMETER                                  VALUE
---------------------------------- ---------------
NLS_CHARACTERSET                     KO16KSC5601

NLS_NCHAR_CHARACTERSET         KO16KSC5601

조치)

update sys.props$ set value$='AL16UTF16' where name='NLS_NCHAR_CHARACTERSET';


이후 위의 조치2)를 다시실행하면 아래와 같은 결과를 얻을수 있다..


문제해결)

CHARACTERSET   TYPES_USED_IN
----------------- -------------
KO16KSC5601        VARCHAR2
KO16KSC5601        CHAR
KO16KSC5601        CLOB
AL16UTF16            NCLOB
AL16UTF16            NVARCHAR2
AL16UTF16            NCHAR



Export 할때 아래문장을 자세히 봤으면 조치하는데 시간이 들 걸릴듯 했건만..

조치전) Export done in KO16KSC5601 character set and KO16KSC5601 NCHAR character set

조치후) Export done in KO16KSC5601 character set and AL16UTF16 NCHAR character set

Posted by 톰켓 Trackback 0 Comment 0
SQL*Plus에서 꼭 알아두어야 할 활용 매뉴얼~!

● 제 1 장 데이터의 검색
• SQL 명령어는 다음과 같이 기술한다.
■ SQL 명령어는 한 줄 혹은 여러 줄에 기술한다.
■ 일반적으로 절들은 수정하기 쉽게 다른 줄에 기술한다.
■ TAB 을 사용할 수 있다.
■ SQL 명령어 단어는 생략하거나 분리할 수 없다.
■ SQL 명령어는 대소문자를 구분하지 않는다.
■ SQL 명령어는 ; 으로 종료한다.
■ SQL 명령어는 SQL BUFFER 에 저장된다.
■ SQL BUFFER 에 저장된 SQL 명령어는 / 혹은 RUN 으로 실행할 수 있다.
•SQL*PLUS 명령어는 다음과 같이 기술한다.
■ SQL*PLUS 명령어는 기본적으로 한 줄에 기술한다.
■ SQL*PLUS 명령어는 대소문자를 구별하지 않는다.
■ SQL*PLUS 명령어는 SQL BUFFER 에 저장되지 않는다.
■ SQL*PLUS 명령어는 다음과 같다.
? DESCRIBE table명 : TABLE 의 구조를 보여준다.
? SAVE file명 : SQL BUFFER 를 file 로 저장한다.
? START file명 : file 을 수행한다.
? @ file명 : file 을 수행한다.
? EDIT file명 : EDITOR 를 사용하여 file 을 편집한다.
? SPOOL file명 : QUERY 결과를 file 에 저장한다.
? SPOOL OFF : SPOOL FILE 을 닫는다.
? HOST : SQL*PLUS 를 떠나지 않고 HOST 상태로 간다.
? HELP 명령어 : SQL, SQL*PLUS, PL/SQL 에 대한 HELP 를 보내준다.
? EXIT : SQL*PLUS 를 종료한다.

• 전체 데이타의 검색
가장 간단한 SELECT 문장의 형식은 다음과 같다.
. SELECT 절에는 검색하고 싶은 COLUMN 명들을 기술한다.
. FROM 절에는 SELECT 절에서 기술된 COLUMN 명들이 포함된 TABLE 명을 기술한다.
TABLE 의 모든 ROW 와 모든 COLUMN 을 검색한다.
SELECT * - FROM table명 ;
[ 예제 ] S_DEPT TABLE 로부터 모든 ROW 와 COLUMN 을 검색하시오.
SELECT *
FROM S_DEPT ;

• 특정 column의 검색
SELECT 절에서 검색하고자 하는 COLUMN 명을 COMMA 를 사용하여 나열한다.
COLUMN 순서는 검색하고 싶은 순서대로 나열한다.
COLUMN HEADING 은 COLUMN 명이 대문자로 출력된다.
SELECT column명, column명, column명,.. - FROM table명 ;
[ 예제 ] S_EMP TABLE 로부터 ID, LAST_NAME, START_DATE 를 검색하시오.
SELECT ID, LAST_NAME, START_DATE
FROM S_EMP ;

- 계속(1) -


• 산술식을 사용한 검색
산술 연산자를 사용하여 검색되는 데이타 값을 변경할 수 있으며 산술 연산식은 COLUMN 명, 상수 값, 산술 연산자로 구성됨.
SELECT 산술연산식 - FROM table명 ;
[ 예제 ] S_EMP TABLE 로부터 ID, LAST_NAME, 연봉을 검색하시오. (연봉은 SALARY * 12 로 계산한다. (+,-,*,/,())
SELECT ID, LAST_NAME, SALARY * 12
FROM S_EMP ;

• Column alias
기본적으로 COLUMN HEADING 은 COLUMN 명이 대문자로 출력된다. 그러나 많은 경우 COLUMN 명이 이해하기 어렵거나
무의미하기 때문에 COLUMN ALIAS 를 사용하여 COLUMN HEADING 을 변경할 수 있다.
ANSI SQL 92 와 호환을 위해 ALIAS 앞에 AS 를 붙일 수 있다.
ALIAS 에 공백이나 특수문자가 포함되거나 대소문자를 구별하고 싶으면 " " 로 막아준다.
COLUMN ALIAS 를 사용하여 COLUMN HEADING 을 변경할 수 있다.
SELECT column명 alias, column명 "alias", column명 as alias - FROM table명 ;
[ 예제 ] S_EMP TABLE 에서 LAST_NAME, (SALARY + 100) * 12, DEPT_ID 를 검색하시오.
(단, COLUMN ALIAS 는 Employee, ANNUAL_SALARY, DEPARTMENT NO 로 정의하시오.)
SELECT LAST_NAME "Employee", (SALARY + 100) * 12 AS ANNUAL_SALARY,
DEPT_ID "DEPARTMENT NO"
FROM S_EMP ;

• Column의 결합
COLUMN 과 다른 COLUMN, 산술연산식, 상수 값과 결합하여 하나의 COLUMN 으로 결합할 수 있다.
SELECT column명|| column명 - FROM table명;
[ 예제 ] S_EMP TABLE 에서 FIRST_NAME 과 LAST_NAME 을 결합하여 ALIAS EMPLOYEE 로 검색하시오.
SELECT FIRST_NAME || LAST_NAME EMPLOYEE
FROM S_EMP ;

• Null값 처리
특정 COLUMN 에 값이 입력되어 있지 않을 때, 그 값이 NULL 이며, NULL 값이 산술 연산식에 포함되면 그 결과도 NULL 이다.
NULL 값은 0 이나 공백과 같지 않다. 그러므로 NVL FUNCTION 을 사용하여 NULL 값을 다른 값으로 대체하여야 한다.
NULL 값을 다른 값으로 대체.NVL (number_column, 9), NVL (date_column, '01-JAN-95'), NVL (character_column, 'ABCDE')
[ 예제 ] S_EMP TABLE 에서 LAST_NAME, COMMISSION 값을 검색하시오.
(COMMISSION 은 SALARY * COMMISSION_PCT /100 으로 계산하시오.)
SELECT LAST_NAME, SALARY * NVL(COMMISSION_PCT,0) /100 COMMISSION
FROM S_EMP ;

• 중복 row의 제거
SELECT 결과 값에 중복된 값이 있을 때 중복을 피하고 UNIQUE 하게 검색한다. 중복된 ROW 를 제거한다.
SELECT DISTINCT column명, column명 - FROM table명;
[ 예제 ] S_DEPT TABLE 에서 NAME 이 중복되지 않게 검색하시오.
SELECT DISTINCT NAME
FROM S_DEPT ;



- 계속(2) -


• 데이타의 정렬
SELECT 되는 ROW 의 순서는 알 수 없다. 그러므로 ROW 를 SORT 하고 싶으면 ORDER BY 절을 사용하여야 한다.
DATA 의 DEFAULT SORT 순서는 ASCENDING 이며 다음과 같다.
? 숫자 : 1 에서 999 순으로 SORT 한다. ? 날짜 : 01-JAN-92 에서 01-JAN-95 순으로 SORT 한다.
? 문자 : A 에서 Z 순서로 SORT 한다. ? NULL : ASC 순에서는 뒤에, DESC 순에서는 앞에 나온다.
역순으로 SORT 하고 싶으면 COLUMN 명 뒤에 DESC 를 붙인다.
COLUMN 명 대신에 ALIAS 혹은 SELECT 한 COLUMN 의 순서로 지정할 수도 있다.
SELECT expr - FROM table명 - ORDER BY {column명, expr} [ASC|DESC] ;
[ 예제 ] S_EMP TABLE 에서 LAST_NAME, DEPT_ID, START_DATE 를 LAST_NAME 순으로 검색하시오.
SELECT LAST_NAME, DEPT_ID, START_DATE
FROM S_EMP ORDER BY LAST_NAME ;

• 특정 row의 검색
WHERE 절에서 조건식을 기술하여 조건을 만족하는 ROW 만 검색할 수 있다.
조건식은 COLUMN 명, COMPARISON OPERATOR, VALUE 로 구성되어 있다.
문자 값은 ' ' 으로 묶어주고 값의 대소문자를 구별하여 적어준다. 날짜 값은 ' ' 으로 묶어주고 지정된 날짜 형태로 적어준다. '01-MAR-97', 숫자값은 값만 적어주며 특정 ROW 만 검색한다.
SELECT expr - FROM table명 - WHERE expr operator value
[ 예제 ] S_EMP TABLE 에서 LAST_NAME 이 Magee 인 사원의 FIRST_NAME, LAST_NAME,
TITLE 을 검색하시오. (=,>,<,>=,<=,<>) SELECT FIRST_NAME, LAST_NAME, TITLE
FROM S_EMP WHERE LAST_NAME = 'Magee' ;

• Between...and
BETWEEN OPERATOR 를 사용하여 범위를 지정할 수 있다. 범위를 지정할 때는 작은 값을 먼저 큰 값을 나중에 지정한다.
두 범위의 한계 값을 포함한다. BETWEEN...AND... - NOT BETWEEN...AND...
[ 예제 ] S_EMP TABLE 에서 START_DATE 가 09-MAY-91 에서 17-JUN-91 사이에 입사한 사원의 FIRST_NAME, LAST_NAME, START_DATE 를 검색하시오.
SELECT FIRST_NAME, LAST_NAME, START_DATE
FROM S_EMP WHERE START_DATE BETWEEN '09-MAY-91' AND '17-JUN-91' ;

• In[list]
IN OPERATOR 를 사용하여 나열된 값들 중에서 값을 검사한다.
IN(LIST), NOT IN(LIST)
[ 예제 ] S_EMP TABLE에서 DEPT_ID 가 10, 31, 41, 50 인 사원의 FIRST_NAME, LAST_NAME, DEPT_ID 를 검색하시오.
SELECT FIRST_NAME, LAST_NAME, DEPT_ID
FROM S_EMP WHERE DEPT_ID IN (10, 31, 41, 50) ;

• like
찾고자 하는 값을 정확히 모를 때, LIKE OPERATOR 를 사용하여 문자형태가 같은 ROW 를 검색한다.
WILDCARD 를 사용하여 문자의 형태를 지정한다. (% : 여러 문자, _ : 한문자 )
LIKE '형태', NOT LIKE '형태'
[ 예제 ] S_EMP TABLE에서 LAST_NAME 이 M 으로 시작하는 사원의 LAST_NAME 을 검색하시오.
SELECT LAST_NAME FROM S_EMP WHERE LAST_NAME LIKE 'M%' ;
SELECT LAST_NAME FROM S_EMP WHERE LAST_NAME LIKE '__M____' ;

- 계속(3) -

• is null
IS NULL OPERATOR을 사용하여 값이 NULL 인 것을 찾을 수 있다.
NULL 값은 값이 정의되지 않은 것을 의미하기 때문에 = OPERATOR를 사용하여 어떤 값과 비교할 수 없기 때문에 사용한다.
IS NULL, IS NOT NULL
[ 예제 ] S_EMP TABLE에서 COMMISSION_PCT가 NULL인 사원의 LAST_NAME, SALARY, COMMISSION_PCT를 검색하시오.
SELECT last_name, salary,commission_pct,last_name, salary
FROM s_emp WHERE commission_pct is null;

• 조건식의 결합
조건식을 기술할 때 AND 와 OR 를 사용하여 여러가지 조건을 결합할 수 있다.
AND 와 OR 가 같이 사용됐을 때 AND 가 먼저 수행되고 OR 가 나중에 수행된다.
그러므로 우선순위를 바꾸고자 하면 ( ) 를 사용한다.
WHERE 조건식 AND | OR 조건식
[ 예제 ] S_EMP TABLE에서 DEPT_ID가 41이고 TITLE이 Stock Clerk인 사원의 LAST_NAME, SALARY, DEPT_ID, TITLE을 검색하시오.
SELECT LAST_NAME, SALARY, DEPT_ID, TITLE
FROM S_EMP WHERE DEPT_ID = 41 AND TITLE = 'Stock Clerk' ;

● 제 2 장 Single Row Functions
• 소문자로 변환
모든 문자를 소문자로 변환시킨다.
LOWER(COLUMN명)
[ 예제 ] S_EMP TABLE 에서 LAST_NAME 이 Smith 인 사원의 FIRST_NAME, LAST_NAME 을 소문자로 출력시키시오.
SELECT LOWER(FIRST_NAME), LOWER(LAST_NAME)
FROM S_EMP WHERE LOWER(LAST_NAME) = 'smith' ;

• 대문자로 변환
모든 문자를 대문자로 변환시킨다.
UPPER(COLUMN명)
[ 예제 ] S_EMP TABLE 에서 LAST_NAME 이 Smith 인 사원의 TITLE 을 대문자로 출력하시오.
SELECT UPPER(TITLE)
FROM S_EMP WHERE UPPER(LAST_NAME) = 'SMITH';

• 첫글자만 대문자로 변환
단어의 첫글자는 대문자로, 나머지는 소문자로 변환시킨다.
INITCAP(COLUMN명)
[ 예제 ] S_EMP TABLE 에서 TITLE 을 단어의 첫글자만 대문자로 출력시키시오.
SELECT INITCAP(TITLE) FROM S_EMP ;

• 문자의 부분을 자름
문자를 시작위치(M)에서 자리수(N) 만큼 잘라준다. 자리수(N)이 생략되면 시작위치(M)에서 끝까지 잘라준다.
SUBSTR(COLUMN명, M, N)
[ 예제 ] S_PRODUCT TABLE 에서 NAME COLUMN 의 앞에서 부터 3글자가 Ace 인 제품의 NAME 을 출력하시오.
SELECT NAME FROM S_PRODUCT
WHERE SUBSTR(NAME, 1, 3) = 'Ace' ;

- 계속(4) -


• 문자의 길이를 계산
문자의 길이를 RETURN 한다.
LENGTH(COLUMN명)
[ 예제 ] S_PRODUCT TABLE 에서 NAME, NAME 의 길이를 출력하시오.
SELECT NAME, LENGTH(NAME)
FROM S_PRODUCT;

• 숫자의 반올림
지정된 자리수(M) 밑에서 반올림한다.
COLUMN 값이 1 2 3 4. 5 6 7 일 때 자리수(M)는 다음과 같다.
      M : -3-2-1 0 1 2 3
ROUND(COLUMN명, M)
[ 예제 ] S_EMP TABLE 에서 LAST_NAME, SALARY/22 의 값을 소수 2째 자리까지 나타내고 소수 3째 자리에서 반올림하시오.
SELECT LAST_NAME, ROUND(SALARY/22, 2)
FROM S_EMP ;

• 숫자의 절사
지정된 자리수(M) 까지 나타내고 그 밑은 잘라버린다.
COLUMN 값이 1 2 3 4. 5 6 7 일 때 자리수(M)은 다음과 같다.
      M : -3-2-1 0 1 2 3 절사 값은 RETURN 한다.
TRUNC(COLUMN명, M)
[ 예제 ] S_EMP TABLE 에서 LAST_NAME, SALARY / 22 의 값을 십의 자리까지 나타내고 일의 자리는 버림.
SELECT LAST_NAME, TRUNC(SALARY/22, -1)
FROM S_EMP ;

• 나누기의 나머지
M 을 N 으로 나누고 남은 나머지를 RETURN 한다.
MOD(M, N)
[ 예제 ] 10 을 3 으로 나눈 나머지를 구하시오.
SELECT MOD(10, 3)
FROM SYS.DUAL ;

• 날짜의 연산
DATABASE 안의 DATE 값은 다음과 같은 숫자로 저장되어 있다.
■ CENTURY, YEAR, MONTH, DAY, HOURS, MINUTES, SECONDS 그러므로 산술 연산을 할 수 있다.
● DATE + NUMBER : 숫자만큼 일이 더해진 날짜가 RETURN 된다.
● DATE - NUMBER : 숫자만큼 일이 빼진 날짜가 RETURN 된다.
● DATE1 - DATE2 : 두 날짜 사이의 일수가 계산된다.
날짜 계산을 한다. (DATE + NUMBER, DATE - NUMBER, DATE1 - DATE2)
[ 예제 ] S_EMP TABLE 에서 LAST_NAME, 입사한지 90 일째 되는 날, 입사한지 며칠 됐는 지 검색하시오.
SELECT LAST_NAME, START_DATE + 90, SYSDATE - START_DATE
FROM S_EMP; ( 날짜에는 시간도 포함되어 있으므로 일수 계산의 결과가 소수로 나온다. )



- 계속(5) -


• 날짜 사이의 개월 수
두 날짜 사이의 개월 수를 RETURN 한다.
MONTHS_BETWEEN(DATE1, DATE2)
[ 예제 ] S_EMP TABLE 에서 LAST_NAME, 입사한지 몇 달이 됐는지 출력하시오.
SELECT LAST_NAME, MONTHS_BETWEEN(SYSDATE, START_DATE)
FROM S_EMP ; (일이 포함되어 있어서 소수로 출력된다.)

• 날짜에 달을 더함
날짜에서 숫자(N) 개월만큼 더해진 날짜를 RETURN 한다.
ADD_MONTHS(DATE, N)
[ 예제 ] S_EMP TABLE 에서 LAST_NAME, START_DATE, 입사한지 3 개월되는 날짜를 출력하시오.
SELECT LAST_NAME, START_DATE, ADD_MONTHS(START_DATE, 3)
FROM S_EMP ;

• 지정한 요일 날짜
날짜에서 지정한 요일(CHAR)이 될 날짜를 RETURN 한다.
NEXT_DAY(DATE, 'CHAR')
[ 예제 ] 오늘을 기준으로 돌아오는 금요일이 언제인지 출력하시오.
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'FRIDAY')
FROM SYS.DUAL ;

• 그 달의 마지막 날
날짜가 포함된 달의 마지막 날을 RETURN 한다.
LAST_DAY(DATE)
[ 예제 ] 이번 달의 마지막 날은 언제인지 출력하시오.
SELECT SYSDATE, LAST_DAY(SYSDATE)
FROM SYS.DUAL ;

• 날짜의 반올림
형태에 따른 반올림 기준은 다음과 같다.
? YEAR : 6월 이후, ? MONTH : 15일 이후, ? DAY : 12시 이후
날짜 데이타를 지정된 형태까지 나타내고 그 이하에서 반올림한다.
ROUND(COLUMN명, '형태')
[ 예제 ] S_EMP TABLE 에서 ID, LAST_NAME, 입사 시작 달을 검색하시오. (단, 15일 이후는 다음달로 올리시오.)
SELECT ID, LAST_NAME, ROUND(START_DATE, 'MONTH')
FROM S_EMP ;

• 날짜의 절사
날짜 데이타를 지정된 형태까지 나타내고 그 밑은 잘라낸다.
TRUNC(COLUMN명, '형태')
[ 예제 ] S_EMP TABLE 에서 ID, LAST_NAME, 입사 시작 달을 검색하시오. (단, 일자는 잘라버리시오.)
SELECT ID, LAST_NAME, TRUNC(START_DATE, 'MONTH')
FROM S_EMP ;


- 계속(6) -


• 문자를 날짜로 변환
CHARACTER TYPE 을 지정된 형태의 DATE TYPE 으로 변환한다.
TO_DATE(character_column명, '형태')
[ 예제 ] S_EMP TABLE 에서 LAST_NAME, START_DATE 를 검색하시오.
(단, START_DATE 의 값이 92/02/07 인 사원을 검색하시오.)
SELECT LAST_NAME, START_DATE
FROM S_EMP WHERE START_DATE = TO_DATE('92/02/07', 'YY/MM/DD') ;

• 날짜를 문자로 변환
DATE 값은 기본적으로 DD-MON-YY 형태로 출력된다.
이것을 TO_CHAR FUNCTION 을 사용하면 원하는 다른 형태로 변환할 수 있다.
■ 형태를 지정할 때 사용된 대소문자로 출력된다.
■ DAY 와 MONTH 형태는 공백을 포함한 9 자리로 출력된다.
■ TO_CHAR 의 결과는 80 자리로 출력된다.
DATE TYPE 을 지정된 형태의 CHARACTER TYPE 으로 변환한다.
TO_CHAR(date_column, '형태')
[ 예제 ] S_EMP TABLE 에서 LAST_NAME, START_DATE 를 검색하시오.
(단, START_DATE 의 형태는 1991/06/17 14:20:00 와 같이 출력하시오.)
SELECT LAST_NAME, TO_CHAR(START_DATE, 'YYYY/MM/DD HH24:MI:SS'), START_DATE
FROM S_EMP ;

• 숫자를 문자로 변환
NUMBER TYPE 을 지정된 형태의 CHARACTER TYPE 으로 변환한다.
TO_CHAR(number_column명, '형태')
[ 예제 ] S_EMP TABLE 에서 LAST_NAME, SALARY 를 검색하시오.(단 SALARY 를 $1,450 와 같은 형태로 출력시키시오.)
SELECT LAST_NAME, TO_CHAR(SALARY, '$999,999')
FROM S_EMP ;

● 제 3 장. 여러Table로부터 Data검색
• Equijoin
SIMPLE JOIN (EQUI-JOIN)
여러개의 TABLE 들로부터 정보를 검색하려면, SELECT 문장의 FROM 절에 TABLE명들을 적고
WHERE 절에 각 TABLE의 ROW들을 연결시킬 조건식을 기술한다.
각 TABLE 의 COLUMN명이 중복될 때는 반드시 COLUMN명 앞에 TABLE명을 붙여야 한다.
(중복되지 않을 때는 붙이지 않아도 되지만 명확성을 위해서나 ACCESS 를 위해서 붙이는 것이 좋다.)
N 개의 TABLE 을 JOIN 할 때는 최소한 N-1 개의 조건식이 필요하다.
복합 COLUMN 으로 JOIN 할 때는 더 많은 조건식이 필요하다.
2개 이상의 TABLE 에서 = 조건식을 만족시키는 ROW 들을 연결하여 검색한다.
SELECT table명.column명, table명.column명...-FROM table1명, table2명-WHERE table1명.column1명 = table2명.column명 ;
[ 예제 ] S_EMP TABLE 과 S_DEPT TABLE 을 사용하여 사원들의 LAST_NAME, DEPT_ID, NAME 을 검색하시오.
SELECT S_EMP.LAST_NAME, S_EMP.DEPT_ID, S_DEPT.NAME
FROM S_EMP, S_DEPT WHERE S_EMP.DEPT_ID = S_DEPT.ID ;



- 계속(7) -


• 특정 row의 join
JOIN 문장을 기술할 때 JOIN 조건식 이외에 다른 조건식을 AND 로 연결할 수 있다.
SELECT table명.column명, table명.column명...
FROM table1명, table2명 - WHERE table1명.column1명 = table2명.column2명 AND condition ;
[ 예제 ] S_EMP TABLE과 S_DEPT TABLE 을 사용하여 LAST_NAME 이 Menchu 인 사원의 LAST_NAME, DEPT_ID,
NAME 을 검색하시오.
SELECT S_EMP.LAST_NAME, S_EMP.DEPT_ID, S_DEPT.NAME
FROM S_EMP, S_DEPT WHERE S_EMP.DEPT_ID = S_DEPT.ID AND S_EMP.LAST_NAME = 'Smith' ;

• Table alias
JOIN 문장에서 TABLE명이 긴 경우 TABLE명.COLUMN명 으로 적는 것이 매우 불편하다.
그런데 TABLE명 대신 ALIAS 를 사용하면 편하게 사용할 수 있다.
(SELECT 문장에서 TABLE명 대신 ALIAS 를 지정했다면 그 문장에서는 계속해서 ALIAS 로 사용하여야 한다.)
TABLE ALIAS를 사용하여 JOIN 문장을 간단하게 기술한다.
SELECT alias명.column명, alias명.column명
FROM table1명 alias1명, table2명 alias2명 - WHERE alias1명.column1명 = alias2명.column2명 ;
[ 예제 ] S_CUSTOMER TABLE과 S_REGION TABLE 을 사용하여 고객 명,지역번호,지역 명을 검색하시오.
(단, COLUMN ALIAS 와 TABLE ALIAS 를 사용하시오.)
SELECT C.NAME "Customer Name", C.REGION_ID "Region Id", R.NAME "Region Name"
FROM S_CUSTOMER C, S_REGION R WHERE C.REGION_ID = R.ID ;

• Non-Equijoin
NON-EQUIJOIN
JOIN 문장에서 두 TABLE 을 JOIN 하는 조건식에 = OPERATOR 가 사용되지 않고
다른 OPERATOR 가 사용되는 것을 말한다.
SELECT table명.column명, table명.column명... - FROM table1명, table2명 - WHERE 조인조건식 ;
[ 예제 ] EMP TABLE 과 SALGRADE TABLE 을 사용하여 사원의 ENAME, JOB, SAL,GRADE를 검색하시오.
SELECT E.ENAME, E.JOB, E.SAL, S.GRADE FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL ;
(BETWEEN OPERATOR 대신에 <= 와 >= 를 사용해도 되지만 BETWEEN 이 간편하다.)

• Outer Join
두 TABLE 을 JOIN 할 때 JOIN 조건식을 만족시키지 못하는 ROW 는 검색에서 빠지게 된다.
그런데 이러한 ROW 들이 검색되도록 하는 것이 OUTER JOIN 이다.
(+) OUTER JOIN OPERATOR 를 데이타가 없는 어느 한쪽의 COLUMN 쪽에 붙인다.
JOIN 결과, 데이타가 없는 쪽의 COLUMN 값은 NULL로 검색된다. 조건식을 만족시키지 못하는 데이타도 검색한다.
SELECT table명.column명, table명.column명 - FROM table1명, table2명
WHERE table1명.column1명 = table2명.column2명(+)
[ 예제 ] S_EMP TABLE 과 S_CUSTOMER TABLE 을 사용하여 영업사원의 LAST_NAME, SALES_REP_ID, NAME 을 검색하시오.
(단, 영업사원이 정해져 있지 않은 고객의 이름도 검색하시오.)
SELECT E.LAST_NAME, C.SALES_REP_ID, C.NAME
FROM S_EMP E, S_CUSTOMER C
WHERE E.ID(+) = C.SALES_REP_ID ;


- 계속(8) -


• Self Join
TABLE 의 ALIAS 를 사용하여, 마치 2 개의 TABLE 처럼 생각하여 자신의 TABLE 과 자신의 TABLE 을 JOIN 한다.
SELECT alias명.column명, alias명.column명...
FROM table명 alias1명, table명 alias2명 - WHERE alias1명.column1명 = alias2명.column2명 ;
[ 예제 ] S_EMP TABLE 에서 사원들의 LAST_NAME 과 그들의 상사 LAST_NAME 을 검색하시오.
SELECT W.LAST_NAME "Woker", M.LAST_NAME "Manager"
FROM S_EMP W, S_EMP M WHERE W.MANAGER_ID = M.ID ;

● 제 4 장. Group Functions
• Group Function
각각의 FUNCTION 은 ARGUMENT 를 받는데 기능은 다음과 같다.
■ DISTINCT : 중복된 값은 제외한다. ■ ALL : DEFAULT 로써 모든 값을 포함한다.
■ COLUMN명 : NULL 값은 제외한다. ■ * : NULL 값도 포함한다.
TABLE 전체를 하나의 GROUP 으로 보고 GROUP FUNCTION 값을 RETURN 한다.
SELECT group_function(column명), group_function(column명)... - FROM table명 ;
[ 예제 ] S_EMP TABLE 에서 회사 전체의 급여합계, 최고급여, 최소급여, 인원수를 검색하시오.
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), COUNT(SALARY) FROM S_EMP ;
( COUNT(SALARY) 는 급여를 받는 사원의 총 인원수고 COUNT(*) 는 급여를 받지 않는 사원의 인원수도 포함된다.)

• 소group으로 분리
기본적인 SELECT 절(그룹화 되지 않은 SELECT절)에는 COLUMN 명과 GROUP FUNCTION 이 같이 기술될 수 없다.
SELECT 절에 COLUMN 명이 기술되려면 GROUP BY 절이 반드시 기술되어야 한다.
SELECT 절에 기술된 COLUMN 명들은 전부 GROUP BY 절에 기술되어야 하며
GROUP BY 절에 기술된 COLUMN 명들은 SELECT 절에 기술되지 않아도 된다.
(하지만 결과를 파악하기 위해서는 SELECT 절에 기술해주는 것이 좋다.)
GROUP BY 절을 기술하면 GROUP BY 절에 기술된 COLUMN 값으로 1 개의 TABLE이 소 GROUP 으로 나눠진다.
결과는 COLUMN 값으로 SORT 되어서 출력된다. 1개의 TABLE 을 소 GROUP 으로 나누어 GROUP FUNCTION 값을 구한다.
SELECT column1명[, column2명], group_function(column명) - FROM table명 - GROUP BY column1명[, column2명] ;
[ 예제 ] S_EMP TABLE 에서 DEPT_ID, TITLE 별로, 최고급여, 최소급여, 인원수를 검색하시오.
SELECT DEPT_ID, TITLE, MAX(SALARY), MIN(SALARY), COUNT(SALARY)
FROM S_EMP GROUP BY DEPT_ID, TITLE;

• 특정 group의 선택
HAVING 절이 기술됐을 때 처리되는 순서는 다음과 같다.
① ROW 들이 GROUPing 된다. ② GROUP 에 대해 GROUP FUNCTION 이 적용된다.
③ HAVING 절을 만족하는 GROUP 을 선택한다.
GROUP BY 절과 HAVING 절의 순서는 바뀌어도 되지만 의미상 GROUP BY 절 다음에 HAVING 절을 기술하는 것이 좋다.
HAVING 절에서는 GROUP FUNCTION 을 사용하여 GROUP 에 대한 조건식을 기술한다.
SELECT column1명[, column2명], group_function(column명) - FROM table명 - GROUP BY column1명[, column2명]
HAVING 그룹조건식 ;
[ 예제 ] S_EMP TABLE에서 TITLE별로 급여합계를 검색하시오. (단, 급여합계가 5000이상인 GROUP만 출력하시오)
SELECT TITLE, SUM(SALARY) PAYROLL FROM S_EMP
GROUP BY TITLE HAVING SUM(SALARY) >= 5000 ;


- 계속(9) -

• Group의 정렬
기본적으로 GROUP BY 절에 기술된 COLUMN 값으로 SORT 된다.
이 순서를 바꾸고자 하면 ORDER BY 절을 기술하면 된다. DATA 의 SORT 순서를 정한다.
SELECT column1명[, column2명], group_function(column명) - FROM table명
GROUP BY column1명[, column2명] - ORDER BY column명| group_function(column명) ;
[ 예제 ] S_EMP TABLE에서 DEPT_ID 별로 인원수를 검색하시오. (단, 인원수가 많은 부서부터 출력하시오.)
SELECT DEPT_ID, COUNT(*) FROM S_EMP GROUP BY DEPT_ID ORDER BY COUNT(*) DESC ;

● 제 5 장. Subquery
• Single Row Subquery
SUBQUERY의 결과가 1개의 ROW로 나오는 것을 SINGLE ROW SUBQUERY라 하며 다음과 같은 OPERATOR를 사용할 수 있다.
=, >, >=, <, <= VALUE 값을 구하기 위해 SELECT 문장을 사용한다.
SELECT column명, column명... - FROM table명
WHERE column명 operator (SELECT column명 FROM table명 WHERE 조건식 );
[ 예제 ] S_EMP TABLE 에서 LAST_NAME 이 Smith 인 사원과 같은 업무(TITLE)를 하고 있는 사원의 LAST_NAME, TITLE 을 검색하시오.
SELECT LAST_NAME, TITLE FROM S_EMP
WHERE TITLE = ( SELECT TITLE FROM S_EMP WHERE LAST_NAME = 'Smith') ;

• From절의 Subquery
FROM 절에 기술된 SUBQUERY 문은 VIEW 처럼 사용된다.
SELECT alias명.column명, alias명,column명... - FROM table1명 alias1명, (SELECT column2명
FROM table2명 - WHERE 조건식) alias2명 - WHERE alias1명.column1명 OPERATOR alias2명.column2명 ;
[ 예제 ] S_EMP TABLE에서 SALARY가 회사평균급여 보다 적은 사원의 LAST_NAME, SALARY, 회사평균급여를 검색하시오.
SELECT E.LAST_NAME, E.SALARY, S.AVGSAL
FROM S_EMP E, (SELECT AVG(SALARY) AVGSAL FROM S_EMP) S WHERE E.SALARY < S.AVGSAL ;

• Multi Row Subquery
SUBQUERY 의 결과가 여러 ROW 일 때는 반드시 IN OPERATOR 를 사용하여야 한다.
SELECT column명, column명... - FROM table명
WHERE column명 IN ( SELECT column명 FROM table명 WHERE 조건식);
[ 예제 ] S_EMP TABLE과 S_DEPT TABLE에서 Operations부서에서 근무하는 사원의 LAST_NAME, TITLE, DEPT_ID 를 검색하시오.
SELECT LAST_NAME, TITLE, DEPT_ID FROM S_EMP
WHERE DEPT_ID IN (SELECT ID FROM S_DEPT WHERE NAME = 'Operations') ;

• Multi Column Subquery
SELECT 문장의 WHERE 절에서 여러개의 COLUMN 값을 비교하려면 LOGICAL OPERATOR 를 사용하여 여러개의 조건식
을 기술하여야 한다. 그런데 MULTI COLUMN SUBQUERY 를 사용하면 이를 해결할 수 있다.
SELECT column명, column명,,, - FROM table명
WHERE (column명, column명...) IN (SELECT column명, column명... FROM table명 WHERE 조건식);
[ 예제 ] S_EMP TABLE에서 LAST_NAME Patel인 사원과 같은 부서, 같은 업무를 맡고 있는 사원의 LAST_NAME, TITLE, DEPT_ID 를 검색하시오.
SELECT LAST_NAME, TITLE, DEPT_ID FROM S_EMP
WHERE (DEPT_ID, TITLE) IN(SELECT DEPT_ID, TITLEFROM S_EMPWHERE LAST_NAME = 'Patel') ;
SELECT LAST_NAME, TITLE, DEPT_ID FROM S_EMP
WHERE (DEPT_ID) IN (SELECT DEPT_ID FROM S_EMP WHERE LAST_NAME = 'Patel')
OR (TITLE) IN (SELECT TITLE FROM S_EMP WHERE LAST_NAME = 'Patel') ;

- 계속(10) -


● 제 6 장. Table 생성
• 이름 붙이는 법
이름은 다음의 규칙을 따라서 지정한다.
■ TABLE 명이나 COLUMN 명은 문자로 시작하고 30 자 이내로 지정한다. ■ A~Z, a~z, 0~9, _, $, # 을 사용할 수 있다.
■ 한 USER 내에서는 다른 OBJECT 의 이름과 동일하게 지정할 수 없다.
■ ORACLE7 SERVER 예약어를 사용할 수 없다. ■ 대소문자를 구별하지 않는다.

• Oracle 7 datatype
COLUMN 의 DATATYPE 은 다음과 같다.
■ CHAR(size) : 고정된 size 의 문자 값, 최대 255 자까지 지정할 수 있다.
■ VARCHAR2(size) : size내에서의 가변길이 문자 값,최대 2000자까지 지정할 수 있다.
■ LONG : 가변길이 문자 값, 최대 2 기가 바이트까지 사용할 수 있다. TABLE 당 한 개의 COLUMN 에만 지정 가능하다.
■ NUMBER(p,s) : 정수, 소수 자리수의 합이 P, 소수 자리수가 s 인 숫자값, 최대 38 자리수까지 지정할 수 있다.
■ DATE : 날짜와 시간 값, B.C. 4712년 1월 1일부터 A.D. 4712년 12월 31일까지 입력할 수 있다.
■ RAW(size) : size 내에서의 가변길이 BINARY DATA ■ LONGRAW : 가변길이 BINARY DATA

• 다른 table로부터 table생성
기존하는 TABLE 로 부터 데이타와 구조를 복사하여 TABLE 을 생성한다.
CREATE TABLE table명 [(column명, column명...)] - AS subquery ;
[ 예제 ] S_EMP TABLE 에서 DEPT_ID 가 41 인 사원들의 ID, LAST_NAME, USERID, START_DATE 만을 복사하여 EMP_41 TABLE 을 생성하시오.
CREATE TABLE EMP_41 AS SELECT ID, LAST_NAME, USERID, START_DATE
FROM S_EMP WHERE DEPT_ID = 41;
(S_EMP TABLE 에서 COLUMN명, TYPE, SIZE, NOT NULL CONSTRAINT 가 복사되어 EMP_41 TABLE 이 생성되며,
데이타는 DEPT_ID = 41 인 ROW 만 복사된다.)

• Constraint
CONSTRAINT 의 종류는 다음과 같다.
■ NOT NULL : COLUMN에 NULL값이 입력되는 것을 허용하지 않는다. COLUMN-CONSTRAINT 로만 기술해야 한다.
■ UNIQUE : 한 개의 COLUMN혹은 복합 COLUMN을 UNIQUE KEY로 지정하며 UNIQUE KEY에는 중복된 값을 허용하지 않는다.
한개의 COLUMN 으로 구성된 UNIQUE KEY 는 NULL 값을 허용한다.
COLUMN 이나 TABLE-CONSTRAINT 로 기술할 수 있다.
복합 COLUMN 으로 구성된 UNIQUE KEY 는 TABLE-CONSTRAINT 로만 기술해야 한다.
UNIQUE KEY COLUMN 의 UNIQUE INDEX FILE 이 자동 생성된다.
■ PRIMARY KEY
ROW 를 UNIQUE 하게 대표할 수 있는 한개의 COLUMN 혹은 복합 COLUMN 으로 지정한다.
PRIMARY KEY 에는 중복된 값과 NULL 값을 허용하지 않는다.
TABLE 당 한 개의 PRIMARY KEY 만 지정할 수 있다. COLUMN 이나 TABLE-CONSTRAINT 로 기술할 수 있다.
복합 COLUMN 으로 구성된 PRIMARY KEY 는 TABLE-CONSTRAINT 로만 기술해야 한다.
PRIMARY KEY COLUMN 의 UNIQUE INDEX FILE 이 자동 생성된다.
■ FOREIGN KEY
한개의 COLUMN 혹은 복합 COLUMN 으로 지정한다.
같은 TABLE 혹은 다른 TABLE의 PRIMARY KEY 나 UNIQUE KEY 값을 참조한다.
FOREIGN KEY 값은 모 TABLE 에 존재하는 데이타와 같던가 NULL 값을 허용한다.
COLUMN 이나 TABLE-CONSTRAINT 로 기술할 수 있다.

- 계속(11) -


※ CHECK : 각각의 ROW 가 만족해야할 조건을 지정한다.
조건식은 QUERY 조건식과 동일하게 지정한다. (단, 다음과 같은 것은 사용할 수 없다.)
CURRVAL, NEXTVAL, LEVEL, ROWNUM, SYSDATE, USER COLUMN이나 TABLE-CONSTRAINT로 기술할 수 있다.
CONSTRAINT 명은 다음과 같이 지정한다.
? CONSTRAINT 는 DICTIONARY 에 저장되므로 참조하기 쉽게 의미있게 붙여준다.
? 일반적으로 TABLE명_COLUMN명_CONSTRAINT종류와 같은 형태로 지정한다.
? 사용자가 CONSTRAINT 명을 지정하지 않으면 ORACLE7이 SYS_Cn의 형태로 붙인다.
? 동일한 USER 내에서 CONSTRAINT명은 UNIQUE해야 한다.
CONSTRAINT 는 다음과 같이 기술할 수 있다.
COLUMN-CONSTRAINT : column명 [CONSTRAINT constraint명] constraint종류
TABLE-CONSTRAINT : [CONSTRAINT constraint명] constraint종류 (column명, column명..)

• Table 생성
CREATE TABLE table명 - (column명 type(size) [DEFAULT VALUE] [column constraint],
column명 type(size) [DEFAULT VALUE] [column constraint], - .... , - [table constraint], - [table constraint], ....) ;
[ 예제 ] S_EMP TABLE CHART를 보고 TABLE 을 생성하시오.
(단, TABLE CONSTRAINT 로 기술할 수 있는 것은 TABLE CONSTRAINT 로 정의하시오.)
CREATE TABLE S_EMP(ID NUMBER(7), LAST_NAME VARCHAR2(25) CONSTRAINT S_EMP_LAST_NAME_NN NOT NULL,
FIRST_NAME VARCHAR2(25), USERID VARCHAR2(8) CONSTRAINT S_EMP_USERID_NN NOT NULL,
START_DATE DATE DEFAULT SYSDATE, COMMENTS VARCHAR2(25), MANAGER_ID NUMBER(7),
TITLE VARCHAR2(25),
DEPT_ID NUMBER(7),
SALARY NUMBER(11,2),
COMMISSION_PCT NUMBER(4,2),
CONSTRAINT S_EMP_ID_PK PRIMARY KEY(ID),
CONSTRAINT S_EMP_USERID_UK UNIQUE,
CONSTRAINT S_EMP_DEPT_ID_FK FOREIGN KEY(DEPT_ID)
REFERENCES S_DEPT(ID),
CONSTRAINT S_EMP_COMMISSION_PCT CHECK
(COMMISSION_PCT IN (10, 12.5, 15, 17.5, 20))) ;

● 제 7 장. Data DICTIONARY
• DICTIONARY
? DATABASE 가 만들어 졌을때 DICTIONARY TABLE 도 만들어 진다.
? DATABASE 가 사용중일때 DICTIONARY TABLE 은 ORACLE7 SERVER 에 의해 UPDATE 된다.
? 사용자들은 DICTIONARY TABLE 을 SELECT 할 수 있다. ? DICTIONARY TABLE 은 SYS USER 의 소유다.
? DICTIONARY TABLE의 값은 대문자로 들어있다. ? DICTIONARY TABLE 의 종류는 다음과 같은 방법으로 알 수 있다.
SELECT   *
FROM    DICTIONARY ;
DICTIONARY TABLE 의 종류는 다음과 같다.
? USER : USER 가 소유하고 있는 OBJECT 의 정보를 보여준다.
? ALL : USER 가 ACCESS 할 수 있는 OBJECT 의 정보를 보여준다.
? DBA : DBA USER 가 ACCESS 할 수 있는 OBJECT 의 정보를 보여준다.


- 계속(12) -


• 활용예
DICTIONARY TABLE 의 검색예는 다음과 같다.
■ 자신이 갖고 있는 TABLE 의 이름을 검색한다.
SELECT   OBJECT_NAME - FROM    USER_OBJECTS - WHERE   OBJECT_TYPE = 'TABLE';
■ 자신이 갖고 있는 OBJECT 의 종류를 검색한다.
SELECT   DISTINCT OBJECT_TYPE - FROM   USER_OBJECTS;
■ GRANT 와 관련된 DICTIONARY TABLE 의 이름을 검색한다.
SELECT   TABLE_NAME - FROM   DICTIONARY - WHERE   UPPER(COMMENTS) LIKE '%GRANT%';
■ S_EMP TABLE 의 CONSTRAINT 종류를 검색한다.
SELECT   CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION, R_CONSTRAINT_NAME
FROM   USER_CONSTRAINTS - WHERE   TABLE_NAME = 'S_EMP';
■ S_EMP TABLE 의 COLUMN CONSTRAINT 를 검색한다.
SELECT CONSTRAINT_NAME, COLUMN_NAME - FROM USER_CONS_COLUMNS - WHERE   TABLE_NAME = 'S_EMP';

● 제 8 장. Data 조작
• 데이타 입력
TABLE 전체 COLUMN 에 값을 입력한다.
INSERT INTO table명 - VALUES (value, value...);
[ 예제 ] S_EMP TABLE에 다음과 같은 데이타를 입력하시오.
[ID : 26, LAST_NAME : Jung Mi, FIRST_NAME : Hong, USERID : Hjungmi,
START_DATE : 05-APR-97, COMMENTS : Teacher, MANAGER_ID : 10,
TITLE : Stock Clerk, DEPT_ID : 45, SALARY : 1200 COMMISSION_PCT : 10]
INSERT INTO S_EMP
VALUES (26, 'Jung Mi', 'Hong', 'Hjungmi', '05-APR-97', Teacher', 10, 'Stock Clerk', 45, 1200, 10) ;
(값을 지정하는 순서는 TABLE 의 COLUMN 순서에 맞춘다. 이 방법보다는 COLUMN명을 기술하여 입력하는 방법이 더 좋다.)

• 특정 column에 데이터입력
데이타를 입력하고자 하는 COLUMN을 선택하여 입력한다.
INSERT INTO table명(column명, column명....) - VALUES (value, value....);
[ 예제 ] S_EMP TABLE 에 다음과 같은 데이타를 입력하시오.
[ID : 27, LAST_NAME : Smith, FIRST_NAME : Donna, START_DATE : 05-APR-97]
INSERT INTO S_EMP(ID, LAST_NAME, FIRST_NAME, START_DATE)
VALUES (27, 'Smith', 'Donna', '05-APR-97') ;

• Null, 특수 value 입력
COLUMN 값에 NULL 값을 지정하는 방법은 3 가지가 있다. ? INSERT 문장의 COLUMN LIST 에서 생략한다.
? INSERT 문장의 VALUE 절에서 NULL 로 지정한다. ? INSERT 문장의 VALUE 절에서 '' 로 지정한다.
COLUMN 값에 특수한 값을 입력할 수 있다. (SYSDATE : 현재날짜와 시간, USER : 현재 USERID)
[ 예제 ] S_EMP TABLE 에 다음과 같은 데이타를 입력하시오.
[ID : 29, LAST_NAME : Donna, USERID : USER, SALARY : NULL, START_DATE : SYSDATE]
INSERT INTO S_EMP(ID, LAST_NAME, USERID, SALARY, START_DATE)
VALUES (29, 'Donna', USER, NULL, SYSDATE);



- 계속(13) -


• 특수형태의 날짜/시간입력
DATE 값을 입력할 때는 지정된 DATE 형태로 입력하여야 한다.
일반적으로 DD-MON-YY 형태를 사용하며, 이 형태로 데이타를 입력하면 세기는 현재의 세기로, 시간은 자정으로 입력된다.
다른 세기의 날짜나 시간을 입력하고 싶으면 TO_DATE FUNCTION 을 사용한다.
지정된 형태가 아닌 다른 형태의 날짜 값을 입력한다. ex>TO_DATE('날짜값','날짜형태')
[ 예제 ] S_EMP TABLE 에 다음과 같은 데이타를 입력하시오.
[ID : 30, LAST_NAME : Donna, USERID : SQL01, START_DATE : 199704051400]
INSERT INTO S_EMP(ID, LAST_NAME, USERID, START_DATE)
VALUES (30, 'Donna', 'SQL01', TO_DATE('199704051400','YYYYMMDDHH24MI'));

• 다른table로부터 데이타입력
INSERT 문장을 사용하여 기존하는 TABLE 의 데이타를 다른 TABLE 로 COPY 할 수 있다.
INSERT INTO table명[(column명, column명...)] - SUBQUERY;
[ 예제 ] S_EMP TABLE 의 ROW들을 HISTORY TABLE 로 COPY 하시오.
(단, 01-JAN-94 이전에 입사한 사원의 ID,LAST_NAME,SALARY,START_DATE 를 COPY 하시오)
INSERT INTO HISTORY(ID, LAST_NAME, SALARY, START_DATE)
SELECT ID, LAST_NAME, SALARY, START_DATE
FROM S_EMP WHERE START_DATE < '01-JAN-94' ;
(INSERT 절의 COLUMN 수와 SELECT 절의 COLUMN 수는 같아야 한다.)

• 데이타 수정
UPDATE 문장을 사용하여 이미 존재하는 COLUMN 값을 수정한다.
UPDATE table명 - SET column명 = value, [column명 = value] - [WHERE 조건식] ;
[ 예제 ] S_EMP TABLE 에서 ID 가 1 인 사원의 데이타를 다음과 같이 수정하시오. (DEPT_ID : 32, SALARY : 2550)
UPDATE S_EMP SET DEPT_ID = 32, SALARY = 2550 WHERE ID = 2 ;

• 데이타 삭제
DELETE 문장을 사용하여 데이타를 삭제한다.
DELETE FROM table명 - [WHERE 조건식] ;
[ 예제 ] S_EMP TABLE에서 ID 가 20 보다 큰 사원을 삭제하시오.
DELETE FROM S_EMP WHERE ID > 20 ;

• 저장
COMMIT 문장(COMMIT;)에 의해 변경된 모든 내용이 DATABASE에 저장되며 변경된 모든 데이타는 DATABASE에 저장된다.
그 전의 데이타는 완전히 지워지며 모든 사용자가 변경한 내용을 볼 수 있고 변경된 ROW 에 걸려있던 LOCK 이 해제된다.
그러므로 다른 사용자가 수정할 수 있으며 모든 SAVEPOINT 가 없어진다.
TRANSACTION 을 종료하고 TRANSACTION 안의 모든 변경된 작업을 저장한다.

• 취소
ROLLBACK 문장(ROLLBACK)은 모든 변경된 내용을 취소하며 수정하기 전의 데이타가 복구된다.
변경된 ROW 에 걸려있던 LOCK 이 해제되고 다른 사용자들이 그 ROW 에 대해서 변경을 할 수 있다.
TRANSACTION 을 종료하고 TRANSACTION 안의 모든 변경된 작업을 취소한다.



- 계속(14) -


• Savepoint지정~취소
TRANSACTION 안에서 ROLLBACK 할 수 있는 POINT 를 지정한다. 지정된 POINT 까지만 ROLLBACK 한다.
SAVEPOINT savepoint명 ; - ROLLBACK TO savepoint명 ;
[ 예제 ] S_EMP TABLE에서 TITLE이 Stock Clerk 인 사원의 SALARY 를 10% 인상하시오. SAVEPOINT 를 지정하시오.
S_REGION TABLE 에 다음과 같은 데이타를 입력하시오. ID : 8, NAME : Central
SAVEPOINT 까지 ROLLBACK 하시오. UPDATE 결과를 저장하시오.
UPDATE S_EMP SET SALARY = SALARY * 1.1 WHERE TITLE = 'Stock Clerk' ; SAVEPOINT S1;
INSERT INTO S_REGION(ID, NAME) VALUES (8, 'Central') ; ROLLBACK TO S1; COMMIT;

● 제 9 장. Table변경/삭제
• Column 추가
TABLE 에 새로운 COLUMN 을 추가한다.
ALTER TABLE table명 - ADD (column명 type(size) [DEFAULT value] [column_constraint], - ...........) ;
[ 예제 ] S_REGION TABLE 에 다음과 같은 COLUMN 을 추가하시오. (COMMENTS VARCHAR2(25))
ALTER TABLE S_REGION ADD (COMMENTS VARCHAR2(25))
(추가될 COLUMN 의 위치는 지정할 수 없다. 새로운 COLUMN 은 마지막 위치에 생성된다.)

• Column 변경
ALTER TABLE 문장의 MODIFY 절을 사용하여 다음과 같은 변경을 할 수 있다. COLUMN 의 크기를 확장할 수 있다.
데이타가 들어있지 않으면 COLUMN 의 크기를 줄일 수 있다. 데이타가 들어있지 않다면 COLUMN 의 타입을 수정할 수 있다.
COLUMN 에 NULL 값이 없다면 NOT NULL CONSTRAINT 를 지정할 수 있다. DEFAULT VALUE 를 변경할 수 있다.
이미 생성되어 있는 COLUMN 을 변경한다.
ALTER TABLE table명 - MODIFY (column명 type(size) [DEFAULT value] [NOT NULL], - .............) ;

• Constraint 추가
이미 생성되어 있는 TABLE 에 CONSTRAINT 를 추가한다.
ALTER TABLE table명 - ADD (table_constraint) ;
[ 예제 ] S_EMP TABLE 에 다음과 같은 CONSTRAINT 를 추가하시오.
[ MANAGER_ID COLUMN 이 S_EMP TABLE 의 ID COLUMN 을 REFERENCE 하는 FOREIGN KEY CONSTRAINT 를 추가하시오. ]
ALTER TABLE S_EMP
ADD (CONSTRAINT S_EMP_MANAGER_ID_FK FOREIGN KEY(MANAGER_ID) REFERENCES S_EMP(ID)) ;

• Constraint 삭제
이미 생성되어 있는 TABLE 의 CONSTRAINT 를 삭제한다.
ALTER TABLE table명 - DROP PRIMARY KEY | - UNIQUE(column명) | - CONSTRAINT constraint명 [CASCADE] ;
[ 예제 ] S_EMP TABLE의 다음과 같은 CONSTRAINT를 삭제하시오. (MANAGER_ID COLUMN의 FOREIGN KEY CONSTRAINT)
ALTER TABLE S_EMP DROP CONSTRAINT S_EMP_MANAGER_ID_FK ;

• 전체 데이타의 삭제
TRUNCATE 문장은 DDL 이다. ROLLBACK SEGMENT 를 만들지 않고 모든 데이타를 삭제한다.
데이타가 삭제된 FREE 영역은 환원된다. TABLE 로부터 모든 데이타를 삭제한다.
TRUNCATE TABLE table명 ;
[ 예제 ] S_ITEM TABLE 의 모든 데이타를 삭제하시오.
TRUNCATE TABLE S_ITEM ;

- 계속(15) -


• Constraint disable/enable
TABLE 에 있는 모든 데이타가 CONSTRAINT 를 만족시켜야 ENABLE 할 수 있다.
PRIMARY KEY, UNIQUE CONSTRAINT 를 ENABLE 하면 그에 따른 INDEX FILE 이 자동적으로 생성된다.
CASCADE OPTION 은 FOREIGN KEY CONSTRAINT 를 DISABLE 할 때 사용한다.
CONSTRAINT 를 삭제하고 새로 만들지 않고 DISABLE, ENABLE 한다.
ALTER TABLE table명-DISABLE |ENABLE PRIMARY KEY |-UNIQUE(column명) |CONSTRAINT constraint명 [CASCADE] ;
[ 예제 ] S_DEPT TABLE 의 PRIMARY KEY CONSTRAINT 를 DISABLE 시키시오.
ALTER TABLE S_DEPT DISABLE CONSTRAINT S_DEPT_ID_PK CASCADE;
(S_EMP TABLE 의 S_EMP_DEPT_ID_FK CONSTRAINT 도 자동적으로 DISABLE 된다.)

• Table 삭제
TABLE 을 삭제하면 그 TABLE 에 딸린 INDEX FILE 도 삭제된다.
VIEW, SYNONYM, STORED PROCEDURE, FUNCTION, TRIGGER 등은 삭제되지 않는다.
CASCADE CONSTRAINTS 는 모 TABLE 을 삭제하고 자 TABLE 의 FOREIGN KEY CONSTRAINT 도 삭제한다.
DROP TABLE table명 [CASCADE CONSTRAINTS] ;
[ 예제 ] S_DEPT TABLE 을 삭제하시오.
DROP TABLE S_DEPT CASCADE CONSTRAINTS ;

• 이름의 변경
TABLE, VIEW, SEQUENCE, SYNONYM 의 이름을 변경한다.
RENAME old명 TO new명 ;
[ 예제 ] S_ORD TABLE 의 이름을 S_ORDER 로 변경하시오.
RENAME S_ORD TO S_ORDER ;

● 제 10 장. Sequence
• Sequence 생성
SEQUENCE 는 여러 사용자에게 UNIQUE 한 값을 생성해 주는 OBJECT 이다.
SEQUENCE 를 사용하여 PRIMARY KEY 값을 자동적으로 생성한다.
CREATE SEQUENCE sequence명 - INCREMENT BY n - START WITH n - MAXVALUE n | NOMAXVALUE
MINVALUE n | NOMINVALUE - CYCLE | NOCYCLE - CACHE n | NOCACHE ;
[ 예제 ] S_DEPT TABLE 의 ID COLUMN 값에 사용할 SEQUENCE 를 다음과 같이 생성하시오.
(START : 51, INCREMENT : 1, MAXVALUE : 9999999, NOCYCLE, NOCACHE)
CREATE SEQUENCE S_DEPT_ID INCREMENY BY 1 START WITH 51
MAXVALUE 9999999 NOCACHE NOCYCLE ;

• Sequence 변경
SEQUENCE 에 정의된 값을 변경한다.
ALTER SEQUENCE sequence명 - INCREMENT BY n - MAXVALUE n | NOMAXVALUE - MINVALUE n | NOMINVALUE
CYCLE | NOCYCLE - CACHE n | NOCACHE ; -
[ 예제 ] S_DEPT_ID SEQUENCE 를 다음과 같이 수정하시오. (CACHE : 10)
ALTER SEQUENCE S_DEPT_ID
CACHE 10 ;



- 계속(16) -


• Sequence 삭제
SEQUENCE 를 삭제한다.
DROP SEQUENCE sequence명 ;
[ 예제 ] S_DEPT_ID SEQUENCE 를 삭제하시오.
DROP SEQUENCE S_DEPT_ID ;

● 제 11 장. VIEW
• Simple view
SUBQUERY 문장이 간단한 경우 VIEW 를 통해 SELECT,INSERT,UPDATE,DELETE 를 할 수 있다.
■ SELECT : SUBQUERY 의 조건식을 만족하는 데이타만 검색된다.
■ INSERT : NOT NULL COLUMN 을 다 포함하고 있는 경우 INSERT 를 할 수 있다.
SUBQUERY 의 조건식을 만족하지 않는 데이타도 입력이 가능하다.
■ UPDATE : VIEW 를 통해 SELECT 할 수 있는 데이타만 수정할 수 있다.
SUBQUERY 의 조건식을 만족하지 않는 데이타도 수정이 가능하다.
■ DELETE : VIEW 를 통해 SELECT 할 수 있는 데이타만 삭제할 수 있다.
CREATE VIEW view명 [(alias명, alias명....)] - AS SUBQUERY ;
[ 예제 ] S_EMP TABLE에서 DEPT_ID가 45인 사원의 ID, LAST_NAME, DEPT_ID, TITLE을 선택해서 VIEW를 생성하시오.
CREATE VIEW EMP41 AS SELECT ID, LAST_NAME, DEPT_ID, TITLE
FROM S_EMP WHERE DEPT_ID = 45 ;

• With check option
VIEW 를 정의할때 지정한 조건식을 만족하는 데이타만 INSERT, 또는 조건식을 만족하는 데이터로만 UPDATE 가 가능하다.
데이타가 VIEW 의 조건식을 만족하는지 CHECK 한다.
CREATE VIEW view명 [ (alias명, alias명...)] - AS SUBQUERY - WITH CHECK OPTION ;
[ 예제 ] S_EMP TABLE에서 DEPT_ID가 45인 사원의 ID, LAST_NAME, DEPT_ID, TITLE을 선택해서 VIEW를 생성하시오.
(단, DEPT_ID 가 45 가 아닌 사원은 입력되지 못하게 만드시오.)
CREATE VIEW EMP45 AS SELECT ID, LAST_NAME, DEPT_ID, TITLE
FROM S_EMP WHERE DEPT_ID = 45 WITH CHECK OPTION ;

• With read only
SELECT만 가능한 VIEW 를 생성한다.
CREATE VIEW view명 [(alias명, alias명...)] - AS SUBQUERY - WITH READ ONLY ;
[ 예제 ] S_EMP TABLE 에서 ID, LAST_NAME, DEPT_ID, SALARY 가 SELECT 만 되도록 VIEW 를 생성하시오.
CREATE VIEW R_EMP AS SELECT ID, LAST_NAME, SALARY
FROM S_EMP WITH READ ONLY ;

• Force
기준 TABLE 이 존재하지 않아도 VIEW 를 생성한다.
CREATE FORCE VIEW view명 [(alias명, alias명...)] - AS SUBQUERY ;
[ 예제 ] S_EMP TABLE 이 없어도 S_EMP TABLE 에서 ID, LAST_NAME, SALARY 를 선택해서 VIEW 를 생성하시오.
CREATE FORCE VIEW T_EMP
AS SELECT ID, LAST_NAME, SALARY
FROM S_EMP ;


- 계속(17) -


• complex view
SUBQUERY문장에 JOIN, FUNCTION, DISTINCT/연산이 포함된 경우를 말하며 이 경우 VIEW를 통한 DML은 수행할 수 없다.
COMPLEX VIEW 를 생성한다. CREATE VIEW view명 (alias명, alias명...) - AS SUBQUERY ;
[ 예제 ] S_EMP TABLE과 S_DEPT TABLE에서 ID, LAST_NAME, DEPT_ID, NAME을 선택해서 VIEW를 생성하시오.
CREATE VIEW EMPDEPT AS SELECT E.ID, E.LAST_NAME, E.DEPT_ID, D.NAME
FROM S_EMP E, S_DEPT D WHERE E.DEPT_ID = D.ID ;

• View 삭제
VIEW 를 삭제하면 DATABASE 로부터 VIEW 의 정의가 삭제된다. VIEW 가 기초로 한 TABLE 은 삭제되지 않는다.
DROP VIEW view명 ;
[ 예제 ] EMPDEPT VIEW 를 삭제하시오.
DROP VIEW EMPDEPT ;

● 제 12 장. Index
• Index 생성
TABLE 생성시 PRIMARY KEY 나 UNIQUE CONSTRAINT 를 지정하면 UNIQUE INDEX 가 자동적으로 만들어 진다.
이 외의 COLUMN 으로 QUERY 를 할 때 속도를 향상시키기 위해서 INDEX 를 생성한다.
INDEX를 생성하면 QUERY 속도는 빨라질 수 있으나 DML속도는 늦어질 수 있다.
일반적으로 다음과 같은 경우에 INDEX 를 생성한다.
■ COLUMN 이 WHERE 절이나 JOIN 조건식에 빈번하게 사용될 때 ■ COLUMN 값이 넓게 분포되어 있을 때
■ COLUMN 값에 NULL 값이 많이 포함되어 있을 때 ■ TABLE 이 크고 QUERY 하는 데이터 양이 10 % 이하일 때
CREATE [UNIQUE] INDEX index명 - ON table명(column명[, column명...]) ;
[ 예제 ] S_EMP TABLE 에서 LAST_NAME 의 QUERY 속도를 향상하기 위하여 INDEX 를 생성하시오.
CREATE INDEX S_EMP_LAST_NAME_IDX ON S_EMP(LAST_NAME) ;

• Index 삭제
INDEX 는 수정할 수 없다. 수정하고 싶은 경우 삭제하고 다시 생성한다.
DROP INDEX index명 ;
[ 예제 ] S_EMP_LAST_NAME_IDX INDEX 를 삭제하시오.
DROP INDEX S_EMP_LAST_NAME_IDX ;
Posted by 톰켓 Trackback 0 Comment 0
1). Table A와 Table B 가 있는데. A의 내용을 B로 복사하는 방법
SQL>INSERT INTO b SELECT * FROM a;

2). Table A가 있고 Table B를 생성하면서 복사하는 방법
SQL>CREATE TABLE b AS SELECT * FROM a;

3). Table A가 있고 Table A와 구조가 같고 내용은 복사하지 않는 Table B 생성 방법
SQL>CREATE TABLE b AS SELECT * FROM a WHERE 1 = 2;
Posted by 톰켓 Trackback 0 Comment 0
No. 10636

ORA-12203 에러에 대한 점검 사항
==============================

ORA-12203 에러는 가장 광범위하게 나오는 에러로 서버의 문제인 경우도 있고
클라이언트 문제인 경우도 있기 때문에 양쪽을 모두 점검해 보아야 한다.

===============
서버 쪽 점검사항
===============

1) listener가 제대로 기동이 되어 있는지 점검한다.

 lsnrctl status

   로 보면 된다. 만약 안 떠 있으면 lsnrctl start 로 띄워 준다.


2) $ORACLE_HOME/dbs/init<SID>.ora 에서 세팅된

 mts_...........

   로 시작하는 파라미터 가운데 프로토콜이 TCP인 것만 두고 IPC로 된 것은
   앞에 #을 넣어서 COMMENT 처리하도록 한다.

   마찬가지로 listener.ora에 IPC 프로토콜 관련 내용이 세팅되어 있으면 이것도
   삭제한다.

3) init<SID>.ora와 listener.ora에서 HOST가 hostname으로 지정되어 있으면
   이것을 모두 ip address로 직접 지정한다.

4) 다음과 같이 해서 실행 화일들을 relink 해본다. DB와 LISTENER를 SHUTDOWN하고
   다음 작업을 한 후에 다시 기동한다.

 cd $ORACLE_HOME/bin
 cp oracle oracle.org
 cp tnslsnr tnslsnr.org

 cd $ORACLE_HOME/network/lib
 make -f network.mk tnslsnr
 chmod 4751 tnslsnr
 cp tnslsnr $ORACLE_HOME/bin

 cd $ORACLE_HOME/rdbms/lib
 make -f oracle.mk oracle
 chmod 6751 oracle
 cp oracle $ORACLE_HOME/bin

 참고로 ORACLE 7.3의 경우에는 oracle.mk 대신에 ins_rdbms.mk를 사용하고
 network.mk 대신에 ins_network.mk 를 사용한다.
 
5) 서버 상에서 LOOPBACK TEST를 해본다. 예를 들어 tnsnames.ora에 세팅된
   ALIAS가 TORA라고 한다면

 sqlplus scott/tiger@TORA

 로 접속을 해보면 된다. 이것이 접속되면 서버쪽은 이상이 없는 것으로 볼 수 있다.


=========================
클라이언트(PC) 점검 사항
=========================

여기서는 win95 용 SQL*Net을 기준으로 설명하지만 Windows 3.1 용 SQL*Net의
경우에는 ORACLE HOME DIRECTORY가 orawin이라는 점만 다르다고 보면 된다.

1) SQL*NET 인스톨 시 SQL*Net V2.x만 깔고 TCP/IP protocol adapter가 install
   되어 있지 않은 경우에 발생한다.
   C:\orawin95\orainst\win95.rgs 화일을 열어서 현재 인스톨되어 있는
   제품 목록을 확인한다.

2) C:\orawin95\network\admin 디렉토리에 tnsnames.ora 화일이 아예 없는 경우
  에도 발생한다. tnsnames.ora 를 적절히 세팅하면 된다.

3) WSOCK32.DLL(또는 WINSOCK.DLL) 화일이 여러 종류가 존재하는 경우 사용 중인
   TCP/IP VENDOR에서 제공하는 것이 아닌 다른 화일을 사용하게 되는 경우이다.
   TCP/IP VENDOR에서 제공하는 DLL 화일만 남기고 나머지는 다른 이름으로
   RENAME하고 나서 PC를 REBOOTING한 다음 테스트 하면 된다.

4) TELNET이나 FTP 서비스가 제대로 동작하는지도 확인한다.

Posted by 톰켓 Trackback 0 Comment 0
 

 


  USE_NL hint는 테이블을 적는 부분에 테이블 기술된 table은 inner table로서 사용하여

     nested loops로서 다른 테이블의 row source와 기술된 table을 join하게 한다.

  USE_NL hint의 문법은

         table : nested loops join의 inner table로서 사용될 테이블의 이름과 alias이다.

  예를 들어서, ACCOUNTS와 CUSTOMERS 테이블을 조인하는 SQL문이 있다고 생각해보자.

     이들 테이블들은 cluster에 함께 저장되지 않았다고 가정한다.

·SELECT accounts.balance, customers.last_name, customers.first_name

              FROM accounts, customers

              WHERE accounts.custno = customers.custo;

  cost-based approach의 default 목적은 best throughput이므로 이 optimizer는

     좀 더 빨리 질의에 의해 select된 모든 행들을 반환하기위해 nested loops operation이나

     sort-merge operation중 하나를 선택한다.

  그러나 질의에 의해 선택된 첫 번째 행만 반환할 때 필요시간이 매우 적어야 할 경우에는

     best throughput보다 best response time으로 SQL문을 최적화하는 것이 더 낫다.

  그렇게 하려면 USE_NL hint를 사용함으로서 optimizer가 nested loops join을

     강제로 선택하게 할 수 있다.

  SQL문에서 USE_NL hint는 CUSTOMERS테이블을 inner table로 가지는

     nested loop를 선택

·SELECT /*+ ORDERED USE_NL(customers) Use N-L to get first row faster */

              accounts.balance, customers.last_name, customers.first_name

              FROM accounts, customers

              WHERE accounts.custno = customers.custno;

  많은 경우에 nested loops join은 sort-merge join보다는 더빨리 첫 번째 행을 반환한다.

  Nested loop join은 한 테이블로부터 첫 번빼 select한 행을 읽은 후에 첫 번째 행을

    반환할 수 있고, 다른 테이블에서 첫 번째로 일치하는 행을 찾고 그것들을 결합한다.

  반면에 sort-merge join은 양 테이블에서 select한 모든 row들을 읽고 정렬한 후 각각의

     저장된 row source의 첫 번째 행들을 결합할 때까지 첫 번째 행을 반환할 수 없다.


원본 위치 <http://blog.empas.com/elly32/read.html?a=26354384>

Posted by 톰켓 Trackback 0 Comment 0
오라클 데이터베이스  scott유저의 emp테이블을 보면 empno와 mgr컬럼이 있습니다.
 

mgr 컬럼의 데이터는 해당 사원의 관리자의 empno를 의미 합니다.
 

예를 들어서 아래의 데이터를 보면은..
 

  EMPNO ENAME         SAL        MGR
 ------ --------- ------- ----------
   7369     SMITH          800       7902
   7902     FORD          3000       7566


empno 7369사원의  관리자는 7902의 empno를 가진 사원이며
empno 7902사원의  관리자는 7566의 empno를 가진 사원입니다.
 
 
이런 상위 계층과 하위계층의 관계를 오라클에서는 START WITHCONNECT BY를 이용해서
쉽게 가져올 수 있습니다.
 
상품의 카테고리(대분류,중분류,소분류...)를 조회 할때 START WITH와 CONNECT BY를 이용하면
트리 구조로 편리하게 조회 할 수 있습니다.


게시판에서의 일반글과 답변글 과의 관계에서도 사용 할 수 있습니다.

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

START WITH와 CONNECT BY를 이용해 데이터를 계층적인 순서로  조회할 수 있습니다.
 
 
START WITH
 
 - 계층 질의의 루트(부모행)로 사용될 행을 지정 합니다..
 - 서브쿼리를 사용할 수도 있습니다.
 
 
CONNECT BY
 
 - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 합니다.
 - 보통 PRIOR 연산자를 많이 사용 합니다..
 - 서브쿼리를 사용할 수 없습니다..
 
 
 
CONNECT BY의 실행순서는 다음과 같습니다.
 
 - 첫째 START WITH절
 - 둘째 CONNECT BY 절
 - 세째 WHERE 절 순서로 풀리게 되어있습니다.

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

-- 테스트를 위해서 scott유저로 접속을 합니다.
SQLPLUS scott/tiger
 
 
예제1)상위계층과 하위 계층 관계의 순서대로 쿼리해 옴
 
 
-- LEVEL 예약어를 사용하여  depth를 표현 할 수 있습니다.
-- 직업이 PRESIDENT을 기준으로 계층 구조로 조회합니다.
 
SQL>SELECT LEVEL,empno,ename, mgr
        FROM  emp
        START WITH job = 'PRESIDENT'   -- 직업이 PRESIDENT를 기준으로
        CONNECT BY PRIOR  empno = mgr; -- 사원(empno)과 관리자(mgr)의  관계를 계층 구조로 조회
   
    LEVEL      EMPNO ENAME                       MGR
--------- ---------- -------------------- ----------
        1       7839      KING
        2       7566      JONES                      7839
        3       7788      SCOTT                     7566 
        4       7876      ADAMS                     7788
        3       7902      FORD                       7566
        4       7369      SMITH                       7902
        2       7698      BLAKE                       7839
        3       7499      ALLEN                       7698
        3       7521      WARD                       7698
        3       7654      MARTIN                     7698
        3       7844      TURNER                     7698
        3       7900      JAMES                       7698
        2       7782      CLARK                       7839
        3       7934      MILLER                      7782


--  LEVEL컬럼은 depth를 나타냅니다.
-- JONES의 관리자는 KING을 나타냅니다.
--  SCOTT의 관리자는 JONES를 나타냅니다.
--  예제와 같이 상/하의 계층 구조를 쉽게 조회 할 수 있습니다.

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

예제2) 사원성명을 계층 구조로 보여 줌


SQL>SET LINESIZE 100
SQL>SET PAGESIZE 100
SQL>COL ename FORMAT A20

 -- 예제1의 결과를 가지고 LPAD함수를 이용해서 ename왼쪽에 공백을 추가 했습니다.
SQL>SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job
       FROM emp
       START WITH job='PRESIDENT'
       CONNECT BY PRIOR empno=mgr;

 ENAME                     EMPNO        MGR JOB
-------------------- ---------- ---------- ---------
KING                        7839                   PRESIDENT
    JONES                  7566       7839      MANAGER
        SCOTT             7788       7566      ANALYST
            ADAMS         7876       7788      CLERK
        FORD               7902       7566      ANALYST
            SMITH          7369       7902      CLERK
    BLAKE                  7698       7839      MANAGER
        ALLEN               7499       7698     SALESMAN
        WARD               7521       7698     SALESMAN
        MARTIN             7654       7698     SALESMAN
        TURNER            7844       7698     SALESMAN
        JAMES              7900       7698     CLERK
    CLARK                  7782       7839     MANAGER
        MILLER              7934       7782     CLERK

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

예제3) 레벨이 2까지만 쿼리해서 가져오는 예제
   
SQL>SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job
        FROM emp
        START WITH job='PRESIDENT'
        CONNECT BY PRIOR empno=mgr
       
AND LEVEL <=2 ;
   
ENAME                     EMPNO        MGR JOB
-------------------- ---------- ---------- ---------
KING                        7839                   PRESIDENT
    JONES                  7566       7839      MANAGER
    BLAKE                  7698       7839      MANAGER
    CLARK                  7782       7839      MANAGER  

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

예제4) 각 label별로 급여의 합과 인원수를 구하는 예제
 
SQL> SELECT LEVEL, SUM(sal) total,COUNT(empno) cnt
        FROM emp
        START WITH job='PRESIDENT'
        CONNECT BY PRIOR empno=mgr      
       
GROUP BY LEVEL
        ORDER BY LEVEL;
 
     LEVEL      TOTAL        CNT
---------- ---------- ----------
         1       5000          1
         2       8275          3
         3      13850          8
         4       1900          2

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

데이터가 많아질 경우....
 
 -
첫째로 풀리는 START WITH job='PRESIDENT' job 컬럼에 index가 생성되어 있지 않는다면
    속도를 보장할 수 없습니다.
 
 - 그리고 둘째로 풀리는 CONNECT BY PRIOR empno = mgr 역시 PRIOR 쪽의 컬럼값이 상수가
   되기 때문에 MGR컬럼에 index를 생성하여야 CONNECT BY의 속도를 보장할 수 있습니다.
   
 - 계층구조를 CONNECT BY, START WITH로 풀면 부분범위 처리가 불가능하고 Desc으로
   표현하기가 어렵 습니다.


참고..
 
http://dblab.changwon.ac.kr/oracle/sqltest/hierarchical.html/
http://www.en-core.com/

 

출처 : 성하경


좋은 자료 감사합니다..

Posted by 톰켓 Trackback 0 Comment 0

저는 TOAD나 PL/Sql Developer를 사용하는데요
웬지 무겁고 라이센스가 있어서
좀 찜짐해서
찾은 결과
가볍고 깔끔하고 무료인 툴을 발견했습니다.
참으로 사용법도 간단하니 좋아용..
모두 사용해 보세용.

Posted by 톰켓 Trackback 0 Comment 0