{"id":1235,"date":"2016-01-03T14:20:23","date_gmt":"2016-01-03T14:20:23","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=1235"},"modified":"2016-01-03T14:20:23","modified_gmt":"2016-01-03T14:20:23","slug":"4-plsql-%ed%94%84%eb%a1%9c%ea%b7%b8%eb%9e%a8%ec%9d%98-sql%eb%ac%b8","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=1235","title":{"rendered":"4. PL\/SQL \ud504\ub85c\uadf8\ub7a8\uc758 SQL\ubb38"},"content":{"rendered":"

4. PL\/SQL \ud504\ub85c\uadf8\ub7a8\uc758 SQL\ubb38
\n

(\uc624\ub77c\ud074 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc11c\ubc84\uc640 \uc0c1\ud638\uc791\uc6a9)<\/P>
\n

 <\/P>
\n

A. PL\/SQL\uc758 SQL\ubb38<\/P>
\n

\u00a7<\/SPAN><\/SPAN>SELECT<\/SPAN> <\/SPAN>\uba85\ub839\uc744 \uc0ac\uc6a9\ud55c <\/SPAN>DB <\/SPAN>\ud589 \uac80\uc0c9<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>DML <\/SPAN>\uba85\ub839\uc744 \uc0ac\uc6a9\ud55c <\/SPAN>DB <\/SPAN>\ud589 \ubcc0\uacbd<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>COMMIT, ROLLBACK, SAVEPOINT <\/SPAN>\uba85\ub839\uc744 \uc0ac\uc6a9\ud55c <\/SPAN>TX <\/SPAN>\uc81c\uc5b4<\/SPAN><\/DIV>
\n
<\/DIV>
\n

*** END <\/SPAN>\ud0a4\uc6cc\ub4dc\ub294 <\/SPAN>TX<\/SPAN>\uc758 \ub05d\uc774 \uc544\ub2cc<\/SPAN>, PL\/SQL <\/SPAN>\ube14\ub85d\uc758 \ub05d<\/SPAN>! <\/SPAN><\/P>
\n

     <\/SPAN>TX<\/SPAN><\/SPAN>\ub3c4 \ub2e4\uc911 \ube14\ub85d\uc744 \ud655\uc7a5\ud560 \uc218 \uc788\ub2e4<\/SPAN>.<\/SPAN><\/P>
\n

*** PL\/SQL<\/SPAN>\uc740 <\/SPAN>DDL, DCL<\/SPAN>\uc744 <\/SPAN>\u2018<\/SPAN>\uc9c1\uc811<\/SPAN>\u2019 <\/SPAN>\uc9c0\uc6d0\ud558\uc9c0 \uc54a\ub294\ub2e4<\/SPAN>. <\/SPAN><\/P>
\n

 <\/SPAN><\/SPAN>    <\/SPAN>=> \u2018<\/SPAN><\/SPAN>\ub3d9\uc801 <\/SPAN>SQL\u2019<\/SPAN>\uc744 \uc0ac\uc6a9<\/SPAN>, DDL, DCL <\/SPAN>\ubb38 \uc2e4\ud589 \uac00\ub2a5<\/SPAN><\/P>
\n

 <\/P>
\n

B. PL\/SQL\uc758 SELECT \ubb38<\/P>
\n

<\/P>
\n

\u00a7<\/SPAN><\/SPAN>ROW<\/SPAN>\ub97c \ud558\ub098\uc758 \ubcc0\uc218\uc5d0 \uac00\uc9c0\uace0 \uc62c \ub54c<\/SPAN>,<\/SPAN> <\/SPAN>PL\/SQL \u201cRECORD\u201d <\/SPAN>\ubcc0\uc218<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc138\ubbf8\ucf5c\ub860<\/SPAN>( ; )<\/SPAN>\uc73c\ub85c \uac01 <\/SPAN>SQL <\/SPAN>\ubb38 \uc885\ub8cc<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>INTO <\/SPAN>\uc808\ub85c \uac80\uc0c9\ud55c \uac12 \ubcc0\uc218\uc5d0 \uc800\uc7a5<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>INTO <\/SPAN>\uc808 \uc0ac\uc6a9\uc2dc \ud55c \ud589\ub9cc <\/SPAN>FETCH!! (<\/SPAN>\uc774 \uacbd\uc6b0 <\/SPAN>WHILE <\/SPAN>\ud544\uc218<\/SPAN>!)<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>INTO <\/SPAN>\uc808\uc740 \ud544\uc218<\/SPAN>!! <\/SPAN> <\/SPAN><\/SPAN>\uc704\uce58\ub294 <\/SPAN>SELECT<\/SPAN>\uc640 <\/SPAN>FROM <\/SPAN>\uc0ac\uc774<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\ubc18\ud658\ub418\ub294 \ud589\uc774 \uc5c6\uac70\ub098 \uc5ec\ub7ec \uac1c\uc77c \uacbd\uc6b0 \uc624\ub958 \ubc1c\uc0dd<\/SPAN>
-> <\/SPAN><\/SPAN>NO_DATA_FOUND, TOO_MANY_ROWS<\/SPAN>\ub85c \uc608\uc678 \ucc98\ub9ac<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uc5ec\ub7ec \ud589\uc744 \uac80\uc0c9\ud558\uace0 \ub370\uc774\ud130\ub97c \uc870\uc791\ud560 \uacbd\uc6b0<\/SPAN>, <\/SPAN>\uba85\uc2dc\uc801 \ucee4\uc11c \uc0ac\uc6a9<\/SPAN><\/DIV>
\n

ex)<\/P>
\n

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

   <\/SPAN><\/SPAN>v_emp_hiredate<\/SPAN>  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>employees.hire_date%TYPE<\/SPAN>;<\/SPAN><\/P>
\n

   <\/SPAN><\/SPAN>v_emp_salary<\/SPAN>       <\/SPAN><\/SPAN>employees.salary%TYPE<\/SPAN>;<\/SPAN><\/P>
\n

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

  <\/SPAN>SELECT <\/SPAN><\/SPAN>hire_date<\/SPAN>, salary<\/SPAN><\/P>
\n

  <\/SPAN>INTO <\/SPAN><\/SPAN>v_emp_hiredate<\/SPAN>, <\/SPAN>v_emp_salary<\/SPAN><\/P>
\n

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

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

  <\/SPAN>DBMS_OUTPUT.PUT_LINE <\/SPAN><\/SPAN>(\u2018Hire date is : \u2019 ||<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>v_emp_hiredate<\/SPAN>);<\/SPAN><\/P>
\n

  <\/SPAN>DBMS_OUTPUT.PUT_LINE <\/SPAN><\/SPAN>(\u2018Salary is : \u2019 ||<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>v_emp_salary<\/SPAN>);<\/SPAN><\/P>
\n

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

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

C. \uc774\ub984 \uc9c0\uc815 \uaddc\uce59 <\/P>
\n

\u00a7<\/SPAN><\/SPAN>WHERE <\/SPAN>\uc808\uc5d0\uc11c\uc758 \ubaa8\ud638\uc131 \ubc29\uc9c0\ub97c \uc704\ud574 \uc0ac\uc6a9\ud55c\ub2e4<\/SPAN>.<\/SPAN>
[<\/SPAN><\/SPAN>\uc774\ub984\uc758 \ud63c\ub3d9 \uac00\ub2a5\uc131\uc740 <\/SPAN>WHERE<\/SPAN>\uc808\uc5d0\ub9cc \uc788\uc74c<\/SPAN>]<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>DB<\/SPAN>\uc758 <\/SPAN>\uceec\ub7fc\uba85\uc744<\/SPAN> <\/SPAN>\uc2dd\ubcc4\uc790\ub85c<\/SPAN> \uc0ac\uc6a9\ud558\uc9c0 \uc54a\ub294\ub2e4<\/SPAN>.<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>PL\/SQL<\/SPAN>\uc740 \ub370\uc774\ud130\ubca0\uc774\uc2a4\ub97c \uba3c\uc800 \uac80\uc0c9\ud558\uc5ec \ud14c\uc774\ube14\uc758 <\/SPAN>\uceec\ub7fc\uc774<\/SPAN> \uc788\ub294\uc9c0\ub97c \ud655\uc778<\/SPAN>, <\/SPAN>\uad6c\ubb38 \uc624\ub958\uac00 \ubc1c\uc0dd\ud560 \uc218 \uc788\ub2e4<\/SPAN>.<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>DB<\/SPAN>\uc758 <\/SPAN>\u201c<\/SPAN>\uceec\ub7fc\uba85<\/SPAN>\u201d<\/SPAN>\uc740 <\/SPAN>\u201c<\/SPAN>\ub85c\uceec\ubcc0\uc218<\/SPAN>\u201d<\/SPAN>\uc758 \uc774\ub984\ubcf4\ub2e4 \uc6b0\uc120\ud55c\ub2e4<\/SPAN>.<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\u201c<\/SPAN>\ub85c\uceec\ubcc0\uc218<\/SPAN>\u201d<\/SPAN>\uc640 \ud615\uc2dd <\/SPAN>\ud30c\ub77c\ubbf8\ud130\uc758<\/SPAN> \uc774\ub984\uc740 <\/SPAN>DB<\/SPAN>\uc758 <\/SPAN>\u201c<\/SPAN>\ud14c\uc774\ube14\uba85<\/SPAN>\u201d<\/SPAN>\ubcf4\ub2e4 \uc6b0\uc120\ud55c\ub2e4<\/SPAN>.<\/SPAN><\/DIV>
\n

 <\/P>
\n

D. PL\/SQL\uc744 \uc0ac\uc6a9\ud55c \ub370\uc774\ud130 \uc870\uc791<\/P>
\n

<\/P>
\n

\u00a7<\/SPAN><\/SPAN>PL\/SQL<\/SPAN>\uc5d0\uc11c <\/SPAN>DML <\/SPAN>\uba85\ub839\uc744 \uc81c\uc57d \uc5c6\uc774 \uc2e4\ud589 \uac00\ub2a5<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>ROW LOCK, TABLE LOCK<\/SPAN>\uc740 <\/SPAN>PL\/SQL <\/SPAN>\ucf54\ub4dc\uc5d0 <\/SPAN>
<\/SPAN>COMMIT, ROLLBACK<\/SPAN>\uc744 \ub123\uc5b4 \ud574\uc81c<\/SPAN><\/DIV>
\n

<\/P>
\n

E. \ub370\uc774\ud130 \uc0bd\uc785<\/P>
\n

\u00a7<\/SPAN><\/SPAN>USER, CURRENT_DATE<\/SPAN>\uc640 \uac19\uc740 <\/SPAN>SQL <\/SPAN>\ud568\uc218 \uc0ac\uc6a9 \uac00\ub2a5<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\uae30\uc874 <\/SPAN>DB SEQUENCE<\/SPAN>\ub97c \uc0ac\uc6a9\ud558\uc5ec <\/SPAN>PRIMARY KEY <\/SPAN>\uac12 \uc0dd\uc131<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>PL\/SQL <\/SPAN>\ube14\ub85d\uc5d0\uc11c \uac12 \ubc1b\uc744 \uc218 \uc788\uc74c
\n

 ex)<\/P><\/SPAN><\/DIV>
\n

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

 <\/SPAN><\/SPAN>  <\/SPAN>INSERT INTO <\/SPAN><\/SPAN>employees<\/SPAN><\/P>
\n

 <\/SPAN><\/SPAN>     <\/SPAN>(<\/SPAN><\/SPAN>employee_id<\/SPAN>, <\/SPAN>first_name<\/SPAN>, <\/SPAN>last_name<\/SPAN>, email,<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>hire_date<\/SPAN>, <\/SPAN>job_id<\/SPAN>, salary)<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN> <\/SPAN>VALUES <\/SPAN><\/SPAN>(<\/SPAN>employees_seq.NEXTVAL<\/SPAN>, \u2018Ruth\u2019, \u2018Cores\u2019,<\/SPAN><\/P>
\n

  <\/SPAN>\u2018RCORES\u2019, CURRENT_DATE, \u2018AD_ASST\u2019, 4000);<\/SPAN><\/SPAN><\/P>
\n

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

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

F. \ub370\uc774\ud130 \uac31\uc2e0<\/P>
\n

\u00a7<\/SPAN><\/SPAN>\ud560\ub2f9 \uc5f0\uc0b0\uc790 \uc67c\ucabd\uc5d0 \uc788\ub294 <\/SPAN>\uc2dd\ubcc4\uc790\ub294<\/SPAN> <\/SPAN>\u201c<\/SPAN>\ud56d\uc0c1<\/SPAN>\u201d DB<\/SPAN>\uc758 <\/SPAN>\uceec\ub7fc<\/SPAN>
<\/SPAN>\ud558\uc9c0\ub9cc<\/SPAN>! <\/SPAN>\ud560\ub2f9 \uc5f0\uc0b0\uc790\uc758 \uc624\ub978\ucabd\uc758 <\/SPAN>\uc2dd\ubcc4\uc790\ub294<\/SPAN> <\/SPAN>DB<\/SPAN>\uc758 <\/SPAN>\uceec\ub7fc\uc77c<\/SPAN> \uc218\ub3c4<\/SPAN>,<\/SPAN>
PL\/SQL<\/SPAN><\/SPAN>\uc758 \ubcc0\uc218\uc77c \uc218\ub3c4 \uc788\uc74c<\/SPAN>!<\/SPAN>
(UPDATE<\/SPAN><\/SPAN>\uc758 <\/SPAN>SET<\/SPAN>\uc5d0\uc11c\uc758 \uc591\ucabd\uc758 <\/SPAN>SALARY)<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>WHERE<\/SPAN>\uc808\uc740 \uc601\ud5a5\uc744 \ubc1b\ub294 \ud589\uc744 \uacb0\uc815<\/SPAN>, UPDATE<\/SPAN>\ub41c \ud589\uc774 \uc5c6\uc5b4\ub3c4 <\/SPAN>
PL\/SQL<\/SPAN><\/SPAN>\uc758 <\/SPAN>SELECT<\/SPAN>\uc640 \ub2ec\ub9ac \uc624\ub958 \ubc1c\uc0dd\ud558\uc9c0 \uc54a\uc74c<\/SPAN><\/DIV>
\n
<\/DIV>
\n

*** PL\/SQL <\/SPAN>\ubcc0\uc218 \ud560\ub2f9\uc740 <\/SPAN>:=<\/SPAN><\/P>
\n

 <\/SPAN><\/SPAN>    <\/SPAN>SQL <\/SPAN><\/SPAN>\uceec\ub7fc\uc758<\/SPAN> \ud560\ub2f9\uc740 <\/SPAN>=<\/SPAN> <\/SPAN><\/P>
\n

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

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

 <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>sal_increase<\/SPAN> <\/SPAN>     <\/SPAN><\/SPAN>employees.salary%TYPE<\/SPAN> <\/SPAN>:= 800;<\/SPAN><\/P>
\n

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

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

   <\/SPAN>  <\/SPAN>SET<\/SPAN>             <\/SPAN><\/SPAN>salary = salary + <\/SPAN>sal_increase<\/SPAN><\/P>
\n

 <\/SPAN><\/SPAN>  <\/SPAN>WHERE<\/SPAN>       <\/SPAN><\/SPAN>job_id<\/SPAN> = \u2018ST_CLERK\u2019;<\/SPAN><\/P>
\n

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

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

G. \ub370\uc774\ud130 \uc0ad\uc81c<\/P>
\n

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

 <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>deptno<\/SPAN> <\/SPAN>     <\/SPAN><\/SPAN>departments.department_id%TYPE<\/SPAN> := 120;<\/SPAN><\/P>
\n

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

 <\/SPAN><\/SPAN>  <\/SPAN>DELETE FROM<\/SPAN>   <\/SPAN><\/SPAN>departments<\/SPAN><\/P>
\n

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

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

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

 <\/P>
\n

H. \ud589 \ubcd1\ud569<\/P>
\n

 <\/P>
\n

CREATE TABLE <\/SPAN>copy_dept<\/SPAN><\/P>
\n

AS <\/SPAN>select * from departments<\/SPAN><\/P>
\n

WHERE <\/SPAN>department_id<\/SPAN> <\/SPAN>IN<\/SPAN>(10,20);<\/SPAN><\/P>
\n

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

<\/P>
\n

UPDATE <\/SPAN>copy_dept<\/SPAN><\/P>
\n

SET <\/SPAN>department_name<\/SPAN> = \u2018MKT\u2019<\/SPAN><\/P>
\n

WHERE <\/SPAN>department_id<\/SPAN> = 20;<\/SPAN><\/P><\/p>\n

 <\/p>\n

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

MERGE INTO <\/SPAN>copy_dept<\/SPAN> c<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN>USING <\/SPAN><\/SPAN>departments d<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN>ON <\/SPAN><\/SPAN>(<\/SPAN>d.department_id<\/SPAN> = <\/SPAN>c.department_id<\/SPAN>)<\/SPAN><\/P>
\n

 <\/SPAN><\/SPAN>     <\/SPAN>WHEN MATCHED THEN<\/SPAN><\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN>UPDATE SET<\/SPAN><\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>c.department_name<\/SPAN> <\/SPAN>= <\/SPAN>d.department_name<\/SPAN>,<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>c.manager_id<\/SPAN> = <\/SPAN>d.manager_id<\/SPAN>,<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>c.location_id<\/SPAN> = <\/SPAN>d.location_id<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN>WHEN NOT MATCHED THEN<\/SPAN><\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN>INSERT VALUES<\/SPAN><\/SPAN> (<\/SPAN>d.department_id<\/SPAN>,<\/SPAN><\/P>
\n

  <\/SPAN><\/SPAN>  <\/SPAN> <\/SPAN>  <\/SPAN><\/SPAN>d.department_name<\/SPAN>, <\/SPAN>d.manager_id<\/SPAN>, <\/SPAN>d.location_id<\/SPAN>);<\/SPAN><\/P>
\n

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

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

 <\/P>
\n

<\/P>
\n

 <\/P>
\n

<\/P>
\n

 <\/P>
\n

I. SQL \ucee4\uc11c<\/P>
\n

\u00a7<\/SPAN><\/SPAN>\ucee4\uc11c\ub294 <\/SPAN>ORACLE <\/SPAN>\uc11c\ubc84\uc5d0\uc11c \ud560\ub2f9\ud55c \uc804\uc6a9<\/SPAN>(PRIVATE) <\/SPAN>\uba54\ubaa8\ub9ac \uc601\uc5ed\uc5d0 \ub300\ud55c \ud3ec\uc778\ud130<\/SPAN>. SELECT <\/SPAN>\ubb38\uc758 \uacb0\uacfc \uc9d1\ud569\uc744 \ucc98\ub9ac\ud558\ub294\ub370 \uc0ac\uc6a9<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\ucee4\uc11c \uc720\ud615<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>IMPLICIT : ORACLE <\/SPAN>\uc11c\ubc84\uc5d0\uc11c <\/SPAN>SQL<\/SPAN>\ubb38\uc744 \ucc98\ub9ac\ud558\uae30 \uc704\ud574<\/SPAN>
<\/SPAN>  <\/SPAN><\/SPAN>  <\/SPAN>   <\/SPAN><\/SPAN>\ub0b4\ubd80\uc801\uc73c\ub85c \uc0dd\uc131<\/SPAN>\/<\/SPAN>\uad00\ub9ac<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>EXPLICIT : <\/SPAN>\ud504\ub85c\uadf8\ub798\uba38\uac00 \uba85\uc2dc\uc801\uc73c\ub85c \uc120\uc5b8<\/SPAN><\/DIV>
\n

<\/P>
\n

 <\/P>
\n

<\/P><\/p>\n

 <\/p>\n

\u00a7<\/SPAN><\/SPAN>EXPLICIT CURSOR<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\ud14c\uc774\ube14\uc5d0\uc11c \uc5ec\ub7ec \ud589\uc744 \uac80\uc0c9<\/SPAN>, <\/SPAN>\uac80\uc0c9\ub41c \uac01 \ud589\uc5d0 \ud3ec\uc778\ud130\ub97c <\/SPAN>
<\/SPAN>\uc9c0\uc815\ud558\uc5ec \ud55c \ubc88\uc5d0 \ud55c \ud589\uc529 \ucc98\ub9ac\ud574\uc57c \ud558\ub294 \uacbd\uc6b0<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>PL\/SQL<\/SPAN>\uc758 <\/SPAN>DECLARE <\/SPAN>\uc139\uc158\uc5d0\uc11c \ud504\ub85c\uadf8\ub798\uba38\uac00 \uc120\uc5b8<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>IMPLICIT CURSOR<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>CONTEXT AREA <\/SPAN>\uc5d0 \ub300\ud55c \ud3ec\uc778\ud130<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>ORACLE <\/SPAN>\uc11c\ubc84\uc5d0\uc11c \uc790\ub3d9 \uad00\ub9ac\ud558\ub294 \ucee4\uc11c<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\ud504\ub85c\uadf8\ub798\uba38\uc5d0\uac8c\ub294 \uc561\uc138\uc2a4 \uad8c\ud55c\uc774 \uc5c6\ub294 \ucee4\uc11c<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>SQL<\/SPAN>\ubb38\uc744 \uc2e4\ud589\ud574\uc57c \ud560 \ub54c \uc0dd\uc131<\/SPAN><\/DIV><\/p>\n

 <\/p>\n

\u00a7<\/SPAN><\/SPAN>CONTEXT AREA<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>ORACLE <\/SPAN>\uc11c\ubc84\uac00 <\/SPAN>SQL<\/SPAN>\ubb38\uc744 \ucc98\ub9ac\ud558\uae30 \uc704\ud574 \ud560\ub2f9\ud55c<\/SPAN>
\u2018<\/SPAN><\/SPAN>\uc804\uc6a9 \uba54\ubaa8\ub9ac \uc601\uc5ed<\/SPAN>\u2019<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>SQL<\/SPAN>\ubb38\uc758 \uad6c\ubb38 \ubd84\uc11d<\/SPAN>, <\/SPAN>\ucc98\ub9ac<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\ucc98\ub9ac\uc5d0 \ud544\uc694\ud55c \uc815\ubcf4\uc640 \ucc98\ub9ac \ud6c4\uc5d0 \uac80\uc0c9\ub41c \uc815\ubcf4\uac00 \uc800\uc7a5<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>ORACLE <\/SPAN>\uc11c\ubc84\uac00 \ub0b4\ubd80\uc801\uc73c\ub85c \uad00\ub9ac<\/SPAN>, USER <\/SPAN>\uc81c\uc5b4 \ubd88\uac00<\/SPAN><\/DIV>
\n

 <\/P>
\n

J. IMPLICIT CURSOR \uc18d\uc131<\/P>
\n

\u00a7<\/SPAN><\/SPAN>SQL <\/SPAN>\ucee4\uc11c \uc18d\uc131\uc744 \uc0ac\uc6a9\ud558\uc5ec <\/SPAN>SQL <\/SPAN>\ubb38\uc758 \uacb0\uacfc\ub97c <\/SPAN>
<\/SPAN>\ud14c\uc2a4\ud2b8\ud560 \uc218 \uc788\ub2e4<\/SPAN>.<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>SQL <\/SPAN>\ucee4\uc11c \uc18d\uc131\uc740 <\/SPAN>PL\/SQL <\/SPAN>\ubb38\uc5d0\uc11c \uc0ac\uc6a9<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>DML <\/SPAN>\ubb38\uc774 \ud14c\uc774\ube14\uc758 \ud589\uc5d0 \uc601\ud5a5\uc744 \uc8fc\uc9c0 \uc54a\uc73c\uba74 \uc624\ub958 \ubc18\ud658<\/SPAN>X<\/SPAN>
<\/SPAN>SELECT <\/SPAN>\ubb38\uc774 \uc544\ubb34 \ud589\ub3c4 \uac80\uc0c9\ud558\uc9c0 \uc54a\uc73c\uba74 <\/SPAN>PL\/SQL<\/SPAN>\uc740 \uc624\ub958 \ubc18\ud658<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>\u2018SQL\u2019<\/SPAN>\uc774\ub77c\ub294 <\/SPAN>\uc811\ub450\uc5b4<\/SPAN> <\/SPAN>: <\/SPAN>\ubb35\uc2dc\uc801 \ucee4\uc11c\ub294 <\/SPAN>PL\/SQL<\/SPAN>\uc5d0 \uc758\ud574 \uc790\ub3d9\uc0dd\uc131<\/SPAN>, <\/SPAN>\uc774\ub984 \uc54c \uc218 \uc5c6\uc74c<\/SPAN>.<\/SPAN>
<\/SPAN>=> <\/SPAN>\ucee4\uc11c \uc774\ub984 \ub300\uc2e0 <\/SPAN>\u2018SQL\u2019<\/SPAN>\uc744 \ubd99\uc5ec\uc11c \uc0ac\uc6a9
\n

 <\/P>
\n

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

\u00a7<\/SPAN><\/SPAN>SQL%FOUND: <\/SPAN>\uac00\uc7a5 \ucd5c\uadfc\uc758 <\/SPAN>SQL<\/SPAN>\ubb38\uc774 <\/SPAN>\ud55c \ud589 \uc774\uc0c1<\/SPAN><\/U>\uc5d0 \uc601\ud5a5<\/SPAN>
  <\/SPAN>    <\/SPAN>->TRUE , <\/SPAN><\/SPAN>\ubd80\uc6b8<\/SPAN> \uc18d\uc131<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>SQL%NOTFOUND: <\/SPAN>\uac00\uc7a5 \ucd5c\uadfc\uc758 <\/SPAN>SQL<\/SPAN>\ubb38\uc774 <\/SPAN>\uc601\ud5a5 \uc900 \ud589\uc774 \uc5c6\ub294<\/SPAN><\/U> \uacbd\uc6b0<\/SPAN>
  <\/SPAN><\/SPAN>  <\/SPAN>    <\/SPAN>->TRUE, <\/SPAN><\/SPAN>\ubd80\uc6b8\uc18d\uc131<\/SPAN><\/DIV>
\n
\u00a7<\/SPAN><\/SPAN>SQL%ROWCOUNT: <\/SPAN>\uac00\uc7a5 \ucd5c\uadfc\uc758 <\/SPAN>SQL<\/SPAN>\ubb38\uc5d0 \uc758\ud574 \uc601\ud5a5\uc744 \ubc1b\uc740 \ud589 \uc218<\/SPAN>
  <\/SPAN>-> <\/SPAN><\/SPAN>\uc815\uc218 \uac12<\/SPAN><\/DIV>
\n

<\/P>
\n

ex)<\/P>
\n

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

 <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>v_rows_deleted<\/SPAN> VARCHAR2(30);<\/SPAN><\/P>
\n

   <\/SPAN><\/SPAN>v_deptno<\/SPAN> <\/SPAN>copy_dept.department_id<\/SPAN>%type<\/SPAN> <\/SPAN>:= 20;<\/SPAN><\/P>
\n

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

 <\/SPAN><\/SPAN>  <\/SPAN>DELETE FROM <\/SPAN><\/SPAN>copy_dept<\/SPAN><\/P>
\n

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

 <\/SPAN><\/SPAN>  <\/SPAN><\/SPAN>v_rows_deleted<\/SPAN> := (<\/SPAN>SQL%ROWCOUNT <\/SPAN>||<\/SPAN><\/P>
\n

  <\/SPAN>\u2018 row deleted. \u2018);<\/SPAN><\/SPAN><\/P>
\n

 <\/SPAN><\/SPAN>  <\/SPAN>DBMS_OUTPUT.PUT_LINE <\/SPAN><\/SPAN>(<\/SPAN>v_rows_deleted<\/SPAN>);<\/SPAN><\/P>
\n

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

\/ <\/SPAN><\/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>
\n

 <\/P>
\n

<\/FONT><\/SPAN> <\/DIV>
\n
<\/FONT><\/SPAN> <\/DIV><\/p>\n","protected":false},"excerpt":{"rendered":"

4. PL\/SQL \ud504\ub85c\uadf8\ub7a8\uc758 SQL\ubb38 (\uc624\ub77c\ud074 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc11c\ubc84\uc640 \uc0c1\ud638\uc791\uc6a9)   A. PL\/SQL\uc758 SQL\ubb38 \u00a7SELECT \uba85\ub839\uc744 \uc0ac\uc6a9\ud55c DB \ud589 \uac80\uc0c9 \u00a7DML \uba85\ub839\uc744 \uc0ac\uc6a9\ud55c DB \ud589 \ubcc0\uacbd \u00a7COMMIT, ROLLBACK, SAVEPOINT \uba85\ub839\uc744 \uc0ac\uc6a9\ud55c TX \uc81c\uc5b4 *** END \ud0a4\uc6cc\ub4dc\ub294 TX\uc758 \ub05d\uc774 \uc544\ub2cc, PL\/SQL \ube14\ub85d\uc758 \ub05d!      TX\ub3c4 \ub2e4\uc911 \ube14\ub85d\uc744 \ud655\uc7a5\ud560 \uc218 \uc788\ub2e4. *** PL\/SQL\uc740 DDL, DCL\uc744 \u2018\uc9c1\uc811\u2019 \uc9c0\uc6d0\ud558\uc9c0 \uc54a\ub294\ub2e4.      […]<\/p>\n","protected":false},"author":1,"featured_media":1236,"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\/cfile1.uf.2148153456892DFB1C97A3.png?fit=472%2C349","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1235"}],"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=1235"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1235\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/1236"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1235"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1235"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1235"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}