Re: Use of SP?

From: <fitzjarrell_at_cox.net>
Date: Mon, 17 Dec 2007 08:49:08 -0800 (PST)
Message-ID: <e57b5f5c-5f32-4e78-b9c9-678b6e9d30c4@e67g2000hsc.googlegroups.com>

On Dec 17, 2:58 am, "Hassi" <ha..._at_nospam.com> wrote:
> Hi!
>
> In our system the user can work offline by synchorinzing the server database
> to a local Oracle Express (Oracle Express 10g on windows platform) database.
> I have one table (call it pricelist) that contains salesparts with pricing
> info. In the system a user can search for a salesparts number and based on
> several criterias the correct price should be fetched from the pricelist
> table.
>
> 1. If condition A, B, C and D are satisfied fetch that row from the
> pricelist table.
> 2. If condition A, B,C and E are satisfied fetch that row from the
> pricelist table.
> 3. If condtion A,B,C are satisfied fetch that row from the pricelist table.
> 4. If condtion A, C, F are satisfied fetch that row from the pricelist
> table.
> 5. Finally fetch baseprice which always exist
>
> I'm not a DBA but my thought for this was to create a stored procedure
> returning a ref cursor and send in the input parameters (like product
> number, customer number, quantity etc) and then basically execute queries in
> number order (I mean following the criterias above) and as soon as the query
> returns records I will exit the SP and return the recordset. Is this the way
> to go to tackle tasks like this or am I walking towards a dead end?
>
> Regards
> Hassi

Why use PL/SQL when SQL*Plus works just as well:

Create tables --

SQL> create table customer(

  2  	     custnum varchar2(20),
  3  	     isdealer	     number,
  4  	     isreseller      number,
  5  	     discline	     number,
  6  	     annsalescat     number,
  7  	     lastactive      number,
  8  	     restrict	     number,
  9  	     constraint customer_pk
 10  	     primary key(custnum)

 11 );

Table created.

SQL>
SQL> create table pricelist(

  2  	     salespart	     varchar2(20),
  3  	     baseprice number(12,2),
  4  	     price   number(12,2),
  5  	     constraint pricelist_pk
  6  	     primary key(salespart)

  7 );

Table created.

SQL>
SQL> set serveroutput on size 1000000 verify off
SQL>

Load the customer table with data --

SQL> begin

  2  	     for i in 75..88 loop
  3  		     for j in 2..2000 loop
  4  			     if mod(j, 2) = 0 then
  5  				     insert into customer
  6  				     values
  7  				     (chr(i)||j, mod(j,1), mod(j,2), mod(j,3), mod(j,4),
mod(j,5), mod(j,6));
  8  			     end if;
  9  		     end loop;
 10  	     end loop;

 11 end;
 12 /

PL/SQL procedure successfully completed.

Load price table with data --

SQL>
SQL> begin

  2  	     for i in 65..74 loop
  3  		     for j in 1..1000 loop
  4  				     insert into pricelist
  5  				     values
  6  				     (chr(i)||to_char(j), 2000, 1234.56);
  7  		     end loop;
  8  	     end loop;

  9 end;
 10 /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

Query the tables for the supplied part number, customer number and customer criteria --

SQL>
SQL> accept aval prompt 'Enter value for isdealer: ' Enter value for isdealer: 1
SQL> accept bval prompt 'Enter value for isreseller: ' Enter value for isreseller: 0
SQL> accept cval prompt 'Enter value for discline: ' Enter value for discline: 0
SQL> accept dval prompt 'Enter value for annsalescat: ' Enter value for annsalescat: 4
SQL> accept eval prompt 'Enter value for lastactive: ' Enter value for lastactive: 0
SQL> accept fval prompt 'Enter value for restrict: ' Enter value for restrict: 0
SQL> accept slspart prompt 'Enter part number: ' Enter part number: A1000
SQL> accept cstnum prompt 'Enter value for custnum: ' Enter value for custnum: X2000
SQL>
SQL> select case
  2 when isdealer = &aval and isreseller = &bval and discline = &cval and annsalescat = &dval then price   3 when isdealer = &aval and isreseller = &bval and discline = &cval and lastactive = &eval then price
  4 when isdealer = &aval and isreseller = &bval and discline = &cval then price
  5 when isdealer = &aval and discline = &cval and restrict = &fval then price

  6  	     else baseprice
  7  	     end currprice

  8 from pricelist, customer
  9 where custnum = '&cstnum'
 10 and salespart = '&slspart';  

CURRPRICE

---

 

2000

SQL> This should get you started in how to write such a query without using PL/SQL. Of course it's not an exhaustive treatise on the subject, and others may devise more elegant solutions to the problem, but you can, and should, do this with regular SQL.

David Fitzjarrell Received on Mon Dec 17 2007 - 10:49:08 CST