\uc624\ub77c\ud074 \ub370\uc774\ud130\ubca0\uc774\uc2a4\ub294 \ub9ce\uc740 \uae30\uc5c5\uc5d0\uc11c \uc0ac\uc6a9\ub418\ub294 \uac00\uc7a5 \ub110\ub9ac \uc54c\ub824\uc9c4 \uad00\uacc4\ud615 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc2dc\uc2a4\ud15c \uc911 \ud558\ub098\uc785\ub2c8\ub2e4. \uace0\uae09 \uc131\ub2a5\uacfc \ud655\uc7a5\uc131\uc744 \uc81c\uacf5\ud558\ub294 \ub3d9\uc2dc\uc5d0, \ub370\uc774\ud130 \uc870\uc791\uc744 \uc704\ud55c \uac15\ub825\ud55c \uae30\ub2a5\uc744 \uac16\ucd98 PL\/SQL<\/strong> \uc5b8\uc5b4\ub97c \uc0ac\uc6a9\ud558\uc5ec \ubcf5\uc7a1\ud55c \ube44\uc988\ub2c8\uc2a4 \ub85c\uc9c1\uc744 \uc27d\uac8c \uad6c\ud604\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \ud558\uc9c0\ub9cc \ud6a8\uc728\uc801\uc778 \ucffc\ub9ac\uc640 \ud504\ub85c\uc2dc\uc800\ub97c \uc791\uc131\ud558\uc9c0 \uc54a\uc73c\uba74, \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc758 \uc131\ub2a5\uc774 \uc800\ud558\ub420 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \ubcf8 \uae00\uc5d0\uc11c\ub294 \uc624\ub77c\ud074 \ud504\ub85c\uc2dc\uc800\ub97c \ucd5c\uc801\ud654\ud558\ub294 \ub2e4\uc591\ud55c \uae30\ubc95\uc744 \uc18c\uac1c\ud558\uace0, \uc131\ub2a5\uc744 \ud5a5\uc0c1\uc2dc\ud0a4\uae30 \uc704\ud55c \ubc29\ubc95\uc744 \uc790\uc138\ud558\uac8c \uc124\uba85\ud558\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n \uc624\ub77c\ud074 \ud504\ub85c\uc2dc\uc800\ub294 \uc7ac\uc0ac\uc6a9 \uac00\ub2a5\ud55c PL\/SQL \ube14\ub85d\uc73c\ub85c, \ud2b9\uc815 \uc791\uc5c5\uc744 \uc218\ud589\ud560 \uc218 \uc788\ub294 \uc77c\ub828\uc758 SQL \ubb38\uacfc \uc81c\uc5b4 \uad6c\uc870\ub85c \uad6c\uc131\ub429\ub2c8\ub2e4. \ud504\ub85c\uc2dc\uc800\ub294 \ub9e4\uac1c\ubcc0\uc218\ub97c \ud1b5\ud574 \uc678\ubd80 \uac12\uacfc \ub370\uc774\ud130\ub97c \uc8fc\uace0\ubc1b\uc73c\uba70, \ubcf5\uc7a1\ud55c \ube44\uc988\ub2c8\uc2a4 \ub85c\uc9c1\uc774\ub098 \uc77c\uad04 \ucc98\ub9ac \uc791\uc5c5\uc744 \uc790\ub3d9\ud654\ud558\ub294 \ub370 \uc720\uc6a9\ud569\ub2c8\ub2e4.<\/p>\n \uc624\ub77c\ud074 \ud504\ub85c\uc2dc\uc800\uc758 \uae30\ubcf8 \uad6c\uc870\ub294 \uc544\ub798\uc640 \uac19\uc2b5\ub2c8\ub2e4:<\/p>\n \ud504\ub85c\uc2dc\uc800\uc758 \uc131\ub2a5\uc774 \uc800\ud558\ub420 \uc218 \uc788\ub294 \uc5ec\ub7ec \uac00\uc9c0 \uc6d0\uc778\uc774 \uc788\uc2b5\ub2c8\ub2e4. \uadf8 \uc911 \uc77c\ubc18\uc801\uc778 \uc6d0\uc778\uc740 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4:<\/p>\n \ud504\ub85c\uc2dc\uc800\uc758 \uc131\ub2a5\uc744 \ud5a5\uc0c1\uc2dc\ud0a4\uae30 \uc704\ud574 \uac00\uc7a5 \uba3c\uc800 \uace0\ub824\ud574\uc57c \ud560 \uc0ac\ud56d\uc740 SQL \ucffc\ub9ac\uc785\ub2c8\ub2e4. \ucffc\ub9ac\ub294 \uac00\ub2a5\ud55c \uac04\ub2e8\ud558\uac8c \uc720\uc9c0\ud558\uace0, \ubd88\ud544\uc694\ud55c \uc870\uc778\uc744 \ud53c\ud558\uba70, \ud544\uc694\ud55c \ub370\uc774\ud130\ub9cc \uc120\ud0dd\ud558\ub3c4\ub85d \ucd5c\uc801\ud654\ud574\uc57c \ud569\ub2c8\ub2e4.<\/p>\n \uc704\uc640 \uac19\uc740 \ucffc\ub9ac\ub294 \uc131\ub2a5\uc744 \uc800\ud558\uc2dc\ud0ac \uc218 \uc788\uc73c\ubbc0\ub85c, \ud544\uc694\ud55c \uceec\ub7fc\ub9cc \uc120\ud0dd\ud558\ub3c4\ub85d \ubcc0\uacbd\ud574\uc57c \ud569\ub2c8\ub2e4:<\/p>\n \uc778\ub371\uc2a4\ub97c \uc798 \ud65c\uc6a9\ud558\uba74 \ub370\uc774\ud130 \uac80\uc0c9 \uc131\ub2a5\uc774 \ud06c\uac8c \ud5a5\uc0c1\ub429\ub2c8\ub2e4. \ucffc\ub9ac\uc5d0\uc11c WHERE \uc808\uc774\ub098 JOIN \uc870\uac74\uc5d0 \uc0ac\uc6a9\ub418\ub294 \uceec\ub7fc\uc5d0 \uc778\ub371\uc2a4\ub97c \uc124\uc815\ud574\uc57c \ud558\uba70, \uc790\uc8fc \ubcc0\uacbd\ub418\ub294 \ub370\uc774\ud130\uc5d0 \uc778\ub371\uc2a4\ub97c \uc801\uc6a9\ud560 \ub54c\ub294 \uc8fc\uc758\uac00 \ud544\uc694\ud569\ub2c8\ub2e4.<\/p>\n \uc608\ub97c \ub4e4\uc5b4:<\/p>\n PL\/SQL \ucf54\ub4dc\uc5d0\uc11c\ub3c4 \uc131\ub2a5\uc744 \uac1c\uc120\ud560 \uc218 \uc788\ub294 \ubc29\ubc95\uc774 \ub9ce\uc2b5\ub2c8\ub2e4. \ub2e4\uc74c\uc740 \uba87 \uac00\uc9c0 \ud301\uc785\ub2c8\ub2e4:<\/p>\n \ud2b8\ub79c\uc7ad\uc158 \uad00\ub9ac\ub3c4 \uc2a4\ud1a0\uc5b4\ub4dc \ud504\ub85c\uc2dc\uc800\uc758 \uc131\ub2a5\uc5d0 \ud070 \uc601\ud5a5\uc744 \ubbf8\uce60 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uac00\ub2a5\ud55c \uc9e7\uc740 \ud2b8\ub79c\uc7ad\uc158\uc744 \uc720\uc9c0\ud558\uace0, \ud544\uc694\ud560 \ub54c\ub9cc \ucee4\ubc0b\ud558\uac70\ub098 \ub864\ubc31\ud558\ub294 \uac83\uc774 \uc911\uc694\ud569\ub2c8\ub2e4. \ub108\ubb34 \ub9ce\uc740 \ub370\uc774\ud130\ub97c \ud55c \ud2b8\ub79c\uc7ad\uc158\uc73c\ub85c \ubb36\ub294 \uac83\uc744 \ud53c\ud574\uc57c \ud569\ub2c8\ub2e4.<\/p>\n \ud504\ub85c\uc2dc\uc800\uc5d0 \uc804\ub2ec\ub418\ub294 \ud30c\ub77c\ubbf8\ud130\uc758 \uc885\ub958\uc640 \uc218\uac00 \uc131\ub2a5\uc5d0 \uc601\ud5a5\uc744 \ubbf8\uce60 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \ud544\uc694\ud55c \uacbd\uc6b0\uc5d0\ub9cc \ud30c\ub77c\ubbf8\ud130\ub97c \uc804\ub2ec\ud558\uc5ec \ubd88\ud544\uc694\ud55c \uc624\ubc84\ud5e4\ub4dc\ub97c \uc904\uc774\ub294 \uac83\uc774 \uc911\uc694\ud569\ub2c8\ub2e4. \uc608\ub97c \ub4e4\uc5b4:<\/p>\n \uba54\ubaa8\ub9ac \ud560\ub2f9\uc744 \ucd5c\uc801\ud654\ud558\uc5ec \ud504\ub85c\uc2dc\uc800\uc758 \uc131\ub2a5\uc744 \uac1c\uc120\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. PL\/SQL \ubc30\uc5f4\uc744 \ud65c\uc6a9\ud558\uc5ec \ub300\ub7c9 \ub370\uc774\ud130\ub97c \ucc98\ub9ac\ud560 \uacbd\uc6b0, \ubc30\uc5f4 \ud06c\uae30\ub97c \uc870\uc815\ud558\uac70\ub098 BIND \ubcc0\uc218\ub97c \uc0ac\uc6a9\ud558\uba74 \uc88b\uc2b5\ub2c8\ub2e4.<\/p>\n \ucd5c\uc801\ud654 \ud6c4\uc5d0\ub294 \ubc18\ub4dc\uc2dc \uc131\ub2a5 \ud14c\uc2a4\ud2b8\ub97c \uc218\ud589\ud574\uc57c \ud569\ub2c8\ub2e4. SQL \ud2b8\ub808\uc774\uc2a4, AWR(Aggregated Workload Report) \ub9ac\ud3ec\ud2b8 \ub4f1 \ub2e4\uc591\ud55c \ub3c4\uad6c\ub97c \uc0ac\uc6a9\ud558\uc5ec \uc131\ub2a5 \ubcc0\ud654\ub97c \ubaa8\ub2c8\ud130\ub9c1\ud558\uace0, \ubcd1\ubaa9 \ud604\uc0c1\uc774 \ubc1c\uc0dd\ud558\ub294 \ubd80\ubd84\uc744 \ucc3e\uc544\ub0b4\uc57c \ud569\ub2c8\ub2e4.<\/p>\n AWR \ubcf4\uace0\uc11c\ub294 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc131\ub2a5\uc744 \uc885\ud569\uc801\uc73c\ub85c \ubd84\uc11d\ud560 \uc218 \uc788\ub294 \uc720\uc6a9\ud55c \ub3c4\uad6c\uc785\ub2c8\ub2e4. AWR \ub9ac\ud3ec\ud2b8\ub97c \ud1b5\ud574 \uc804\uccb4 \ucffc\ub9ac\uc758 \uc131\ub2a5\uc744 \ubd84\uc11d\ud558\uace0, \uac00\uc7a5 \ub9ce\uc740 \uc2dc\uac04\uc744 \uc18c\uc694\ud558\ub294 \ucffc\ub9ac\ub97c \ucc3e\uc544\ub0bc \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n \uc624\ub77c\ud074 \ud504\ub85c\uc2dc\uc800 \ucd5c\uc801\ud654\ub294 \uc131\ub2a5 \ud5a5\uc0c1\uc5d0 \uc788\uc5b4 \ub9e4\uc6b0 \uc911\uc694\ud55c \uc791\uc5c5\uc785\ub2c8\ub2e4. \ub2e4\uc591\ud55c \ucd5c\uc801\ud654 \uae30\ubc95\uc744 \ud1b5\ud574 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc131\ub2a5\uc744 \ub192\uc77c \uc218 \uc788\uc73c\uba70, \uadf8\uc5d0 \ub530\ub978 \ube44\uc988\ub2c8\uc2a4 \ud6a8\uc728\uc131 \ub610\ud55c \uac1c\uc120\ub429\ub2c8\ub2e4. \uc704\uc5d0\uc11c \uc81c\uc2dc\ud55c \ubc29\ubc95\ub4e4\uc744 \uc801\uc6a9\ud558\uc5ec \uc131\ub2a5\uc744 \uc9c0\uc18d\uc801\uc73c\ub85c \ubaa8\ub2c8\ud130\ub9c1\ud558\uace0 \uac1c\uc120\ud558\ub294 \uacfc\uc815\uc744 \ubc18\ubcf5\ud558\uc5ec\uc57c \ud569\ub2c8\ub2e4.<\/p>\n1. \uc624\ub77c\ud074 \ud504\ub85c\uc2dc\uc800\ub780?<\/h2>\n
1.1 \ud504\ub85c\uc2dc\uc800\uc758 \uae30\ubcf8 \uad6c\uc870<\/h3>\n
CREATE OR REPLACE PROCEDURE \ud504\ub85c\uc2dc\uc800\uba85 (\ub9e4\uac1c\ubcc0\uc218\uba85 \ud0c0\uc785)\n IS\n BEGIN\n -- SQL \ubb38 \ubc0f PL\/SQL \ub85c\uc9c1\n END;<\/code><\/pre>\n2. \ud504\ub85c\uc2dc\uc800 \uc131\ub2a5 \uc800\ud558\uc758 \uc6d0\uc778<\/h2>\n
\n
3. \uc131\ub2a5\uc744 \ud5a5\uc0c1\uc2dc\ud0a4\uae30 \uc704\ud55c \ucd5c\uc801\ud654 \uae30\ubc95<\/h2>\n
3.1 SQL \ucffc\ub9ac \ucd5c\uc801\ud654<\/h3>\n
SELECT * FROM \ud14c\uc774\ube14\uba85 WHERE \uc870\uac74;<\/code><\/pre>\nSELECT \ud544\uc694\ud55c\uceec\ub7fc1, \ud544\uc694\ud55c\uceec\ub7fc2 FROM \ud14c\uc774\ube14\uba85 WHERE \uc870\uac74;<\/code><\/pre>\n3.2 \uc801\uc808\ud55c \uc778\ub371\uc2a4 \ud65c\uc6a9<\/h3>\n
CREATE INDEX \uc778\ub371\uc2a4\uba85 ON \ud14c\uc774\ube14\uba85(\uceec\ub7fc\uba85);<\/code><\/pre>\n3.3 PL\/SQL \uc131\ub2a5 \uac1c\uc120<\/h3>\n
\n
3.4 \ud6a8\uc728\uc801\uc778 \ud2b8\ub79c\uc7ad\uc158 \uad00\ub9ac<\/h3>\n
3.5 \ud30c\ub77c\ubbf8\ud130 \ucd5c\uc801\ud654<\/h3>\n
CREATE OR REPLACE PROCEDURE \ucd5c\uc801\ud654\ub41c\ud504\ub85c\uc2dc\uc800(\uc778\uc218 IN NUMBER) IS\n BEGIN\n -- \ub85c\uc9c1\n END;<\/code><\/pre>\n3.6 \uba54\ubaa8\ub9ac \uad00\ub9ac<\/h3>\n
4. \uc131\ub2a5 \ud14c\uc2a4\ud2b8 \ubc0f \ubaa8\ub2c8\ud130\ub9c1<\/h2>\n
4.1 AWR \ubcf4\uace0\uc11c \uc0ac\uc6a9<\/h3>\n
5. \uacb0\ub860<\/h2>\n