[SQLD] SQL ํ™œ์šฉ - 2

ํ˜คยท2023๋…„ 5์›” 15์ผ
0

SQLD

๋ชฉ๋ก ๋ณด๊ธฐ
6/18
post-thumbnail

๐Ÿ’ก SQLD ์ž๊ฒฉ์ฆ ์‹œํ—˜ ๋Œ€๋น„ ํ•™์Šตํ•œ ๋‚ด์šฉ์„ ์š”์•ฝ์ •๋ฆฌํ•ฉ๋‹ˆ๋‹ค.

๊ทธ๋ฃน ํ•จ์ˆ˜

๋ฐ์ดํ„ฐ ๋ถ„์„์„ ์œ„ํ•œ 3๊ฐ€์ง€ ํ•จ์ˆ˜

  1. Group Function
  2. Aggregate Function
  3. Window Function

Group Function(๊ทธ๋ฃน ํ•จ์ˆ˜)

  • ํ…Œ์ด๋ธ”์˜ ์ „์ฒด ํ–‰์„ ํ•˜๋‚˜ ์ด์ƒ์˜ ์ปฌ๋Ÿผ์„ ๊ธฐ์ค€์œผ๋กœ ์ปฌ๋Ÿผ๊ฐ’์— ๋”ฐ๋ผ ๊ทธ๋ฃนํ™”ํ•˜์—ฌ ๊ฒฐ๊ณผ๋ฅผ ์ถœ๋ ฅํ•˜๋Š” ํ•จ์ˆ˜
  • ๋ณต์ˆ˜ ํ–‰ ํ•จ์ˆ˜๋ผ๊ณ ๋„ ํ•œ๋‹ค.
  • ROLLUP, CUBE, GROUPING SETS ํ•จ์ˆ˜๊ฐ€ ์žˆ๋‹ค.

(Group)Aggregate Function

  • Group Function์˜ ํ•œ ๋ถ€๋ถ„
  • ๊ฐ์ข… ์ง‘๊ณ„ ํ•จ์ˆ˜๋“ค์ด ํฌํ•จ(COUNT, SUM, AVG, MAX ๋“ฑ)

ROLLUP ํ•จ์ˆ˜

  • ์†Œ๊ทธ๋ฃน ๊ฐ„์˜ ์†Œ๊ณ„๋ฅผ ๊ณ„์‚ฐํ•˜๋Š” ํ•จ์ˆ˜
  • GROUP BY์˜ ํ™•์žฅ๋œ ํ˜•ํƒœ๋กœ ์‚ฌ์šฉํ•˜๊ธฐ ์‰ฝ๊ณ , ๋ณ‘๋ ฌ ์ˆ˜ํ–‰ ๊ฐ€๋Šฅ
  • ๊ณ„์ธต์  ๋ถ„๋ฅ˜๋ฅผ ํฌํ•จํ•˜๊ณ  ์žˆ์–ด ๋ฐ์ดํ„ฐ ์ง‘๊ณ„์— ์ ํ•ฉ
  • ROLLUP์— ์ง€์ •ํ•œ Grouping Column์˜ List๋Š” Subtotal์„ ์ƒ์„ฑํ•˜๊ธฐ ์œ„ํ•ด ์‚ฌ์šฉ.
  • Grouping Column์˜ ์ˆ˜๋ฅผ n์ด๋ผ๊ณ  ํ–ˆ์„ ๋•Œ, n+1 ๋ ˆ๋ฒจ์˜ Subtotal์ด ์ƒ์„ฑ.
  • ์ธ์ˆ˜ ์ˆœ์„œ์— ๋”ฐ๋ผ ๊ฒฐ๊ณผ๊ฐ€ ๋ฐ”๋€Œ๊ฒŒ ๋˜๋ฏ€๋กœ ์ˆœ์„œ์— ์ฃผ์˜ํ•ด์•ผ ํ•จ
  • ๊ณ„์ธต ๊ฐ„ ์ง‘๊ณ„์— ๋Œ€ํ•ด ๋ ˆ๋ฒจ ๋ณ„ ์ˆœ์„œ๋กœ ์ •๋ ฌํ•จ

GROUPING : Subtotal์˜ total์„ ์ƒ์„ฑ

CUBE ํ•จ์ˆ˜

  • ๊ฒฐํ•ฉ ๊ฐ€๋Šฅํ•œ ๋ชจ๋“  ๊ฐ’์— ๋Œ€ํ•˜์—ฌ ๋‹ค์ฐจ์›์ ์ธ ์ง‘๊ณ„ ์ƒ์„ฑ
  • ROLLUP์— ๋น„ํ•ด ๋‹ค์–‘ํ•œ ๋ฐ์ดํ„ฐ๋ฅผ ์–ป๋Š” ์žฅ์ ์ด ์žˆ์ง€๋งŒ ์‹œ์Šคํ…œ์— ๋ถ€ํ•˜๋ฅผ ๋งŽ์ด ์ฃผ๋Š” ๋‹จ์ ์ด ์žˆ๋‹ค.
  • ๋‚ด๋ถ€์ ์œผ๋กœ Grouping Column์˜ ์ˆœ์„œ๋ฅผ ๋ฐ”๊ฟ” ๋˜ ํ•œ๋ฒˆ ์ฟผ๋ฆฌ๋ฅผ ์ถ”๊ฐ€ ์ˆ˜ํ–‰ ํ•ด์•ผ ํ•œ๋‹ค.
  • ํ‘œ์‹œ๋œ ์ธ์ˆ˜๋“ค์— ๋Œ€ํ•œ ๊ณ„์ธต๋ณ„ ์ง‘๊ณ„๋ฅผ ๊ตฌํ•  ์ˆ˜ ์žˆ๋‹ค.
  • ํ‘œ์‹œ๋œ ์ธ์ˆ˜๋“ค์€ ํ‰๋“ฑํ•œ ๊ด€๊ณ„๋ฅผ ๊ฐ€์ ธ ์ˆœ์„œ๊ฐ€ ๋ฐ”๋€Œ๋”๋ผ๋„ ๊ฒฐ๊ณผ์— ์˜ํ–ฅ์„ ๋ฏธ์น˜์ง€ ์•Š๋Š”๋‹ค.
  • Grouping Column์˜ ์ˆ˜๋ฅผ n์ด๋ผ๊ณ  ํ–ˆ์„ ๋•Œ, 2^n ๋ ˆ๋ฒจ์˜ Subtotal๋ฅผ ์ƒ์„ฑ

GROUPING SETS ํ•จ์ˆ˜

  • ์›ํ•˜๋Š” ๋ถ€๋ถ„์˜ ์†Œ๊ณ„๋งŒ ์ถ”์ถœํ•˜๋Š” ํ•จ์ˆ˜
  • ํ‘œ์‹œ๋œ ์ธ์ˆ˜๋“ค์— ๋Œ€ํ•œ ๊ฐœ๋ณ„ ์ง‘๊ณ„๋ฅผ ๊ตฌํ•  ์ˆ˜ ์žˆ๋‹ค.
  • ํ‘œ์‹œ๋œ ์ธ์ˆ˜๋“ค์€ ํ‰๋“ฑํ•œ ๊ด€๊ณ„๋ฅผ ๊ฐ€์ ธ ์ˆœ์„œ๊ฐ€ ๋ฐ”๋€Œ๋”๋ผ๋„ ๊ฒฐ๊ณผ์— ์˜ํ–ฅ์„ ๋ฏธ์น˜์ง€ ์•Š๋Š”๋‹ค.

๐Ÿ’ก ROLLUP, CUBE, GROUPING SETS ํ•จ์ˆ˜๋Š” ๊ฒฐ๊ณผ์— ๋Œ€ํ•œ ์ •๋ ฌ์ด ํ•„์š”ํ•œ ๊ฒฝ์šฐ ORDER BY ์ ˆ์— ์ •๋ ฌ ์ปฌ๋Ÿผ์„ ๋ช…์‹œํ•ด์•ผ
ํ•œ๋‹ค.


Window Function

์œˆ๋„์šฐ ํ•จ์ˆ˜

  • ํ–‰๊ณผ ํ–‰๊ฐ„์˜ ๊ด€๊ณ„๋ฅผ ์ •์˜ํ•˜๊ธฐ ์œ„ํ•ด ๋งŒ๋“  ํ•จ์ˆ˜
  • ๋ถ„์„ํ•จ์ˆ˜, ์ˆœ์œ„ ํ•จ์ˆ˜๋กœ๋„ ๋ถˆ๋ฆฌ์šด๋‹ค.
  • ๋‹ค๋ฅธ ํ•จ์ˆ˜์™€ ๋‹ฌ๋ฆฌ ์ค‘์ฒฉํ•ด์„œ ์‚ฌ์šฉํ•˜์ง€ ๋ชปํ•˜๋ฉฐ, ์„œ๋ธŒ์ฟผ๋ฆฌ์—์„œ ์‚ฌ์šฉ๊ฐ€๋Šฅํ•˜๋‹ค.
  • OVER ๋ฌธ๊ตฌ๊ฐ€ ํ‚ค์›Œ๋“œ๋กœ ํ•„์ˆ˜ ํฌํ•จ๋œ๋‹ค.

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ([PARTITION BY ์ปฌ๋Ÿผ] [ ORDER BY ์ ˆ] [ WINDOWING ์ ˆ]) FROM ํ…Œ์ด๋ธ”๋ช…;

  • ARGUMENTS : ํ•จ์ˆ˜์— ๋”ฐ๋ผ 0~n๊ฐœ์˜ ์ธ์ˆ˜ ์ง€์ •
  • PARTITION BY์ ˆ : ์ „์ฒด ์ง‘ํ•ฉ์„ ๊ธฐ์ค€์— ์˜ํ•ด ์†Œ๊ทธ๋ฃน์œผ๋กœ ๋‚˜๋ˆŒ ์ˆ˜ ์žˆ๋‹ค.
  • WINDOWING ์ ˆ : ํ•จ์ˆ˜์— ๋Œ€์ƒ์ด ๋˜๋Š” ํ–‰ ๊ธฐ๋ถ„์˜ ๋ฒ”์œ„๋ฅผ ๊ฐ•๋ ฅํ•˜๊ฒŒ ์ง€์ •. ROWS, RANGE ๋‘˜ ์ค‘ ํ•˜๋‚˜๋งŒ ์‚ฌ์šฉ ๊ฐ€๋Šฅ
    • ROWS : ๋ฌผ๋ฆฌ์ ์ธ ๊ฒฐ๊ณผ ํ–‰์˜ ์ˆ˜
    • RANGE : ๋…ผ๋ฆฌ์ ์ธ ๊ฐ’์— ์˜ํ•œ ๋ฒ”์œ„

๐Ÿ’ก WINDOWING ์ ˆ์€ SQL Server ์—์„œ๋Š” ์ง€์›ํ•˜์ง€ ์•Š์Œ

์œˆ๋„์ˆ˜ ํ•จ์ˆ˜์˜ ์ข…๋ฅ˜

  • ๊ทธ๋ฃน ๋‚ด ์ˆœ์œ„ : RANK, DENSE_RANK, ROW_NUMBER
  • ๊ทธ๋ฃน ๋‚ด ์ง‘๊ณ„ : SUM, MAX, MIN, AVG, COUNT
  • ๊ทธ๋ฃน ๋‚ด ์ˆœ์„œ : FIRST_VALUE, LAST_VALUE, LAG, LEAD
  • ๊ทธ๋ฃน ๋‚ด ๋น„์œจ : COME_DIST, PERCENT_RANK, NTILE, RATIO_REPORT
  • ์„ ํ˜• ๋ถ„์„์„ ํฌํ•จํ•œ ํ†ต๊ณ„ ๋ถ„์„ ๊ด€๋ จ ํ•จ์ˆ˜ : ํ†ต๊ณ„์— ํŠนํ™”

๊ทธ๋ฃน ๋‚ด ์ˆœ์œ„ ํ•จ์ˆ˜

RANK ํ•จ์ˆ˜

  • ORDER BY๋ฅผ ํฌํ•จํ•œ ์ฟผ๋ฆฌ๋ฌธ์—์„œ ํŠน์ • ํ•ญ๋ชฉ(์ปฌ๋Ÿผ)์— ๋Œ€ํ•œ ์ˆœ์œ„๋ฅผ ๊ตฌํ•˜๋Š” ํ•จ์ˆ˜
  • ํŠน์ • ๋ฒ”์œ„(PARTITION) ๋‚ด์—์„œ ์ˆœ์œ„๋ฅผ ๊ตฌํ•˜๊ฑฐ๋‚˜, ์ „์ฒด ๋ฐ์ดํ„ฐ์— ๋Œ€ํ•œ ์ˆœ์œ„๋ฅผ ๊ตฌํ•œ๋‹ค.
  • ๋™์ผํ•œ ๊ฐ’์— ๋Œ€ํ•ด์„œ ๋™์ผํ•œ ์ˆœ์œ„๋ฅผ ๋ถ€์—ฌ

DENSE_RANK ํ•จ์ˆ˜

  • RANK๊ณผ ๋™์ผํ•œ ๊ธฐ๋Šฅ
  • RANK์™€ ๋‹ค๋ฅด๊ฒŒ ๋™์ผํ•œ ์ˆœ์œ„๋ฅผ ํ•˜๋‚˜์˜ ๊ฑด์ˆ˜๋กœ ์ทจ๊ธ‰

ROW_NUMBER ํ•จ์ˆ˜

  • RANK์™€ ๋™์ผํ•œ ๊ธฐ๋Šฅ
  • RANK์™€ ๋‹ฌ๋ฆฌ ๋™์ผํ•œ ๊ฐ’์ด๋ผ๋„ ๊ณ ์œ ํ•œ ์ˆœ์œ„๋ฅผ ๋ถ€์—ฌ

์ผ๋ฐ˜ ์ง‘๊ณ„ ํ•จ์ˆ˜

SUM : ํŒŒํ‹ฐ์…˜ ๋ณ„ ์œˆ๋„์šฐ์˜ ํ•ฉ
ex) ๊ฐ™์€ ๋งค๋‹ˆ์ €๋ฅผ ๋‘๊ณ  ์žˆ๋Š” ์‚ฌ์›๋“ค์˜ ์›”๊ธ‰ ํ•ฉ

MAX : ํŒŒํ‹ฐ์…˜ ๋ณ„ ์œˆ๋„์šฐ์˜ ์ตœ๋Œ€๊ฐ’
ex) ๊ฐ™์€ ๋งค๋‹ˆ์ €๋ฅผ ๋‘๊ณ  ์žˆ๋Š” ์‚ฌ์›๋“ค์˜ ์›”๊ธ‰ ์ค‘ ์ตœ๋Œ€๊ฐ’

MIN : ํŒŒํ‹ฐ์…˜ ๋ณ„ ์œˆ๋„์šฐ์˜ ์ตœ์†Œ๊ฐ’
ex) ๊ฐ™์€ ๋งค๋‹ˆ์ €๋ฅผ ๋‘๊ณ  ์žˆ๋Š” ์‚ฌ์›๋“ค์˜ ์›”๊ธ‰ ์ค‘ ์ตœ์†Œ๊ฐ’

AVG : ํŒŒํ‹ฐ์…˜ ๋ณ„ ROWS ์œˆ๋„์šฐ๋ฅผ ์ด์šฉํ•ด ์›ํ•˜๋Š” ์กฐ๊ฑด์— ๋งž๋Š” ๋ฐ์ดํ„ฐ์— ๋Œ€ํ•œ ํ†ต๊ณ„๊ฐ’
ex) ๊ฐ™์€ ๋งค๋‹ˆ์ €๋ฅผ ๋‘๊ณ  ์žˆ๋Š” ์‚ฌ์›๋“ค ์ค‘, ์ž์‹  ๋ฐ”๋กœ ์•ž, ๋ฐ”๋กœ ๋’ค์˜ ์‚ฌ๋ฒˆ์ธ ์ง์›๋งŒ์„ ๋Œ€์ƒ์œผ๋กœ ํ‰๊ท  ์›”๊ธ‰์„ ๊ตฌํ•ด๋ผ

COUNT : ํŒŒํ‹ฐ์…˜ ๋ณ„ ROWS ์œˆ๋„์šฐ๋ฅผ ์ด์šฉํ•ด ์›ํ•˜๋Š” ์กฐ๊ฑด์— ๋งž๋Š” ๋ฐ์ดํ„ฐ์— ๋Œ€ํ•œ ํ†ต๊ณ„๊ฐ’
ex) ๋ณธ์ธ์˜ ๊ธ‰์—ฌ๋ณด๋‹ค 50 ์ดํ•˜๊ฐ€ ์ ๊ฑฐ๋‚˜ 150 ์ดํ•˜๋กœ ๋งŽ์€ ๊ธ‰์—ฌ๋ฅผ ๋ฐ›๋Š” ์ธ์›์ˆ˜

๊ทธ๋ฃน ๋‚ด ํ–‰ ์ˆœ์„œ ํ•จ์ˆ˜

SQL Server์—์„œ ์ง€์› X

FIRST_VALUE

  • ํŒŒํ‹ฐ์…˜ ๋ณ„ ์œˆ๋„์šฐ์—์„œ ๊ฐ€์žฅ ๋จผ์ € ๋‚˜์˜จ ๊ฐ’.
  • ๊ณต๋™ ๋“ฑ์ˆ˜๋ฅผ ์ธ์ •ํ•˜์ง€ ์•Š๊ณ  ์ฒ˜์Œ ๋‚˜์˜จ ํ–‰๋งŒ ์ฒ˜๋ฆฌ

LAST_VALUE

  • ํŒŒํ‹ฐ์…˜ ๋ณ„ ์œˆ๋„์šฐ์—์„œ ๊ฐ€์žฅ ๋‚˜์ค‘์— ๋‚˜์˜จ ๊ฐ’.
  • ๊ณต๋™ ๋“ฑ์ˆ˜๋ฅผ ์ธ์ •ํ•˜์ง€ ์•Š๊ณ  ๊ฐ€์žฅ ๋‚˜์ค‘์— ๋‚˜์˜จ ํ–‰๋งŒ ์ฒ˜๋ฆฌ

LAG : ํŒŒํ‹ฐ์…˜ ๋ณ„ ์œˆ๋„์šฐ์—์„œ ์ด์ „ ๋ช‡ ๋ฒˆ์งธ ํ–‰์˜ ๊ฐ’

LEAD : ํŒŒํ‹ฐ์…˜ ๋ณ„ ์œˆ๋„์šฐ์—์„œ ์ดํ›„ ๋ช‡ ๋ฒˆ์งธ ํ–‰์˜ ๊ฐ’

๊ทธ๋ฃน ๋‚ด ๋น„์œจ ํ•จ์ˆ˜

SQL Server์—์„œ ์ง€์› X (NTILE์€ ์ง€์› O)

RATIO_TO_REPORT

  • ํŒŒํ‹ฐ์…˜ ๋‚ด ์ „์ฒด SUM(์ปฌ๋Ÿผ)๊ฐ’์— ๋Œ€ํ•œ ํ–‰ ๋ณ„ ์ปฌ๋Ÿผ ๊ฐ’์˜ ๋ฐฑ๋ถ„์œจ์„ ์†Œ์ˆ˜์ ์œผ๋กœ ๊ตฌํ•  ์ˆ˜ ์žˆ๋‹ค.
  • 0< ๊ฒฐ๊ณผ๊ฐ’ <= 1
  • ๊ฐœ๋ณ„ ๊ฒฐ๊ณผ๊ฐ’์˜ ํ•ฉ = 1

PERCENT_RANK

  • ํŒŒํ‹ฐ์…˜ ๋ณ„ ์œˆ๋„์šฐ์—์„œ ์ œ์ผ ๋จผ์ € ๋‚˜์˜ค๋Š” ๊ฒƒ์„ 0, ์ œ์ผ ๋Šฆ๊ฒŒ ๋‚˜์˜ค๋Š” ๊ฒƒ์„ 1๋กœ ํ•˜์—ฌ ๊ฐ’์ด ์•„๋‹Œ ํ–‰์˜ ์ˆœ์„œ๋ณ„ ๋ฐฑ๋ถ„์œจ์„ ๊ตฌํ•จ
  • 0 <= ๊ฒฐ๊ณผ๊ฐ’ <= 1

CUME_DIST

  • ํŒŒํ‹ฐ์…˜ ๋ณ„ ์œˆ๋„์šฐ์˜ ์ „์ฒด๊ฑด์ˆ˜์—์„œ ํ˜„์žฌ ํ–‰๋ณด๋‹ค ์ž‘๊ฑฐ๋‚˜ ๊ฐ™์€ ๊ฑด์ˆ˜์— ๋Œ€ํ•œ ๋ˆ„์ ๋ฐฑ๋ถ„์œจ์„ ๊ตฌํ•จ
  • 0 < ๊ฒฐ๊ณผ๊ฐ’ <= 1

NTILE : ํŒŒํ‹ฐ์…˜ ๋ณ„ ์ „์ฒด ๊ฑด์ˆ˜๋ฅผ ARGUMENT ๊ฐ’์œผ๋กœ N๋“ฑ๋ถ„ํ•œ ๊ฒฐ๊ณผ


DCL

์œ ์ €๋ฅผ ์ƒ์„ฑํ•˜๊ณ  ๊ถŒํ•œ์„ ์ œ์–ดํ•˜๋Š” ๋ช…๋ น์–ด

์˜ค๋ผํด, SQL Server์˜ ์‚ฌ์šฉ์ž์— ๋Œ€ํ•œ ์•„ํ‚คํ…์ณ ์ฐจ์ด

์˜ค๋ผํด

  • ์œ ์ €๋ฅผ ํ†ตํ•ด DB์— ์ ‘์†
  • ์•„์ด๋””, ๋น„๋ฐ€๋ฒˆํ˜ธ ๋ฐฉ์‹์œผ๋กœ ์ธ์Šคํ„ด์Šค์— ์ ‘์†ํ•˜๊ณ  ๊ทธ์— ํ•ด๋‹นํ•˜๋Š” ์Šคํ‚ค๋งˆ์— ์˜ค๋ธŒ์ ํŠธ ์ƒ์„ฑ๋“ฑ์˜ ๊ถŒํ•œ์„ ๋ถ€์—ฌ

SQL Server

  • ์ธ์Šคํ„ด์Šค์— ์ ‘์†ํ•˜๊ธฐ ์œ„ํ•ด ๋กœ๊ทธ์ธ ์ƒ์„ฑ
  • ์ธ์Šคํ„ด์Šค ๋‚ด์— ์กด์žฌํ•˜๋Š” ๋‹ค์ˆ˜์˜ DB์— ์—ฐ๊ฒฐํ•˜์—ฌ ์ž‘์—…ํ•˜๊ธฐ ์œ„ํ•ด ์œ ์ €๋ฅผ ์ƒ์„ฑํ•œ ํ›„ ๋กœ๊ทธ์ธ๊ณผ ์œ ์ €๋ฅผ ๋งคํ•‘
  • ํŠน์ • ์œ ์ €๋Š” ํŠน์ • DB๋‚ด์˜ ํŠน์ • ์Šคํ‚ค๋งˆ์— ๋Œ€ํ•œ ๊ถŒํ•œ์„ ๋ถ€์—ฌ๋ฐ›์Œ
  • ์œ ์ €๋Š” DB๋งˆ๋‹ค ์กด์žฌํ•˜๊ธฐ ๋•Œ๋ฌธ์— ์œ ์ € ์ƒ์„ฑ์„ ์œ„ํ•ด์„œ ์ƒ์„ฑํ•˜๊ณ ์ž ํ•˜๋Š” ์œ ์ €๊ฐ€ ์†ํ•  DB๋กœ ์ด๋™ ํ›„ ์ฒ˜๋ฆฌํ•ด์•ผ ํ•จํ•จ

SQL Server์˜ ๋กœ๊ทธ์ธ ๋ฐฉ์‹

  1. ์œˆ๋„์šฐ ์ธ์ฆ ๋ฐฉ์‹ : ์œˆ๋„์šฐ์— ๋กœ๊ทธ์ธํ•œ ์ •๋ณด๋ฅผ ๊ฐ€์ง€๊ณ  SQL Server์— ์ ‘์†
  2. ํ˜ผํ•ฉ ๋ชจ๋“œ ๋ฐฉ์‹ : ์œˆ๋„์šฐ ์ธ์ฆ ๋˜๋Š” SQL ์ธ์ฆ์œผ๋กœ ์ง„ํ–‰๋จ

์‹œ์Šคํ…œ ๊ถŒํ•œ : ์‚ฌ์šฉ์ž๊ฐ€ ์‹คํ–‰ํ•˜๋Š” ๋ชจ๋“  DDL ๋ฌธ์žฅ์„ ์‹คํ–‰ํ•  ์ˆ˜ ์žˆ๋Š” ๊ถŒํ•œ
GRANT : ๊ถŒํ•œ ๋ถ€์—ฌ
REVOKE : ๊ถŒํ•œ ์ทจ์†Œ

๐Ÿ’ก ๋ชจ๋“  ์œ ์ €๋Š” ๊ฐ๊ฐ ์ž์‹ ์ด ์ƒ์„ฑํ•œ ํ…Œ์ด๋ธ” ์™ธ์— ๋‹ค๋ฅธ ์œ ์ €์˜ ํ…Œ์ด๋ธ”์— ์ ‘๊ทผํ•˜๋ ค๋ฉด ํ•ด๋‹น ํ…Œ์ด๋ธ”์— ๋Œ€ํ•œ ์˜ค ๋ธŒ์ ํŠธ ๊ถŒํ•œ์„ ์†Œ์œ ์ž๋กœ๋ถ€ํ„ฐ ๋ถ€์—ฌ๋ฐ›์•„์•ผ ํ•œ๋‹ค.

ROLE

  • ๊ฐ์ข… ๊ถŒํ•œ์„ ํ•˜๋‚˜์˜ ๋ฌถ์Œ์œผ๋กœ ๋ชจ์•„ ์œ ์ €์—๊ฒŒ ๋ถ€์—ฌํ•˜๋Š” ๊ฒƒ
  • ์‹œ์Šคํ…œ ๊ถŒํ•œ๊ณผ ์˜ค๋ธŒ์ ํŠธ ๊ถŒํ•œ์„ ๋ชจ๋‘ ๋ถ€์—ฌํ•  ์ˆ˜ ์žˆ๋‹ค.
  • ๊ถŒํ•œ์„ ์ง์ ‘ ๋ถ€์—ฌํ•˜๋Š” ๊ฒƒ๋ณด๋‹ค ๋น ๋ฅด๊ณ  ์•ˆ์ „ํ•˜๊ฒŒ ์œ ์ €๋ฅผ ๊ด€๋ฆฌํ•  ์ˆ˜ ์žˆ๋‹ค.
// ์—ญํ•  ์ƒ์„ฑ
CREATE ROLE LOGIN_TABLE; 
// ๊ถŒํ•œ ๋ถ€์—ฌ
GRANT CREATE TABLE TO LOGIN_TABLE; 
// ์œ ์ € ์‚ญ์ œ
DROP USER PJS CASCADE;

CASCADE : ํ•˜์œ„ ์˜ค๋ธŒ์ ํŠธ๊นŒ์ง€ ์‚ญ์ œ

๐Ÿ’ก SQL Server์—์„œ๋Š” ROLE์„ ์ƒ์„ฑํ•˜์—ฌ ์‚ฌ์šฉํ•˜๊ธฐ ๋ณด๋‹ค ๊ธฐ๋ณธ์ ์œผ๋กœ ์ œ๊ณต๋˜๋Š” ROLE์— ๋ฉค๋ฒ„๋กœ ์ฐธ์—ฌํ•˜๋Š” ๋ฐฉ์‹์„ ์‚ฌ์šฉํ•˜๋ฉฐ ์„œ๋ฒ„, DB ์ˆ˜์ค€ ์—ญํ• ์„ ์ด์šฉํ•˜์—ฌ ๋กœ๊ทธ์ธ ๋ฐ ์‚ฌ์šฉ์ž ๊ถŒํ•œ์„ ์ œ์–ดํ•œ๋‹ค.


์ ˆ์ฐจํ˜• SQL

SQL๋ฌธ์˜ ์—ฐ์†์ ์ธ ์‹คํ–‰์ด๋‚˜ ์กฐ๊ฑด์— ๋”ฐ๋ฅธ ๋ถ„๊ธฐ์ฒ˜๋ฆฌ๋ฅผ ์ด์šฉํ•˜์—ฌ ํŠน์ • ๊ธฐ๋Šฅ์„ ์ˆ˜ํ–‰ํ•˜๋Š” ์ €์žฅ ๋ชจ๋“ˆ์„ ์ƒ์„ฑํ•  ์ˆ˜ ์žˆ๋‹ค.

PL/SQL

  • ์˜ค๋ผํด์—์„œ ์ œ๊ณตํ•˜๋Š” ์ ˆ์ฐจ์  ํ”„๋กœ๊ทธ๋ž˜๋ฐ์„ ๊ฐ€๋Šฅํ•˜๊ฒŒ ํ•˜๋Š” ํŠธ๋žœ์žญ์…˜ ์–ธ์–ด
  • ๋‹ค์–‘ํ•œ ์ €์žฅ ๋ชจ๋“ˆ์„ ๊ฐœ๋ฐœํ•  ์ˆ˜ ์žˆ๋‹ค.

์ €์žฅ ๋ชจ๋“ˆ

  • PL/SQL๋ฌธ์žฅ์„ DB ์„œ๋ฒ„์— ์ €์žฅํ•˜์—ฌ ์‚ฌ์šฉ์ž์™€ ์• ํ”Œ๋ฆฌ์ผ€์ด์…˜ ์‚ฌ์ด์—์„œ ๊ณต์œ ํ•  ์ˆ˜ ์žˆ๋„๋ก ๋งŒ๋“  ์ผ์ข…์˜ SQL ์ปดํฌ๋„ŒํŠธ ํ”„๋กœ๊ทธ๋žจ.
  • ๋…๋ฆฝ์ ์œผ๋กœ ์‹คํ–‰๋˜๊ฑฐ๋‚˜ ๋‹ค๋ฅธ ํ”„๋กœ๊ทธ๋žจ์œผ๋กœ๋ถ€ํ„ฐ ์‹คํ–‰๋  ์ˆ˜ ์žˆ๋Š” ์™„์ „ํ•œ ์‹คํ–‰ ํ”„๋กœ๊ทธ๋žจ
  • ์˜ค๋ผํด์˜ ์ €์žฅ ๋ชจ๋“ˆ : Procedure, User Defined Function, Trigger

PL/SQL์˜ ํŠน์ง•

  • Block ๊ตฌ์กฐ๋กœ ๋˜์–ด์žˆ์–ด ๊ฐ ๊ธฐ๋Šฅ๋ณ„๋กœ ๋ชจ๋“ˆํ™”๊ฐ€ ๊ฐ€๋Šฅ
  • ๋ณ€์ˆ˜, ์ƒ์ˆ˜๋“ฑ์„ ์„ ์–ธํ•˜์—ฌ SQL ๋ฌธ์žฅ ๊ฐ„ ๊ฐ’์„ ๊ตํ™˜ํ•œ๋‹ค.
  • IF, LOOP ๋“ฑ์˜ ์ ˆ์ฐจํ˜• ์–ธ์–ด๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์ ˆ์ฐจ์ ์ธ ํ”„๋กœ๊ทธ๋žจ์ด ๊ฐ€๋Šฅ
  • DBMS ์ •์˜ ์—๋Ÿฌ๋‚˜ ์‚ฌ์šฉ์ž ์ •์˜ ์—๋Ÿฌ๋ฅผ ์ •์˜ํ•˜์—ฌ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋‹ค.
  • ์‘์šฉ ํ”„๋กœ๊ทธ๋žจ์˜ ์„ฑ๋Šฅ์„ ํ–ฅ์ƒ์‹œํ‚จ๋‹ค.
  • ์—ฌ๋Ÿฌ SQL๋ฌธ์žฅ์„ Block์œผ๋กœ ๋ฌถ๊ณ  ํ•œ ๋ฒˆ์— Block ์ „๋ถ€๋ฅผ ์„œ๋ฒ„๋กœ ๋ณด๋‚ด ํ†ต์‹ ๋Ÿ‰์„ ์ค„์ž„

PL/SQL ์•„ํ‚คํ…์ณ

  • SQL ๋ฌธ์žฅ๊ณผ ํ”„๋กœ๊ทธ๋žจ ๋ฌธ์žฅ์„ ๊ตฌ๋ถ„ํ•˜์—ฌ ์ฒ˜๋ฆฌ
  • ํ”„๋กœ๊ทธ๋žจ ๋ฌธ์žฅ : PL/SQL ์—”์ง„์ด ์ฒ˜๋ฆฌ
  • SQL ๋ฌธ์žฅ : ์˜ค๋ผํด ์„œ๋ฒ„์˜ SQL Statement Executor๊ฐ€ ์ฒ˜๋ฆฌ

PL/SQL ๊ตฌ์กฐ

  • DECLARE : BEGIN-END ์ ˆ์—์„œ ์‚ฌ์šฉ๋  ๋ณ€์ˆ˜, ์ธ์ˆ˜์— ๋Œ€ํ•œ ์ •์˜ ๋ฐ ๋ฐ์ดํ„ฐ ํƒ€์ž…์„ ์„ ์–ธํ•˜๋Š” ์„ ์–ธ๋ถ€.
  • BEGIN-END : ๊ฐœ๋ฐœ์ž๊ฐ€ ์ฒ˜๋ฆฌํ•˜๊ณ ์ž ํ•˜๋Š” SQL๋ฌธ๊ณผ ์—ฌ๋Ÿฌ๊ฐ€์ง€ ๋น„๊ต๋ฌธ, ์ œ์–ด๋ฌธ์„ ์ด์šฉํ•˜์—ฌ ํ•„์š”ํ•œ ๋กœ์ง์„ ์ฒ˜๋ฆฌํ•˜๋Š” ์‹คํ–‰๋ถ€.
  • EXCEPTION : BEGIN-END ์ ˆ์—์„œ ์‹คํ–‰๋˜๋Š” SQL๋ฌธ์ด ์‹คํ–‰๋  ๋•Œ ๋ฐœ์ƒํ•˜๋Š” ์—๋Ÿฌ๋ฅผ ์ฒ˜๋ฆฌํ•˜๋Š” ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๋ถ€.

PL/SQL ๊ธฐ๋ณธ ๋ฌธ๋ฒ•

// ํ”„๋กœ์‹œ์ € ์ƒ์„ฑ
CREATE Procedure [Procedure_name]
// DB์— ๊ฐ™์€ ์ด๋ฆ„์˜ ํ”„๋กœ์‹œ์ €๊ฐ€ ์žˆ์„ ์‹œ, ๊ธฐ์กด ๋‚ด์šฉ์„ ๋ฌด์‹œํ•˜๊ณ  ์ƒˆ ๋‚ด์šฉ์„ ๋ฎ์–ด์”Œ์›€
REPLACE Procedure [Procedure_name]
// ํ”„๋กœ์‹œ์ € ์‚ญ์ œ
DROP Procedure [Procedure_name]
/ <- ์ปดํŒŒ์ผ ํ•˜๋ผ๋Š” ๋ช…๋ น์–ด 

T-SQL์˜ ํŠน์ง•

  • SQL Server๋ฅผ ์ œ์–ดํ•˜๊ธฐ ์œ„ํ•œ ์–ธ์–ด
  • ์ „์—ญ๋ณ€์ˆ˜(SQL ์„œ๋ฒ„์— ๋‚ด์žฅ๋œ ๊ฐ’)์™€ ์ง€์—ญ๋ณ€์ˆ˜๋ฅผ ์„ ์–ธํ•  ์ˆ˜ ์žˆ๋‹ค.
  • ๋ฐ์ดํ„ฐ ์œ ํ˜•(int, float, varchar ๋“ฑ)์„ ์ œ๊ณตํ•œ๋‹ค.
  • ์‚ฐ์ˆ , ๋น„๊ต, ๋…ผ๋ฆฌ ์—ฐ์‚ฐ์ž ์‚ฌ์šฉ ๊ฐ€๋Šฅ
  • ํ๋ฆ„ ์ œ์–ด ๊ธฐ๋Šฅ(IF-ELSE, WHILE, CASE-THEN) ์‚ฌ์šฉ ๊ฐ€๋Šฅ

T-SQL์˜ ๊ตฌ์กฐ

  • DECLARE : BEGIN-END ์ ˆ์—์„œ ์‚ฌ์šฉ๋  ๋ณ€์ˆ˜, ์ธ์ˆ˜์— ๋Œ€ํ•œ ์ •์˜ ๋ฐ ๋ฐ์ดํ„ฐ ํƒ€์ž…์„ ์„ ์–ธํ•˜๋Š” ์„ ์–ธ๋ถ€.
  • BEGIN-END : ๊ฐœ๋ฐœ์ž๊ฐ€ ์ฒ˜๋ฆฌํ•˜๊ณ ์ž ํ•˜๋Š” SQL๋ฌธ๊ณผ ์—ฌ๋Ÿฌ๊ฐ€์ง€ ๋น„๊ต๋ฌธ, ์ œ์–ด๋ฌธ์„ ์ด์šฉํ•˜์—ฌ ํ•„์š”ํ•œ ๋กœ์ง์„ ์ฒ˜๋ฆฌํ•˜๋Š” ์‹คํ–‰๋ถ€.
  • ERROR ์ฒ˜๋ฆฌ : BEGIN-END ์ ˆ์—์„œ ์‹คํ–‰๋˜๋Š” SQL๋ฌธ์ด ์‹คํ–‰๋  ๋•Œ ๋ฐœ์ƒํ•˜๋Š” ์—๋Ÿฌ๋ฅผ ์ฒ˜๋ฆฌํ•˜๋Š” ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๋ถ€.

๐Ÿ’ก BEGIN-END๋ฌธ์„ ํ•„์ˆ˜์ ์œผ๋กœ ์‚ฌ์šฉ X, ๋ธ”๋ก ๋‹จ์œ„๋กœ ์ฒ˜๋ฆฌํ•˜๊ณ ์ž ํ•  ๋•Œ๋Š” ํ•„์ˆ˜์ ์ด๋‹ค.

CREATE Procedure schema_NAME.Procedure_name

User Defined Function : Return์„ ์‚ฌ์šฉํ•ด ํ•˜๋‚˜์˜ ๊ฐ’์„ ๋ฐ˜๋“œ์‹œ ๋ฐ˜ํ™˜ํ•ด์•ผ ํ•จ

Trigger

  • ํŠน์ •ํ•œ ํ…Œ์ด๋ธ”์— DML๋ฌธ์ด ์ˆ˜ํ–‰๋˜์—ˆ์„ ๋•Œ, DB์—์„œ ์ž๋™์œผ๋กœ ๋™์ž‘ํ•˜๋„๋ก ์ž‘์„ฑ๋œ ํ”„๋กœ๊ทธ๋žจ
  • ์‚ฌ์šฉ์ž๊ฐ€ ์ง์ ‘ ํ˜ธ์ถœํ•˜์—ฌ ์‚ฌ์šฉํ•˜๋Š” ๊ฒƒ์ด ์•„๋‹ˆ๋ผ DB์—์„œ ์ž๋™์ ์œผ๋กœ ์ˆ˜ํ–‰
  • ์ „์ฒด ํŠธ๋žœ์žญ์…˜ ์ž‘์—…์— ๋Œ€ํ•ด ๋ฐœ์ƒ๋˜๋Š” Trigger์™€ ๊ฐ ํ–‰์— ๋Œ€ํ•ด ๋ฐœ์ƒํ•˜๋Š” Trigger๊ฐ€ ์žˆ๋‹ค.

CREATE Trigger Trigger_name

ํ”„๋กœ์‹œ์ €์™€ ํŠธ๋ฆฌ๊ฑฐ์˜ ์ฐจ์ด์ 

ํ”„๋กœ์‹œ์ €๋Š” BEGIN~END ์ ˆ ๋‚ด์— COMMIT, ROLLBACK๊ณผ ๊ฐ™์€ ํŠธ๋žœ์žญ์…˜ ์ข…๋ฃŒ ๋ช…๋ น์–ด ์‚ฌ์šฉ๊ฐ€๋Šฅ, DB ํŠธ๋ฆฌ๊ฑฐ๋Š” BEGIN~END ์ ˆ ๋‚ด์— ์‚ฌ์šฉ ๋ถˆ๊ฐ€

ํ”„๋กœ์‹œ์ €ํŠธ๋ฆฌ๊ฑฐ
CREATE Procedure ๋ฌธ๋ฒ• ์‚ฌ์šฉCREATE Trigger ๋ฌธ๋ฒ• ์‚ฌ์šฉ
EXECUTE ๋ช…๋ น์–ด๋กœ ์‹คํ–‰์ƒ์„ฑ ํ›„ ์ž๋™์œผ๋กœ ์‹คํ–‰
COMMIT, ROLLBACK ์‹คํ–‰ ๊ฐ€๋ŠฅCOMMIT, ROLLBACK ์‹คํ–‰ ์•ˆ๋จ
profile
๐Ÿง‘โ€๐Ÿ’ป๋ฐฑ์—”๋“œ ๊ฐœ๋ฐœ์ž, ์กฐ๊ธˆ์”ฉ ๊พธ์ค€ํ•˜๊ฒŒ

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