| Please advice on my first PL-SQL code [message #36030] |
Tue, 30 October 2001 20:38  |
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  |
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;
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
