MySql - Creating Stored Function and Calling It

Question

I need help creating this particular stored function and call it by using the single select statement. Below are the questions with my answer. I think I got the first part right but I'm not sure. Any suggestions/advice? For the second question (part b), I'm calling the function incorrectly and can't get it to appear as specified in question/part b. Any advice? I would really appreciate the assistance.

Part A) Create a stored function called get_customer_balance which will return a customer’s balance from the membership table by passing in a membership number.

My Answer:

DELIMITER $$
CREATE FUNCTION get_customer_balance (membershipNo INT) 
RETURNS dec 
DETERMINISTIC
BEGIN
DECLARE CustBal dec;
SET CustBal = 0;
SELECT balance INTO CustBal  
FROM membership
WHERE membership_id = membershipNo;
RETURN CustBal;
END$$
DELIMITER ;

Part B question

Membership Table. This is the original table of the problem (for reference guide)

answer is in this thread link

nowRails
– nowRails

2015-12-04 21:06:45 +00:00
Commented Dec 4, 2015 at 21:06 — nowRails
– nowRails, Commented Dec 4, 2015 at 21:06

Drew · Accepted Answer · 2015-12-04 21:46:36Z

0

create table membership
(   membership_id int primary key,
    balance decimal(10,2) not null
);

insert membership(membership_id,balance) values (1,1),(102,11),(103,109.25);

select membership_id,format(get_customer_balance(membership_id),2) as theBalance 
from membership 
where membership_id=102;

+---------------+------------+
| membership_id | theBalance |
+---------------+------------+
|           102 | 11.00      |
+---------------+------------+

Mysql Manual page on Create Proc and Functions

Calling your user defined function (UDF) would be like calling any built-in function. Even if it involved joins on tables with aliases (which the above does not show).

A much better example would be one in which there are two tables. A membership table, and a transaction table that needs summed. But that wasn't your schema.

Would you like to see such a thing?

edited Dec 4, 2015 at 21:46

answered Dec 4, 2015 at 21:06

Drew

25k10 gold badges47 silver badges81 bronze badges

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

5 Comments

Batman101 Over a year ago

Thanks Drew! But I notice your first example is creating a table. I need to create a function. My table is already in the database.

Drew Over a year ago

I am using the function you created, and showed the data you did not have scripted. So I did not feel the need to re-show your function. I was focusing on the part of how to call it ... which was your question afterall, no ?

Drew Over a year ago

I don't get paid enough for this :P

Drew Over a year ago

when you are happy with Answers, give them a Green check mark signifying they are answered. Even if that means waiting a week or two

Batman101 Over a year ago

Will do. But there's an issue. The number 11 doesn't appear as 11.00 (with zeros) like displayed in the part b problem. Any idea why that is so? Is it because I might've done something wrong with my statement in part a?

Collectives™ on Stack Overflow

MySql - Creating Stored Function and Calling It

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related