Credits

Monday, August 29, 2011

SQL's Stored Procedure Plus More

When we were taught to write stored procedures in command prompt, I simply had harder time writing and editing. So, good thing that MySQL 5.0 supports stored procedure.


Creating a stored procedure requires that we call our database, thus, to call a table CUSTOMER to allow new record, we write:
/* the database for required table */
USE MOMSDB

/* to call our stored procedure procedure with the new record*/
CALL proc_customer_INSERT_byPK(103,'CHRISTY','christy@gmail.com','Calumpang','0912-345-2345')

 /* the codes inside our stored procedure*/
DELIMITER $$

DROP PROCEDURE IF EXISTS `momsdb`.`proc_customer_INSERT_byPK` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_customer_INSERT_byPK`(
in p_customer_number int(11),
in p_name text,
in p_email varchar(50),
in p_shipping_address text,
in p_phone_number text)
BEGIN
INSERT INTO customers
(customer_number,
name,
email,
shipping_address,
phone_number
)
VALUES
(
p_customer_number ,
p_name ,
p_email ,
p_shipping_address ,
p_phone_number
);


END $$

DELIMITER ;

AddThis