{"id":4573,"date":"2017-02-22T10:56:33","date_gmt":"2017-02-22T01:56:33","guid":{"rendered":"http:\/\/www.gisdeveloper.co.kr\/?p=4573"},"modified":"2020-05-28T15:53:27","modified_gmt":"2020-05-28T06:53:27","slug":"postgresql%ec%9d%98-plpgsql-%ed%8a%9c%ed%86%a0%eb%a6%ac%ec%96%bc-3-%eb%b3%80%ec%88%98%ec%99%80-%ec%83%81%ec%88%98","status":"publish","type":"post","link":"http:\/\/www.gisdeveloper.co.kr\/?p=4573","title":{"rendered":"PostgreSQL\uc758 PL\/pgSQL \ud29c\ud1a0\ub9ac\uc5bc \u2013 3 : \ubcc0\uc218\uc640 \uc0c1\uc218"},"content":{"rendered":"

\uc548\ub155\ud558\uc138\uc694, GIS Developer \uae40\ud615\uc900\uc785\ub2c8\ub2e4. \uc774\ubc88 \ud3ec\uc2a4\ud2b8\ub294 PL\/pgSQL\uc5d0\uc11c \ubcc0\uc218\uc640 \uc0c1\uc218\ub97c \uc815\uc758\ud558\uace0 \uc0ac\uc6a9\ud558\ub294 \ub0b4\uc6a9\uc5d0 \ub300\ud574 \uc815\ub9ac\ud558\uc5ec \uc124\uba85\ud558\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n

\ubcc0\uc218\ub780 \uc5b4\ub5a4 \uac12\uc744 \uac00\uc9c0\uace0 \uc788\uc73c\uba74\uc11c, \ud544\uc694\ud560 \uacbd\uc6b0 \uadf8 \uac12\uc744 \ubcc0\uacbd\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \ub610\ud55c \ubcc0\uc218\ub294 \uadf8 \uc774\ub984\uacfc \ubcc0\uc218\uac00 \uac00\uc9c0\ub294 \uac12\uc758 \ud0c0\uc785\uc744 \uc9c0\uc815\ud574\uc57c \ud569\ub2c8\ub2e4.<\/p>\n

\ubcc0\uc218\uc758 \uc774\ub984\uc740 \uac1c\ubc1c\uc790\uc5d0\uac8c \uc758\ubbf8\uc788\ub294 \uc774\ub984\uc744 \ubd80\uc5ec\ud558\ub294 \uac83\uc774 \uc88b\uace0, \uac12\uc758 \ud0c0\uc785\uc740 integer, numeric, char, varchar \ub4f1\uacfc \uac19\uc774 \uac01\uac01 \uc815\uc218, \uc2e4\uc218, \ubb38\uc790, \uac00\ubcc0\uae38\uc774 \ubb38\uc790 \ub4f1\uc73c\ub85c \uc9c0\uc815\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

\ubcc0\uc218\ub294 \uc120\uc5b8 \uc2dc\uc5d0 \uae30\ubcf8\uac12\uc744 \uc9c0\uc815\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \ub9cc\uc57d \uc9c0\uc815\ud558\uc9c0 \uc54a\ub294\ub2e4\uba74 \uadf8 \ubcc0\uc218\uc758 \uac12\uc740 NULL \uac12\uc744 \uac16\uac8c \ub429\ub2c8\ub2e4.<\/p>\n

\ub2e4\uc74c\uc740 \uc5ec\ub7ec \uac1c\uc758 \ubcc0\uc218\ub97c \uc120\uc5b8\ud558\ub294 PL\/pgSQL \ucf54\ub4dc\uc785\ub2c8\ub2e4. \ud2b9\ud788 \uc544\ub798\uc758 \ucf54\ub4dc\ub294 \ud568\uc218\ub97c \uc815\uc758\ud558\uc9c0 \uc54a\uace0 \ubc14\ub85c PL\/pgSQL\uc744 \uc2e4\ud589\ud569\ub2c8\ub2e4.<\/p>\n

\r\nDO $$ \r\nDECLARE\r\n   age integer := 40;\r\n   korean_name varchar(10) := '\uae40\ud615\uc900';\r\n   alias_name varchar(50) := 'Dip2K';\r\n   weight numeric(3,1) := 65.5;\r\nBEGIN \r\n   RAISE NOTICE '%\uc758 \ubcc4\uba85\uc740 % \uc774\uace0 \ub098\uc774\ub294 %\uc774\uba70 \ubab8\ubb34\uac8c\ub294 %\uc785\ub2c8\ub2e4.', \r\n       korean_name, alias_name, age, weight;\r\nEND $$;\r\n<\/pre>\n
\uc704\uc758 \ucf54\ub4dc\ub97c \ubcf4\uc2dc\uba74 DECLARE \uad6c\ubb38 \ubc14\ub85c \ub4a4\uc5d0 \ubcc0\uc218\ub97c \uc120\uc5b8\ud558\uace0 \uc788\uc2b5\ub2c8\ub2e4. 4\uac1c\uc758 \ubcc0\uc218\ub97c \uc120\uc5b8\ud558\uace0 \uc788\uace0, \ucd08\uae30\uac12 40\uc778 \uc815\uc218\ud615 \ubcc0\uc218 age\uc640 ‘\uae40\ud615\uc900’ \uac12\uc744 \uac00\uc9c0\ub294 \uac00\ubcc0\uae38\uc774 10\uc778 \ubb38\uc790\ud615 \ubcc0\uc218 korean_name \uadf8\ub9ac\uace0 65.5 \uac12\uc744 \uac00\uc9c0\ub294 \uc2e4\uc218\ud615 \ubcc0\uc218 weight\ub97c \uac16\uc2b5\ub2c8\ub2e4. \uc2e4\uc218\ud615 \ud0c0\uc785\uc744 \uc9c0\uc815\ud558\uae30 \uc704\ud574 numeric(n,m)\uc73c\ub85c \uc120\uc5b8\ud558\ub294\ub370, n\uc740 \uc804\uccb4 \uc790\ub9ac\uc218\uc774\uace0, m\uc740 \uc18c\uc218\uc810 \uc544\ub798\uc5d0 \ub300\ud55c \uc790\ub9ac\uc218\uc785\ub2c8\ub2e4. 8\ubc88 \uc904\uc758 RAISE NOTICE\ub294 \ud574\ub2f9 \ubcc0\uc218\ub4e4\uc744 \uc6d0\ud558\ub294 \ubb38\uc790\uc5f4\ub85c \ud3ec\ub9f7\uc73c\ub85c \uad6c\uc131\ud55c \uac83\uc744 PL\/pgSQL\uc758 \uc2e4\ud589\ud658\uacbd\uc5d0\uc11c \ud45c\uc2dc\ud558\ub294 \ucf54\ub4dc\uc785\ub2c8\ub2e4. \uc2e4\ud589\ud558\uba74 \uc544\ub798\uc640 \uac19\uc740 \uacb0\uacfc\ub97c \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n
\uc77c\ubc18\uc801\uc778 \uc22b\uc790\ud615\uc774\ub098 \ubb38\uc790\ud615 \uc774\uc678\uc5d0\ub3c4 \ud6e8\uc52c \ub2e4\uc591\ud55c \ub370\uc774\ud130 \ud0c0\uc785\uc774 \uc874\uc7ac\ud569\ub2c8\ub2e4. \uadf8 \uc911\uc5d0 \uc544\ub798\uc758 \ucf54\ub4dc\uc5d0\uc11c \ubcfc \uc218 \uc788\ub294 \uc2dc\uac04(time) \ud0c0\uc785\uc785\ub2c8\ub2e4.<\/p>\n
\r\nDO $$ \r\nDECLARE\r\n   current_time time := now();\r\nBEGIN \r\n   RAISE NOTICE '%', current_time;\r\nEND $$;<\/pre>\n
\uc704 \ucf54\ub4dc\uc758 \uc2e4\ud589 \uacb0\uacfc\ub294 \uc544\ub798\uc640 \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\"\"<\/p>\n
\ubcc0\uc218\uc758 \ub370\uc774\ud130 \ud0c0\uc785\uc744 \uacb0\uc815\ud560 \ub54c, \uae30\uc874\uc5d0 \uc874\uc7ac\ud558\ub294 \ud14c\uc774\ube14\uc758 \ud2b9\uc815 \ud544\ub4dc\uc758 \ub370\uc774\ud130 \ud0c0\uc785\uc744 \uac00\uc838\uc640 \uadf8 \ub370\uc774\ud130 \ud0c0\uc785\uc73c\ub85c \ubcc0\uc218\uc758 \ud0c0\uc785\uc744 \uc9c0\uc815\ud574\uc57c \ud560 \ud544\uc694\uac00 \uc788\uc2b5\ub2c8\ub2e4. \uc608\ub97c \ub4e4\uc5b4 employee\ub77c\ub294 \ub370\uc774\ube14\uc758 salary\ub77c\ub294 \ud544\ub4dc\uc758 \ub370\uc774\ud130 \ud0c0\uc785\uc744 \uac00\uc838\uc640 my_salary\ub77c\ub294 \ubcc0\uc218\ub97c \uc120\uc5b8\ud558\ub294 \ucf54\ub4dc\ub294 \uc544\ub798\uc640 \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n
\r\nmy_salary employee.salary#TYPE := 100000.12;<\/pre>\n
\ub05d\uc73c\ub85c \ubcc0\uc218 \uc120\uc5b8\uc5d0 \ub300\ud574 \ud558\ub098\ub354 \uc124\uba85\ud558\uba74, \ub3d9\uc77c\ud55c \ub300\uc0c1\uc758 \uac12\uc744 \ub2f4\uace0 \uc788\ub294 \ubcc0\uc218\uc5d0 \ub300\ud574 \ub610 \ub2e4\ub978 \uc774\ub984(\ubcc4\uce6d)\uc744 \ubd99\uc77c \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uc544\ub798\uc758 \ucf54\ub4dc\ub294 current_time\uc774\ub77c\ub294 \ubcc0\uc218\ub97c old_time\uc774\ub77c\ub294 \ubcc4\uce6d\uc744 \ubd99\uc774\ub294 \uc608\uc785\ub2c8\ub2e4. \uc774\ub294 \uc5b4\ub5a4 \ubcc0\uc218\uc5d0\uc11c, \uac1c\ubc1c\ud558\uace0\uc790 \ud558\ub294 \uae30\ub2a5\uc5d0 \uc880\ub354 \uc758\ubbf8\uc5d0 \ubd80\ud569\ud558\ub294 \uc774\ub984\uc744 \ubd99\uc774\uae30 \uc704\ud55c \ubaa9\uc801\uc785\ub2c8\ub2e4.<\/p>\n
\r\nDO $$ \r\nDECLARE\r\n   current_time time := now();\r\n   old_time ALIAS FOR current_time;\r\nBEGIN \r\n   RAISE NOTICE '%', old_time;\r\nEND $$;<\/pre>\n
\uc774\uc81c \uc0c1\uc218(constant)\uc5d0 \ub300\ud574 \uc124\uba85\ud558\uaca0\uc2b5\ub2c8\ub2e4. \uc0c1\uc218\ub294 \ubcc0\uc218\uc640 \ub2e4\ub974\uac8c \ud55c\ubc88 \uac12\uc774 \uc815\ud574\uc9c0\uba74 \ubcc0\uacbd\ud560 \uc218 \uc5c6\ub2e4\ub294 \ucc28\uc774\uac00 \uc788\uc2b5\ub2c8\ub2e4. \uc0c1\uc218\ub97c \uc120\uc5b8\ud558\ub294 \ubc29\uc2dd\uc740 \ubcc0\uc218\uc758 \uc120\uc5b8\ucc98\ub7fc DECLARE \uad6c\ubb38 \ub4a4\uc5d0\uc11c \uc774\ub8e8\uc5b4\uc9c0\uba70, \uc0c1\uc218 \uc774\ub984\uacfc \ud0c0\uc785 \uc0ac\uc774\uc5d0 CONSTANT\ub77c\uace0 \uc9c0\uc815\ud558\uba74 \ub429\ub2c8\ub2e4. \uc544\ub798\uc758 \uc608\ub97c \ubcf4\uba74 \uc27d\uac8c \uc54c \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
\r\nDO $$ \r\nDECLARE\r\n   vat CONSTANT numeric := 0.1;\r\n   cost numeric := 100000;\r\n   real_cost numeric;\r\nBEGIN \r\n   real_cost = cost + cost * vat;\r\n   RAISE NOTICE '\ubd80\uac00\uc138\ub97c \uc801\uc6a9\ud55c \uc2e4\uc81c \uac00\uaca9\uc740 % \uc785\ub2c8\ub2e4', real_cost;\r\nEND $$;<\/pre>\n
\uc704\uc758 3\ubc88 \ucf54\ub4dc\uac00 vat\ub77c\ub294 \uc2e4\uc218\ud615 \uc0c1\uc218\ub97c \uc120\uc5b8\ud558\ub294 \ucf54\ub4dc\uc785\ub2c8\ub2e4.<\/p>\n
\uc774\uc0c1\uc73c\ub85c PL\/pgSQL\uc5d0\uc11c \ubcc0\uc218\uc640 \uc0c1\uc218\ub97c \uc120\uc5b8\ud558\ub294 \ub0b4\uc6a9\uc5d0 \ub300\ud574 \uc0b4\ud3b4 \ubcf4\uc558\uc2b5\ub2c8\ub2e4.<\/p>\n","protected":false},"excerpt":{"rendered":"
\uc548\ub155\ud558\uc138\uc694, GIS Developer \uae40\ud615\uc900\uc785\ub2c8\ub2e4. \uc774\ubc88 \ud3ec\uc2a4\ud2b8\ub294 PL\/pgSQL\uc5d0\uc11c \ubcc0\uc218\uc640 \uc0c1\uc218\ub97c \uc815\uc758\ud558\uace0 \uc0ac\uc6a9\ud558\ub294 \ub0b4\uc6a9\uc5d0 \ub300\ud574 \uc815\ub9ac\ud558\uc5ec \uc124\uba85\ud558\uaca0\uc2b5\ub2c8\ub2e4. \ubcc0\uc218\ub780 \uc5b4\ub5a4 \uac12\uc744 \uac00\uc9c0\uace0 \uc788\uc73c\uba74\uc11c, \ud544\uc694\ud560 \uacbd\uc6b0 \uadf8 \uac12\uc744 \ubcc0\uacbd\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \ub610\ud55c \ubcc0\uc218\ub294 \uadf8 \uc774\ub984\uacfc \ubcc0\uc218\uac00 \uac00\uc9c0\ub294 \uac12\uc758 \ud0c0\uc785\uc744 \uc9c0\uc815\ud574\uc57c \ud569\ub2c8\ub2e4. \ubcc0\uc218\uc758 \uc774\ub984\uc740 \uac1c\ubc1c\uc790\uc5d0\uac8c \uc758\ubbf8\uc788\ub294 \uc774\ub984\uc744 \ubd80\uc5ec\ud558\ub294 \uac83\uc774 \uc88b\uace0, \uac12\uc758 \ud0c0\uc785\uc740 integer, numeric, char, varchar \ub4f1\uacfc \uac19\uc774 \uac01\uac01 \uc815\uc218, … <\/p>\n
\ub354 \ubcf4\uae30 “PostgreSQL\uc758 PL\/pgSQL \ud29c\ud1a0\ub9ac\uc5bc \u2013 3 : \ubcc0\uc218\uc640 \uc0c1\uc218”<\/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-4573","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\/4573","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=4573"}],"version-history":[{"count":9,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/4573\/revisions"}],"predecessor-version":[{"id":12663,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/4573\/revisions\/12663"}],"wp:attachment":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4573"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4573"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4573"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}