Mark Farnham Oaktable World 2015

-- =====================================================================
-- FILE NAME     : u_inventory_04_pkg.sql
-- AUTHOR        : Mark W. Farnham
-- DATE CREATED  : 2015_06_05 (YYYY_MM_DD)
-- APPLICATION   : demonstrate some packaged procedure advantages
-- VERSION       : 0.0
--
-- DESCRIPTION   : Procedures to demonstrate increasing levels of sophistication
--                 with a goal of superior performance over commands submitted
--                 from clients
-- =====================================================================
-- MODIFICATION HISTORY
-- =====================================================================
-- DATE         VERSION  Author     CHANGE DESCRIPTION
-- ---------------------------------------------------------------------
-- 2015_06_05       0.0     mwf     Creation
-- =====================================================================
create or replace
package sell_pkg is
--
-- Package global values
--
   
--
-- Package procedures
--
   procedure minus_inventory_p1(p_skucs number, p_quantity number);
   function sell_and_ship(p_cust_id number,p_skucs number,p_quantity number) return number;
   function enough_left(p_skucs number,p_quantity number) return number;
   function sell_and_ship2(p_cust_id number,p_skucs number,p_quantity number) return number;
   function enough_left2(p_skucs_ri urowid,p_quantity number) return number;
   procedure get_skucs_ri(p_skucs number,p_status out number,p_skucs_ri out urowid);
--
end sell_pkg;
/
show errors
create or replace
package body sell_pkg is
--
procedure get_skucs_ri(p_skucs in number, p_status out number, p_skucs_ri out urowid) is
--
begin
   p_status := 0;
--
   select i.rowid into p_skucs_ri 
      from inventory i
      where i.skucs = p_skucs;
--  
exception
   when no_data_found then
      p_status := -1; /* no such sku color size */
   when others then
      p_status := -99; /* unidentified error */
--
end get_skucs_ri;
--

function enough_left(p_skucs number,p_quantity number) return number is
--
   v_status           number;
   v_remaining_onhand number;
--
begin
   if p_quantity < 0 then
      v_status := -98; /* let's not try to sell negative quantities */
      return v_status;
   end if;
--
   select i.onhand into v_remaining_onhand 
      from inventory i
      where i.skucs = p_skucs;
   if v_remaining_onhand < p_quantity then
      v_status := -2; /* not enough on hand */
   else
      v_status := 0;
   end if;
--
   return v_status;
--
exception
   when no_data_found then
      v_status := -1; /* no such sku color size */
      return v_status;
   when others then
      v_status := -99; /* unidentified error */
      return v_status;
end enough_left;
--
function enough_left2(p_skucs_ri urowid,p_quantity number) return number is
--
   v_status           number;
   v_remaining_onhand number;
--
begin
   if p_quantity < 0 then
      v_status := -98; /* let's not try to sell negative quantities */
      return v_status;
   end if;
--
   select i.onhand into v_remaining_onhand 
      from inventory i
      where i.rowid = p_skucs_ri;
   if v_remaining_onhand < p_quantity then
      v_status := -2; /* not enough on hand */
   else
      v_status := 0;
   end if;
--
   return v_status;
--
exception
   when no_data_found then
      v_status := -1; /* no such sku color size */
      return v_status;
   when others then
      v_status := -99; /* unidentified error */
      return v_status;
end enough_left2;
--
procedure minus_inventory_p1(p_skucs number, p_quantity number) is
--
begin
   update inventory i set i.onhand = i.onhand - p_quantity
      where i.skucs = p_skucs;
   commit;
--
end minus_inventory_p1;
--
function sell_and_ship(p_cust_id number,p_skucs number,p_quantity number) return number is
--
   v_status           number;
   v_remaining_onhand number;
   v_ship_id          number;
   v_skucs            number;
   v_name             customer.name%type;
   v_shipto_addr1     customer.shipto_addr1%type;
   v_shipto_addr2     customer.shipto_addr2%type;
   v_shipto_city      customer.shipto_city%type;
   v_shipto_state     customer.shipto_state%type;
   v_shipto_postal    customer.shipto_postal%type;
   v_shipping_id      number;
--
begin
--
   v_status := enough_left (p_skucs, p_quantity);
   if v_status < 0 then
      return v_status; /* bad sku, not enough, neg. qty, or misc. error; don't bother with rest */
   end if;
--
   v_status := -3;     /* interpret for not found, other errors with respect to customer */
--
   select 
      c.name, c.shipto_addr1, c.shipto_addr2, c.shipto_city, c.shipto_state, c.shipto_postal
   into
      v_name, v_shipto_addr1, v_shipto_addr2, v_shipto_city, v_shipto_state, v_shipto_postal
   from customer c
      where c.cust_id = p_cust_id;
--
   v_status := -4;
   v_ship_id := shipping_id.nextval; /* if we get this far it's worth burning an id */
--
-- debatable race condition re-test for quantity; could select for update (business decision)
-- maybe not worth it if it rarely happens, can check for negative onhand periodically off shipping
--
   v_status := enough_left (p_skucs, p_quantity);
   if v_status < 0 then
      v_status := v_status - 10000;
      return v_status; /* might want to log losing race condition while getting other information  */
   end if;
--
-- only now do we actually start a transaction (we can debate whether fetching the sequence started a transaction
-- but it sure does not impede concurrency unless the sequences are very badly defined.)
--
   v_status := -5;   
   update inventory i set i.onhand = i.onhand - p_quantity
      where i.skucs = p_skucs;
--
   insert into shipping s
      (s.ship_id, s.skucs, s.quantity, s.name, s.shipto_addr1, s.shipto_addr2, s.shipto_city, s.shipto_state, s.shipto_postal)
   values
      (v_ship_id, p_skucs, p_quantity, v_name, v_shipto_addr1, v_shipto_addr2, v_shipto_city, v_shipto_state, v_shipto_postal);
--  
commit;
--
-- arguably could check that at least zero of skucs remain else log a warning that some sale around this time cannot be completed
-- contrast scalability with actually doing select for update (this is a business decision).
--
v_status := 0;
return v_status;
--
v_status := 0;
--
exception
   when no_data_found then
      v_status := -1; /* no such sku color size */
      return v_status;
   when others then
      v_status := -99; /* unidentified error */
      return v_status;
--
end sell_and_ship;
--
function sell_and_ship2(p_cust_id number,p_skucs number,p_quantity number) return number is
--
   v_status           number;
   v_remaining_onhand number;
   v_ship_id          number;
   v_skucs_ri         urowid;
   v_name             customer.name%type;
   v_shipto_addr1     customer.shipto_addr1%type;
   v_shipto_addr2     customer.shipto_addr2%type;
   v_shipto_city      customer.shipto_city%type;
   v_shipto_state     customer.shipto_state%type;
   v_shipto_postal    customer.shipto_postal%type;
   v_shipping_id      number;
--
begin
--
   get_skucs_ri(p_skucs,v_status,v_skucs_ri);
   if v_status < 0 then
      return v_status; /* bad sku or unknown */
   end if;
--
   v_status := enough_left2 (v_skucs_ri, p_quantity);
   if v_status < 0 then
      return v_status; /* not enough, neg. qty, or misc. error; don't bother with rest */
   end if;
--
   v_status := -3;     /* interpret for not found, other errors with respect to customer */
--
   select 
      c.name, c.shipto_addr1, c.shipto_addr2, c.shipto_city, c.shipto_state, c.shipto_postal
   into
      v_name, v_shipto_addr1, v_shipto_addr2, v_shipto_city, v_shipto_state, v_shipto_postal
   from customer c
      where c.cust_id = p_cust_id;
--
   v_status := -4;
   v_ship_id := shipping_id.nextval; /* if we get this far it's worth burning an id */
--
-- debatable race condition re-test for quantity; could select for update (business decision)
-- maybe not worth it if it rarely happens, can check for negative onhand periodically off shipping
--
   v_status := enough_left2 (v_skucs_ri, p_quantity);
   if v_status < 0 then
      v_status := v_status - 10000;
      return v_status; /* might want to log losing race condition while getting other information  */
   end if;
--
-- only now do we actually start a transaction (we can debate whether fetching the sequence started a transaction
-- but it sure does not impede concurrency unless the sequences are very badly defined.)
--
   v_status := -5;   
   update inventory i set i.onhand = i.onhand - p_quantity
      where i.rowid = v_skucs_ri;
--
   insert into shipping s
      (s.ship_id, s.skucs, s.quantity, s.name, s.shipto_addr1, s.shipto_addr2, s.shipto_city, s.shipto_state, s.shipto_postal)
   values
      (v_ship_id, p_skucs, p_quantity, v_name, v_shipto_addr1, v_shipto_addr2, v_shipto_city, v_shipto_state, v_shipto_postal);
--  
commit;
--
-- arguably could check that at least zero of skucs remain else log a warning that some sale around this time cannot be completed
-- contrast scalability with actually doing select for update (this is a business decision).
--
v_status := 0;
return v_status;
--
v_status := 0;
--
exception
   when no_data_found then
      v_status := -1; /* no such sku color size */
      return v_status;
   when others then
      v_status := -99; /* unidentified error */
      return v_status;
--
end sell_and_ship2;
--
end sell_pkg;
/
show errors

Comments