MySQL user defined functions

Question

I have a table contains a few columns say: column_1, column_2 and column_3.

I appended an new column to the table called score. What I want to do is calculate the score based on these three columns and tune the parameters easily.

Say my score formula looks like below:

score = a * column_1 + b * column_2 + c * column_3

is it possible to create a udf or process(never used before) to easily do that?

so I have a function like getScore(a,b,c) and I could do something like:

select 
    column_1,
    column_2, 
    column_3, 
    getScore(0.5, 0.1, 0.4) as score
from table

or

update table set score = getScore(0.5, 0.1, 0.4)

Thanks!

Niko Hujanen · Accepted Answer · 2013-09-04 16:17:23Z

19

Yes.

CREATE FUNCTION `getScore`(`a` DECIMAL(12,4), `b` DECIMAL(12,4), `c` DECIMAL(12,4)) RETURNS DECIMAL(12,4)
BEGIN
    RETURN a + b + c;
END

SELECT getScore(0.3, 0.4, 0.5)
-> 1.2000

But if you need some values from the table, you need to include those as parameters too.

SELECT getScore(column1, column2, column3, 0.5, 0.1, 0.4) AS score FROM table

answered Sep 4, 2013 at 16:17

Niko Hujanen

7335 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL user defined functions

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related