DB#CH04. SQL 함수 이해

생각해보니까 설명은 책 보면 되는데... 내가 해당 쿼리 설명을 하나하나 다 적을 이유가 없다고 느껴졌다.

시간 낭비임. 그냥 필기 했으면 됐지 뭘 또 거기서 더 필기해... 책 쓰는 것도 아니고... 물론 써두면 좋겠지... 그치만 시간을 효율적으로 쓰자....! 나 시간 없으니까.. 밤 샐거 아니자나... 사실 완벽하게 다시 시작하기 위해서 이전에 쓴 것도 지우고 싶은데... 뭔 시간지랄인가... 싶어서.. 그냥 냅둬야지.. 하 정말 맘에 안 들어... 하지만 걍 살자...

그리고 쿼리 예제문... 다 안 올릴 거임... 이것도 사유: 시간 없음. 필기에 시간 쓸 시간 없음 내 시간의 효율을 위해 일단 실행한 쿼리문만 올린다... 그리고 헷갈리는 함수들만!!!!!!! 근데 이래놓고 미래의 내가 또 바뀔 수 있음... 그럴 가능성 완정 100프로임... 과거의 나야 미래의 나야... 잘 타협봐라..... 응...

 

1. 숫자 관련 함수

2. 문자 관련 함수

3. 날짜 관련 함수

4. 변환 함수 => to_char, to_number, to_date

5. NULL 관련 함수

6. 기타 함수

 

1. 숫자 관련 함수

- ABS(n)

- CEIL(n) & FLOOR(n)

- ROUND(n, i) & TRUNC(n1, n2)

- POWER(n2, n1) & SQRT(n)

- MOD(n2, n1)

- EXP(n) & LN(n) & LOG(n2, n1)

 

2. 문자 관련 함수

- INITCAP(char) & LOWER(char), UPPER(char)

- CONCAT(char1, char2) & SUBSTR(char, pos, len)

- LTRIM(char, set), RTRIM(char, set)

- LPAD(expr1, n, expr2), RPAD(expr1, n, expr2)

- REPLACE(char, search, str, replace str)

- INSTR(str, substr, pos, occur) & LENGTH(chr), LENGTHB(chr)

 

3. 날짜 관련 함수

- SYSDATE, STSTIMESTAMP

- ADD_MODTHS(date, integer)

- MONTHS_BETWEEN(date1, date2)

- LAST_DATE(date)

- ROUND(date, format) & TRUNC(date, format)

- NEXT_DAY(date, char)

 

4. 변환 함수

- TO_CHAR(숫자 혹은 날짜, format)

   밑에 틀도 모두 암기~~

- TO_NUMBER(expr, format)

- TO_DATE(char, format), TO_TIMESTAMP(char, format)

 

5. NULL 관련 함수 //  여기 관련 함수들 잘 이해 못 한 것 같음... 복습 해야함.....

- NVL(expr1, expr2) & NVL2(expr1, expr2, expr3)

- COALESCE(expr1, expr2, ...)

- LNNVL 

- NULLIF(expr1, expr2) 

https://web-obj.tistory.com/410 참고

 

LNNVL(condition)

 

LNNVL provides a concise way to evaluate a condition when one or both operands of the condition may be null. The function can be used in the WHERE clause of a query, or as the WHEN condition in a searched CASE expression. It takes as an argument a condition and returns TRUE if the condition is FALSE or UNKNOWN and FALSE if the condition is TRUE. LNNVL can be used anywhere a scalar expression can appear, when contexts where the IS [NOT] NULL, AND, or OR conditions are not valid but would otherwise be required to account for potential nulls.

 

Oracle Database sometimes uses the LNNVL function internally in this way to rewrite NOT IN conditions as NOT EXISTS conditions. In such case, output from EXPLAIN PLAN showw this operation in the plan table output. The condition can evaluate any scalar values but cannot be a compound condition containing AND, OR, or BETWEEN.

 

The table that follows shows what LNNVL returns given that a -2 and b is null.

Condition	Truth of Condition	LNNVL Return Value
a=1	FALSE	TRUE
a=2	TRUE	FALSE
a Is NULL	FALSE	TRUE
b=1	UNKNOWN	TRUE
b IS NULL	TRUE	FALSE
a=b	UNKNOWN	TRUE

 

1)

SQL> SELECT COUNT(*)  FROM employees;

   --> 107

 

2)

SQL> SELECT COUNT(*) 
FROM employees
WHERE commission_pct < .2 ;

   --> 11

 

3)

SQL > SELECT COUNT(*) 
FROM employees
WHERE commission_pct IS NULL ;

   --> 72

 

4)

SQL>SELECT COUNT(*)  
FROM employees
WHERE LNNVL(commission_pct >= .2) ;

   --> 83

 

* 전체 107건 가운데 commission_pct >= .2 이 아닌 것을 구해준다.

그럼 commission_pct >= .2 이 아닌 것은 commission_pct < .2 인 것과 commission_pct IS NULL 인 것이 있을 것이다.

그것을 구해주는 용도로 사용할 수 있을 것 같다.

 

6. 기타 함수

- GREATEST(expr1, expr2, ...) & LEAST(expr1, expr2, ...)

- DECODE(expr, search1, resul1, search2, result2, ..., default)

 

SQLGATE 실행>

SELECT NEXT_DAY(SYSDATE,'화요일'), sysdate
  FROM dual;
 -- 2022-08-02 오전 11:11:34   2022-07-27 오전 11:11:345

SELECT TO_CHAR(SYSDATE, 'dd') FROM dual;
SELECT TO_CHAR(SYSDATE, 'ww') FROM dual;

SELECT TO_CHAR(1234, '9,999') FROM dual;
--   1234 
;

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD')
  FROM dual;
SELECT SYSDATE FROM dual;
-- 2022-07-27 오후 12:22:00

SELECT SYSDATE FROM dual; -- sysdate : 현재시간
-- 특정 날짜 시간 다루기 필요
-- 특정 날짜 : '2022 01 01 00:00:00' -> DATE => 날짜 계산(함수)
SELECT TO_DATE('20220101','YYYYMMDD')
  FROM dual;

SELECT TO_DATE('20220101','YYYYMMDD')
  FROM dual;
  
-- 아래 코드는 틀이 맞지 않아서 에러.
--SELECT TO_DATE('2022-01-01', 'YYYYMMDD')
-- FROM dual;

-- '-'와 '/' 기호는 ORACLE에서 DATE를 나타낼 때 동일 하게 처리함 그래서 아래 문장 가능
SELECT TO_DATE('20220101', 'YYYY/MM-DD')
FROM   dual
;

-- 문제 : 인센티브 포함 연봉 계산
SELECT a.SALARY
     , a.COMMISSION_PCT
     , a.SALARY + (a.SALARY*a.COMMISSION_PCT) 
  FROM EMPLOYEES a
;
SELECT a.SALARY
     , a.COMMISSION_PCT
     , a.SALARY + (a.SALARY*NVL(a.COMMISSION_PCT,0)) 
  FROM EMPLOYEES a
;

SELECT a.SALARY
       , a.COMMISSION_PCT
       , NVL2(
             a.COMMISSION_PCT
           , a.SALARY + (a.SALARY * a.COMMISSION_PCT)
           , a.SALARY
       ) AS SALARY1
  FROM EMPLOYEES a
;

SELECT a.SALARY -- 2600
       , a.COMMISSION_PCT -- null
       , COALESCE(
           a.SALARY + (a.SALARY *a.COMMISSION_PCT)
        , a.SALARY
        ) AS SALARY2 
  FROM EMPLOYEES a
  -- 2500      NULL 2500
  -- 14000   0.4    5600

SELECT COUNT(*)
  FROM EMPLOYEES a
 WHERE a.COMMISSION_PCT IS NULL
;

SELECT a.EMPLOYEE_ID, a.COMMISSION_PCT
  FROM EMPLOYEES a
 WHERE a.COMMISSION_PCT < 0.2
;

SELECT COUNT(a.EMPLOYEE_ID)
  FROM EMPLOYEES a
 WHERE NVL(a.COMMISSION_PCT,0) < 0.2
;


SELECT a.EMPLOYEE_ID, a.SALARY, a.COMMISSION_PCT 
	FROM   EMPLOYEES a -- where nvl(c_p, 0) < 0.2;
;
--142   3100   NULL
--143   2600   NULL
--144   2500   NULL
--145   14000   0.4
--146   13500   0.3
--147   12000   0.3

DESC job_history;
-- job_history table : 사원 이력 관리하는 테이블
--EMPLOYEE_ID : 사원번호
--START_DATE : 시작일
--END_DATE : 종료일
--JOB_ID : 업무번호
--DEPARTMENT_ID : 부서번호
--CREATE_DATE : 생성일
--UPDATE_DATE : 수정일
-- 어떤 사원이 어느 부서에서 어떤 업무를 언제 시작해서 언제 끝내는가? 를 나타내는 테이블
SELECT * FROM JOB_HISTORY a
;

SELECT a.EMPLOYEE_ID
			,TO_CHAR (START_DATE, 'YYYY'
				) AS start_year
			,TO_CHAR (end_date, 'YYYY'
				) AS end_year
			, nullif(
					TO_CHAR(end_date, 'YYYY')
				,	TO_CHAR(START_DATE, 'YYYY')
				)	AS nullif_year		
	FROM JOB_HISTORY a
;

DESC channels; -- 판매 경로를 저장하는 테이블
--CHANNEL_ID : 판매 경로 번호
--CHANNEL_DESC : 판매 경로에 대한 설명
--CHANNEL_CLASS :  판매 경로 분류
--CHANNEL_CLASS_ID : 판매 경로 분류에 대한 번호
--CHANNEL_TOTAL 
--CHANNEL_TOTAL_ID
--CREATE_DATE : 생성일
--UPDATE_DATE : 수정일

SELECT * FROM CHANNELS a; 

SELECT FROM CHANNELS a;