Converting a MySQL function into Oracle PL/SQL

Question

I am trying to port a MySQL database into Oracle 12c there are a number functions that I need to convert since I am new to Oracle and PL/SQL in particular, I would really appreciate your help in conversion :

CREATE FUNCTION get_customer_balance(p_customer_id INT
                   , p_effective_date DATETIME) 
          RETURNS DECIMAL(5,2)
    DETERMINISTIC
    READS SQL DATA
BEGIN


  DECLARE v_rentfees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY
  DECLARE v_overfees INTEGER;      #LATE FEES FOR PRIOR RENTALS
  DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY

  SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
    FROM film, inventory, rental
    WHERE film.film_id = inventory.film_id
      AND inventory.inventory_id = rental.inventory_id
      AND rental.rental_date <= p_effective_date
      AND rental.customer_id = p_customer_id;

  SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
        ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
    FROM rental, inventory, film
    WHERE film.film_id = inventory.film_id
      AND inventory.inventory_id = rental.inventory_id
      AND rental.rental_date <= p_effective_date
      AND rental.customer_id = p_customer_id;


  SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
    FROM payment

    WHERE payment.payment_date <= p_effective_date
    AND payment.customer_id = p_customer_id;

  RETURN v_rentfees + v_overfees - v_payments;
END

Stored code conversion is a big job; it's more than you can expect from SO contributors. A couple of hints. 1. IFNULL() maps to NVL(). 2. You can subtract Oracle date/time data directly, and it comes out as the number of days difference. — O. Jones
– O. Jones, Commented Aug 23, 2014 at 11:26

user3658506 · Accepted Answer · 2014-08-23 17:06:39Z

2

EDITED : With corrections and it should work

CREATE OR REPLACE FUNCTION get_customer_balance(p_customer_id INT, p_effective_date DATE) RETURN NUMBER
DETERMINISTIC
    IS

  v_rentfees NUMBER(5,2); --FEES PAID TO RENT THE VIDEOS INITIALLY
  v_overfees INTEGER;       --LATE FEES FOR PRIOR RENTALS
  v_payments NUMBER(5,2); --SUM OF PAYMENTS MADE PREVIOUSLY


    BEGIN
        SELECT  NVL(
                    SUM(film.rental_rate),
                    0
                  )
          INTO  v_rentfees
          FROM  film,
                inventory,
                rental
          WHERE film.film_id = inventory.film_id
            AND inventory.inventory_id = rental.inventory_id
            AND rental.rental_date <= p_effective_date
            AND rental.customer_id = p_customer_id;
        SELECT  SUM(
                    CASE
                      WHEN TRUNC(rental.return_date) - TRUNC(rental.rental_date) > film.rental_duration
                        THEN (TRUNC(rental.return_date) - TRUNC(rental.rental_date)) - film.rental_duration
                      ELSE 0
                    END
                  )
          INTO  v_overfees
          FROM  rental,
                inventory,
                film
          WHERE film.film_id = inventory.film_id
            AND inventory.inventory_id = rental.inventory_id
            AND rental.rental_date <= p_effective_date
            AND rental.customer_id = p_customer_id;
        SELECT  NVL(
                    SUM(payment.amount),
                    0
                  )
          INTO  v_payments
          FROM payment
          WHERE payment.payment_date <= p_effective_date
            AND payment.customer_id = p_customer_id;
        RETURN v_rentfees + v_overfees - v_payments;
END;
/

edited Aug 23, 2014 at 17:06

answered Aug 23, 2014 at 12:15

user3658506

6334 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TonyaLepski Over a year ago

Thanks ! but I am getting the following error :GET_CUSTOMER_BALANCE FUNCTION 1 2 55 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % with default character

Collectives™ on Stack Overflow

Converting a MySQL function into Oracle PL/SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related