Home » SQL & PL/SQL » SQL & PL/SQL » Please advice on my first PL-SQL code
Please advice on my first PL-SQL code [message #36030] Tue, 30 October 2001 20:38 Go to next message
Henning
Messages: 6
Registered: October 2001
Junior Member
I have now succesfully created my first PL-SQL script.

I would like anyone to give me some comments about the code. If there something I should have done differently to make it faster?

CREATE OR REPLACE PROCEDURE P_CreateTarif
AS
CURSOR Tariff_Cur IS
SELECT rowid, Date_Charging sd, Time_Charging st
FROM Tariffs
FOR UPDATE;

Tariff_rec Tariff_Cur%ROWTYPE;
chTariff varchar(1);
dtDate date;
intHour number;

BEGIN
FOR Tariff_rec IN Tariff_Cur LOOP
dtDate := to_date(Tariff_rec.sd || Tariff_rec.st ,'yyyymmddhh24miss');
chTariff := 'O';
if to_char(dtdate,'d') < 6 then
intHour := to_char(dtdate,'hh24');
if intHour < 7 then
chTariff := 'O';
elsif intHour < 13 then
chTariff := 'M';
elsif intHour < 19 then
chTariff := 'A';
end if;
end if;
UPDATE Tariffs set TariffPeriod = chTariff, startdatetime = dtDate WHERE rowid = Tariff_rec.rowid;
END LOOP;
COMMIT;
END;

Regards
Henning

----------------------------------------------------------------------
Re: Please advice on my first PL-SQL code [message #36034 is a reply to message #36030] Wed, 31 October 2001 03:52 Go to previous message
Phenoracle
Messages: 35
Registered: March 2001
Member
Hi,

Just a quick look at it.

CURSOR Tariff_Cur IS
SELECT rowid, to_date(Date_Charging || Time_Charging ,'yyyymmddhh24miss') sd
FROM Tariffs
FOR UPDATE;

Tariff_rec Tariff_Cur%ROWTYPE;
chTariff varchar(1);
dtDate date;
intHour number;

BEGIN
FOR Tariff_rec IN Tariff_Cur LOOP
--
--- usually letting SQl handle this runs better
--- but test out the alternatives.
--
/*dtDate := to_date(Tariff_rec.sd || Tariff_rec.st ,'yyyymmddhh24miss');
*/
chTariff := 'O';

IF to_char(dtdate,'d') < 6 THEN
intHour := to_char(dtdate,'hh24');
IF intHour < 7 THEN
chTariff := 'O';
ELSIF intHour < 13 THEN
chTariff := 'M';
ELSIF intHour < 19 THEN
chTariff := 'A';
END IF;

END IF;

UPDATE Tariffs
SET TariffPeriod = chTariff, startdatetime = dtDate
WHERE rowid = Tariff_rec.rowid;

--
--- Depending on the amount of data manipulated
--- then consider a commiting after a certain
--- amount of rows.
--

END LOOP;
COMMIT;



----------------------------------------------------------------------
Previous Topic: to create a table structure from an existing structure
Next Topic: Spooling in SQLPlus 3.3
Goto Forum:
  


Current Time: Mon Oct 26 14:10:15 CDT 2020