title: How to write a stored procedure in oracle?
body: <p>I am new to oracle so not sure about how to write stored procedure in oracle.  Currently I am googling and reading some articles/blogs on net on SP's in oracle. I am begining to get some ideas about it. Even then I have following few queries. </p>
  
  <p>What I am trying to do is, </p>
  
  <p>I have a Employee table with emp_id, Region, Division, Product, Title etc. </p>
  
  <p>I also have emp_update table with emp_id, column (what is changed), old_value, new_value and date. </p>
  
  <p>e.g. emp_update table will have a row for for emp_id = 10, which says region is changed from Asia to UK on 30 April. </p>
  
  <p>Another row for same emp which says product changed from A to B on 30 April. </p>
  
  <p>So, multiple entries for same employee and on same date. </p>
  
  <p>Now on that particular date I am running a cron job which will run this Sp to update employee table to these updates.</p>
  
  <p>e.g. On date 30 April, update employee set region = UK, Product = B where emp_id = 10. </p>
  
  <p>I want to run a single update statement for a employee with all its updates from emp_update table on that date.</p>
  
  <p>So basically, </p>
  
  <p>get all employees from emp_update with update on today.
  iterate over that employees updates, assembple them in a single update statement and then execute that update query.
  repeat for each employee.</p>
  
  <p>Please help</p>
  
  <p><strong>EDIT 1:-</strong></p>
  
  <pre><code>    CREATE OR REPLACE
  PROCEDURE SP_RUN_EMPLOYEE_UPDATES 
  IS
  
  
   CURSOR 
      c_emp 
   IS
      SELECT DISTINCT(employee_id) as employee_id FROM BI_EMPLOYEE_UPDATE WHERE EFFECTIVE_DATE = to_date('30-Apr-2012','dd-mm-yy');
  
  BEGIN
  
  FOR employee in c_emp
  
    LOOP
  
      CURSOR 
          c_emp_update 
      IS
        SELECT * FROM BI_EMPLOYEE_UPDATE WHERE employee_id = :employee.employee_id AND EFFECTIVE_DATE = to_date('30-Apr-2012','dd-mm-yy');
  
      FOR emp_update in c_emp_update
      LOOP
  
  
  
    --      dbms_output.put_line(emp_update.column_name);
      END LOOP;
  
    END LOOP;
  
  END;
  </code></pre>
  
  <p>Currently this is what I have done so far</p>