day3

์ƒ์€๐Ÿ‘ธยท2023๋…„ 11์›” 30์ผ
0

๋šœ๋ฒ…๋šœ๋ฒ… ์„ธ๋ฒˆ์งธ

๋ชฉ๋ก ๋ณด๊ธฐ
3/11
post-thumbnail

๐Ÿ“– ๋ฐ์ดํ„ฐ ํƒ€์ž…

(1) VARCHAR2 : ๊ฐ€๋ณ€๊ธธ์ด์˜ ๋ฌธ์ž

(2) NUMBER : ์ˆซ์ž

(3) DATE : ๋‚ ์งœ

(4) CLOB : ํฐ~๋ฌธ์ž

(5) CHAR : ๊ณ ์ •๊ธธ์ด์˜ ๋ฌธ์ž

๐Ÿ“– ๋ฐ์ดํ„ฐ ํƒ€์ž… ๋ณ€ํ™˜

์ˆซ์ž ๋ฌธ์ž ๋‚ ์งœ
NUMBER VARCHER2 DATE
TO_CHAR TO_CHAR
TO_NUMBER TO_DATE

๐Ÿ“Œ TO_CHAR, TO_NUMBER

=> TO_CHAR ๋˜ TO_NUMBER ๋˜ ์—ฐ์‚ฐ์ด ๋ถ™์œผ๋ฉด ๋ชจ๋‘ ์ˆซ์ž๋กœ ์ž๋™ ํ˜•๋ณ€ํ™˜ ํ•ด์คŒ.. ์ˆซ์ž๋ฅผ ๋ฌธ์ž๋กœ ์“ฐ๋ ค๋ฉด || ์‚ฌ์šฉ!

SELECT 
    2+2, 
    TO_CHAR(2) + 123, --๋ฌธ์ž๋กœ ๋ฐ”๊พธ๊ฒ ๋‹ค ํ•˜์ง€๋งŒ ์ž๋™ํ˜•๋ณ€ํ™˜~!
    TO_NUMBER('123123')+300 --์ˆซ์ž๋กœ ๋ฐ”๊พธ๊ฒ ๋‹ค
FROM dual;

๐Ÿ’ป

SELECT 
    sal,
    sal+100,
    sal+'100', --์ˆซ์ž๋กœ ์ž๋™ํ˜•๋ณ€ํ™˜~!
    sal || '100'
FROM emp;

๐Ÿ’ป

๐Ÿ“Œ TO_CHAR TO_DATE

SELECT 
    TO_CHAR(SYSDATE, 'YYYY-MM-DD'), --ํ˜„์žฌ์‹œ๊ฐ„์„ ์ด ํ˜•ํƒœ๋กœ ๋ฐ”๊พธ๊ฒ ๋‹ค
    TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
    TO_CHAR(SYSDATE, 'YY/MM/DD'),
    TO_CHAR(SYSDATE, 'MM-DD'),
    TO_CHAR(SYSDATE, 'DD HH:MI:SS')
FROM dual;

๐Ÿ’ป

SELECT 
    TO_CHAR(hiredate, 'YY/MM/DD') --hiredate๋ฅผ ์ด ํ˜•ํƒœ๋กœ ๋ฐ”๊พธ๊ฒ ๋‹ค
FROM emp;

๐Ÿ’ป

๐ŸŸ 81๋…„๋„์— ์ž…์‚ฌํ•œ ์‚ฌ๋žŒ๋“ค์˜ ์ •๋ณด๋ฅผ ์กฐํšŒํ•ด๋ผ

SELECT * 
FROM emp
WHERE TO_CHAR(hiredate, 'YY') = '81'; --TO_CHAR(hiredate, 'YY')='81' ์—ฐ๋„๊ธฐ์ค€์œผ๋กœ 2์ž๋ฆฌ๋งŒ ๋ฝ‘๊ฒ ๋‹ค->81์„ ๋ฝ‘์•„๋ƒ„

๐Ÿ’ป

๐Ÿฐ Student ํ…Œ์ด๋ธ”์˜ birthday ์ปฌ๋Ÿผ์„ ์‚ฌ์šฉํ•˜์—ฌ ์ƒ์ผ์ด 1์›”์ธ ํ•™์ƒ์˜ ์ด๋ฆ„๊ณผ birthday ๋ฅผ ์ถœ๋ ฅํ•˜์„ธ์š”.

SELECT
    studno,
    name, 
    TO_CHAR(birthday, 'YYYY-MM-DD') birthday --birthday๋ฅผ ์ด ํ˜•ํƒœ๋กœ ๋ฐ”๊พธ๊ฒ ๋‹ค
FROM student
WHERE TO_CHAR(birthday, 'MM') = '01'; --์›”์„ ๋ฝ‘์•„๋‚ด์•ผํ•จ

๐Ÿ’ป

๐ŸŒญ emp ํ…Œ์ด๋ธ”์„ ์กฐํšŒํ•˜์—ฌ comm ๊ฐ’์„ ๊ฐ€์ง€๊ณ  ์žˆ๋Š” ์‚ฌ๋žŒ๋“ค์˜ empno , ename , hiredate, ์ด์—ฐ๋ด‰,15% ์ธ์ƒ ํ›„ ์—ฐ๋ด‰์„ ์•„๋ž˜ ํ™”๋ฉด์ฒ˜๋Ÿผ ์ถœ๋ ฅํ•˜์„ธ์š”. ๋‹จ ์ด์—ฐ๋ด‰์€ (sal*12)+comm ์œผ๋กœ ๊ณ„์‚ฐํ•˜๊ณ ์•„๋ž˜ ํ™”๋ฉด์—์„œ๋Š” SAL ๋กœ ์ถœ๋ ฅ๋˜์—ˆ์œผ๋ฉฐ 15% ์ธ์ƒํ•œ ๊ฐ’์€ ์ด์—ฐ๋ด‰์˜ 15% ์ธ์ƒ ๊ฐ’์ž…๋‹ˆ๋‹ค.

SELECT 
    empno,
    ename,
    TO_CHAR(hiredate, 'YYYY-MM-DD') hiredate,
    TO_CHAR((sal*12)+comm, '$999,999') sal, --TO_CHAR(์ˆซ์ž, '$999,999') : ์ˆซ์ž ์ถœ๋ ฅ์‹œ ์•ž์— $๋ถ™์–ด์„œ ๊ธˆ์•ก์ž๋ฆฟ์ˆ˜๋งˆ๋‹ค ,๋ถ™์Œ
    TO_CHAR((sal*12)+comm + ((sal*12)+comm)*0.15, '$999,999') "15%์ธ์ƒ" --์›๋ž˜์—ฐ๋ด‰ + ์—ฐ๋ด‰15% / ์—ฐ๋ด‰*1.15
FROM emp
WHERE comm IS NOT NULL;

๐Ÿ’ป

๐Ÿ“– NVL(), NVL2()

NVL() : NULL๊ฐ’์„ ๋‹ค๋ฅธ ๊ธฐ๋ณธ๊ฐ’์œผ๋กœ ๋Œ€์ฒด (NULL VALUE)
NVL(ํŠน์ •์ปฌ๋Ÿผ, ๊ธฐ๋ณธ ๋Œ€์ฒด๊ฐ’)

NVL2() : NULL ๊ฐ’ ๋Œ€์ฒด
NVL2(ํŠน์ •์ปฌ๋Ÿผ, ๋„์ด์•„๋‹ˆ๋ฉด(๊ฐ’์ด์žˆ์œผ๋ฉด), ๋„์ด๋ฉด(๊ฐ’์ด์—†์œผ๋ฉด)) == ์‚ผํ•ญ์—ฐ์‚ฐ์ž

SELECT
    position,
    NVL(position, 'member') --position ์ปฌ๋Ÿผ์— null์ธ ๊ฒƒ๋“ค์˜ ๊ธฐ๋ณธ๊ฐ’์„ member์œผ๋กœ ์ง€์ •!
FROM emp2;

๐Ÿ’ป

SELECT 
    empno, 
    ename,  
    comm,
    NVL2(comm, 'Exist', 'NULL') NVL2 --NULL์ด ์•„๋‹Œ ๊ฒฝ์šฐ(๊ฐ’์ด ์žˆ๋Š” ๊ฒฝ์šฐ) Exist, NULL์ธ ๊ฒฝ์šฐ(๊ฐ’์ด ์—†๋Š” ๊ฒฝ์šฐ) NULL
FROM emp
WHERE deptno = 30;

๐Ÿ’ป

๐Ÿ“– DECODE()

DECODE(์ปฌ๋Ÿผ, 100, '๋งž์œผ๋ฉด์—ฌ๊ธฐ', '์•„๋‹ˆ๋ฉด์—ฌ๊ธฐ') --if ์ปฌ๋Ÿผ์ด 100์ด๋ฉด, ์•„๋‹ˆ๋ฉด

DECODE(์ปฌ๋Ÿผ, ์ด ๊ฐ’๊ณผ ๋™์ผํ•˜๋ฉด, '๋งž์œผ๋ฉด์—ฌ๊ธฐ') ์•„๋‹Œ๊ฒฝ์šฐ๋Š” ์ž๋™์œผ๋กœ NULL

DECODE(์ปฌ๋Ÿผ, 100, (DECODE ์ปฌ๋Ÿผ, 200, A), DECODE(์ปฌ๋Ÿผ, 300, C)) --์ปฌ๋Ÿผ์ด 100์ด๋ฉด (DECODE ์ปฌ๋Ÿผ, 200, A) 100์ด ์•„๋‹ˆ๋ฉด DECODE(์ปฌ๋Ÿผ, 300, C)

SELECT 
    name,
    SUBSTR(jumin, 1, 6) || '-' || SUBSTR(jumin, 7, 11) jumin,
    DECODE(SUBSTR(jumin, 7, 1), '1', '๋‚จ์ž', '2', '์—ฌ์ž') ์„ฑ๋ณ„
FROM student
WHERE deptno1 = 101;

๐Ÿ’ป

SELECT
    name,
    tel,
    DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1) , '02', '์„œ์šธ', '031', '๊ฒฝ๊ธฐ', '051', '๋ถ€์‚ฐ', '052', '์šธ์‚ฐ', '055', '๊ฒฝ๋‚จ', '053', '๋Œ€๊ตฌ', NULL) ์ง€์—ญ๋ช…  
FROM student
WHERE deptno1 = 101;

๐Ÿ’ป

profile
๋’ค์ฃฝ๋ฐ•์ฃฝ ๋ฒจ๋กœ๊ทธ

0๊ฐœ์˜ ๋Œ“๊ธ€