{"id":278,"date":"2008-11-22T12:59:07","date_gmt":"2008-11-22T11:59:07","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=278"},"modified":"2008-11-22T12:59:07","modified_gmt":"2008-11-22T11:59:07","slug":"bulk-operations-in-plsql","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=278","title":{"rendered":"Bulk Operations in PL\/SQL"},"content":{"rendered":"<p>When I was in Chicago for the OPP2008 and APEXposed event I talked to someone who seems to have trouble understanding bulk operations in PL\/SQL. I helped him out by providing a test\/demo script, that shows how it could be done. First of all, one of the most important rules of thumb that I got from the seminar (almost everyone talked about this):<\/p>\n<ul>\n<li>If you can do it in SQL, do it in SQL <\/li>\n<li>If you can\u2019t, use PL\/SQL <\/li>\n<li>If you still can\u2019t, then resort to some other tool, like Java, VB or whatever\u2026 <\/li>\n<\/ul>\n<p>If you can do your update, entirely in SQL, then that is the fastest solution. But in the case described to me, there is a lot going on between the select (from table) and the update (maybe even some other table).<\/p>\n<p> <!--more-->  <\/p>\n<p>To work with the demonstration files, we need to create a table that we can use for this demonstration:<\/p>\n<div class=\"csharpcode\">\n<pre class=\"alt\"><span class=\"lnum\">   1:  <\/span><span class=\"kwrd\">DELETE<\/span> EMP;<\/pre>\n<pre><span class=\"lnum\">   2:  <\/span><span class=\"kwrd\">DELETE<\/span> DEPT;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   3:  <\/span><span class=\"kwrd\">DROP<\/span> <span class=\"kwrd\">TABLE<\/span> EMP;<\/pre>\n<pre><span class=\"lnum\">   4:  <\/span><span class=\"kwrd\">DROP<\/span> <span class=\"kwrd\">TABLE<\/span> DEPT;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   5:  <\/span>&#160;<\/pre>\n<pre><span class=\"lnum\">   6:  <\/span><span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">TABLE<\/span> DEPT<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   7:  <\/span>       ( DEPTNO NUMBER(2)<\/pre>\n<pre><span class=\"lnum\">   8:  <\/span>       , DNAME  VARCHAR2(14)<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   9:  <\/span>       , LOC    VARCHAR2(13) <\/pre>\n<pre><span class=\"lnum\">  10:  <\/span>       );<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  11:  <\/span>&#160;<\/pre>\n<pre><span class=\"lnum\">  12:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> DEPT <span class=\"kwrd\">VALUES<\/span> (10, <span class=\"str\">'ACCOUNTING'<\/span>, <span class=\"str\">'NEW YORK'<\/span>);<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  13:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> DEPT <span class=\"kwrd\">VALUES<\/span> (20, <span class=\"str\">'RESEARCH'<\/span>,   <span class=\"str\">'DALLAS'<\/span>);<\/pre>\n<pre><span class=\"lnum\">  14:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> DEPT <span class=\"kwrd\">VALUES<\/span> (30, <span class=\"str\">'SALES'<\/span>,      <span class=\"str\">'CHICAGO'<\/span>);<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  15:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> DEPT <span class=\"kwrd\">VALUES<\/span> (40, <span class=\"str\">'OPERATIONS'<\/span>, <span class=\"str\">'BOSTON'<\/span>);<\/pre>\n<pre><span class=\"lnum\">  16:  <\/span>&#160;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  17:  <\/span><span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">TABLE<\/span> EMP<\/pre>\n<pre><span class=\"lnum\">  18:  <\/span>       ( EMPNO    NUMBER(4) <span class=\"kwrd\">NOT<\/span> <span class=\"kwrd\">NULL<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  19:  <\/span>       , ENAME    VARCHAR2(10)<\/pre>\n<pre><span class=\"lnum\">  20:  <\/span>       , JOB      VARCHAR2(9)<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  21:  <\/span>       , MGR      NUMBER(4)<\/pre>\n<pre><span class=\"lnum\">  22:  <\/span>       , HIREDATE <span class=\"kwrd\">DATE<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  23:  <\/span>       , SAL      NUMBER(7, 2)<\/pre>\n<pre><span class=\"lnum\">  24:  <\/span>       , COMM     NUMBER(7, 2)<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  25:  <\/span>       , DEPTNO   NUMBER(2)<\/pre>\n<pre><span class=\"lnum\">  26:  <\/span>       );<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  27:  <\/span>&#160;<\/pre>\n<pre><span class=\"lnum\">  28:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> EMP <span class=\"kwrd\">VALUES<\/span> (7369, <span class=\"str\">'SMITH'<\/span>,  <span class=\"str\">'CLERK'<\/span>,     7902, TO_DATE(<span class=\"str\">'17-DEC-1980'<\/span>, <span class=\"str\">'DD-MON-YYYY'<\/span>),  800, <span class=\"kwrd\">NULL<\/span>, 20);<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  29:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> EMP <span class=\"kwrd\">VALUES<\/span> (7499, <span class=\"str\">'ALLEN'<\/span>,  <span class=\"str\">'SALESMAN'<\/span>,  7698, TO_DATE(<span class=\"str\">'20-FEB-1981'<\/span>, <span class=\"str\">'DD-MON-YYYY'<\/span>), 1600,  300, 30);<\/pre>\n<pre><span class=\"lnum\">  30:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> EMP <span class=\"kwrd\">VALUES<\/span> (7521, <span class=\"str\">'WARD'<\/span>,   <span class=\"str\">'SALESMAN'<\/span>,  7698, TO_DATE(<span class=\"str\">'22-FEB-1981'<\/span>, <span class=\"str\">'DD-MON-YYYY'<\/span>), 1250,  500, 30);<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  31:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> EMP <span class=\"kwrd\">VALUES<\/span> (7566, <span class=\"str\">'JONES'<\/span>,  <span class=\"str\">'MANAGER'<\/span>,   7839, TO_DATE(<span class=\"str\">'2-APR-1981'<\/span> , <span class=\"str\">'DD-MON-YYYY'<\/span>), 2975, <span class=\"kwrd\">NULL<\/span>, 20);<\/pre>\n<pre><span class=\"lnum\">  32:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> EMP <span class=\"kwrd\">VALUES<\/span> (7654, <span class=\"str\">'MARTIN'<\/span>, <span class=\"str\">'SALESMAN'<\/span>,  7698, TO_DATE(<span class=\"str\">'28-SEP-1981'<\/span>, <span class=\"str\">'DD-MON-YYYY'<\/span>), 1250, 1400, 30);<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  33:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> EMP <span class=\"kwrd\">VALUES<\/span> (7698, <span class=\"str\">'BLAKE'<\/span>,  <span class=\"str\">'MANAGER'<\/span>,   7839, TO_DATE(<span class=\"str\">'1-MAY-1981'<\/span> , <span class=\"str\">'DD-MON-YYYY'<\/span>), 2850, <span class=\"kwrd\">NULL<\/span>, 30);<\/pre>\n<pre><span class=\"lnum\">  34:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> EMP <span class=\"kwrd\">VALUES<\/span> (7782, <span class=\"str\">'CLARK'<\/span>,  <span class=\"str\">'MANAGER'<\/span>,   7839, TO_DATE(<span class=\"str\">'9-JUN-1981'<\/span> , <span class=\"str\">'DD-MON-YYYY'<\/span>), 2450, <span class=\"kwrd\">NULL<\/span>, 10);<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  35:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> EMP <span class=\"kwrd\">VALUES<\/span> (7788, <span class=\"str\">'SCOTT'<\/span>,  <span class=\"str\">'ANALYST'<\/span>,   7566, TO_DATE(<span class=\"str\">'09-DEC-1982'<\/span>, <span class=\"str\">'DD-MON-YYYY'<\/span>), 3000, <span class=\"kwrd\">NULL<\/span>, 20);<\/pre>\n<pre><span class=\"lnum\">  36:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> EMP <span class=\"kwrd\">VALUES<\/span> (7839, <span class=\"str\">'KING'<\/span>,   <span class=\"str\">'PRESIDENT'<\/span>, <span class=\"kwrd\">NULL<\/span>, TO_DATE(<span class=\"str\">'17-NOV-1981'<\/span>, <span class=\"str\">'DD-MON-YYYY'<\/span>), 5000, <span class=\"kwrd\">NULL<\/span>, 10);<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  37:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> EMP <span class=\"kwrd\">VALUES<\/span> (7844, <span class=\"str\">'TURNER'<\/span>, <span class=\"str\">'SALESMAN'<\/span>,  7698, TO_DATE(<span class=\"str\">'8-SEP-1981'<\/span> , <span class=\"str\">'DD-MON-YYYY'<\/span>), 1500,    0, 30);<\/pre>\n<pre><span class=\"lnum\">  38:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> EMP <span class=\"kwrd\">VALUES<\/span> (7876, <span class=\"str\">'ADAMS'<\/span>,  <span class=\"str\">'CLERK'<\/span>,     7788, TO_DATE(<span class=\"str\">'12-JAN-1983'<\/span>, <span class=\"str\">'DD-MON-YYYY'<\/span>), 1100, <span class=\"kwrd\">NULL<\/span>, 20);<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  39:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> EMP <span class=\"kwrd\">VALUES<\/span> (7900, <span class=\"str\">'JAMES'<\/span>,  <span class=\"str\">'CLERK'<\/span>,     7698, TO_DATE(<span class=\"str\">'3-DEC-1981'<\/span> , <span class=\"str\">'DD-MON-YYYY'<\/span>),  950, <span class=\"kwrd\">NULL<\/span>, 30);<\/pre>\n<pre><span class=\"lnum\">  40:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> EMP <span class=\"kwrd\">VALUES<\/span> (7902, <span class=\"str\">'FORD'<\/span>,   <span class=\"str\">'ANALYST'<\/span>,   7566, TO_DATE(<span class=\"str\">'3-DEC-1981'<\/span> , <span class=\"str\">'DD-MON-YYYY'<\/span>), 3000, <span class=\"kwrd\">NULL<\/span>, 20);<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  41:  <\/span>INSERT <span class=\"kwrd\">INTO<\/span> EMP <span class=\"kwrd\">VALUES<\/span> (7934, <span class=\"str\">'MILLER'<\/span>, <span class=\"str\">'CLERK'<\/span>,     7782, TO_DATE(<span class=\"str\">'23-JAN-1982'<\/span>, <span class=\"str\">'DD-MON-YYYY'<\/span>), 1300, <span class=\"kwrd\">NULL<\/span>, 10);<\/pre>\n<pre><span class=\"lnum\">  42:  <\/span>&#160;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  43:  <\/span><span class=\"kwrd\">COMMIT<\/span>;<\/pre>\n<\/div>\n<style type=\"text\/css\">\n<p>.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }<\/style>\n<p>Then we create the code as a stored procedure<\/p>\n<div class=\"csharpcode\">\n<pre class=\"alt\"><span class=\"lnum\">   1:  <\/span><span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">OR<\/span> REPLACE <span class=\"kwrd\">PROCEDURE<\/span> bulk_demonstration<\/pre>\n<pre><span class=\"lnum\">   2:  <\/span><span class=\"kwrd\">IS<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   3:  <\/span>  <span class=\"rem\">-- The maximum rows collected in the bulk collect operation<\/span><\/pre>\n<pre><span class=\"lnum\">   4:  <\/span>  c_maxrows                       CONSTANT PLS_INTEGER := 5;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   5:  <\/span>&#160;<\/pre>\n<pre><span class=\"lnum\">   6:  <\/span>  <span class=\"rem\">-- Subtypes based on the columns in the cursor<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   7:  <\/span>  SUBTYPE empno_t <span class=\"kwrd\">IS<\/span> emp.empno%TYPE;<\/pre>\n<pre><span class=\"lnum\">   8:  <\/span>  SUBTYPE ename_t <span class=\"kwrd\">IS<\/span> emp.ename%TYPE;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   9:  <\/span>  SUBTYPE job_t <span class=\"kwrd\">IS<\/span> emp.job%TYPE;<\/pre>\n<pre><span class=\"lnum\">  10:  <\/span>  SUBTYPE mgr_t <span class=\"kwrd\">IS<\/span> emp.mgr%TYPE;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  11:  <\/span>  SUBTYPE hiredate_t <span class=\"kwrd\">IS<\/span> emp.hiredate%TYPE;<\/pre>\n<pre><span class=\"lnum\">  12:  <\/span>  SUBTYPE sal_t <span class=\"kwrd\">IS<\/span> emp.sal%TYPE;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  13:  <\/span>  SUBTYPE comm_t <span class=\"kwrd\">IS<\/span> emp.comm%TYPE;<\/pre>\n<pre><span class=\"lnum\">  14:  <\/span>  SUBTYPE deptno_t <span class=\"kwrd\">IS<\/span> emp.deptno%TYPE;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  15:  <\/span>&#160;<\/pre>\n<pre><span class=\"lnum\">  16:  <\/span>  <span class=\"rem\">-- Column Collections for every column in the cursor<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  17:  <\/span>  TYPE empno_cc <span class=\"kwrd\">IS<\/span> <span class=\"kwrd\">TABLE<\/span> <span class=\"kwrd\">OF<\/span> empno_t       <span class=\"kwrd\">INDEX<\/span> <span class=\"kwrd\">BY<\/span> BINARY_INTEGER;<\/pre>\n<pre><span class=\"lnum\">  18:  <\/span>  TYPE ename_cc <span class=\"kwrd\">IS<\/span> <span class=\"kwrd\">TABLE<\/span> <span class=\"kwrd\">OF<\/span> ename_t       <span class=\"kwrd\">INDEX<\/span> <span class=\"kwrd\">BY<\/span> BINARY_INTEGER;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  19:  <\/span>  TYPE job_cc <span class=\"kwrd\">IS<\/span> <span class=\"kwrd\">TABLE<\/span> <span class=\"kwrd\">OF<\/span> job_t           <span class=\"kwrd\">INDEX<\/span> <span class=\"kwrd\">BY<\/span> BINARY_INTEGER;<\/pre>\n<pre><span class=\"lnum\">  20:  <\/span>  TYPE mgr_cc <span class=\"kwrd\">IS<\/span> <span class=\"kwrd\">TABLE<\/span> <span class=\"kwrd\">OF<\/span> mgr_t           <span class=\"kwrd\">INDEX<\/span> <span class=\"kwrd\">BY<\/span> BINARY_INTEGER;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  21:  <\/span>  TYPE hiredate_cc <span class=\"kwrd\">IS<\/span> <span class=\"kwrd\">TABLE<\/span> <span class=\"kwrd\">OF<\/span> hiredate_t <span class=\"kwrd\">INDEX<\/span> <span class=\"kwrd\">BY<\/span> BINARY_INTEGER;<\/pre>\n<pre><span class=\"lnum\">  22:  <\/span>  TYPE sal_cc <span class=\"kwrd\">IS<\/span> <span class=\"kwrd\">TABLE<\/span> <span class=\"kwrd\">OF<\/span> sal_t           <span class=\"kwrd\">INDEX<\/span> <span class=\"kwrd\">BY<\/span> BINARY_INTEGER;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  23:  <\/span>  TYPE comm_cc <span class=\"kwrd\">IS<\/span> <span class=\"kwrd\">TABLE<\/span> <span class=\"kwrd\">OF<\/span> comm_t         <span class=\"kwrd\">INDEX<\/span> <span class=\"kwrd\">BY<\/span> BINARY_INTEGER;<\/pre>\n<pre><span class=\"lnum\">  24:  <\/span>  TYPE deptno_cc <span class=\"kwrd\">IS<\/span> <span class=\"kwrd\">TABLE<\/span> <span class=\"kwrd\">OF<\/span> deptno_t     <span class=\"kwrd\">INDEX<\/span> <span class=\"kwrd\">BY<\/span> BINARY_INTEGER;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  25:  <\/span>&#160;<\/pre>\n<pre><span class=\"lnum\">  26:  <\/span>  <span class=\"rem\">-- The cursor used<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  27:  <\/span>  <span class=\"kwrd\">CURSOR<\/span> emp_cur<\/pre>\n<pre><span class=\"lnum\">  28:  <\/span>  <span class=\"kwrd\">IS<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  29:  <\/span>    <span class=\"kwrd\">SELECT<\/span> emp.empno<\/pre>\n<pre><span class=\"lnum\">  30:  <\/span>         , emp.ename<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  31:  <\/span>         , emp.job<\/pre>\n<pre><span class=\"lnum\">  32:  <\/span>         , emp.mgr<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  33:  <\/span>         , emp.hiredate<\/pre>\n<pre><span class=\"lnum\">  34:  <\/span>         , emp.sal<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  35:  <\/span>         , emp.comm<\/pre>\n<pre><span class=\"lnum\">  36:  <\/span>         , emp.deptno<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  37:  <\/span>      <span class=\"kwrd\">FROM<\/span> emp;<\/pre>\n<pre><span class=\"lnum\">  38:  <\/span>&#160;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  39:  <\/span>  <span class=\"rem\">-- Local variables to 'catch' the results from the cursor<\/span><\/pre>\n<pre><span class=\"lnum\">  40:  <\/span>  l_empno                                  empno_cc;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  41:  <\/span>  l_ename                                  ename_cc;<\/pre>\n<pre><span class=\"lnum\">  42:  <\/span>  l_job                                    job_cc;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  43:  <\/span>  l_mgr                                    mgr_cc;<\/pre>\n<pre><span class=\"lnum\">  44:  <\/span>  l_hiredate                               hiredate_cc;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  45:  <\/span>  l_sal                                    sal_cc;<\/pre>\n<pre><span class=\"lnum\">  46:  <\/span>  l_comm                                   comm_cc;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  47:  <\/span>  l_deptno                                 deptno_cc;<\/pre>\n<pre><span class=\"lnum\">  48:  <\/span>  <span class=\"rem\">-- local function to do something with the data<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  49:  <\/span>  <span class=\"kwrd\">function<\/span> raisesal(sal_in sal_t) <span class=\"kwrd\">return<\/span> sal_t<\/pre>\n<pre><span class=\"lnum\">  50:  <\/span>  <span class=\"kwrd\">is<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  51:  <\/span>    l_returnvalue sal_t;<\/pre>\n<pre><span class=\"lnum\">  52:  <\/span>  <span class=\"kwrd\">begin<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  53:  <\/span>    l_returnvalue := sal_in * 1.1;<\/pre>\n<pre><span class=\"lnum\">  54:  <\/span>    <span class=\"kwrd\">RETURN<\/span> l_returnvalue;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  55:  <\/span>  <span class=\"kwrd\">end<\/span> raisesal;<\/pre>\n<pre><span class=\"lnum\">  56:  <\/span>    <\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  57:  <\/span><span class=\"kwrd\">BEGIN<\/span><\/pre>\n<pre><span class=\"lnum\">  58:  <\/span>  <span class=\"rem\">-- open the cursor<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  59:  <\/span>  <span class=\"kwrd\">OPEN<\/span> emp_cur;<\/pre>\n<pre><span class=\"lnum\">  60:  <\/span>  <span class=\"rem\">-- start a simple loop<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  61:  <\/span>  LOOP<\/pre>\n<pre><span class=\"lnum\">  62:  <\/span>    <span class=\"rem\">-- clear out the collections<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  63:  <\/span>    l_empno.<span class=\"kwrd\">delete<\/span>;<\/pre>\n<pre><span class=\"lnum\">  64:  <\/span>    l_ename.<span class=\"kwrd\">delete<\/span>;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  65:  <\/span>    l_job.<span class=\"kwrd\">delete<\/span>;<\/pre>\n<pre><span class=\"lnum\">  66:  <\/span>    l_mgr.<span class=\"kwrd\">delete<\/span>;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  67:  <\/span>    l_hiredate.<span class=\"kwrd\">delete<\/span>;<\/pre>\n<pre><span class=\"lnum\">  68:  <\/span>    l_sal.<span class=\"kwrd\">delete<\/span>;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  69:  <\/span>    l_comm.<span class=\"kwrd\">delete<\/span>;<\/pre>\n<pre><span class=\"lnum\">  70:  <\/span>    l_deptno.<span class=\"kwrd\">delete<\/span>;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  71:  <\/span>    <span class=\"rem\">-- fetch from the cursor using bulk collect for the result, but limited to limit<\/span><\/pre>\n<pre><span class=\"lnum\">  72:  <\/span>    <span class=\"kwrd\">FETCH<\/span> emp_cur <span class=\"kwrd\">BULK<\/span> COLLECT <span class=\"kwrd\">INTO<\/span> l_empno<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  73:  <\/span>                                  , l_ename<\/pre>\n<pre><span class=\"lnum\">  74:  <\/span>                                  , l_job<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  75:  <\/span>                                  , l_mgr<\/pre>\n<pre><span class=\"lnum\">  76:  <\/span>                                  , l_hiredate<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  77:  <\/span>                                  , l_sal<\/pre>\n<pre><span class=\"lnum\">  78:  <\/span>                                  , l_comm<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  79:  <\/span>                                  , l_deptno<\/pre>\n<pre><span class=\"lnum\">  80:  <\/span>    <span class=\"kwrd\">LIMIT<\/span> c_maxrows;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  81:  <\/span>    <span class=\"rem\">-- if we fetched any data<\/span><\/pre>\n<pre><span class=\"lnum\">  82:  <\/span>    <span class=\"kwrd\">IF<\/span> l_empno.<span class=\"kwrd\">count<\/span> &gt; 0 <span class=\"kwrd\">THEN<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  83:  <\/span>      <span class=\"rem\">-- do some complicated stuff<\/span><\/pre>\n<pre><span class=\"lnum\">  84:  <\/span>      <span class=\"kwrd\">FOR<\/span> idx <span class=\"kwrd\">IN<\/span> l_empno.<span class=\"kwrd\">first<\/span>..l_empno.<span class=\"kwrd\">last<\/span> LOOP<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  85:  <\/span><span class=\"rem\">--        l_sal(idx) := l_sal(idx) * 1.1;<\/span><\/pre>\n<pre><span class=\"lnum\">  86:  <\/span>        l_sal(idx) := raisesal(l_sal(idx));<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  87:  <\/span>      <span class=\"kwrd\">END<\/span> LOOP;<\/pre>\n<pre><span class=\"lnum\">  88:  <\/span>      <span class=\"rem\">-- update the records in the database, using a bulk operation<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  89:  <\/span>      FORALL idx <span class=\"kwrd\">IN<\/span> l_empno.<span class=\"kwrd\">first<\/span>..l_empno.<span class=\"kwrd\">last<\/span><\/pre>\n<pre><span class=\"lnum\">  90:  <\/span>        <span class=\"kwrd\">UPDATE<\/span> emp<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  91:  <\/span>           <span class=\"kwrd\">SET<\/span> sal = l_sal(idx)<\/pre>\n<pre><span class=\"lnum\">  92:  <\/span>         <span class=\"kwrd\">WHERE<\/span> empno = l_empno(idx);<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  93:  <\/span>    <span class=\"kwrd\">END<\/span> <span class=\"kwrd\">IF<\/span>;<\/pre>\n<pre><span class=\"lnum\">  94:  <\/span>    <span class=\"rem\">-- exit when we didn't fetch our maximum rows<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  95:  <\/span>    <span class=\"kwrd\">EXIT<\/span> <span class=\"kwrd\">WHEN<\/span> l_empno.<span class=\"kwrd\">count<\/span> &lt; c_maxrows;<\/pre>\n<pre><span class=\"lnum\">  96:  <\/span>  <span class=\"kwrd\">END<\/span> LOOP;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  97:  <\/span><span class=\"kwrd\">END<\/span> bulk_demonstration;<\/pre>\n<\/div>\n<style type=\"text\/css\">\n<p>.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }<\/style>\n<p>Then a small script to see if it works<\/p>\n<div class=\"csharpcode\">\n<pre class=\"alt\"><span class=\"lnum\">   1:  <\/span>CLEAR screen<\/pre>\n<pre><span class=\"lnum\">   2:  <\/span><span class=\"kwrd\">SET<\/span> serveroutput <span class=\"kwrd\">on<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   3:  <\/span><span class=\"kwrd\">SELECT<\/span> emp.empno<\/pre>\n<pre><span class=\"lnum\">   4:  <\/span>     , emp.sal<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   5:  <\/span>  <span class=\"kwrd\">FROM<\/span> emp;<\/pre>\n<pre><span class=\"lnum\">   6:  <\/span><span class=\"kwrd\">EXEC<\/span> bulk_demonstration;<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   7:  <\/span><span class=\"kwrd\">SELECT<\/span> emp.empno<\/pre>\n<pre><span class=\"lnum\">   8:  <\/span>     , emp.sal<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   9:  <\/span>  <span class=\"kwrd\">FROM<\/span> emp;<\/pre>\n<\/div>\n<style type=\"text\/css\">\n<p>.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }<\/style>\n<p>Let\u2019s take the code from the stored procedure step by step:<\/p>\n<p>Line 5: Define a constant to hold the maximum rows collected in a bulk collect operation<\/p>\n<table cellspacing=\"0\" cellpadding=\"2\" width=\"600\" border=\"0\">\n<tbody>\n<tr>\n<td valign=\"top\" width=\"52\"><strong>Line<\/strong><\/td>\n<td valign=\"top\" width=\"548\"><strong>Description<\/strong><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"52\">4<\/td>\n<td valign=\"top\" width=\"548\">Define a constant to hold the maximum rows collected in a bulk collect operation<br \/>\n        <br \/><em>This should of course be bigger than 5, but for demonstration purposes this is a nice value<\/em><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"52\">7-14<\/td>\n<td valign=\"top\" width=\"548\">Subtypes defined on the columns in the table<br \/>\n        <br \/><em>This is done so these types can be used in collections, parameters and variables<\/em><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"52\">17-24<\/td>\n<td valign=\"top\" width=\"548\">Collection types based on the column types<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"52\">27-37<\/td>\n<td valign=\"top\" width=\"548\">The cursor used to retrieve the data from the table<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"52\">40-47<\/td>\n<td valign=\"top\" width=\"548\">Variables based on the collection types, to catch the results from the cursor<br \/>\n        <br \/><em>In <a title=\"Oracle 11g, Siebel, PeopleSoft | Oracle, The World&#39;s Largest Enterprise Software Company\" href=\"http:\/\/www.oracle.com\/\" target=\"_blank\">Oracle<\/a> 11G you can catch the results into records instead of lots of scalar collections<\/em><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"52\">49-55<\/td>\n<td valign=\"top\" width=\"548\">A simple inline function to demonstrate the usage of the data<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"52\">59<\/td>\n<td valign=\"top\" width=\"548\">Open the cursor<br \/>\n        <br \/><em>Like you would always open it<\/em><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"52\">61<\/td>\n<td valign=\"top\" width=\"548\">Start of a simple loop<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"52\">63-70<\/td>\n<td valign=\"top\" width=\"548\">Clear out the collections<br \/>\n        <br \/><em>To make sure they only hold the retrieved data and no old data<\/em><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"52\">72-80<\/td>\n<td valign=\"top\" width=\"548\">Fetch the records from the cursor with a max of whatever the constant is set to<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"52\">82<\/td>\n<td valign=\"top\" width=\"548\">Check if any data was retrieved<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"52\">84-87<\/td>\n<td valign=\"top\" width=\"548\">A for loop to process the data just retrieved<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"52\">89-92<\/td>\n<td valign=\"top\" width=\"548\">Write all data back to the table in one pass<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"52\">95<\/td>\n<td valign=\"top\" width=\"548\">Exit the loop if we didn\u2019t retrieve the maximum number of rows<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&#160;<\/p>\n<p>The really interesting parts of this code are of course lines 72-80 where we retrieve the data from the cursor in a single pass. The <strong>BULK COLLECT<\/strong> keyword tells the engines to do a single switch and retrieve all data in one pass. The <strong>LIMIT<\/strong> keyword is included to preserve memory. Since all data will be retrieved this could lead to memory problems. Especially in a multi-user environment.<\/p>\n<p>Then there is the <strong>FORALL<\/strong> statement (lines 89-92) that writes all the data in the collections back to the table in one pass.<\/p>\n<p>Normally we would process the contents of the cursor record by record, but by adding these keywords we use the bulk operations provided by Oracle to speed up things.<\/p>\n<p>Hope this helps your understanding of the Bulk Operations in PL\/SQL<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When I was in Chicago for the OPP2008 and APEXposed event I talked to someone who seems to have trouble understanding bulk operations in PL\/SQL. I helped him out by providing a test\/demo script, that shows how it could be done. First of all, one of the most important rules of thumb that I got [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5,9],"tags":[],"class_list":["post-278","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/278","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=278"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/278\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=278"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=278"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=278"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}