ICT블로그

cast, convert 등의 함수를 쓸 때 분명히 overflow가 발생하지 않을것이라고 예상했는데 발생하는 경우가 있다.

아래의 예를 보자.

select convert(bigint,accounted_debit) from dbo.JE_Sample
where DR_CR = 'CR' and accounted_debit <> 0;

실행시키면 이런 메시지가 뜬다.

Msg 248, Level 16, State 1, Line 63
The conversion of the nvarchar value '2794014278' overflowed an int column.

분명 accounted_debit의 가장 큰 자리수는 14자리라 bigint로 충분히 가능하다,

하지만 왜 이런 현상이 발생할까...?

눈치채셨는지..?

올바른 쿼리는 다음과 같다.

select convert(bigint,accounted_debit) from dbo.JE_Sample
where DR_CR = 'CR' and convert(bigint,accounted_debit) <> 0;

where 절에서 convert함수(명시적 형변환)을 안해줬기 때문에 select 절이 아닌 where절에서 묵시적 형 변환으로 bigint 가 아닌 int로 변환하는 과정에서 overflow가 발생한 것이다!

이것을 깜빡하여 시간을 낭비하는 저 같은 사람이 없기를 바라며 극미세팁을 올린다.... ㅠㅠㅠ

추가 (2018-09-14)

ORA-00257은 DB을 Archive log Mode로 운영중일 때 발생할 수 있는 에러입니다. Redo Log에 대한 archiving이 정상적으로 완료되지 않았을 경우, Log switch가 실패하며 DB를 정상적으로 사용할수 없습니다. (sysdba 유저 외 일반 유저는 접속불가)

이때 예상되는 문제로는 다음 2가지가 있습니다.

(1) Archiving 공간이 Full이 됨. (File system 혹은 지정한 ASM diskgroup)

(2) FRA기능을 사용할 때 FRA에 할당된 공간(=db_recovery_file_dest_size 파리미터 값)이 100%가 되어, 더 이상 archive log가 저장될 수 없을 때 발생--> ORA-19809, 19804, 19815 에러와 함께 발생하는 특징

실제 DB운영시에는 (2)번의 경우를 주의해야 하는데요, 왜냐하면 (1)번의 경우 일반적으로 File System에 대한 사용률 모니터링, ASM Diskgroup에 대한 사용률 모니터링을 통해 100% 임계치에 도달하기 전에 장애를 예방할 수 있기 때문입니다.

따라서 해당 에러 발생전 사전에 DB의 FRA기능 사용유무를 파악하고 (db_recovery_file_dest_size<>0 이면 FRA는 ON이 된것입니다.) 아카이브 Full로 인한 장애를 예방하는 것이 중요합니다.

실제 운영중인 DB가 아닐경우, 또는 개인이 학습용도로 사용하는 경우 Archive Log는 DB복구에 사용될 뿐이지 필수 파일이 아니므로, 해당 에러가 발생한다면 임의로 archive log 파일을 삭제해도 무방합니다. 단, 운영계 시스템이라면 해당 archive log가 추후 복구 등에 사용될수 있으므로 (시간적 여유가 된다면) 백업 후 archive log를 삭제하거나, 적어도 여유가되는 다른 File system이나 asm diskgroup에 아카이브 로그 파일을 move하기를 권장드립니다.

그리고 FRA기능을 꼭 써야 하는 경우가 아니라면 db_recovery_file_dest_size=0으로 하는것도 관리포인트를 줄이는 효과가 있습니다.

아래는 저의 학습과정을 기록한 것으로 skip 하셔도 무방합니다.

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

※ 주의! : 저의 지식 한계 내에서 문제를 파악하고 분석한 글로서, 부족하거나 잘못된 내용이 포함될 수 있습니다. 참고만 해주시길 바라며, 지적 및 조언은 언제나 환영합니다.

(1) 로그인 시 ORA-00257에러가 떨어짐 (sqlplus "/as sysdba")

sys user를 제외한 전 유저로 로그인시에도 동일한 현상 발생

(2) ORA-00257 에러를 확인한다. (E17766-03)

** archiving 영역의 공간 부족이 의심된다.

(3) alert log, trace log, 관련 제반 parameter 확인

- alert log 확인

************************************************************************

Errors in file f:\app\inseong\diag\rdbms\test1\test1\trace\test1_arc1_4720.trc:

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 32109056 bytes disk space from 4102029312 limit

ARC1: Error 19809 Creating archive log file to 'F:\APP\INSEONG\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2016_03_14\O1_MF_1_279_%U_.ARC'

Mon Mar 14 23:29:16 2016

Errors in file f:\app\inseong\diag\rdbms\test1\test1\trace\test1_arc3_4728.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 4102029312 bytes is 100.00% used, and has 0 remaining bytes available.

************************************************************************

You have following choices to free up space from recovery area:

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,

   then consider changing RMAN ARCHIVELOG DELETION POLICY.

2. Back up files to tertiary device such as tape using RMAN

   BACKUP RECOVERY AREA command.

3. Add disk space and increase db_recovery_file_dest_size parameter to

   reflect the new space.

4. Delete unnecessary files using RMAN DELETE command. If an operating

   system command was used to delete files, then use RMAN CROSSCHECK and

   DELETE EXPIRED commands.

************************************************************************

Errors in file f:\app\inseong\diag\rdbms\test1\test1\trace\test1_arc3_4728.trc:

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 32109056 bytes disk space from 4102029312 limit

ARC3: Error 19809 Creating archive log file to 'F:\APP\INSEONG\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2016_03_14\O1_MF_1_279_%U_.ARC'

==> 정확히 1분 간격으로 해당 error 메시지가 alert.log에 기록됨.

- trace file (archive process) 확인

*** 2016-03-14 23:32:16.882

ORA-19815: WARNING: db_recovery_file_dest_size of 4102029312 bytes is 100.00% used, and has 0 remaining bytes available.

************************************************************************

You have following choices to free up space from recovery area:

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,

   then consider changing RMAN ARCHIVELOG DELETION POLICY.

2. Back up files to tertiary device such as tape using RMAN

   BACKUP RECOVERY AREA command.

3. Add disk space and increase db_recovery_file_dest_size parameter to

   reflect the new space.

4. Delete unnecessary files using RMAN DELETE command. If an operating

   system command was used to delete files, then use RMAN CROSSCHECK and

   DELETE EXPIRED commands.

************************************************************************

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 32109056 bytes disk space from 4102029312 limit

*** 2016-03-14 23:32:17.074 4132 krsh.c

ARC3: Error 19809 Creating archive log file to 'F:\APP\INSEONG\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2016_03_14\O1_MF_1_279_%U_.ARC'

*** 2016-03-14 23:32:17.074 2747 krsi.c

krsi_dst_fail: dest:1 err:19809 force:0 blast:1

DDE rules only execution for: ORA 312

----- START Event Driven Actions Dump ----

---- END Event Driven Actions Dump ----

----- START DDE Actions Dump -----

Executing SYNC actions

----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----

Successfully dispatched

----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----

Executing ASYNC actions

----- END DDE Actions Dump (total 0 csec) -----

*** 2016-03-14 23:32:17.084 4529 kcrr.c

==> alert log, trace log의 내용은 대동소이하며, 결국 db_recovery_file_dest_size에서 설정된 한계치 만큼 archive log가 공간을 다 사용했기 때문으로 추정된다.

해결방법은 1. 필요없는 archive file 삭제

            2. db_recovery_file_dest_size 크기 확장 

두가지 정도로 정리된다.

※ no archive log mode로 운영 ==> 이것은 해결방안이 아니다.

-- 설정 변경 적용 전 몇가지 DB 설정사항 확인 및 세부 현상 확인

현재 DB는 archive log mode로 운영되고 있으며, 279번째 리두로그가 archiving 되어야 하나, 현재 archive 저장 영역 (db_recovery_file_dest_size)의 사용량이 100%이기 때문에 archive 되지 못하고 있다. (트레이스 파일 분홍색에서 확인 가능)

v$log의 조회내용이며, 현재 1, 2, 3번 리두 로그 그룹이 공통적으로 아카이빙이 되지 않았음(ARC=NO)을 확인 할 수 있다.

실제 db_recovery_file_dest_size 확인 내용 (3912M=4,102,029,312 bytes)

실제로 flash_recovery_area 영역이 한계치에 다다랐음을 알 수 있다.

주의! 이 부분은 디스크 사용량과는 별개이다.

(3) 문제해결

1. 필요없는 archive file 삭제

가장 오래된 archive log file을 삭제하여, 추가용량을 800M 정도 확보하였다.

system user로 login 시 여전히 같은 에러(ORA-00257)이 발생하는데, DB를 재시작 하면 해결될까?

Errors in file f:\app\inseong\diag\rdbms\test1\test1\trace\test1_arc3_4728.trc:

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 32109056 bytes disk space from 4102029312 limit

ARC3: Error 19809 Creating archive log file to 'F:\APP\INSEONG\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2016_03_15\O1_MF_1_279_%U_.ARC'

Tue Mar 15 00:11:50 2016

Active call for process 15292 user 'SYSTEM' program 'ORACLE.EXE (J002)'

SHUTDOWN: waiting for active calls to complete.

Tue Mar 15 00:12:17 2016

Errors in file f:\app\inseong\diag\rdbms\test1\test1\trace\test1_arc0_4656.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 4102029312 bytes is 100.00% used, and has 0 remaining bytes available.

shutdown immediate 를 수행한지 20여분이 지났어도 DB가 정상적으로 shutdown 되지 않는다. Ctrl+ C로 작업 중단 후 확인하자, DB가 비정상 종료(중)인걸 알수 있었다. 결국 윈도우 서비스관리(services.msc)를 통해 서비스 중단 및 재시작하였다.

SQL> startup

DB가 OPEN되지 않는다. 어떤 문제일까..

Errors in file f:\app\inseong\diag\rdbms\test1\test1\trace\test1_arc3_8992.trc:

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 33826304 bytes disk space from 4102029312 limit

ARC3: Error 19809 Creating archive log file to 'F:\APP\INSEONG\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2016_03_15\O1_MF_1_280_%U_.ARC'

   BACKUP RECOVERY AREA command.

3. Add disk space and increase db_recovery_file_dest_size parameter to

   reflect the new space.

4. Delete unnecessary files using RMAN DELETE command. If an operating

   system command was used to delete files, then use RMAN CROSSCHECK and

   DELETE EXPIRED commands.

************************************************************************

Errors in file f:\app\inseong\diag\rdbms\test1\test1\trace\test1_ora_16388.trc:

ORA-19809: 복구 파일에 대한 한계를 초과함

ORA-19804: 32109056바이트의 디스크 공간(4102029312 제한)을 재생할 수 없음

ARCH: Error 19809 Creating archive log file to 'F:\APP\INSEONG\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2016_03_15\O1_MF_1_279_%U_.ARC'

Errors in file f:\app\inseong\diag\rdbms\test1\test1\trace\test1_ora_16388.trc:

ORA-16038: 3 로그 279 시퀀스 번호는 기록될 수 없습니다

ORA-19809: 복구 파일에 대한 한계를 초과함

ORA-00312: 온라인 로그 3 스레드 1: 'F:\APP\INSEONG\ORADATA\TEST1\REDO03.LOG'

USER (ospid: 16388): terminating the instance due to error 16038

Instance terminated by USER, pid = 16388

동일한 문제로 보인다. archive log file 중 일부를 삭제했음에도 여전히 저장 공간 오류가 나는것을 이해하기가 어렵다.

해결방법 2. db_recovery_file_dest_size 크기 확장 

다행히 db_recovery_file_dest_size 는 modifiable로, alter system 명령어로 변경가능하다.

database mount 수행 후 파라미터 값 변경 --> database open까지 정상적으로 완료되었다.

※alert log 내용 참조

Instance terminated by USER, pid = 16388 --> shutdown immediate 중 Ctrl+C 로 강제 취소했을때

Tue Mar 15 00:37:50 2016

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on. 

IMODE=BR

ILAT =27

LICENSE_MAX_USERS = 0

SYS auditing is enabled

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options.

Using parameter settings in server-side spfile F:\APP\INSEONG\PRODUCT\11.2.0\DATABASE\SPFILETEST1.ORA

System parameters with non-default values:

  processes                = 150

  memory_target            = 2G

  memory_max_target        = 3G

  control_files            = "F:\APP\INSEONG\ORADATA\TEST1\CONTROL01.CTL"

  control_files            = "F:\APP\INSEONG\FLASH_RECOVERY_AREA\TEST1\CONTROL02.CTL"

  db_block_size            = 8192

  compatible               = "11.2.0.0.0"

  db_recovery_file_dest    = "F:\app\inseong\flash_recovery_area"

  db_recovery_file_dest_size= 3912M  --> 변경이 필요한 parameter 값

  undo_tablespace          = "UNDOTBS1"

  remote_login_passwordfile= "EXCLUSIVE"

  audit_sys_operations     = TRUE

  db_domain                = ""

  dispatchers              = "(PROTOCOL=TCP) (SERVICE=TEST1XDB)"

  local_listener           = "LISTENER_TEST1"

  audit_file_dest          = "F:\APP\INSEONG\ADMIN\TEST1\ADUMP"

  audit_trail              = "XML"

  audit_trail              = "EXTENDED"

  db_name                  = "TEST1"

  open_cursors             = 300

  diagnostic_dest          = "F:\APP\INSEONG"

Tue Mar 15 00:37:51 2016

PMON started with pid=2, OS id=10512 

Tue Mar 15 00:37:51 2016

VKTM started with pid=3, OS id=15484 at elevated priority

VKTM running at (10)millisec precision with DBRM quantum (100)ms

Tue Mar 15 00:37:51 2016

GEN0 started with pid=4, OS id=14140 

Tue Mar 15 00:37:51 2016

DIAG started with pid=5, OS id=14836 

Tue Mar 15 00:37:52 2016

DBRM started with pid=6, OS id=16916 

Tue Mar 15 00:37:52 2016

PSP0 started with pid=7, OS id=12912 

Tue Mar 15 00:37:52 2016

DIA0 started with pid=8, OS id=12248 

Tue Mar 15 00:37:52 2016

MMAN started with pid=9, OS id=11272 

Tue Mar 15 00:37:53 2016

DBW0 started with pid=10, OS id=16336 

Tue Mar 15 00:37:53 2016

LGWR started with pid=11, OS id=17308 

Tue Mar 15 00:37:53 2016

CKPT started with pid=12, OS id=16664 

Tue Mar 15 00:37:53 2016

SMON started with pid=13, OS id=14868 

Tue Mar 15 00:37:53 2016

RECO started with pid=14, OS id=988 

Tue Mar 15 00:37:53 2016

MMON started with pid=15, OS id=16096 

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 1 shared server(s) ...

ORACLE_BASE from environment = F:\app\inseong

Tue Mar 15 00:37:54 2016

ALTER DATABASE   MOUNT

Tue Mar 15 00:37:54 2016

MMNL started with pid=16, OS id=16424 

Successful mount of redo thread 1, with mount id 1283555746

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE   MOUNT --> MOUNT 완료

Tue Mar 15 00:39:42 2016

ALTER SYSTEM SET db_recovery_file_dest_size='10240M' SCOPE=BOTH; --> 파라미터 값 변경

Tue Mar 15 00:39:42 2016

db_recovery_file_dest_size of 10240 MB is 38.07% used. This is a --> 사용량이 나오며 깨알같은 설명도...

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup. 

Tue Mar 15 00:40:53 2016

alter database open

Tue Mar 15 00:40:53 2016

LGWR: STARTING ARCH PROCESSES

Tue Mar 15 00:40:53 2016

ARC0 started with pid=20, OS id=16708 

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Tue Mar 15 00:40:54 2016

ARC1 started with pid=21, OS id=14076 

Tue Mar 15 00:40:54 2016

ARC2 started with pid=22, OS id=16032 

ARC1: Archival started

ARC2: Archival started

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

Archived Log entry 137 added for thread 1 sequence 279 ID 0x4bb0414b dest 1:

Thread 1 advanced to log sequence 282 (thread open)

Thread 1 opened at log sequence 282

  Current log# 3 seq# 282 mem# 0: F:\APP\INSEONG\ORADATA\TEST1\REDO03.LOG

Tue Mar 15 00:40:54 2016

ARC3 started with pid=23, OS id=2980 

Archived Log entry 138 added for thread 1 sequence 280 ID 0x4bb0414b dest 1:

Archived Log entry 139 added for thread 1 sequence 281 ID 0x4bb0414b dest 1:

Successful open of redo thread 1

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Tue Mar 15 00:40:57 2016

SMON: enabling cache recovery

Successfully onlined Undo Tablespace 2.

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is KO16MSWIN949

No Resource Manager plan active

SMON: Parallel transaction recovery tried

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Tue Mar 15 00:40:57 2016

QMNC started with pid=32, OS id=11476 

Completed: alter database open

Tue Mar 15 00:40:58 2016

Starting background process CJQ0

Tue Mar 15 00:40:58 2016

CJQ0 started with pid=34, OS id=15816 

Setting Resource Manager plan SCHEDULER[0x3003]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

Tue Mar 15 00:41:01 2016

Starting background process VKRM

Tue Mar 15 00:41:01 2016

VKRM started with pid=33, OS id=15316 

Tue Mar 15 00:50:58 2016

Starting background process SMCO

Tue Mar 15 00:50:58 2016

SMCO started with pid=24, OS id=10976 

279~281 sequence#에 해당하는 online redo group들이 모두 archiving 완료되었고, current redo group은 group3, sequence#는 282이다.

archiving이 정상적으로 완료되었음을 파일 시스템 영역에서도 확인할수 있다.

또한 아까 문제상황에서는 수행되지 못했던 'alter system switch logfile;' 구문도 정상적으로 수행됨을 확인 할 수 있었다.

덤으로 체크 포인트까지(참고로 체크포인트는 문제상황에서도 작동되었다.) -->

※ 정리

1. db_recovery_file_dest_size 파라미터에서 설정한 값 이상으로 recovery-related files 파일(특히 아카이브)이 쌓였을때 ORA-00257에러가 발생할 수 있으며, 이 때는 sys를 제외한 일반 유저의 접근이 불가능하다.

2. db_recovery_file_dest_size 파라미터 크기를 늘려줌으로써 DB를 다시 정상적으로 운영가능하다.

3. ORA-00257과 같은 상황이 발생하지 않도록, 평소 백업영역 관리에 신경을 써야 한다.

(archiving 삭제 스케쥴러 지정 등)

더 생각해 볼만한 문제

(1) 아카이브 파일을 삭제했으나, 그 내용이 DB로 바로 전달되는 것 같지는 않다... 반영되기 까지 delay가 있는 것인가?

--> 4. Delete unnecessary files using RMAN DELETE command. If an operating

   system command was used to delete files, then use RMAN CROSSCHECK and

   DELETE EXPIRED commands.

==> 문제 확인: RMAN DELETE command를 사용하지 않았기 때문에 문제가 발생했을 것 같다.

(2) archive dest를 지정할 때, 여러 군데를 지정해서 운영가능한 것으로 알고 있다. 이 경우 해당 문제는 발생하지 않는가?

※ Oracle PL/SQL Programming 6판 기준입니다. (O'REILLY)

#Creating and Editing Source Code (22p)

What is unique about PL/SQL is the fact the the source code for stored programs must be loaded into the database before it can be compiled and executed.

--> PL/SQL은 다른 프로그램(C 등)과 달리, 데이터 베이스 안에서 compile되고 구동되어야 하기 때문에 일반 java program 등과 달리 version 관리 등에 고민해야 할 부분들이 발생한다.

#Starting Up SQL*Plus (24p)

3 ways to connect to a database with SQL*Plus

(1)

>sqlplus

Enter user-name : system

Enter password : ******

connect to (...)

SQL>

(2)

> sqlplus system/******

※do not recommend because of possibility of the password being leaked 

--> 어깨너머로 훔쳐 볼 수 도 있고, UNIX등 multi user 환경에서는 실제로 OS user가 수행한 명령어를 확인할 수 있다. 

(3)
> sqlplus /nolog

SQL> connect system/******

SQL> Connected.

--> nolog 구문을 이용하여 접속은 후에 맺는 식으로 패스워드 유출을 막을 수 있음.

#Running A PL/SQL Program (27p)

예제1)

begin

  dbms_output.put_line('Hey look, ma!');

end;

/

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

PL/SQL 프로시저가 성공적으로 완료되었습니다.

예제2)

set serveroutput on

begin

  dbms_output.put_line('Hey look, ma!');

end;

/         

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

PL/SQL 프로시저가 성공적으로 완료되었습니다.

Hey look, ma!

※기억해야 할 점

1. set serveroutput on 옵션을 켜야 PL/SQL 결과가 출력된다.

2. /(slash)를 반드시 입력해야 한다.

**    /(slash)의 역할 및 특징  **

To tell SQL*Plus that you're done entering a PL/SQL statement, you must usually include a trailing slash

1. sql*plus 상에서 / 는 '가장 최근에 입력된 문장을 수행하라'는 명령어이다. (PL/SQL, SQL 상관없이)

2. The slash is a command unique to SQL*Plus; it is not part of the PL/SQL language, nor is it part of SQL.

3. It must appear on a line by itself; no other commands can be included on the line.

4. Oracle 9i 이전의 버전은 / 앞, 뒤의 공백유무에 따라 수행이 안될수도 있다. (9i 이후버전은 앞뒤 공백 모두 상관 없다.)

예제3) BEGIN, END, / (slash) 입력없이 수행하기

execute dbms_output.put_line('Hey look, Ma!')

exec dbms_output.put_line('Hey look, Ma!!!!')

EXECUTE란 command를 통해 Begin, end, /를 모두 생략가능하다. 

1) semicolon (;)은 붙여도 되고 안붙여도 된다.

2) execute의 축약형으로 exec를 사용가능하다.

※ DECODE 함수의 특징

- 프로그래밍 언어의 IF문을 SQL, PL/SQL 내에서 구현하기 위해 만들어진 오라클 함수.

- FROM 절을 제외한 나머지 절 전체(SELECT, GROUP BY, WHERE 등등)에 사용가능. --> 동적으로 FROM절의 TABLE을 선택할 수는 없다는 의미.

- 중복된 DECODE사용이 가능

- 단독으로 쓰이기보단 SIGN, LEAST, GREATEST 등의 함수, SUM, MAX, MIN 등의 GROUP 함수와 많이 쓰인다.

- DECODE는 최소 3개의 인수만 받을 수 있으며, 이 경우 내부적으로 NULL을 출력하는 ELSE로 처리한다.

따라서 아래 i)번과 ii)번은 같다.

 예) 

i) IF A = B THEN

   RETURN 'T';

END IF;

==> DECODE(A,B,'T')

ii) IF A = B THEN

    RETURN 'T';

ELSE

    NULL

END IF;

==> DECODE(A,B,'T',NULL)

- 단순 상수뿐만 아니라 컬럼 간의 비교에도 유용하게 쓰일 수 있다.

예제) decode를 이용한 암호 생성

select to_char(sysdate,'DD')||decode(to_char(sysdate,'DAY'),'월요일','01','화요일','11','수요일','21','목요일','31','금요일','41','토요일','51','일요일','61') SEC_KEY  from dual;

예제) decode가 sign과 함께 쓰이는 경우

   ==> decode는 부등호를 이용한 대소비교를 할 수 없기 때문에, 필요한 경우 sign, least 등의 함수로 대소비교 처리가 가능하게끔 해준다.

 select lec_id,lec_time,lec_point,

        decode(sign(lec_time-lec_point),0,'일반과목',1,'실험과목',-1,'기타과목') as ty from lecture;

select lec_id, lec_time, lec_point, decode(sign(lec_time-lec_point),1,'실험과목','일반과목') from lecture;

select lec_id,lec_time,lec_point, decode(least(lec_time,lec_point),lec_time,'일반과목','실험과목') from lecture;

-- 중첩된 DECODE의 사용 (중첩된 IF)

-- 예제: DECODE(A,B,DECODE(A,C,'T','F'),'F')

desc lecture;

select lec_id, lec_time, lec_point from lecture;

select lec_id, lec_time, lec_point, decode(lec_time,lec_point,decode(lec_time,3,'중요과목')) from lecture;

-- 연습문제 Q/A : 04-2

질문: TEMP의 행을 3개씩 묶어 하나의 번호를 부여하고, 해당 ROWNUM을 3으로 나눈 나머지가 1, 2, 0 인 경우로 나누어 컬럼의 위치를 정해주는 SQL을 만들어라.

select rownum, ceil(rownum/3) "no+", decode(mod(rownum,3),1,'A',2,'B',0,'C') as G, emp_id, emp_name from temp;

select ceil(rownum/3) COL1, decode(mod(rownum,3),1,emp_id) COL2, decode(mod(rownum,3),1,emp_name) COL3, decode(mod(rownum,3),2,emp_id) COL4

, decode(mod(rownum,3),2,emp_name) COL5, decode(mod(rownum,3),0,emp_id) COL7, decode(mod(rownum,3),0,emp_name) COL8

from temp;

-- 연습문제 Q/A : 04-3

(발전형) TEMP의 자료를 이용하여 한 행에 사번, 성명을 3명씩 (총 6개 컬럼) 보여주는 SQL을 작성하라.

select emp_id, emp_name from temp;

select ceil(rownum/3) COL1,

        decode(mod(rownum,3),1,emp_id) COL2,

        decode(mod(rownum,3),1,emp_name) COL3, 

        decode(mod(rownum,3),2,emp_id) COL4, 

        decode(mod(rownum,3),2,emp_name) COL5, 

        decode(mod(rownum,3),0,emp_id) COL7, 

        decode(mod(rownum,3),0,emp_name) COL8

from temp;

==>

select ceil(rownum/3) COL1,

        max(decode(mod(rownum,3),1,emp_id)) COL2,

        max(decode(mod(rownum,3),1,emp_name)) COL3, 

        max(decode(mod(rownum,3),2,emp_id)) COL4, 

        max(decode(mod(rownum,3),2,emp_name)) COL5, 

        max(decode(mod(rownum,3),0,emp_id)) COL7, 

        max(decode(mod(rownum,3),0,emp_name)) COL8

from temp

where emp_id>0

group by ceil(rownum/3);

- group by 절을 이용해 3명씩 그룹을 지어준다.

1-1 ROWNUM

※ rownum의 속성에 대하여

-- rownum에 대해 알아 두어야 할 사실

첫째. 정의: rownum은 where절 (where절 없으면 from절)에 의해 추출된 DATA SET에 row 단위로 붙는 순번이다.

둘째. where절에 rownum을 조건으로 줄 때에는 항상 다른 조건을 모두 만족시킨 결과에 대해 조건이 반영된다.

셋째, order by 절을 사용할 때에는 우선 rownum이 부여되고 난 후 해당 결과 집합에 대해 sort를 하게 된다.

   (순서) rownum --> order by

① 단순 조회 시

 select rownum, emp_id, emp_name

  from temp

 where lev = '수습';

1 20000101 이태백

2 20000102 김설악

3 20000203 최오대

4 20000334 박지리

5 20000305 정북악

6 20006106 유도봉

7 20000407 윤주왕

8 20000308 강월악

9 20000119 장금강

10 20000210 나한라

--> 별도 정렬 없이 temp 자료 중에서 "lev=수습"인 값을 보여줌

② 단순 조회 + 조건 추가 (where emp_id >0) 

 select rownum, emp_id, emp_name

  from temp

 where emp_id >0

 and lev = '수습';

1 20000101 이태백

2 20000102 김설악

3 20000119 장금강

4 20000203 최오대

5 20000210 나한라

6 20000305 정북악

7 20000308 강월악

8 20000334 박지리

9 20000407 윤주왕

10 20006106 유도봉

where emp_id >0 조건이 추가되면서, 별도의 order by 절 없이도 emp_id 순으로 ascending 하여 자료가 출력됨.

③ rownum을 이용한 필터링 (and rownum < 5)

 select rownum, emp_id, emp_name

  from temp

 where emp_id >0

 and lev = '수습'

 and rownum < 5;

1 20000101 이태백

2 20000102 김설악

3 20000119 장금강

4 20000203 최오대

②번에서 출력된 자료에 and rownum < 5 라는 조건을 통해 emp_id 순으로 상위 4개 값만 출력(필터링)된 것을 알 수 있다.

!!주의!! rownum 조건을 이용한 필터링 시에는 <, <= 두 개 연산자 외에는 결과 값이 출력되지 않는다

*예외적으로 1과 비교할 때는 =(같다)로 비교가능하다.

④ rownum을 이용한 필터링 + order by 구문 추가

 select rownum, emp_id, emp_name

  from temp

 where emp_id >0

 and lev = '수습'

 and rownum < 5

 order by emp_name;

2 20000102 김설악

1 20000101 이태백

3 20000119 장금강

4 20000203 최오대

③번에 order by emp_name 절을 추가하였다. 이때 첫번째, ③번의 결과집합은 그대로 가져감. 둘째, ③번에서 확인했던 rownum은 바뀌지 않음을 알 수 있다.

즉, 'and rownum < 5' 구문으로 필터링 시 각 레코드에 대해 rownum 번호가 부여되며, 이후 order by를 이용한 sort가 이뤄졌단 사실을 알 수 있다.

연습문제 Q/A : 04-1 ROWNUM의 이용

 --> TEMP 테이블의 자료를 이용하여 SELECT 결과를 3개행씩 묶어 하나의 번호를 부여하는 SQL을 만들어보자. ROWNUM, 부여된번호, EMP_ID, EMP_NAME을 보여주면 된다.

나의답변)

 select rownum, trunc((rownum-1)/3)+1 as "부여된번호", emp_id, emp_name

 from temp;

모범답안)

 select rownum, ceil(rownum/3) as "부여된번호", emp_id, emp_name from temp;

* ceil : 지정된 값 이상의 가장 작은 정수를 리턴하는 함수

1-2 ROWID

※ ANY와 ALL

ANY와 ALL은 ①서브쿼리와 함께 쓰이거나 ②값들의 집합과 함께 쓰인다. + 비교 연산자와 함께

②값들의 집합

select * from temp

where salary > all (10000000,20000000,30000000,40000000);

그러나 대게는 ①서브쿼리를 사용할 때 쓰이는데, 만약 질의에 필요할 결과값 집합을 미리 알고 있다면, 굳이 ANY나 ALL연산자를 사용해야 할 이유가 없기 때문이다.

예제

<1-1 ANY>

select emp_id, emp_name, salary

from temp

where salary > any (select salary

                      from temp

                     where lev = '과장');

<1-2 MIN을 사용할 경우 -- 연관성 없는 서브쿼리>    

select emp_id, emp_name, salary

from temp

where salary > (select min(salary)

                      from temp

                     where lev = '과장');

<2-1 ALL>

select emp_id, emp_name, salary

from temp

where salary > all (select salary

                      from temp

                     where lev = '과장');

<2-2 ALL -- 연관성 없는 서브쿼리>                     

select emp_id, emp_name, salary

from temp

where salary > (select max(salary)

                      from temp

                     where lev = '과장');

뇌를 자극하는 오라클 프로그래밍(홍형경) 392p

<1. 서브쿼리 사용 시>

select emp.first_name||' '||emp.last_name as EMP_NAMES , emp.department_id,

          (select dep.department_name

              from departments dep

             where emp.department_id=dep.department_id) dep_name

             from employees emp;

<2. 단순 조인 사용 시>

select first_name||' '||last_name as EMP_NAMES , emp.department_id, dep.department_name

from employees emp, departments dep

where emp.department_id=dep.department_id(+);

서브쿼리를 사용한 1과 2는 같은 결과를 산출하지만, 성능면에서는 OUTER JOIN을 사용하지 않아도 되는 1번 문장이 더 나을 수있다. 만약 emp.department_id 값이 null 이 아닌 컬럼이 있을 경우, 아래 쿼리처럼 단순 EQUI JOIN을 사용해도 무방하다.

<2-1. 단순 조인 사용시 - EQUI JOIN>

select first_name||' '||last_name as EMP_NAMES , emp.department_id, dep.department_name

from employees emp JOIN departments dep

on emp.department_id = dep.department_id;

결국 설계 단계에서 테이블이 어떻게 정의될지와 사용될 쿼리에 대한 계획이 있어야 불필요한 성능 저하를 막을 수 있다.

-- Inner Query에서 Outer Query의 어떤 컬럼 값을 사용하는 경우를 일컬음

-- Inner Query = Sub Query, Outer Query = Main Query

-- 대게의 경우 Main Query가 Sub Query의 결과를 이용하기만 하는데, CORERELATED 서브쿼리의 경우 Sub Query도 Main Query의 값을 이용하게 됨

-- 주의: Correlated Sub query는 Main Query에서 하나의 레코드가 조건 절에서 비교되기 위해서 Inner Query 가 꼭 한번씩 수행되어야 하므로, Sub Query의 response time이 늦고 Main Query에서 처리될 record 수가 많은 경우 속도가 저하 될 수 있음 --> InLine View 등 다른 방법을 강구해야 함.

[따라하기]

-- 틀린 답 (조인 사용)

select a.emp_id, a.emp_name from

   temp a, (select lev, avg(salary) as a_sal

            from temp

            group by lev) b

            where a.lev = b.lev

              and a.salary > b.a_sal;

-- 옳은 답 (연관성 있는 서브 쿼리 사용)

select a.emp_id, a.emp_name

  from temp a

  where a.salary > (select avg(salary)

                      from temp b

                      where b.lev = a.lev);

[예제2] UPDATE 문에서의 활용

update tdept a

      set a.boss_id = (select min(b.emp_id)

                      from temp b

                      where b.dept_code =a.dept_code);

[Q/A : 03-13] INSERT문의 이용

-- 실행 안되는 문장

insert into tcom values (select '2002',emp_id, 0.1, 0.1 * salary from temp);

-- 실행 되는 문장

insert into tcom (work_year, emp_id, bonus_rate, comm)

           select '2002',emp_id, 0.1, 0.1 * salary from temp;

-- OUTER JOIN (오라클)

select a.emp_id, b.emp_id, b.comm, b.work_year

from temp a, tcom b

where b.work_year(+) = '2001'

and b.emp_id(+) = a.emp_id;

-- OUTER JOIN (ANSI 표준)

select a.emp_id, b.emp_id, b.comm

from temp a LEFT JOIN tcom b

ON a.emp_id = b.emp_id

and b.work_year = '2001';

-- EQUI JOIN (오라클)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a, emp_level b

where a.lev = b.lev;

-- EQUI JOIN (ANSI 표준)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a JOIN emp_level b

ON a.lev = b.lev;

-- OUTER JOIN (오라클)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a, emp_level b

where a.lev = b.lev(+);

-- LEFT OUTER JOIN (ANSI 표준)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a LEFT OUTER JOIN emp_level b

ON a.lev = b.lev;

-- JOINING TABLE ITSELT (SELF JOIN)

select * from tdept;

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a join tdept b

on a.parent_dept = b.dept_code;

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a, tdept b

where a.parent_dept = b.dept_code;

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a, tdept b

where a.parent_dept = b.dept_code

and b.dept_name = '영업';

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a join tdept b

on a.parent_dept = b.dept_code

where b.dept_name = '영업';

select * from temp;

-- non equi join (Q/A : 03-8번)

select emp_id, emp_name, birth_date from temp;

-- 직원이 자신보다 생일이 빠른 사람과 매칭했을 때 나타나는 경우의 수 (SELF JOIN, NON-EQUI JOIN)

select a.emp_id, a.emp_name, a.birth_date, b.emp_id, b.emp_name, b.birth_date

from temp a, temp b

where a.birth_date > b.birth_date(+);

-- 20 + ... + 1 = 210 개

-- 19 + ... + 1 = 190 개

-- 직원이 자신보다 생일이 빠른 사람과 매칭했을 때 나타나는 경우의 수2 

-- 단순히 숫자만 COUNT 시 (SELF JOIN, NON-EQUI JOIN)

-- 1. GROUP BY 를 쓴다

select a.emp_id, a.emp_name, a.birth_date, count(*)

from temp a, temp b

where a.birth_date >= b.birth_date

group by a.emp_id, a.emp_name, a.birth_date

order by count(*);

-- 2. 조금 더 조건을 명확히 나타내고 싶다면, >= 대신 OUTER JOIN을 함께 쓰고, null 이 나오는 컬럼을 count한다.

select a.emp_id, a.emp_name, a.birth_date, count(b.emp_id)

from temp a, temp b

where a.birth_date > b.birth_date(+)

group by a.emp_id, a.emp_name, a.birth_date

order by count(b.emp_id);

-- 3. 2번을 ANSI 표준 SQL로 바꾸기

select a.emp_id, a.emp_name, a.birth_date, count(b.emp_id)

from temp a LEFT OUTER JOIN temp b

  ON a.birth_date > b.birth_date

group by a.emp_id, a.emp_name, a.birth_date

order by count(b.emp_id);

-- Q/A : 03-9 #상위부서 찾기

select * from temp;

select * from tdept;

-- 1단계

select a.emp_id 사번, a.emp_name 성명, b.dept_code 부서코드, b.dept_name 부서명

from temp a, tdept b

where a.dept_code = b.dept_code;

select * from tdept;

select * from temp;

-- 2단계

select a.emp_id 사번, a.emp_name 성명, b.dept_code 부서코드, b.dept_name 부서명, c.dept_code 상위부서코드, c.dept_name 상위부서명, c.boss_id 상위부서장코드, d.emp_name 상위부서장명

from temp a, tdept b, tdept c, temp d

where b.parent_dept = 'CA0001'

  and a.dept_code = b.dept_code

  and b.parent_dept = c.dept_code

  and c.boss_id = d.emp_id(+);

-- 2단계 (ANSI 표준--> 다중 테이블 조인 연습)

select a.emp_id 사번, a.emp_name 성명, b.dept_code 부서코드, b.dept_name 부서명, c.dept_code 상위부서코드, c.dept_name 상위부서명, c.boss_id 상위부서장코드, d.emp_name 상위부서장명

from temp a JOIN tdept b

  ON (a.dept_code = b.dept_code and b.parent_dept = 'CA0001') 

     JOIN  tdept c

  ON (b.parent_dept = c.dept_code)

     LEFT OUTER JOIN temp d

  ON c.boss_id = d.emp_id;

     --WHERE b.parent_dept = 'CA0001';

     --> 참고: ANSI 표준 SQL에서 where 절로 필터링 하고 싶을땐, 맨 밑에 where 절을 쓰거나 혹은 JOIN 시 ON 구문안에 and 절로 표시한다. 단 ON 절에 넣는 것은 데이터 검증 조건을 추가하는 것이며, 단순 검색 조건 추가일 경우 WHERE절을 쓸 것을 권고한다. (SQL 전문가 가이드 316p 참조)

-- 참조 부서장을 맡고 있는 부서원 및 해당 부서의 상위부서코드/명/부서장코드/부서장명   

select a.emp_id 사번, a.emp_name 부서장명, b.dept_name 부서명, b.parent_dept 상위부서코드, c.dept_name 상위부서명, d.emp_id 상위부서장코드, d.emp_name 상위부서장명 

from temp a, tdept b, tdept c, temp d

where a.emp_id = b.boss_id

  and b.parent_dept=c.dept_code

  and c.boss_id = d.emp_id(+);

-- 참고 자료

select * from temp;

19970101 김길동 74/01/25 AA0001 정규 Y 등산 100000000 부장

19960101 홍길동 73/03/22 AB0001 정규 Y 낚시 72000000 과장

19970201 박문수 75/04/15 AC0001 정규 Y 바둑 50000000 과장

19930331 정도령 76/05/25 BA0001 정규 Y 노래 70000000 차장

19950303 이순신 73/06/15 BB0001 정규 Y 56000000 대리

19966102 지문덕 72/07/05 BC0001 정규 Y 45000000 과장

19930402 강감찬 72/08/15 CA0001 정규 Y 64000000 차장

19960303 설까치 71/09/25 CB0001 정규 Y 35000000 사원

19970112 연흥부 76/11/05 CC0001 정규 Y 45000000 대리

19960212 배뱅이 72/12/15 CD0001 정규 Y 39000000 과장

*빈 값은 모두 NULL

-- 그룹함수          

-- (1)COUNT           

select count(*), count(8) from temp; -- 10, 10 

select count(*), count(8) from dual; -- 1,1 

--> *나 의미없는 값을 넣으면 row수만큼 읽어온다

select * from temp order by lev, hobby;

select count(emp_id), count(tel), count(hobby) from temp; -- 10, 0, 4

--> count() 괄호 안에 컬럼을 넣으면, 해당 컬럼이 null이 아닌 레코드의 개수를 읽어온다

select lev, 

count(*),  

count(hobby)

from temp

group by lev

order by lev;

-- 과장 4 2

-- 대리 2 0

-- 부장 1 1

-- 사원 1 0

-- 차장 2 1

--참고. GROUP 함수의 특징

select count(*) from temp

where hobby = '공부';

-- 또는

select count(hobby) from temp

where hobby = '공부';

-- count(*)

----------

-- 0

-- 1 row selected. --> 0 rows selected 가 아님. 해당 1개의 그룹(*group by 절이 없으므로 레코드 전체가 하나의 그룹)에 대해 1건의 결과를 출력하며, 그 그룹 중 '공부'가 취미인 레코드가 0개라는 1건의 결과를 출력함

select count(*)

from temp

where lev = '과장'

and salary >= 40000000;

select count(distinct lev) from temp;

-- min과 max

select min(emp_id) from temp;

select lev, min(emp_id)

from temp

group by lev;

-- 연습문제 그룹함수 연습

TEMP에서 직급별로 최소연봉을 가진 직원의 사번과 연봉을 읽어오자. (단,SUBQUERY를 사용하면 안 된다.)

select to_char(salary) from temp;

select lpad(to_char(salary),10,'0') from temp;

select min(lpad(to_char(salary),10,'0')) from temp;

select substr(min(lpad(to_char(salary),10,'0')||emp_id),11,8) EMP_ID from temp;

select lev, substr(min(lpad(to_char(salary),10,'0')||emp_id),11,8) EMP_ID, min(salary) SAL

from temp

group by lev;

-- SUM, AVG

-- 주의 : number type 컬럼만 인수로 받을 수 있다

-- 따라하기

select lev, count(*), sum(salary), avg(salary), sum(salary)/count(*) AVG2

from temp

group by lev;

-- 주의! 평균을 구할때는 NULL 값을 유의해야 한다.

--실험

select avg(salary) from temp; --57600000

update temp set salary = 0 where emp_name = '홍길동';

select count(salary), avg(salary), sum(salary)/count(*) AVG2 from temp; -- 10, 50400000, 50400000

update temp set salary = null where emp_name = '홍길동';

select count(*), count(salary), avg(salary), sum(salary), sum(salary)/count(*) AVG2 from temp; -- 10, 9, 56000000, 504000000, 50400000

--> 결국 avg(salary)= sum(salary)/count(salary) 로 구해진다는 것 (홍길동을 제외한 나머지 9명의 연봉 평균이 구해졌다.)

--> 전체 10명의 평균을 구하기 위해서는 nvl 함수를 사용해줘야 한다.

select avg(nvl(salary,0)) from temp; --50400000

rollback;