\n* Single-Row Functions (\ub2e8\uc77c \ud589 \ud568\uc218)
\n-> \ud589\ub9c8\ub2e4 \ud568\uc218\uac00 \uc801\uc6a9\ub418\uc5b4 \uacb0\uacfc\ub97c \ubc18\ud658\ud568. \uc138\ubd80\uc801\uc73c\ub85c \uc544\ub798\uc640 \uac19\uc774 \uad6c\ubd84\ud560 \uc218 \uc788\ub294\ub370, \ub300\uc0c1\uc774 \ub418\ub294 \ub370\uc774\ud130\uc758 \ud0c0\uc785\uc5d0 \ub530\ub77c \uc774\ub97c \ubd84\ub958\ud558\uace0 \uc788\ub2e4.<\/p>\n
1) Number Functions (\uc22b\uc790 \ud568\uc218)
\n-> \uc22b\uc790 \ub370\uc774\ud130 \ud0c0\uc785\uc758 \uac12\uc744 \uc870\uc791\ud558\uc5ec \ubcc0\ud658\ub41c \uc22b\uc790 \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.<\/p>\n
2) Character Functions (\ubb38\uc790 \ud568\uc218)
\n-> \ubb38\uc790 \ub370\uc774\ud130 \ud0c0\uc785\uc758 \uac12\uc744 \uc870\uc791\ud558\uc5ec \ubcc0\ud658\ub41c \ubb38\uc790 \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.<\/p>\n
3) Datetime Function (\ub0a0\uc9dc \ud568\uc218)
\n-> DATE\ub85c \uc9c0\uc815\ub41c \ub370\uc774\ud130 \ud0c0\uc785\uc758 \uac12\uc5d0 \uc801\uc6a9\ud55c\ub2e4.<\/p>\n
4) Conversion Function (\ubcc0\ud658 \ud568\uc218)
\n-> \ub370\ud2f0\uc5b4 \ud0c0\uc785\uc744 \ubcc0\ud658\uc2dc\ucf1c \ud45c\ud604\ud55c\ub2e4. \uc608\ub97c \ub4e4\uc5b4 \ub0a0\uc9dc\ub97c \ubb38\uc790\ub85c \ubcc0\ud658\ud558\uba74\uc11c \ub144\/\uc6d4\/\uc77c\uc5d0 \ub300\ud574 \uc2dd\ubcc4\ub825\uc744 \ub192\uc774\ub294 \ubcc0\ud658\uc744 \uc218\ud589\ud558\uace0 \uc788\ub2e4.<\/p>\n
5) Miscellaneous Single-Row Functions (\uae30\ud0c0 \ud568\uc218)
\n-> \uae30\ud0c0 \uc720\uc6a9\ud55c \ud568\uc218\ub97c \ud3ec\ud568\ud558\uace0 \uc788\ub2e4.<\/p>\n
\n* Aggregate Functions (\uc9d1\ud569 \ud568\uc218)
\n-> \ud558\ub098 \uc774\uc0c1\uc758 \ud589(\uc9d1\ud569)\uc744 \ub300\uc0c1\uc73c\ub85c \uc5f0\uc0b0\uc744 \uc218\ud589\ud55c \uacb0\uacfc\uac12\uc744 \ubc18\ud658\ud55c\ub2e4. \uc55e\uc5d0\uc11c \ubcf8 \ub2e8\uc77c \ud589 \ud568\uc218\ub294 \ud558\ub098\uc758 \ud589\uc744 \ub300\uc0c1\uc73c\ub85c \uc218\ud589\ub41c\ub2e4\ub294 \uc810\uc5d0\uc11c \ucc28\uc774\uac00 \uc788\ub2e4. \ubd80\uc11c\uc758 \ud3c9\uade0\uc774\ub098 \ud2b9\uc815 \uc81c\ud488\uc758 \ub9e4\ucd9c \uc218\ub7c9 \ud569\uc744 \uad6c\ud560 \ub54c \uc0ac\uc6a9\ub41c\ub2e4.<\/p>\n
\n* Analytical Functions (\ubd84\uc11d \ud568\uc218)
\n-> \ud589\uc758 \uc9d1\ub2e8\uc5d0 \ub300\ud574 \uc5f0\uc0b0\uc774 \uc774\ub8e8\uc5b4\uc9c4\ub2e4\ub294 \uc810\uc5d0\uc11c \uc77c\ubc18 \uadf8\ub8f9 \ud568\uc218\uc640 \uc720\uc0ac\ud558\ub2e4. \uadf8\ub7ec\ub098 \uc77c\ubc18 \uadf8\ub8f9 \ud568\uc218\ub294 \ud558\ub098\uc758 \uc9d1\ub2e8\uc5d0 \ub300\ud574
\n\ud558\ub098\uc758 \uacb0\uacfc\uac00 RETURN \ub418\uc9c0\ub9cc \ubd84\uc11d\uc6a9 \ud568\uc218\ub294 \ud558\ub098\uc758 \uc9d1\ub2e8\uc5d0 \ub300\ud574 \uc5ec\ub7ec \uac00\uc9c0 \uae30\uc900\uc744 \uc801\uc6a9\ud574\uc11c \uc5ec\ub7ec \uac1c\uc758 \uacb0\uacfc\uac00 RETURN \ub420
\n\uc218 \uc788\ub2e4. \uc608\ub97c \ub4e4\uc5b4 \ub300\ub7c9\uc758 \ub370\uc774\ud130\ub97c \ub300\uc0c1\uc73c\ub85c \ub204\uc801 \ud569\uacc4, \uc21c\uc704, \uc774\ub3d9 \ud3c9\uade0 \ub4f1\uc744 \uad6c\ud558\ub824\uace0 \ud560 \ub54c \ubd84\uc11d\uc6a9 \ud568\uc218\ub97c \uc720\uc6a9\ud558\uac8c \uc0ac\uc6a9\ud560
\n\uc218 \uc788\ub2e4.<\/p>\n
\n* Regular Expression (\uc815\uaddc \ud45c\ud604\uc2dd)
\n-> Oracle 10g\uc5d0\uc11c \uc0c8\ub85c \ub3c4\uc785\ub41c \uc815\uaddc \ud45c\ud604\uc2dd(Regular Expression)\uc744 \uad6c\ud604\ud558\uae30 \uc704\ud574 Oracle\uc740 \uae30\uc874 \ud568\uc218\uc758 \uae30\ub2a5\uc744 \ud655\uc7a5\ud55c \uc0c8\ub85c\uc6b4 \ud568\uc218\ub97c \uc81c\uacf5\ud558\uace0 \uc788\ub2e4.<\/p>\n
============================================================<\/p>\n
3.1 \ub2e8\uc77c \ud589 \ud568\uc218(Single-Row Functions)
\n-> \ub2e8\uc77c \ud589 \ud568\uc218\ub294 \ud568\uc218\uac00 \ud589\ub9c8\ub2e4 \uc801\uc6a9\ub41c\ub2e4. \uc5b4\ub5a4 \ud14c\uc774\ube14\uc5d0 10\uac1c\uc758 \ud589\uc774 \uc788\uace0 \uc9c8\uc758\ubb38\uc5d0 \uc870\uac74\uc774 \uc5c6\ub2e4\uba74, 10\uac1c\uc758 \ud589 \ubaa8\ub450\uc5d0 \ub2e8\uc77c \ud589 \ud568\uc218\uac00 \uc801\uc6a9\ub418\uc5b4 \ubcc0\ud658\ub41c \uacb0\uacfc\ub97c \ubc18\ud658\ud55c\ub2e4.<\/p>\n
\n1) \uc22b\uc790 \ud568\uc218(Number Functions)
\n-> \uc22b\uc790 \ub370\uc774\ud130 \ud0c0\uc785\uc758 \uac12\uc744 \uc870\uc791\ud558\uc5ec \ubcc0\ud654\ub41c \uc22b\uc790 \uac12\uc744 \ubc18\ud658\ud558\ub294 \ud568\uc218\uc774\ub2e4.<\/p>\n
(1) MOD \ud568\uc218<\/p>\n
MOD(m, n)
\n-> m\uc744 n\uc73c\ub85c \ub098\ub204\uc5c8\uc744 \ub54c \ub098\uba38\uc9c0\ub97c \ubc18\ud658\ud55c\ub2e4.<\/p>\n
SQL> SELECT MOD(10, 3) “Modules”
\n 2 FROM dual;<\/p>\n
Modules
\n———-
\n 1<\/p>\n
* dual \ud14c\uc774\ube14\uc774\ub780?
\n-> FROM \uc808\uc5d0 \uc0ac\uc6a9\ud55c dual \ud14c\uc774\ube14\uc740 \uc885\uc885 \uc5f0\uc0b0\uc774\ub098 \ub0a0\uc9dc\uc5d0 \ub300\ud55c \uc815\ubcf4\ub97c \ubcf4\uae30 \uc704\ud574 \uc0ac\uc6a9\ud558\ub294 Oracle\uc774 \uc81c\uacf5\ud558\ub294
\n\ud14c\uc774\ube14\uc774\ub2e4. \uc774 \ud14c\uc774\ube14\uc758 \ud2b9\uc9d5\uc740 \ub370\uc774\ud130\uac00 \ud55c \uac1c\uc758 \ud589\uc73c\ub85c \uad6c\uc131\ub418\uc5b4 \uc788\uc5b4 \ubcc4\ub3c4\uc758 \ud14c\uc774\ube14 \uc0dd\uc131 \uc5c6\uc774 \ubcf4\ub294 \uac83\uc744 \uac00\ub2a5\ud558\uac8c \ud55c\ub2e4.
\n \uc608\ub97c \ub4e4\uc5b4 \ubc18\uc9c0\ub984\uc774 5\uc778 \uc6d0\uc758 \uba74\uc801\uc744 \uad6c\ud558\ub294 \uacc4\uc0b0\uc744 \ud574\ubcf4\uc790. \uc774\ub54c \ubd80\uc11c \ud14c\uc774\ube14\uc744 \uc774\uc6a9\ud574\uc11c \uc9c8\uc758\ud574 \ubcf4\uba74 \uc5f0\uc0b0\uc758 \uacb0\uacfc\uac12\uc774
\n\ubc18\ubcf5\ud574\uc11c \ub098\ud0c0\ub098\uac8c \ub41c\ub2e4. \uc989 \ubd80\uc11c \ud14c\uc774\ube14\uc758 \ud589\uc758 \uc218\ub9cc\ud07c \ub098\ud0c0\ub09c\ub2e4. \uc0ac\uc6a9\uc790\ub294 \ub3d9\uc77c \uacb0\uacfc\ub97c \ubc18\ubcf5\ud574\uc11c \ubcfc \uc774\uc720\uac00 \uc5c6\uc73c\ubbc0\ub85c \ud558\ub098\uc758
\n\ud589\ub9cc\uc744 \uac00\uc9c4 dual \ud14c\uc774\ube14\uc744 \uc774\uc6a9\ud574\uc11c \uacb0\uacfc\ub97c \ubcf4\uace4 \ud55c\ub2e4.<\/p>\n
(2) ROUND \ud568\uc218<\/p>\n
ROUND(m, n)
\n-> m\uc744 \uc18c\uc218\uc810 n + 1 \uc790\ub9ac\uc5d0\uc11c \ubc18\uc62c\ub9bc\ud55c \uacb0\uacfc\ub97c \ubc18\ud658\ud55c\ub2e4.<\/p>\n
ex) 18.354\ub97c \uc18c\uc218\uc810 \uc14b\uc9f8 \uc790\ub9ac\uc5d0\uc11c \ubc18\uc62c\ub9bc\ud55c \uacb0\uacfc\ub97c \ubc18\ud658\ud558\ub77c.<\/p>\n
SQL> SELECT ROUND(18.354, 2) “Round”
\n 2 FROM dual;<\/p>\n
Round
\n———-
\n 18.35<\/p>\n
ex) 18.354\ub97c \uc815\uc218 \uc77c\uc758 \uc790\ub9ac(\uc22b\uc790 8)\uc5d0\uc11c \ubc18\uc62c\ub9bc\ud55c \uacb0\uacfc\ub97c \ubc18\ud658\ud558\ub77c.<\/p>\n
SQL> SELECT ROUND(18.354, -1) “Round”
\n 2 FROM dual;<\/p>\n
Round
\n———-
\n 20<\/p>\n
-> n \uac12\uc774 \uc591\uc218\uc774\uba74 \uc18c\uc218\uc810\uc774 \uc6b0\uce21\uc73c\ub85c \uc774\ub3d9\ud558\uba70, \uc74c\uc218\uc774\uba74 \uc88c\uce21\uc73c\ub85c \uc774\ub3d9\ud55c\ub2e4. 18.354\uc5d0\uc11c \uc6b0\uce21\uc73c\ub85c \uc18c\uc218\uc810 2\uc790\ub9ac
\n\uc774\ub3d9\uc744 \ud558\uba74 \uc18c\uc218\uc810 \uc774\ud558 5 \ub2e4\uc74c\uc5d0 \uc704\uce58\ud558\uc5ec \uc18c\uc218\uc810 \uc774\ud558 \uc989 4\uc5d0 \ub300\ud574 \ubc18\uc62c\ub9bc\uc744 \ud55c\ub2e4. \ud3b8\uc758\uc0c1 n + 1 \uc790\ub9ac\uc5d0\uc11c \ubc18\uc62c\ub9bc\uc744
\n\uc218\ud589\ud558\uc5ec \uacb0\uacfc\ub97c \ubc18\ud658\ud55c\ub2e4\uace0 \ubcf4\uba74 \ub41c\ub2e4. \ucc38\uace0\ub85c \ubc18\uc62c\ub9bc \ub300\uc2e0 \ub0b4\ub9bc\uc744 \uc218\ud589\ud558\ub294 TRUNC() \ud568\uc218\ub3c4 \uc788\ub2e4.<\/p>\n
\ub2e4\uc74c\uc740 ROUND() \ud568\uc218\ub97c \uc774\uc6a9\ud558\uc5ec \uae09\uc5ec \ub300\ud55c \ud3c9\uade0\uc744 \uc18c\uc218 \ub458\uc9f8 \uc790\ub9ac\uc5d0\uc11c \ubc18\uc62c\ub9bc\ud55c \uacb0\uacfc\uc774\ub2e4.<\/p>\n
SQL> SELECT department_id, ROUND(AVG(salary), 1) AS AvgSalary
\n 2 FROM employees
\n 3 GROUP BY department_id;<\/p>\n
DEPARTMENT_ID AVGSALARY
\n————- ———-
\n 100 8600
\n 30 4150
\n 7000
\n 20 9500
\n 70 10000
\n 90 19333.3
\n 110 10150
\n 50 3475.6
\n 40 6500
\n 80 8955.9
\n 10 4400<\/p>\n
DEPARTMENT_ID AVGSALARY
\n————- ———-
\n 60 5760<\/p>\n
12 rows selected.<\/p>\n
\n(3) WIDTH_BUCKET \ud568\uc218<\/p>\n
WIDTH_BUCJET(expr, min_value, max_value, num_buckets)
\n-> \uc5b4\ub5a4 \uac12\uc758 \ucd5c\uc18c\uc5d0\uc11c\ubd80\ud130 \ucd5c\ub300\uac12\uc744 \uc124\uc815\ud558\uace0 bucket\uc744 \uc9c0\uc815\ud558\uc5ec \uc784\uc758\uc758 \uac12\uc774 \uc9c0\uc815\ub41c \ubc94\uc704 \ub0b4\uc5d0\uc11c \uc5b4\ub290 \uc704\uce58\uc5d0 \uc788\ub294\uac00\ub97c \ubc18\ud658\ud55c\ub2e4.<\/p>\n
ex) 0\ubd80\ud130 100\uae4c\uc9c0\ub97c 10\uac1c\uc758 \uad6c\uac04\uc73c\ub85c \ub098\ub208 \ud6c4 92\uac00 \uba87 \ubc88\uc9f8 \uad6c\uac04\uc5d0 \uc18d\ud558\ub294\uc9c0\ub97c \uc54c\uc544\ubcf8\ub2e4.<\/p>\n
SQL> SELECT WIDTH_BUCKET(92, 0, 100, 10) “Score”
\n 2 FROM dual;<\/p>\n
Score
\n———-
\n 10<\/p>\n
-> bucket\uc758 \uc0ac\uc804\uc801\uc778 \uc758\ubbf8\ub294 ‘\ubb3c\ud1b5’, ‘bucket\uc73c\ub85c \ubb3c\uc744 \ub098\ub974\ub2e4’\uc758 \uc758\ubbf8\uc774\ub2e4. \uc704 \ud568\uc218\uc5d0\uc11c\ub294 \uc5b4\ub5a4 \uac12\uc758
\n\ucd5c\uc18c\uc5d0\uc11c\ubd80\ud130 \ucd5c\ub300\uac12\uc774 \uc124\uc815\ub418\uba74 \ubc94\uc704 \ub0b4 bucket\uc744 \uc9c0\uc815\ud558\uc5ec \uc784\uc758\uc758 \uac12\uc774 \uc774 \ubc94\uc704 \ub0b4\uc5d0\uc11c \uc5b4\ub290 \uc704\uce58\uc5d0 \uc788\ub294\uc9c0\ub97c \uc54c \uc218 \uc788\ub2e4.<\/p>\n
ex) \ub2e4\uc74c\uc740 hr \uc18c\uc720\uc758 \uc0ac\uc6d0 \ud14c\uc774\ube14\uc5d0\uc11c \uae09\uc5ec\uc5d0 \ub300\ud574 \ub4f1\uae09\uc744 \uc815\uc758\ud55c \uac83\uc774\ub2e4.<\/p>\n
SQL> SELECT employee_id, salary, WIDTH_BUCKET(salary, 0, 20000, 10) “Grade”
\n 2 FROM employees
\n 3 WHERE department_id = 50;<\/p>\n
\n2) \ubb38\uc790 \ud568\uc218(Character Functions)
\n-> \ubb38\uc790 \ub370\uc774\ud130 \ud0c0\uc785\uc758 \uac12\uc744 \uc870\uc791\ud558\uc5ec \ubb38\uc790 \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.<\/p>\n
(1) LOWER \ud568\uc218<\/p>\n
LOWER(char)
\n-> \uc785\ub825\ub41c \ubb38\uc790 \uac12\uc744 \uc18c\ubb38\uc790\ub85c \ubcc0\ud658\ud55c\ub2e4.<\/p>\n
ex) ‘Korea’\ub77c\ub294 \ubb38\uc790\uc5f4\uc744 \ubaa8\ub450 \uc18c\ubb38\uc790\ub85c \ubcc0\ud658\ud55c \ud6c4 ‘Lower’\ub85c \ub098\ud0c0\ub0b8\ub2e4.<\/p>\n
SQL> SELECT LOWER(‘Korea’) “Lower”
\n 2 FROM dual;<\/p>\n
Lower
\n—–
\nkorea<\/p>\n
-> \ud14c\uc774\ube14\uc5d0 \uc800\uc7a5\ub418\uc5b4 \uc788\ub294 \uc601\ubb38\uc790\uc5d0 \ub300\ud574\uc11c Oracle\uc740 \ub300\uc18c\ubb38\uc790\ub97c \uad6c\ubd84\ud55c\ub2e4. WHERE\uc808\uc5d0\uc11c \uc9c0\uc815\ub41c \uac12\uc5d0 \ub300\ud574 \uc815\ud655\ud558\uac8c \ub300\uc18c\ubb38\uc790\uac00 \ud45c\ud604\ub418\uc5b4\uc57c \ud55c\ub2e4.<\/p>\n
ex) \uc0ac\uc6d0\ubc88\ud638 139\ubc88\uc778 ‘Seo’\uc758 \uc815\ubcf4\ub97c \uc5bb\uc5b4\uc628\ub2e4. \ub9cc\uc57d ‘Seo’\ub77c\ub294 \uc774\ub984\uc774 \uc788\ub2e4\uba74 \ub300\uc18c\ubb38\uc790\uc5d0 \uad00\uacc4\uc5c6\uc774 \uc544\ub798\uc640 \uac12\uc774 LOWER() \ud568\uc218\ub97c \uc774\uc6a9\ud558\uc5ec \uc9c8\uc758\ubb38\uc744 \uc791\uc131\ud560 \uc218 \uc788\ub2e4.<\/p>\n
SQL> SELECT employee_id, last_name
\n 2 FROM employees
\n 3 WHERE last_name = ‘Seo’;<\/p>\n
EMPLOYEE_ID LAST_NAME
\n———– ————————-
\n 139 Seo<\/p>\n
SQL> SELECT employee_id, last_name
\n 2 FROM employees
\n 3 WHERE LOWER(last_name) = ‘seo’;<\/p>\n
EMPLOYEE_ID LAST_NAME
\n———– ————————-
\n 139 Seo<\/p>\n
\uc774\uc640 \ube44\uc2b7\ud558\uac8c \ubaa8\ub4e0 \ubb38\uc790\uac12\uc744 \ub300\ubb38\uc790\ub85c \ubc18\ud658\ud558\ub294 UPPER() \ud568\uc218\ub3c4 \uc788\ub2e4.<\/p>\n
\n(2) SUBSTR \ud568\uc218<\/p>\n
SUBSTR(string, position, substring_length)
\n-> \ubb38\uc790\uc5f4\uc5d0\uc11c \uc77c\ubd80 \ubb38\uc790\uac12\uc744 \uc120\ud0dd\uc801\uc73c\ub85c \ubc18\ud658\ud55c\ub2e4.<\/p>\n
ex) ‘Korea Economy’ \ubb38\uc790\uc5f4\uc758 \ucc98\uc74c\ubd80\ud130 \ub2e4\uc12f \uc790\ub9ac\uae4c\uc9c0\uc758 \ubb38\uc790\ub97c \ubc18\ud658\ud55c\ub2e4.<\/p>\n
SQL> SELECT SUBSTR(‘Korea Economy’, 1, 5) “substr”
\n 2 FROM dual;<\/p>\n
subst
\n—–
\nKorea<\/p>\n
-> \ubb38\uc790\uc5f4 ‘Korea Economy’\uc5d0\uc11c \uc2dc\uc791 \ubb38\uc790 \uc704\uce58\uac00 1 \uadf8\ub9ac\uace0 \ubb38\uc790 5\uac1c\ub97c \uc120\ud0dd\ud558\uc5ec \ubc18\ud658\ud568\uc73c\ub85c \uc704\uc640 \uac19\uc740 \uacb0\uacfc\ub97c \uc5bb\uc744 \uc218 \uc788\ub2e4.<\/p>\n
3) \ub0a0\uc9dc \ud568\uc218(Datetime Functions)
\n-> DATE\ub85c \uc9c0\uc815\ub41c \ub370\uc774\ud130 \ud0c0\uc785\uc758 \uac12\uc5d0 \uc801\uc6a9\ud55c\ub2e4.<\/p>\n
(1) SYSDATE \ud568\uc218<\/p>\n
SYSDATE
\n-> \uc2dc\uc2a4\ud15c\uc758 \uc124\uc815\ub41c \ub0a0\uc9dc \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.<\/p>\n
ex) \ud604\uc7ac \uc2dc\uc2a4\ud15c\uc758 \ub0a0\uc9dc\ub97c \uac00\uc838\uc628\ub2e4.<\/p>\n
SQL> SELECT SYSDATE
\n 2 FROM dual;<\/p>\n
SYSDATE
\n———
\n30-MAR-09<\/p>\n
\n(2) ADD_MONTHS \ud568\uc218<\/p>\n
ADD_MONTHS(date, integer)
\n-> \uc9c0\uc815\ud55c \ub0a0\uc9dc \uc911 \ud574\ub2f9 \uc6d4\uc5d0 \uc815\uc218\ub97c \ub354\ud55c \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.<\/p>\n
ex) \ud604\uc7ac \ub0a0\uc9dc\uc5d0\uc11c 30\uac1c\uc6d4 \ub4a4\uc758 \ub0a0\uc9dc\ub97c \ud655\uc778\ud55c\ub2e4.<\/p>\n
SQL> SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 30), ‘YYYY-MM-DD’)
\n 2 FROM dual;<\/p>\n
TO_CHAR(AD
\n———-
\n2011-09-30<\/p>\n
-> \uccab \ubc88\uc9f8 \uc778\uc790\ub85c\ubd80\ud130 \ud604\uc7ac\uc758 \ub0a0\uc9dc\uc778 ‘2009\ub144 3\uc6d4 30\uc77c’\uc744 \uc5bb\uace0, \ub450 \ubc88\uc9f8 \uc778\uc790\ub85c\ubd80\ud130 \uc5bb\uc740 \uac12 ’30’\uc744 \uccab \ubc88\uc9f8
\n\uac12\uc758 \uc6d4\uc5d0 \ub354\ud55c \uacb0\uacfc\ub97c \ubc18\ud658\ud55c\ub2e4. \ubc18\ud658\ub418\ub294 \uac82\uc740 \ub0a0\uc9dc \ud0c0\uc785\uc774\uba70, \uc774\ub97c \ubb38\uc790\ub85c \ud615 \ubcc0\ud658\ud558\uc5ec \uc27d\uac8c \uc2dd\ubcc4\ud560 \uc218 \uc788\ub3c4\ub85d \ud558\uc600\ub2e4.<\/p>\n
\n(3) LAST_DAY \ud568\uc218<\/p>\n
LAST_DAY(date)
\n-> \ud574\ub2f9\ud558\ub294 \uc6d4\uc758 \ub9c8\uc9c0\ub9c9 \uc77c\uc744 \ubc18\ud658\ud55c\ub2e4.<\/p>\n
ex) \ud604\uc7ac\uc758 \ub0a0\uc9dc\ub85c\ubd80\ud130 \ub9c8\uc9c0\ub9c9 \ub0a0\uc9dc\uae4c\uc9c0 \ub0a8\uc740 \uc77c \uc218\ub97c \ubc18\ud658\ud55c\ub2e4.<\/p>\n
SQL> SELECT LAST_DAY(SYSDATE) – SYSDATE “Remain Days”
\n 2 From dual;<\/p>\n
Remain Days
\n———–
\n 1<\/p>\n
-> \ud604\uc7ac\uc758 \ub0a0\uc9dc ‘2009\ub144 3\uc6d4 30\uc77c’\ub85c\ubd80\ud130 3\uc6d4\uc758 \ub9c8\uc9c0\ub9c9 \ub0a0\uc9dc\ub97c LAST_DAY() \ud568\uc218\ub85c\ubd80\ud130 \uc5bb\uc5b4\ub0b4\uace0 \uc788\ub2e4.
\n\ubc18\ud658\ub418\ub294 \uac12 ‘2009 3\uc6d4 30\uc77c’\ub85c\ubd80\ud130 \ud604\uc7ac\uc758 \ub0a0\uc9dc\ub97c \ube80 \uacb0\uacfc\ub97c \ubc18\ud658\ud55c\ub2e4. \uc989 2009\ub144 3\uc6d4 30\uc77c\uc740 \uc774 \ub2ec \ub9d0\uc77c\ub85c\ubd80\ud130
\n1\uc77c \ub0a8\uc544 \uc788\uc74c\uc744 \uc54c \uc218 \uc788\ub2e4. \ubc18\ud658\ub418\ub294 \uac12\uc740 \uc22b\uc790\uc774\uba70, \ub2e8\uc704\ub294 ‘\uc77c(day)’\uc774\ub2e4.<\/p>\n
\n(4) MONTHS_BETWEEN \ud568\uc218<\/p>\n
MONTHS_BETWEEN(date1, date2)
\n-> \ub0a0\uc9dc\uc640 \ub0a0\uc9dc \uc0ac\uc774\uc758 \uae30\uac04\uc744 ‘\uc6d4(month)’\ub85c \ub098\ud0c0\ub0b8\ub2e4. date1\uc774 date2\ubcf4\ub2e4 \ud070 \uac12\uc774\ub2e4.<\/p>\n
ex) \ud604\uc7ac \ub2ec\uc758 \ub9c8\uc9c0\ub9c9 \ub0a0\uc9dc\ub85c\ubd80\ud130 \uc624\ub298 \ub0a0\uc9dc\ub97c \ube80 \uacb0\uacfc\ub97c \uc6d4\ub85c \ub098\ud0c0\ub0b8\ub2e4.<\/p>\n
SQL> SELECT MONTHS_BETWEEN(LAST_DAY(SYSDATE), SYSDATE) “Remain Months”
\n 2 FROM dual;<\/p>\n
Remain Months
\n————-
\n .032258065<\/p>\n
-> LAST_DAY() \ud568\uc218\uc758 \uc608\uc81c\uc640 \ub3d9\uc77c\ud55c \uacbd\uc6b0\uc774\ub2e4. \ub2e8, \ubc18\ud658\ub418\ub294 \ub2e8\uc704\ub294 ‘\uc6d4(month)’\uc774\ub2e4. \uc989, 2009\ub144
\n3\uc6d4 30\uc77c\uc740 \uc774 \ub2ec \ub9d0\uc778\uc778 2009\ub144 3\uc6d4 31\uc77c\ub85c\ubd80\ud130 0.032258065 \uac1c\uc6d4 \ub0a8\uc544 \uc788\uc74c\uc744 \uc54c \uc218 \uc788\ub2e4.<\/p>\n
\n4) \ubcc0\ud658 \ud568\uc218(Conversion Functions)<\/p>\n
(1) TO_CHAR(datetime)<\/p>\n
TO_CHAR(datetime, ‘format’)
\n-> DATE \uad00\ub828 \ud0c0\uc785\uc744 VARCHAR2 \ub370\uc774\ud130 \ud0c0\uc785\uc73c\ub85c \ubcc0\ud658\ud55c\ub2e4.<\/p>\n
ex) \uc2dc\uc2a4\ud15c \ub0a0\uc9dc\ub97c ‘\uc5f0-\uc6d4-\uc77c \uc2dc-\ubd84-\ucd08’\uc758 \ud615\uc2dd\uc73c\ub85c \ubcc0\ud658\ud574 \ubcf8\ub2e4.<\/p>\n
SQL> SELECt TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’) “sysdate”
\n 2 FROM dual;<\/p>\n
sysdate
\n——————-
\n2009-03-30 02:43:08<\/p>\n
-> \ub0a0\uc790 \ub370\uc774\ud130 \ud0c0\uc785\uc758 \uac12\uc744 \uc77c\uc815\ud55c \uc591\uc2dd(format)\uc5d0 \uc758\ud574 \ubb38\uc790\ub85c \ubcc0\ud658\ud55c\ub2e4. \ub0a0\uc9dc\uc758 \ud2b9\uc815 \ubd80\ubd84\uc744 \ud45c\ud604\ud560 \uc218 \uc788\ub294 \ubc29\ubc95\uc740 \uc544\ub798\uc640 \uac19\ub2e4.<\/p>\n
\uad6c\uc131\uc694\uc18c : \uc758\ubbf8
\nD : \uc694\uc77c \ubc88\ud638(1 – 7)
\nDAY : \uc694\uc77c\uba85
\nDD : \uc77c(1 – 31)
\nDY : \uc694\uc77c\uba85 \uc57d\uc790
\nHH : \uc2dc (1 – 12)
\nHH24 : \uc2dc(0 – 23)
\nMI : \ubd84(0 – 59)
\nMM : \uc6d4(1 – 12)
\nMON : \uc6d4 \uc774\ub984 \uc57d\uc790
\nMONTH : \uc6d4 \uc774\ub984
\nSS : \ucd08(0 – 59)
\nYEAR : \ub144\ub3c4
\nYYYY : 4\uc790\ub9ac \ub144\ub3c4<\/p>\n
ex) \uc2dc\uc2a4\ud15c \ub0a0\uc9dc\ub97c \uc704\uc640\ub294 \ub2e4\ub978 \ubc29\uc2dd\uc73c\ub85c \ud45c\ud604\ud574 \ubcf8\ub2e4.<\/p>\n
SQL> SELECT TO_CHAR(SYSDATE, ‘YEAR-MON-DY HH:MI:SS’) “sysdate”
\n 2 FROM dual;<\/p>\n
sysdate
\n—————————————————————————–
\nTWO THOUSAND NINE-MAR-MON 02:46:43<\/p>\n
ex) \ub2e4\uc74c\uc740 1998\ub144 \uc6d4\ubcc4 \uc785\uc0ac\uc790\uc758 \uc218\ub97c \uacc4\uc0b0\ud558\ub294 \uc608\uc774\ub2e4.<\/p>\n
SQL> SELECT TO_CHAR(hire_date, ‘YYYY-MM’), COUNT(last_name)
\n 2 FROM employees
\n 3 WHERE TO_CHAR(hire_date, ‘YYYY’) = ‘1998’
\n 4 GROUP BY TO_CHAR(hire_date, ‘YYYY-MM’);<\/p>\n
TO_CHAR COUNT(LAST_NAME)
\n——- —————-
\n1998-05 1
\n1998-09 1
\n1998-01 2
\n1998-02 3
\n1998-08 1
\n1998-04 3
\n1998-07 3
\n1998-12 1
\n1998-06 1
\n1998-03 5
\n1998-11 2<\/p>\n
\n(2) TO_CHAR(number)<\/p>\n
TO_CHAR(number, ‘format’)
\n-> NUMBER \ud0c0\uc785\uc744 VARCHAR2 \ub370\uc774\ud130 \ud0c0\uc785\uc73c\ub85c \ubcc0\ud658\ud55c\ub2e4. VARCHAR2\ub294 \uac00\ubcc0 \ubb38\uc790(Variable Character)\uc758 \uc758\ubbf8\ub85c, \uae38\uc774\uac00 \uc77c\uc815\ud558\uc9c0 \uc54a\uc740 \ubb38\uc790 \uac12\uc744 \uc800\uc7a5\ud558\ub294 \ub370\uc774\ud130 \ud0c0\uc785\uc774\ub2e4.<\/p>\n
ex) ‘20000’\uc744 \uc22b\uc790 \ud45c\ud604 \uc591\uc2dd \ud654\ud3d0 \ub2e8\uc704\uc778 ‘$’\ub85c \ud45c\ud604\ud574 \ubcf8\ub2e4.<\/p>\n
SQL> SELECT TO_CHAR(20000, ‘$999,999’) “money”
\n 2 FROM dual;<\/p>\n
money
\n———
\n $20,000<\/p>\n
-> \uc22b\uc790 \ub370\uc774\ud130 \ud0c0\uc785\uc758 \uac12\uc744 \uc77c\uc815\ud55c \uc591\uc2dd(format)\uc5d0 \uc758\ud574 \ubb38\uc790\ub85c \ubcc0\ud658\ud55c\ub2e4. \uc774\ucc98\ub7fc \uc22b\uc790\uc758 \ud2b9\uc815 \ubd80\ubd84\uc744 \ud45c\ud604\ud560 \uc218 \uc788\ub294 \ubc29\ubc95\uc740 \uc544\ub798\uc640 \uac19\ub2e4.<\/p>\n
\uc22b\uc790 : \ud615\uc2dd : \uacb0\uacfc
\n123.45 : 999.999 : 123.450
\n123.45 : 9999 : 123
\n123.45 : $999.99 : $123.45
\n123.45 : L999.99 : 123.45
\n123.45 : S999.999 : +123.45
\n12345 : 999,999 : 12,345<\/p>\n
-> L\uc740 \uc9c0\uc5ed \ud654\ud3d0 \ub2e8\uc704\ub97c \ub098\ud0c0\ub0b8\ub2e4. \ub300\ud55c\ubbfc\uad6d\uc740 \uc6d0\ud654\ub97c \ub2e8\uc704\ub85c \ub85c \ud45c\ud604\ub41c\ub2e4. S\ub294 \uc591\uc218\ub098 \uc74c\uc218\ub97c \ud45c\uc2dc\ud558\ub294 \ubd80\ud638\ub97c \ub098\ud0c0\ub0b8\ub2e4. \uc591\uc218\uc774\ubbc0\ub85c +\ub85c \ud45c\ud604\ud55c\ub2e4.<\/p>\n
5) \uae30\ud0c0 \ud568\uc218(Miscellaneous Single-Row Functions)<\/p>\n
(1) DECODE \ud568\uc218<\/p>\n
DECODE(expr, search, result, default)
\n-> ‘expr’\uacfc \uac01 ‘search’ \uac12\uc744 \ube44\uad50\ud558\uc5ec \uac19\uc73c\uba74 result \uac12\uc744 \ubc18\ud658\ud55c\ub2e4. \uac19\uc9c0 \uc54a\ub2e4\uba74 default \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.<\/p>\n
ex) job_id\uac00 ‘SA_MAN’\ud639\uc740 ‘SA_REP’\uc774\uba74 \ud310\ub9e4 \ubd80\uc11c\uc778 ‘Sales Dept’\ub97c, \uadf8 \uc678\uc5d0\ub294 \ub2e4\ub978 \ubd80\uc11c\uc774\ubbc0\ub85c ‘Another’\ub97c \ud45c\uc2dc\ud55c\ub2e4.<\/p>\n
SQL> SELECT job_id,
\n 2 DECODE (job_id, ‘SA_MAN’, ‘Sales Dept’, ‘SA_REP’, ‘Sales Dept’, ‘Another’)
\n 3 “decode”
\n 4 FROM jobs WHERE job_id LIKE ‘S%’;<\/p>\n
JOB_ID decode
\n———- ———-
\nSA_MAN Sales Dept
\nSA_REP Sales Dept
\nSH_CLERK Another
\nST_CLERK Another
\nST_MAN Another<\/p>\n
-> job_id\uac00 \ub300\ubb38\uc790 S\ub85c \uc2dc\uc791\ud558\ub294 \uac83 \uc911\uc5d0\uc11c ‘SA_MAN’\uacfc ‘SA_REP’\uc778 \uacbd\uc6b0\ub294 \uc601\uc5c5 \ubd80\uc11c\uc784\uc744 \ub098\ud0c0\ub0b4\uae30 \uc704\ud574
\n‘Sales Dept’\ub85c \ud45c\ud604\ud558\ub418 \uadf8 \uc774\uc678\ub294 ‘Another’\ub85c \ub098\ud0c0\ub0b8 \uc608\uc774\ub2e4. \ucc38\uace0\ub85c DECODE)_ \ud568\uc218\ub294 CASE \uad6c\ubb38\uc73c\ub85c
\n\ud45c\ud604\ud560 \uc218 \uc788\ub2e4. \ub2e4\uc74c\uc740 CASE\uad6c\ubb38\uc73c\ub85c \ud45c\ud604\ud55c \uc608\uc774\ub2e4.<\/p>\n
* CASE \uad6c\ubb38\uc744 \uc774\uc6a9\ud55c \ud45c\ud604<\/p>\n
CASE value
\n WHEN expr1 THEN
\n \uad6c\ubb381
\n WHEN expr2 THEN
\n \uad6c\ubb382
\n ELSE
\n \uad6c\ubb383
\n END CASE<\/p>\n
SQL> SELECT job_id,
\n 2 CASE job_id
\n 3 WHEN ‘SA_MAN’ THEN
\n 4 ‘Sales Dept’
\n 5 WHEN ‘SA_REP’ THEN
\n 6 ‘Sales Dept’
\n 7 ELSE
\n 8 ‘Another’
\n 9 END CASE
\n10 FROM jobs
\n11 WHERE job_id LIKE ‘S%’;<\/p>\n
JOB_ID CASE
\n———- ———-
\nSA_MAN Sales Dept
\nSA_REP Sales Dept
\nSH_CLERK Another
\nST_CLERK Another
\nST_MAN Another<\/p>\n
\n (2) NVL \ud568\uc218<\/p>\n
NVL(expr1, expr2)
\n -> expr1\uc774 NULL \uac12\uc778 \uacbd\uc6b0 expr2\uc758 \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.<\/p>\n
ex) \ucee4\ubbf8\uc158(commission_pct)\uc774 \uc5c6\ub294 \uacbd\uc6b0, \uc989 NULL\uc774\uba74 0\uc73c\ub85c \ud45c\uc2dc\ud55c\ub2e4.<\/p>\n
SQL> SELECT employee_id, salary, NVL(commission_pct, 0) “commission_pct”
\n 2 FROM employees
\n 3 WHERE job_id = ‘IT_PROG’;<\/p>\n
EMPLOYEE_ID SALARY commission_pct
\n———– ———- ————–
\n 103 9000 0
\n 104 6000 0
\n 105 4800 0
\n 106 4800 0
\n 107 4200 0<\/p>\n
\n * \ub2e4\uc74c\uc740 \uc790\uc8fc \uc0ac\uc6a9\ub418\ub294 \ub2e8\uc77c \ud589 \ud568\uc218\ub97c \uc694\uc57d\ud55c \ud45c\uc774\ub2e4.<\/p>\n
\ud568\uc218\uc774\ub984 : \uc124\uba85<\/p>\n
* \uc22b\uc790 \ud568\uc218 (Number Functions)
\nMOD(m, n) : m\uc744 n\uc73c\ub85c \ub098\ub204\uc5c8\uc744 \ub54c \ub098\uba38\uc9c0\ub97c \ubc18\ud658\ud55c\ub2e4.
\nROUND(m, n) : m\uc744 \uc18c\uc218\uc810 n + 1 \uc790\ub9ac\uc5e3 \ubc18\uc62c\ub9bc\ud55c \uacb0\uacfc\ub97c \ubc18\ud658\ud55c\ub2e4.
\nWIDTH_BUCKET(expr, min_value, max_value, num_buckets) : \uc5b4\ub5a4 \uac12\uc758 \ucd5c\uc18c\uc5d0\uc11c \ucd5c\ub300\uac12\uc744 \uc124\uc815\ud558\uace0 bucket\uc744 \uc9c0\uc815\ud558\uc5ec \uc784\uc758\uc758 \uac12\uc774 \uc9c0\uc815\ub41c \ubc94\uc704 \ub0b4\uc5d0\uc11c \uc5b4\ub290 \uc704\uce58\uc5d0 \uc788\ub294\uc9c0\ub97c \ubc18\ud658\ud55c\ub2e4.
\nCEIL(n) : \uc62c\ub9bc\ud55c \ud6c4 \uc815\uc218\ub97c \ubc18\ud658\ud55c\ub2e4.
\nFLOOR(n) : \ub0b4\ub9bc\ud55c \ud6c4 \uc815\uc218\ub97c \ubc18\ud658\ud55c\ub2e4.
\nABS(n) : \uc808\ub300\uac12\uc744 \ubc18\ud658\ud55c\ub2e4.
\nNANVL(m, n) : \uc785\ub825 \uac12 m\uc774 \uc22b\uc790\uac00 \uc544\ub2c8\ub77c\uba74 \ub300\uccb4 n\uc744 \ubc18\ud658\ud558\uace0, \uc22b\uc790\ub77c\uba74 m\uc744 \ubc18\ud658\ud55c\ub2e4.
\nTRUNC(n, [m]) : \uc778\uc218 n \uc18c\uc218\uc810\uc790\ub9ac m\uc774\ud558\ub97c \uc808\uc0ad\ud55c\ub2e4.<\/p>\n
* \ubb38\uc790 \ud568\uc218 (Charater Functions)
\nLOWER(char) : \uc785\ub825\ub41c \ubb38\uc790 \uac12\uc744 \uc18c\ubb38\uc790\ub85c \ubcc0\ud658\ud55c\ub2e4.
\nSUBSTR(string, position, substring_length) : \ubb38\uc790\uc5f4\uc5d0\uc11c \uc77c\ubd80 \ubb38\uc790\uac12\uc744 \uc120\ud0dd\uc801\uc73c\ub85c \ubc18\ud658\ud55c\ub2e4.
\nASCII(char) : \ubb38\uc790\uc758 ASCII \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.
\nCHR(n [USING NCHAR_CS]) : 10\uc9c4\uc218 n\uc5d0 \ub300\uc751\ud558\ub294 \uc544\uc2a4\ud0a4 \ucf54\ub4dc\ub97c \ubc18\ud658\ud55c\ub2e4.
\nLENGTH(char)
\nLTRIM(char, [set]) : \ubb38\uc790\uc5f4 char \uc88c\uce21\uc73c\ub85c\ubd80\ud130 set\uc73c\ub85c \uc9c0\uc815\ub41c \ubaa8\ub4e0 \ubb38\uc790\ub97c \uc81c\uac70\ud55c\ub2e4.
\nLPAD(expr1, n, [expr2]) : \uc9c0\uc815\ub41c \uc790\ub9ac\uc218 n\uc73c\ub85c\ubd80\ud130 expr1\uc744 \ucc44\uc6b0\uace0, \uc67c\ud3b8\uc758 \ub0a8\uc740 \uacf5\uac04\uc5d0 expr2\ub97c \ucc44\uc6b4\ub2e4.
\nREPLACE(expr, str1, [str2]) : \ud30c\ub77c\ubbf8\ud130\ub85c \uc8fc\uc5b4\uc9c0\ub294 \uccab \ubc88\uc9f8 \ubb38\uc790\uc5f4\uc5d0\uc11c, \ub450 \ubc88\uc9f8 \ubb38\uc790\uc5f4\uc744 \ubaa8\ub450 \uc138 \ubc88\uc9f8 \ubb38\uc790\uc5f4\ub85c \ubc14\uafbc \ud6c4 \uacb0\uacfc\ub97c \ubc18\ud658\ud55c\ub2e4.<\/p>\n
* \ub0a0\uc9dc \ud568\uc218 (Datetime Functions)
\nSYSDATE : \uc2dc\uc2a4\ud15c\uc758 \uc124\uc815\ub41c \ub0a0\uc9dc \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.
\nADD_MONTHS(date, integer) : \uc9c0\uc815\ud55c \ub0a0\uc9dc \uc911 \ud574\ub2f9 \uc6d4\uc5d0 \uc815\uc218\ub97c \ub354\ud55c \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.
\nLAST_DAY(date) : \ud574\ub2f9\ud558\ub294 \uc6d4\uc758 \ub9c8\uc9c0\ub9c9 \uc77c\uc744 \ubc18\ud658\ud55c\ub2e4.
\nMONTHS_BETWEEN(date1, date2) : \ub0a0\uc9dc\uc640 \ub0a0\uc9dc \uc0ac\uc774\uc758 \uae30\uac04\uc744 ‘\uc6d4(month)’\ub85c \ub098\ud0c0\ub0b8\ub2e4.
\nNEXT_DAY(date, char) : \ud574\ub2f9\uc77c\uc744 \uae30\uc900\uc73c\ub85c \uba85\uc2dc\ub41c \uc694\uc77c\uc758 \ub2e4\uc74c \ub0a0\uc9dc\ub97c \ubc18\ud658\ud55c\ub2e4.<\/p>\n
* \ubcc0\ud658 \ud568\uc218 (Conversion Functions)
\nTO_CHAR(datetime, ‘fmt’) : DATE \uad00\ub828 \ub370\uc774\ud130 \ud0c0\uc785\uc744 VARCHAR2 \ub370\uc774\ud130 \ud0c0\uc785\uc73c\ub85c \ubcc0\ud658\ud55c\ub2e4.
\nTO_CHAR(number, ‘fmt’) : NUMBER \ud0c0\uc785\uc744 VARCHAR2 \ub370\uc774\ud130 \ud0c0\uc785\uc73c\ub85c \ubcc0\ud658\ud55c\ub2e4.
\nCAST({expr | MULTISET(subquery)} AS type_name) : \ub370\uc774\ud130 \ud615\uc2dd\uc774\ub098 collection \ud615\uc2dd\uc744 \ub2e4\ub978 \ub370\uc774\ud130 \ud615\uc2dd\uc774\ub098 collection \ud615\uc2dd\uc73c\ub85c \ubcc0\ud658\ud55c\ub2e4.
\nCONVERT(char, dest_char, [source_char]) : \ubb38\uc790\uc138\ud2b8\ub97c \ub2e4\ub978 \ubb38\uc790\uc138\ud2b8\ub85c \ubb38\uc790\uc5f4\uc744 \ubcc0\ud658\ud55c\ub2e4.
\nTO_DATE(char, [fmt, ‘nlsparam’]) : char\ub97c \ub0a0\uc9dc\ud615 \ub370\uc774\ud130 \ud0c0\uc785\uac12\uc73c\ub85c \ubcc0\ud658\ud55c\ub2e4.
\nTO_NUMBER(expr, [fmt, ‘nlsparam’]) : expr\uc744 NUMBER \ub370\uc774\ud130\ud615\uc758 \uac12\uc73c\ub85c \ubcc0\ud658\ud55c\ub2e4.<\/p>\n
* \uae30\ud0c0 \ud568\uc218 (Miscellaneous Single-row Functions)
\nDECODE(expr, search, result, default) : expr\uacfc search \uac12\uc744 \ube44\uad50\ud558\uc5ec \uac19\uc73c\uba74 result \uac12\uc744 \ubc18\ud658\ud55c\ub2e4. \uac19\uc9c0 \uc54a\ub2e4\uba74 default \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.
\nNVL(expr1, expr2) : expr1\uc774 NULL \uac12\uc778 \uacbd\uc6b0 expr2\uc758 \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.<\/p>\n
\n==========================================================<\/p>\n
3.2 \uc9d1\ud569 \ud568\uc218(Aggregate Functions)
\n -> \ud558\ub098 \uc774\uc0c1\uc758 \ud589\uc73c\ub85c\ubd80\ud130 \ud558\ub098\uc758 \uacb0\uacfc\ub97c \ubc18\ud658\ud55c\ub2e4.<\/p>\n
(1) AVG \ud568\uc218<\/p>\n
AVG(expr)
\n -> \ud558\ub098 \uc774\uc0c1\uc758 \uac12\ub4e4\ub85c\ubd80\ud130 \ud3c9\uade0\uac12\uc744 \ubc18\ud658\ud55c\ub2e4.<\/p>\n
ex) EMPLOYEES \ud14c\uc774\ube14\ub85c\ubd80\ud130 \uae09\uc5ec \ud3c9\uade0\uc744 \uad6c\ud55c\ub2e4.<\/p>\n
SQL> SELECT AVG(salary)
\n 2 FROM employees;<\/p>\n
AVG(SALARY)
\n———–
\n 6461.68224<\/p>\n
AVG(expr) OVER(analytical_clause)
\n -> \ubd84\uc11d \ud568\uc218\ub85c \uc0ac\uc6a9\ub41c \uc608. AVG() \ud568\uc218\ub294 \ubd84\uc11d \ud568\uc218\ub85c\ub3c4 \uc0ac\uc6a9\ud560 \uc218 \uc788\ub2e4.<\/p>\n
ex) \ubd80\uc11c(department_id)\ubcc4 \ud3c9\uade0\uac12\uc744 \uc5bb\uc5b4 \ubc18\ud658\ud558\ub294 \uc608\uc774\ub2e4 \ud3c9\uade0\uac12\uc758 \uc18c\uc218\uc810 \uc774\ud558 \ucc98\ub9ac\ub97c \uc704\ud574 \uc18c\uc218\uc810 \uc774\ud558\uc5d0\uc11c \ubc18\uc62c\ub9bc \ucc98\ub9ac\ud558\uc5ec \ubc18\ud658\ud558\uace0 \uc788\ub2e4.<\/p>\n
SQL> SELECT employee_id, department_id, salary,
\n 2 ROUND(AVG(salary) OVER(PARTITION BY department_id), 0) “AvgByDeptid”
\n 3 FROM employees
\n 4 WHERE department_id IN (10, 20, 30);<\/p>\n
EMPLOYEE_ID DEPARTMENT_ID SALARY AvgByDeptid
\n———– ————- ———- ———–
\n 200 10 4400 4400
\n 201 20 13000 9500
\n 202 20 6000 9500
\n 117 30 2800 4150
\n 118 30 2600 4150
\n 116 30 2900 4150
\n 119 30 2500 4150
\n 114 30 11000 4150
\n 115 30 3100 4150<\/p>\n
9 rows selected.<\/p>\n
(2) RANK \ud568\uc218
\n RANK(expr) WITHIN GROUP(ORDER BY expr)
\n -> \uc804\uccb4 \uac12\uc744 \ub300\uc0c1\uc73c\ub85c \uac01 \uac12\uc758 \uc21c\uc704\ub97c \ubc18\ud658\ud55c\ub2e4.<\/p>\n
ex) \uae09\uc5ec\uac00 $3,000\uc778 \uc0ac\ub78c\uc758 \uc0c1\uc704 \uae09\uc5ec \uc21c\uc704\ub97c \ub098\ud0c0\ub0b8\ub2e4.<\/p>\n
SQL> SELECT RANK(3000) WITHIN GROUP(ORDER BY salary DESC) “rank”
\n 2 FROM employees;<\/p>\n
rank
\n———-
\n 82<\/p>\n
RANK() OVER(query_partition_clause order_by_clause)
\n -> \ud2b9\uc815 \uae09\uc5ec\uc758 \uc21c\uc704\uac00 \uc544\ub2c8 \uc804\uccb4 \uae09\uc5ec\uc5d0 \ub300\ud574 \ud558\ub098\ud558\ub098 \uc21c\uc704\ub97c \uc54c\uace0 \uc2f6\uc744 \ub54c\uc5d0\ub3c4 RANK() \ud568\uc218\ub97c \uc0ac\uc6a9\ud55c\ub2e4.<\/p>\n
ex) \uc804\uccb4 \uc0ac\uc6d0\uc758 \uae09\uc5ec \uc21c\uc704\ub97c \ub098\ud0c0\ub0b8\ub2e4.<\/p>\n
SQL> SELECT employee_id, salary, RANK() OVER(ORDER BY salary) “rank”
\n 2 FROM employees;<\/p>\n
\n (3) SUM, MIN, MAX, COUNT \ud568\uc218
\n -> \ucc38\uace0\ub85c \ud558\ub098 \uc774\uc0c1\uc758 \ud589\uc73c\ub85c\ubd80\ud130 \ud569\uacc4\ub97c \ubc18\ud658\ud558\ub294 SUM() \ud568\uc218\uc640 \ucd5c\ub300\uac12\/\ucd5c\uc18c\uac12\uc744 \ubc18\ud658\ud558\ub294 MAX()\/MIN() \ud568\uc218\uac00 \uc788\ub2e4. \ub610 \uc804\uccb4 \ud589\uc758 \uc218\ub97c \ubc18\ud658\ud558\ub294 COUNT() \ud568\uc218\ub3c4 \uc788\ub2e4.<\/p>\n
ex) \uc5f0\ub3c4\ubcc4 \uc785\uc0ac\uc790\uc758 \uc218\uc640 \ucd5c\ub300, \ucd5c\uc18c \uae09\uc5ec\ub97c \ub098\ud0c0\ub0b4\ubcf4\ub77c.<\/p>\n
SQL> SELECT TO_CHAR(hire_date, ‘YYYY’), COUNT(last_name), MAX(salary), MIN(salary)
\n 2 FROM employees
\n 3 GROUP BY TO_CHAR(hire_date, ‘YYYY’);<\/p>\n
TO_C COUNT(LAST_NAME) MAX(SALARY) MIN(SALARY)
\n—- —————- ———– ———–
\n2000 11 10500 2200
\n1987 2 24000 4400
\n1997 28 13500 2500
\n1994 7 12000 6500
\n1991 1 6000 6000
\n1995 4 7900 3100
\n1990 1 9000 9000
\n1989 1 17000 17000
\n1999 18 11000 2100
\n1996 10 14000 3300
\n1993 1 17000 17000<\/p>\n
TO_C COUNT(LAST_NAME) MAX(SALARY) MIN(SALARY)
\n—- —————- ———– ———–
\n1998 23 10000 2500<\/p>\n
ex) \uc774\ubc88\uc5d0\ub294 \uac01 \uc5f0\ub3c4 \ubcc4 \uc785\uc0ac\uc790 \uc218\ub97c \ub098\ud0c0\ub0b4 \ubcf4\ub77c.<\/p>\n
SQL> SELECT TO_CHAR(hire_date, ‘YYYY’), COUNT(employee_id)
\n 2 FROM employees
\n 3 GROUP BY TO_CHAR(hire_date, ‘YYYY’);<\/p>\n
TO_C COUNT(EMPLOYEE_ID)
\n—- ——————
\n2000 11
\n1987 2
\n1997 28
\n1994 7
\n1991 1
\n1995 4
\n1990 1
\n1989 1
\n1999 18
\n1996 10
\n1993 1<\/p>\n
TO_C COUNT(EMPLOYEE_ID)
\n—- ——————
\n1998 23<\/p>\n
ex) 2000\ub144 \uc774\ud6c4 \uc785\uc0ac\uc790\ub97c \ub300\uc0c1\uc73c\ub85c \uc9c1\ubb34\ubcc4\ub85c \uae09\uc5ec\uc758 \ud569\uc744 \uc5bb\uc5b4\ubcf4\ub77c. \ud574\ub2f9 \uc9c1\ubb34\uc5d0 \uc18c\uc18d\ud55c \uc0ac\uc6d0\uc758 \uc218\ub3c4 \ud568\uaed8 \ub098\ud0c0\ub0b4\ub77c.<\/p>\n
SQL> SELECT job_id, SUM(salary), COUNT(employee_id)
\n 2 FROM employees
\n 3 WHERE TO_CHAR(hire_date, ‘YYYY’) >= 2000
\n 4 GROUP BY job_id;<\/p>\n
JOB_ID SUM(SALARY) COUNT(EMPLOYEE_ID)
\n———- ———– ——————
\nSH_CLERK 5400 2
\nSA_MAN 10500 1
\nSA_REP 38900 6
\nST_CLERK 4400 2<\/p>\n
* \ub2e4\uc74c\uc740 \uc790\uc8fc \uc0ac\uc6a9\ub418\ub294 \uc9d1\ud569 \ud568\uc218\ub97c \uc694\uc57d\ud55c \ud45c\uc774\ub2e4.<\/p>\n
\ud568\uc218\uc774\ub984 : \uc124\uba85
\nAVG(expr) : \ud558\ub098 \uc774\uc0c1\uc758 \uac12\ub4e4\ub85c\ubd80\ud130 \ud3c9\uade0\uac12\uc744 \ubc18\ud658\ud55c\ub2e4.
\nAVG(expr) OVER(analytical_clause) : \ud558\ub098 \uc774\uc0c1\uc758 \uac12\ub4e4\ub85c\ubd80\ud130 \ud3c9\uade0\uac12\uc744 \ubc18\ud658\ud55c\ub2e4.
\nRANK(expr) WITHIN GROUP(ORDER BY expr) : \uc804\uccb4 \uac12\uc744 \ub300\uc0c1\uc73c\ub85c \uac01 \uac12\uc758 \uc21c\uc704\ub97c \ubc18\ud658\ud55c\ub2e4.
\nSUM : \ud558\ub098 \uc774\uc0c1\uc758 \ud589\uc73c\ub85c\ubd80\ud130 \ud569\uacc4\ub97c \ubc18\ud658\ud55c\ub2e4.
\nMIN : \ud558\ub098 \uc774\uc0c1\uc758 \ud589\uc73c\ub85c\ubd80\ud130 \ucd5c\uc18c\uac12\uc744 \ubc18\ud658\ud55c\ub2e4.
\nMAX : \ud558\ub098 \uc774\uc0c1\uc758 \ud589\uc73c\ub85c\ubd80\ud130 \ucd5c\ub300\uac12\uc744 \ubc18\ud658\ud55c\ub2e4.
\nCOUNT : \ud558\ub098 \uc774\uc0c1\uc758 \ud589\uc73c\ub85c\ubd80\ud130 \uc804\uccb4 \ud589\uc758 \uc218\ub97c \ubc18\ud658\ud55c\ub2e4.<\/p>\n
\n===============================================================<\/p>\n
3.3 \ubd84\uc11d \ud568\uc218(Analytical Functions)
\n -> Oracle 8.1.6\ubd80\ud130 \uc9c0\uc6d0\ub418\ub294 \ubd84\uc11d\uc6a9 \ud568\uc218\ub294 \uae30\uc874\uc758 SELECT\ub85c \ud574\uacb0\ud558\uae30 \uc5b4\ub824\uc6e0\ub358 \ubb38\uc81c\ub97c \uc27d\uac8c \ucc98\ub9ac\ud558\ub3c4\ub85d
\n\uc9c0\uc6d0\ud55c\ub2e4. \uc608\ub97c \ub4e4\uc5b4 \ub300\ub7c9\uc758 \ub370\uc774\ud130\ub97c \ub300\uc0c1\uc73c\ub85c \ub204\uc801 \ud569\uacc4, \uc21c\uc704, \uc774\ub3d9 \ud3c9\uade0 \ub4e4\uc744 \uad6c\ud558\ub824\uace0 \ud560 \ub54c \uae30\uc874\uc758 SELECT\ub97c \uc774\uc6a9\ud558\uba74
\n\ud504\ub85c\uadf8\ub798\ubc0d\uc774 \ubcf5\uc7a1\ud560 \ubfd0\ub9cc \uc544\ub2c8\ub77c \uc131\ub2a5\ub3c4 \ubcf4\uc7a5\ub418\uc9c0 \uc54a\uc558\ub2e4. \uc774\ub54c \ubd84\uc11d\uc6a9 \ud568\uc218\ub97c \uc0ac\uc6a9\ud558\uac8c \ub418\uba74 \uac04\uacb0\ud55c \ud504\ub85c\uadf8\ub798\ubc0d\uacfc \ud5a5\uc0c1\ub41c \uc131\ub2a5\uc744
\n\ubcf4\uc7a5\ubc1b\uc744 \uc218 \uc788\uac8c \ub41c\ub2e4.<\/p>\n
\ubd84\uc11d\uc6a9 \ud568\uc218\ub294 \ud589\uc758 \uc9d1\ub2e8\uc5d0 \ub300\ud574 \uc5f0\uc0b0\uc774 \uc774\ub8e8\uc5b4\uc9c4\ub2e4\ub294 \uc810\uc5d0\uc11c \uc77c\ubc18 \uadf8\ub8f9 \ud568\uc218\uc640 \uc720\uc0ac\ud558\ub2e4. \uadf8\ub7ec\ub098 \uc77c\ubc18 \uadf8\ub8f9 \ud568\uc218\ub294 \ud558\ub098\uc758 \uc9d1\ub2e8\uc5d0
\n\ub300\ud574 \ud558\ub098\uc758 \uacb0\uacfc\uac00 RETURN \ub418\uc9c0\ub9cc \ubd84\uc11d\uc6a9 \ud568\uc218\ub294 \ud558\ub098\uc758 \uc9d1\ub2e8\uc5d0 \ub300\ud574 \uc5ec\ub7ec \uac00\uc9c0 \uae30\uc900\uc744 \uc801\uc6a9\ud574\uc11c \uc5ec\ub7ec \uac1c\uc758 \uacb0\uacfc\uac00
\nRETURN \ub420 \uc218 \uc788\ub2e4. \uc774\ub54c \ucc98\ub9ac \ub300\uc0c1\uc774 \ub418\ub294 \ud589\uc758 \uc9d1\ub2e8\uc744 \uc708\ub3c4\uc6b0(Window)\ub77c\uace0 \uc9c0\uce6d\ud55c\ub2e4.<\/p>\n
\ubd84\uc11d\uc6a9 \ud568\uc218\ub294 \ud558\ub098\uc758 Query\uc5d0\uc11c ORDER BY \uc808 \uc9c1\uc804\uc5d0 \uc218\ud589\ub41c\ub2e4. \uc989, JOIN, WHERE, GROUP BY,
\nHAVING\uc774 \ucc98\ub9ac\ub41c \uacb0\uacfc\uc5d0 \ub300\ud574 \ubd84\uc11d\uc6a9 \ud568\uc218\uac00 \uc801\uc6a9\ub418\uace0 \ub9c8\uc9c0\ub9c9\uc73c\ub85c ORDER BY\uac00 \uc218\ud589\ub41c\ub2e4\ub294 \uac83\uc774\ub2e4. \ub530\ub77c\uc11c \ubd84\uc11d\uc6a9 \ud568\uc218\ub294
\nSELECT \uc808\uc774\ub098 ORDER BY \uc808\uc5d0\ub9cc \ub098\ud0c0\ub0a0 \uc218 \uc788\ub2e4.<\/p>\n
Analytic-Function(<Argument>. <Argument>, …)
\n OVER(<Query-Partition-Clause> <Order-By-Clause> <Windowing-Clause>)<\/p>\n
(1) FIRST_VALUE \ud568\uc218<\/p>\n
FIRST_VALUE(expr) OVER(analytic_clause)
\n -> \uc815\ub82c\ub41c \uac12\ub4e4 \uc911 \uccab \ubc88\uc9f8 \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.<\/p>\n
ex) \uc804\uccb4 \uc0ac\uc6d0\uc758 \uae09\uc5ec\uc640 \ud568\uaed8 \uac01 \ubd80\uc11c\uc758 \ucd5c\uace0 \uae09\uc5ec\ub97c \ub098\ud0c0\ub0b4\uace0 \ube44\uad50\ud558\uc2dc\uc624.<\/p>\n
SQL> SELECT employee_id, salary,
\n 2 FIRST_VALUE(salary)
\n 3 OVER(PARTITION BY department_id ORDER BY salary DESC)
\n 4 “Highsal_Departmentid”
\n 5 FROM employees;<\/p>\n
-> \uc9c8\uc758\ubb38\uc744 \ubcf4\uba74 Highsal_Departmentid \uce7c\ub7fc\uc740 \ubd80\uc11c\ubcc4(PARTITION BY
\ndepartment_id)\ub85c \uae09\uc5ec\uc5d0 \ub300\ud574 \ub0b4\ub9bc\ucc28\uc21c \uc815\ub82c\ud558\uc5ec(ORDER BY salary DESC) \uc5bb\uc740 \uac12 \uc911 \uccab \ubc88\uc9f8
\n\uac12(FIRST_VALUE), \uc989, \ud574\ub2f9 \ubd80\uc11c\uc758 \ucd5c\uace0 \uae09\uc5ec\ub97c \ubc18\ud658\ud55c\ub2e4.
\n \uc774\uc640\ub294 \ubc18\ub300\ub85c LAST_VALUE() \ud568\uc218\ub294 \uc815\ub82c\ub41c \uac12\ub4e4 \uc911 \ub9c8\uc9c0\ub9c9 \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.<\/p>\n
\n (2) COUNT \ud568\uc218<\/p>\n
COUNT(expr) OVER (analytic_clause)
\n -> \uc870\uac74\uc744 \ub9cc\uc871\ud558\ub294 \ud589\uc758 \uc218\ub97c \ubc18\ud658\ud55c\ub2e4.<\/p>\n
SQL> SELECT employee_id, salary, COUNT(*) OVER(ORDER BY salary) “count”
\n 2 FROM employees
\n 3 WHERE department_id = 50;<\/p>\n
(3) SUM \ud568\uc218<\/p>\n
SUM(expr) OVER(analytic_clause)
\n -> \uc870\uac74\uc744 \ub9cc\uc871\ud558\ub294 \ud589\uc758 \ud569\uc744 \ubc18\ud658\ud55c\ub2e4.<\/p>\n
ex) \ud2b9\uc815 \uac12\uc744 \ub204\uc801\ud558\uc5ec \uacb0\uacfc\ub97c \ubcf4\uc5ec\uc8fc\ub294 \uc0ac\ub840\uc774\ub2e4.<\/p>\n
SQL> SELECT employee_id, last_name, salary,
\n 2 SUM(salary) OVER(ORDER BY employee_id) “acc_salary”
\n 3 FROM employees;<\/p>\n
ex) \uc704 \uacb0\uacfc\uc5d0 \ub354\ud574 \ubd80\uc11c\ubcc4 \ub204\uc801 \uacb0\uacfc\ub97c \ud568\uaed8 \ubcf4\uace0\uc790 \ud55c\ub2e4.<\/p>\n
SQL> SELECT employee_id, last_name, department_name, salary,
\n 2 SUM(salary) OVER(ORDER BY d.department_id, employee_id) “acc_salary”,
\n 3 SUM(salary) OVER(PARTITION BY d.department_id ORDER BY employee_id) “acc_dept_salary”
\n 4 FROM employees e JOIN departments d
\n 5 ON d.department_id = e.department_id;<\/p>\n
-> \uc704 \uc608\uc81c\uc5d0\uc11c\ub294 ‘SUM(SALARY) OVER(PARTITION BY DEPARTMENT_ID ORDER BY
\nEMPLOYEE_ID)’ \ubd80\ubd84\uc774 \ud575\uc2ec\uc801\uc778 \ub0b4\uc6a9\uc774\ub2e4. \uc989, \ubd80\uc11c\ubcc4\ub85c SUM(SALARY)\ub97c \uad6c\ud558\uae30 \uc704\ud574 \uc804\uccb4 \uc9d1\ud569\uc744 \uc5ec\ub7ec
\n\ud30c\ud2f0\uc158\uc73c\ub85c \ub098\ub204\uc5c8\uc73c\uba70 \uc774\ub97c \uc704\ud574 PARTITION BY DEPARTMENT_ID \uc808\uc744 \uc0ac\uc6a9\ud588\ub2e4. \uadf8\ub9ac\uace0 \uc774\ub807\uac8c \ub098\ub204\uc5b4\uc9c4 \uac01
\n\ud30c\ud2f0\uc158\uc5d0 \ub300\ud574 \ud568\uc218\ub97c \uc0ac\uc6a9\ud574\uc11c \ud30c\ud2f0\uc158 \ubcc4\ub85c \ub204\uc801 \ud569\uacc4\ub97c \uad6c\ud55c \uac83\uc774\ub2e4.<\/p>\n
\uc0c1\uc704 n \uac1c\uc758 \ud589\uc744 \uad6c\ud558\uace0\uc790 \ud560\ub54c \ubd84\uc11d\uc6a9 \ud568\uc218\ub97c \uc0ac\uc6a9\ud558\ub294 \ubc29\ubc95\uc774\ub2e4. \uc608\ub97c \ub4e4\uc5b4 ‘\uc6d4\uae09 \uc0c1\uc704 n \uba85’, ‘\uc785\uc0ac\uc77c\uc790 \uc21c\uc73c\ub85c n
\n\uba85’, ‘\ub9e4\ucd9c \uc0c1\uc704 n \uac1c \ud488\ubaa9’\ub4f1\uc744 \uad6c\ud558\uace0\uc790 \ud560 \uacbd\uc6b0 \ubd84\uc11d\uc6a9 \ud568\uc218\ub294 \uc544\uc8fc \uc88b\uc740 \ud574\uacb0\ucc45\uc774 \ub41c\ub2e4. \ud2b9\ud788 \uac19\uc740 \uc21c\uc704\uc758 \ud589\uc774 \uc5ec\ub7ec
\n\uac1c\ub77c\uba74 \uc6d4\uae09 \uc21c\uc704 3\uc704\uae4c\uc9c0 \uad6c\ud560 \uacbd\uc6b0, \uc2e4\uc81c \uacb0\uacfc\ub294 4\uac1c\ub97c \ub118\uc5b4\uc124 \uc218\ub3c4 \uc788\uc5c8\ub358 \uac83\uc774\ub2e4. \uc21c\uc704\uc640 \uad00\ub828\ub41c \ud568\uc218\ub294
\nROW_NUMBER(), RANK(), DENSE_RANK() \ub4f1\uc774 \uc788\ub2e4.<\/p>\n
ex) \ubd80\uc11c\ubcc4\ub85c \uae09\uc5ec\ub97c \ub9ce\uc774 \ubc1b\ub294 \uc21c\uc11c\ub300\ub85c \ub098\ud0c0\ub0b4\uc2dc\uc624.<\/p>\n
SQL> SELECT employee_id, last_name, department_name, salary,
\n 2 ROW_NUMBER() OVER(PARTITION BY d.department_id ORDER BY salary DESC) AS Rank
\n 3 FROM employees e JOIN departments d
\n 4 ON d.department_id = e.department_id;<\/p>\n
\ubd80\uc11c\ubcc4\ub85c \uc0c1\uc704 n \uba85\uc774 \ud544\uc694\ud558\ub2e4\uace0 \ud55c\ub2e4\uba74 \uc6b0\uce21 \ub9c8\uc9c0\ub9c9 \uce7c\ub7fc\uc778 RANK\ub97c \uc774\uc6a9\ud558\uc5ec \uc81c\ud55c\ud558\uba74 \ub41c\ub2e4. \uc608\ub97c \ub4e4\uc5b4 ‘Shipping’ \ubd80\uc11c\ub97c \ub300\uc0c1\uc73c\ub85c \uae09\uc5ec\ub97c \ub9ce\uc774 \ubc1b\ub294 \uc21c\uc73c\ub85c \uc5bb\uace0\uc790 \ud55c\ub2e4\uba74 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n
SQL> SELECT employee_id, last_name, department_name, salary,
\n 2 RANK() OVER(PARTITION BY d.department_id ORDER BY salary DESC) AS Rank
\n 3 FROM employees e JOIN departments d
\n 4 ON d.department_id = e.department_id
\n 5 WHERE department_name = ‘Shipping’;<\/p>\n
* \ub2e4\uc74c\uc740 \uc790\uc8fc \uc0ac\uc6a9\ub418\ub294 \ubd84\uc11d \ud568\uc218\ub97c \uc694\uc57d\ud55c \ud45c\uc774\ub2e4.<\/p>\n
\ud568\uc218\uc774\ub984 : \uc124\uba85
\n FIRST_VALUE(expr) OVER(analytic_clause) : \uc815\ub82c\ub41c \uac12\ub4e4 \uc911 \uccab \ubc88\uc9f8 \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.
\n COUNT(expr) OVER(analytic_clause) : \uc870\uac74\uc744 \ub9cc\uc871\ud558\ub294 \ud589\uc758 \uc218\ub97c \ubc18\ud658\ud55c\ub2e4.
\n SUM(expr) OVER (analytic_clause) : \uc870\uac74\uc744 \ub9cc\uc871\ud558\ub294 \ud589\uc758 \ud569\uc744 \ubc18\ud658\ud55c\ub2e4.
\n LAST_VALUE(expr) OVER(analaytic_clause) : \uc708\ub3c4\uc6b0\uc5d0\uc11c \uc815\ub82c\ub41c \uac12\uc911\uc5d0\uc11c \ub9c8\uc9c0\ub9c9 \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.
\n RANK() OVER(order_by_clause) : \uac12\uc758 \uadf8\ub8f9\uc5d0\uc11c \uac12\uc758 \uc21c\uc704\ub97c \uacc4\uc0b0\ud55c\ub2e4.
\n ROW_NUMBER() OVER([query_partition_clause[ order_by_clase) : ORDER BY \uc808\uc5d0\uc11c \uc9c0\uc815\ub41c \ud589\uc758 \uc21c\uc704 \uc21c\uc11c\ub85c \uac01 \ud589\uc5d0 \uace0\uc720\ud55c \uc21c\uc11c\ub97c 1\ubd80\ud130 \ud560\ub2f9\ud55c\ub2e4.
\n DENSE_RANK() OVER([query_partition_clause] order_by_clause) : \uac12\uc758 \uadf8\ub8f9\uc5d0\uc11c
\n\uac12\uc758 \uc21c\uc704\ub97c \uacc4\uc0b0\ud55c\ub2e4. RANK\uc640\ub294 \ub2ec\ub9ac \uac19\uc740 \uc21c\uc704\uac00 \ub458 \uc774\uc0c1 \uc788\uc5b4\ub3c4 \ub2e4\uc74c \uc21c\uc704\ub294 1\ub9cc \uc99d\uac00\ud558\uc5ec \ubc18\ud658\ud55c\ub2e4.<\/p>\n
\n======================================================<\/p>\n
3.4 \uc815\uaddc \ud45c\ud604\uc2dd(Regular Expression)
\n -> Oracle 10g\uc5d0\uc11c \uc0c8\ub85c \ub3c4\uc785\ub41c \uc815\uaddc \ud45c\ud604\uc2dd(Regular Expression)\uc744 \ud45c\ud604\ud558\uae30 \uc704\ud574 Oracle\uc740 \uae30\uc874 \ud568\uc218\uc758 \uae30\ub2a5\uc744 \ud655\uc7a5\ud55c \uc0c8\ub85c\uc6b4 \ud568\uc218\ub97c \uc81c\uacf5\ud55c\ub2e4.<\/p>\n
(1) REGEXP_LIKE \ud568\uc218<\/p>\n
REGEXP_LIKE(srcstr, pattern [, match_option])
\n srcstr : \uac80\uc0c9\ud558\uace0\uc790 \ud558\ub294 \uac12\uc744 \ub9d0\ud55c\ub2e4.
\n pattern : Regular Expression Operator\ub97c \ud1b5\ud574 \ubb38\uc790\uc5f4\uc5d0\uc11c \ud2b9\uc815 \ubb38\uc790\ub97c \ubcf4\ub2e4 \ub2e4\uc591\ud55c pattern\uc73c\ub85c \uac80\uc0c9\ud558\ub294 \uac83\uc774 \uac00\ub2a5\ud568.
\n match_option : \ucc3e\uace0\uc790 \ud558\ub294 \ubb38\uc790\uc758 \ub300\uc18c\ubb38\uc790 \uad6c\ubd84\uc774 \uae30\ubcf8\uc73c\ub85c \uc124\uc815\ub418\uc5b4 \uc788\ub2e4. \ub300\uc18c\ubb38\uc790\ub97c \uad6c\ubd84\ud560 \ud544\uc694\uac00 \uc5c6\ub2e4\uba74 ‘i’ \uc635\uc158 \uc0ac\uc6a9\uc744 \uc9c0\uc815\ud55c\ub2e4.<\/p>\n
ex) \uc81c\ud488\uc758 \uc774\ub984 \uc911\uc5d0 ‘SS’\ub2e4\uc74c\uc5d0 ‘P’\ub97c \ud3ec\ud568\ud558\uc9c0 \uc54a\uc740 \ubb38\uc790\uc5f4\uc744 \ucc3e\ub294 \uacbd\uc6b0\uc774\ub2e4.<\/p>\n
SQL> SELECT product_name
\n 2 FROM oe.product_information
\n 3 WHERE REGEXP_LIKE(product_name, ‘SS[^P]’);<\/p>\n
PRODUCT_NAME
\n————————————————–
\nSS Stock – 3mm
\nSS Stock – 1mm
\nSpreadsheet – SSS\/S 2.1
\nSpreadsheet – SSS\/V 2.1
\nSpreadsheet – SSS\/CD 2.2B
\nSpreadsheet – SSS\/V 2.0
\nSpreadsheet – SSS\/S 2.2<\/p>\n
7 rows selected.<\/p>\n
[^expression]\uc758 \uc758\ubbf8\ub294 expression\uc774 \ubd80\uc815\ub418\ub294 \uacbd\uc6b0\ub97c \ub098\ud0c0\ub0b8\ub2e4.<\/p>\n
ex)\uc774\ubc88 \uacbd\uc6b0\ub294 \uc81c\ud488 \uc774\ub984\uc5d0 ‘SS’\ub2e4\uc74c ‘P’\ub098 ‘S’\ub97c \ud3ec\ud568\ud558\ub294 \ubb38\uc790\uc5f4\uc744 \ucc3e\ub294 \uacbd\uc6b0\uc774\ub2e4.<\/p>\n
SQL> SELECT product_name
\n 2 FROM oe.product_information
\n 3 WHERE REGEXP_LIKE(product_name, ‘SS[PS]’);<\/p>\n
PRODUCT_NAME
\n————————————————–
\nSpreadsheet – SSP\/V 2.0
\nSpreadsheet – SSS\/S 2.1
\nSpreadsheet – SSS\/V 2.1
\nSpreadsheet – SSS\/CD 2.2B
\nSpreadsheet – SSS\/V 2.0
\nSpreadsheet – SSS\/S 2.2
\nSpreadsheet – SSP\/S 1.5<\/p>\n
7 rows selected.<\/p>\n
[]\uc758 \uc758\ubbf8\ub294 [] \uc548\uc5d0 \uba85\uc2dc\ub418\ub294 \ud558\ub098\uc758 \ubb38\uc790\ub77c\ub3c4 \uc77c\uce58\ud558\ub294 \uac83\uc774 \uc788\uc73c\uba74 \uc774\ub97c \ub098\ud0c0\ub0b4\ubbc0\ub85c, \uc704 \uacbd\uc6b0 ‘SS’\ub85c \uc2dc\uc791\ud558\uba74\uc11c ‘P’\ub098 ‘S’\ub97c \ud3ec\ud568\ud558\ub294 \ubb38\uc790\uc5f4\uc774 \uc788\uc73c\uba74 \uc704\uc758 \uc870\uac74\uc744 \ub9cc\uc871\ud558\uac8c \ub418\ub294 \uac83\uc774\ub2e4.<\/p>\n
\uadf8\ub7fc, REGEXP_LIKE \ud568\uc218\uc758 \uc608\ub97c \uc0b4\ud3b4\ubcf4\uc790.<\/p>\n
ex) \ub9cc\uc57d \uace0\uac1d\uc758 \uac1c\uc778 \uc815\ubcf4\ub97c \uc785\ub825\ud558\ub294 \uacfc\uc815\uc5d0\uc11c \uba85\ud655\ud558\uc9c0 \uc54a\uc740 \uc774\uba54\uc77c(email) \uc8fc\uc18c\ub97c \uc785\ub825\ud55c \uacbd\uc6b0, \uace0\uac1d \ud14c\uc774\ube14\uc758 \uacbd\uc6b0 \uc911\ubcf5\uc744 \ubc29\uc9c0\ud558\ub294 \uc81c\uc57d \uc870\uac74\ub9cc \uc124\uc815\ub418\uc5b4 \uc788\uc5b4 \uc774\ub7ec\ud55c \uacbd\uc6b0\uc5d0\ub3c4 \uc815\uc0c1\uc801\uc778 \uc785\ub825\uc774 \uac00\ub2a5\ud558\ub2e4<\/p>\n
SQL> INSERT INTO customers
\n 2 (customer_id, cust_first_name, cust_last_name, cust_email)
\n 3 VALUES(9999, ‘Christian’, ‘Patel’, ‘for.econet-gmail.com’);<\/p>\n
\uc774 \uacbd\uc6b0 \uc815\ud655\ud55c \uba54\uc77c \uc8fc\uc18c\uc758 \uc785\ub825\uc744 \uc704\ud574 REGEXP_LIKE \uc815\uaddc \ud45c\ud604 \uc2dd\uc744 \uc774\uc6a9\ud558\uc5ec \uc8fc\uc18c\uc5d0 ‘@’\uc774 \ud3ec\ud568\ub418\ub3c4\ub85d \uc124\uc815\ud574\ubcf4\uc790.<\/p>\n
SQL> ALTER TABLE customers
\n 2 ADD CONSTRAINT cust_email_addr
\n 3 CHECK(REGEXP_LIKE(cust_email, ‘@’)) NOVALIDATE;<\/p>\n
Table altered.<\/p>\n
\ud14c\uc774\ube14\uc5d0 \uc81c\uc57d \uc870\uac74\uc774 \ucd94\uac00\ub418\uc5c8\ub2e4\uba74, \ub2e4\uc2dc \uc55e\uc758 \uc2e0\uaddc \uad50\uac1d \uc785\ub825 SQL\ubb38\uc744 \uc2e4\ud589\ud574 \ubcf4\ub77c.<\/p>\n
SQL> INSERT INTO customers(customer_id, cust_first_name, cust_last_name, cust_email)
\n 2 VALUES(9998, ‘Christian’, ‘Patel’, ‘for.econet-gmail.com’);
\nINSERT INTO customers(customer_id, cust_first_name, cust_last_name, cust_email)
\n*
\nERROR at line 1:
\nORA-02290: check constraint (OE.CUST_EMAIL_ADDR) violated<\/p>\n
\uc81c\uc57d \uc870\uac74\uc5d0 \uc704\ubc30\ub418\uc5b4 \uc785\ub825\uc774 \ubd88\uac00\ub2a5\ud568\uc744 \uc54c \uc218 \uc788\ub2e4. \ucc38\uace0\ub85c \uc9c0\uc815\ud574 \uc900 \uc81c\uc57d \uc870\uac74\uc740 \uc544\ub798\uc640 \uac19\uc774 \ud574\uc81c\ud560 \uc218 \uc788\ub2e4.<\/p>\n
SQL> ALTER TABLE customers DROP CONSTRAINT cust_email_addr;<\/p>\n
Table altered.<\/p>\n
\n (2) REGEXP_SUBSTR \ud568\uc218<\/p>\n
REGEXP_SUBSTR(srcstr, pattern [, position [, occurrence [, match_option]]])
\n position : Oracle\uc774 \ubb38\uc790\uc5f4\uc5d0\uc11c \ud2b9\uc815 \ubb38\uc790\ub97c \uc5b4\ub514\uc5d0\uc11c \ucc3e\uc544\uc57c \ud558\ub294\uc9c0 \uc704\uce58\ub97c \ub098\ud0c0\ub0b8\ub2e4. \uae30\ubcf8\uc740 1\ub85c \uc124\uc815\ub418\uc5b4 \uc788\uc73c\ubbc0\ub85c, \ubb38\uc790\uc5f4\uc758 \ucc98\uc74c\ubd80\ud130 \uac80\uc0c9\uc744 \uc2dc\uc791\ud55c\ub2e4.
\n occurrence : \uac80\uc0c9\ud558\uace0\uc790 \ud558\ub294 \ubb38\uc790\uc5f4\uc5d0\uc11c \ud2b9\uc815 \ubb38\uc790\uc758 \ubc1c\uc0dd pattern\uc774\ub2e4. \uae30\ubcf8\uc740 1\ub85c \uc124\uc815\ub418\uc5b4 \uc788\uc73c\uba70, \uc774\ub294 Oracle\uc774 \ubb38\uc790\uc5f4\uc5d0\uc11c \uccab \ubc88\uc9f8 \ubc1c\uc0dd pattern\uc744 \ucc3e\ub294\ub2e4\ub294 \uc758\ubbf8\uc774\ub2e4.<\/p>\n
ex) \uace0\uac1d\uc758 \uc774\uba54\uc77c \uc911 \uc544\uc774\ub514\ub9cc \uc5bb\uace0\uc790 \ud55c\ub2e4. REGEXP_SUBSTR \ud568\uc218\ub294 \uace0\uac1d\uc758 \uc774\uba54\uc77c(cust_email)\uc5d0 \uc800\uc7a5\ub41c \ubb38\uc790\uc5f4\uc5d0\uc11c ‘@’\uc774 \uc544\ub2d0 \ub54c\uae4c\uc9c0 \uc77d\uc5b4 \uace0\uac1d\uc758 \uc544\uc774\ub514\ub9cc\uc744 \uc544\ub798\uc640 \uac19\uc774 \uc5bb\uace0 \uc787\ub2e4.<\/p>\n
SQL> SELECT cust_email, REGEXP_SUBSTR(cust_email, ‘[^@]+’) “REGEXP_SUBSTR”
\n 2 FROM oe.customers
\n 3 WHERE nls_territory = ‘SWITZERLAND’;<\/p>\n
\n (3) REGEXP_REPLACE \ud568\uc218<\/p>\n
REGEXP_REPLACE(srcstr, pattern [,replacestr [,position [,occurrence [, match_option]]]])
\n replacestr : \ub300\uccb4\ud558\uace0\uc790 \ud558\ub294 \ubb38\uc790\uc5f4\uc744 \ub098\ud0c0\ub0b8\ub2e4.<\/p>\n
ex) \uc0ac\uc6a9\uc790\uac00 \ubb38\uc790\uc5f4\uc744 \uc785\ub825 \uacfc\uc815\uc5d0\uc11c \uc885\uc885 \ub744\uc5c8\uae30\uac00 \uc77c\uc815\ud558\uc9c0 \uc54a\uc544 \uacf5\ubc31\uc774 \ub450 \ubb38\uc790\ub098 \uadf8 \uc774\uc0c1\uc758 \ubc1c\uc0dd\ud558\ub294 \uacbd\uc6b0\uac00 \uc788\ub2e4. \uc774 \uacbd\uc6b0 \uacf5\ubc31\uc744 \uc77c\uc815\ud558\uac8c \uc720\uc9c0\ub420 \uc218 \uc788\ub3c4\ub85d \uc124\uc815\ud574 \uc900\ub2e4\uba74 \ubb38\uc790\uc5f4\uc744 \uc77d\uae30\uc5d0 \ud3b8\ub9ac\ud558\ub2e4.<\/p>\n
\n * \ub2e4\uc74c\uc740 \uc815\uaddc \ud45c\ud604\uc2dd\uc5d0 \uc0ac\uc6a9\ub418\ub294 \ud568\uc218\ub97c \uc694\uc57d\ud55c \ud45c\uc774\ub2e4.<\/p>\n
\ud568\uc218 \uc774\ub984 : \uc124\uba85
\n REGEXP_LIKE : LIKE \uc5f0\uc0b0\uc790\uc640 \uc720\uc0ac\ud558\uba70, \ud45c\ud604 \uc2dd \ud328\ud134(regular expression pattern)\uc744 \uc218\ud589\ud558\uc5ec, \uc77c\uce58\ud558\ub294 \uac12\uc744 \ubc18\ud658\ud55c\ub2e4.<\/p>\n
REGEXP_SUBSTR : SUBSTR \ud568\uc218\uc758 \uae30\ub2a5\uc744 \ud655\uc7a5\ud55c\ub2e4. \uc8fc\uc5b4\uc9c4 \ubb38\uc790 \uc5f4\uc744 \ub300\uc0c1\uc73c\ub85c \uc815\uaddc \ud45c\ud604\uc2dd \ud328\ud134\uc744 \uc218\ud589\ud558\uc5ec, \uc77c\uce58\ud558\ub294 \ud558\uc704 \ubb38\uc790\uc5f4\uc744 \ubc18\ud658\ud55c\ub2e4.<\/p>\n
REGEXP_REPLACE : \uc8fc\uc5b4\uc9c4 \ubb38\uc790 \uc5f4\uc744 \ub300\uc0c1\uc73c\ub85c \uc815\uaddc \ud45c\ud604 \uc2dd \ud328\ud134\uc744 \uc870\uc0ac\ud558\uc5ec, \ub2e4\ub978 \ubb38\uc790\ub85c \ub300\uccb4\ud55c\ub2e4.<\/p>\n
REGEXP_INSTR : \uc815\uaddc \ud45c\ud604\uc744 \ub9cc\uc871\ud558\ub294 \ubd80\ubd84\uc744 \uc704\uce58\ub97c \ubc18\ud658\ud55c\ub2e4.<\/p>\n
\uc815\uaddc \ud45c\ud604\uc2dd\uc5d0 \uad00\ud55c \uc0c1\uc138\ud55c \ub0b4\uc6a9\uc740 \ub2e4\uc74c\uc758 \uc0ac\uc774\ud2b8\ub97c \ucc38\uace0\ud55c\ub2e4.
\n Oracle Database Documentation Library:
\n http:\/\/www.oracle.com\/pls\/db10g\/homapage<\/p>\n
C Oracle Regular Expression Support:
\n http:\/\/download-west.oracle.com\/docs\/cd\/B14117_01\/server.101\/b10759\/ap_posix.htm<\/p>\n","protected":false},"excerpt":{"rendered":"
* Single-Row Functions (\ub2e8\uc77c \ud589 \ud568\uc218) -> \ud589\ub9c8\ub2e4 \ud568\uc218\uac00 \uc801\uc6a9\ub418\uc5b4 \uacb0\uacfc\ub97c \ubc18\ud658\ud568. \uc138\ubd80\uc801\uc73c\ub85c \uc544\ub798\uc640 \uac19\uc774 \uad6c\ubd84\ud560 \uc218 \uc788\ub294\ub370, \ub300\uc0c1\uc774 \ub418\ub294 \ub370\uc774\ud130\uc758 \ud0c0\uc785\uc5d0 \ub530\ub77c \uc774\ub97c \ubd84\ub958\ud558\uace0 \uc788\ub2e4. 1) Number Functions (\uc22b\uc790 \ud568\uc218) -> \uc22b\uc790 \ub370\uc774\ud130 \ud0c0\uc785\uc758 \uac12\uc744 \uc870\uc791\ud558\uc5ec \ubcc0\ud658\ub41c \uc22b\uc790 \uac12\uc744 … Continue reading