{"id":4601,"date":"2017-02-22T15:30:52","date_gmt":"2017-02-22T06:30:52","guid":{"rendered":"http:\/\/www.gisdeveloper.co.kr\/?p=4601"},"modified":"2020-05-28T15:51:47","modified_gmt":"2020-05-28T06:51:47","slug":"postgresql%ec%9d%98-plpgsql-%ed%8a%9c%ed%86%a0%eb%a6%ac%ec%96%bc-5-case-%ec%a1%b0%ea%b1%b4%eb%ac%b8","status":"publish","type":"post","link":"http:\/\/www.gisdeveloper.co.kr\/?p=4601","title":{"rendered":"PostgreSQL\uc758 PL\/pgSQL \ud29c\ud1a0\ub9ac\uc5bc \u2013 5 : CASE \uc870\uac74\ubb38"},"content":{"rendered":"<p>\uc548\ub155\ud558\uc138\uc694, GIS Developer \uae40\ud615\uc900\uc785\ub2c8\ub2e4. \uc774\ubc88 \ud3ec\uc2a4\ud2b8\uc5d0\uc11c\ub294 \uc870\uac74\ubb38 \uc911 CASE \ubb38\uc5d0 \ub300\ud574 \uc0b4\ud3b4 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. CASE \ubb38\uc758 \uc124\uba85\uc744 \uc704\ud574 \uc2e4\uc81c DBMS\uc5d0 \uc800\uc7a5\ub41c Table\uc744 \ud65c\uc6a9\ud574 \uc124\uba85\ud558\uaca0\uc2b5\ub2c8\ub2e4. \uc0ac\uc6a9\ud560 \ud14c\uc774\ube14\uc740 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/www.gisdeveloper.co.kr\/wp-content\/uploads\/2017\/02\/plpgsql_t5_i1.png\" alt=\"\" width=\"700\" class=\"aligncenter size-full wp-image-4602\" \/><\/p>\n<p>PERSON\uc774\ub77c\ub294 \uc774\ub984\uc758 \ud14c\uc774\ube14\uc5d0 \ucd1d 5\uac1c\uc758 Row\uac00 \uc800\uc7a5\ub418\uc5b4 \uc788\ub294 \uac04\ub2e8\ud55c \ub370\uc774\ud130\uc785\ub2c8\ub2e4.<\/p>\n<p>CASE \ubb38\uc758 \uc124\uba85\uc744 \uc704\ud574 CASE\ub97c \ud65c\uc6a9\ud55c \uc544\ub798\uc640 \uac19\uc740 \ud568\uc218\ub97c \ub9cc\ub4e4\uc5b4 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. <\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\r\nCREATE OR REPLACE FUNCTION get_surname(part_name CHAR(20)) \r\nRETURNS CHAR(2) AS $$\r\nDECLARE \r\n    full_name CHAR(20);\r\n    surname CHAR(1);\r\nBEGIN\r\n    SELECT INTO full_name name\r\n    FROM person\r\n    WHERE name LIKE '%' || part_name || '%';\r\n    \r\n    IF full_name IS NOT NULL THEN\r\n        surname = substr(full_name, 1, 1);\r\n        \r\n        CASE surname\r\n        WHEN '\uae40' THEN\r\n            RETURN '\u91d1\u6c0f';\r\n        WHEN '\uc774' THEN\r\n            RETURN '\uf9e4\u6c0f';\r\n        WHEN '\ubc15' THEN\r\n            RETURN '\u535a\u6c0f';\r\n        WHEN '\ucd5c' THEN\r\n            RETURN '\u5d14\u6c0f';\r\n        ELSE\r\n            RETURN '\ubaa8\ub984'; \r\n        END CASE;\r\n        \r\n    \tRETURN surname;\r\n    ELSE\r\n\t\tRETURN NULL;\r\n\tEND IF;\r\nEND; $$\r\nLANGUAGE plpgsql;<\/pre>\n<p>\uc704\uc758 \ud568\uc218\uc5d0 \ub300\ud55c \ucf54\ub4dc \uc911 14\ubc88\uc5d0 CASE \uc870\uac74\uc808\uc774 \ubcf4\uc785\ub2c8\ub2e4. surname \ubcc0\uc218\uc5d0\ub294 \uc870\ud68c\ub41c \uc774\ub984\uc758 \uc131\uc5d0 \ub300\ud55c 1\uac1c\uc758 \ubb38\uc790\uac00 \ud560\ub2f9\ub429\ub2c8\ub2e4. \uc774 \ubb38\uc790\uc5d0 \ub530\ub77c \ud55c\uc790\ub85c  \uadf8 \uacb0\uacfc\ub97c \ubc18\ud658\ud574 \uc8fc\uac8c \ub418\ub294\ub370\uc694. 14\ubc88\uc758 CASE\uc5d0 \uc870\uac74\uacfc \ube44\uad50\ud560 \ub300\uc0c1 \uac12\uc744 \ub2f4\uace0 \uc788\ub294 \ubcc0\uc218\ub97c \uc9c0\uc815\ud558\uace0, 15\ubc88\uacfc 17\ubc88 \ub4f1\uc758 WHEN\uc5d0 \uc77c\uce58\ud558\ub294 \uac12\uc744 \uc9c0\uc815\ud569\ub2c8\ub2e4. \uc870\uac74\uacfc \uc77c\uce58\ud558\ub294 WHEN\uc744 \ub9cc\ub098\uba74 WHEN\uc758 \ubc11\uc5d0 \uc874\uc7ac\ud558\ub294 \ucf54\ub4dc \ubd80\ubd84\uc774 \uc2e4\ud589\ub429\ub2c8\ub2e4. \ub2e4\uc74c\uc740 \uc2e4\ud589 \uacb0\uacfc\uc785\ub2c8\ub2e4.<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/www.gisdeveloper.co.kr\/wp-content\/uploads\/2017\/02\/plpgsql_t5_i2.png\" alt=\"\" width=\"700\" class=\"aligncenter size-full wp-image-4605\" \/><\/p>\n<p>\uc704\uc758 \uacbd\uc6b0\ucc98\ub7fc \uc815\ud655\ud55c \uac12\uc758 \uc77c\uce58\uc5d0 \ub300\ud55c \uc870\uac74 \ube44\uad50\uac00 \uc544\ub2cc \uc5b4\ub5a4 \uac12\uc758 \ubc94\uc704\uc5d0 \ub300\ud55c \uc870\uac74 \ube44\uad50\uc5d0\ub3c4 CASE\uc640 WHEN\uc744 \uc0ac\uc6a9\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uc774\uc5d0 \ub300\ud55c \ub2e4\uc74c\uc758 \ucf54\ub4dc\ub294 \uc544\ub798\uc640 \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\r\nCREATE OR REPLACE FUNCTION get_age_level(part_name CHAR(20)) \r\nRETURNS CHAR(20) AS $$\r\nDECLARE \r\n    the_age INTEGER;\r\nBEGIN\r\n    SELECT INTO the_age age\r\n    FROM person\r\n    WHERE name LIKE '%' || part_name || '%';\r\n    \r\n    CASE\r\n        WHEN the_age < 10 THEN\r\n            RETURN '\uc5b4\ub9b0\uc544\uc774';\r\n        WHEN the_age >= 10 AND the_age < 20 THEN\r\n            RETURN '10\ub300 \uc0ac\ucd98\uae30';\r\n        WHEN the_age >= 20 AND the_age < 30 THEN\r\n            RETURN '20\ub300 \ucde8\uc900\uc0dd';\r\n        WHEN the_age >= 30 AND the_age < 40 THEN\r\n            RETURN '30\ub300 \uccad\ucd98';        \r\n        ELSE\r\n            RETURN '\ubd88\ub178\uc7a5\uc0dd';\r\n    END CASE;\r\nEND; $$\r\nLANGUAGE plpgsql;<\/pre>\n<p>get_age_level\uc740 \uc778\uc790\ub85c \uc9c0\uc815\ud55c \uc774\ub984\uacfc \uc720\uc0ac\ud55c Row\uc758 age \ud544\ub4dc\uac12\uc758 \ubc94\uc704\uc5d0 \ub530\ub77c \uac01\uae30 \uadf8 \ub098\uc774\ub300\ub97c \ud45c\ud604\ud558\ub294 \ubb38\uc790\uc5f4\uc744 \ubc18\ud658\ud569\ub2c8\ub2e4. \uc774\ub54c \ub098\uc774 \ubc94\uc704\uc5d0 \ub300\ud55c \uc870\uac74\uc744 CASE WHEN \uad6c\ubb38\uc73c\ub85c \ubd84\ub958\ud558\uace0 \uc788\uc2b5\ub2c8\ub2e4. \uc544\ub798\ub294 \uc2e4\ud589 \uacb0\uacfc\uc785\ub2c8\ub2e4.<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/www.gisdeveloper.co.kr\/wp-content\/uploads\/2017\/02\/plpgsql_t5_i3.png\" alt=\"\" width=\"700\" class=\"aligncenter size-full wp-image-4609\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\uc548\ub155\ud558\uc138\uc694, GIS Developer \uae40\ud615\uc900\uc785\ub2c8\ub2e4. \uc774\ubc88 \ud3ec\uc2a4\ud2b8\uc5d0\uc11c\ub294 \uc870\uac74\ubb38 \uc911 CASE \ubb38\uc5d0 \ub300\ud574 \uc0b4\ud3b4 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. CASE \ubb38\uc758 \uc124\uba85\uc744 \uc704\ud574 \uc2e4\uc81c DBMS\uc5d0 \uc800\uc7a5\ub41c Table\uc744 \ud65c\uc6a9\ud574 \uc124\uba85\ud558\uaca0\uc2b5\ub2c8\ub2e4. \uc0ac\uc6a9\ud560 \ud14c\uc774\ube14\uc740 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4. PERSON\uc774\ub77c\ub294 \uc774\ub984\uc758 \ud14c\uc774\ube14\uc5d0 \ucd1d 5\uac1c\uc758 Row\uac00 \uc800\uc7a5\ub418\uc5b4 \uc788\ub294 \uac04\ub2e8\ud55c \ub370\uc774\ud130\uc785\ub2c8\ub2e4. CASE \ubb38\uc758 \uc124\uba85\uc744 \uc704\ud574 CASE\ub97c \ud65c\uc6a9\ud55c \uc544\ub798\uc640 \uac19\uc740 \ud568\uc218\ub97c \ub9cc\ub4e4\uc5b4 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. CREATE OR REPLACE FUNCTION get_surname(part_name CHAR(20)) RETURNS &hellip; <\/p>\n<p class=\"link-more\"><a href=\"http:\/\/www.gisdeveloper.co.kr\/?p=4601\" class=\"more-link\">\ub354 \ubcf4\uae30<span class=\"screen-reader-text\"> &#8220;PostgreSQL\uc758 PL\/pgSQL \ud29c\ud1a0\ub9ac\uc5bc \u2013 5 : CASE \uc870\uac74\ubb38&#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-4601","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\/4601","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=4601"}],"version-history":[{"count":8,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/4601\/revisions"}],"predecessor-version":[{"id":9694,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/4601\/revisions\/9694"}],"wp:attachment":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4601"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4601"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4601"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}