단일(행) (반환)함수의 구분
단일 함수란 단일행 적용단위가 테이블의 행단위로 처리
(1) 문자 (처리)함수 : 문자와 관련된 특별한 조작을 위한 함수
a. INITCAP - 첫글자만 대문자로 변경
b. UPPER - 모든 글자를 대문자로 변경
c. LOWER - 모든 글자를 소문자로 변경
d. CONCAT - 두 문자열 연결 >> 이거 쓸바에는 || 연결연산자 씀
e. LENGTH - 문자열의 길이 반환
f. INSTR - 문자열에서, 특정 문자열의 위치(인덱스) 반환
g. SUBSTR - 문자열에서, 부분문자열(substring) 반환
h. REPLACE - 문자열 치환(replace)
i. LPAD - 문자열 오른쪽 정렬 후, 왼쪽의 빈 공간에 지정문자 채우기(padding)
j. RPAD - 문자열 왼쪽 정렬 후, 오른쪽의 빈 공간에 지정문자 채우기(padding)
k. LTRIM - 문자열의 왼쪽에서, 지정문자 삭제(trim)
l. RTRIM - 문자열의 오른쪽에서, 지정문자 삭제(trim)
m. TRIM - 문자열의 왼쪽/오른쪽/양쪽에서, 지정문자 삭제(trim)
(단, 문자열의 중간은 처리못함)
a. INITCAP - 첫글자만 대문자로 변경
-- ------------------------------------------------------
-- (1) 문자(처리) 함수 - INITCAP (카넬기법에 식별에 적용)
-- ------------------------------------------------------
-- 첫글자만 대문자로 변경
-- ------------------------------------------------------
SELECT
'ORACLE SQL',
initcap('ORACLE SQL'),('나이로비') -- 여러단어에 다 적용 됌. 결과값= Oracle Sql / 한글은 적용이 안됌 그냥 나옴
FROM
dual;
SELECT
email,
initcap(email) -- 결과값 Email
FROM
employees;b. UPPER - 모든 글자를 대문자로 변경
-- ------------------------------------------------------
-- (2) 문자(처리) 함수 - UPPER
-- ------------------------------------------------------
-- 모든 글자를 대문자로 변경
-- ------------------------------------------------------
SELECT
'Oracle Sql',
upper('Oracle Sql'),('나이로비'),('oracle 오라클') -- 결과값 ORACLE SQL, 나이로비, oracle 오라클
FROM
dual;
SELECT
last_name,
upper(last_name)
FROM
employees;
SELECT
last_name,
salary
FROM
employees
-- WHERE --조건식으로 참조한 컬럼이 인덱스를 생성하는데 검색성능 향상수단 이와 같이 컬럼 자체에 인덱스를 가공처리하면 사용을 못함.
-- upper(last_name) = 'KING'; -- *Decommendation* : The index with the column cannot be used.
WHERE
last_name = initcap('KING'); -- *Recommendation* : The index with the column can be used.
c. LOWER - 모든 글자를 소문자로 변경
-- ------------------------------------------------------
-- (3) 문자(처리) 함수 - LOWER
-- ------------------------------------------------------
-- 모든 글자를 소문자로 변경
-- ------------------------------------------------------
SELECT
'Oracle Sql',
lower('Oracle Sql')
FROM
dual;
SELECT
last_name,
lower(last_name)
FROM
employees;
d. CONCAT - 두 문자열 연결 >> 이거 쓸바에는 || 연결연산자 씀
-- ------------------------------------------------------
-- (4) 문자(처리) 함수 - CONCAT
-- ------------------------------------------------------
-- 두 문자열 연결(Concatenation)
-- ------------------------------------------------------
-- SELECT
-- -- || (Concatenation Operator) == concat function
-- 'Oracle' || 'Sql',
-- concat('Oracle', 'Sql')
SELECT
-- || (Concatenation Operator) == concat function
'Oracle' || 'Sql' || 'third',
concat( concat('Oracle', 'Sql'), 'third')
FROM
dual;
SELECT
-- || (Concatenation Operator) == concat function
last_name || salary,
concat(last_name, salary)
FROM
employees;
SELECT
-- || (Concatenation Operator) == concat function
last_name || hire_date,
concat(last_name, hire_date)
FROM
employees;
e. LENGTH - 문자열의 길이 반환
-- ------------------------------------------------------
-- (5) 문자(처리) 함수 - LENGTH
-- ------------------------------------------------------
-- 문자열의 길이 반환
-- ------------------------------------------------------
-- A. LENGTH returns Characters
-- B. LENGTHB returns Bytes -- 바이트의 개수
-- ------------------------------------------------------
SELECT
'Oracle',
length('Oracle')
FROM
dual;
SELECT
last_name,
length(last_name)
FROM
employees;
-- '한글' 문자열을 유니코드(Unicode)로 표현하면, '\D55C\AE00' 임.
SELECT
'한글',
length('한글') AS length,
lengthb('한글') AS lengthb
FROM
dual;
SELECT
unistr('\D55C\AE00'),
length( unistr('\D55C\AE00') ) AS length,
lengthb( unistr('\D55C\AE00') ) AS lengthb
FROM
dual;f. INSTR - 문자열에서, 특정 문자열의 위치(인덱스) 반환
-- ------------------------------------------------------
-- (6) 문자(처리) 함수 - INSTR
-- ------------------------------------------------------
-- 문자열에서, 특정 문자열의 (시작)위치(시작 인덱스) 반환
-- ------------------------------------------------------
-- 주의) Oracle 의 인덱스 번호는 1부터 시작함!!! -- 명심해라!
-- ------------------------------------------------------
SELECT
instr('MILLER', 'L', 1, 2), -- 1: offset, 2: occurence 밀러라는 문자에서 주어진 구간에 L이라는 문자가 있느냐 시작인덱스와 끝점을 정해서
instr('MILLER', 'X', 1, 2) -- 1: offset, 2: occurence 못찾는 경우는 0으로 반환한다.
FROM
dual;g. SUBSTR - 문자열에서, 부분문자열(substring) 반환
주의) Oracle 의 인덱스 번호는 1부터 시작함!!!
-- ------------------------------------------------------
-- (7) 문자(처리) 함수 - SUBSTR
-- ------------------------------------------------------
-- 문자열에서, 부분문자열(substring) 반환
-- ------------------------------------------------------
-- 주의) Oracle 의 인덱스 번호는 1부터 시작함!!!
-- ------------------------------------------------------
SELECT
-- substr('123456-1234567', 1, 6), -- 정해진 범위 만큼 뽑는다. 123456
-- substr('123456-1234567', 8), -- 끝에서부터 해당 번호의 인덱스까지 뽑는다.
substr('123456-1234567', 1, 7) || '*******' as "주민등록번호"
FROM
dual;
-- In the Oracle SQL*Developer
-- SELECT
-- hire_date AS 입사일,
-- substr(hire_date, 1, 2) AS 입사년도
-- In the Visual Source Code
SELECT
hire_date,
to_char(hire_date) AS 입사일,
to_char(hire_date, 'RR/MM/DD') AS 일사일2,
substr( to_char(hire_date), 8, 2 ) AS 입사년도 --(컬럼, offest, length)
FROM
employees;
SELECT
'900303-1234567',
substr('900303-1234567', 8)
FROM
dual;
-- 그런데, offset index를 음수를 사용할 수 있다면????ㅠ
SELECT
'900303-1234567',
substr('900303-1234567', -7) --음수 인덱스는 뒷자리부터시작
FROM
dual;
h. REPLACE - 문자열 치환(replace)
-- ------------------------------------------------------
-- (8) 문자(처리) 함수 - REPLACE
-- ------------------------------------------------------
-- 문자열 치환(replace)
-- ------------------------------------------------------
SELECT
replace('JACK and JUE', 'J', 'BL') -- J를 BL로 바꾼다
FROM
dual;i. LPAD - 문자열 오른쪽 정렬 후, 왼쪽의 빈 공간에 지정문자 채우기(padding)
-- ------------------------------------------------------
-- (9) 문자(처리) 함수 - LPAD
-- ------------------------------------------------------
-- 문자열 오른쪽 정렬 후,
-- 왼쪽의 빈 공간에 지정문자 채우기(padding)
-- ------------------------------------------------------
SELECT
lpad('MILLER', 10, '*') -- 6자리인 문자에 10자리까지 왼쪽에 * 4개를 더함
FROM
dual;
j. RPAD - 문자열 왼쪽 정렬 후, 오른쪽의 빈 공간에 지정문자 채우기(padding)
-- ------------------------------------------------------
-- (10) 문자(처리) 함수 - RPAD
-- ------------------------------------------------------
-- 문자열 왼쪽 정렬 후,
-- 오른쪽의 빈 공간에 지정문자 채우기(padding)
-- ------------------------------------------------------
SELECT
rpad('MILLER', 10, '*') -- 6자리인 문자에 10자리까지 오른쪽에 * 4개를 더함
FROM
dual;
SELECT
substr('900303-1234567', 1, 8) || '******' AS 주민번호
FROM
dual;
SELECT
rpad(
substr('900303-1234567', 1, 8), 14, '*'
) AS 주민번호
FROM
dual;k. LTRIM - 문자열의 왼쪽에서, 지정문자 삭제(trim)
-- ------------------------------------------------------
-- (11) 문자(처리) 함수 - LTRIM
-- ------------------------------------------------------
-- 문자열의 왼쪽에서, 지정문자 삭제(trim)
-- ------------------------------------------------------
SELECT
ltrim('MMMIMLLER', 'M')
FROM
dual;
SELECT
ltrim(' MILLER '),
length( ltrim(' MILLER ') )
FROM
dual;l. RTRIM - 문자열의 오른쪽에서, 지정문자 삭제(trim)
-- ------------------------------------------------------
-- (12) 문자(처리) 함수 - RTRIM
-- ------------------------------------------------------
-- 문자열의 오른쪽에서, 지정문자 삭제(trim)
-- ------------------------------------------------------
SELECT
rtrim('MILLRER', 'R')
FROM
dual;
SELECT
rtrim(' MILLER '),
length( rtrim(' MILLER ') )
FROM
dual;
m. TRIM - 문자열의 왼쪽/오른쪽/양쪽에서, 지정문자 삭제(trim)
(단, 문자열의 중간은 처리못함)
-- ------------------------------------------------------
-- (13) 문자(처리) 함수 - TRIM
-- ------------------------------------------------------
-- 문자열의 왼쪽/오른쪽/양쪽에서, 지정문자 삭제(trim)
-- (단, 문자열의 중간은 처리못함)
-- ------------------------------------------------------
-- 문법)
-- TRIM( LEADING 'str' FROM 컬럼명|표현식 )
-- TRIM( TRAILING 'str' FROM 컬럼명|표현식 )
-- TRIM( BOTH 'str' FROM 컬럼명|표현식 )
-- TRIM( 'str' FROM 컬럼명|표현식 ) -- BOTH (default)
-- ------------------------------------------------------
SELECT
trim( '0' FROM '0001234567000' ) -- default: BOTH (양쪽에서 제거)
FROM
dual;
SELECT
trim( LEADING '0' FROM '0001234567000' ) -- 문자열 왼쪽에서 제거
FROM
dual;
SELECT
trim( TRAILING '0' FROM '0001234567000' ) -- 문자열 오른쪽에서 제거
FROM
dual;
(2) 숫자 (처리)함수
a. ROUND - 지정한 자리 수 이하에서 반올림 b. TRUNC - 지정한 자리 수 이하에서 절삭 c. MOD - 나누기 연산을 한 후에 나머지 값을 반환 d. CEIL - 주어진 숫자값보다 크거나 같은 최소 정수값을 반환 e. FLOOR - 주어진 숫자값보다 작거나 같은 최대 정수값을 반환 f. SIGN - 주어진 값이 양수인지 음수인지 0인지 식별할 수 있는 값을 반환
a. ROUND - 지정한 자리 수 이하에서 반올림
-- ------------------------------------------------------
-- (1) 숫자 (처리)함수 - ROUND
-- ------------------------------------------------------
-- 지정한 자리 수 이하에서 반올림
-- ------------------------------------------------------
-- 1. ROUND 함수 (양수)
SELECT
-- round( 456.789, 2 ) -- 456.79
-- round( 456.789, 1 ) -- 456.8
round( 456.789, 3 ) -- 456.789
FROM
dual;
-- 2. ROUND 함수 (음수) -- 0을 기준으로 그 왼쪽 자리에서 반올림한다.
SELECT
round( 456.789, -1 ) --460
-- round( 456.789, -2 ) --500
-- round( 456.789, -3 ) -- 0
FROM
dual;
-- 3. ROUND 함수 (양수/음숫 미지정 -- 소숫점 자리에서 반올림)
SELECT
round( 456.789 )
FROM
dual;b. TRUNC - 지정한 자리 수 이하에서 절삭
-- ------------------------------------------------------
-- (2) 숫자 (처리)함수 - TRUNC
-- ------------------------------------------------------
-- 지정한 자리 수 이하에서 절삭
-- ------------------------------------------------------
-- 1. TRUNC 함수 (양수)
SELECT
trunc( 456.789, 2 )
FROM
dual;
-- 2. TRUNC 함수 (음수)
SELECT
trunc( 456.789, -1 )
FROM
dual;
-- 3. TRUNC 함수 (양수/음숫 미지정 -- 소숫점 자리에서 절삭)
SELECT
trunc( 456.789 )
FROM
dual;
c. MOD - 나누기 연산을 한 후에 나머지 값을 반환
-- ------------------------------------------------------
-- (3) 숫자 (처리)함수 - MOD (Modular)
-- ------------------------------------------------------
-- 나누기 연산을 한 후에 나머지 값을 반환
-- ------------------------------------------------------
SELECT
mod(10, 3) , -- 3으로 나눔
mod(10, 0) -- 0으로 나누면??? : 그대로 피젯수 반환 (원래수를 반환한다. 허용은 하되 연산은 안됌)
FROM
dual;
-- MOD 함수의 응용 (홀수 판단)
SELECT
employee_id,
last_name,
salary
FROM
employees
-- WHERE
-- mod(employee_id, 2) = 1;
WHERE
-- mod(employee_id, 2) != 0;
mod(employee_id, 3) = 0;
-- if ( 사원번호 % 2) != 0 :
d. CEIL - 주어진 숫자값보다 크거나 같은 최소 정수값을 반환
-- ------------------------------------------------------
-- (4) 숫자 (처리)함수 - CEIL
-- ------------------------------------------------------
-- 주어진 숫자값보다 크거나 같은 최소 정수값을 반환
-- ------------------------------------------------------
SELECT
ceil(10.6),
ceil(-10.6)
FROM
dual;
e. FLOOR - 주어진 숫자값보다 작거나 같은 최대 정수값을 반환
-- ------------------------------------------------------
-- (5) 숫자 (처리)함수 - FLOOR
-- ------------------------------------------------------
-- 주어진 숫자값보다 작거나 같은 최대 정수값을 반환
-- ------------------------------------------------------
SELECT
floor(10),
floor(10.6),
floor(-10.6)
FROM
dual;
f. SIGN - 주어진 값이 양수인지 음수인지 0인지 식별할 수 있는 값을 반환
-- ------------------------------------------------------
-- (6) 숫자 (처리)함수 - SIGN
-- ------------------------------------------------------
-- 주어진 값이 양수인지 음수인지 0인지 식별할 수 있는 값을 반환
-- ------------------------------------------------------
SELECT
sign(100), --1
sign(-20), --(-1)
sign(0) --0
FROM
dual;
-- SIGN 함수의 응용
SELECT
employee_id,
last_name,
salary
FROM
employees
-- WHERE
-- sign(salary - 15000) = 1;
WHERE
sign(salary - 15000) != -1;
-- if salay > 15000 와 같음
-- sign 함수는, 비교연산자를 대체할 수 있음(>,<,=,>=,<=)
일단 흐자