๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ

ORACLE/SQL26

[ORACLE] LEAD LAG ์•ž๋’ค ๋ฐ์ดํ„ฐ ์ถœ๋ ฅ :: ๋งˆ์ด์ž๋ชฝ ORACLE ์•ž๋’ค ๋ฐ์ดํ„ฐ ์ถœ๋ ฅ ORACLE SCOTT ๊ณ„์ • EMPํ…Œ์ด๋ธ”์„ ์ด์šฉํ•ด์„œ ์‚ฌ์› ๋ฒˆํ˜ธ๋กœ ์ •๋ ฌ๋œ ๋ฐ์ดํ„ฐ์—์„œ ์•ž ๋’ค ๊ธ‰์—ฌ์™€ ํ•จ๊ป˜ ์ถœ๋ ฅํ•˜์‹œ์˜ค. JOIN ํ’€์ด ์•ž๋’ค ๋ฐ์ดํ„ฐ๋ฅผ ์ถœ๋ ฅํ•˜๊ธฐ ์œ„ํ•ด์„œ ์–ด๋–ป๊ฒŒ ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ•ด์•ผํ• ๊นŒ์š”? ๋ถ„์„ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์ง€ ์•Š๊ณ  ๊ฒฐ๊ณผ๋ฅผ ์ถœ๋ ฅํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” 3๊ฐœ์˜ ํ…Œ์ด๋ธ”์„ ์กฐ์ธํ•˜๋Š” ๋ฐฉ๋ฒ•์ด ์žˆ์Šต๋‹ˆ๋‹ค. ๊ฐ™์€ ํ…Œ์ด๋ธ”์— ROWNUM ๊ฐ’์„ ๋ณ€๊ฒฝํ•˜์—ฌ 3๊ฐœ์˜ ํ…Œ์ด๋ธ”์„ ์กฐ์ธ์‹œ์ผœ ๊ฐ’์„ ์–ป์„ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 SELECT EMPNO ,ENAME ,PREV.SAL AS PREV ,CURR.SAL AS CURR ,NEXT.SAL AS NEXT FROM ( SELE.. 2020. 4. 4.
[Oracle] ๋ˆ„์  ํ•ฉ๊ณ„ ๊ตฌํ•˜๊ธฐ, WINDOWING ํ–‰ ๊ธฐ์ค€ ๋ฒ”์œ„ ์—ฐ์‚ฐ :: ๋งˆ์ด์ž๋ชฝ ๋ˆ„์  ํ•ฉ๊ณ„ ๊ตฌํ•˜๊ธฐ EMPํ…Œ์ด๋ธ”์—์„œ ์‚ฌ์›์˜ ์ •๋ณด์™€ ๋ถ€์„œ๋ฒˆํ˜ธ๋กœ ์ •๋ ฌ๋˜์–ด ์žˆ์„๋•Œ ๊ธ‰์—ฌ์˜ ๋ˆ„์ ํ•ฉ๊ณ„๋„ ๊ฐ™์ด ์ถœ๋ ฅํ•ด๋ผ. ORACLE SCOTT ๊ณ„์ •์œผ๋กœ ์‹ค์Šต์„ ์ง„ํ–‰ํ•ฉ๋‹ˆ๋‹ค. 1 2 3 4 5 6 7 8 9 10 SELECT DEPTNO ,EMPNO ,ENAME ,SAL ,SUM(SAL) OVER( ORDER BY DEPTNO ROWS UNBOUNDED PRECEDING ) AS SUM_SAL FROM EMP; 1 2 3 4 5 6 7 8 9 10 11 SELECT DEPTNO ,EMPNO ,ENAME ,SAL ,SUM(SAL) OVER( ORDER BY DEPTNO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS SUM_SAL FROM EMP; ๋ˆ„์  ํ•ฉ๊ณ„๋ฅผ ๊ตฌํ•˜๊ธฐ ์œ„ํ•ด.. 2020. 4. 3.
[Oracle] LISTAGG ์ปฌ๋Ÿผ ๊ฒฐ๊ณผ ๋‚˜์—ดํ•˜๊ธฐ :: ๋งˆ์ด์ž๋ชฝ 1 2 3 4 5 SELECT DEPTNO ,ENAME FROM EMP ORDER BY DEPTNO; ORACLE SCOTT๊ณ„์ •์˜ EMPํ…Œ์ด๋ธ”์˜ ๋‚ด์šฉ์ž…๋‹ˆ๋‹ค. RDB์— ์ต์ˆ™ํ•ด์ง€๋ฉด ์œ„์™€๊ฐ™์€ ๊ฒฐ๊ณผ๊ฐ€ ๋ˆˆ์— ์ž˜ ๋“ค์–ด์˜ต๋‹ˆ๋‹ค. ํ•˜์ง€๋งŒ, ์ผ๋ฐ˜ ๋ฌธ์„œ์—์„œ๋Š” ๊ฐ ๋ถ€์„œ๋ณ„๋กœ ์–ด๋–ค ์‚ฌ๋žŒ๋“ค์ด ์žˆ๋Š”์ง€ ','๋ฅผ ๊ตฌ๋ถ„์ž๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ๋ณด๋Š”๊ฒŒ ์ดํ•ดํ•˜๊ธฐ ํŽธํ•  ๊ฒƒ ์ž…๋‹ˆ๋‹ค. LISTAGG ํ•จ์ˆ˜ 1 2 3 4 5 SELECT DEPTNO ,LISTAGG(ENAME, ',') WITHIN GROUP(ORDER BY ENAME) AS EMPS FROM EMP GROUP BY DEPTNO; LISTAGGํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜๋ฉด ๊ฐ€๋กœ๋กœ ๋‚˜์—ดํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. GROUP BYํ•œ ๊ฒฐ๊ณผ์— ๋Œ€ํ•ด์„œ GROUP์•ˆ์˜ ์นผ๋Ÿผ์„ ๋‚˜์—ด์‹œํ‚ฌ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. MULTI INDEX ํ™•์ธ LI.. 2020. 4. 2.
[Oracle] PIVOT, UNPIVOT ํ•จ์ˆ˜ ์‚ฌ์šฉ ํ–‰์—ด ์ „ํ™˜ :: ๋งˆ์ด์ž๋ชฝ ํ–‰์—ด ์ „ํ™˜ ORACLE SCOTT ๊ณ„์ • EMP ํ…Œ์ด๋ธ”์—์„œ 2๊ฐœ์˜ ์นผ๋Ÿผ์„ ์ด์šฉํ•˜์—ฌ GROUP BY ๊ฒฐ๊ณผ๋ฅผ ํ™•์ธํ•ด๋ดค์Šต๋‹ˆ๋‹ค. ํ•˜๊ณ  ์‹ถ์€ ์ž‘์—…์€ GROUPINGํ•œ ์นผ๋Ÿผ์„ ๊ฐ€๋กœ ์„ธ๋กœ์ถ•์œผ๋กœ ๋‘๊ณ  ๋ฐ์ดํ„ฐ๋ฅผ ์ถœ๋ ฅํ•˜๋ ค๊ณ  ํ•ฉ๋‹ˆ๋‹ค. CASE ํ‘œํ˜„์‹์ด๋‚˜ DECODE ํ•จ์ˆ˜๋ฅผ ์ด์šฉํ•ด์„œ ๊ฒฐ๊ณผ๋ฅผ ์ถœ๋ ฅํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. [Oracle] DECODE ํ•จ์ˆ˜ ์‚ฌ์šฉ ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ ๊ฐ€๋กœ๋กœ ๋ณด๊ธฐ :: ๋งˆ์ด์ž๋ชฝ DECODE ํ•จ์ˆ˜ DECODE ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์ฟผ๋ฆฌ๋‚ด์˜ ์กฐ๊ฑด๋ฌธ ์ฒ˜๋Ÿผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ฒซ๋ฒˆ์งธ ์ธ์ž๋กœ ํ™•์ธํ•  ๋Œ€์ƒ์„ ๋„ฃ์–ด์ฃผ๊ณ  2๋ฒˆ์งธ ์ธ์ž์— ํ™•์ธํ•  ๋Œ€์ƒ์˜ ์˜ˆ์ธก ๊ฐ’์ด ์žˆ์œผ๋ฉด 3๋ฒˆ์งธ ์ธ์ž๋ฅผ ์—†์œผ๋ฉด 4๋ฒˆ์งธ ์ธ์ž์˜ ๊ฐ’์„ ๋ฐ˜.. myjamong.tistory.com DECODE ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜๋ฉด ๊ฐ™์€ ํ•จ์ˆ˜๋ฅผ ๊ณ„์† ์‚ฌ์šฉํ•˜๋ฉด์„œ ์ค‘๋ณต๋˜๋Š” ์ž‘์—…์„ ํ•ด์•ผํ•ฉ๋‹ˆ๋‹ค. .. 2020. 3. 30.
[Oracle] ROW_NUMBER, RANK, DENSE_RANK ํ•จ์ˆ˜ ์ˆœ์œ„ ๋งค๊ธฐ๊ธฐ :: ๋งˆ์ด์ž๋ชฝ ์‚ฌ์›๋“ค์˜ ๊ธ‰์—ฌ๋ฅผ ๋งŽ์ด ๋ฐ›๋Š” ์ˆœ์„œ๋Œ€๋กœ ์ˆœ์œ„๋ฅผ ์ถœ๋ ฅํ•˜์‹œ์˜ค. ORACLE SCOTT ๊ณ„์ •์˜ EMPLOYEES ํ…Œ์ด๋ธ”์„ ์ด์šฉํ•ด์„œ ์ถœ๋ ฅํ•œ ๊ฒฐ๊ณผ์ž…๋‹ˆ๋‹ค. ์ˆœ์„œ๋ฅผ ๋งค๊ธฐ๋Š” ๋ฐฉ๋ฒ•์—๋Š” ์—ฌ๋Ÿฌ๊ฐ€์ง€ ๋ฐฉ๋ฒ•์ด ์žˆ์Šต๋‹ˆ๋‹ค. ๋ถ„์„ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์ง€ ์•Š๋Š”๋‹ค๋ฉด ์•„๋ž˜์ฒ˜๋Ÿผ ๊ตฌํ• ๊ฒƒ ์ž…๋‹ˆ๋‹ค. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT EMPNO ,ENAME ,SAL ,DEPTNO ,ROWNUM AS RNK FROM ( SELECT * FROM EMP ORDER BY SAL DESC ); SELECT ์ ˆ์€ ORDER BY ์ด์ „์— ์‹คํ–‰๋˜๊ธฐ ๋•Œ๋ฌธ์— SUBQUERY๋ฅผ ์ด์šฉํ•ด์„œ ์ˆœ์œ„๋ฅผ ๋งค๊ธธ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ „์ฒด ์ˆœ์œ„์ค‘ ์ผ๋ถ€๋ฅผ ์ถœ๋ ฅํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” TOP-N์ฟผ๋ฆฌ๋‚˜ ROW LIMIT CLAUSE๋ฅผ ์‚ฌ์šฉํ•ด์„œ ์ถœ๋ ฅ์ด ๊ฐ€๋Šฅํ•ฉ๋‹ˆ๋‹ค. ์ž์„ธํ•œ ๋‚ด์šฉ์€ ์•„.. 2020. 3. 29.
[Oracle] OVER ... PARTITION BY ๋ถ„์„์ ˆ ๋ถ„์„ํ•จ์ˆ˜ ANALYTIC FUNCTION :: ๋งˆ์ด์ž๋ชฝ ์‚ฌ์› ์ •๋ณด๋ฅผ ์ž…๋ ฅํ•˜๋Š”๋ฐ, ์ „์ฒด ๊ธ‰์—ฌ ํ‰๊ท ๊ฐ’๊ณผ ํ•จ๊ป˜ ์ถœ๋ ฅํ•˜์‹œ์˜ค. ORACLE SCOTT ๊ณ„์ • EMP ํ…Œ์ด๋ธ”๋กœ ์ถœ๋ ฅ๋œ ๊ฒฐ๊ณผ์ž…๋‹ˆ๋‹ค. ์œ„ ๋ฌธ์ œ๋ฅผ ํ’€๊ธฐ ์œ„ํ•ด ์–ด๋–ป๊ฒŒ ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ•˜์‹ค๊ฑด๊ฐ€์š”? 1 2 3 4 5 6 7 8 SELECT DEPTNO ,EMPNO ,ENAME ,JOB ,SAL ,ROUND((SELECT AVG(SAL) FROM EMP)) AS AVG FROM EMP; SUBQUERY๋ฅผ ์ด์šฉํ•  ์ˆ˜๋„ ์žˆ์ง€๋งŒ... ANALYTIC FUNCTION ๋ถ„์„ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•ด์„œ ์ข€ ๋” ํŽธํ•œ ๋ฐฉ๋ฒ•์œผ๋กœ ํ•ด๊ฒฐํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. 1 2 3 4 5 6 7 8 SELECT DEPTNO ,EMPNO ,ENAME ,JOB ,SAL ,ROUND(AVG(SAL) OVER()) AS AVG FROM EMP; ๋ถ„์„ํ•จ์ˆ˜ OVER ์ ˆ์„ ์‚ฌ์šฉํ•ด์„œ ๋ฌธ์ œ.. 2020. 3. 28.
[Oracle] Multiple INSERT ALL, FIRST ๋‹ค์ค‘ ํ–‰ ์‚ฝ์ž… ์ฐจ์ด :: ๋งˆ์ด์ž๋ชฝ ์ „์ฒด INSERT & ์กฐ๊ฑด๋ถ€ INSERT ๋‹ค์ค‘ํ–‰ INSERT์—์„œ ALL๊ณผ FIRST๋กœ ๊ตฌ๋ถ„ํ•˜๊ธฐ ์ „์— ์ „์ฒด INSERT์™€ ์กฐ๊ฑด๋ถ€ INSERT๋กœ ๊ตฌ๋ถ„ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ๋ง๊ทธ๋Œ€๋กœ ์ „์ฒด๋Š” ๋ชจ๋“  ํ–‰์„ ์‚ฝ์ž…ํ•˜๋Š” ๊ฒƒ์ด๊ณ , ์กฐ๊ฑด๋ถ€ INSERT๋Š” ์กฐ๊ฑด์— ๋”ฐ๋ผ INSERTํ•  ์ˆ˜ ์žˆ๋Š” ๊ธฐ๋Šฅ์ž…๋‹ˆ๋‹ค. Multiple Insert๋ฅผ ์‹ค์Šตํ•ด๋ณด๊ธฐ ์œ„ํ•ด Oracle HR ๊ณ„์ •์„ ์‚ฌ์šฉํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค. ์ „์ฒด INSERT ALL ์ž„์‹œ ์‚ฌ์› ํ…Œ์ด๋ธ” ์ƒ์„ฑ 1 2 3 4 5 6 7 8 9 10 11 --ํ˜„์žฌ ๊ทผ๋ฌดํ•˜๋Š” ์‚ฌ์› ํ…Œ์ด๋ธ” CREATE TABLE CURR_EMP AS SELECT EMPLOYEE_ID ,FIRST_NAME ,HIRE_DATE ,JOB_ID ,SALARY FROM EMPLOYEES WHERE 1=0; Multiple Insert ์ž‘.. 2020. 3. 27.
[Oracle] ROLLUP ๊ณ ๊ธ‰ ์‚ฌ์šฉ๋ฒ• GROUPING ํ•จ์ˆ˜ :: ๋งˆ์ด์ž๋ชฝ GROUP BY 1 2 3 4 5 6 SELECT DEPTNO ,JOB ,SUM(SAL) FROM EMP GROUP BY DEPTNO, JOB; ORACLE SCOTT ๊ณ„์ •์˜ EMP ํ…Œ์ด๋ธ”์„ ์‚ฌ์šฉํ•ด์„œ ์‹ค์Šต์„ ์ง„ํ–‰ํ–ˆ์Šต๋‹ˆ๋‹ค. ์ผ๋ฐ˜์ ์œผ๋กœ ์‚ฌ์šฉํ•˜๋Š” GROUP BY ๋ฌธ์ž…๋‹ˆ๋‹ค. SUM ํ•จ์ˆ˜๋ฅผ ์ด์šฉํ•ด์„œ ๊ฐ ๋ถ€์„œ๋ณ„, ์ง์—…๋ณ„ ๊ธ‰์—ฌ์˜ ํ•ฉ์„ ๊ตฌํ–ˆ์Šต๋‹ˆ๋‹ค. ROLLUP 1 2 3 4 5 6 SELECT DEPTNO ,JOB ,SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO, JOB); ์œ„์˜ GROUP BY์ ˆ์— ROLLUP์„ ์ถ”๊ฐ€ํ–ˆ์Šต๋‹ˆ๋‹ค. ๊ฒฐ๊ณผ๋ฅผ ๋ถ€์„œ๋ณ„, ์ง์—…๋ณ„ ๋ฟ๋งŒ์•„๋‹ˆ๋ผ ์ „์ฒด ๊ธ‰์—ฌ์˜ ํ•ฉ๊ณผ ๋ถ€์„œ๋ณ„ ๊ธ‰์—ฌ์˜ ํ•ฉ์„ ํ•จ๊ป˜ ์ถœ๋ ฅํ•ฉ๋‹ˆ๋‹ค. ๊ทธ๋Ÿฐ๋ฐ ์—ฌ๊ธฐ์„œ ์˜๋ฌธ์ด ๋“œ๋Š”๊ฒŒ ์žˆ์Šต๋‹ˆ๋‹ค. ์ „์ฒด ๊ธ‰์—ฌ๋กœ๋„ ํ•ฉ์„ ๊ณ„์‚ฐํ•ด์ฃผ๊ณ  ๋ถ€์„œ๋ณ„๋กœ.. 2020. 3. 26.
[ORACLE] CTAS CREATE TABLE AS SELECT ์‚ฌ์šฉ :: ๋งˆ์ด์ž๋ชฝ CTAS CREATE TABLE AS SELECT ... SUBQUERY SELECT๋ฌธ์„ ์ด์šฉํ•ด์„œ ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๋Š” ๋ฐฉ์‹์ž…๋‹ˆ๋‹ค. CREATE TABLE AS SELECT์˜ ์•ž ๊ธ€์ž๋ฅผ ๋”ฐ์„œ CTAS๋ผ๊ณ ๋„ ํ•ฉ๋‹ˆ๋‹ค. CTAS๋ฌธ์€ ์ฃผ๋กœ OLAP์ฒ˜๋Ÿผ ๋ถ„์„์ž‘์—…์ด ๋งŽ์€ ํ™˜๊ฒฝ์—์„œ ์ฃผ๋กœ ์‚ฌ์šฉํ•˜๊ฒŒ ๋ฉ๋‹ˆ๋‹ค. ํ•„์š”ํ•œ ๋ฐ์ดํ„ฐ๋ฅผ ์›๋ณธ๋ฐ์ดํ„ฐ์˜ ์นดํ”ผ๋กœ ๋งŒ๋“ค์–ด ์†๋„๋„ ํ–ฅ์ƒ์‹œํ‚ฌ์ˆ˜ ์žˆ๊ณ  ์›๋ฐ์ดํ„ฐ๋ฅผ ํ›ผ์†์‹œํ‚ค์ง€ ์•Š๊ณ  ์ž‘์—…์„ ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. CTAS ์ƒ์„ฑ Oracle HR๊ณ„์ • EMPLOYEES ํ…Œ์ด๋ธ”์—์„œ 90๋ฒˆ๋ถ€์„œ์—์„œ ๊ทผ๋ฌดํ•˜๋Š” ์‚ฌ์›๋“ค๋งŒ ๋”ฐ๋กœ ๋ชจ์€ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค. 1 2 3 4 5 6 7 8 9 CREATE TABLE DEPT_90 AS SELECT EMPLOYEE_ID ,FIRST_NAME ,SALARY FROM EMPLOY.. 2020. 3. 24.
[Oracle] ORA-00955 name is already used by an existing object ํ•ด๊ฒฐ :: ๋งˆ์ด์ž๋ชฝ ORA-00955 ORA-00955 : ๊ธฐ์กด์˜ ๊ฐ์ฒด๊ฐ€ ์ด๋ฆ„์„ ์‚ฌ์šฉํ•˜๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค. "name is already used by an existing object" ํ…Œ์ด๋ธ”, ๋ทฐ, ์‹œํ€€์Šค ๋“ฑ ์˜ค๋ผํด ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•˜๋ ค๊ณ ํ•˜๋Š”๋ฐ ์œ„์™€ ๊ฐ™์€ ์—๋Ÿฌ๊ฐ€ ๋ฐœ์ƒํ•ฉ๋‹ˆ๋‹ค. ์ฃผ๋กœ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๋ ค๋Š”๋ฐ ์ด๋Ÿฐ ์—๋Ÿฌ๊ฐ€ ๋ฐœ์ƒํ•ด์„œ ํ…Œ์ด๋ธ”์„ DROP์‹œํ‚ค๋ ค๊ณ  ํ•˜๋Š”๋ฐ ํ…Œ์ด๋ธ”์€ ์กด์žฌํ•˜์ง€ ์•Š๋Š”๋‹ค๊ณ  ํ•˜๊ณ ... ๋‹ค์‹œ ๊ฐ™์€ ์ด๋ฆ„์œผ๋กœ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๋ ค๋Š”๋ฐ ์ด๋ฏธ ์‚ฌ์šฉํ•˜๊ณ  ์žˆ๋Š” ์ด๋ฆ„์ด๋ผ๊ณ ํ•˜๊ณ ... ๋„๋ฐ์ฒด ๋ญ๊ฐ€ ๋ฌธ์ œ์ธ์ง€.... ์˜ค๋ผํด์—์„œ๋Š” ๊ฐ์ฒด์˜ ํƒ€์ž…์ด ๋‹ค๋ฅด๋”๋ผ๋„ ๊ฐ™์€ ๋ช…์นญ์œผ๋กœ ์ƒ์„ฑํ•  ์ˆ˜ ์—†์Šต๋‹ˆ๋‹ค. ๊ทธ๋ž˜์„œ ์œ„์™€ ๊ฐ™์€ ์ƒํ™ฉ์ด ๋ฒŒ์–ด์กŒ์„ ๋•Œ๋Š” ๋‹ค๋ฅธ ๊ฐ์ฒด ํƒ€์ž…์œผ๋กœ ํ•ด๋‹น ๋ช…์นญ์ด ์ƒ์„ฑ๋˜์–ด ์žˆ์„ ๊ฒ๋‹ˆ๋‹ค. ๋ฌธ์ œ๋ฅผ ํ•ด๊ฒฐํ•˜๊ธฐ ์œ„ํ•ด Data Dictionary์˜ ํ•ด๋‹น ์œ ์ €๊ฐ€ ์–ด๋–ค .. 2020. 3. 24.
[Oracle] ์˜ค๋ผํด DELETE, TRUNCATE ์ฐจ์ด(HIGH WATER MARK) :: ๋งˆ์ด์ž๋ชฝ ์˜ค๋ผํด์—์„œ๋Š” ๋ฐ์ดํ„ฐ๋ฅผ ์‚ญ์ œํ•˜๋Š” ๋ช…๋ น์–ด๋กœ DELETE, TRUNCATE, DROP ์ด ์žˆ์Šต๋‹ˆ๋‹ค. DROP์˜ ๊ฒฝ์šฐ ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ๊นŒ์ง€ ์ „๋ถ€์‚ญ์ œ๋ฅผ ํ•ด์ฃผ๊ณ  DELETE์™€ TRUNCATE์€ ํ…Œ์ด๋ธ”์„ ๋‚จ๊ธฐ๊ณ  ๋ฐ์ดํ„ฐ๋งŒ ์‚ญ์ œํ•ฉ๋‹ˆ๋‹ค. DELETE์™€ TRUNCATE ์ฐจ์ด DML๊ณผ DDL DELETE์™€ TRUNCATE์˜ ๊ฐ€์žฅ ํฐ ์ฐจ์ด๋Š” DML๊ณผ DDL์ด๋ผ๋Š” ๊ฒƒ์ž…๋‹ˆ๋‹ค. DDL์˜ ๊ฒฝ์šฐ ํ•˜๋‚˜์˜ ๋ช…๋ น์ด ํŠธ๋žœ์žญ์…˜์˜ ์‹œ์ž‘๊ณผ ๋์ด๊ธฐ ๋•Œ๋ฌธ์— ๋ฐ”๋กœ ๋ฐ˜์˜์ด ๋˜์–ด๋ฒ„๋ฆฝ๋‹ˆ๋‹ค. ๊ทธ๋ž˜์„œ TRUNCATE์€ ROLLBACK์ด ๋ถˆ๊ฐ€๋Šฅํ•œ ๋ฐ˜๋ฉด DELETE์€ DML ๋ช…๋ น์–ด์ด๋ฏ€๋กœ TRANSACTION ๋‹จ์œ„๋กœ ROLLBACK๊ณผ COMMIT์ด ๊ฐ€๋Šฅํ•ฉ๋‹ˆ๋‹ค. ์ €์žฅ๊ณต๊ฐ„ ๋ฐ˜๋‚ฉ๊ณผ ์œ ์ง€ EMP ํ…Œ์ด๋ธ”์—์„œ 192๋ฒˆ์‚ฌ์›์„ ์ฐพ๋Š”๋‹ค๊ณ  ๊ฐ€์ •ํ•ด๋ด…๋‹ˆ๋‹ค. SQL ๋ช…๋ น ์ˆœ์„œ์— ์˜ํ•ด์„œ FRO.. 2020. 3. 23.
[Oracle] ์˜ค๋ผํด WITH, RECURSIVE WITH ์žฌ๊ท€ ์ฟผ๋ฆฌ ์‚ฌ์šฉ :: ๋งˆ์ด์ž๋ชฝ ๋‚˜๋ผ๋ณ„ ๊ธ‰์—ฌ ํ‰๊ท ์˜ ํ‰๊ท ๋ณด๋‹ค ๋งŽ์ด ๊ธ‰์—ฌ๋ฅผ ์ฃผ๊ณ  ์žˆ๋Š” ๋‚˜๋ผ์™€ ๋‚˜๋ผ์˜ ํ‰๊ท  ๊ธ‰์—ฌ๋ฅผ ๊ตฌํ•˜์‹œ์˜ค. (๋‚˜๋ผ๋ช…๊ณผ ๋‚˜๋ผ์˜ ํ‰๊ท  ๊ธ‰์—ฌ๋งŒ ์ถœ๋ ฅ) ์œ„์˜ ๋ฌธ์ œ๋ฅผ ํ’€๊ธฐ ์œ„ํ•ด ์–ด๋–ป๊ฒŒ ํ•ด์•ผํ• ๊นŒ์š”? ๋‚˜๋ผ๋ณ„ ํ‰๊ท  ๊ธ‰์—ฌ๋ฅผ ์กฐ์ธ๋ฌธ์œผ๋กœ ์ด์šฉํ•ด์„œ ๊ตฌํ•˜๋Š” ์ฟผ๋ฆฌ๋ฅผ ์ฟผ๋ฆฌA๋ผ๊ณ  ํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค. ํ‰๊ท ์˜ ํ‰๊ท ๊ฐ’์„ ๊ตฌํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” ์ฟผ๋ฆฌA๋ฅผ SUBQUERY๋กœ ์‚ฌ์šฉํ•ด์„œ ํ‰๊ท ์„ ๊ตฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ํ‰๊ท ์˜ ํ‰๊ท ๊ฐ’๋ณด๋‹ค ๋†’์€ ๊ฒฐ๊ณผ๋ฅผ ์ถœ๋ ฅํ•˜๊ธฐ ์œ„ํ•ด์„œ ์ฟผ๋ฆฌA ๊ฒฐ๊ณผ์˜ PROJECTION์ด ํ•„์š”ํ•ฉ๋‹ˆ๋‹ค. ์œ„ ๊ณผ์ •์„ ์ƒ๊ฐํ•ด๋ดค์„๋•Œ ์ฟผ๋ฆฌA๋Š” ์ „์ฒด ์ฟผ๋ฆฌ์—์„œ 2๋ฒˆ ์‚ฌ์šฉํ•ด์•ผํ•ฉ๋‹ˆ๋‹ค. 2๋ฒˆ ์‚ฌ์šฉํ•˜๊ฒŒ๋˜๋ฉด ํผํฌ๋จผ์Šค๋„ ๋‚ฎ์•„์ง€๊ณ  ์ฟผ๋ฆฌ์˜ ๊ฐ€๋…์„ฑ๋„ ๋–จ์–ด์ง€๊ฒŒ ๋ฉ๋‹ˆ๋‹ค. ์ด๋Ÿฌํ•œ ๋ฌธ์ œ๋ฅผ ํ•ด๊ฒฐํ•˜๊ธฐ ์œ„ํ•ด์„œ WITH์ ˆ์„ ์‚ฌ์šฉํ•ด์„œ ์ฟผ๋ฆฌ๋ฌธ์„ ์žฌ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์‹ค์Šต์€ Oracle HR ๊ณ„์ •์œผ๋กœ ์ง„ํ–‰ํ–ˆ์Šต๋‹ˆ๋‹ค. WI.. 2020. 3. 23.
[Oracle] Correlated Subquery ์ƒํ˜ธ์—ฐ๊ด€์ฟผ๋ฆฌ :: ๋งˆ์ด์ž๋ชฝ Correlated Subquery Correlated Subquery๋Š” ๋‚ด๋ถ€ Subquery์—์„œ ์™ธ๋ถ€ํ…Œ์ด๋ธ”์˜ ๊ฐ’์„ ์ฐธ์กฐํ•  ๋•Œ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค. Subquery์™€๋Š” ๋‹ค๋ฅด๊ฒŒ Inner Query ๋ถ€ํ„ฐ Outer Query ์ˆœ์„œ๋Œ€๋กœ ์‹คํ–‰๋˜๋Š” ๊ฒƒ์ด ์•„๋‹ˆ๋ผ Outer Query์—์„œ ์ฝ์–ด์˜จ ํ–‰์„ ๊ฐ–๊ณ  Inner์ฟผ๋ฆฌ๋ฅผ ์‹คํ–‰ํ•˜๋Š” ๊ฒƒ์„ ๋ฐ˜๋ณตํ•˜์—ฌ ๊ฒฐ๊ณผ๋ฅผ ์ถœ๋ ฅํ•ด์ค๋‹ˆ๋‹ค. Outer Query์™€ Inner Query์—์„œ ๊ฐ™์€ ํ…Œ์ด๋ธ”์„ ์ฐธ์กฐํ•œ๋‹ค๋ฉด Outer Query์˜ ํ…Œ์ด๋ธ”์— Alias๋ฅผ ์‚ฌ์šฉํ•ด์„œ ๊ตฌ๋ถ„ํ•ด์ค๋‹ˆ๋‹ค. ์˜ˆ์‹œ๋ฌธ์ œ Oracle HR ๊ณ„์ • Employees ํ…Œ์ด๋ธ”์„ ์‚ฌ์šฉํ•ด์„œ ์†Œ์†๋œ ๋ถ€์„œ์˜ ํ‰๊ท  ๊ธ‰์—ฌ๋ณด๋‹ค ๋งŽ์€ ๊ธ‰์—ฌ๋ฅผ ๋ฐ›๋Š” ์‚ฌ์›์„ ์ถœ๋ ฅํ•˜์‹œ์˜ค. 1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT FIRST_NAME ,.. 2020. 3. 21.
[Oracle] DECODE ํ•จ์ˆ˜ ์‚ฌ์šฉ ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ ๊ฐ€๋กœ๋กœ ๋ณด๊ธฐ :: ๋งˆ์ด์ž๋ชฝ DECODE ํ•จ์ˆ˜ DECODE ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์ฟผ๋ฆฌ๋‚ด์˜ ์กฐ๊ฑด๋ฌธ ์ฒ˜๋Ÿผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ฒซ๋ฒˆ์งธ ์ธ์ž๋กœ ํ™•์ธํ•  ๋Œ€์ƒ์„ ๋„ฃ์–ด์ฃผ๊ณ  2๋ฒˆ์งธ ์ธ์ž์— ํ™•์ธํ•  ๋Œ€์ƒ์˜ ์˜ˆ์ธก ๊ฐ’์ด ์žˆ์œผ๋ฉด 3๋ฒˆ์งธ ์ธ์ž๋ฅผ ์—†์œผ๋ฉด 4๋ฒˆ์งธ ์ธ์ž์˜ ๊ฐ’์„ ๋ฐ˜ํ™˜ํ•ด์ค๋‹ˆ๋‹ค. CASE ํ‘œํ˜„์‹๊ณผ๋Š” ๋‹ค๋ฅด๊ฒŒ ๋ฒ”์œ„์‹์˜ ์กฐ๊ฑด์„ ๋„ฃ์„์ˆ˜ ์—†๊ณ  2๋ฒˆ์งธ ์ธ์ž๊ฐ€ ์žˆ๋Š”์ง€ ์—†๋Š”์ง€ ํ™•์ธํ•ฉ๋‹ˆ๋‹ค. Oracle Scott ๊ณ„์ •์œผ๋กœ DECODE ํ•จ์ˆ˜ ์‚ฌ์šฉ ์˜ˆ์‹œ๋ฅผ ์•Œ์•„๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค. ์„ธ๋กœ๊ฒฐ๊ณผ๋ฅผ ๊ฐ€๋กœ๋กœ 1 2 3 4 5 6 SELECT DEPARTMENT_ID ,SUM(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID 2020. 3. 20.
[Oracle] NON-EQUI JOIN ๋น„๋“ฑ๊ฐ€ ์กฐ์ธ ์ •๋ฆฌ :: ๋งˆ์ด์ž๋ชฝ NON-EQUI JOIN ๋‘๊ฐœ์˜ ํ…Œ์ด๋ธ”์„ ํŠน์ •ํ•œ ํ‚ค๋ฅผ ๊ธฐ์ค€์œผ๋กœ ํ•ฉ์ณ์„œ ๋ฐ์ดํ„ฐ๋ฅผ ํ‘œํ˜„ํ•˜๋Š” ๊ฒƒ์„ JOIN์ด๋ผ๊ณ  ํ•ฉ๋‹ˆ๋‹ค. ์‚ฌ์› ํ…Œ์ด๋ธ”์— ๋ถ€์„œ ๋ฒˆํ˜ธ๊ฐ€ ์žˆ๊ณ  ๋ถ€์„œ ํ…Œ์ด๋ธ”์— ๋ถ€์„œ๋ช…์ด ์žˆ์–ด์„œ ์‚ฌ์›์˜ ์ด๋ฆ„๊ณผ ๋ถ€์„œ๋ช…์„ ํ™•์ธํ•˜๊ธฐ ์œ„ํ•ด์„œ ์‚ฌ์›ํ…Œ์ด๋ธ”์˜ ๋ถ€์„œ๋ฒˆํ˜ธ์™€ ๋ถ€์„œ ํ…Œ์ด๋ธ”์˜ ๋ถ€์„œ๋ฒˆํ˜ธ๋กœ JOIN ์‹œ์ผœ ๋ฐ์ดํ„ฐ๋ฅผ ํ‘œํ˜„ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ด๋ ‡๊ฒŒ ํŠน์ • ์นผ๋Ÿผ์€ ๊ฐ™๋‹ค๋Š” ํ˜•์‹์œผ๋กœ ์กฐ์ธ์„ ์‹œํ‚ค๋Š” ๊ฒƒ์€ EQUI JOIN์ด๋ผ๊ณ  ํ•ฉ๋‹ˆ๋‹ค. ๊ทธ๋Ÿผ NON-EQUI JOIN์€ ๋ญ˜๊นŒ์š”? ์ƒํ˜ธํ‰๊ฐ€ ๋“ฑ๊ธ‰, ์„ฑ์  ๋“ฑ๊ธ‰๊ณผ ๊ฐ™์ด ํŠน์ • ๋ฒ”์œ„์™€ ๋“ฑ๊ธ‰์„ ์ง€์ •ํ•˜๊ณ  ํ•ด๋‹น ๋ฒ”์œ„์— ๋“ค์—ˆ์„ ๋•Œ ๋“ฑ๊ธ‰์œผ๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ํ‘œํ˜„ํ•˜๊ธฐ ์œ„ํ•ด ์‚ฌ์šฉ๋˜๋Š” ๊ฒƒ์ด NON-EQUI JOIN์ž…๋‹ˆ๋‹ค. NON-EQUI JOIN์€ EQUI JOIN๊ณผ ๋‹ค๋ฅด๊ฒŒ ํŠน์ • ์นผ๋Ÿผ์„ ๋ฒ”์œ„๋กœ JOIN ์‹œํ‚ต๋‹ˆ๋‹ค. Oracle S.. 2020. 3. 19.
[Oracle] ์˜ค๋ผํด GROUP BY ROLLUP, CUBE, GROUPING SETS ์ •๋ฆฌ :: ๋งˆ์ด์ž๋ชฝ GROUP BY ํŠน์ • ์นผ๋Ÿผ๋“ค์„ ๊ธฐ์ค€์œผ๋กœ ๊ทธ๋ฃนํ™”ํ•˜์—ฌ ํ•ฉ์‚ฐ, ํ‰๊ท , ์ตœ๊ณ ๊ฐ’, ์ตœ์†Œ๊ฐ’ ๋“ฑ์˜ ์ˆ˜์น˜๋ฅผ ํ™•์ธํ•˜๊ธฐ ์œ„ํ•ด GROUP BY ์ ˆ์„ ์ด์šฉํ•ฉ๋‹ˆ๋‹ค. ๋ถ€์„œ๋ณ„ ์—ฐ๋ด‰ ํ‰๊ท , ๋ฐ˜ ์‹œํ—˜ ์ตœ๊ณ  ์ ์ˆ˜, ๋งค์žฅ๋ณ„ ์žฌ๊ณ ๋Ÿ‰๊ณผ ๊ฐ™์ด ํ•˜๋‚˜์˜ ๊ธฐ์ค€์œผ๋กœ ๊ทธ๋ฃน์„ ์‹œํ‚ฌ๋•Œ๋Š” ํ•œ๊ฐ€์ง€ ๊ฒฐ๊ณผ๋งŒ ํ™•์ธํ•˜๋ฉด ๋ฉ๋‹ˆ๋‹ค. ํ•˜์ง€๋งŒ ๋‘ ๊ฐ€์ง€ ์ด์ƒ์˜ ๊ธฐ์ค€์œผ๋กœ GROUP BY ์‹œ์ผฐ์„๋•Œ๋Š” ํ‘œํ˜•์‹์œผ๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ํ™•์ธํ•˜๊ณ  ์‹ถ๋‹ค๋Š” ์ƒ๊ฐ์ด ๋“ค๊ฒƒ์ž…๋‹ˆ๋‹ค. ๋ถ€์„œ๋ณ„ ํŒ€๋ณ„ ์—ฐ๋ด‰ ํ‰๊ท , ๋ฐ˜๋ณ„ ๊ณผ๋ชฉ๋ณ„ ์‹œํ—˜ ์ตœ๊ณ  ์ง‘์ˆ˜, ๋งค์žฅ๋ณ„ ์นดํ…Œ๊ณ ๋ฆฌ๋ณ„ ์žฌ๊ณ ๋Ÿ‰์ฒ˜๋Ÿผ 2๊ฐœ์˜ ๊ธฐ์ค€์ด ์žˆ์„๋•Œ๋Š” ๊ฐ€๋กœ ์„ธ๋กœ ๊ฐ๊ฐ ๊ธฐ์ค€์œผ๋กœ ํ‘œํ˜•ํƒœ๋กœ ์ถœ๋ ฅ๋˜๋Š” ๋ฐ์ดํ„ฐ๊ฐ€ ์ฒ˜์Œ ๋– ์˜ฌ๋ž์„ ๊ฒƒ์ž…๋‹ˆ๋‹ค. ์˜ˆ๋ฅผ ๋“ค์–ด ๋ถ€์„œ๋ณ„ ์ง์—…๋ณ„ ์—ฐ๋ด‰ ํ•ฉ์‚ฐ๊ธˆ์•ก์„ ์•Œ๊ณ  ์‹ถ๋‹ค๋ฉด ๊ฐ€๋กœ์— ๋ถ€์„œ, ์„ธ๋กœ์— ์ง์—…์„ ๋†“์€ ํ‘œํ˜•ํƒœ์™€ ๊ฐ๊ฐ ๊ทธ๋ฃน๋ณ„๋กœ ๋”ฐ๋กœ๋”ฐ๋กœ ๊ทธ๋ฆฌ๊ณ  ์ „์ฒด ํ•ฉ์‚ฐ๋„ ์ž๋™์œผ.. 2020. 3. 18.
[Oracle] SQL SELECT ์ฟผ๋ฆฌ ์‹คํ–‰ ์ˆœ์„œ ์ฒ˜๋ฆฌ ๊ณผ์ • :: ๋งˆ์ด์ž๋ชฝ SELECT ์ฟผ๋ฆฌ ์‹คํ–‰ ์ˆœ์„œ SQL ์ฟผ๋ฆฌ๋ฌธ์„ ์ž‘์„ฑํ• ๋•Œ ์‚ฌ์šฉ๋˜๋Š” WHERE, GROUP BY, ORDER BY ์ ˆ๊ณผ ๊ฐ™์€ ๊ตฌ๋ฌธ์„ ์‹คํ–‰ํ•˜๋Š”๋ฐ ์ˆœ์„œ๊ฐ€ ์กด์žฌํ•ฉ๋‹ˆ๋‹ค. ์ด ์ˆœ์„œ์— ์˜ํ•ด์„œ ์ฟผ๋ฆฌ๊ฐ€ ์ฒ˜๋ฆฌ๋˜๊ณ  ์–ด๋–ป๊ฒŒ ์ฟผ๋ฆฌ๋ฌธ์„ ์ž‘์„ฑํ•˜๋Š๋ƒ์— ๋”ฐ๋ผ ํผํฌ๋จผ์Šค์˜ ์ฐจ์ด๊ฐ€ ๋ฐœ์ƒํ•ฉ๋‹ˆ๋‹ค. ORACLE HR ๊ณ„์ •์˜ EMPLOYEES ํ…Œ์ด๋ธ”๋กœ ์–ด๋–ค ์ฒ˜๋ฆฌ ๊ณผ์ •์— ์˜ํ•ด์„œ SELECT ์ฟผ๋ฆฌ๊ฐ€ ์‹คํ–‰๋˜๋Š”์ง€ ์•Œ์•„ ๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค. 1 2 3 4 5 6 7 8 9 10 11 12 SELECT JOB_ID ,AVG(SALARY) SAL_AVG FROM EMPLOYEES WHERE SALARY > 13000 GROUP BY JOB_ID HAVING COUNT(*) > 1 ORDER BY SAL_AVG DESC; ์œ„์˜ ์ฟผ๋ฆฌ๋ฌธ์„ ์‹คํ–‰ํ–ˆ์„ ๋•Œ์˜ ์ตœ์ข… ๊ฒฐ๊ณผ์ž…๋‹ˆ๋‹ค. ํ™”๋ฉด.. 2020. 3. 17.
[Oracle] ์˜ค๋ผํด '&' ๋ฌธ์ž ์น˜ํ™˜๋ณ€์ˆ˜ ์‚ฌ์šฉํ•˜๊ธฐ :: ๋งˆ์ด์ž๋ชฝ ๋ฐ์ดํ„ฐ๋ฅผ ํ™•์ธํ•˜๊ธฐ ์œ„ํ•ด ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ•˜๊ณ  ํ™•์ธํ•˜๊ณ ... ๊ฐ™์€ ์ฟผ๋ฆฌ๋‚ด์—์„œ ํŠน์ • ๊ฐ’๋งŒ ๋ณ€๊ฒฝํ•ด์„œ ๋ฐ์ดํ„ฐ๋ฅผ ํ™•์ธํ•˜๊ณ  ์‹ถ์„๋•Œ ์–ด๋–ป๊ฒŒ ํ•˜์‹œ๋‚˜์š”? ์˜ˆ๋ฅผ๋“ค์–ด ํŠน์ • ๋ถ€์„œ์— ์–ด๋–ค ์ง์›๋“ค์ด ์žˆ๋Š”์ง€ ํ™•์ธํ•˜๊ณ  ์‹ถ์€๋ฐ... ์˜์—…๋ถ€์„œ๋ฅผ ํ™•์ธํ•˜๊ณ  ์ธ์‚ฌ๋ถ€์„œ์˜ ์ง์›๋“ค์„ ํ™•์ธํ•˜๊ธฐ ์œ„ํ•ด WHERE์ ˆ์˜ Value๊ฐ’์„ ๊ณ„์† ๋ณ€๊ฒฝํ•ด๊ฐ€๋ฉด์„œ ํ™•์ธํ•˜์‹œ๋‚˜์š”? ํ•œ ๋‘๊ฐœ๋ฉด ๋ชฐ๋ผ๋„... ๋ถ€์„œ๊ฐ€ 20๊ฐœ์”ฉ ์žˆ๊ณ  ์ด๋Ÿฌ๋ฉด ์ผ์ผํžˆ ์ฟผ๋ฆฌ๋‚ด์šฉ ๋ณ€๊ฒฝํ•ด๊ฐ€๋ฉด์„œ ํ™•์ธํ•˜๊ธฐ ํž˜๋“ค์ž–์•„์š”? ๊ทธ๋Ÿด๋•Œ ์น˜ํ™˜๋ณ€์ˆ˜ '&'๋ฅผ ์‚ฌ์šฉํ•˜์‹œ๋ฉด ์ข€ ๋” ํŽธํ•˜๊ฒŒ ์ž‘์—…์„ ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์น˜ํ™˜๋ณ€์ˆ˜์— ๋Œ€ํ•ด์„œ ์•Œ์•„๋ณด๊ธฐ ์œ„ํ•ด Oracle์˜ scott ๊ณ„์ •์•ˆ์— ์žˆ๋Š” EMP ํ…Œ์ด๋ธ”์„ ๊ฐ–๊ณ  ์‹ค์Šตํ•ด ๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค. ์˜ค๋ผํด ์น˜ํ™˜๋ณ€์ˆ˜ ์˜ค๋ผํด '&' ๋ฌธ์ž 1 SELECT * FROM EMP WHERE SAL >= 1000; EMP.. 2020. 3. 16.
[Oracle] ์˜ค๋ผํด ํŽ˜์ด์ง• ์ฟผ๋ฆฌ ์‰ฝ๊ฒŒ ๋งŒ๋“ค๊ธฐ Row Limiting Clause ์‚ฌ์šฉ :: ๋งˆ์ด์ž๋ชฝ ์˜ค๋ผํด ํŽ˜์ด์ง• ์˜ค๋ผํด ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ํŽ˜์ด์ง• ์ฟผ๋ฆฌ๋Š” ์–ด๋–ป๊ฒŒ ์ž‘์„ฑํ• ๊นŒ์š”? ํฌํ„ธ ์‚ฌ์ดํŠธ์—์„œ ๊ฒ€์ƒ‰์„ ํ–ˆ์„ ๋•Œ, ๊ฒŒ์‹œํŒ ํ˜•ํƒœ์˜ ์›น์‚ฌ์ดํŠธ์—์„œ ๊ฒฐ๊ณผ๋ฅผ ๋ณผ๋•Œ, ํ•œ๋ฒˆ์— ๋ชจ๋“  ๊ฒฐ๊ณผ๋ฅผ ๋ณผ ์ˆ˜ ์—†๊ธฐ ๋•Œ๋ฌธ์— ํŽ˜์ด์ง• ์ฒ˜๋ฆฌ๋ฅผ ํ•˜์—ฌ ํ™”๋ฉด์— ์ถœ๋ ฅ๋˜๋Š” ๊ฒŒ์‹œ๋ฌผ์„ ์ œํ•œํ•ฉ๋‹ˆ๋‹ค. ๊ทธ๋Ÿผ ์ด๋Ÿฐ ๋ณ€๋™์ ์ธ ๊ฒฐ๊ณผ๋ฅผ ๋ณด์—ฌ์ฃผ๊ธฐ ์œ„ํ•ด ์˜ค๋ผํด ํŽ˜์ด์ง• ์ฟผ๋ฆฌ๋Š” ์–ด๋–ป๊ฒŒ ์งค๊นŒ์š”? Top-N Query๋ฅผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์ง€๋งŒ, ์˜ค๋ผํด ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ๋Š” Row Limiting Clause๋ผ๋Š” Feature๋ฅผ ์ œ๊ณตํ•ฉ๋‹ˆ๋‹ค. Oracle Database scott ๊ณ„์ •์— ๊ธฐ๋ณธ์ ์œผ๋กœ ์กด์žฌํ•˜๋Š” EMP ํ…Œ์ด๋ธ”์„ ๊ฐ–๊ณ  ํŽ˜์ด์ง• ์ฒ˜๋ฆฌ๋ฅผ ํ•ด๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค. Top-N Query Top-N Query๋Š” ์ƒ์œ„ N๊ฐœ ๋ฐ์ดํ„ฐ๋ฅผ ๋ณด์—ฌ์ฃผ๋Š” ์ฟผ๋ฆฌ์ž…๋‹ˆ๋‹ค. ํŽ˜์ด์ง•์„ ํ•˜๊ธฐ์ „์— ๋ฐ์ดํ„ฐ๋ฅผ ์–ด๋–ค ์ˆœ์„œ๋กœ ์ •๋ ฌํ•ด์„œ ๋ณด์—ฌ์ค„๊ฑด.. 2020. 3. 15.
[ORACLE] ๋ฌธ์ž์—ด ๋Œ€์†Œ๋ฌธ์ž ํ•จ์ˆ˜ LOWER, UPPER, INITCAP :: ๋งˆ์ด์ž๋ชฝ ์˜ค๋ผํด ๋Œ€์†Œ๋ฌธ์ž ํ•จ์ˆ˜์˜ค๋ผํด์—์„œ ๋ฌธ์ž์—ด ๋Œ€์†Œ๋ฌธ์ž ๋ณ€ํ™˜ํ•จ์ˆ˜์— ๋Œ€ํ•ด ์•Œ์•„๋ณด์ž. LOWER ํ•จ์ˆ˜LOWER ํ•จ์ˆ˜๋Š” ๋ชจ๋“  ๋ฌธ์ž๋ฅผ ์†Œ๋ฌธ์ž๋กœ ๋ฐ”๊ฟ”์ฃผ๋Š” ํ•จ์ˆ˜์ด๋‹ค.๋ช…๋ น : SELECT LOWER([๋ฌธ์ž์—ด]) FROM [ํ…Œ์ด๋ธ”]; QUERY1SELECT LOWER('MYJAMONG MYjamong') FROM DUAL;cs RESULT UPPER ํ•จ์ˆ˜UPPERํ•จ์ˆ˜๋Š” ๋ชจ๋“  ๋ฌธ์ž๋ฅผ ๋Œ€๋ฌธ์ž๋กœ ๋ณ€ํ™˜ํ•ด์ฃผ๋Š” ํ•จ์ˆ˜์ด๋‹ค.๋ช…๋ น : SELECT UPPER([๋ฌธ์ž์—ด]) FROM [ํ…Œ์ด๋ธ”]; QUERY1SELECT UPPER('MYJAMONG MYjamong') FROM DUAL;cs RESULT INITCAP ํ•จ์ˆ˜INITCAP ํ•จ์ˆ˜๋Š” ๋ฌธ์ž์—ด์˜ ์ฒซ๋ฒˆ์งธ ๋ฌธ์ž๋ฅผ ๋Œ€๋ฌธ์ž๋กœ ๋ณ€ํ™˜ํ•ด์ฃผ๋Š” ํ•จ์ˆ˜์ด๋‹ค. ๋ฌธ์ž์—ด์˜ ๊ธฐ์ค€์„ ๊ณต๋ฐฑ์œผ๋กœ ์ดํ•ดํ•˜๋Š”๋ฐ ํŠน์ˆ˜๊ธฐํ˜ธ ๋˜ํ•œ ๊ตฌ๋ถ„์ž.. 2019. 9. 15.