MySQL Store Procedures Need Help!

Development Web Development
1

Hello everyone,
I'm new here, I just joined recently so please don't be too harsh about the things I am not aware of yet.

Anyways, I am doing a project for a database class at my university, and I was wondering if someone could link me to a good tutorial or website reference on the subject. I'm asking you guys, because I have searched all over the web but I keep finding store procedures that use different syntax, which may be deprecated or something totally different. My professor went over it already, but I'm still really confused and she expects us to google the things she didn't cover...

Here is an example of a store procedure:

delimiter $$
create procedure getTotalStudent(INOUT total INT)
begin
select count(*) into total
from STUDENT;
end $$

set @total = 0;
call getTotal(@total);
select @total;

and Here is a store procedure that uses the cursor:

delimiter $$
create procedure listStudentName(INOUT namelist varchar(4000))
begin
declare isDone integer default 0;
declare eName varchar(255) default "";
declare name_cursor CURSOR for
select sname from student;

declare continue HANDLER
for not found set isDone = 1;

open name_cursor;
getList: LOOP

FETCH name_cursor INTO eName;
if isDone = 1 THEN
LEAVE getList;
END IF;

SET namelist = CONCAT (eName,"---\n",nameList);

END LOOP getList;

CLOSE name_cursor;
END $$

DELIMITER ;

Any help is appreciated, thanks,
diamond44

2

Welcome to the forums, People here are friendly, and very smart. surely someone will come by to help. I will also give it a look

3

So, I'm having some difficulty understanding the question. Can you clarify what you're looking for?

In this area you're not going to find something that looks like a text book. You're going to find articles of people with problems, and then you can work pieces of their solutions into yours. StackOverflow is about to be your new best friend.

4

I tried to run your second SP and noticed a difference between how you did it and how I did it. I think you need to select your value into your output variable like such:

DELIMITER $$
CREATE PROCEDURE listStudentName(INOUT namelist varchar(255))
BEGIN
    DECLARE done int DEFAULT FALSE;
    DECLARE ename varchar(255);
    DECLARE prevout varchar(255) DEFAULT "";
    DECLARE newout varchar(255) DEFAULT "";
    DECLARE cur CURSOR FOR SELECT sname FROM student;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO ename;

        IF done THEN
            LEAVE read_loop;
        END IF;

        SET newout = CONCAT(ename,"---\n",newout);

    END LOOP;

    CLOSE cur;
    SELECT newout INTO namelist;
END $$
DELIMITER ;

That test was run with mysql 5.7 in ubuntu.

5

Thanks for replying everybody :)

Basically, what I'm asking is:
Where are some good places to learn about Store Procedures used in MySQL?

Like websites, tutorials, videos, etc...

6

Stored procedures aren’t all that popular - I wouldn’t spend more time on it than necessary, you could go through the MySQL manual.

Triggers are slightly more popular, especially with mysql where they’re used to help with schema changes.

With both, I think the kicker is which permissions does the procedure use to run, the ones from a user who defined it, the ones from the user that triggered it or ran it, or has someone actually gone into mysql.* and made sure there’s a separate third account that uses it.

Operationally, stored procedures and triggers are harder to debug than just plain queries, MySQL show full processlist and show innodb status commands tend to be a bit iffy around the two.

In general, stored procedures allow you to abstract an API on top of your data. There’s nothing stopping you from writing an API in a more familiar language with better tooling like C++/Java/Go/Python/Ruby, where you can run static and dynamic analysis tools and can write and run tests to ensure your code behaves the way you want it to more easily.

Stored procedures also run right next to the data, but in today’s world of virtualization, and fast networking if you want to run something in the same cluster/or on the same machine as the database (I call it “the coprocessor model”) typically you can, all you have to do is just work on it with your admins, and typically they’ll be happy to get more performance and features out of their existing system.

For these reasons above, a lot of places just ban the use of stored procedures and triggers (sometimes with a handful of exceptions around schema changes).