LuceteMango:)

- TOAD 단축키 모음

▶ F3: 단어찾기 시 다음 일치하는 단어검색 (이전검색은  SHFIT + F3)

▶ F4 : 테이블 등 오브젝트의 정보를 보여줌. (OR CTRL + 오브젝트 클릭)

▶ F5 : 현재화면의 모든 SQL 실행 (CTRL + ENTER : 커서가 위치한 곳 또는 블록으로 선택한 영역실행)

▶ F8 : 이전에 실행했던 SQL History 조회

▶ CTRL + SHIFT + F : 쿼리 자동정렬

▶ CTRL + . : 테이블, 컬럼 등 자동완성

▶ CTRL + M : 자바코드로 변환. 단축키 실행 후 붙여넣기

▶ CTRL + INSERT : 조회된 로우복사시 컬림ID도 같이 복사됨.

▶ CTRL + B : 주석처리

▶ CTRL + SHIFT + B : 주석해제

▶ CTRL + L : 소문자 변경

▶ CTRL + U : 대문자 변경

▶ CTRL + F9 : 실행없이 SQL 문장 Vaildation 체크

출처 : http://mycup.tistory.com/29

- BLOB 문자열 Select

SQL) SELECT UTL_RAW.CAST_TO_VARHCAR2(BLOB컬럼명)

          FROM TABLE_NAME

이렇게 하면, 문자열이 정상적으로 나오는데...한글이 깨진다(영어만 정상)'- ' 

이유는 모르겟다. BLOB 넣는 부분은 내가한게아니니'- '

암튼, 구글링해서... 찾음'-' V

위의 쿼리문 실행해서 한글이 깨지면 CONVERT() 로 인코딩 변경

SQL) SELECT CONVERT(UTL_RAW.CAST_TO_VARHCAR2(BLOB컬럼), 'AL32UTF8', 'KO16KSC5601')

          FROM TABLE_NAME

숫자형 함수란 수 값을 인수로 받아 NUMBER 타입의 값을 반환하는 함수이다.

ABS(n)

ABS 함수는 절대값을 계산하는 함수이다.

CEIL(n)

CEIL 함수는 올림값을 반환하는 함수이다.

FLOOR(n)

FLOOR 함수는 버림값을 반환하는 함수이다.

MOD(m, n)

MOD 함수는 m을 n으로 나눈 나머지를 반환 한다. n이 0일 경우 m을 반환 한다.

ROUND(n, [m])

ROUND 함수는 n값의 반올림을 하는 함수로 m은 소숫점 아래 자릿수를 나타낸다.

TRUNC(n, m)

TRUNC 함수는 n값을 절삭하는 함수로 m은 소숫점 아래 자릿수를 나타낸다.

Oracle Number Functions

ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
NANVL
POWER
REMAINDER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WIDTH_BUCKET

- 출처 : 꿈꾸는 개발자 DBA 커뮤니티 (그루비) http://www.gurubee.net/

◈ CONNECT BY의 실행순서

    (1) START WITH 절

    (2) CONNECT BY 절

    (3) WHERE 절

◈ 

    START WITH : 시작위치 설정 (서브쿼리를 사용가능)

    CONNECT BY : 순환고리의 조건 (서브쿼리를 사용불가)

    ORDER SIBLINGS BY : 같은 레벨중 순환고리 순서를 정할때 사용

◈ PRIOR 의 위치 

    (1) CONNECT BY PRIOR 자식컬럼 =       부모컬럼  ==> 부모에서 자식으로 트리 구성

    (2) CONNECT BY       자식컬럼 = PRIOR 부모컬럼  ==> 자식에서 부모으로 트리 구성

◈ 데이터가 많아질 경우....

    - START WITH MENU_ID = 'TOP' 

          MENU_ID 컬럼에 index가 생성되어 있지 않는다면 속도를 보장할 수 없습니다.

    - CONNECT BY PRIOR MENU_ID = UP_MENU_ID 

          역시 PRIOR 쪽의 컬럼값이 상수가 되기 때문에 UP_MENU_ID컬럼에 index를 생성하여야 CONNECT BY의 속도를 보장할 수 있습니다.

    - 계층구조를 CONNECT BY, START WITH로 풀면 부분범위 처리가 불가능하고 Desc으로 표현하기가 어렵 습니다.

◈  

    (1) '상품' 메뉴는 모두 출력 안함.

    SELECT

            LEVEL AS LEV

          , MENU.*

    FROM    

            TBL_MENU  MENU

    WHERE               MENU.MENU_NAME  <> '상품'

    START WITH          MENU.MENU_ID = 'TOP'

    CONNECT BY PRIOR    MENU.MENU_ID = MENU.UP_MENU_ID

    ORDER SIBLINGS BY   MENU_SEQ 

    (2) '상품'메뉴 밑으로 모든 메뉴는 출력안함.

    SELECT

            LEVEL AS LEV

          , MENU.*

    FROM    

            TBL_MENU  MENU

    START WITH          MENU.MENU_ID    = 'TOP'

    CONNECT BY PRIOR    MENU.MENU_ID    = MENU.UP_MENU_ID

    AND                 MENU.MENU_NAME  <> '상품'

    ORDER SIBLINGS BY   MENU_SEQ 

    ** 참고) 메뉴중 2LEVEL까지만 결과 출력

    (WHERE조건으로도 LEVEL <= 2가능함. 그러나 권하지는 않는다. 

     왜? WHERE 조건은 모든 나온결과에 대해서 FILTER하지만 CONNECT BY절의 조건으로 넣으면 순환자체를 안한다. 즉 성능에 좋다)

    SELECT

            LEVEL AS LEV

          , MENU.*

    FROM    

            TBL_MENU  MENU

    START WITH          MENU.MENU_ID    = 'TOP'

    CONNECT BY PRIOR    MENU.MENU_ID    = MENU.UP_MENU_ID

    AND                 LEVEL  <= 2

    ORDER SIBLINGS BY   MENU_SEQ     

◈ 

    (1) 들여쓰기로 결과출력

    SELECT

            LPAD(’ ’, 4*(LEVEL-1)) || MENU.MENU_NAME

          , MENU.*

    FROM

            TBL_MENU  MENU

    WHERE               MENU_TYPE_CD = '1'

    START WITH          MENU.MENU_ID = 'TOP'

    CONNECT BY PRIOR    MENU.MENU_ID = MENU.UP_MENU_ID

    ORDER SIBLINGS BY   MENU_SEQ

    (2) 엑셀과 같이 셀 단위로 들여쓰기

    SELECT 

             DECODE(LEV, '1', MENU_NAME, '') AS LEV1

           , DECODE(LEV, '2', MENU_NAME, '') AS LEV2

           , DECODE(LEV, '3', MENU_NAME, '') AS LEV3

           , DECODE(LEV, '4', MENU_NAME, '') AS LEV4

           , DECODE(LEV, '5', MENU_NAME, '') AS LEV5

    FROM

           (

             SELECT

                     LEVEL AS LEV

                   , MENU.*

             FROM    TBL_MENU  MENU

             WHERE               MENU_TYPE_CD = '1'

             START WITH          MENU.MENU_ID = 'TOP'

             CONNECT BY PRIOR    MENU.MENU_ID = MENU.UP_MENU_ID         

             ORDER SIBLINGS BY   MENU_SEQ 

           ) 

- DB 링크 생성방법 중에 TNSNAMES.ora 에 DB 정보를 추가하고 추가한 정보의 네이밍으로 연결하는방법과

  TNSNAMES.ora 추가없이 생성시 바로 정보를 넣는 방법 두가지가 있는데... 두번째 방법이 쉽고 편하늬까 그걸로...

  SQL> CREATE DATABASE LINK 'DB링크 별칭'

           CONNECT TO 'DB링크 접속 ID'

           IDENTIFIED BY 'DB링크 접속 PASSWORD'

           USING 'DB 접속정보'

  EX-SQL> CREATE DATABASE LINK DB_LINK_TEST

                CONNECT TO DANIEL

                IDENTIFIED BY DANIEL

                USING '(DESCRIPTION=
                                 (ADDRESS=
                                 (PROTOCOL=TCP)
                                 (HOST=98.28.5.111)
                                 (PORT=1521)
                            )
                                 (CONNECT_DATA=
                                 (SERVER=dedicated)
                                 (SERVICE_NAME=DANIEL)
                            )
                          )';

- DB LINK 사용

SQL> SELECT * FROM TABLE명@DB_LINK명

- DB LINK 삭제

SQL> DROP DATABASE LINK DB_LINK명

- 참 필요없는 기능인거 같지만, 프로젝트 나가면 소스를 보고 분석하기 귀찮고, 몬가 빨리 찾고싶을때 가끔 

  편리하게 쓰긴하지만.. 이러지말자.  

- SQL QUERY

SELECT *
   FROM USER_TAB_COMMENTS
 WHERE COMMENTS LIKE '%자가검침%';

- ORACLE MERGE INTO

  두개의 테이블을 비교한 후에 INSERT / UPDATE 할때 대단히 유용할 것 같다.

  적용할 테이블, 비교할 테이블의 값을 비교 후 존재하면 UPDATE, 없으면 INSERT

  정말 대박이다. 

시퀀스란?

• - 유일(UNIQUE)한 값을 생성해주는 오라클 객체이다.
• - 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성 할 수 있다.
• - 보통 PRIMARY KEY 값을 생성하기 위해 사용 한다.
• - 메모리에 Cache되었을 때 시퀀스값의 액세스 효율이 증가 한다.
• - 시퀀스는 테이블과는 독립적으로 저장되고 생성된다.

시퀀스 생성

-- 시퀀스 생성 Syntax
CREATE SEQUENCE sequence_name
    [START WITH n]
    [INCREMENT BY n]
    [MAXVALUE n | NOMAXVALUE]
    [MINVALUE n | NOMINVALUE]
    [CYCLE | NOCYCLE]
    [CACHE | NOCACHE]

• - START WITH : 시퀀스의 시작 값을 지정한다. n을 1로 지정하면 1부터 순차적으로 시퀀스번호가 증가 한다.
• - INCREMENT BY : 시퀀스의 증가 값을 지정한다. n을 2로 하면 2씩 증가한다. START WITH를 1로, INCREMENT BY를 2로 설정하면 1, 3, 5, 7,.. 이렇게 시퀀스 번호가 증가한다.
• - MAXVALUE : 시퀀스 최대값
• - MINVALUE : 시퀀스 최소값
• - CYCLE|NOCYCLE : 최대값 도달시 순환 여부
• - CACHE | NOCACHE : CACHE 여부, 원하는 숫자만큼 미리 만들어 Shared Pool의 Library Cache에 상주시킨다.

시퀀스 수정

START WITH 값을 제외하고 ALTER SEQUENCE 명령어로 수정 할 수 있다.

-- 시퀀스 수정 Syntax
ALTER SEQUENCE sequence_name
    [INCREMENT BY n]
    [MAXVALUE n | NOMAXVALUE]
    [MINVALUE n | NOMINVALUE]
    [CYCLE | NOCYCLE]
    [CACHE | NOCACHE]

시퀀스의 사용예

시퀀스 생성

empno를 자동 증가하는 시퀀스를 만들어 보자

-- 8000부터 시작하는 시퀀스를 생성하자
CREATE SEQUENCE  seq_empno
 START WITH  8000
INCREMENT BY 1
 CACHE 20;


-- 시퀀스가 정상적으로 생성되었는지 조회해 보자
SELECT seq_empno.NEXTVAL FROM DUAL;
SELECT seq_empno.NEXTVAL FROM DUAL;
SELECT seq_empno.CURRVAL FROM DUAL;

시퀀스를 이용해서 데이터를 등록해 보자

-- INSERT 시 시퀀스 사용
INSERT INTO emp 
        (empno, 
         ename, job, mgr, 
         hiredate, sal, comm, deptno)
VALUES
        (seq_empno.NEXTVAL, 
         'TIGER', 'MANAGER', 7839, 
         SYSDATE, 3000, null, 20);


-- SELECT 절에서 시퀀스 사용
CREATE TABLE emp2
AS 
SELECT seq_empno.NEXTVAL as empno, ename, 
       job, mgr, hiredate, sal, comm, deptno
FROM emp; 


-- 시퀀스가 정상적으로 반영되었는지 조회해 보자
SELECT * FROM emp;

시퀀스 수정

시퀀스 수정 예이다.

-- seq_empno  시퀀스의 증과값과 Cache 사이즈를 변경하는 예이다.
ALTER SEQUENCE  seq_empno
INCREMENT BY 10
CACHE 40;


-- 시퀀스가 정상적으로 변경되었는지 조회해 보자
SELECT seq_empno.NEXTVAL FROM DUAL;
SELECT seq_empno.NEXTVAL FROM DUAL;
SELECT seq_empno.NEXTVAL FROM DUAL;
SELECT seq_empno.CURRVAL FROM DUAL;

시퀀스 삭제

DROP SEQUENCE 명령으로 시퀀스를 삭제 할 수 있다.

-- seq_empno 시퀀스를 삭제한다.
DROP SEQUENCE seq_empno;

1. Single-Row Subquery

- SELECT 문장에서 오직 하나의 행(값)만 반환하는 Query이다

- 단일 행 연산자(=,>, >=, <, <=, <>, !=) 만 사용된다.

-- empno가 7369의 job을 조회 한 후 
-- job이 'CLERK'인 사원의 이름과 직업을 반환 한다.
SELECT ename,job
  FROM emp
 WHERE job = (SELECT job
                FROM emp
               WHERE empno = 7369);

• [그림] Single-Row Subquery
• 

 
-- 부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제
SELECT empno,ename,sal,deptno  
  FROM emp
 WHERE sal IN (SELECT MAX(sal)
                 FROM emp
                GROUP BY deptno);

EMPNO ENAME         SAL     DEPTNO
----- --------  --------- ---------
 7698 BLAKE        2850        30
 7788 SCOTT        3000        20
 7902 FORD         3000        20

 
-- SALESMAN 직업의 급여보다 많이 받는 사원의 사원명과 급여 정보를 출력하는 예제
SELECT ename, sal
  FROM emp
 WHERE deptno != 20
   AND sal > ANY (SELECT sal 
                    FROM emp 
                   WHERE job='SALESMAN');

ENAME             SAL
---------- ----------
ALLEN            1600
BLAKE            2850
CLARK            2450
...

 
-- 모든 SALESMAN직업의 급여보다 많이받는 사원의 사원명과 급여정보를 출력하는예제
SELECT ename, sal
  FROM emp
 WHERE deptno != 20
   AND sal > ALL (SELECT sal 
                    FROM emp 
                   WHERE job='SALESMAN');

ENAME             SAL
---------- --------
CLARK            2450
BLAKE            2850
KING             5000

 
-- 아래 예처럼 emp 테이블을 통해 사원들이 속한 부서번호의 정보만 조회하는 경우
-- 추출하고자 하는 대상은 dept 테이블이지만 emp 테이블과 조인하여 부서번호를 
-- 체크해야 한다.
-- 두 테이블의 관계가 1 : M 이므로 불필요하게 EMP 테이블을 모두 액세스하고 
-- DISTINCT로 중복 제거를 한다.
SELECT DISTINCT d.deptno, d.dname
  FROM dept d, emp e
 WHERE d.deptno = e.deptno;



-- EXISTS를 사용하는 Subquery로 변경
-- 추출하고자 하는 대상만을 FROM절에 놓고 emp테이블은 체크만 하기위해 
-- EXISTS절에 위치시켰으며 이로 인해 수행속도가 대폭 감소하게 된다.
SELECT d.deptno, d.dname
  FROM dept d
 WHERE EXISTS 
      (SELECT 1
         FROM emp e
        WHERE e.deptno = d.deptno);

3. Multiple-Column Subquery란?
결과 값이 두 개 이상의 컬럼을 반환하는 Subquery이다
Pairwise(쌍비교) Subquery
Subquery가 한 번 실행되면서 두 개 이상의 컬럼을 검색해서 주 쿼리로 넘겨 준다.
 
SELECT empno, sal, deptno
  FROM emp
 WHERE (sal, deptno) IN (SELECT sal, deptno
                          FROM emp
                         WHERE deptno = 30
                           AND comm is NOT NULL);

     EMPNO        SAL     DEPTNO
---------- ---------- ----------
      7521       1250         30
      7654       1250         30
      7844       1500         30
      7499       1600         30 
Nonpairwise(비쌍비교) Subquery
WHERE 절에서 두 개 이상의 서로 다른 Subquery가 사용되어서 결과 값을 주 쿼리로 넘겨 준다.
 
SELECT empno, sal, deptno
  FROM emp
 WHERE sal IN (SELECT sal
                 FROM emp
                WHERE deptno = 30
                  AND comm is NOT NULL);
  AND deptno IN (SELECT deptno
                   FROM emp
                  WHERE deptno = 30
                    AND comm is NOT NULL); 

     EMPNO        SAL     DEPTNO
---------- ---------- ----------
      7521       1250         30
      7654       1250         30
      7844       1500         30
      7499       1600         30 

Null Values in a Subquery
서브쿼리에서 NULL 값이 반환 되면 주 쿼리 에서는 어떠한 행도 반환되지 않는다.

4. Inline View (From절 Subquery)란?
FROM절에 오는 Subquery이다.
FROM절에서 원하는 데이터를 조회하여 가상의 집합을 만들어 조인을 수행하거나 가상의 집합을 다시 조회 할 때 사용한다.
Inlivew View 안에 또 다른 Inline View가 올 수 있다.
 
-- 부서번호 20의 평균 급여보다 크고, 
-- 부서번호 20에 속하지 않은 관리자를 조회하는 예제이다.
SELECT b.empno, b.ename, b.job, b.sal, b.deptno
  FROM (SELECT empno
          FROM emp  
         WHERE sal >(SELECT AVG(sal) 
                       FROM emp 
                      WHERE deptno = 20)) a, emp b
 WHERE a.empno = b.empno
   AND b.mgr is NOT NULL
   AND b.deptno != 20;

 EMPNO ENAME      JOB         SAL     DEPTNO
 ----- --------- --------- ------- ---------
  7698 BLAKE      MANAGER     2850        30
  7782 CLARK      MANAGER     2450        10

5. Scalar Subquery란?
SELECT 절에서 사용하는 Subquery 이다.
Scalar Subquery의 특징
한 개의 로우만 반환 한다.
메인 쿼리에서 추출되는 데이터 건 수 만큼 수행되기 때문에 조인으로 수행 될 때보다 수행회수가 적을 수 있다.
일치하는 값이 없는 경우 NULL을 반환 한다.
코드성 테이블에서 코드값을 조회 할 때, 불필요한 조인을 하지 않기 위해 많이 사용한다.
Scalar Subquery 예제
아래는 일반적인 Scalar Subquery 예이다.
 
-- 직업이 ‘MANAGER’인 사원의 사원명, 부서명을 조회하는 예제
SELECT ename,
       (SELECT dname FROM dept d WHERE d.deptno = e.deptno) deptno
FROM emp e
WHERE job ='MANAGER';

ENAME      DEPTNO
---------- --------------
JONES      RESEARCH
BLAKE      SALES
CLARK      ACCOUNTING
NULL값을 반환하는 Outer Join형태의 Scalar Subquery 예
 -- Scalar Subquery는 일치하는 값이 없으면 NULL을 반환하므로 Outer Join과 같은 형태이다.
-- 아래는 부서별 최대 급여정보 조회 예이다.
-- 부서 40에 해당하는 직원이 없기 때문에 최대 급여가 조회 되지 않는다.
SELECT d.deptno, d.dname,
       (SELECT MAX(sal) 
          FROM emp
         WHERE deptno = d.deptno) sal
  FROM dept d;



-- 결과가 동일한 Outer Join 예이다.
SELECT d.deptno, d.dname, MAX(e.sal)
  FROM dept d, emp e
 WHERE d.deptno = e.deptno(+)
 GROUP BY d.deptno, d.dname
 ORDER bY d.deptno;

DEPTNO DNAME          SAL
------ -----------  -----
    10 ACCOUNTING    5000
    20 RESEARCH      3000
    30 SALES         2850
    40 OPERATIONS