1. ๋จ์ผํ ์ซ์ ํจ์
๋จ์ผํ ํจ์๋ ์ฟผ๋ฆฌ ๋ ํ
์ด๋ธ ๋๋ ๋ทฐ์ ๋ชจ๋ ํ์ ๋ํด ๋จ์ผ ๊ฒฐ๊ณผํ์ ๋ฐํ
์ซ์ ํจ์ ์ข ๋ฅ
์์ ์ฐ์ฐ์ ํ๋ ํจ์, ๋งค๊ฐ๋ณ์๋ ๋ฐํ ๊ฐ์ด ๋๋ถ๋ถ ์ซ์ ํํ
ABS(n) ์ ๋๊ฐ
SELECT ABS(20), ABS(-20) FROM dual; -- 20 -20
SIGN(n) ๋ถํธ
SELECT SIGN(20), SIGN(-20), SIGN(0) FROM dual; -- 1 -1 0
MOD(n2, n1)
n2๋ฅผ n1์ผ๋ก ๋๋ ๋๋จธ์ง๋ฅผ ๋ฐํ n1์ด 0์ด๋ฉด n2๋ฅผ ๋ฐํ
๋ด๋ถ ์ฐ์ฐ ๋ฐฉ์ : n2 - n1 * FLOOR(n2/n1)
SELECT MOD(11, 4) FROM dual; -- 3
SELECT MOD(13, 5) FROM dual; -- 3
REMAINDER(n2, n1)
n2๋ฅผ n1์ผ๋ก ๋๋ ๋๋จธ์ง๋ฅผ ๋ฐํ
๋ด๋ถ ์ฐ์ฐ ๋ฐฉ์ : n2 - n1 * ROUND(n2/n1)
SELECT REMAINDER(13, 5) FROM dual; -- -2
SELECT REMAINDER(13, 6) FROM dual; -- 1
CEIL(n)
n๋ณด๋ค ํฌ๊ฑฐ๋ ๊ฐ์ ๊ฐ์ฅ ์์ ์ ์
SELECT CEIL(20.5), CEIL(-20.5), CEIL(20) FROM dual; -- 21 -20 20
FLOOR(n)
n ์ดํ์ ๊ฐ์ฅ ํฐ ์ ์๋ฅผ ๋ฐํ
SELECT 13/5 FROM dual; -- 2.6
SELECT 13 - 5 * FLOOR(13/5) FROM dual; --3
SELECT FLOOR(20.5), FLOOR(-20.5), FLOOR(20) FROM dual; -- 20 -21 20
ROUND(n [, integer ]) ๋ฐ์ฌ๋ฆผ
n์ ์์์ ์ค๋ฅธ์ชฝ์ ์ ์ ์๋ฆฌ๋ก ๋ฐ์ฌ๋ฆผ
SELECT ROUND(13/5) FROM dual; -- 3
SELECT 13 - 5 * ROUND(13/5) FROM dual; -- -2
SELECT ROUND(15.683) FROM dual; -- 16 : ์์์ ์ฒซ์งธ ์๋ฆฌ ๋ฐ์ฌ๋ฆผ
SELECT ROUND(15.683, 0) FROM dual; -- 16
SELECT ROUND(15.683, 1) FROM dual; -- 15.7 : ์์์ ๋์งธ์๋ฆฌ์์ ๋ฐ์ฌ๋ฆผํด์ ์์์ ํ์๋ฆฌ ํํ
SELECT ROUND(15.683, 2) FROM dual; -- 15.68
SELECT ROUND(15.683, -1) FROM dual; -- 20. ์์์ด๋ฉด ์์์ ์ผ์ชฝ์์ ๋ฐ์ฌ๋ฆผ
TRUNC(n1 [, n2 ]) ์ ์ญ
SELECT TRUNC(15.683) FROM dual; -- 15
SELECT TRUNC(15.683, 1) FROM dual; -- 15.6
SELECT TRUNC(15.683, 2) FROM dual; -- 15.68
SELECT TRUNC(15.683, -1) FROM dual; -- 10
-- emp : name, sal, sal์ 5๋ง์๊ถ ๋งค์, sal 1๋ง์๊ถ ๋งค์, ๋๋จธ์ง ๊ธ์ก ์ถ๋ ฅ
-- ์ถ๋ ฅ ์ปฌ๋ผ๋ช
: ์ด๋ฆ ๊ธ์ฌ 5๋ง์๊ถ 1๋ง์๊ถ ๊ธฐํ ๊ธ์ก
-- ์ปฌ๋ผ๋ช
(๋ณ๋ช
)์ ์ซ์๋ก ์์ํ ์ ์๊ณ ๊ณต๋ฐฑ๋ ๋ถ๊ฐ๋ฅ.
-- ๊ณต๋ฐฑ์ด ์๊ฑฐ๋ ๋๋ ์ซ์๋ก ์์ํด์ผ ํ๋ ๊ฒฝ์ฐ์๋ "์นผ๋ผ๋ช "์ฒ๋ผ " ์์ ๊ธฐ์
SELECT name ์ด๋ฆ, sal ๊ธ์ฌ,
TRUNC(sal/50000) "5๋ง์๊ถ์",
TRUNC(MOD(sal,50000)/10000) AS "1๋ง์๊ถ",
MOD(sal, 10000) ๋๋จธ์ง
FROM emp;
SIN(n), COS(n), TAN(n)
์ผ๊ฐ ํจ์ซ๊ฐ์ ๋ฐํ
SELECT SIN(30/180*3.14592),
COS(30/180*3.14592),
TAN(30/180*3.14592)
FROM dual;
EXP(n), POWER(n2, n1), SQRT(n), LOG(n2, n1), LN(n) ๋ฑ
SELECT POWER(2, 10), SQRT(2) FROM dual;
2. ๋จ์ผํ ๋ฌธ์ ํจ์
๋ฌธ์ ํจ์ ์ข
๋ฅ
LOWER(char) ์๋ฌธ์
SELECT LOWER('Seoul'), LOWER('KOREA') FROM dual;
SELECT * FROM col WHERE tname = 'emp'; -- ์ถ๋ ฅ๋๋ ํ์ด ์์
SELECT * FROM col WHERE tname = 'EMP';
-- ์ค๋ผํด์ ๋์
๋๋ฆฌ์ ํ
์ด๋ธ๋ช
(๊ฐ์ฒด๋ช
), ์ปฌ๋ผ๋ช
๋ฑ์ ๋ชจ๋ ๋๋ฌธ์๋ก ์ ์ฅ๋๋ค.
SELECT * FROM col WHERE LOWER(tname) = 'emp';
UPPER(char) ๋๋ฌธ์
SELECT UPPER('Seoul'), UPPER('korea') FROM dual;
SELECT * FROM col WHERE tname = UPPER('emp');
INITCAP(char) ๋จ์ด์ ์ฒซ ๊ธ์๋ง ๋๋ฌธ์๋ก ํ๊ณ ๋๋จธ์ง๋ ์๋ฌธ์๋ก
SELECT INITCAP('KOREA seoul') FROM dual; -- Korea Seoul
CHR(n [ USING NCHAR_CS ]) ascii ์ฝ๋์ ํด๋นํ๋ ๋ฌธ์
SELECT CHR(75) || CHR(79) FROM dual; --KO
ASCII(char) ์ฒซ ๋ฌธ์์ ๋ฐ์ดํฐ๋ฒ ์ด์ค ๋ฌธ์ ์ธํธ์์ 10์ง์ ์ฝ๋๊ฐ
SELECT ASCII('ABC') FROM dual; -- 65
ASCIISTR(char) ์๋ฌธ์์ ์ซ์๋ ๊ทธ๋๋ก ์ถ๋ ฅ๋๊ณ ๋๋จธ์ง non-ascii ๋ฌธ์๋ ์ ๋์ฝ๋ ์ถ๋ ฅ
SELECT ASCIISTR('korea 123'), ASCIISTR('๋ํ') FROM dual;
SUBSTR(char, position [, substring_length ]) : ํน์ ์์น์ ๋ฌธ์์ด ๋ฐํ
-- ์ธ๋ฑ์ค๋ 1๋ถํฐ
SELECT SUBSTR('Seoul Korea', 7, 3) FROM dual; -- Kor
SELECT SUBSTR('Seoul Korea', -5, 3) FROM dual; -- Kor
SELECT SUBSTR('Seoul Korea', 7) FROM dual; -- Korea
-- emp : 1900~1999๋
์์ธ ์ฌ๋. name, rrn, city, sal
SELECT name, rrn, city,sal
FROM emp
WHERE SUBSTR(rrn,8,1) IN(1,2,5,6) AND city='์์ธ';
-- emp : 2000~ ๋
๋ ์ฌ๋. name, rrn, city, sal
SELECT name, rrn, city,sal
FROM emp
WHERE SUBSTR(rrn,8,1) IN(3,4,7,8);
-- emp : 78 ~ 82 ๋
์. name, rrn, city, sal
SELECT name, rrn, city, sal
FROM emp
WHERE SUBSTR(rrn,1,2)>='78' AND SUBSTR(rrn,1,2)<='82';
-- emp : ์์ธ์ฌ๋ ์ด๋ฉด์ ๊น ์จ. name, rrn, city, sal
SELECT name, rrn, city, sal
FROM emp
WHERE city='์์ธ' AND SUBSTR(name,1,1) = '๊น';
-- emp : 80~89 ๋ ์. name, rrn, city, sal
SELECT name, rrn, city, sal
FROM emp
WHERE SUBSTR(rrn, 1, 1) = '8';
-- emp : ๊น ์จ, ์ด ์จ, ์ต ์จ ์ถ๋ ฅ. name, rrn, city, sal
SELECT name, rrn, city, sal
FROM emp
WHERE SUBSTR(name,1,1) IN ('๊น','์ด','์ต');
-- emp : ํ์๋ฌ์ ํ์ด๋ ์ฌ๋ ์ค sal ๋ด๋ฆผ์ฐจ์. name, rrn, city, sal
SELECT name, rrn, city, sal
FROM emp
WHERE MOD(SUBSTR(rrn, 4, 1), 2) = 1
ORDER BY sal DESC;
- INSTR(string , substring [, position [, occurrence ] ]) ๋ฌธ์์ด์ ๊ฒ์ํ์ฌ ์์น๋ฅผ ๋ฐํ. ์์ผ๋ฉด 0
SELECT INSTR('korea seoul', 'e') FROM dual; -- 4
-- ์ฒ์๋ถํฐ ๊ฒ์ํ์ฌ ๊ฐ์ฅ ์ฒ์ ๋ง๋๋ ์์น
SELECT INSTR('korea seoul', 'abc') FROM dual; -- 0
-- ์์ผ๋ฉด 0
SELECT INSTR('korea seoul', 'e', 7) FROM dual; -- 8
-- 7๋ฒ์งธ ์์น(seoul)๋ถํฐ ๊ฒ์
SELECT INSTR('korea seoul', 'e', 1, 2) FROM dual; -- 8
-- ์ฒ์๋ถํฐ ๊ฒ์ํ์ฌ ๋๋ฒ์งธ ๋์ค๋ ์์น
-- emp : ์ด๋ฆ์ '์ด'๋ผ๋ ๋จ์ด๊ฐ ํฌํจ๋ ์ฌ๋. name, tel, sal
SELECT name, tel, sal
FROM emp
WHERE INSTR(name,'์ด') > 0;
-- emp : name, tel, tel ์๋ฒ์ค๋ฒํธ, tel ๊ตญ๋ฒ, tel ๋ฒํธ
-- ์ด๋ฐ ๋ฌธ์ ๋ ์๋ฐ์ ๋ถ๋ฆฌํ๋ ๊ฒ์ด ๋ ํจ์จ์
SELECT name, tel, SUBSTR(tel, 1, INSTR(tel, '-') - 1) ์๋น์ค,
SUBSTR(tel, INSTR(tel, '-') + 1,
INSTR(tel, '-', 1, 2) - INSTR(tel, '-') - 1) ๊ตญ๋ฒ,
SUBSTR(tel, INSTR(tel, '-', 1, 2) + 1) ๋ฒํธ
FROM emp;
- LENGTH(char) ๋ฌธ์์ด์ ๊ธธ์ด. ํ๊ธ๋ 1์์ฉ ์ถ๋ ฅ
SELECT LENGTH('๋ํ๋ฏผ๊ตญ') FROM dual; -- 4
SELECT LENGTHB('๋ํ๋ฏผ๊ตญ') FROM dual; -- 12. byte ์ ์ถ๋ ฅ. ํ๊ธ์ UTF-8 ๋ก ์ ์ฅ๋จ
- REPLACE(char, search_string [, replacement_string])
SELECT REPLACE('seoul korea', 'seoul', 'busan') FROM dual; -- busan korea
SELECT REPLACE('123455245853', '5') FROM dual; -- ๋ชจ๋ 5 ์ ๊ฑฐ(12342483)
-- emp ํ ์ด๋ธ์์ tel์ - ์ ๊ฑฐ
SELECT name, tel, REPLACE(tel, '-') FROM emp;
-- emp ํ
์ด๋ธ์์ tel์ -์ #์ผ๋ก ๋ณ๊ฒฝ
SELECT name, tel, REPLACE(tel, '-', '#') FROM emp;
- CONCAT(char1, char2) ๋ฌธ์์ด ๊ฒฐํฉ
SELECT '์์ธ' || '๋ถ์ฐ' FROM dual; --์์ธ๋ถ์ฐ
SELECT CONCAT('์์ธ', '๋ถ์ฐ') FROM dual; --์์ธ๋ถ์ฐ
- LPAD(expr1, n [, expr2]) : ๋จ๋ ์ผ์ชฝ ๊ณต๊ฐ์ expr2๋ก ์ฑ์
- RPAD(expr1, n [, expr2])
SELECT LPAD('korea', 12, '*') FROM dual; -- *******korea
SELECT RPAD('korea', 12, '*') FROM dual; -- korea*******
SELECT LPAD('korea', 3, '*') FROM dual; -- kor
SELECT LPAD('korea', 0, '*') FROM dual; -- null
SELECT LPAD('๋ํ', 6, '*') FROM dual; -- **๋ํ. LPAD๋ ํ๊ธ์ 2์นธ์ผ๋ก ์ฒ๋ฆฌ
-- emp : name, rrn, rrn(์ฑ๋ณ ๋ค์๋ถํฐ๋ * ๋ก)
SELECT name, rrn, SUBSTR(rrn,1,8) || '******' ์ฃผ๋ฏผ๋ฒํธ
FROM emp;
SELECT name, rrn, RPAD(SUBSTR(rrn,1,8), 14, '*') ์ฃผ๋ฏผ๋ฒํธ
FROM emp;
-- emp : name, tel. ๋จ ์ ํ๋ฒํธ ๋ค 3์๋ฆฌ๋ *๋ก ์ถ๋ ฅ. ์ ๋ฒ ๊ธธ์ด๋ 12, 13 ์ผ์ ์๋ค.
SELECT name, RPAD(SUBSTR(tel,1,LENGTH(tel)-3), LENGTH(tel), '*') tel
FROM emp;
-- last_name์ 9์๋ฆฌ๋ก ์ถ๋ ฅํ๋ฉฐ ๋จ๋ ์๋ฆฌ๋ ํด๋น ์๋ฆฟ์์ ํด๋นํ๋ ์ซ์๋ก ์ถ๋ ฅ
WITH tb AS (
SELECT 'seoul' last_name FROM dual
UNION ALL
SELECT 'haha' last_name FROM dual
UNION ALL
SELECT 'no' last_name FROM dual
)
SELECT RPAD(last_name, 9, SUBSTR('123456789', LENGTH(last_name)+1)) last_name
FROM tb;
SELECT name, sal, LPAD('*', TRUNC(sal/100000), '*') -- ๊ทธ๋ํ
FROM emp;
-- ์ด๋ฆ ๋งํน
๊นํธ -> ๊น*ํธ, ๋๊ฐ๋ค -> ๋*๋ค, ํธ๊ฐ๋๋ค -> ํธ**๋ค
'๐ปProgramming > ๐ฝOracle' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
[Oracle] ๋ฐ์ดํฐ ๋์ ๋๋ฆฌ(Data Dictionary) (0) | 2022.08.10 |
---|---|
[ORACLE] ๋ฐ์ดํฐ ์กฐ์์ธ์ด(DML) (0) | 2022.08.09 |
[ORACLE] ๋ฐ์ดํฐ ์ ์์ธ์ด (DDL) (0) | 2022.08.09 |
[Oracle] ๊ธฐ๋ณธ์ ์ธ SQL (0) | 2022.08.04 |
[Oracle] DQL - Select (0) | 2022.08.04 |