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… Read More

MySQL Sample Trigger

1.Create TRIGGER BEFORE INSERT ON table Example 1: DELIMITER $$ CREATE TRIGGER trg_schema_return BEFORE INSERT ON carried FOR EACH ROW BEGIN IF (NEW.carried_return IS NULL) THEN SET NEW.carried_return = NEW.carried_ outbound; END IF; END$$ Example 2: DELIMITER $$ CREATE TRIGGER trg_used_cost BEFORE INSERT ON used… Read More

MySQL ALTER Command

1.Create Table with CTAS Command We will use the CREATE TABLE AS SELECT command to create a new table based on an existed table Example: CREATE TABLE baseora.chemical AS SELECT * FROM peps.chemical; CREATE TABLE baseora.used AS SELECT * FROM peps.used; CREATE TABLE baseora.job AS… Read More