Royalvip's Second Brain

펌 url : http://www.koug.net/bbs/view.php?id=oracle_tiptech&no=294

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

SQL은 CASE문장의 네가지 유형을 지원합니다.

1. 간단한 CASE 표현식
2. 검색 CASE표현식
3. NULLIF
4. COALESCE

이중에서 간단한 CASE표현식은 Oracle 8.1.7 에서 제공되었으며, 나머지 기능들은  Oracle 9i에 포함됩니다. 또한 CASE표현식은 PL/SQL에서도 지원(9i 이후부터)되며, 표현식과 문장으로 분류됩니다.

간단한 CASE 표현식
------------------------------------------------------------------
이것은 Oracle 9i 이전의 서버에서 제공하던 DECODE 문장과 유사하며 주어진 표현식에서 검색을 하거나 값을 치환하는데 사용될 수 있습니다. 또 각각의 검색 값에 대해서 반환되는 값을 명시할 수 있으나, 비교 연산자는 허용되지 않습니다.

SQL> SELECT ename ,
   2     (CASE EXTRACT(YEAR FROM hiredate)
   3       WHEN 1982 THEN '3 years service'
   4       WHEN 1981 THEN '4 years service'
   5       WHEN 1980 THEN '5 years service'
   6       END) AS "Award for 2000"
   7         FROM emp
   8       WHERE EXTRACT(YEAR FROM hiredate) IN (1982, 1981, 1980)
   9      ORDER BY hiredate ;

검색 CASE 표현식
-----------------------------------------------------------------
검색 CASE 표현식은 IF...THEN ELSE 구조와 유사하며, 표현식 내에서 조건에 따른 검색과  값들의 치환을 하는데 사용될 수 있다. 각 WHEN 조건은 논리적 연산자(AND, OR등)와 결합되어 사용될 수 있으며, 조건 표현식에서 비교 연산자를 사용할 수 있다. 따라서, 간단한 CASE 표현식보다는 유연하다고 할 수 있다. 예를 들어 EMP 테이블에서 Sal 컬럼의 범위에 따른 영역을 표현하는 SQL문과 결과는 다음과 같다.

SQL> SELECT empno, ename,
   2     (CASE
   3       WHEN sal >= 5000 THEN 'High Sal'
   4       WHEN sal >= 3000 AND sal <= 5000 THEN 'Middle Sal'
   5       WHEN sal >= 1000 AND sal <  3000 THEN 'Average Sal'
   6       WHEN sal <   1000 THEN 'Low Sal'
   7       END) AS Sal_Category
   8       FROM emp ;

NULLIF 함수
-----------------------------------------------------------------
NULLIF 함수의 경우 첫번째 입력인자와 두번째 입력인자가 같은 경우에는 NULL을 반환하고, 그렇지 않을 경우에는 첫번째 입력인자의 값을 반환한다. 예를 들어 EMP 테이블의 사원중에 Sal 컬럼의 값이 5000인 사람에 대해 NULLIF함수를 적용하는 SQL문과 결과는 다음과 같다.

SQL> SELECT empno, sal
   2         FROM emp
   3      WHERE NULLIF(sal, 5000) IS NULL ;

만약 NULLIF 함수를 CASE표현식으로 나타낸다면 다음과 같다.

CASE
       WHEN expression1 = expression2 THEN NULL
        ELSE expression1
END ;

COALESCE 함수
-----------------------------------------------------------------
COALESCE 함수는 Oracle의 NVL함수를 일반화한 것이다. 즉, 첫번째 입력 인자가 null이 아닌 경우에는 첫번째 입력인자를 반환하고, 아닐 경우에는 두번재 표현식에 대해서 평가를 계속하게 된다. 예를 들어, EMP 테이블에서 COMM 컬럼의 값이 널인지 검사하는 SQL문은 다음과 같다.

SQL> SELECT ename, COALESCE(comm, 'NULL')
   2         FROM emp ;

NULLIF함수와 유사하게 CASE표현식으로 나타내면 다음과 같다.

CASE
        WHEN expression1 IS NOT NULL THEN expression1
         ELSE expression2
END ;

또한, NVL함수에 비해 COALESCE함수의 장점은 여러 대체 값을 취할 수 있다는 것이다. 예를 들어, 첫번째 입력 인자가 null일 경우에는 나머지 표현식에 대해서 COALESCE를 계속 적용하는 것이 가능하다.

1. 숫자함수
ABS(n) : ABS함수는 절대값을 계산하는 함수입니다.
CEIL(n) : CEIL함수는 주어진 값보다는 크지만 가장 근접하는 최소값을 구하는 함수입니다. EXP(n) : EXP함수는 주어진 값의 e의 승수를 나타냅니다. e는 2.171828183..입니다.
FLOOR(n) : FLOOR함수는 주어진 값보다 작거나 같은 최대 정수값을 구하는 함수입니다. (CEIL 함수와 비교해 보세요. )
LN(n) : LN함수는 주어진 값의 자연로그 값을 반환합니다.
MOD(m, n) : MOD함수는 m을 n으로 나누어 남은 값을 반환한다. n이 0일 경우 m을 반환합니다. POWER(m, n) : POWER함수는 m의 n승 값을 계산합니다.
ROUND(n, [m]) : ROUND함수는 n값의 반올림을 하는 함수로 m은 소숫점 아래 자릿수를 나타낸다. SIGN(n) : SIGN함수는 n<0일 경우 -1DFM N=0일 경우 0을 N>0일 경우 1을 반환합니다.
SQRT(n) : SQRT함수는 n값의 루트값을 계산한다. n은 양수여야 합니다.
TRUNC(n, m) : TRUNC함수는 n값을 m 소숫점 자리로 반내림한 값을 반환합니다. (ROUND 함수와 비교해 보세요.)

2. 문자열 처리함수
CONCAT(char1, char2) : CONCAT 함수는 Concatenation의 약자로 두 문자를 결합하는 역할을 합니다. "||" 연산자와 같은 역할을 합니다.
INITCAP(char) : 주어진 문자열의 첫 번째 문자를 대문자로 변환시켜 줍니다.
LOWER(char) : 문자열을 소문자로 변환 시켜 줍니다.
UPPER(char) : 문자열을 대문자로 변환 시켜 줍니다.
LPAD(char1, n [,char2]) :왼쪽에 문자열을 끼어 놓는 역할을 합니다. n은 반환되는 문자열의 전체 길이를 나타내며, char1의 문자열이 n보다 클 경우 char1을 n개 문자열 만큼 반환합니다.
RPAD(char1, n [,char2]) : LPAD와 반대로 오른쪽에 문자열을 끼어 놓는 역할을 합니다.
SUBSTR(char, m ,[n]) : SUBSTR함수를 이용하여 m 번째 자리부터 길이가 n개인 문자열을 반환한 합니다. m이 음수일 경우에는 뒤에서 M번째 문자부터 반대 방향으로 n개의 문자를 반환합니다. LENGTH(char1) : 문자열의 길이를 리턴 합니다.
REPLACE(char1, str1, str2) : REPLACE는 문자열의 특정 문자를 다른 문자로 변환 합니다. INSTR : 문자열이 포함되어 있는지를 조사하여 문자열의 위치를 반환합니다. 지정한 문자열이 발견되지 않으면 0이 반환 됩니다. TRIM : 특정한 문자를 제거 합니다. 제거할 문자를 입력하지 않으면 기본적으로 공백이 제거 됩니다. 리턴값의 데이터타입은 VARCHAR2 입니다. -- 두개의 문자를 연결한다.

SQL>SELECT CONCAT('Oracle', ' Korea') NAME FROM dual; -- 문자좌측에 *를 붙인다. 문자 길이는 10개로 제한

SQL>SELECT LPAD('JUNG-SICK', 10, '*') NAME FROM dual; -- 문자우측에 *를 붙인다. 문자 길이는 11개로 제한

SQL>SELECT RPAD('JUNG-SICK', 11, '*') NAME FROM dual; -- 앞에서부터, 뒤에서부터 예제...
SQL>SELECT SUBSTR('JUNG-SICK', 3, 3) NAME FROM dual;

SQL>SELECT SUBSTR('JUNG-SICK', -3, 3) NAME FROM dual; -- 문자의 길이를 구함

SQL>SELECT LENGTH('JUNG-SICK') TEST FROM dual; -- 대소문자 구별합니닷!!

SQL>SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM dual;

SQL>SELECT REPLACE('JACK and JUE','j','BL') "Changes" FROM dual; -- 지정한 문자 OK가 발견되지 않아서 0이 반환 됩니다.

SQL>SELECT INSTR('CORPORATE FLOOR','OK') "Instring" FROM dual; -- OR이 있는 위치 2를 반환 합니다. 왼쪽부터 비교를 한다는 것을 알 수 있습니다.

SQL>SELECT INSTR('CORPORATE FLOOR','OR') "Instring" FROM dual; -- 0을 제거 합니다.

SQL>SELECT TRIM(0 FROM 0009872348900) "TRIM Example" FROM dual; -- 어떤 문자도 입력하지 않으면 기본적으로 공백이 제거 됩니다. -- TRIM을 사용한 위에 예제와 사용하지 않은 아래 예제의 결과 값이 다르게 나오는 것을 알 수 있습니다. -- NVL(a,b) 함수는 a가 NULL일 경우 b로 바꿔주는 함수입니다.
SQL>SELECT NVL(TRIM (' '),'공백') "TRIM Example" FROM dual; 3. 날짜 처리함수 LAST_DAY(d) : LAST_DAY함수는 달의 마지막 날의 날짜를 반환합니다.

ADD_MONTHS(a, b) : ADD_MONTHS 함수는 a의 날짜에 b의 달을 더한 값을 반환 합니다. MONTH_BETWEEN(a1, a2) : MONTH_BETWEEN은 a1과 a2 사이의 달의 수를 NUMBER형 타입으로 반환 합니다.
ROUND(d[,F]) : ROUND 함수는 F에 지정된 단위로 반올림 합니다. F가 연도라면 연도 단위로 반올림 합니다. -- 오늘과 이달의 마지막날 반환

SQL>SELECT SYSDATE TODAY, LAST_DAY(SYSDATE) LASTDAY FROM dual; -- 오늘에서 3개월을 더한 후, RRRR/MM/DD 형식으로 반환

SQL>SELECT TO_CAHR(ADD_MONTHS(SYSDATE,3),'YYYY/MM/DD') "date" FROM dual; -- 두 날짜 사이의 개월수를 반환 SQL>SELECT MONTHS_BETWEEN(TO_DATE('2000/06/05') ,

TO_DATE('2000/09/23')) "Date" FROM dual; -- '1998/09/11' 의 년도인 1998을 반환

SQL>SELECT ROUND(TO_DATE('1998/09/11'), 'YEAR') FROM dual;
 
4. 변환함수

TO_CHAR :
TO_CHAR함수는 DATE형, NUMBER형을 VARCHAR2형으로 바꿔줍니다.
TO_DATE : TO_DATE함수는 CHAR, VARCHAR2형을 DATE 타입으로 변환합니다.
TO_NUMBER : TO_NUMBER함수는 CHAR, VARCHAR2의 데이터 타입을 숫자형식으로 변환합니다. -- 오늘의 월만 반환
SQL>SELECT TO_CHAR(sysdate, 'MONTH') CHARTEST FROM dual;

-- 문자형이 날짜형으로 변환
SQL>SELECT TO_DATE('2000/06/16','YYYY/MM/DD') FROM dual;

-- '1210616'의 문자열이 숫자형으로 변환
SQL>SELECT TO_NUMBER('1210616') FROM dual; 5. 기타함수 NVL - NVL 함수는 NULL값을 다른 값으로 바꿀 때 쓰입니다. - 모든 데이터 타입에 적용 가능합니다. - 전환되는 값의 데이터 타입을 일치시켜야 합니다.

DECODE - DECODE 함수는 데이터 들을 다른 값으로 바꾸어 줍니다.
- 형식 DECODE(VALUE, IF1, THEN1, IF2, THEN2...) - VALUE 값이 IF1일경우에 THEN1값으로 바꾸어 주고 VALUE값이 IF2일경우에는 THEN2값으로 바꾸어 줍니다.
DUMP : DUMP는 바이트 크기와 해당 데이터 타입 코드를 반환합니다.
GREATEST : GREATEST함수는 검색값 중에서 가장 큰 값을 반환 합니다.
LEAST : LEAST함수는 GREATEST함수와 반대로 가장 작은 값을 반환합니다.
UID : 현재 사용자의 유일한 ID번호를 반환 합니다.
USER : 현재 오라클을 사용하는 사용자를 VARCHAR2형식으로 반환 합니다.
USERENV : USERENV 함수는 현재 세션의 환경 정보를 반환합니다.
 - ENTRYID : 사용 가능한 Auditing entry Identifier를 반환합니다.
 - LABEL : 현재 세션의 Label을 반환합니다.
 - LANGUAGE : 현재 세션에서 사용중인 언어와 테리토리 값을 반환합니다.
 - SESSIONID : Auditing(감사) Session ID를 반환 합니다.
 - TERMINAL : 현재 세션 터미널의 OS ID를 반환 합니다.

VSIZE : 해당 문자의 BYTE수를 반환 합니다. 해당 문자가 NULL이면 NULL값이 반환 됩니다.

-- 컬럼 comm의 값이 NULL일 경우, 0을 반환합니다.
SQL>SELECT empno, NVL(comm, 0) FROM emp WHERE deptno = 30;

-- 컬럼 deptno의 값이 10이며 'ACCOUNTING'으로
-- 컬럼 deptno의 값이 20이며 'RESEARCH'으로
-- 컬럼 deptno의 값이 30이며 'SALES'으로
-- 컬럼 deptno의 값이 40이며 'OPERATIONS'으로
SQL>SELECT deptno, DECODE(deptno, 10 , 'ACCOUNTING', 20 , 'RESEARCH', 30 , 'SALES', 40 , 'OPERATIONS') FROM emp;

-- 만약에 16대신 8을 넣으면 8진수로, 10를 넣으면 10진수로 변환이 됩니다.
-- 16, 10, 8, 17이 올수 있는데요 17은 단일 문자열을 반환합니다. -- 결과값 중, Len은 ename의 해당 byte수 입니다.
SQL>SELECT ename, DUMP(ename, 16) "16진수" FROM emp WHERE ename = 'ALLEN'
-- 가장 큰수/가장 작은수 반환 SQL>SELECT GREATEST(10, 100, 5, -7) FROM dual;

SQL>SELECT LEAST(10, 100, 5, -7) FROM dual; -- 현재 사용자의 이름과 ID값을 반환 SQL>
SELECT USER, UID FROM DUAL; -- 현재 세션의 환경정보를 반환 SQL> SELECT USERENV('LANGUAGE') FROM dual; -- 현재 문자의 byte 수를 반환

 SQL> SELECT VSIZE(ename), ename FROM emp WHERE deptno = 30; 출처 : http://savour.tistory.com

1. 두 날짜 데이터간 일수 차이

select  to_date(col1, 'yyyymmdd')- to_date(col2, 'yyyymmdd') 

from table

2.MONTHS_BETWEEN(date1, date2) : date1과 date2 사이의 달수 차이

date1이 빠른날짜면 양수

date2가 빠른 날짜면 음수

3.ADD_MONTHS(date, n) : date에 n달을 추가.

n은 정수

4.NEXT_DAY(date,'char') : 요일 찾기

-- 테이블스페이스 삭제
drop tablespace ex including contents;

-- 테이블 스페이스 생성
CREATE TABLESPACE ex  DATAFILE 'C:/oracle/product/10.2.0/oradata/ex/ex.DBF' SIZE 300M  AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

-- 테이블 스페이스를 이용할 유저 생성
create user ex identified by ex default tablespace ex;

-- 생성한 유저에게 데이터베이스 관리자 권한 부여
grant connect,dba to ex;

<hr>
세부사용법

# SQLPLUS CONNECT - sqlplus system/manager - sqlplus "/as sysdba" /* DB관리자로 접속 */

# TABLESPACE 생성 CREATE TABLESPACE 스페이스명 /* 생성될 테이블스페이스 이름 */ DATAFILE '파일경로/파일명_DAT.DBF' SIZE 100M /* 생성될 데이터 파일의 경로위치와 크기 */
AUTOEXTEND ON NEXT 10M /* 데이터 파일 용량 초과시 자동증가 설정(ON/OFF : AUTOEXTEND OFF), 초과시 다음 자동 증가 용량(OFF시 생략) */
MAXSIZE 500M /* 데이터 파일 최대 크기(OFF시 생략) */
DEFAULT STORAGE( INITIAL 10K /* 테이블 스페이스의 맨 첫번째 Extents의 크기 */
NEXT 10K /* 다음 Extents의 크기 */
MINEXTENTS 2 /* 생성할 Extents의 최소 값 */
MAXEXTENTS 50 /* 생성할 Extents의 최대 값 */
PCTINCREASE 50) /* Extents의 증가율, Default값은 50 입니다 */
ONLINE; /* ONLINE : 사용가능하게 활성화 | OFFLINE : 사용할수없도록 비활성화 */
 
# TEMPORARY TABLESPACE 생성
CREATE TEMPORARY TABLESPACE TEMP스페이스명 TEMPFILE '파일경로/파일명_TMP.DBF' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 500M DEFAULT STORAGE( INITIAL 10K NEXT 10K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 50) ONLINE;
 
# 사용자 생성 CREATE USER 사용자아이디 IDENTIFIED BY 패스워드 DEFAULT TABLESPACE 스페이스명 TEMPORARY TABLESPACE TEMP스페이스명;
 
# 사용자 권한부여 GRANT CONNECT, DBA, RESOURCE TO 사용자아이디;
 
# 사용자 비밀번호변경 ALTER USER 사용자아이디 IDENTIFIED BY 패스워드