MYSQL---stored procedures

Stored procedures with no parameters

CREATE PROCEDURE SP1 ()

SELECT VERSION ();

Calling a stored procedure: Call SP1//stored procedure without parameters can be called without parentheses

DELIMITER//

Stored procedure with in parameter

CREATE PROCEDURE Removeuserbyid (in uid INT UNSIGNED)

BEGIN

DELETE from users WHERE id=uid;

end//

Calling the stored procedure: Call Removeuserbyid (3);

To delete a stored procedure

DROP PROCEDURE [IF EXISTS] Removeuserbyid

Stored procedures with in and out parameters

DELIMITER//

CREATE PROCEDURE removeuserandreturnusernums (in uid INT unsigned,out usernums INT UNSIGNED)

BEGIN

DELETE from users WHERE id=uid;

SELECT COUNT (ID) from the users into Usernums;

end//

DELIMITER;

Call:

Call Removeuserandreturnusernum (@nums);

SELECT @nums;

Stored procedures with multiple Outo type parameters

DELIMITER//

CREATE PROCEDURE Removeuserbyageandreturninfos (in p_age int unsigned,out deleteusers int unsigned,out UserCount int UNSIG NED)

BEGIN

DELETE from users WHERE age=p_age;

SELECT Row_count () into deleteusers;

SELECT COUNT (ID) from the users into UserCount;

end//

DELIMITER;

Call Removeuserbyageandreturninfos (20,@A,@B);

SELECT @a,@b;

MYSQL---stored procedures