oracle - SQL관련 정리

SQL관련 정리(임시) 

 

[ CONCAT ]

- 두개의 문자열을 결합시켜 출력한다.

SELECT CONCAT('부서명은 : ',dept_nm) FROM CMMANDPTMNT

[OUTER JOIN]

- OUT조인 조건이 걸려있는 테이블에는 다른 조건절이 들어와도 

똑같이 OUT조인 연산자인 (+)를 해주어야 합니다.  

SELECT DISTINCT(a.dept_code), b.dep_code

FROM cmmandptmnt a, cmmanuser b

WHERE  a.dept_code(+) = b.dep_code

AND a.dept_code(+) LIKE '%';

[FULL OUTER JOIN]

-양쪽 테이블에 다 outer join을 거는것을 TWO-WAY OUTER JOIN 또는 FULL OUTER JOIN이라 합니다. 

SELECT DISTINCT(a.deptno), b.deptno 

FROM emp a FULL OUTER JOIN dept b 

ON a.deptno = b.deptno; 

INITCAP(char)

SELECT INITCAP('jun kyungmin') name from dual

- 첫문자를 대문자로 변화시켜준다.

LOWER(char) 

문자열을 소문자로 변환 시켜준다.

UPPER(char) 

문자열을 대문자로 변환시켜준다.

LPAD(char1, n [,char2])

- 왼쪽에 문자열을 끼어 놓는 역할을 합니다. n은 반환되는 문자열의 전체 길이를 나타내며, 

char1의 문자열이 n보다 클 경우 char1을 n개 문자열 만큼 반환합니다. 

SELECT LPAD('JUNG-SICK', 10, '*') NAME FROM dual 

결과)

JUNG-SIC

JUNG-SICK

*JUNG-SICK

**JUNG-SICK

SUBSTR(char, m ,[n]) 

-SUBSTR함수를 이용하여 m 번째 자리부터 길이가 n개인 문자열을 반환한 합니다. 

m이 음수일 경우에는 뒤에서 M번째 문자부터 반대 방향으로 n개의 문자를 반환합니다. 

SELECT SUBSTR('JUNG-SICK', 3, 3) NAME FROM dual ; 

LENGTH(char1) 

-문자열의 길이를 리턴 합니다. 

SELECT LENGTH('JUNG-SICK') TEST FROM dual ; 

REPLACE(char1, str1, str2) 

-REPLACE는 문자열의 특정 문자를 다른 문자로 변환 합니다. 

SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL;

INSTR

- 문자열이 포함되어 있는지를 조사하여 문자열의 위치를 반환합니다. 

- 지정한 문자열이 발견되지 않으면 0이 반환 됩니다. 

SELECT INSTR('CORPORATE FLOOR','OK')  "Instring" FROM DUAL

-- 왼쪽에서 3번째부터 시작을 해서 비교를 합니다. 2번째 OR의 위치가 반환 됩니다. 

SELECT INSTR('CORPORATE FLOOR','OR', 3)  "Instring" FROM DUAL 

  Instring

----------

      5

-- 왼쪽에서 3번째부터 시작을 해서 비교를 하는데  OR이 두 번째 검색되는 지점의 위치를 반환 합니다. 

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)  "Instring" FROM DUAL;

  Instring

----------

     14

TRIM 

 - 특정한 문자를 제거 합니다.  

 - 제거할 문자를 입력하지 않으면 기본적으로 공백이 제거 됩니다.

 - 리턴값의 데이터타입은 VARCHAR2 입니다.

SELECT TRIM(0 FROM 0009872348900)  "TRIM Example" FROM DUAL;

LTRIM

SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example"  FROM DUAL;

RTRIM

SQL>SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM example"     FROM DUAL;

TO_CHAR 

 - TO_CHAR함수는 DATE형, NUMBER형을 VARCHAR2형으로 바꺼 줍니다. 

SELECT TO_CHAR(SYSDATE, 'MONTH') CHARTEST FROM dual ; 

TO_DATE 

 - TO_DATE함수는 CHAR, VARCHAR2형을 DATE 타입으로 변환합니다. 

SELECT TO_DATE('2000/06/16','RRRR/MM/DD') FROM dual ; 

 

TO_NUMBER 

 - TO_NUMBER함수는 CHAR, VARCHAR2의 데이터 타입을 숫자형식으로 변환합니다. 

SELECT TO_NUMBER('1210616') FROM dual ; 

테스트를 위한 테이블 생성

 - 완전히 똑같은 테이블이 생성되는 것은 아니다. (데이터 백업용으로 적합할듯)

CREATE TABLE emp_test

AS SELECT * FROM emp WHERE deptno = 10;

MERGE

 - 한번에 조건에 따라 INSERT,UPDATE 가 가능합니다.

 - 해당 ROW가 있으면 UPDATE, 없으면 INSERT 문장이 실행 됩니다.

 ◈ syntax 설명

 - INTO : DATA가 UPDATE되거나 INSERT될 TABLE이름을 지정 합니다. 

 - USING : 대상 TABLE의 DATA와 비교한 후 UPDATE 또는 INSERT할 대상이 되는 DATA의 SOURCE 테이블 또는 뷰를 지정

 - ON  : UPDATE나 INSERT를 하게 될 조건으로, 해당 condition을 만족하는 DATA가 있으면 WHEN MATCHED 절을 실행하게 되고, 없으면 WHEN NOT MATCHED 이하를 실행하게 됩니다. 

 - WHEN MATCHED : ON 조건절이 TRUE인 ROW에 수행 할 내용 

 - WHEN NOT MATCHED : ON 조건절에 맞는 ROW가 없을 때 수행할 내용 

 MERGE INTO SLFANDUTORG_TEST et

    USING SLFANDUTORG_TEST E // 본 예제는 테이블간 비교가 아니기에 여기서 사용된 테이블은 형식을 맞추기위함

    ON(et.DUT_ORG_CODE <> 'A01') // 조건은 '='나 '<>'사용하면된다.

 WHEN MATCHED THEN 

    UPDATE SET REM = 'TEST1' // 항상 업데이트문이 와야한다.

 WHEN NOT MATCHED THEN 

    INSERT VALUES ('B01','본청3','on','1','','1100001234561','20060913','B01',

    '0','부산광역시 강동구','A','011-111-1111','1010','1210') // 직접 값을 써주었지만. 해담 컬럼명을 써주어도 된다.

CASE

 1) simple CASE expression

  - DECODE 함수와 비슷하다고 보시면 됩니다. 

SQL>SELECT deptno, CASE deptno

               WHEN 10 THEN 'ACCOUNTING'

               WHEN 20 THEN 'RESEARCH'

               WHEN 30 THEN 'SALES'

               ELSE 'OPERATIONS'

            END as "Dept Name"

        FROM emp

    

    

 2) searched CASE expression

  - IF.. THEN .. ELSE 구문과 비슷 합니다.

  - WHEN절 다음에 여러 조건이 올 수 있습니다.

SQL>INSERT INTO raise

    SELECT last_name ,

           CASE

              WHEN job_id LIKE 'AD%' THEN '10%'

              WHEN job_id LIKE 'IT%' THEN '15%'

              WHEN job_id LIKE 'PU%' THEN '18%'

              ELSE '20%'

           END

    FROM employees ;

NULLIF 

  - NULLIF(exp1, exp2)

  - exp1값과 exp2값이 동일하면 NULL을 그렇지 않으면 exp1을 반환 합니다.

  - exp1는 NULL이 올 수 없습니다.

  - CASE WHEN expr1 = expr2

         THEN NULL

         ELSE expr1

    END 

SQL>SELECT DUT_ORG_CODE,

    CASE WHEN DUT_ORG_CODE = 'A01'

         THEN (SELECT DUT_ORG_NM || '이지롱' FROM SLFANDUTORG WHERE DUT_ORG_CODE = 'A01')

         ELSE 'B01'

    END AS TEST

    FROM SLFANDUTORG