MySQL: Create function with select statement + calculation

Question

I need your help. I should create a function which calculates the prescription costs of a prescription. The formula should be like this:

price of medicine x dosage x ceiling(duartion/24h)

The signature of function is:

prescription costs(mname varchar(100), dosage int, duration time)

DELIMITER $$
CREATE FUNCTION prescription_costs (mname varchar(100), dosage int, duration time) RETURNS INT
BEGIN 
    DECLARE prescription_costs INT;
    IF mname < 0 THEN 
        SET prescription_costs = 0;
    ELSEIF dosage < 0 THEN
        SET prescription_costs = 0;
    ELSEIF duration < 0 THEN
        SET prescription_costs = 0;
    ELSE 
        SET prescription_costs = price*dosage*ceiling(duration/24);
    END IF;

    RETURN (prescription_costs);
END;
$$
DELIMITER ;

So, after running I will get this window:

Instead of usage of the price from the medicine I have to use the name of the medicine which refers to its price in the table.

The table prescription is:

That means, if I put in the name of the medicine, the price should be taken for the calculation but I dont know how to.

I suppose I have to combine select statement in the create function statement.

BTW: English is not my native language!!! Please forgive me for mistakes!

xto · Accepted Answer · 2016-01-12T22:51:07.817

0

You can use SELECT...INTO statement and do something like this:

SELECT your_price_column INTO price FROM your_table WHERE Name = mname
IF price < 0 THEN 
    SET prescription_costs = 0;
...

edited Jan 12 '16 at 22:51

answered Jan 12 '16 at 22:40

xto

406
2
8

In which position in the code should I use the select...into statement? – user2379123 Jan 12 '16 at 22:44
You want to get `price` by `mname` from your table, to use it later in IF condition. So, use select...into statement before you start to using `price` – xto Jan 12 '16 at 22:55

score 0 · Answer 2 · answered Jan 12 '16 at 23:15

CREATE FUNCTION prescription_costs (mname varchar(100), dosage int, duration time) RETURNS INT
BEGIN 
    DECLARE prescription_costs INT;
    DECLARE price decimal(8,2);
    SELECT Price into price from medicine where Name=mname;
    IF price < 0 THEN 
        SET prescription_costs = 0;

Like this..?

score 0 · Answer 3 · answered Jan 15 '16 at 17:35

CREATE FUNCTION prescription_costs (mname varchar(100), dosage int, duration time) RETURNS INT
BEGIN 
    DECLARE prescription_costs INT;
    DECLARE price1 DECIMAL(8,2);
    SELECT Preis into price1 from medicine where Name = mname;
    IF price1 < 0 THEN 
        SET prescription_costs = 0;
    ELSEIF dosage < 0 THEN
        SET prescription_costs = 0;
    ELSEIF duration < '0' THEN
        SET prescription_costs = 0;
    ELSE 
        SET prescription_costs = price1*dosage*ceiling(dauer/24h);
    END IF;

    RETURN (prescription_costs);
END;

Above code is nearly finished but there is a slightly mistake which makes the calculation wrong. The ceiling() function could be the source of error. What argument doe we have? ceiling(date/24h)?

This could be e.g. ...ceiling('00:50:00'/'24:00:00') but how will this be calculated? However, I get error 1292.

Error Code: 1292. Truncated incorrect DOUBLE value: '24:00:00'

What should I do? How can I correct this?

Please help me?

MySQL: Create function with select statement + calculation

3 Answers3