19th Example - Calculating Parking Fee on PL/SQL

At a shopping area usually has a parking facility equipped with automated parking system. Automatic calculation is done by a parking program. Parking fee is determined by the time of the vehicle was in the mall parking area. Calculation is obtained from the difference from output and input times that multiplies by the hourly rate. The following PL/SQL block to perform automatic parking fee calculation:

DECLARE

 jamin TIMESTAMP;

 jamout TIMESTAMP;

 jam NUMBER;

 menit NUMBER;

 waktu VARCHAR2(32);

 tarif NUMBER;

 biaya NUMBER;

BEGIN

 tarif := 1000;

 jamin := TO_TIMESTAMP('&time_input','HH24:MI');--ex 08:15

 jamout := TO_TIMESTAMP('&time_output','HH24:MI');--ex 13:45

 jam := EXTRACT(HOUR FROM jamout)-EXTRACT(HOUR FROM jamin);

 menit := EXTRACT(MINUTE FROM jamout)-EXTRACT(MINUTE FROM jamin);

 waktu := jam||' jam '||menit||' menit ';

 IF (menit >= 1) THEN

     biaya := (jam+1)*tarif;

 ELSE

     biaya := jam * tarif;

 END IF;

 DBMS_OUTPUT.PUT_LINE(waktu);

 DBMS_OUTPUT.PUT_LINE(biaya);

END;

/

PL / SQL block above has a variable with data type TIMESTAMP. TIMESTAMP data type is able to separate the date and time being the smallest unit. Before the calculation process, the conversion of the input needs to be done at a TIMESTAMP, for example TO_TIMESTAMP ('& time_input', 'HH24: MI'). To take hours of TIMESTAMP use the EXTRACT (HOUR FROM jamout). Next do the hour and minute of reduction, if minutes> = 1 then the hours will be increased by 1. Furthermore selisihjam multiply the cost of the rate to be paid will be obtained. For example, if the input time = 8:15 and output time = 13:45 then the difference is 5 hours 30 minutes at a cost of 6000.