{"id":1268,"date":"2016-01-03T15:07:41","date_gmt":"2016-01-03T15:07:41","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=1268"},"modified":"2016-01-03T15:07:41","modified_gmt":"2016-01-03T15:07:41","slug":"7-plsql-%eb%aa%85%ec%8b%9c%ec%a0%81-%ec%bb%a4%ec%84%9c-%ec%82%ac%ec%9a%a9","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=1268","title":{"rendered":"7. PL\/SQL \uba85\uc2dc\uc801 \ucee4\uc11c \uc0ac\uc6a9"},"content":{"rendered":"

7. PL\/SQL \uba85\uc2dc\uc801 \ucee4\uc11c \uc0ac\uc6a9
\n

 <\/P>
\n

A. Explicit Cursor<\/P>
\n

 <\/P>
\n

<\/P>
\n

 <\/P>
\n

<\/P>
\n

 <\/P>
\n

<\/P>
\n

B. Cursor \uc120\uc5b8<\/P>
\n

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

  <\/SPAN><\/SPAN>CURSOR <\/SPAN>cursor_name<\/SPAN> IS<\/SPAN><\/P>
\n

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

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

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

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

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

CURSOR <\/SPAN>c_emp_cursor<\/SPAN> IS<\/SPAN><\/P>
\n

SELECT <\/SPAN>employee_id<\/SPAN>, <\/SPAN>last_name<\/SPAN> FROM employees<\/SPAN><\/P>
\n

WHERE <\/SPAN>department_id<\/SPAN> = 30;<\/SPAN><\/P>
\n

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

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

v_locid<\/SPAN> NUMBER := 1700;<\/SPAN><\/P>
\n

CURSOR <\/SPAN>c_dept_cursor<\/SPAN> IS<\/SPAN><\/P>
\n

SELECT * FROM departments<\/SPAN><\/P>
\n

WHERE <\/SPAN>location_id<\/SPAN> = <\/SPAN>v_locid<\/SPAN>;<\/SPAN><\/P>
\n

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

 <\/P>
\n

C. Opening the Cursor<\/P><\/p>\n

 <\/p>\n

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

CURSOR <\/SPAN>c_emp_cursor<\/SPAN> IS<\/SPAN><\/P>
\n

SELECT <\/SPAN>employee_id<\/SPAN>, <\/SPAN>last_name<\/SPAN> FROM employees<\/SPAN><\/P>
\n

WHERE <\/SPAN>department_id<\/SPAN> = 30;<\/SPAN><\/P>
\n

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

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

OPEN <\/SPAN>c_emp_cursor<\/SPAN>;<\/SPAN><\/P>
\n

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

 <\/P>
\n

D. Fetching Data from the Cursor<\/P>
\n

1)<\/P>
\n

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

cursor <\/SPAN>c_emp_cursor<\/SPAN> is<\/SPAN><\/P>
\n

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

where <\/SPAN>department_id<\/SPAN> = 30;<\/SPAN><\/P>
\n

v_empno<\/SPAN> <\/SPAN>employees.employee_id%type<\/SPAN>;<\/SPAN><\/P>
\n

v_lname<\/SPAN> <\/SPAN>employees.last_name%type<\/SPAN>;<\/SPAN><\/P>
\n

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

open <\/SPAN>c_emp_cursor<\/SPAN>;<\/SPAN><\/P>
\n

fetch <\/SPAN>c_emp_cursor<\/SPAN> into <\/SPAN>v_empno<\/SPAN>, <\/SPAN>v_lname<\/SPAN>;<\/SPAN><\/P>
\n

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

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

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

 <\/P>
\n

2)<\/P>
\n

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

cursor <\/SPAN>c_emp_cursor<\/SPAN> is<\/SPAN><\/P>
\n

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

where <\/SPAN>department_id<\/SPAN> = 30;<\/SPAN><\/P>
\n

v_empno<\/SPAN> <\/SPAN>employees.employee_id%type<\/SPAN>;<\/SPAN><\/P>
\n

v_lname<\/SPAN> <\/SPAN>employees.last_name%type<\/SPAN>;<\/SPAN><\/P>
\n

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

open <\/SPAN>c_emp_cursor<\/SPAN>;<\/SPAN><\/P>
\n

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

  <\/SPAN><\/SPAN>fetch <\/SPAN>c_emp_cursor<\/SPAN> into <\/SPAN>v_empno<\/SPAN>, <\/SPAN>v_lname<\/SPAN>;<\/SPAN><\/P>
\n

  <\/SPAN>exit when <\/SPAN><\/SPAN>c_emp_cursor%notfound<\/SPAN>;<\/SPAN><\/P>
\n

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

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

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

 <\/P>
\n

E. Closing the Cursor<\/P>
\n

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

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

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

FETCH <\/SPAN>c_emp_cursor<\/SPAN> INTO <\/SPAN>empno<\/SPAN>, <\/SPAN>lname<\/SPAN>;<\/SPAN><\/P>
\n

EXIT WHEN <\/SPAN>c_emp_cursor%NOTFOUND<\/SPAN>;<\/SPAN><\/P>
\n

DBMS_OUTPUT.PUT_LINE( <\/SPAN>empno<\/SPAN> || \u2018 \u2018 || <\/SPAN>lname<\/SPAN><\/P>
\n

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

CLOSE <\/SPAN>c_emp_cursor<\/SPAN>;<\/SPAN><\/P>
\n

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

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

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

 <\/P>
\n

F. Cursors and Records<\/P>
\n

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

cursor <\/SPAN>c_emp_cursor<\/SPAN> is<\/SPAN><\/P>
\n

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

where <\/SPAN>department_id<\/SPAN> = 30;<\/SPAN><\/P>
\n

v_emp_record<\/SPAN>  <\/SPAN><\/SPAN>c_emp_cursor%rowtype<\/SPAN>;<\/SPAN><\/P>
\n

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

open <\/SPAN>c_emp_cursor<\/SPAN>;<\/SPAN><\/P>
\n

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

fetch <\/SPAN>c_emp_cursor<\/SPAN> into <\/SPAN>v_emp_record<\/SPAN>;<\/SPAN><\/P>
\n

exit when <\/SPAN>c_emp_cursor%notfound<\/SPAN>;<\/SPAN><\/P>
\n

dbms_output.put_line<\/SPAN> ( <\/SPAN>v_emp_record.employee_id<\/SPAN> || \u2018 \u2018 || <\/SPAN>v_emp_record.last_name<\/SPAN>);<\/SPAN><\/P>
\n

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

close <\/SPAN>c_emp_cursor<\/SPAN>;<\/SPAN><\/P>
\n

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

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

 <\/P>
\n

G. Cursor FOR Loops<\/P>
\n

\u00a7<\/SPAN><\/SPAN>FOR loop <\/SPAN>\uc0ac\uc6a9\uc2dc \uba85\uc2dc\uc801 \ucee4\uc11c\ub97c \uac04\ub2e8\ud788 \ucc98\ub9ac \uac00\ub2a5<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>open, fetch, close <\/SPAN>\uc791\uc5c5\uc774 \ubb35\uc2dc\uc801\uc73c\ub85c \uc77c\uc5b4\ub0a8<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Record <\/SPAN>\uac00 \ubb35\uc2dc\uc801\uc73c\ub85c \uc120\uc5b8<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Syntax<\/SPAN><\/DIV>
\n

  <\/SPAN><\/SPAN>FOR <\/SPAN>record_name<\/SPAN> IN <\/SPAN>cursor_name<\/SPAN> LOOP<\/SPAN><\/P>
\n

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

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

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

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

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

ex)<\/P><\/p>\n

 <\/p>\n

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

cursor <\/SPAN>c_emp_cursor<\/SPAN> is<\/SPAN><\/P>
\n

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

where <\/SPAN>department_id<\/SPAN> = 30;<\/SPAN><\/P>
\n

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

for <\/SPAN>emp_record<\/SPAN> in <\/SPAN>c_emp_cursor<\/SPAN> loop<\/SPAN><\/P>
\n

dbms_output.put_line<\/SPAN>( <\/SPAN>emp_record.employee_id<\/SPAN> || \u2018 \u2018|| <\/SPAN>emp_record.last_name<\/SPAN>);<\/SPAN><\/P>
\n

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

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

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

<\/DIV>
\n

 <\/P>
\n

H. Explicit Cursor \uc18d\uc131<\/P>
\n

 <\/P>
\n

<\/P>
\n

[%ISOPEN \uc18d\uc131]<\/P>
\n

\u00a7<\/SPAN><\/SPAN>Fetch <\/SPAN>\ub97c \uc218\ud589\ud558\uae30 \uc804\uc5d0 <\/SPAN>%ISOPEN <\/SPAN>\uc18d\uc131\uc744 \uc0ac\uc6a9\ud558\uc5ec <\/SPAN>Cursor <\/SPAN>\uac00 <\/SPAN>open <\/SPAN>\ub418\uc5b4\uc788\ub294\uc9c0\ub97c \ud14c\uc2a4\ud2b8<\/SPAN><\/DIV>
\n
<\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>Example<\/SPAN><\/DIV>
\n

if not <\/SPAN>c_emp_cursor%isopen<\/SPAN> then<\/SPAN><\/P>
\n

open <\/SPAN>c_emp_cursor<\/SPAN>;<\/SPAN><\/P>
\n

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

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

fetch <\/SPAN>c_emp_cursor<\/SPAN> \u2026<\/SPAN><\/P>
\n

 <\/P>
\n

[%ROWCOUNT \ubc0f %NOTFOUND \uc608\uc81c]<\/P><\/p>\n

 <\/p>\n

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

cursor <\/SPAN>c_emp_cursor<\/SPAN> is<\/SPAN><\/P>
\n

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

v_emp_record<\/SPAN> <\/SPAN>  <\/SPAN><\/SPAN>c_emp_cursor%rowtype<\/SPAN>;<\/SPAN><\/P>
\n

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

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

  <\/SPAN>fetch <\/SPAN><\/SPAN>c_emp_cursor<\/SPAN> into <\/SPAN>v_emp_record<\/SPAN>;<\/SPAN><\/P>
\n

  <\/SPAN>exit when <\/SPAN><\/SPAN>c_emp_cursor%rowcount<\/SPAN> > 10 or <\/SPAN>c_emp_cursor%notfound<\/SPAN>;<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>dbms_output.put_line<\/SPAN>( <\/SPAN>v_emp_record.employee_id<\/SPAN> || \u2018 \u2018 || <\/SPAN>v_emp_record.last_name<\/SPAN>);<\/SPAN><\/P>
\n

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

  <\/SPAN>close <\/SPAN><\/SPAN>c_emp_cursor<\/SPAN>;<\/SPAN><\/P>
\n

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

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

<\/DIV>
\n

[Subquery \ub97c \uc0ac\uc6a9\ud558\ub294 Cursor FOR Loop]<\/P>
\n

 <\/P>
\n

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

for <\/SPAN>emp_record<\/SPAN> in (<\/SPAN>select <\/SPAN>employee_id<\/SPAN>, <\/SPAN>last_name<\/SPAN> <\/SPAN><\/P>
\n

from employees where <\/SPAN>department_id<\/SPAN> = 30<\/SPAN>)<\/SPAN><\/P>
\n

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

dbms_output.put_line<\/SPAN>(<\/SPAN>emp_record.employee_id<\/SPAN> || \u2018 \u2018|| <\/SPAN>emp_record.last_name<\/SPAN>);<\/SPAN><\/P>
\n

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

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

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

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

<\/DIV>
\n

[Parameter \uac00 \ud3ec\ud568\ub41c Cursor]<\/P>
\n

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

CURSOR <\/SPAN>cursor_name<\/SPAN><\/P>
\n

[(<\/SPAN>parameter_name<\/SPAN> <\/SPAN>datatype<\/SPAN>, \u2026)]<\/SPAN><\/P>
\n

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

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

OPEN <\/SPAN>cursor_name<\/SPAN>(<\/SPAN>parameter_value<\/SPAN>, \u2026);<\/SPAN><\/P>
\n

\u00a7<\/SPAN><\/SPAN>Cursor <\/SPAN>\uac00 <\/SPAN>open <\/SPAN>\ub418\uace0 <\/SPAN>query <\/SPAN>\uac00 \uc2e4\ud589\ub418\uba74\uc11c <\/SPAN>parameter <\/SPAN>\uac12\uc774 \uc804\ub2ec<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\ub9e4\ubc88 \ub2e4\ub978 <\/SPAN>\uacb0\uacfc\ud589<\/SPAN> \uc9d1\ud569\uc73c\ub85c \uc5ec\ub7ec \ubc88 \uba85\uc2dc\uc801 <\/SPAN>Cursor <\/SPAN>\ub97c <\/SPAN>open <\/SPAN>\ud55c\ub2e4<\/SPAN>.<\/SPAN><\/DIV>
\n

ex)<\/P>
\n

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

cursor<\/SPAN>  <\/SPAN><\/SPAN>c_emp_cursor<\/SPAN> (<\/SPAN>deptno<\/SPAN> number) is<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN>select <\/SPAN><\/SPAN>employee_id<\/SPAN>, <\/SPAN>last_name<\/SPAN><\/P>
\n

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

  <\/SPAN><\/SPAN>  <\/SPAN>where <\/SPAN><\/SPAN>department_id<\/SPAN> = <\/SPAN>deptno<\/SPAN>;<\/SPAN><\/P>
\n

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

  <\/SPAN>for <\/SPAN><\/SPAN>emp_record<\/SPAN> in <\/SPAN>c_emp_cursor<\/SPAN>(10) loop<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>dbms_output.put_line<\/SPAN>( <\/SPAN>emp_record.employee_id<\/SPAN> || \u2018 \u2018|| <\/SPAN>emp_record.last_name<\/SPAN>);<\/SPAN><\/P>
\n

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

  <\/SPAN>close <\/SPAN><\/SPAN>c_emp_cursor<\/SPAN>;<\/SPAN><\/P>
\n

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

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

<\/DIV>
\n

 <\/P>
\n

[WHERE CURRENT OF \uc808]<\/P><\/p>\n

 <\/p>\n

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

WHERE CURRENT OF cursor ;<\/SPAN><\/P>
\n

\u00a7<\/SPAN><\/SPAN>Cursor <\/SPAN>\ub97c \uc0ac\uc6a9\ud558\uc5ec \ud604\uc7ac \ud589\uc744 \uac31\uc2e0 \ub610\ub294 \uc0ad\uc81c<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uba3c\uc800 <\/SPAN>row<\/SPAN>\uc5d0 <\/SPAN>lock <\/SPAN>\uc744 \uac78 \uc218 \uc788\ub3c4\ub85d <\/SPAN>FOR UPDATE <\/SPAN>\uc808\uc744 \ud3ec\ud568<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>WHERE CURRENT OF <\/SPAN>\uc808\uc744 \uc0ac\uc6a9\ud558\uc5ec \uba85\uc2dc\uc801 <\/SPAN>CURSOR <\/SPAN>\uc5d0\uc11c \ud604\uc7ac \ud589\uc744 \ucc38\uc870\ud569\ub2c8\ub2e4<\/SPAN>.<\/SPAN><\/DIV>
\n

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

set salary = \u2026<\/SPAN><\/P>
\n

where current of<\/SPAN> <\/SPAN>c_emp_cursor<\/SPAN>;<\/SPAN><\/P><\/p>\n","protected":false},"excerpt":{"rendered":"

7. PL\/SQL \uba85\uc2dc\uc801 \ucee4\uc11c \uc0ac\uc6a9   A. Explicit Cursor       B. Cursor \uc120\uc5b8 \u00a7Syntax   CURSOR cursor_name IS   Select_statement;   \u00a7Examples DECLARE CURSOR c_emp_cursor IS SELECT employee_id, last_name FROM employees WHERE department_id = 30; \u2026 DECLARE v_locid NUMBER := 1700; CURSOR c_dept_cursor IS SELECT * FROM departments WHERE location_id = v_locid; \u2026 […]<\/p>\n","protected":false},"author":1,"featured_media":1269,"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.2361C64E568937BB240A69.png?fit=427%2C263","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1268"}],"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=1268"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1268\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/1269"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1268"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1268"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1268"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}