'oracle' 카테고리의 글 목록

정말 당황했는데

아주 꿀팁

CREATE TEMPORARY TABLESPACE temp01 TEMPFILE '/app/oracle/temp01.dbf' size 5600M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;

drop tablespace temp;

http://jun3.tistory.com/26

'oracle' 카테고리의 다른 글

[Oracle] START WITH CONNECT BY - 계층쿼리 (0)	2018.10.29
[Oracle] ORA-00911:문자가 부적합 합니다. (0)	2018.10.29
[Oracle] NVL(), NVL2(), DECODE() 함수 (0)	2018.10.29
[Oracle] Window Oracle 11g 설치 (0)	2018.10.28

오라클에서 유용한 기능중의 하나인

START WITH

CONNECT BY 문입니다.

구루비에 좋은 내용이 있어서 공유합니다.

출처

http://www.gurubee.net/lecture/1300

계층구조 쿼리란?

오라클 데이터베이스 scott 유저의 emp 테이블을 보면 empno와 mgr컬럼이 있으며, mgr 컬럼 데이터는 해당 사원의 관리자의 empno를 의미 한다.

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

1

2

3

4

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

- empno 7369사원의 관리자는 7902의 empno를 가진 사원이며
- empno 7902사원의 관리자는 7566의 empno를 가진 사원이다.

이런 상위 계층과 하위계층의 관계를 오라클에서는 START WITH와 CONNECT BY를 이용해서 쉽게 조회 할 수 있다.

계층구조 쿼리 Synctax

START WITH

- 계층 질의의 루트(부모행)로 사용될 행을 지정 한다.
- 서브쿼리를 사용할 수도 있다.

CONNECT BY

- 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 할 수 있다.
- PRIOR 연산자와 함께 사용하여 계층구조로 표현할 수 있다.
- CONNECT BY PRIOR 자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리구성 (Top Down)
- CONNECT BY PRIOR 부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)
- CONNECT BY NOCYCLE PRIOR : NOCYCLE 파라미터를 이용하여 무한루프 방지
- 서브쿼리를 사용할 수 없다.

LEVEL Pseudocolumn

- LEVEL은 계층구조 쿼리에서 수행결과의 Depth를 표현하는 의사컬럼이다.

ORDER SIBLINGS BY

- ORDER SIBLINGS BY절을 사용하면 계층구조 쿼리에서 편하게 정렬작업을 할 수 있다.

CONNECT BY의 실행순서는 다음과 같다.

- 첫째 START WITH 절
- 둘째 CONNECT BY 절
- 세째 WHERE 절 순서로 풀리게 되어있다.

계층구조 쿼리 예제

간단예제

아래는 직업이 PRESIDENT을 기준으로 계층 구조로 조회하는 예이다.

-- LEVEL컬럼으로 depth를 알수 있다. 
-- JONES의 관리자는 KING 이며, SCOTT의 관리자는 JONES 이다. 
-- 상/하의 계층 구조를 쉽게 조회 할 수 있다. 
SELECT LEVEL, empno, ename, mgr
  FROM emp
 START WITH job = 'PRESIDENT'
CONNECT BY PRIOR  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
...

PRIOR 연산자 : 상위행의 컬럼임을 나타낸다. CONNECT BY 절에서 상하위간의 관계를 기술할때 사용.

LEVEL의 활용

LEVEL Pseudocolumn을 이용하면 계층구조 쿼리를 좀 더 다양하게 활용 할 수 있다.

아래는 LEVEL의 배율만큼 공백을 왼쪽에 추가하여 계층구조를 한눈에 볼 수 있게 표현한 예이다.

-- SQL*Plus에서만 깔끔하게 보기위해서 
COL ename FORMAT A20;
 
 
-- 왼쪽에 LEVEL만큼 공백을 추가하여 계층구조로 조회하는 예제
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job 
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr; 
 
 
LEVEL ENAME                     EMPNO    MGR    JOB
------ --------------------    -------  -----  --------
     1 KING                       7839         PRESIDEN
     2     JONES                  7566   7839  MANAGER
     3         SCOTT              7788   7566  ANALYST
     4             ADAMS          7876   7788  CLERK
     3         FORD               7902   7566  ANALYST
     4             SMITH          7369   7902  CLERK
     2     BLAKE                  7698   7839  MANAGER
...

아래는 LEVEL별로 급여 합계와 사원수를 조회하는 예제이다.

-- LEVEL별로 급여 합계와 사원수를 조회하는 예제
SELECT LEVEL, AVG(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

PRIOR의 활용

PRIOR연산자를 SELECT 절에서 사용해보자.

아래는 사원의 관리자를 PRIOR연산자를 이용해서 조회하는 예제이다.

-- SQL*Plus에서만 깔끔하게 보기위해서 
COL mgrname FORMAT A10;
 
 
-- SELECT절에 "PRIOR ename mgrname"을 확인해 보자
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, 
       PRIOR ename mgrname,
       empno, mgr, job 
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr; 
 
 
  LEVEL ENAME                MGRNAME         EMPNO        MGR JOB
------- -------------------- ---------- ---------- ---------- ---------
      1 KING                                  7839            PRESIDENT
      2     JONES            KING             7566       7839 MANAGER
      3         SCOTT        JONES            7788       7566 ANALYST
      4             ADAMS    SCOTT            7876       7788 CLERK
      3         FORD         JONES            7902       7566 ANALYST
      2     BLAKE            KING             7698       7839 MANAGER
      3         MARTIN       BLAKE            7654       7698 SALESMAN
      3         TURNER       BLAKE            7844       7698 SALESMAN
      3         JAMES        BLAKE            7900       7698 CLERK
      2     CLARK            KING             7782       7839 MANAGER
      3         MILLER       CLARK            7934       7782 CLERK

PRIOR 연산자 : 상위행의 컬럼임을 나타낸다. CONNECT BY 절에서만 사용할 수 있는 것은 아니다.

Bottom Up 조회 예제

위 간단 예제를 역순(자식에서 부모로 트리 구성, Bottom Up)으로 조회 해 보자

-- SQL*Plus에서만 깔끔하게 보기위해서 
COL ename FORMAT A20;
 
 
-- ename을 기준으로 Bottom Up으로 조회하는 예제이다.
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job 
  FROM emp
 START WITH ename='SMITH'  -- 최 하위 노드 값이 와야 한다. 
CONNECT BY PRIOR mgr = empno; 
 
 
LEVEL ENAME                EMPNO      MGR    JOB
------ ---------------    -------- -------- ---------
     1 SMITH                 7369     7902   CLERK
     2     FORD              7902     7566   ANALYST
     3         JONES         7566     7839   MANAGER
     4             KING      7839            PRESIDENT

PRIOR 컬럼에 따라(상위 OR 하위) 계층전개 방향이 달라진다.

- 순방향(Top Down-상위~하위) : PRIOR 하위 = 상위
- 역방향(Bottom Up-하위~상위) : PRIOR 상위 = 하위

출처

http://www.gurubee.net/lecture/1300

'oracle' 카테고리의 다른 글

[Oracle] ORA-01652:128(으)로 테이블 공간 TEMP에서 임시 세그먼트를 확장할 수 없습니다. (0)	2018.12.31
[Oracle] ORA-00911:문자가 부적합 합니다. (0)	2018.10.29
[Oracle] NVL(), NVL2(), DECODE() 함수 (0)	2018.10.29
[Oracle] Window Oracle 11g 설치 (0)	2018.10.28

Spring과 mybatis를 섞어서 사용하시는 분들은

한번쯤 겪었을법한

ORA-00911:문자가 부적합 합니다.

에러입니다.

주로 오후 4시 이후에나 나오지 않을까 싶은데 ...

(제가 그랬죠 ..)

쿼리 마지막부분에

세미콜론(;) 같은것들을 확인해보시기 바랍니다.

Toad에서는 돌아가는데 mybatis 환경에서는 에러가 난다 싶으면

거의 100퍼센트 세미콜론이니 주의해서 봐보세요~~

궁금한점이나 잘못된점 있으면 댓글 주세요~! 피드백하겠습니다.

'oracle' 카테고리의 다른 글

[Oracle] ORA-01652:128(으)로 테이블 공간 TEMP에서 임시 세그먼트를 확장할 수 없습니다. (0)	2018.12.31
[Oracle] START WITH CONNECT BY - 계층쿼리 (0)	2018.10.29
[Oracle] NVL(), NVL2(), DECODE() 함수 (0)	2018.10.29
[Oracle] Window Oracle 11g 설치 (0)	2018.10.28

어쩌면 오라클에서 가장 많이사용하는 함수인

DECODE(), NVL(), NVL2() 함수에 대해서 다루겠습니다.

전부 값을 어떤것으로 치환할때 사용하는건데요

처음에 익숙하지 않아서 그런데

계속 사용하다보면 굉장히 편합니다

1.NVL(컬럼,A)

먼저 NVL()함수입니다.

파라미터는 총 두개입니다.

NVL(컬럼,A)

컬럼이 Null값이면 A값으로 치환해줍니다.

간단하죠..?

2.NVL2(컬럼,A,B)

NVL2()함수입니다.

눈치 채셨다시피

컬럼이 Null이 아니라면 A로 치환해주는것이고요.

컬럼이 Null이면 B로 치환해줍니다.

3.DECODE(컬럼,조건1,값1,조건2,값2,기본값)

DECODE()함수입니다.

간단하게 설명드리면

프로그래밍에서 switch-case 문과 맥락이 같다고 보시면 됩니다

컬럼에 대하여 조건1을 만족한다면 값1을 출력하고 조건2를 만족한다면 값2를 출력...

DECODE함수에서 개수는 상관이 없습니다.

그리고 조건에 해당하는 값이 없다면 맨 마지막에 기본 Default 값을 출력하게 할 수 있습니다.

세 함수 모두 SELECT 절에서 사용 가능하며

중첩 또한 가능합니다.

궁금한점이나 잘못된점 있으면 댓글 주세요~! 피드백하겠습니다.

'oracle' 카테고리의 다른 글

[Oracle] ORA-01652:128(으)로 테이블 공간 TEMP에서 임시 세그먼트를 확장할 수 없습니다. (0)	2018.12.31
[Oracle] START WITH CONNECT BY - 계층쿼리 (0)	2018.10.29
[Oracle] ORA-00911:문자가 부적합 합니다. (0)	2018.10.29
[Oracle] Window Oracle 11g 설치 (0)	2018.10.28

옛날에 학원다닐때 Oracle 잠깐 써보고 지금까지

MySQL만 사용했었는데

현재 회사에서는 Oracle을 사용하기 때문에 제 PC에 설치도 해볼겸 포스팅합니다.

(근데 window로...)

오라클 설치입니다.

https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

해당 경로로 들어가시면

이렇게 쿠키 관련된 팝업이 뜨는데 그냥 동의 및 진행 클릭하시면 됩니다.

oracle jdk를 설치해보신 분이라면 아시겠지만

Accept License Agreement

에 체크해줘야 원활하게 다운로드를 진행하실 수 있습니다.

각자의 os에 맞는 파일을 선택하신 후

다운로드를 진행하시면 됩니다.

(두개 전부 받아야하네요 ..)

...!

오라클 계정이 있어야합니다.

없으신분들은 번거로우시더라도 회원가입 하신 후 진행하시면 되겠습니다.

간신히 찾아낸 계정으로 다운로드가 잘 되고 있네요

저의 C드라이브는 위태위태하기때문에 D드라이브 기준으로 설치하겠습니다.

win64_11gR2_database_1of2.zip

win64_11gR2_database_2of2.zip

파일들이 전부 다운로드가 되었다면 압축을 풀어줘야하는데

여기에서 주의해야하실 부분이 있습니다.

두 파일을 동시에 압축을 풀어주셔야합니다 ~

만약 폴더명으로 각자 풀으신분들은

D:\oracle\win64_11gR2_database_2of2\database\stage\Components

안에 있는 파일들을

D:\oracle\win64_11gR2_database_2of1\database\stage\Components

여기로 옮겨주시기만 하시면 됩니다

이후에

database 폴더에

setup.exe를 실행시켜주시면 됩니다.

저처럼 이렇게

INS-13001 환경이 최소 요구 사항을 충족하지 않습니다.

계속하겠습니까?

라는 메세지가 나오셔도 그냥 예 누르시고 설치 진행하셔도 됩니다.

크게 신경 안쓰셔도 돼요.

앞으로 나오는 그림은 전부 참고만하시고

그냥 기본세팅으로 다음 진행하시면 됩니다.

(그냥 넘어가셔도 돼요.)

여기에서는 원하시는 경로를 입력해주시면 됩니다.

전역 데이터베이스 이름(sid) 는 기본이 orcl이고 변경하실분들은 변경하셔도 좋습니다.

비밀번호는 최대 8글자 이상으로 문자(대문자 or 소문자), 숫자가 전부 포함되어야 합니다.

db가 전부 설치가 되면 다음과 같은 창이 뜨는데요

여기서 ORACLE 연습용계정 SCOTT계정의 비밀번호를 설정하실 수 있습니다.

만약 여기서 SCOTT계정 설정을 못하신분들은

인터넷에 검색하시면 많이 나와있으니 참고하시면 됩니다.

(저 계정을 잠금을 풀어주셔야합니다.)

설치가 전부 완료가 되면

CMD창을 열어서 SQLPLUS를 입력하시면

ORACLE DB에 접근하실 수 있습니다.

여기까지 포스팅 마치겠습니다~

궁금한점이나 잘못된점 있으면 댓글 주세요~! 피드백하겠습니다.

'oracle' 카테고리의 다른 글

[Oracle] ORA-01652:128(으)로 테이블 공간 TEMP에서 임시 세그먼트를 확장할 수 없습니다. (0)	2018.12.31
[Oracle] START WITH CONNECT BY - 계층쿼리 (0)	2018.10.29
[Oracle] ORA-00911:문자가 부적합 합니다. (0)	2018.10.29
[Oracle] NVL(), NVL2(), DECODE() 함수 (0)	2018.10.29

섭이의 개발일지

oracle

[Oracle] ORA-01652:128(으)로 테이블 공간 TEMP에서 임시 세그먼트를 확장할 수 없습니다.

'oracle' 카테고리의 다른 글

[Oracle] START WITH CONNECT BY - 계층쿼리

계층구조 쿼리란?

계층구조 쿼리 Synctax

START WITH

CONNECT BY

LEVEL Pseudocolumn

ORDER SIBLINGS BY

CONNECT BY의 실행순서는 다음과 같다.

계층구조 쿼리 예제

간단예제

LEVEL의 활용

PRIOR의 활용

Bottom Up 조회 예제

'oracle' 카테고리의 다른 글

[Oracle] ORA-00911:문자가 부적합 합니다.

'oracle' 카테고리의 다른 글

[Oracle] NVL(), NVL2(), DECODE() 함수

'oracle' 카테고리의 다른 글

[Oracle] Window Oracle 11g 설치

'oracle' 카테고리의 다른 글

+ Recent posts

티스토리툴바