반응형

plsql developer 편의기능집합



sql 툴을 사용할 때

편하게 사용할 수 있는 세팅을

미리 작업해놓거나

편의기능들을 알아두어

개발할 땐 집중해서 일할 수 있는

환경을 만들어놓고자 정리해놓습니다.



#plsql developer 세미콜론 단위로 실행

plsql 실행 시 블럭지정 후 실행해야 하는 불편함이 존재한다.

세미콜론 단위로 실행할 수 있도록 아래 옵션을 체크한다.

Tool > Preferences > preferences > Window Types > SQL Window > AuthSelect statement





#라인번호 표시

Tool > Preferences > preferences > Window Types > SQL Window > Show gutter ( line numbers) 체크





#null 값 색깔 표시

Tool > Preferences > preferences > Window Types > SQL Window > Null value cell color 에서 색 선택





#결과값 색상 번갈아가며 표시하지 않기

Tool > Preferences > preferences > Window Types > SQL Window > Alternate row color 의 Enabled 체크 해제








#sql 결과갯수제한

3가지 옵션이 있는데 주로 Fixed 로 100 정도 설정해놓습니다.

전체 데이터를 확인할 일은 생각보다 많이 없고 필요하면 추가조회하면 됩니다.


Tool > Preferences > preferences > Window Types > SQL Window > Records per Page




#연결 유지

Tool > Preferences > preferences > Oracle > Connection > Check connection 에 체크





#접속정보 저장

Tool > Preferences > preferences > Oracle > Logon History > Definition > Store with password 체크





#sql 실행내역 / sql history / sql 로그 확인

Edit > Recall Statement ( 단축키 Ctrl + E )


반응형
블로그 이미지

나남나여

일상 제품리뷰와 맛집/여행/사진을 좋아하고 IT 관련 프로그래밍 초급 & 고급 정보를 공유하는 블로그

,
반응형

오라클 최근 생성된 테이블, 뷰 확인




시스템운영 또는 프로젝트 진행중

테이블이나 뷰 생성 시

공유하지 않는다면 알 수 없습니다.



테이블 생성시간

테이블 컬럼변경 확인

테이블 변경여부 확인


위의 내용을 ALL_OBJECTS 테이블에서 조회하면

테이블이나 뷰의 신규/변경된 내역을 확인할 수 있습니다.


( DBA_OBJECTS = ALL_OBJECTS )


응용


최근 생성된 테이블 확인


SELECT OWNER
     , OBJECT_NAME
     , SUBOBJECT_NAME
     , OBJECT_TYPE
     , CREATED
     , LAST_DDL_TIME
     , TIMESTAMP
     , STATUS
     , TEMPORARY
  FROM ALL_OBJECTS 
 ORDER BY CREATED DESC ;



[결과]


OWNER

OBJECT_NAME

SUBOBJECT_NAME

OBJECT_TYPE

CREATED

LAST_DDL_TIME

TIMESTAMP

STATUS

TEMPORARY

201102 SP_PARAM_SELECT PROCEDURE 17/11/09 17/11/09 2017-11-09:15:55:54 VALID N
201102

DAILY_SALE

PR_DAILY_SALE_2016

TABLE 17/11/09 17/11/09 2017-11-09:15:49:36 VALID N
201102

JOB_PARA

TABLE 17/11/09 17/11/09 2017-11-09:15:41:59 VALID N





최근 변경된 테이블 확인


SELECT OWNER
     , OBJECT_NAME
     , SUBOBJECT_NAME
     , OBJECT_TYPE
     , CREATED
     , LAST_DDL_TIME
     , TIMESTAMP
     , STATUS
     , TEMPORARY
  FROM ALL_OBJECTS 
 ORDER BY LAST_DDL_TIME DESC ;



[결과]


OWNER

OBJECT_NAME

SUBOBJECT_NAME

OBJECT_TYPE

CREATED

LAST_DDL_TIME

TIMESTAMP

STATUS

TEMPORARY

201102

DAILY_SALE

PR_DAILY_SALE_2016 TABLE 17/11/09 17/11/09 2017-11-09:15:49:36 VALID N
201102

SP_PARAM_SELECT

PROCEDURE 17/11/09 17/11/09 2017-11-09:15:55:54 VALID N
201102

JOB_PARA

TABLE 17/11/09 17/11/09 2017-11-09:15:41:59 VALID N


oracle 11g 기준 ALL_OBJECT column comments



 컬럼명

컬럼 COMMENT 

ONWER

object 의 소유자

OBJECT_NAME

object 이름

SUBOBJECT_NAME

하위 object 이름 ( ex  파티션명 )

OBJECT_ID

object 번호

DATA_OBJECT_ID

data object 번호

OBJECT_TYPE

object 타입 ( table, index, view, function, procedure, partition, index partition 등 )

CREATED

object 생성시간

LAST_DDL_TIME

DDL 문( grant 와 revoke 포함 ) 으로 인한 마지막 수정시간 

TIMESTAMP

view나 package의 정의가 변경된 시간
( ex : 뷰의 컬럼갯수가 변경된 경우 ( view 의 select 문장이 변경되는것과 상관없음) )

TEMPORARY

temporary 객체로 만든 테이블인지 여부
( ex : create temporary table tb_emp ( empno varchar2(10 byte) ) )

GENERATED

object name이 시스템에 의해 생성되었는지 여부

SECONDARY

오라클 데이터 카트리지의 ODCIIndexCreate 메서드에 의해 생성된 부가적인 object인지 여부

NAMESPACE

object 의 네임스페이스

EDITION_NAME

향후 사용을 위한 예약 컬럼




ALL_OBJECTS vs USER_OBJECT 차이


USER_OBJECT : 로그인유저의 객체조회

ALL_OBJECT  : 전체 owner 의 객체조회 ( owner 컬럼을 사용하여 구분가능 )


ALL_OBJECTS view 에 권한이 없다면

USER_OBJECT 로 확인 가능합니다.

반응형
블로그 이미지

나남나여

일상 제품리뷰와 맛집/여행/사진을 좋아하고 IT 관련 프로그래밍 초급 & 고급 정보를 공유하는 블로그

,
반응형

오라클 자주발생하는 에러 및 해결







오라클 자주 발생하는 에러모음이며

추가적인 오류 발생 및 해결방안/예측가능한 방법이

생길 시 주기적으로 정리하여 업데이트 예정입니다.








오류 순서 ( 오류코드를 ctrl + F 로 찾아서 원하는 내용 확인하시면 편합니다. )


ORA-00001: 무결성 제약 조건(USERS.EMP)에 위배됩니다.

ORA-00904: "EMPNOA": 부적합한 식별자

ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

ORA-01400: NULL을 ("SCOTT"."EMP"."EMPNO") 안에 삽입할 수 없습니다.

ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다

ORA-01555: 너무 이전 스냅샷:롤백 세그먼트 11수에 "_SYSSMU11_1602219420$" 이름으로 된 것이 너무 작습니다

ORA-14016: LOCAL 분할영역 인덱스로 강조된 테이블은 분할영역이 되어 있어야 합니다

ORA-12899: "SCOTT"."EMP"."ENAME" 열에 대한 값이 너무 큼(실제: 13, 최대값: 10)

ORA-30926: 원본 테이블의 고정 행 집합을 가져올 수 없습니다









오류코드 ( ORA-00001 )

ORA-00001: 무결성 제약 조건(USERS.EMP)에 위배됩니다.


간단하지만 자주 발생하는 오류로 PK 가 설정되어 있을 때 
중복된 값이 입력된 경우






EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7369 SMITH CLERK 7902 1980-12-17 800   20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30



원인

1. EMPNO 가 Primary Key 로 설정되어 있을 때 같은 값을 입력할 경우 key 중복오류가 발생합니다.

2. 다수의 건을 적재 시 Primary Key 값이 동일한 row 가 여러건이 아닌지 확인이 필요합니다.











오류코드 ( ORA-00904 )


ORA-00904: "EMPNOA": 부적합한 식별자



SQL 실행


SELECT EMPNOA FROM EMP ;



원인


1. 테이블에 없는 컬럼을 조회했을 경우 발생합니다.

2. 오탈자가 원인인 경우가 많으니 잘 살펴봅니다.

3. 정상적으로 사용중인 테이블에서 발생하는 경우 컬럼이 DROP 되었을 수 있으니 테이블을 다시 확인해봅니다.



해결


1. 오탈자 확인 후 수정합니다.

2. 컬럼이 DROP 된 경우 ADD , 또는 SELECT 문에서 같이 제외합니다.









오류코드 ( ORA-00942 )

ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

( table or view does not exist )



원인

1. FROM 절에 테이블명이 잘못 입력된 경우

2. 테이블은 존재하지만 해당 스키마에 권한이 없는 경우

   ( GRANT 로 권한을 부여하여 해결합니다. ( GRANT 권한 부여 방법 ) )


해결

대부분의 오류는 이 두개에서 자주 발생하게 되니 오탈자가 없는지 먼저 확인해보시길 바랍니다.









오류코드 ( ORA-01400 )


ORA-01400: NULL을 ("SCOTT"."EMP"."EMPNO") 안에 삽입할 수 없습니다.



SQL 실행


INSERT INTO EMP (ENAME) VALUES ('James Cameron') ;




원인


Primary Key 가 존재하는 테이블인데 PK 에 null 이 입력되어 발생하는 오류입니다.




해결


테이블의 PK 가 EMPNO 로 구성되어 있으므로, 해당컬럼에 값을 입력하여 정상적인 ROW 로 생성합니다.









오류코드 ( ORA-01438 )


ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.



SQL 실행


INSERT INTO EMP (EMPNO) VALUES (12345) ;



원인


입력하려는 컬럼의 자릿수가 NUMBER(4,0) 인데 4 자리를 초과하여 입력하여 오류가 발생하였습니다.




해결


예상치 못한 값이 입력되었을 수 있으니 입력하려는 값의 확인이 필요합니다.









오류코드 ( ORA-01555 )


ORA-01555: 너무 이전 스냅샷:롤백 세그먼트 11수에 "_SYSSMU11_1602219420$" 이름으로 된 것이 너무 작습니다



SQL 실행


INSERT INTO EMP
SELECT EMPNO FROM CUSTOMER



원인


대용량 테이블에서 발생할 가능성이 매우 높음.


위의 SQL 실행을 예로 들면

CUSTOMER 테이블을 조회 하여 EMP 테이블에 데이터를 INSERT 하는 SQL 입니다.


SQL 실행시간이 약 10분 걸리는데

그 사이 CUSTOMER 테이블의 데이터가 변경된 경우

오류가 발생할 수 있습니다.




해결


1. INSERT 도중 SELECT 하는 데이터에 변화를 일으키지 않도록 합니다.

2. SQL 튜닝을 통해 실행시간을 줄여 데이터를 입력하도록 합니다.










오류코드 ( ORA-14016 )

ORA-14016: LOCAL 분할영역 인덱스로 강조된 테이블은 분할영역이 되어 있어야 합니다.

( underlying table of a LOCAL partitioned idnex must be partitioned )



SQL 실행

CREATE UNIQUE INDEX PK_TB_USER_INFO ON TB_USER_INFO ( USER_NO )
TABLESPACE USERS LOCAL ;



원인

파티션 테이블이 아닌 일반 테이블에서 INDEX 생성 시 LOCAL 영역으로 지정하려고 하니 오류가 발생하였습니다.




해결

CREATE UNIQUE INDEX PK_TB_USER_INFO ON TB_USER_INFO ( USER_NO )
TABLESPACE USERS ;









오류코드 ( ORA-12899 )


ORA-12899: "SCOTT"."EMP"."ENAME" 열에 대한 값이 너무 큼(실제: 13, 최대값: 10)



SQL 실행


INSERT INTO EMP (EMPNO, ENAME ) VALUES (1111, 'James Cameron') ;



원인


오류메세지를 보면 알 수 있듯 ENAME 의 지정된 컬럼 길이는 VARCHAR2(10 BYTE) 로 설정되어 있으나,

실제입력한 컬럼의 값은 13 BYTE 로 입력되어 오류가 발생하였습니다.



해결


1. 컬럼사이즈를 증가시켜줍니다.

2. 입력되는 값을 줄여서 입력합니다.









오류코드 ( ORA-30926 )



ORA-30926: 원본 테이블의 고정 행 집합을 가져올 수 없습니다

unable to get a stable set of rows in the source tables



SQL 실행



MERGE INTO SCOTT.EMP T1
USING
(
    SELECT DEPTNO
      FROM SCOTT.DEPT
     WHERE DNAME LIKE '%SALES%'
) T2
ON
(T1.DEPTNO = T2.DEPTNO)
WHEN MATCHED THEN
UPDATE
SET T1.SAL = '3000'



원인


일단 말도 안되는 문장이지만 예를 들면 부서가 SALES 라는 명칭이 들어간 부서는 급여를 3000 으로 변경하는 쿼리입니다.


USING 안의 구분에서 실행한 결과가 




DEPTNO

DNAME

30

SALES

30

SALES



위처럼 출력되었다면 데이터가 2건이상 발생하였고

ON 절에서 JOIN 으로 사용하는 DEPTNO 가 2건이상이 발생한 경우 오류가 발생할 수 있습니다.




해결



1. USING 절에서 사용되는 문장이 KEY 당 1건만 조회되도록 SQL 을 작성합니다. ( EX : GROUP or DISTINCT 이용 )

2. ON 절에서 KEY 값으로 JOIN 이 잘 되었는지 확인합니다.

반응형
블로그 이미지

나남나여

일상 제품리뷰와 맛집/여행/사진을 좋아하고 IT 관련 프로그래밍 초급 & 고급 정보를 공유하는 블로그

,
반응형

 

ORACLE RANK

 


RANK 함수는 일반적으로 매출, 영업, 성적 등의 순위를 표현할 때 사용됩니다.


어렵지 않지만 자주 사용되는 함수이니 사용법을 잘 숙지해두시면


평소 업무도 뚝딱 해결할 수 있으니 같이 공유해요^^







ORACLE 에서 제공하는 RANK 문법은 아래와 같습니다.

 

 


RANK 문법

 


 





RANK 함수 사용하는 방법은

아래의 EMP 테이블을 예제로 설명드리겠습니다.



잠깐!

간단한 질문과 결과를 올려놓았으니 

잠시 생각하시고 SQL 문을 확인하시면 조금 더 도움이 되실꺼에요!



 

EMPNO 

ENAME 

JOB 

MGR 

HIREDATE 

SAL 

COMM 

DEPTNO 

7369

SMITH

CLERK

7902

1980-12-17

800

(null)

20

7499

ALLEN

SALESMAN

7698

1981-02-20

1600

300

30

7521

WARD

SALESMAN

7698

1981-02-22

1250

500

30

7566

JONES

MANAGER

7839

1981-04-02

2975

(null)

20

7654

MARTIN

SALESMAN

7698

1981-09-28

1250

1400

30

7698

BLAKE

MANAGER

7839

1981-05-01

2850

(null)

30

7782

CLARK

MANAGER

7839

1981-06-09

2450

(null)

10

7788

SCOTT

ANALYST

7566

1987-04-19

3000

(null)

20

7839

KING

PRESIDENT

(null)

1981-11-17

5000

(null)

10

7844

TURNER

SALESMAN

7698

1981-09-08

1500

0

30

7876

ADAMS

CLERK

7788

1987-05-23

1100

(null)

20

7900

JAMES

CLERK

7698

1981-12-03

950

(null)

30

7902

FORD

ANALYST

7566

1981-12-03

3000

(null)

20

7934

MILLER

CLERK

7782

1982-01-23

1300

(null)

10

 

 


자 그럼 기본적인 사용법부터 확인해볼까요?







RANK()


1. 직원의 급여가 높은 순서대로 순위를 아래와 같이 조회해보세요.

  ( 항목 추출 : EMPNO, ENAME, SAL, 순위 )


< 결과 >


EMPNO

ENAME

SAL

RK

7839

KING

5000

1

7902

FORD

3000

2

7788

SCOTT

3000

2

7566

JONES

2975

4

7698

BLAKE

2850

5

7782

CLARK

2450

6

7499

ALLEN

1600

7

7844

TURNER

1500

8

7934

MILLER

1300

9

7521

WARD

1250

10

7654

MARTIN

1250

10

7876

ADAMS

1100

12

7900

JAMES

950

13

7369

SMITH

800

14









DENSE_RANK()


2,3번째 row를 보면 FORD 와 SCOTT 은 같은급여를 받고 있습니다.

다음 순위의 급여를 받는 JONES의 순위가 3위가 아니라 4위가 되었습니다.

3위로 출력하고 싶은 경우는 어떻게 할까요?



2. 순위의 공백이 없도록 DENSE_RANK 를 사용하여 조회해보세요.



< 결과 >


EMPNO

ENAME

SAL

RK

7839

KING

5000

1

7902

FORD

3000

2

7788

SCOTT

3000

2

7566

JONES

2975

3

7698

BLAKE

2850

4

7782

CLARK

2450

5

7499

ALLEN

1600

6

7844

TURNER

1500

7

7934

MILLER

1300

8

7521

WARD

1250

9

7654

MARTIN

1250

9

7876

ADAMS

1100

10

7900

JAMES

950

11

7369

SMITH

800

12








ROW_NUMBER()


동일한 급여에도 순위가 나뉘도록 조회해보겠습니다.


3. 동일한 순위를 인정하지 않도록 조회해보세요.



< 결과 >


EMPNO

ENAME

SAL

RK

7839

KING

5000

1

7902

FORD

3000

2

7788

SCOTT

3000

3

7566

JONES

2975

4

7698

BLAKE

2850

5

7782

CLARK

2450

6

7499

ALLEN

1600

7

7844

TURNER

1500

8

7934

MILLER

1300

9

7521

WARD

1250

10

7654

MARTIN

1250

11

7876

ADAMS

1100

12

7900

JAMES

950

13

7369

SMITH

800

14











자, 위와 같이 조회하면 어떤 기준으로 2등과 3등이 나뉜지 알 수 없습니다.

"2등과 3등의 차이가 뭐야?" 라고 물어본다면 대답할 수 없으니

우리는 명시적으로 그 차이를 지정하는 기준을 만들어볼께요!



4. 급여는 같지만 입사일이 빠른경우를 상위순위로 배치하도록 조회해보세요.


순위를 산정하는 방식이 2가지 인것을 생각해보면 쉬워요~ ( 급여, 입사일 )



< 결과 >


EMPNO

ENAME

SAL

HIREDATE

RK

7839

KING

5000

1981-11-17

1

7902

FORD

3000

1981-12-03

2

7788

SCOTT

3000

1987-04-19

3

7566

JONES

2975

1981-04-02

4

7698

BLAKE

2850

1981-05-01

5

7782

CLARK

2450

1981-06-09

6

7499

ALLEN

1600

1981-02-20

7

7844

TURNER

1500

1981-09-08

8

7934

MILLER

1300

1982-01-23

9

7521

WARD

1250

1981-02-22

10

7654

MARTIN

1250

1981-09-28

11

7876

ADAMS

1100

1987-05-23

12

7900

JAMES

950

1981-12-03

13

7369

SMITH

800

1980-12-17

14






여기까지 보셨다면 기본적인 RANK 의 사용법을 알게 되었습니다.

아래부터는 RANK 함수를 응용한 내용을 공유해보겠습니다.







RANK 응용1


 - 급여가 가장 높은 사람이 누구인지 1명만 조회하세요.


  ( 지금까지는 모든 사용자들을 전부 조회했지만 이번엔 1명만 조회되었네요! )


< 결과 >


ENAME

KING








위와 같은 결과지만 좀 더 쉽게 조회 할 수 있는 방법도 있습니다.

바로 오라클의 KEEP / FIRST / LAST 문법을 사용하는것인데 MAX 값을 조회 후

다른 컬럼의 값을 바로 조회할 수 있습니다!






조금 더 자세한 내용을 알고 싶다면 FIRST, LAST, KEEP 포스트를 확인해주세요~ ( 바로확인하기 )



RANK 응용2


 - 총급여가 많은 부서별 순위를 조회하세요.


부서별로 급여의 순위는 단순하게 처리가 가능합니다.

부서로 GROUP BY 후 ORDER BY 에서 지정한 정렬컬럼을 SUM 처리하면 됩니다.



< 결과 >


DEPTNO

DNAME

SAL

RK

20

RESEARCH

10875

1

30

SALES

9400

2

10

ACCOUNTING

8750

3







RANK 응용3


 - 부서별로 급여를 가장 많이 받는 사람을 조회하세요.



RANK 함수를 사용할 때 PARTITION BY 를 사용하면 해당 컬럼을 파티션으로 묶어 순위를 부여할 수 있습니다.



< 결과 >


DEPTNO

DNAME

EMPNO

ENAME

SAL

RK

10

ACCOUNTING

7839

KING

5000

1

10

ACCOUNTING

7782

CLARK

2450

2

10

ACCOUNTING

7934

MILLER

1300

3

20

RESEARCH

7902

FORD

3000

1

20

RESEARCH

7788

SCOTT

3000

1

20

RESEARCH

7566

JONES

2975

3

20

RESEARCH

7876

ADAMS

1100

4

20

RESEARCH

7369

SMITH

800

5

30

SALES

7698

BLAKE

2850

1

30

SALES

7499

ALLEN

1600

2

30

SALES

7844

TURNER

1500

3

30

SALES

7521

WARD

1250

4

30

SALES

7654

MARTIN

1250

4

30

SALES

7900

JAMES

950

6






이상으로 오라클 RANK 함수에 대해 알아보았습니다.


도움이 되셨거나 추가질문이 있다면 댓글 남겨주세요~^^



반응형
블로그 이미지

나남나여

일상 제품리뷰와 맛집/여행/사진을 좋아하고 IT 관련 프로그래밍 초급 & 고급 정보를 공유하는 블로그

,
반응형

오라클 KEEP


KEEP 단어 자체의 의미를 보면

"유지하다" 라는 의미를 가지고 있습니다.




오라클에서 사용하는 KEEP 은 단독으로 사용하기 보다는

FIRST , LAST 와 함께 사용하게 되는데


정렬을 통해 첫번째나 마지막에 나온 row 를 KEEP 해놓고 ( 유지 / 보관해놓고 )

해당 row 의 다른 컬럼을 사용한다고 생각하면 쉽게 사용할 수 있습니다.



다만 어떤 기준으로 추출하는지 명시적으로 사용할 수 있는 장점이 있습니다.






잠깐 예를 들어볼까요?

어느 회사에서 "부서별로 가장 높은 급여를 받는 사람이 얼마지?" 라고 하면

단순히 부서별 GROUP BY 후 MAX 값으로 추출하면 되겠죠?


그런데 "부서별로 가장 오래 근무한 사람의 급여가 얼마지?" 라고 하면

부서별 입사일이 가장 빠른 사람을 추출 후 급여를 찾아야 해서 서브쿼리나 다른 방법으로 쿼리를 해야겠죠?





하지만 KEEP 문법을 사용하면 한번에 찾을수 있더라고요!

그럼 샘플 데이터를 통해서 확인해볼께요!



샘플데이터


EMPNO 

ENAME 

JOB 

MGR 

HIREDATE 

SAL 

COMM 

DEPTNO 

7369

SMITH

CLERK

7902

1980-12-17

800

(null)

20

7499

ALLEN

SALESMAN

7698

1981-02-20

1600

300

30

7521

WARD

SALESMAN

7698

1981-02-22

1250

500

30

7566

JONES

MANAGER

7839

1981-04-02

2975

(null)

20

7654

MARTIN

SALESMAN

7698

1981-09-28

1250

1400

30

7698

BLAKE

MANAGER

7839

1981-05-01

2850

(null)

30

7782

CLARK

MANAGER

7839

1981-06-09

2450

(null)

10

7788

SCOTT

ANALYST

7566

1987-04-19

3000

(null)

20

7839

KING

PRESIDENT

(null)

1981-11-17

5000

(null)

10

7844

TURNER

SALESMAN

7698

1981-09-08

1500

0

30

7876

ADAMS

CLERK

7788

1987-05-23

1100

(null)

20

7900

JAMES

CLERK

7698

1981-12-03

950

(null)

30

7902

FORD

ANALYST

7566

1981-12-03

3000

(null)

20

7934

MILLER

CLERK

7782

1982-01-23

1300

(null)

10

 

 


FIRST 와 LAST 는 비슷하게 사용할 수 있는데

집계함수나 분석함수로 조회된 ROW 에 대해 동작할 수 있습니다.






FIRST나 LAST 를 사용하면

HIREDATE 라는 특정 컬럼을 기준으로 정렬해야 하지만 필요한 값이 HIREDATE 가 아닌 경우

FIRST 나 LAST 함수를 사용하면 SELF JOIN 이나 VIEW 가 필요 없이 더 좋은 성능으로 사용할 수 있습니다.



SELECT DEPTNO
     , MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY HIREDATE ) AS JOIN_FIRST_SAL
     , MAX(SAL) KEEP ( DENSE_RANK LAST  ORDER BY HIREDATE ) AS JOIN_LAST_SAL
  FROM EMP
 GROUP BY DEPTNO


결과


DEPTNO

JOIN_FIRST_SAL

JOIN_LAST_SAL

10

2450

1300

20

800

1100

30

1600

950



위처럼 입사일(HIREDATE)을 기준으로


입사일이 오래된 순으로 정렬(FIRST)할 것인지

최근 입사한 순으로 정렬(LAST)할 것인지 정렬 후

집계함수(MAX, MIN)를 사용하면 집계함수에서 조회 된

첫번째나 마지막에 출력된 row 에 존재하는 급여(SAL)컬럼을 사용 할 수 있습니다.







CAUTION

하지만, FIRST 나 LAST 를 사용 시 주의 할 점이 있는데

바로, 정렬 시 null 이 존재하는 경우입니다.



정렬을 FIRST 로 입력 시 null 값은 기본적으로 맨 처음에 조회되기 때문에

의도된 바와 다른 결과를 조회할 수 있는데 아래 예를 확인해보겠습니다.


SELECT DEPTNO , MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY MGR NULLS FIRST ) AS MGR_NULLS_FST , MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY MGR NULLS LAST ) AS MGR_NULLS_LST FROM EMP GROUP BY DEPTNO


결과


DEPTNO

MGR_NULLS_FST

MGR_NULLS_LST

10

5000

1300

20

3000

3000

30

950

950


같은 문법이지만 NULLS FIRST 로 사용했느냐 NULLS LAST 로 사용했느냐에 따라

처리결과가 다르게 조회된 것을 확인할 수 있습니다.


NULLS FIRST 와 NULLS LAST 는 null 값이 존재할 때

null 을 앞에 조회되게 할 지, 뒤에 조회되게 할 지를 명시하는 문장이라고 보시면 됩니다.








같은 사용방법으로 맨 하단 ORDER BY 문장에도 사용할 수 있어요


SELECT *
   FROM EMP
  ORDER BY MGR NULLS LAST>


결과


EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7902

FORD

ANALYST

7566

1981-12-03

3000

(null)

20

7788

SCOTT

ANALYST

7566

1987-04-19

3000

(null)

20

7900

JAMES

CLERK

7698

1981-12-03

950

(null)

30

7499

ALLEN

SALESMAN

7698

1981-02-20

1600

300

30

7521

WARD

SALESMAN

7698

1981-02-22

1250

500

30

7844

TURNER

SALESMAN

7698

1981-09-08

1500

0

30

7654

MARTIN

SALESMAN

7698

1981-09-28

1250

1400

30

7934

MILLER

CLERK

7782

1982-01-23

1300

(null)

10

7876

ADAMS

CLERK

7788

1987-05-23

1100

(null)

20

7698

BLAKE

MANAGER

7839

1981-05-01

2850

(null)

30

7566

JONES

MANAGER

7839

1981-04-02

2975

(null)

20

7782

CLARK

MANAGER

7839

1981-06-09

2450

(null)

10

7369

SMITH

CLERK

7902

1980-12-17

800

(null)

20

7839

KING

PRESIDENT

(null)

1981-11-17

5000

(null)

10



SELECT *
   FROM EMP
  ORDER BY MGR NULLS FIRST


결과


EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7839

KING

PRESIDENT

(null)

1981-11-17

5000

(null)

10

7788

SCOTT

ANALYST

7566

1987-04-19

3000

(null)

20

7902

FORD

ANALYST

7566

1981-12-03

3000

(null)

20

7499

ALLEN

SALESMAN

7698

1981-02-20

1600

300

30

7844

TURNER

SALESMAN

7698

1981-09-08

1500

0

30

7521

WARD

SALESMAN

7698

1981-02-22

1250

500

30

7900

JAMES

CLERK

7698

1981-12-03

950

(null)

30

7654

MARTIN

SALESMAN

7698

1981-09-28

1250

1400

30

7934

MILLER

CLERK

7782

1982-01-23

1300

(null)

10

7876

ADAMS

CLERK

7788

1987-05-23

1100

(null)

20

7698

BLAKE

MANAGER

7839

1981-05-01

2850

(null)

30

7566

JONES

MANAGER

7839

1981-04-02

2975

(null)

20

7782

CLARK

MANAGER

7839

1981-06-09

2450

(null)

10

7369

SMITH

CLERK

7902

1980-12-17

800

(null)

20


결과와 같이 MGR 을 기준으로 오름차순으로 정렬되는건 동일하지만

null 의 위치가 어디에 위치하라고 명시해놓는 문법이라고 보시면 됩니다








또한, OVER 절을 사용하면 분석함수로도 사용할 수 있습니다.


부서에서 제일먼저 입사한 직원과 나중에 입사한 직원의 급여가 본인의 급여와 어느정도 차이나는지 비교해보세요.


SELECT DEPTNO, ENAME, SAL, HIREDATE
     , MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY HIREDATE ) OVER ( PARTITION BY DEPTNO )       AS JOIN_FIRST_SAL   /* 부서내 가장 먼저 입사한 직원의 급여 */
     , MAX(SAL) KEEP ( DENSE_RANK LAST  ORDER BY HIREDATE ) OVER ( PARTITION BY DEPTNO )       AS JOIN_LAST_SAL    /* 부서내 최근 입사한 직원의 급여 */
     , SAL - MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY HIREDATE ) OVER ( PARTITION BY DEPTNO ) AS JOIN_FIRST_DIFF  /* 부서내 가장 먼저 입사한 직원과 본인의 급여 비교 */
     , SAL - MAX(SAL) KEEP ( DENSE_RANK LAST  ORDER BY HIREDATE ) OVER ( PARTITION BY DEPTNO ) AS JOIN_LAST_DIFF   /* 부서내 최근 입사한 직원과 본인의 급여 비교 */
  FROM EMP
 ORDER BY DEPTNO, HIREDATE


결과

DEPTNO

ENAME

SAL

HIREDATE

JOIN_FIRST_SAL

JOIN_LAST_SAL

JOIN_FIRST_DIFF

JOIN_LAST_DIFF

10

CLARK

2450

1981-06-09

2450

1300

0

1150

10

KING

5000

1981-11-17

2450

1300

2550

3700

10

MILLER

1300

1982-01-23

2450

1300

-1150

0

20

SMITH

800

1980-12-17

800

1100

0

-300

20

JONES

2975

1981-04-02

800

1100

2175

1875

20

FORD

3000

1981-12-03

800

1100

2200

1900

20

SCOTT

3000

1987-04-19

800

1100

2200

1900

20

ADAMS

1100

1987-05-23

800

1100

300

0

30

ALLEN

1600

1981-02-20

1600

950

0

650

30

WARD

1250

1981-02-22

1600

950

-350

300

30

BLAKE

2850

1981-05-01

1600

950

1250

1900

30

TURNER

1500

1981-09-08

1600

950

-100

550

30

MARTIN

1250

1981-09-28

1600

950

-350

300

30

JAMES

950

1981-12-03

1600

950

-650

0



위의 쿼리는 부서별로 파티션을 나눈 후 최초입사, 최근입사로 정렬 후 급여를 추출했습니다.


입사일로 MAX 와 MIN 을 조회했지만 실질적으로 급여를 추출할 수 있었습니다.




이상 KEEP 문법을 사용하여 쿼리를 간단하게 작성하는법을 확인해보았습니다.


집계함수 MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV 함수에 적용하여
FIRST 나 LAST 에 해당하는 row 의 값에 대해 동작할 수 있으니 직접 사용하여 확인해보세요^^




반응형
블로그 이미지

나남나여

일상 제품리뷰와 맛집/여행/사진을 좋아하고 IT 관련 프로그래밍 초급 & 고급 정보를 공유하는 블로그

,
반응형

DB2 날짜 계산 ( TIMESTAMP 타입 )


동작시간, 세션시간, 실행시간 등을 계산하고 싶은 경우

[ 종료시간 - 시작시간 ] 

으로 계산합니다.


위와 같이 작성하여 결과값을 얻으면 좋겠지만

DB2 는 TIMESTAMPDIFF 함수를 사용하여 계산해야 합니다.


문법


>>-TIMESTAMPDIFF--(--expression--,--expression--)--------------><


TIMESTAMPDIFF ( 2, char( timestamp('2017-08-30 17:40:44.046')- timestamp('2017-08-30 17:55:25.335'))) 


첫번째 expression : 시간 단위 유형

두번째 expression : 종료시간 - 시작시간을 뺀 결과를 CHAR (22)로 변환 한 결과



첫번째 expression 에 원하는 형태의 값을 입력하는대로 결과를 출력합니다.


1 : Fractions of a second ( 초의 소수부분  )

2 : Seconds ( 초 )

4 : Minutes ( 분 )

8 : Hours ( 시간 )

16 : Days ( 일 )

32 : Weeks ( 주 )

64 : Months ( 월 )

128 : Quarters (분기 )

256 : Years ( 년 )





주의사항


SELECT TIMESTAMPDIFF(16, CHAR(timestamp('2017-03-01-00.00.00') - timestamp('2017-02-01-00.00.00'))) FROM sysibm.sysdummy1



2017.02.01 ~ 2017.03.01 의 경우 실제일수는 28일인데 결과는 30일로 출력됩니다.


1달이상 계산시 정확한 값이 필요한 경우 다른 방법을 권장합니다.





응용 예제


로그인 시간과 로그아웃 시간을 이용하여 사용자의 세션시간을 확인하는 예제입니다.


SELECT TIMESTAMPDIFF(2, CHAR(timestamp('2017-08-30 17:55:25.335') - timestamp('2017-08-30 17:40:44.046') )) / 60 ||'분'||
       LPAD(
            MOD(
                 TIMESTAMPDIFF(2, CHAR(timestamp('2017-08-30 17:55:25.335') - timestamp('2017-08-30 17:40:44.046') ))
                ,60
               )
           ,2
           ,'0'
          ) ||'초' AS SESSION_TIME   /* 세션시간(분:초) */
  FROM sysibm.sysdummy1
;



결과 : 14분41초

반응형
블로그 이미지

나남나여

일상 제품리뷰와 맛집/여행/사진을 좋아하고 IT 관련 프로그래밍 초급 & 고급 정보를 공유하는 블로그

,
반응형

오라클 파티션 테이블 샘플(생성 수정 삭제) 및 주의사항




대용량 데이터를 조회 시 조금이라도 더 빠른 데이터의 접근을 위해서는 

테이블을 파티션으로 분할하여 테이블을 생성할 수가 있습니다.





파티션 테이블 생성


CREATE TABLE TB_SALE  ( 
      SALE_DATE          VARCHAR2(8 BYTE)  NOT NULL ,
      SALE_TIME          VARCHAR2(6 BYTE)  NOT NULL ,
      CUST_NO            VARCHAR2(10 BYTE) NOT NULL ,
      SALE_AMT           NUMBER                     ,
      INPUT_DATE         DATE
)
  TABLESPACE TS_DATA
  PARTITION BY RANGE (SALE_DATE) 
 (
 PARTITION PR_TB_SALE_201801  VALUES LESS THAN ('20180201') , 
 PARTITION PR_TB_SALE_201802  VALUES LESS THAN ('20180301') , 
 PARTITION PR_TB_SALE_201803  VALUES LESS THAN ('20180401') , 
 PARTITION PR_TB_SALE_201804  VALUES LESS THAN ('20180501') , 
 PARTITION PR_TB_SALE_201805  VALUES LESS THAN ('20180601') , 
 PARTITION PR_TB_SALE_201806  VALUES LESS THAN ('20180701') , 
 PARTITION PR_TB_SALE_201807  VALUES LESS THAN ('20180801') , 
 PARTITION PR_TB_SALE_201808  VALUES LESS THAN ('20180901') , 
 PARTITION PR_TB_SALE_201809  VALUES LESS THAN ('20181001') , 
 PARTITION PR_TB_SALE_201810  VALUES LESS THAN ('20181101') , 
 PARTITION PR_TB_SALE_201811  VALUES LESS THAN ('20181201') , 
 PARTITION PR_TB_SALE_201812  VALUES LESS THAN ('20190101') , 
 PARTITION PR_TB_SALE_201901  VALUES LESS THAN ('20190201') , 
 PARTITION PR_TB_SALE_201902  VALUES LESS THAN ('20190301') , 
 PARTITION PR_TB_SALE_201903  VALUES LESS THAN ('20190401') , 
 PARTITION PR_TB_SALE_201904  VALUES LESS THAN ('20190501') , 
 PARTITION PR_TB_SALE_201905  VALUES LESS THAN ('20190601') , 
 PARTITION PR_TB_SALE_201906  VALUES LESS THAN ('20190701') , 
 PARTITION PR_TB_SALE_201907  VALUES LESS THAN ('20190801') , 
 PARTITION PR_TB_SALE_201908  VALUES LESS THAN ('20190901') , 
 PARTITION PR_TB_SALE_201909  VALUES LESS THAN ('20191001') , 
 PARTITION PR_TB_SALE_201910  VALUES LESS THAN ('20191101') , 
 PARTITION PR_TB_SALE_201911  VALUES LESS THAN ('20191201') , 
 PARTITION PR_TB_SALE_201912  VALUES LESS THAN ('20200101') 
-- PARTITION PR_TB_SALE_MAX  VALUES LESS THAN (MAXVALUE) 
 ) 
 ;


CREATE UNIQUE INDEX PK_TB_SALE ON TB_SALE (SALE_DATE, SALE_TIME, CUST_NO)
TABLESPACE TS_INDEX LOCAL ;  


ALTER TABLE TB_SALE
  ADD CONSTRAINT PK_TB_SALE PRIMARY KEY (SALE_DATE, SALE_TIME, CUST_NO);


COMMENT ON COLUMN TB_SALE.SALE_DATE IS '매출일자';
COMMENT ON COLUMN TB_SALE.SALE_TIME IS '매출시간';
COMMENT ON COLUMN TB_SALE.CUST_NO   IS '고객번호';
COMMENT ON COLUMN TB_SALE.SALE_AMT  IS '매출 금액';
COMMENT ON TABLE  TB_SALE           IS '고객 매출내역';





파티션 구성 시 마지막에 MAXVALUE 를 주석으로 막은 이유는 

분할해 둔 파티션의 마지막이 임박해오는 경우 SPLIT 보다 파티션 추가가 간편하기 때문입니다!

물론 예기치 않은 데이터가 들어오는 경우 MAXVALUE 를 지정해두는것이 안정적이긴 합니다.



파티션 추가

 ALTER TABLE TB_SALE
 ADD PARTITION PR_TB_SALE_202001 VALUES LESS THAN ('20200201' )
 TABLESPACE TS_INDEX ;





파티션 삭제


ALTER TABLE TB_SALE DROP PARTITION PR_TB_SALE_202001 ;








파티션 분할 (MAXVALUE 파티션 분할)


ALTER TABLE TB_SALE 
SPLIT PARTITION PR_TB_SALE_MAX AT ( '20200201' )
INTO ( PARTITION PR_TB_SALE_202001
     , PARTITION PR_TB_SALE_MAX
     ) ;



MAXVALUE 로 잡혀있던 PR_TB_SALE_MAX 파티션을 2020년 02월 01일 이전 데이터가 입력될 파티션으로 분리하였다.






파티션 TRUNCATE


ALTER TABLE TB_SALE TRUNCATE PARTITION PR_TB_SALE_201801 ;





파티션 테이블 인덱스 생성


CREATE UNIQUE INDEX PK_TB_SALE ON TB_SALE (SALE_DATE, SALE_TIME, CUST_NO)
TABLESPACE TS_INDEX LOCAL ; 






파티션 테이블을 생성할 때 INDEX 설정 시 주의할 사항이 있습니다.

예를 들어 위와 같이 RANGE 파티션을 가진 테이블을 월별로 파티션을 분리한다고 했을 때 

테이블 전체에 INDEX 를 구성하는 것 보다 각각의 파티션 별로 INDEX 를 가지고 있어야 

대용량의 테이블을 조회할 때 더 효과적인 속도를 기대할 수 있습니다.





여기서 테이블스페이스의 INDEX 지정 시 LOCAL INDEX 가 아닌 default 로 설정하거나 GLOBAL INDEX 로 설정할 경우

파티션기준이 아닌 전체 테이블을 기준으로 INDEX 가 만들어지니 주의해야 합니다.





파티션 테이블이 GLOBAL INDEX 로 생성될 경우 테이블의 중간중간 데이터가 삭제되거나 변경된다면 

전체 테이블 기준으로 최적화된 INDEX 가 뒤죽박죽 될 수 있으나

LOCAL 로 생성되는 경우에는 다른 파티션의 INDEX 에는 영향이 가지 않으니 

INDEX 를 사용하는 SQL 인 경우 더 빠른 실행계획을 세울 수 있게 됩니다.




궁금한점이나 잘못된 부분이 있으면 댓글 남겨주세요^^

감사합니다.

반응형
블로그 이미지

나남나여

일상 제품리뷰와 맛집/여행/사진을 좋아하고 IT 관련 프로그래밍 초급 & 고급 정보를 공유하는 블로그

,
반응형

오라클 MERGE 문 간단한 예제로 한번에 INSERT, UPDATE 처리




UPDATE INSERT 를 동시에 처리하고 싶은 경우가 간혹 존재하는데

이럴 경우 과거 CURSOR 를 사용하거나 UPDATE 를 먼저 처리하고 INSERT 를 처리한 경우가 있습니다.


이제 MERGE 문을 사용하면 SELECT ~ INSERT 시 원하는 조건에 맞는 행은 UPDATE 를, 조건에 맞지 않는 행은 INSERT 를 할 수 있게 되었습니다.







즉,

       이미 존재하는 값은 UPDATE

       신규로 입력된 값은 INSERT






이 처리가 한번에 가능합니다.

그것도 다수의 행을 UPDATE, INSERT 가 가능합니다.

일명 UPSERT 라고도 부르기도 하죠.


오라클에서 제공하는 문법은 아래와 같습니다.




MERGE 문법









이렇게 보시면 복잡하지만 아래예제와 설명을 보면 간단하게 알 수 있습니다!





MERGE INTO 예제

MERGE INTO CUSTOMER C
USING
(
      SELECT USERNO
           , USERNAME
           , ADDRESS
           , PHONE
       FROM NEW_JOIN
      WHERE INPUT_DATE = '20170724'
) N
ON ( C.USERNO = N.USERNO)
WHEN MATCHED THEN
UPDATE
SET C.USERNAME = N.USERNAME
  , C.ADDRESS  = N.ADDRESS
  , C.PHONE    = N.PHONE
WHEN NOT MATCHED THEN
INSERT ( USERNO
       , USERNAME
       , ADDRESS
       , PHONE
       )
 VALUES (
         N.USERNO
       , N.USERNAME
       , N.ADDRESS
       , N.PHONE
 )






위의 간단한 예제는 신규고객이 들어왔을 때 기존 고객테이블에 데이터를 입력하는 업무입니다.

하나씩 문법을 살펴보겠습니다.






MERGE INTO CUSTOMER C : INSERT 또는 UPDATE 할 테이블과 테이블의 ALIAS 를 지정해줍니다.




USING : 원하는 결과를 추출하기 위한 SELECT 문입니다.  이 SELECT 문에서 나온 결과를 INSERT 또는 UPDATE 할 예정입니다.




ON : SELECT 한 결과와 입력하고 싶은 테이블의 UNIQUE 한 값을 매칭하는 연결고리입니다. ( 주로 KEY 값을 사용 )




WHEN MATCHED THEN : SELECT 의 결과가 INSERT 할 테이블에 값이 이미 존재하는 경우 UPDATE 를 실행합니다.




WHEN NOT MATCHED THEN : SELECT 의 결과가 INSERT 할 테이블에 값이 없는 경우 INSERT 를 실행합니다.









업데이트만 하고 싶은 경우에는 아래 WHEN NOT MATCHED THEN 이하 문장을 제거하면 됩니다.


MERGE UPDATE 예제

MERGE INTO CUSTOMER C
USING
(
      SELECT USERNO
           , USERNAME
           , ADDRESS
           , PHONE
       FROM NEW_JOIN
      WHERE INPUT_DATE = '20170724'
) N
ON ( C.USERNO = N.USERNO)
WHEN MATCHED THEN
UPDATE
SET C.USERNAME = N.USERNAME
  , C.ADDRESS  = N.ADDRESS
  , C.PHONE    = N.PHONE





또한 오라클 10g 이후로는 SELECT 한 결과를 UPDATE 뿐 아니라 DELETE 도 처리할 수 있습니다.



MERGE UPDATE DELETE 예제

MERGE INTO CUSTOMER C
USING
(
      SELECT USERNO
           , USERNAME
           , ADDRESS
           , PHONE
       FROM NEW_JOIN
      WHERE INPUT_DATE = '20170724'
) N
ON ( C.USERNO = N.USERNO)
WHEN MATCHED THEN
UPDATE
SET C.USERNAME = N.USERNAME
  , C.ADDRESS  = N.ADDRESS
  , C.PHONE    = N.PHONE
DELETE WHERE SAL >= 3000





이 MERGE 문은 DML 의 문장으로 트랜잭션(TRANSACTION) 단위로 실행되기 때문에 COMMIT, ROLLBACK 을 처리해야 완전한 반영이 됩니다.



이 외에 궁금하신점은 댓글로 문의주시면 답변 달아드리겠습니다.

감사합니다.

반응형
블로그 이미지

나남나여

일상 제품리뷰와 맛집/여행/사진을 좋아하고 IT 관련 프로그래밍 초급 & 고급 정보를 공유하는 블로그

,
반응형

오라클 SUBSTR 함수 문법과 예제로 마스터하기




문법

SUBSTR(기준문자, 시작위치, 종료위치);

SUBSTR(기준문자, 시작위치);



예제

SELECT SUBSTR('HELLOSQL', 1, 5 );
------------------------------------------
결과 : HELLO


SUBSTR 함수는 결과의 원하는 문자열 자르기를 하기 위해 시작위치와 종료위치를 입력하여 결과의 일부분을 추출하게됩니다.

일반적으로 SUBSTR 은 문자를 자를 때 사용하게 됩니다.




SUBSTR 사용 규칙


 - 시작위치가 0이면 1로 처리됩니다. 

SELECT SUBSTR('HELLOSQL',0,1) FROM DUAL
SELECT SUBSTR('HELLOSQL',1,1) FROM DUAL
 ------------------------------------------
 결과 : H

   


  

 - 시작위치에 양수를 입력하면 문자의 시작부분부터 카운트하여 첫번째 문자를 찾습니다. 

SELECT SUBSTR('HELLOSQL',3,2) FROM DUAL
------------------------------------------
결과 : LL     



 - 시작위치에 음수를 입력하면 문자의 끝부분부터 뒤로 계산하여 첫번째 문자를 찾습니다.

SELECT SUBSTR('HELLOSQL',-3,2) FROM DUAL
------------------------------------------
결과 : SQ         




 - 종료위치가 생략되면, 시작위치 이후의 모든 문자를 리턴합니다.

SELECT SUBSTR('HELLOSQL',3) FROM DUAL
------------------------------------------
결과 : LLOSQL
 

 



  - substring_length가 1보다 작으면 null을 리턴합니다. 

SELECT SUBSTR('HELLOSQL',3,0) FROM DUAL
------------------------------------------
결과 : NULL




  - 시작위치와 종료위치에 소수점을 입력한 경우 자동으로 정수로 변환됩니다. ( 3.8 -> 3 , 3.1 -> 3 )

SELECT SUBSTR('HELLOSQL',3,3.9) FROM DUAL
SELECT SUBSTR('HELLOSQL',3,3.1) FROM DUAL
------------------------------------------
결과 : LLO

     

 - SUBSTR 의 첫번째 파라메터에 들어갈 수 있는 데이터타입은 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB 중 1개 타입을 사용할 수 있습니다.

    또한, 결과값은 파라메터에 입력한 타입과 동일한 데이터타입을 리턴합니다.

    



 - 시작위치와 종료위치에 입력되어야 할 값은 데이터타입이 NUMBER 타입이거나 NUMBER 타입으로 변환될 수 있는 데이터타입으로 입력해야 합니다. ( 즉, 정수값이 입력되어야 합니다. )








SUBSTR 과 비슷하지만 다른 문자형태를 잘라서 사용하고 싶은 경우 아래의 함수를 이용할 수 있습니다. ( 문법동일 )


SUBSTRB : 문자 대신 바이트를 사용

SUBSTRC : 유니코드 완성형 문자를 사용

SUBSTR2 : UCS2 코드포인트를 사용

SUBSTR4 : UCS4 코드포인트를 사용


위의 함수 중 SUBSTRB 는 1~2년에 한두번 사용해보았으나 그 밑의 함수들은 아직 사용해본적이 한번도 없습니다.




♡공감과 댓글은 저에게 더 큰 힘이 됩니다!

( 로그인하지 않아도 공감버튼은 클릭됩니다. )



반응형
블로그 이미지

나남나여

일상 제품리뷰와 맛집/여행/사진을 좋아하고 IT 관련 프로그래밍 초급 & 고급 정보를 공유하는 블로그

,
반응형

오라클 다양한 INSERT INTO 방법


INSERT문을 사용하면 테이블에 데이터를 입력할 수가 있습니다.


물론 해당테이블에 INSERT 권한이 있어야 입력이 가능합니다.


이 포스팅에서는 다양한 방법으로 INSERT 처리하는 법을 알아보겠습니다.


INSERT 문법

INSERT INTO dml_table_expression_clause [ t_alias ]
[ (column [, column ]...) ]
VALUES ({ expr | DEFAULT }
          [, { expr | DEFAULT } ]...
       )



INSERT 예제

INSERT INTO EMP e ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( '7499','ALLEN','SALESMAN','7698',SYSDATE,'1600','300','30');
INSERT INTO EMP e ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( '7521','WARD' ,'SALESMAN','7698',SYSDATE,'1250','500','30');


INSERT INTO EMP e VALUES ( '7499','ALLEN','SALESMAN','7698',SYSDATE,'1600','300','30');
INSERT INTO EMP e VALUES ( '7521','WARD' ,'SALESMAN','7698',SYSDATE,'1250','500','30');


INSERT INTO EMP e VALUES ( '7369','SMITH','CLERK','7902',SYSDATE,'800',NULL,'20');
INSERT INTO EMP e ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO ) VALUES ( '7369','SMITH','CLERK','7902',SYSDATE,'800','20');


일반적인 INSERT 방법은 위의 문법으로 입력을 하게 됩니다.


아래 두줄의 차이점은 전체 컬럼의 값을 넣느냐 넣지 않느냐로 비교할 수 있는데

5번 라인은 전체 컬럼 기준으로 COMM 컬럼에 NULL 을 명시하여 입력한 CASE 이며

6번 라인은 INSERT 할 컬럼에서 COMM 컬럼을 제외한 컬럼에 직접 값을 입력한 CASE 입니다.

( 동일한 결과를 확인 할 수 있습니다. )






테이블 생성과 데이터 입력을 한번에!

CREATE TABLE SCOTT.EMP_BACKUP AS 
SELECT * 
  FROM SCOTT.EMP
;


또는 테이블 백업 시 데이터도 그대로 넣어야 할 경우도 있습니다.

위와 같이 생성 시 테이블 생성 + 데이터 입력이 동시에 이루어지게 됩니다.


CREATE TABLE SCOTT.EMP_BACKUP AS 
SELECT EMPNO
     , ENAME
     , JOB
     , MGR
     , 2500 AS SAL
     , COMM
     , DEPTNO 
  FROM SCOTT.EMP
 ;

INSERT 와 마찬가지로 데이터는 CREATE TABLE [table_name] AS SELECT 문에서도 원하는 컬럼만 생성하여 컬럼을 생성할 수 있습니다.

이 때, 컬럼의 ALIAS 는 필수입니다!



일반적으로 테이블 백업을 할 때는 위의 방법을 사용할것으로 예상됩니다.




다수의 INSERT 처리

INSERT INTO EMP_BAK
SELECT * FROM EMP
;

INSERT 할 테이블을 지정 후 SELECT 결과를 INSERT 합니다.

INSERT INTO SCOTT.EMP_BAK ( EMPNO, ENAME, JOB, SAL )
 SELECT EMPNO, ENAME, JOB, SUM(SAL)
   FROM SCOTT.EMP
 GROUP BY EMPNO, ENAME, JOB
;

위와 같이 가공한 데이터를 INSERT 할 때 주로 사용합니다.



다수 테이블의 INSERT 처리

INSERT ALL
  INTO EMP_BAK  ( EMPNO, ENAME, JOB )
  INTO EMP_BAK2 ( EMPNO, ENAME, JOB )
  INTO EMP_BAK3 ( EMPNO, ENAME, JOB )
  INTO EMP_BAK4 ( EMPNO, ENAME, JOB )
  INTO EMP_BAK5 ( EMPNO, ENAME, JOB )
SELECT EMPNO, ENAME, JOB 
  FROM EMP
;

사실 다수 테이블에 한번에 INSERT 할 일이 없어서 실제로 써본적은 없지만 이런 방법이 있다는 것만 알아도 될 것 같습니다.



엑셀을 이용한 다수의 INSERT 처리


이건 INSERT 뿐만 아니라 여러방면으로 활용될 수 있는 방법인데

엑셀의 각 열에 데이터를 입력 후 수식을 이용해 문장을 만든 후 INSERT 처리하는 방법입니다.


선행작업 : A B C 열에 데이터를 입력합니다.

-- 수식
="INSERT INTO EMP VALUES('"&A1&"',"&"'"&B1&"',"&"'"&C1&"');"
="INSERT INTO EMP VALUES('"&A2&"',"&"'"&B2&"',"&"'"&C2&"');"
="INSERT INTO EMP VALUES('"&A3&"',"&"'"&B3&"',"&"'"&C3&"');"

-- 값 복사
INSERT INTO EMP VALUES('7369','SMITH','CLERK');
INSERT INTO EMP VALUES('7499','ALLEN','SALESMAN');
INSERT INTO EMP VALUES('7521','WARD','SALESMAN');

값 복사를 통해 위의 결과를 한번에 실행하여 INSERT 가능합니다.





이상으로 테이블 데이터 INSERT 하는 방법에 대해 알아보았습니다.


반응형
블로그 이미지

나남나여

일상 제품리뷰와 맛집/여행/사진을 좋아하고 IT 관련 프로그래밍 초급 & 고급 정보를 공유하는 블로그

,
반응형