MySQL Forums
Forum List  »  Stored Procedures

Unpredictable results from stored procedure
Posted by: Grzegorz Laszczak
Date: July 16, 2013 04:09AM

Hi all!

Mysql version: 5.0.70, 5.0.95

I have defined stored function:

DELIMITER //
CREATE DEFINER=`root`@`%` FUNCTION `cena_bylinia`(`indata` DATE, `inczas` TIME, `innumer` TEXT character set latin2, `incz_trwania` DECIMAL(10,2), `inLINIA` TEXT character set latin2, `tmpIdx` BIGINT(20) unsigned) RETURNS decimal(24,8)
READS SQL DATA
BEGIN
declare lstart decimal(10,4) default null;
declare lwolnysek decimal(24,8) default null;
declare loplata decimal(24,8) default null;
declare lzasek decimal(24,8) default null;
declare lzaokr decimal(24,8) default null;
declare llweekday text character set latin2 default convert(weekday(indata),char);
declare llhour text character set latin2 default left(convert(inczas,char),2);
declare lllength integer unsigned default char_length(innumer);
declare continue handler for not found set lstart=null ;

call _debug(concat_WS('#',tmpIdx,indata,inczas,innumer,incz_trwania,inlinia,llweekday,llhour,lllength));

select `start`, `wolnysek`, `oplata`, `zasek`, `zaokr`
into lstart, lwolnysek, loplata, lzasek, lzaokr
from `tar_work2`
where
(inLINIA like `linie`)
and (innumer like `prefix`)
and (llweekday regexp `xdzien`)
and (llhour regexp `xgodzina`)
and (indata between `waznaod` and `waznado`)
and (lllength between `mincyfr` and `maxcyfr`)
order by `waga` desc, `prefix`
limit 1
;


call _debug(concat_WS('|',tmpIdx,lstart,lwolnysek,loplata,lzasek,lzaokr));

return round(lstart+ if(incz_trwania<=lwolnysek,0.0,loplata*ceiling((incz_trwania - lwolnysek)/lzasek)),lzaokr);
END//
DELIMITER ;


And I've use this function 3 times for each row in select:
mysql> select cena_bylinia(`data`,czas,inf_wybier,cz_trwania,linia,tar_asterisk_id) as x1,
-> cena_bylinia(`data`,czas,inf_wybier,cz_trwania,linia,tar_asterisk_id) as x2,
-> cena_bylinia(`data`,czas,inf_wybier,cz_trwania,linia,tar_asterisk_id) as x3,
-> tmp1.tar_asterisk_id from tmp1
-> order by tar_asterisk_id asc
-> ;
+------------+------------+------------+-----------------+
| x1 | x2 | x3 | tar_asterisk_id |
+------------+------------+------------+-----------------+
| 0.29000000 | 0.29000000 | 0.29000000 | 296 |
| 0.29000000 | 0.29000000 | 0.29000000 | 297 |
| 0.29000000 | 0.29000000 | 0.29000000 | 300 |
| 0.29000000 | 0.29000000 | 0.29000000 | 306 |
| 0.29000000 | NULL | NULL | 321 |
| NULL | NULL | NULL | 324 |
| 0.29000000 | NULL | NULL | 332 |
| NULL | NULL | NULL | 333 |
| NULL | NULL | NULL | 339 |
| NULL | NULL | NULL | 340 |
| NULL | NULL | NULL | 355 |
| 2.32000000 | NULL | NULL | 369 |
| 0.29000000 | NULL | NULL | 410 |
| NULL | NULL | NULL | 417 |
| 0.87000000 | NULL | NULL | 463 |
| 0.29000000 | NULL | NULL | 475 |
| NULL | NULL | NULL | 502 |
+------------+------------+------------+-----------------+
17 rows in set (0.01 sec)

In each row results returned from function should be the same... The tables have data that all function calls in above selection should return non null results.

I try to redefine this function as procedure, generally with the same effect

I need some workaround, can anybody help me?

thanks
g.

[edit]
The _debug function is not necessary of course, It's for debugging porpouses only...



Edited 1 time(s). Last edit at 07/16/2013 04:11AM by Grzegorz Laszczak.

Options: ReplyQuote


Subject
Views
Written By
Posted
Unpredictable results from stored procedure
2430
July 16, 2013 04:09AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.