{"id":4546,"date":"2017-02-21T13:01:24","date_gmt":"2017-02-21T04:01:24","guid":{"rendered":"http:\/\/www.gisdeveloper.co.kr\/?p=4546"},"modified":"2020-05-28T15:54:36","modified_gmt":"2020-05-28T06:54:36","slug":"postgresql%ec%9d%98-stored-procedure-%ed%8a%9c%ed%86%a0%eb%a6%ac%ec%96%bc-18-%ec%86%8c%ea%b0%9c-%eb%b0%8f-%ec%b2%ab%eb%b2%88%ec%a7%b8-%ec%82%ac%ec%9a%a9%ec%9e%90-%ec%a0%95%ec%9d%98-%ed%95%a8","status":"publish","type":"post","link":"http:\/\/www.gisdeveloper.co.kr\/?p=4546","title":{"rendered":"PostgreSQL\uc758 PL\/pgSQL \ud29c\ud1a0\ub9ac\uc5bc &#8211; 1 : \uc18c\uac1c \ubc0f \uccab\ubc88\uc9f8 \uc0ac\uc6a9\uc790 \uc815\uc758 \ud568\uc218"},"content":{"rendered":"<p>\uc548\ub155\ud558\uc138\uc694, GIS Developer \uae40\ud615\uc900\uc785\ub2c8\ub2e4. GIS \ud504\ub85c\uc81d\ud2b8\ub97c \uc9c4\ud589\ud558\uba74\uc11c PostgreSQL\uc744 \ub9ce\uc774 \uc0ac\uc6a9\ud558\uac8c \ub418\ub294\ub370\uc694. \ud504\ub85c\uc81d\ud2b8\uc5d0\uc11c PostgreSQL\ub97c \uc0ac\uc6a9\ud558\uba74\uc11c \uc810\uc9c4\uc801\uc73c\ub85c \ub354 \ub098\uc740 \uac1c\ubc1c \ubc29\ubc95\uc744 \ucc3e\uac8c\ub418\uc5c8\uace0, \ub354 \ub098\uc740 \uac1c\ubc1c \uc0dd\uc0b0\uc131\uacfc \uac1c\ubc1c\ud558\uace0\uc790 \ud558\ub294 \uc2dc\uc2a4\ud15c\uc758 \uc548\uc815\uc131\uacfc \ud37c\ud3ec\uba3c\uc2a4\ub97c \uc704\ud574 Trigger\uc640 Stored Procedure\ub97c \ub3c4\uc785\ud558\uac8c \ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/p>\n<p>\uc774 \uae00\uc740 Stored Procedure\uc5d0 \ub300\ud55c \uac15\uc88c \ud615\uc2dd\uc73c\ub85c \uc815\ub9ac\ud558\ub294 \uae00\uc785\ub2c8\ub2e4. PostgreSQL\ub294 \ud504\ub85c\uadf8\ub798\ubc0d \uc5b8\uc5b4\ub97c \uc774\uc6a9\ud574 \ud568\uc218\ub97c \uc9c1\uc811 \ub9cc\ub4e4\uc5b4 \uc2e4\ud589\ud558\uae30 \uc704\ud574 PL\/pgSQL\uc774\ub77c\ub294 \uc5b8\uc5b4\ub97c \uc81c\uacf5\ud569\ub2c8\ub2e4. \uc774 PL\/pgSQL\ub97c \ud65c\uc6a9\ud55c\ub2e4\ub294 \uac83\uc774 \ubc14\ub85c Stored Procedure\ub97c \uc0ac\uc6a9\ud55c\ub2e4\ub294 \uac83\uc774\uace0, \uc774\ub97c \uc774\uc6a9\ud574 Trigger\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub294 \ubc1c\ud310\uc774 \ub429\ub2c8\ub2e4.<\/p>\n<p>PL\/pgSQL\uc740 PostgreSQL\uc744 \ubcf4\ub2e4 \ud3b8\ub9ac\ud558\uace0 \ube60\ub978 \uc751\ub2f5\uc131\uc744 \uc81c\uacf5\ubc1b\uae30 \uc704\ud574 \uc0ac\uc6a9\ud560 \uc218 \uc788\uc744 \ubfd0\ub9cc \uc544\ub2c8\ub77c \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc790\uccb4\uc758 \uae30\ub2a5\uc744 \ud655\uc7a5\ud560 \uc218 \uc788\ub294 \uae30\ubc18\uc744 \uc81c\uacf5\ud569\ub2c8\ub2e4. PostgreSQL\uc758 Stored Procedure \uac1c\ubc1c\uc744 \uc704\ud55c \uc5b8\uc5b4\uac00 \ube44\ub2e8 PL\/pgSQL\ub9cc \uac00\ub2a5\ud55c \uac83\uc740 \uc544\ub2cc\ub370\uc694, \ud2b9\ud788 C\uc5b8\uc5b4\ub97c \ud1b5\ud574\uc11c\ub3c4 \uac1c\ubc1c\uc774 \uac00\ub2a5\ud558\uc9c0\ub9cc, \uc774 \uae00\uc5d0\uc11c\ub294 PL\/pgSQL\uc5d0\ub9cc \uc9d1\uc911\ud558\ub3c4\ub85d \ud558\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n<p>\uba3c\uc800 PostgreSQL\uc758 Stored Procedure\ub97c \uc0ac\uc6a9\ud558\uba74 \uc5bb\uc744 \uc218 \uc788\ub294 \uc7a5\uc810\uc744 \uc815\ub9ac\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n<ul>\n<li>\uc5ec\ub7ec\uac1c\uc758 SELECT\ub098 UPDATE, INSERT \ub4f1\uacfc \uac19\uc740 Query \ubb38\uc744 Stored Procedure \ubb38\uc744 \ud1b5\ud574 \ud558\ub098\ub85c \ubb36\uc5b4 \uc2e4\ud589\ud568\uc73c\ub85c\uc368 \uc11c\ubc84\uc640 \ud074\ub77c\uc774\uc5b8\ud2b8 \uac04\uc758 Round Trip\uc758 \uac1c\uc218\ub97c \uc904\uc5ec \uc90d\ub2c8\ub2e4. \uc774\ub7ec\ud55c Round Trip\uc744 \uc904\uc774\uba74 \ubd88\ud544\uc694\ud55c \ub124\ud2b8\uc6cc\ud06c \ud1b5\uc2e0 \ud69f\uc218\ub97c \uc904\uc5ec \ub354 \ube60\ub974\uac8c DBMS \uc5f0\uc0b0 \uacb0\uacfc\ub97c \uc5bb\uc744 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/li>\n<li>SELECT\ub098 UPDATE, INSERT, DELETE \ubb38 \ub4f1\uc744 Java\uc758 JDBC \ub4f1\uc744 \uc774\uc6a9\ud574 \uc2e4\ud589\ud558\uac8c \ub418\uba74, Query \ubb38\uc744 \ud574\uc11d\ud558\uace0 \uc2e4\ud589\ud558\uae30\uc5d0 \uc55e\uc11c \ub9ce\uc740 \uc900\ube44\uac00 \ud544\uc694\ud55c\ub370\uc694. Stored Procedure\ub294 \uc774\ubbf8 \ucef4\ud30c\uc77c\ub418\uc5b4 DBMS \ub2e8\uc5d0\uc11c \ubc14\ub85c \uc2e4\ud589\ud560 \uc218 \uc788\ub3c4\ub85d \uc900\ube44\ub418\uc5b4 \uc788\uc5b4 \uadf8 \uc2e4\ud589 \uc18d\ub3c4\uac00 \ub9e4\uc6b0 \ube60\ub974\uba70, \ud568\uc218 \ud638\ucd9c \uc2dc \uc778\uc790\ub9cc\uc744 \ubcc0\uacbd\ud558\uc5ec \ube60\ub974\uace0 \uc27d\uac8c \uc7ac\ud65c\uc6a9\uc774 \uac00\ub2a5\ud569\ub2c8\ub2e4.<\/li>\n<\/ul>\n<p>PostgreSQL\uc758 \uc7a5\uc810\uc774 \uc788\ub2e4\uba74, \ub2e8\uc810\ub3c4 \uc788\uaca0\uc9c0\uc694. \ub2e8\uc810\uc744 \uc5b8\uae09\ud574 \ubcf8\ub2e4\uba74 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n<ul>\n<li>Stored Procedure\ub97c C\ub098 PL\/pgSQL\uacfc \uac19\uc740 \ud504\ub85c\uadf8\ub798\ubc0d \uc5b8\uc5b4\ub85c \uac1c\ubc1c\ud574\uc57c \ud558\ubbc0\ub85c \ud2b9\ubcc4\ud658 \uc2a4\ud0ac\uc744 \uc694\uad6c\ud558\uace0, \ubb38\uc81c \ubc1c\uc0dd\uc2dc \ub514\ubc84\uae45\uc774 \uc5b4\ub835\uc2b5\ub2c8\ub2e4.<\/li>\n<li>Stored Procedure\ub294 \ud45c\uc900\uc778 SQL\uacfc\ub294 \ub2e4\ub974\uac8c DBMS \ub9c8\ub2e4 \ub2e4\ub974\ubbc0\ub85c DBMS\uac00 \ubcc0\uacbd\ub418\uba74 \ud574\ub2f9 DBMS\uc5d0 \ub9de\uac8c Stored Procedure\ub97c \ub2e4\uc2dc \uac1c\ubc1c\ud574\uc57c \ud569\ub2c8\ub2e4.<\/li>\n<\/ul>\n<p>\uc790, \uc774\uc81c PL\/pgSQL\uc744 \uc774\uc6a9\ud574 \uac04\ub2e8\ud55c Stored Procedure\ub97c \ub9cc\ub4e4\uc5b4 \ubcf4\ub3c4\ub85d \ud558\uaca0\uc2b5\ub2c8\ub2e4. \uc0ac\uc6a9\uc790 \uc815\uc758 \ud568\uc218\ub97c \ub9cc\ub4e4\uae30 \uc704\ud574\uc11c\ub294 CREATE FUNCTION \ubb38\uc744 \uc0ac\uc6a9\ud558\ub294\ub370\uc694. \uadf8 \ubb38\ubc95\uc740 \uc544\ub798\uc640 \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\r\nCREATE FUNCTION function_name(param1 type, param2 type)\r\n RETURNS return_type AS\r\nBEGIN\r\n -- code\r\nEND;\r\nLANGUAGE language_name;<\/pre>\n<p>\uc704\uc758 \ucf54\ub4dc\ub97c \uc880\ub354 \uc0c1\uc138\ud788 \uc124\uba85\ud558\uba74 function_name\uc740 \ud568\uc218\uc758 \uc774\ub984\uc774\uace0 param1 type\uacfc param2 type\ub294 \ud568\uc218\uc758 \uc778\uc790\ub97c \uc758\ubbf8\ud569\ub2c8\ub2e4. \uc5ec\uae30\uc11c\ub294 2\uac1c\uc774\uace0 \uc778\uc790\uc758 \uc774\ub984\uc740 param1, param2\uc774\uba70, \uc778\uc790\uba85 \ubc14\ub85c \ub4a4\uc5d0\ub294 \uc778\uc790\uc758 \ud0c0\uc785\uc774 \uc635\ub2c8\ub2e4. return_type\uc5d0\ub294 \uc774 \ud568\uc218\uc758 \ubc18\ud658\uac12\uc758 \ud0c0\uc785\uc744 \uc758\ubbf8\ud569\ub2c8\ub2e4. BEGIN\uacfc END \uc0ac\uc774\uc758 &#8212; code\uc5d0 \uc774 \ud568\uc218\uac00 \uc2e4\ud589\ud560 \ucf54\ub4dc\ub97c \uc785\ub825\ud558\uba74 \ub429\ub2c8\ub2e4. \uadf8\ub9ac\uace0 \ub9c8\uc9c0\ub9c9\uc73c\ub85c language_name\uc5d0\ub294 \uc774 \ud568\uc218\uc5d0 \ub300\ud55c \ud504\ub85c\uadf8\ub798\ubc0d \uc5b8\uc5b4\uac00 \ubb34\uc5c7\uc778\uc9c0\ub97c \ub098\ud0c0\ub0b4\ub294\ub370, \uc5ec\uae30\uc11c \uc6b0\ub9ac\ub294 PL\/pgSQL \uc5b8\uc5b4\ub97c \uc0ac\uc6a9\ud558\ubbc0\ub85c \ud56d\uc0c1 plpgsql\uc774 \ub429\ub2c8\ub2e4.<\/p>\n<p>\uc774\uc81c Stored Procedure\ub97c \ub9cc\ub4e4\uae30 \uc704\ud55c CREATE FUNCTION \uad6c\ubb38\uc5d0 \ub300\ud55c \ubb38\ubc95\uc744 \ubaa8\ub450 \uc0b4\ud3b4\ubcf4\uc558\uc73c\ubbc0\ub85c, \uc2e4\uc81c \uac04\ub2e8\ud55c \ud568\uc218\ub97c \ub9cc\ub4e4\uc5b4 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. <\/p>\n<p>\ub9cc\ub4e4\uace0\uc790 \ud558\ub294 \ud568\uc218\ub294 2\uac1c\uc758 \uc815\uc218\ub97c \uc785\ub825\ubc1b\uc544 \ud569\ud55c \uacb0\uacfc\ub97c \ubc18\ud658\ud574 \uc8fc\ub294 \ud568\uc218\uc785\ub2c8\ub2e4. <\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\r\nCREATE FUNCTION add(a INTEGER, b INTEGER)\r\nRETURNS INTEGER AS\r\n$$ BEGIN\r\n    RETURN a+b;\r\nEND; $$\r\nLANGUAGE PLPGSQL;<\/pre>\n<p>\uc704\uc758 \ucf54\ub4dc\ub97c pgAdmin \ud234\uc5d0\uc11c \uc785\ub825\ud558\uace0 \uc2e4\ud589\ud558\uba74 functions\uc5d0 add\ub77c\ub294 \ud568\uc218\uac00 \uc0c8\ub86d\uac8c \ucd94\uac00\ub41c \uac83\uc744 \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/www.gisdeveloper.co.kr\/wp-content\/uploads\/2017\/02\/plpgsql0.png\" alt=\"\" width=\"1956\" height=\"1125\" class=\"aligncenter size-full wp-image-4557\" \/><\/p>\n<p>\uc704\uc758 \uadf8\ub9bc\uc740 \uc2e4\uc81c \uc704\uc758 add \ud568\uc218\ub97c pgAdmin\uc5d0\uc11c \uc785\ub825\ud558\uace0 \uc2e4\ud589\ud558\uc5ec \uc0c8\ub85c\uc6b4 Function\uc744 \ub4f1\ub85d\ud55c \ubaa8\ub4e0 \ub0b4\uc6a9\uc744 \ub2f4\uace0 \uc788\uc2b5\ub2c8\ub2e4. \uc704\uc758 \uadf8\ub9bc\uc5d0\uc11c (1)\uc740 \ud568\uc218\ub97c \uc0dd\uc131\ud558\uae30 \uc704\ud55c CREATE FUNCTION\uc744 \uc785\ub825\ud558\uace0 (2)\ub294 \ud568\uc218\ub97c \uc0dd\uc131\ud558\ub3c4\ub85d \uc785\ub825\ub41c CREATE FUNCTION \ubb38\uc744 \uc2e4\ud589\ud558\uace0 (3)\uc740 \uadf8 \uc2e4\ud589 \uacb0\uacfc\uc774\uba70 (4)\ub294 \ub4f1\ub85d\uc5d0 \uc131\uacf5\ud588\uc744\ub54c \ucd94\uac00\ub418\ub294 add \ud568\uc218\uc785\ub2c8\ub2e4.<\/p>\n<p>\uc774\ub807\uac8c \ucd94\uac00\ub41c add \ud568\uc218\ub294 \ub2e4\uc74c\ucc98\ub7fc \uc2e4\ud589\ud574 \uadf8 \uacb0\uacfc\ub97c \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/www.gisdeveloper.co.kr\/wp-content\/uploads\/2017\/02\/plpgsql2.png\" alt=\"\" width=\"700\" class=\"aligncenter size-full wp-image-4553\" \/><\/p>\n<p>\uc774\uc0c1\uc73c\ub85c PostgreSQL\uc5d0\uc11c Stored Procedure\uc758 \uc18c\uac1c\uc640 \uc7a5\ub2e8\uc810\uc5d0 \ub300\ud574 \uc54c\uc544\ubd24\uace0, PG\/pgSQL\uc744 \uc774\uc6a9\ud574 \uac04\ub2e8\ud55c \uc0ac\uc6a9\uc790 \uc815\uc758 \ud568\uc218\ub97c \ub9cc\ub4e4\uc5b4 \ubcf4\uc558\uc2b5\ub2c8\ub2e4.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\uc548\ub155\ud558\uc138\uc694, GIS Developer \uae40\ud615\uc900\uc785\ub2c8\ub2e4. GIS \ud504\ub85c\uc81d\ud2b8\ub97c \uc9c4\ud589\ud558\uba74\uc11c PostgreSQL\uc744 \ub9ce\uc774 \uc0ac\uc6a9\ud558\uac8c \ub418\ub294\ub370\uc694. \ud504\ub85c\uc81d\ud2b8\uc5d0\uc11c PostgreSQL\ub97c \uc0ac\uc6a9\ud558\uba74\uc11c \uc810\uc9c4\uc801\uc73c\ub85c \ub354 \ub098\uc740 \uac1c\ubc1c \ubc29\ubc95\uc744 \ucc3e\uac8c\ub418\uc5c8\uace0, \ub354 \ub098\uc740 \uac1c\ubc1c \uc0dd\uc0b0\uc131\uacfc \uac1c\ubc1c\ud558\uace0\uc790 \ud558\ub294 \uc2dc\uc2a4\ud15c\uc758 \uc548\uc815\uc131\uacfc \ud37c\ud3ec\uba3c\uc2a4\ub97c \uc704\ud574 Trigger\uc640 Stored Procedure\ub97c \ub3c4\uc785\ud558\uac8c \ub418\uc5c8\uc2b5\ub2c8\ub2e4. \uc774 \uae00\uc740 Stored Procedure\uc5d0 \ub300\ud55c \uac15\uc88c \ud615\uc2dd\uc73c\ub85c \uc815\ub9ac\ud558\ub294 \uae00\uc785\ub2c8\ub2e4. PostgreSQL\ub294 \ud504\ub85c\uadf8\ub798\ubc0d \uc5b8\uc5b4\ub97c \uc774\uc6a9\ud574 \ud568\uc218\ub97c \uc9c1\uc811 \ub9cc\ub4e4\uc5b4 \uc2e4\ud589\ud558\uae30 \uc704\ud574 PL\/pgSQL\uc774\ub77c\ub294 \uc5b8\uc5b4\ub97c &hellip; <\/p>\n<p class=\"link-more\"><a href=\"http:\/\/www.gisdeveloper.co.kr\/?p=4546\" class=\"more-link\">\ub354 \ubcf4\uae30<span class=\"screen-reader-text\"> &#8220;PostgreSQL\uc758 PL\/pgSQL \ud29c\ud1a0\ub9ac\uc5bc &#8211; 1 : \uc18c\uac1c \ubc0f \uccab\ubc88\uc9f8 \uc0ac\uc6a9\uc790 \uc815\uc758 \ud568\uc218&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[50],"tags":[],"class_list":["post-4546","post","type-post","status-publish","format-standard","hentry","category-gisdev"],"_links":{"self":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/4546","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4546"}],"version-history":[{"count":13,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/4546\/revisions"}],"predecessor-version":[{"id":9702,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/4546\/revisions\/9702"}],"wp:attachment":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4546"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4546"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4546"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}