Physical Modeling › Stored Procedures › DBMS Migration Example › Informix Stored Procedure Template Code

Informix Stored Procedure Template Code

The following is an example of an Informix stored procedure template code:

CREATE PROCEDURE OVERDUE_MOVIE_RENT(@scannum integer)as

/*This proc retrieves information on a customer based on the*/
/*barcode number on their customer ID card, locates any*/
/*outstanding movie rentals and calculates charges. Calcs*/
/*overdue charges for the application running on the store*/
/*cash register and inserts a record in the PYMT table.*/
/*The MOVIE RENTAL RECORD and PMNT tables are updated in the*/
/*stored procedure to enhance security.*/

DECLARE @renting_customer int/**/
DECLARE @master_number int/**/
DECLARE @movie_copy_number int/**/
DECLARE @rental_record_date datetime/**/
DECLARE @rental_date datetime/**/
DECLARE @due_date datetime/**/
DECLARE @rental_status varchar(1)/**/
DECLARE @payment_amount decimal(6,2)/**/
DECLARE @overdue_charge decimal(6,2);
DECLARE cursor1 CURSOR FOR
  SELECT renting_customer,master_number,movie_copy_number,
  rental_record_date,rental_date,due_date,rental_status,
  payment_amount,overdue_charge
    FROM MOVIE_RENTAL_RECORD
    WHERE renting_customer = @scannum

 open cursor1
 fetch cursor1 into @renting_customer, @master_number,@movie_copy_number, @rental_record_date, @rental_date, @due_date, @rental_status, @payment_amount, @overdue_charge

 while (@@sqlstatus = 0)
 begin
  IF  (getdate()>@due_date)
  begin
  UPDATE MOVIE_RENTAL_RECORD
  SET overdue_charge = datediff(day,getdate(),@due_date)*payment_amount
  WHERE  CURRENT OF cursor1
 end
end
INSERT INTO PMNT VALUES (0,@scannum,getdate(),0)
commit

More information:

Stored Procedures

DBMS Migration Example