{"id":1232,"date":"2016-01-03T13:54:37","date_gmt":"2016-01-03T13:54:37","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=1232"},"modified":"2016-01-03T13:54:37","modified_gmt":"2016-01-03T13:54:37","slug":"3-plsql-%ec%8b%a4%ed%96%89%eb%ac%b8-%ec%9e%91%ec%84%b1","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=1232","title":{"rendered":"3. PL\/SQL \uc2e4\ud589\ubb38 \uc791\uc131"},"content":{"rendered":"

3. \uc2e4\ud589\ubb38 \uc791\uc131<\/P>
\n

 <\/P>
\n

A. Lexical Unit<\/P>
\n

\u00a7<\/SPAN><\/SPAN>\ubaa8\ub4e0 <\/SPAN>PL\/SQL <\/SPAN>\ube14\ub85d\uc758 \uae30\ubcf8 \uad6c\uc870<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\ubb38\uc790<\/SPAN>, <\/SPAN>\uc22b\uc790<\/SPAN>, <\/SPAN>\ud0ed<\/SPAN>, <\/SPAN>\uacf5\ubc31<\/SPAN>, Return <\/SPAN>\ubc0f \uae30\ud638\ub97c \ud3ec\ud568\ud55c \ubb38\uc790<\/SPAN>, <\/SPAN>\uc2dc\ud000\uc2a4<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\ub2e4\uc74c\uacfc \uac19\uc774 \ubd84\ub958<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc2dd\ubcc4\uc790<\/SPAN> <\/SPAN>: <\/SPAN>v_fname<\/SPAN>, <\/SPAN>c_percent<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uad6c\ubd84\uc790<\/SPAN> <\/SPAN>: ; , + – * \/<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\ub9ac\ud130\ub7f4<\/SPAN> <\/SPAN>: John, 428, True<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc8fc\uc11d <\/SPAN>: –, \/*<\/SPAN>  <\/SPAN>*\/<\/SPAN><\/SPAN><\/DIV>
\n
<\/DIV>
\n

B. \ucf54\ub4dc \uc8fc\uc11d\ucc98\ub9ac<\/P>
\n

<\/P>
\n

— \ub85c \ucc98\ub9ac\ub41c \ubd80\ubd84<\/P>
\n

C. PL\/SQL \uc758 SQL \ud568\uc218<\/P>
\n

\u00a7<\/SPAN><\/SPAN>\ud504\ub85c\uc2dc\uc800\ubb38<\/SPAN> \uc5d0\uc11c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub294 \ud568\uc218<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Single-row functions<\/SPAN><\/DIV>
\n
<\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\ud504\ub85c\uc2dc\uc800\ubb38\uc5d0\uc11c<\/SPAN> \uc0ac\uc6a9\ud560 \uc218 \uc5c6\ub294 \ud568\uc218<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>DECODE<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Group functions<\/SPAN><\/DIV>
\n

EX)<\/P>
\n

set <\/SPAN>serveroutput<\/SPAN> on<\/SPAN><\/P>
\n

declare<\/SPAN><\/P>
\n

v_desc_size<\/SPAN> integer(5);<\/SPAN><\/P>
\n

v_desc<\/SPAN> varchar2(10) := \u2018Hello\u2019;<\/SPAN><\/P>
\n

begin<\/SPAN><\/P>
\n

v_desc_size<\/SPAN> := length(<\/SPAN>v_desc<\/SPAN>);<\/SPAN><\/P>
\n

dbms_output.put_line<\/SPAN>( <\/SPAN>v_desc_size<\/SPAN>);<\/SPAN><\/P>
\n

end;<\/SPAN><\/P>
\n

\/<\/SPAN><\/P>
\n

D. PL\/SQL \ud45c\ud604\uc2dd\uc5d0\uc11c \uc2dc\ud000\uc2a4 \uc0ac\uc6a9<\/P>
\n

\u00a7<\/SPAN><\/SPAN>11g <\/SPAN>\uc774\uc804<\/SPAN><\/DIV>
\n

declare<\/SPAN><\/P>
\n

v_new_id<\/SPAN> number;<\/SPAN><\/P>
\n

begin<\/SPAN><\/P>
\n

select <\/SPAN>my_seq.nextval<\/SPAN> into <\/SPAN>v_new_id<\/SPAN> from dual ;<\/SPAN><\/P>
\n

END<\/SPAN>;<\/SPAN><\/P>
\n

\u00a7<\/SPAN><\/SPAN>11g <\/SPAN>\uc774<\/SPAN>\ud6c4<\/SPAN><\/DIV>
\n

declare<\/SPAN><\/P>
\n

v_new_id<\/SPAN> number;<\/SPAN><\/P>
\n

begin<\/SPAN><\/P>
\n

v_new_id<\/SPAN> := <\/SPAN>my_seq.NEXTVAL<\/SPAN> ;<\/SPAN><\/P>
\n

END;<\/SPAN><\/P>
\n

<\/DIV>
\n

E. Data Type \ubcc0\ud658<\/P>
\n

\u00a7<\/SPAN><\/SPAN>Implicit Conversion<\/SPAN><\/DIV>
\n
<\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Explicit Conversion<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\ud568\uc218 \uc0ac\uc6a9<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>TO_CHAR<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>TO_DATE<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>TO_NUMBER<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>TO_TIMESTAMP<\/SPAN><\/DIV>
\n

EX)<\/P>
\n

1)<\/P>
\n

declare<\/SPAN><\/P>
\n

v_salary<\/SPAN> number(6) :=6000;<\/SPAN><\/P>
\n

v_sal_hike<\/SPAN> varchar2(5) :=\u20181000\u2019;<\/SPAN><\/P>
\n

v_total_salary<\/SPAN> <\/SPAN>v_salary%type<\/SPAN>;<\/SPAN><\/P>
\n

begin<\/SPAN><\/P>
\n

v_total_salary<\/SPAN> := <\/SPAN>v_salary<\/SPAN> + <\/SPAN>v_sal_hike<\/SPAN>;<\/SPAN><\/P>
\n

dbms_output.put_line<\/SPAN>(<\/SPAN>v_total_salary<\/SPAN>);<\/SPAN><\/P>
\n

end;<\/SPAN><\/P>
\n

\/<\/SPAN><\/P>
\n

2)<\/P>
\n

declare<\/SPAN><\/P>
\n

v_date_of_joining<\/SPAN> date := \u201802-Feb-2000\u2019;<\/SPAN><\/P>
\n

v_date2 date:= <\/SPAN>to_date<\/SPAN>(\u2018February 02, 2000\u2019, <\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>\u2018Month DD, YYYY\u2019);<\/SPAN><\/P>
\n

begin<\/SPAN><\/P>
\n

dbms_output.put_line<\/SPAN>(<\/SPAN>v_date_of_joining<\/SPAN>);<\/SPAN><\/P>
\n

dbms_output.put_line<\/SPAN>(v_date2);<\/SPAN><\/P>
\n

end;<\/SPAN><\/P>
\n

\/<\/SPAN><\/P>
\n

F. \uc911\ucca9 \ube14\ub85d<\/P>
\n

<\/P>
\n

\u00a7<\/SPAN><\/SPAN>\uc2e4\ud589 \uc139\uc158<\/SPAN>(BEGIN \u2026 END)<\/SPAN>\uc740 <\/SPAN>\uc911\ucca9 \ube14\ub85d\uc744 \ud3ec\ud568\ud560 \uc218 \uc788\ub2e4<\/SPAN>.<\/SPAN><\/DIV>
\n
<\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc608\uc678\ucc98\ub9ac \uc139\uc158\uc740 <\/SPAN>\uc911<\/SPAN>\ucca9 \ube14\ub85d\uc744<\/SPAN> <\/SPAN><\/SPAN>\ud3ec\ud568\ud560 \uc218 \uc788\ub2e4<\/SPAN>.<\/SPAN><\/DIV>
\n

 <\/P>
\n

<\/SPAN><\/P>
\n

EX)<\/P>
\n

1)<\/P>
\n

declare<\/SPAN><\/P>
\n

v_father_name<\/SPAN> varchar2(20) := \u2018Patrick\u2019;<\/SPAN><\/P>
\n

v_date_birth<\/SPAN> date := \u201920-Apr-1972\u2019;<\/SPAN><\/P>
\n

begin<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>declare<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>v_child_name<\/SPAN> varchar2(20) := \u2018Mike\u2019;<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>v_date_birth<\/SPAN> date := \u201812-Dec-2002\u2019;<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>begin<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>dbms_output.put_line<\/SPAN>( \u2018<\/SPAN>Father\u2019\u2019s<\/SPAN> Name : \u2018 || <\/SPAN>v_father_name<\/SPAN>);<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>dbms_output.put_line<\/SPAN>( <\/SPAN>\u2018Date of Birth : \u2018 <\/SPAN>|| <\/SPAN>v_date_birth<\/SPAN>);<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>dbms_output.put_line<\/SPAN>( <\/SPAN>\u2018<\/SPAN>Child\u2019\u2019s<\/SPAN> Name : <\/SPAN>\u2018 || <\/SPAN>v_child_name<\/SPAN>);<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>end;<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>dbms_output.put_line<\/SPAN>( \u2018Date of Birth : \u2018 || <\/SPAN>v_date_birth<\/SPAN>);<\/SPAN><\/P>
\n

end;<\/SPAN><\/P>
\n

\/<\/SPAN><\/P>
\n

2) \uc911\ucca9 \ube14\ub85d\uc5d0 \uc218\uc2dd\uc790 \uc0ac\uc6a9<\/P>
\n

begin <\/SPAN><<outer>><\/SPAN><\/P>
\n

declare<\/SPAN><\/P>
\n

v_father_name<\/SPAN> varchar2(20) := \u2018Patrick\u2019;<\/SPAN><\/P>
\n

v_date_birth<\/SPAN> date := \u201920-Apr-1972\u2019;<\/SPAN><\/P>
\n

begin<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>declare<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>v_child_name<\/SPAN> varchar2(20) := \u2018Mike\u2019;<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>v_date_birth<\/SPAN> date := \u201812-Dec-2002\u2019;<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>begin<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>dbms_output.put_line<\/SPAN>( \u2018<\/SPAN>Father\u2019\u2019s<\/SPAN> Name : \u2018 || <\/SPAN>v_father_name<\/SPAN>);<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>dbms_output.put_line<\/SPAN>( <\/SPAN>\u2018Date of Birth : \u2018 <\/SPAN>|| <\/SPAN>outer.<\/SPAN>v_date_birth<\/SPAN>);<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>dbms_output.put_line<\/SPAN>( <\/SPAN>\u2018<\/SPAN>Child\u2019\u2019s<\/SPAN> Name : <\/SPAN>\u2018 || <\/SPAN>v_child_name<\/SPAN>);<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>dbms_output.put_line<\/SPAN>( \u2018Date of Birth : \u2018 || <\/SPAN>v_date_birth<\/SPAN>);<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>end;<\/SPAN><\/P>
\n

end;<\/SPAN><\/P>
\n

end <\/SPAN>outer<\/SPAN>;<\/SPAN><\/P>
\n

\/<\/SPAN><\/P>
\n

G. PL\/SQL \uc758 \uc5f0\uc0b0\uc790<\/P>
\n

\u00a7<\/SPAN><\/SPAN>\ub17c\ub9ac\uc801 <\/SPAN>(AND, OR, NOT)<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc0b0\uc220 <\/SPAN>( + – * \/ )<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc5f0\uacb0 <\/SPAN>( || )<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc5f0\uc0b0 \uc21c\uc11c \uc81c\uc5b4\ub97c \uc704\ud55c \uad04\ud638<\/SPAN><\/DIV>
\n
<\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc9c0\uc218 \uc5f0\uc0b0\uc790<\/SPAN>(**)<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>10**3 = 10*10*10<\/SPAN><\/DIV>
\n

 <\/P>
\n

 <\/P>
\n

 <\/P>
\n

 <\/P>
\n

 <\/P>
\n

 <\/P>
\n

 <\/P>
\n

 <\/P>
\n

 <\/P>
\n

 <\/P>
\n

 <\/P>
\n

 <\/P>
\n

 <\/P><\/p>\n","protected":false},"excerpt":{"rendered":"

3. \uc2e4\ud589\ubb38 \uc791\uc131   A. Lexical Unit \u00a7\ubaa8\ub4e0 PL\/SQL \ube14\ub85d\uc758 \uae30\ubcf8 \uad6c\uc870 \u00a7\ubb38\uc790, \uc22b\uc790, \ud0ed, \uacf5\ubc31, Return \ubc0f \uae30\ud638\ub97c \ud3ec\ud568\ud55c \ubb38\uc790, \uc2dc\ud000\uc2a4 \u00a7\ub2e4\uc74c\uacfc \uac19\uc774 \ubd84\ub958 \u00a7\uc2dd\ubcc4\uc790 : v_fname, c_percent \u00a7\uad6c\ubd84\uc790 : ; , + – * \/ \u00a7\ub9ac\ud130\ub7f4 : John, 428, True \u00a7\uc8fc\uc11d : –, \/*  *\/ B. \ucf54\ub4dc \uc8fc\uc11d\ucc98\ub9ac — \ub85c \ucc98\ub9ac\ub41c \ubd80\ubd84 C. […]<\/p>\n","protected":false},"author":1,"featured_media":1233,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[9],"tags":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/1\/cfile7.uf.223FAC3F5689278829FBA4.png?fit=530%2C934","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1232"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1232"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1232\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/1233"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1232"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1232"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1232"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}