Oracle 함수

/* ORACLE 숫자함수 */

-- MAX(char) - 하나 이상의 행으로부터 최대값 반환

SELECT MAX(salary) FROM employees WHERE job_id = 'IT_PROG';

-- MIN(char) - 하나 이상의 행으로부터 최소값 반환

SELECT MIN(salary) FROM employees WHERE job_id = 'IT_PROG';

-- ROUND(M, N) - M을 소수점 N+1 자리에서 반올림한 결과 반환

SELECT ROUND(3.141579, 3) "반올림 함수" FROM DUAL;

-- POWER(M, N) - M에 N승 한 결과 반환

SELECT POWER(2, 10) "POWER 함수" FROM DUAL;

/* ORACLE 문자함수 */

-- LENGTH(CHAR) - 문자열의 길이를 반환

SELECT LENGTH('KOREA TEAM FIGHTING') "LENGTH 함수" FROM DUAL;

-- LOWER(CHAR) - 입력된 문자를 소문자로 반환

SELECT LOWER('KOREA TEAM FIGHTING') "LOWER 함수" FROM DUAL;

-- UPPER(CHAR) - 입력된 문자를 대문자로 반환

SELECT UPPER('korea team fighting') "UPPER 함수" FROM DUAL;

-- LTRIM(CHAR [, SET]) - 문자열 좌측으로부터 SET으로 지정된 문자를 만날때까지 문자를 제거

SELECT LTRIM('XXAXBAXX','X') "LTRIM" FROM DUAL;

-- RTRIM(CHAR [, SET]) - 문자열 우측으로부터 SET으로 지정된 문자를 만날때까지 문자를 제거

SELECT RTRIM('XXAXBAXX','X') "RTRIM" FROM DUAL;

-- SUBSTR(String, Position, SubString_length) - 문자열에서 일부 문자값을 선택적으로 반환

SELECT SUBSTR('ORACLE PROJECT', 1, 3) SUBSTR1,
        SUBSTR('ORACLE PROJECT', 4, 5) SUBSTR2,
        SUBSTR('ORACLE PROJECT', 10) SUBSTR3 FROM DUAL;

/* ORACLE 날짜 함수 */

-- CURRENT_TIMESTAMP/LOCALTIMESTAMP - 현재 SESSION의 날짜와 시간 정보를 반환

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, CURRENT_DATE FROM DUAL;

-- EXTRACT - 특정 날짜/시간값이나 날짜값을 가진 표현식으로부터 원하는 날짜 영역을 추출

SELECT EXTRACT(year from date '2004-8-2') FROM DUAL;

-- MONTHS_BETWEEN(date1, date2) - 날짜와 날짜 사이의 기간을 '월(MONTH)'로 나타냄

SELECT MONTHS_BETWEEN(LAST_DAY(SYSDATE),SYSDATE) "Remain Months" FROM DUAL;

-- SYSDATE - 시스템의 설정된 날짜 값 반환

SELECT SYSDATE FROM DUAL;

/* 변환함수 */

-- CAST(DATA_FORM,COLLECTION_FORM AS DATA_FORM,COLLECTION_FORM) -- 데이터 형식이나 COLLECTION 형식을 다른 데이터 형식이나 다른 COLLECTION 형식으로 변환

SELECT CAST(CURRENT_DATE AS TIMESTAMP) FROM DUAL;

-- TO_CHAR(DATA_TYPE, DATA_TYPE) - 각종 데이터 타입을 VARCHAR2 데이터 타입으로 변환

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') "SYSDATE" FROM DUAL;

/* ORACLE 함수 */

-- NTILE(bucket) OVER (analytic_clause) - 순서화된 데이터를 bucket수로 분할, 각 행을 적절한 bucket 번호를 할당, 출력 결과를 사용자가 지정한 그룹수로 나누어 출력

SELECT first_name,salary,NTILE(3) OVER (ORDER BY salary DESC) FROM employees WHERE job_id = 'ST_CLERK';

-- COUNT(expr) OVER(analytic_clause) - 조건을 만족하는 행의 수를 반환

SELECT employee_id,salary,COUNT(*) OVER (ORDER BY salary) AS count FROM employees WHERE department_id = 50;

-- SUM(expr) OVER(analytic_clause) - 조건을 만족하는 행의 합을 반환

SELECT employee_id,last_name,salary,SUM(salary) OVER (ORDER BY employee_id) AS acc_salary FROM employees;

-- DECODE(칼럼이름, 비교값, 입력값, [비교값2, 입력값2], 기본값) - CASE나 IF-THEN-ELSE 문처럼 조건부 결과 반환

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 Recieve FROM employees;

-- RANK(expr) WITH GROUP(ORDER BY expr)/RANK () OVER(query_partition_clause order_by_clause)
-- 값의 그룹에서 해당값의 절대순위를 계산(1위 - 공동2위, 공동2위, 공동2위 - 공동5위...)

SELECT RANK(15500, .05) WITHIN GROUP(ORDER BY salary DESC, commission_pct) "Rank" FROM employees;

-- DENSE RANK(expr) WITH GROUP(ORDER BY expr)/DENSE_RANK() OVER(query_partition_clause order_by_clause);
-- 값의 그룹에서 해당값의 상대순위를 계산(1위 - 공동2위, 공동2위, 공동2위 - 공동3위...)

SELECT DENSE_RANK(15500, .05) WITHIN GROUP(ORDER BY salary DESC, commission_pct) "Rank" FROM employees;

-- PERCENT_RANK - 그룹 수에 대한 값의 퍼센트 반환. 최고 순위 0, 최대 순위 1롤 놓고 해당 비율을 반환

SELECT PERCENT_RANK(15500, .05) WITHIN GROUP(ORDER BY salary DESC, commission_pct) AS Percent_Rank FROM employees;

-- FIRST_VALUE(expr) OVER (analyic_clause)/LAST_VALUE(expr) OVER (analyic_clause)

SELECT employee_id, salary, department_id, FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) as "Highsal" FROM employees;

-- NVL(expr1, expr2) -- 변수 1의 값을 체크하여 NULL이면 변수 2의 값을 반환하고 그렇지 않으면 변수 1의 값 반환

SELECT last_name, nvl(manager_id, 0) AS Manager_id FROM employees WHERE employee_id BETWEEN 100 AND 105;

-- WIDTH_BUCKET(expr, min_value, max_value, num_buckets) - 어떤 값의 최소에서 최대값을 설정하고 bucket(구간) 지정하여 어느 위치(등급)에 있는지 반환

SELECT salary, WIDTH_BUCKET(salary, 0, 20000, 5) FROM employees ORDER BY salary desc;

-- LAG(value_expr [,offset] [,default]) OVER([query_partition_clause] order_by_clause)
-- SELF JOIN 하지 않고 하나의 테이블에서 동시에 한 행 이상 접근. 분석용 함수로서 동일 테이블에 있는 다른행을 참조하는 함수

SELECT last_name, salary, LAG(salary,1,0) OVER(ORDER BY hire_date) AS prev_sal FROM employees WHERE job_id = 'IT_PROG';

-- LEAD(value_expr [,offset] [,default]) OVER([query_partition_clause] order_by_clause)
-- SELF JOIN 하지 않고 하나의 테이블에서 동시에 한 행 이상 접근. LAG와 반대로 하나 앞의 레코드 값 참조

SELECT last_name, salary, LEAD(salary,2,0) OVER(ORDER BY hire_date) AS Next_sal FROM employees WHERE job_id = 'IT_PROG';