Create function in MySQL

Question

I'm just needing a little jump start, I'm a little confused how to do this. What I need to do is pull a 'customer_id' from the table and see how many items the customer has rented. If there are no 'customer_id' then it'll return 0. I'm just no quite grasping how to do this so any help is appreciated.

Create Table:

CREATE TABLE Customer
(name VARCHAR(30) NOT NULL,
    address VARCHAR(70),
    phone CHAR(10) NOT NULL,
    customer_id INT(10) PRIMARY KEY NOT NULL);

Create Function: Have this partially started, but unsure if I'm doing it correctly.

DELIMITER $$
CREATE FUNCTION Num_Of_Rented(IN customer_id INT(10))
RETURNS INT(10)
BEGIN
    DECLARE num INT(10);
    SELECT IFNULL()
    FROM
    WHERE
    RETURN num;
END $$
DELIMITER;

This seems like a weird use case for a function. I am assuming that you have a table that relates to your Customer table giving rental history. If that is the case, why not simply query against that table? — Mike Brant
– Mike Brant, Commented Oct 20, 2014 at 19:07
Assignment wanted this in a function, querying would be easier I agree since this is simply pulling up how many items are rented but also returning 0 if there are no customer_id. @MikeBrant — user2318083
– user2318083, Commented Oct 20, 2014 at 19:19

gmiley · Accepted Answer · 2014-10-20 19:23:48Z

1

Inside your function, you need to select your value into your variable and then return your variable:

DECLARE num INT(10);
SELECT COUNT(field) INTO num
FROM table
WHERE condition;
RETURN num;

In your case:

DELIMITER $$
CREATE FUNCTION Num_Of_Rented(IN custId INT(10))
RETURNS INT(10)
BEGIN
  DECLARE num INT(10);
  SELECT COUNT(*) INTO num
  FROM Customer C
  WHERE C.customer_id = custId ;
  RETURN num;
END $$
DELIMITER;

edited Oct 20, 2014 at 19:23

answered Oct 20, 2014 at 19:10

gmiley

6,6041 gold badge15 silver badges25 bronze badges

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

7 Comments

user2318083 Over a year ago

Would the 'field' be the customer_id? That seems like the only thing that's keeping track of any rental history. So if the customer_id appears 2 times in a query it'll count 2?

gmiley Over a year ago

I just used generic names in my query, you will need to determine what you need in each place of it based off of your table structure and conditions for counting. In my above example, "field" can be any field or just * or 'x' or whatever, we are just counting records, "table" is your table name, and "condition" is any where clause that will narrow your record selection to desired subset. For example: WHERE customer_id = 2

user2318083 Over a year ago

Thanks, this helped. I just changed it right when you edited yours also. Seems like it's accepting it since it gives me 0 errors. How would I call the function to see if it's working correctly?

gmiley Over a year ago

SELECT DISTINCT(customer_id), Num_Of_Rented(customer_id) FROM Customers;

gmiley Over a year ago

or SELECT customer_id, Num_Of_Rented(customer_id) FROM Customers GROUP BY customer_id;

|

Collectives™ on Stack Overflow

Create function in MySQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related