PROGRAM 5


/*  program:  program5.sql     author:  anthony f. ortiz          */
/*  this program demonstrates an oracle pl/sql stored procedure.  */


SQL> get hw5.sql
  1  /*  this pl/sql procedure updates the agents table by adding 10 to the  */
  2  /*  percent of any agent who sum (dollars) of orders in any month is    */
  3  /*  >= 1000.                                                            */
  4  create or replace procedure update_agents as
  5  begin
  6      update agents
  7      set percent = percent + 10
  8      where exists (select aid, month, sum (dollars)
  9                    from orders
 10                    where agents.aid = orders.aid
 11                    group by aid, month
 12                    having sum (dollars) >= 1000);
 13* end;
 14  /
 
Procedure created.
 
SQL> select *
  2  from agents;
 
AID ANAME         CITY                    PERCENT
--- ------------- -------------------- ----------
a01 Smith         New York                      6
a02 Jones         Newark                        6
a03 Brown         Tokyo                         7
a04 Gray          New York                      6
a05 Otasi         Duluth                        5
a06 Smith         Dallas                        5
 
6 rows selected.
 
SQL> execute update_agents;
 
PL/SQL procedure successfully completed.
 
SQL> select *
  2  from agents;
 
AID ANAME         CITY                    PERCENT
--- ------------- -------------------- ----------
a01 Smith         New York                     16
a02 Jones         Newark                        6
a03 Brown         Tokyo                        17
a04 Gray          New York                      6
a05 Otasi         Duluth                        5
a06 Smith         Dallas                       15
 
6 rows selected.




BACK TO CS4660 PAGE.