새해 복 많이 받으세요.
어제에 이어 DML문 종류인 WHERE, ORDER BY 절, 그리고 단일행 함수을 복습해 봅니다.
직접 tool에서 연습을 해보면 손맛(?)에 익혀 쉽게 외워질수 있는 함수입니다.
복습하면서 FROM을 생략하는 경우가 많습니다. 문맥상 이해하시고 연습해보세요.
처음 단일행 함수를 테스트할 때는 dual 테이블을 많이 사용하고 있습니다.
WHERE 절은 무엇이고 주의사항은?
쉽게 말해서 하나의 조건을 따오는 것입니다. 처리되는 행을 선택한다는 것이 일반적인 정의라고 볼 수 있습니다.
다음은 간단한 주의사항입니다.
1) WHERE절에 오는 조건의 상수는 숫자와 문자가 가능합니다.
다만, 숫자 상수의 경우 따옴표 ( ' ' ) 의 생략이 가능하지만 문자의 경우 ( '콩쓰' ) 와 같이 붙여주셔야 합니다.
2) WHERE절에 적용된 데이터 형식이 세션(사용자)의 표시형식과 다를 경우 에러가 발생합니다.
ex) 현재 세션이 영미권으로 설치된 경우라면 세션의 DATE 형식은 'DD-MON-RR (미국식)' 이와 같습니다.
그런데, 조건을 다음과 같이 주어지면 에러가 발생합니다.
WHERE hire_date >= '2008/01/01' ; DATE형식이 한국방식인 (YYYY/MM/DD) 이기 때문입니다.
-----> 에러 수정 방법은 이후 복습합니다. 수정 방법은 형변환 혹은 세션을 바꿔주면 됩니다.
3) WHERE절에서는 SELECT에서 컬럼에 선언한 별명으로는 넣을 수 없습니다.
ex) SELECT employee_id AS "emp_id"
FROM hr.employees
WHERE emp_id = 100; (x)
--> ORA-00904 invalid identifier ; (부적절한 식별자)가 오류 발생한다.
--> WHERE employee_id = 100; (o)
WHERE절에 사용되는 연산자들은?
매우 유용하게 사용하는 연산자들입니다.
1) 단순 비교 연산자
= ; 같다
<> , != , ^= ; 같지 않다
> ; 크다
< ; 작다
>= ; 크거나 같다
<= ; 작거나 같다
ex) WHERE salary <= 10000 ;
- 봉급 컬럼에서 10000 보다 작거나 같은 조건을 선택합니다.
2) BETWEEN A AND B 연산자 ; B가 A보다 작으면 오류 발생, A,B 값 형식이 같아야 한다.
ex) WHERE salary BETWEEN 5000 AND 10000 ;
- 5000보다 크거나 같고 10000보다 작거나 같습니다. 즉 A>=5000, B<=10000
3) IN 연산자 ; 컬럼 값이 명시된 값들 중 하나와 같은지 비교합니다. 명시된 값 형식은 동일해야 한다.
ex) WHERE manager_id IN (100, 101, 201);
- 매니저(상관) ID의 100, 101, 201 조건을 선택한다.
4) LIKE 연산자 ; 명시된 문자열의 패턴을 비교한다. '%, _' 을 사용한다.
ex) WHERE last_name LIKE '%A%' ;
- 이름 컬럼에서 A가 들어있는 조건을 선택한다.
WHERE last_name LIKE '_b_%'
- 이름 컬럼에서 b가 양쪽으로 문자가 있는 조건을 선택한다.
ESCAPE(\)문자와 같이 사용하는 LIKE 조건
WHERE jcode LIKE 'AD\_%' ESCAPE '\';
- '_'는 연산자이지만 '\'문자내에서는 일반 문자로 인식한다.
즉 결과는 AD_가 들어가는 레코드가 선택된다.
WHERE절에서 NULL 사용?
WHERE절에서도 NULL을 사용할 수 있다. 하지만 '=' 을 사용하지 않는다.
IS NULL, IS NOT NULL 두가지를 활용하여 쿼리문을 작성한다.
ex) WHERE manager_id IS NULL ;
논리 연산자 AND OR NOT 사용?
WHERE절에서 두가지 조건을 동시에 부여하고자 할 때 사용한다.
AND ; A,B 두 조건 모두 만족할 때 사용
OR ; A,B 두 조건중 하나를 만족할 때 사용
NOT ; A 조건을 만족하지 않는 행만 처리할 때 사용한다.
NOT연산자는 연산자를 부정하는 방법에 많이 사용한다.
ex) WHERE manager_id IS NULL AND/OR salary > 10000 ;
WHERE NOT (manager_id IS NULL) ;
ORDER BY 절은 무엇인가?
결과 레코드를 정렬할 때 사용한다. 반드시 자리는 SELECT 문 마지막에 위치한다.
ORDER BY 절에는 3가지 형식이 아래와 같이 올 수 있다. ASC/DESC는 그 뒤에 위치한다.
ASC 정렬이 기본 DEAFAULT 값이다.
1) ORDER BY employee_id -- 컬럼으로 정렬
2) ORDER BY emp -- employee_id의 별명(alias)
3) ORDER BY 1 -- SELECT문에서 employee_id 가 있을 경우 그것을 기준으로 정렬)
ex) ORDER BY 1 ASC -- 3)의 기준에서 오름차순(낮은수부터 높은수로) 정렬한다.
ORDER BY절에 NULL 상태가 포함된다면?
NULL상태 정렬의 경우 ASC 가장 높은 값 그 아래에 위치한다. DESC 사용할 때는 가장 최상단에 위치한다.
만약, NULLS FIRST, NULLS LAST를 명시하게 되면 ASC/DESC 상관없이 각 명시된 위치에 위치한다.
ex) ORDER BY 2 ASC NULLS FIRST ; -- NULL은 결과 최상단에 위치한다.
ORDER BY department_id DESC NULLS LAST ; -- NULL은 결과 최하단에 위치한다.
단일 행 함수(SINGLE ROW FUNCTION)는 무엇인가?
함수는 기본 문법으로서 중요하다. 필히 암기하도록 한다. 참고로 PL/SQL에서는 함수를 많이 이용한다.
이 부분은 암기와 반복적인 연습이 중요하다.
단일 행 함수와 다중 행 함수(수학적 연산)가 있다.
기본적으로 함수 연습을 할 때 HR계정의 테이블이 아닌, DUAL이라는 오라클 서버 테이블을 사용한다.
함수 테스트를 위하여 오라클 서버에 접속하는 모든 사용자가 이용할 수 있다.
기존 hr계정 테이블에서 사용하고자 하면, 데이터가 변경될 수 있으니 반드시 ROLLBACK 명령어를 사용하자.
ROLLBACK 명령어는 이후 복습에서 다룬다.
ex) SELECT UPPER('abcd') -- abcd 대문자 표기
FROM dual;
단일행 함수의 분류는 어떻게 될까?
- 문자함수(CHARACTER FUNCTION) ; 처리되는 데이터가 문자 형식이다.
- 숫자함수(NUMBER FUNCTION) ; 처리되는 데이터가 숫자 형식이다.
- 날짜함수(DATE FUNCTION) ; 처리되는 데이터가 날짜 형식이다.
- 일반함수(GENERAL FUNCTION) ; 처리되는 데이터가 데이터 형식에 제한이 없다.
- 형변환함수(CONVERSION FUNCTION) ; 처리되는 데이터가 다른 데이터 형식으로 변경할 필요가 있을때 사용.
1) 문자 --> 숫자 ; TO_NUMBER()
2) 문자 --> 날짜 ; TO_DATE()
3) 숫자, 날짜 --> 문자 ; TO_CHAR()
1) UPPER ; 해당 조건이 명시된 (컬럼/행) 레코드를 전부 대문자로 변경한다.
ex) SELECT UPPER('abcd') -- abcd 레코드는 전부 대문자로 변경된다.
2) LOWER ; 해당 조건이 명시된 컬럼(행)의 레코드를 전부 소문자로 변경한다.
ex) SELECT UPPER('ABCD') --ABCD 레코드는 전부 대문자로 변경된다.
3) INITCAP ; 해당 조건이 명시된 컬럼(행)의 레코드를 첫 문자만 대문자 나머지는 소문자로 변경한다.
ex) SELECT INITCAP ('aBCD') -- Abcd의 결과로 변경된다.
4) CONCAT ; 두 문자열을 붙입니다. ( SELECT문에서 배운 '||' 연산자와 기능이 같습니다)
ex) SELECT CONCAT('ABC', 'DEF') -- ABCDEF 결과가 나타난다.
5) SUBSTR ; 데이터로부터 원하는 문자(열)을 출력한다.
ex1) SELECT SUBSTR('200831001', 1, 4) -- 1번째 글자부터 4번째 글자까지 출력한다.
ex2) SELECT SUBSTR('200831001', 7) -- 7번째 글자부터 끝까지 출력한다.
ex3) SELECT SUBSTR('200831001', -2) -- 뒤에서 2번째 글자부터 끝까지 출력한다.
6) TRIM ; 문자열의 양 끝의 연속된 문자를 삭제한다. 버전에 따라 방식이 다양하다.
ex1) SELECT TRIM(BOTH 'S' FROM 'SSMISTHSS'), TRIM('S' FROM 'SSMISTHSS')
-- 양쪽의 S (연속된) 부분을 삭제한다.
ex2) SELECT TRIM(LEADING 'S' FROM 'SSMISTHSS')
-- 앞쪽의 S (연속된) 부분을 삭제한다.
ex3) SELECT TRIM(TRAILING 'S' FROM 'SSMISTHSS')
-- 뒷쪽의 S (연속된) 부분을 삭제한다.
ex4) SELECT RTRIM/LTRIM('SSMISTHSS', 'S')
-- 오른쪽/왼쪽의 S (연속된) 부분을 삭제한다.
ex5) SELECT RTRIM(LTRIM('SSMISTHSS', 'S'), 'S')
-- 왼쪽의 S (연속된) 부분을 삭제하고, 오른쪽의 S (연속된) 부분을 삭제한다.
7) LENGTH ; 문자열의 글자수를 숫자로 출력한다. (주의) BYTE단위가 아니다.
ex) SELECT LENGTH('KONGSSS') -- 7이라는 결과가 출력된다.
8) INSTR ; 특정 문자나, 문자열이 있다면 그 문자(열)의 시작된 자리 수를 반환하며 없으면 0으로 출력한다.
ex1) SELECT INSTR('KONGSSS', 'K', 1, 1)
-- KONGSSS 문자열에서 K로 시작하는 문자를 1번째부터 검색하여 1번째로 나온 결과를 출력한다.
ex2) SELECT INSTR('KONGSSS', 'S', 1, 3)
-- KONGSSS 문자열에서 S로 시작하는 문자를 1번째부터 검색하여 3번째로 나온 결과를 출력한다. ex3) SELECT INSTR('KONG SSS', 'S', -5, 2) -- KONGSSS 문자열에서 S로 시작하는 문자를 뒤에서 5번째부터 검색하여 2번째로 나온 결과를 출력. 9) LPAD/RPAD ; 좌/우에 특정 Byte수만큼 문자를 채운다. 주의) 영어는 1Byte, 한글은 2Byte이다. ex) SELECT LPAD('TEACHER', 10, '*'); -- TEACHER 문자는 7바이트이므로 왼쪽에 3바이트만큼 * 문자를 채워준다. -- RPAD의 경우는 오른쪽에 3바이트만큼 * 문자를 채워준다. 10) REPLACE ; 문자열에서 문자(열)을 찾아서 해당 문자(열)로 바꿔준다. ex) SELECT REPLACE('KONGSSS', 'KONGSSS', '콩쓰'); cf) 참고로 한글 사용이 가능하다. -- KONGSSS 문자에서 KONGSSS 문자를 찾아 콩쓰로 바꿔준다. 11) MOD ; 나머지 값을 반환하는 함수이다. ex) SELECT MOD(300, 200); -- 나머지는 100을 반환한다. 12) ROUND ; 소수점을 표시하는 그 이하에서 반올림하는 함수이다. ex1) SELECT ROUND(11.3400, 4); -- 유효 소수점을 초과하므로 그대로 표시된다. ex2) SELECT ROUND(11.3425, 2); -- 소수점 3번째 자리에서 반올림하면 11.34가 출력된다. ex3) SELECT ROUND(11.5602, 0); -- 소수점 1번째 자리에서 반올림하면 12가 출력된다. ex4) SELECT ROUND(1154.32, -2); -- 정수 10 자리에서 반올림하면 12가 출력된다. ex5) SELECT ROUND(1154.32, -4); -- 정수 1000 자리에서 반올림하면 0이 출력된다. 반올림 단위를 초과하므로 그렇게 된다. 13) TRUNC ; 소수점을 표시하는 자리에서 버림 하는 함수이다.
ex1) SELECT TRUNC(11.3450, 4);
-- 소수 4번째 자리에서 버림하면 11.340이 출력된다.
ex2) SELECT TRUNC(1154.32, -3);
-- 정수 100번째 자리에서 버림하면 1000이 출력된다.
14) SYSDATE ; 오라클 DB 서버가 운영하는 운영체제의 날짜와 시간을 출력한다.
ex) SELECT SYSDATE
cf) 날짜, 시간 부분은 이후 중요하게 다시 복습하겠습니다.
15) TO_DATE ; 날짜 형 변환 함수이다.
ex1) WHERE hire_date > TO_DATE('2015', 'YYYY');
-- 2015라는 문자를 DATE형변환하여 hire_date 컬럼의 데이터 형과 일치시킨다.
-- 만약 데이터 형이 일치하지 않으면 오류가 발생한다.
ex2) WHERE hire_date BETWEEN TO_DATE('2013-01-01', 'YYYY-MM-DD')
AND TO_DATE('2013-12-31', 'YYYY-MM-DD')
-- 입사일을 확인하고자 할 때 사용하면 편하다.
16) 날짜 함수
ㄱ. MONTHS_BETWEEN ; 두 날짜 사이의 달 수를 정확하게 보여준다.
ex) SELECT MONTHS_BETWEEN(SYSDATE, SYSDATE+100);
-- 현재 날짜부터 100일을 더한 날짜와 사이의 달 수를 출력한다.
ㄴ. NEXT_DAY ; 다음에 오는 요일의 날짜를 출력한다.
ex) SELECT NEXT_DAY(SYSDATE, 'FRIDAY')
-- 다음 금요일의 날짜를 표기한다.
cf) 'FRIDAY'에 숫자 6을 표시해도 같은 효과가 적용된다.
영미권에서는 1; 일요일 2; 월요일 3; 화요일 .... 7; 토요일이 된다.
ㄷ. LAST_DAY ; 자신이 지정한 날짜의 마지막 날을 출력한다.
ex) SELECT LAST_DAY(SYSDATE)
-- 현재 지정한 날의 마지막날을 출력한다.
ㄹ. ADD_MONTHS ; 날짜의 달 수를 더한다.
ex) SELECT ADD_MONTHS(SYSDATE, 6);
-- 현재 지정한 달의 6개월 후를 출력한다.
ㅁ. ROUND,TRUNC ; 날짜 반올림, 내림을 출력한다.
cf) 요일에서 기준은 수요일이 된다. 세션에서 24시를 기준으로 하면 12시가 기준이 된다.
(중요 ; 날짜 세션 변경법은 반드시 외운다.)
ALTER SESSION SET nls_language=KOREAN ;
ALTER SESSION SET nls_date_format='RR/MM/DD' ; -- 한국 시간 기준
ALTER SESSION SET NLS_LANGUAGE=AMERICAN ;
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR' ; -- 미국 시간 기준
ALTER SESSION SET
NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS' ; -- 날짜 세션 설정
팁) 세션 설정 없이 결과를 보고자 한다면 TO_DATE 함수를 자주 활용한다.
17) 자동 데이터 형 변환과 각 데이터(숫자, 날짜) 형식
VARCHAR2 (CHAR) --- NUMBER --> 숫자로 된 문자만 숫자로 자동 변환한다.
VARCHAR2 (CHAR) --- DATE --> DATE표시형식과 일치하면 자동 변환한다.
NUMBER --- VARCHAR2 --> 숫자는 문자로 항상 변환된다.
DATE --- VARCHAR2 --> 세션에서 표시된 형식 그대로 문자로 변환된다.
ex) SELECT 1+1, '1'+'1', TO_CHAR(1)+TO_CHAR(1)
-- 결과는 2, 2, 11이 나온다.
ㄱ. 숫자 형식 모델(FORMAT MODEL)
- 숫자(0~9), 천단위(1,000,000), 달러($999,999), 그외(L999,999)
ㄴ. 날짜 형식 모델
- YEAR, YYYY, YY, RR, DD, DAY, DY(FRI, '금'), HH24(24시간기준), HH12(12시간기준)
,MI(분),SS(초) 등..
18) TO_CHAR ; 문자 형 변환 함수이다.
ex) SELECT TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS AM DAY')
-- 현재 날짜를 문자형으로 표시한다.
ㄱ. 첫번째 글자가 소문자 (mONTH) --- 출력되는 결과는 전부 소문자 ex) may
ㄴ. 첫번째, 두번째 글자가 대문자 (MOnth) -- 출력되는 결과는 전부 대문자 ex) MAY
ㄷ. 첫번째 글자가 대문자 두번째 글자가 소문자 (MoNth) -- 출력 결과는 첫번째 대문자 나머지 소문자 ex) May
ㄹ. 숫자 데이터를 통화 형식으로 표시하고자 할 때
ex) SELECT TO_CHAR(salary, '$999,999,999)
cf) 'L999,999,999.99' 과 'L099,999,999.99' 의 차이는
앞 자리가 0으로 되어있으면 현재 적용된 숫자가 90000이라고 할 때 앞에 0이 채워진다.
앞 자리가 9로 되어있으면 현재 적용된 숫자가 90000이라고 할 때 그대로 출력된다.
즉, 0은 VARCHAR2 형식의 LTRIM 이 적용된, 9는 CHAR형식이라고 생각하면 된다.
L999,999"원"이라고 작성하면 오류를 출력한다.
일반 함수(GENERAL FUNCTION)는 무엇일까?
- 데이터 형식에 제한받지 않는 함수이다.
- NVL, NVL2, DECODE, NULLIF 등의 함수가 있다.
1) NVL ; 컬럼의 대체값을 적용한다. NULL 상태를 값으로 표현할 때 많이 사용한다.
ex) SELECT NVL(commission_pct,0) -- NULL 상태는 0의 값으로 바꿔서 출력한다.
2) NVL2 ; 컬럼 데이터의 존재 상태를 적용한다.
ex) SELECT NVL2(commission_pct, 'OK', 'NO') -- 데이터 존재하면 OK, NULL이면 NO이다.
3) NULLIF ; 컬럼1, 컬럼2가 같으면 NULL, 다르면 컬럼1을 표시한다.
ex) SELECT first_name, last_name, NULLIF(first_name, last_name) AS "RESULT"
-- 같으면 NULL, 다르면 first_name을 출력한다.
4) COALESCE ; 최초로 NULL 이 아닌 컬럼 데이터를 출력한다.
ex) 컬럼간의 데이터 형식이 같아야 한다.
5) CASE ; 조건식이다.
ex) SELECT last_name, job_id, salary,
(CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary
END) AS "REVISED_SALARY"
-- JOB_ID 컬럼의 'IT_PROG'의 레코드는 salary의 1.10을 곱한다. 'ST_CLERK'의 레코드는....
'SA_REP' 의 레코드.... 나머지 레코드는 봉급 그대로를 받는다.
6) DECODE ; 조건식이다.
ex) SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG' , 1.10*salary,
'ST_CLERK' , 1.15*salary,
'SA_REP' , 1.20*salary,
salary ) AS "REVISED_SALARY"
-- 위 내용과 같다.
여기까지 입니다. 저도 다시 정리를 하면서 복습을 할 수 있어 좋은 기회가 되네요. 본문에 결과를 단순하게 설명으로만 보여드리고 있습니다. 단일 행 함수는 정말 많이 사용하는 중요 함수들입니다. 가장 기초적인 함수인만큼 반드시 숙지하고 지나가시길 바랍니다.^^ 다음 강은 다중 행 함수입니다.
