{"id":1229,"date":"2016-01-03T13:47:18","date_gmt":"2016-01-03T13:47:18","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=1229"},"modified":"2016-01-03T13:47:18","modified_gmt":"2016-01-03T13:47:18","slug":"2-plsql-%eb%b3%80%ec%88%98-%ec%84%a0%ec%96%b8","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=1229","title":{"rendered":"2. PL\/SQL \ubcc0\uc218 \uc120\uc5b8"},"content":{"rendered":"

2. PL\/SQL \ubcc0\uc218 \uc120\uc5b8<\/P>
\n

A. \ubcc0\uc218 \uc0ac\uc6a9 \ubc0f \ucc98\ub9ac<\/P>
\n

\u00a7<\/SPAN><\/SPAN>Use<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Data <\/SPAN>\uc758 \uc784\uc2dc \uc800\uc7a5 \uc601\uc5ed<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc800\uc7a5\ub41c \uac12 \uc870\uc791<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc7ac\uc0ac\uc6a9\uc131<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Handling<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc120\uc5b8 \uc139\uc158\uc5d0\uc11c \uc120\uc5b8 \ubc0f \ucd08\uae30\ud654<\/SPAN>(optionally)<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc2e4\ud589 \uc139\uc158\uc5d0\uc11c \uc0ac\uc6a9\ub418\uace0 \uc0c8 \uac12\uc774 \ud560\ub2f9\ub428<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Sub program<\/SPAN>\uc5d0 <\/SPAN>parameter <\/SPAN>\ub85c \uc804\ub2ec\ub428<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Sub program<\/SPAN>\uc758 \ucd9c\ub825 \uacb0\uacfc\ub97c \ubcf4\uc720\ud558\ub294\ub370 \uc0ac\uc6a9\ub428<\/SPAN><\/DIV>
\n

B. \ubcc0\uc218 \uc774\ub984\uc5d0 \ub300\ud55c \uc694\uad6c\uc0ac\ud56d<\/P>
\n

\u00a7<\/SPAN><\/SPAN>\ubb38\uc790\ub85c \uc2dc\uc791\ud55c\ub2e4<\/SPAN>.<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\ubb38\uc790\ub098 \uc22b\uc790\ub97c \ud3ec\ud568\ud560 \uc218 \uc788\ub2e4<\/SPAN>.<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\ud2b9\uc218 \ubb38\uc790<\/SPAN>($, _, # <\/SPAN>\ub4f1<\/SPAN>)<\/SPAN>\ub97c \ud3ec\ud568\ud560 \uc218 \uc788\ub2e4<\/SPAN>.<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>30<\/SPAN>\uc790 \uc774\ud558\uc758 \ubb38\uc790\ub9cc \ud3ec\ud568<\/SPAN> <\/SPAN>\ud55c\ub2e4<\/SPAN>.<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc608\uc57d\uc5b4\ub97c<\/SPAN> \ud3ec\ud568\ud558\uba74 <\/SPAN>\uc548\ub41c\ub2e4.<\/SPAN>  <\/SPAN><\/DIV>
\n

C. \ubcc0\uc218 \uc120\uc5b8 \ubc0f \ucd08\uae30\ud654<\/P>
\n

\u00a7<\/SPAN><\/SPAN>Syntax<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Identifier [CONSTANT] <\/SPAN>datatype<\/SPAN> [NOT NULL]<\/SPAN><\/DIV>
\n

  <\/SPAN>[:= | DEFAULT expr] ;<\/SPAN><\/SPAN><\/P>
\n

\u00a7<\/SPAN><\/SPAN>Examples<\/SPAN><\/DIV>
\n

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

v<\/SPAN>_hiredatae<\/SPAN>      <\/SPAN>DATE;<\/SPAN><\/SPAN><\/P>
\n

v_deptno<\/SPAN>         <\/SPAN>NUMBER(2) NOT NULL := 10;<\/SPAN><\/SPAN><\/P>
\n

v_location<\/SPAN>        <\/SPAN>VARCHAR2(13) := \u2018Atlanta\u2019;<\/SPAN><\/SPAN><\/P>
\n

c_comm<\/SPAN>          <\/SPAN>CONSTANT NUMBER := 1400;<\/SPAN><\/SPAN><\/P>
\n

EX) <\/P>
\n

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

<\/P>
\n

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

v_myname<\/SPAN> varchar2 (20) ;<\/SPAN><\/P>
\n

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

dbms_output.put_line<\/SPAN>( \u2018 My name is : \u2018 || <\/SPAN>v_myname<\/SPAN>);<\/SPAN><\/P>
\n

v_myname<\/SPAN> := \u2018John\u2019;<\/SPAN><\/P>
\n

dbms_output.put_line<\/SPAN>( \u2018 My name is : \u2018 || <\/SPAN>v_myname<\/SPAN>);<\/SPAN><\/P>
\n

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

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

D. \ubb38\uc790\uc5f4 \ub9ac\ud130\ub7f4\uc758 \uad6c\ubd84\uc790<\/P>
\n

 <\/P>
\n

E. \ubcc0\uc218 Type<\/P>
\n

\u00a7<\/SPAN><\/SPAN>PL\/SQL <\/SPAN>\ubcc0\uc218<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Scalar<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Reference<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>LOB<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Composite<\/SPAN><\/DIV>
\n
<\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Non-PL\/SQL <\/SPAN>\ubcc0\uc218<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Bind <\/SPAN>\ubcc0\uc218<\/SPAN><\/DIV>
\n

F. %TYPE<\/P>
\n

\u00a7<\/SPAN><\/SPAN>Database column <\/SPAN>\uc815\uc758<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>table.column<\/SPAN> <\/SPAN>\uc774\ub984\uc774 \uc55e\uc5d0 \ub098\uc634<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>type <\/SPAN>\uc740 \uac19\uc9c0\ub9cc \uc81c\uc57d\uc870\uac74\uc740 \uc801\uc6a9\ub418\uc9c0 \uc54a\uc74c<\/SPAN><\/DIV>
\n
<\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\ub2e4\ub978 \uc120\uc5b8\ub41c \ubcc0\uc218<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc120\uc5b8\ub41c \ubcc0\uc218\uc758 \uc774\ub984\uc774 \uc55e\uc5d0 \ub098\uc634<\/SPAN><\/DIV>
\n
<\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Syntax<\/SPAN><\/DIV>
\n

identifier<\/SPAN>  <\/SPAN><\/SPAN>table.column_name%TYPE<\/SPAN>;<\/SPAN><\/P>
\n

<\/DIV>
\n

EX)<\/P>
\n

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

v_emp_lname<\/SPAN>  <\/SPAN><\/SPAN>employees.last_name%type<\/SPAN> := \u2018King\u2019 ;<\/SPAN><\/P>
\n

v_lname<\/SPAN> <\/SPAN>  <\/SPAN><\/SPAN>v_emp_lname%type<\/SPAN> := \u2018John\u2019 ;<\/SPAN><\/P>
\n

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

dbms_output.put_line<\/SPAN>(\u2018Name is : \u2018 || <\/SPAN>v_emp_lname<\/SPAN>);<\/SPAN><\/P>
\n

dbms_output.put_line<\/SPAN>(\u2018Name is : \u2018 || <\/SPAN>v_lname<\/SPAN>);<\/SPAN><\/P>
\n

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

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

G. Boolean<\/P>
\n

\u00a7<\/SPAN><\/SPAN>TRUE, FALSE, NULL <\/SPAN>\uac12\ub9cc \ud560\ub2f9 \uac00\ub2a5<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc870\uac74\uc2dd\uc5d0<\/SPAN> <\/SPAN>AND, OR, NOT <\/SPAN>\uc0ac\uc6a9\ud558\uc5ec \ubcc0\uc218 \uac12 \ud655\uc778<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc0b0\uc220<\/SPAN>, <\/SPAN>\ubb38\uc790 \ubc0f \ub0a0\uc9dc <\/SPAN>\ud45c\ud604\uc2dd\uc744<\/SPAN> \uac12 \ubc18\ud658\ud558\ub294\ub370 \uc0ac\uc6a9 \uac00\ub2a5<\/SPAN><\/DIV>
\n
<\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>example<\/SPAN><\/DIV>
\n

  <\/SPAN>flag<\/SPAN><\/SPAN>  <\/SPAN>Boolean := false<\/SPAN><\/SPAN>;<\/SPAN><\/P>
\n

  <\/SPAN>flag <\/SPAN><\/SPAN>:= emp_sal1 < emp_sal2;<\/SPAN><\/P>
\n

<\/DIV>
\n
<\/DIV>
\n

H. Composite Data Types<\/P>
\n

<\/P>
\n

 <\/P>
\n

I. Bind \ubcc0\uc218 <\/P>
\n

\u00a7<\/SPAN><\/SPAN>\ud638\uc2a4\ud2b8 \ud658\uacbd\uc5d0\uc11c \uc0dd\uc131<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\ud638\uc2a4\ud2b8 \ubcc0\uc218\ub77c\uace0\ub3c4 \ud55c\ub2e4<\/SPAN>.<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>VARIABLE <\/SPAN>\ud0a4\uc6cc\ub4dc\ub97c \uc0ac\uc6a9\ud558\uc5ec \uc0dd\uc131<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>SQL <\/SPAN>\ubb38\uacfc <\/SPAN>PL\/SQL <\/SPAN>\ube14\ub85d\uc5d0\uc11c \uc0ac\uc6a9<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>PL\/SQL <\/SPAN>\ube14\ub85d\uc774 \uc2e4\ud589 \ub41c \ud6c4\uc5d0\ub3c4 <\/SPAN>Access <\/SPAN>\uac00\ub2a5<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc55e\uc5d0 <\/SPAN>\u2018 : \u2019 <\/SPAN>\ub97c \uc0ac\uc6a9\ud558\uc5ec \ucc38\uc870<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>PRINT <\/SPAN>\uba85\ub839\uc744 \uc0ac\uc6a9\ud558\uc5ec \uac12 \ucd9c\ub825<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>SET AUTOPRINT ON <\/SPAN>\uae30\ub2a5\uc744 \uc0ac\uc6a9\ud574\uc11c \ucd9c\ub825<\/SPAN><\/DIV>
\n

EX)<\/P>
\n

 1)    <\/P>
\n

variable <\/SPAN>b_result<\/SPAN> number<\/SPAN><\/P>
\n

<\/P>
\n

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

select (salary*12) + <\/SPAN>nvl<\/SPAN>(commission_pct,0) into :<\/SPAN>b_result<\/SPAN><\/P>
\n

from employees<\/SPAN><\/P>
\n

where <\/SPAN>employee_id<\/SPAN> = 100;<\/SPAN><\/P>
\n

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

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

<\/P>
\n

print <\/SPAN>b_result<\/SPAN><\/P>
\n

<\/DIV>
\n

2)<\/P>
\n

variable <\/SPAN>b_emp_salary<\/SPAN> number<\/SPAN><\/P>
\n

SET AUTOPRINT ON<\/SPAN><\/P>
\n

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

select salary into :<\/SPAN>b_emp_salary<\/SPAN><\/P>
\n

from employees<\/SPAN><\/P>
\n

where <\/SPAN>employee_id<\/SPAN> = 178;<\/SPAN><\/P>
\n

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

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

select <\/SPAN>last_name<\/SPAN>, salary from employees <\/SPAN><\/P>
\n

where salary =:<\/SPAN>b_emp_salary<\/SPAN>;<\/SPAN><\/P>
\n

<\/DIV>
\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":"

2. PL\/SQL \ubcc0\uc218 \uc120\uc5b8 A. \ubcc0\uc218 \uc0ac\uc6a9 \ubc0f \ucc98\ub9ac \u00a7Use \u00a7Data \uc758 \uc784\uc2dc \uc800\uc7a5 \uc601\uc5ed \u00a7\uc800\uc7a5\ub41c \uac12 \uc870\uc791 \u00a7\uc7ac\uc0ac\uc6a9\uc131 \u00a7Handling \u00a7\uc120\uc5b8 \uc139\uc158\uc5d0\uc11c \uc120\uc5b8 \ubc0f \ucd08\uae30\ud654(optionally) \u00a7\uc2e4\ud589 \uc139\uc158\uc5d0\uc11c \uc0ac\uc6a9\ub418\uace0 \uc0c8 \uac12\uc774 \ud560\ub2f9\ub428 \u00a7Sub program\uc5d0 parameter \ub85c \uc804\ub2ec\ub428 \u00a7Sub program\uc758 \ucd9c\ub825 \uacb0\uacfc\ub97c \ubcf4\uc720\ud558\ub294\ub370 \uc0ac\uc6a9\ub428 B. \ubcc0\uc218 \uc774\ub984\uc5d0 \ub300\ud55c \uc694\uad6c\uc0ac\ud56d \u00a7\ubb38\uc790\ub85c \uc2dc\uc791\ud55c\ub2e4. \u00a7\ubb38\uc790\ub098 \uc22b\uc790\ub97c \ud3ec\ud568\ud560 \uc218 \uc788\ub2e4. \u00a7\ud2b9\uc218 […]<\/p>\n","protected":false},"author":1,"featured_media":1230,"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\/cfile2.uf.210BD541568924ED1BF6BF.png?fit=820%2C541","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1229"}],"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=1229"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1229\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/1230"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1229"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1229"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1229"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}