Credits

Monday, August 29, 2011

SQL’S Update and Delete Stored Procedures

In my previous post, we have created an INSERT  stored procedure to allow us entry of new record without using the normal way of adding records to our table.

We can also create update and delete stored procedures while embedding our SQL commands to do the usual works.

To call these procedures, we type:
CALL proc_customers_DELETE_byPK(1001)  // to delete customer number 1001

CALL proc_customer_UPDATE_byPK(1000,'new@gmail.com')  // to change the old email address of customer number 1000

[+/-] show/hide

/* stored procedure to update */
DELIMITER $$

DROP PROCEDURE IF EXISTS `momsdb`.`proc_customer_UPDATE_byPK` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_customer_UPDATE_byPK`(in p_customer_number int(11),in p_email varchar(50))
BEGIN
UPDATE customers
SET
email=p_email
WHERE customer_number = p_customer_number;

END $$

DELIMITER ;

/*stored procedure to delete */

DELIMITER $$

DROP PROCEDURE IF EXISTS `momsdb`.`proc_customers_DELETE_byPK` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_customers_DELETE_byPK`(in p_customer_number int(11))
BEGIN
DELETE FROM customers
WHERE customer_number = p_customer_number;
END $$

DELIMITER ;

AddThis