Chapter 7
Page 174
Example 7-1.Sample Solutions to Find the Current Price for a Given Product (p_code)

Example 1a-Nonprocedural approach

SELECT price
  FROM prices
 WHERE product_code = :p_code 
  AND  SYSDATE BETWEEN date_from AND date_to

Example 1b-Procedural (preferred) approach

CREATE OR REPLACE FUNCTION price_now
    ( p_code IN price.product_code%TYPE
    ) RETURN NUMBER IS

  CURSOR c_price IS
    SELECT price
         , date_from
      INTO p_price
         , p_from
      FROM prices
     WHERE product_code = p_code
      AND  date_to     >= SYSDATE
  ORDER BY date_to;

  p_price NUMBER;
  p_from  DATE := TO_DATE('.1-Dec-4712'. 'DD-Mon-YYYY'.;

BEGIN
  OPEN  c_price;
  FETCH c_price INTO p_price, p_from;
  CLOSE c_price;
  IF p_from <= SYSDATE 
  THEN RETURN 0;
  ELSE RETURN p_price;
  END IF;
EXCEPTION
  WHEN no_data_found
  THEN RETURN 0;
END price_now;