MySQL Sample Procedure

1.Create a Procedure
DELIMITER $$

DROP PROCEDURE IF EXISTS `new_drive` $$

CREATE PROCEDURE `new_drive` (IN my_emp_num VARCHAR(30), IN my_truck_num VARCHAR(30), IN my_drive_date VARCHAR(30))
BEGIN
IF EXISTS (SELECT * FROM employee WHERE emp_num = my_emp_num) THEN
SELECT ‘The Employee is already Existed’;
ELSE IF EXISTS (SELECT * FROM truck WHERE truck_num = my_truck_num) THEN
SELECT ‘The Truck is already Existed’;
ELSE IF EXISTS (SELECT * FROM drive WHERE drive_date = my_drive_date AND truck_num = my_truck_num AND emp_num = my_emp_num) THEN
SELECT ‘The Drive Date is already Existed’;
ELSE
INSERT INTO drive VALUES(‘101010’, my_emp_num, my_truck_num, my_drive_date, ‘schedule’);
END IF;
END$$

DELIMITER;

Add Comment