Sample commands from
http://datacharmer.org/presentations/mysqluc2007/stored_routines_DBA.ppt
delimiter //
CREATE PROCEDURE how_is_it (IN x INT)
BEGIN
IF (x > 5) THEN
SELECT CONCAT(x, " is higher") as answer;
ELSE
SELECT CONCAT(x, " is lower") as answer;
END IF;
END
//
delimiter ;
CALL how_is_it(6);
CALL how_is_it(2);
delimiter //
CREATE FUNCTION is_bigger (x INT)
RETURNS CHAR(3)
BEGIN
IF (x > 5) THEN
RETURN 'YES';
ELSE
RETURN 'NO';
END IF;
END
//
delimiter ;
SELECT is_bigger(6);
SELECT is_bigger(2);
use test;
create table salary (emp_no INT, work_done INT, bonus REAL) ;
delimiter //
CREATE TRIGGER salary_bi
BEFORE INSERT ON salary
FOR EACH ROW
BEGIN
CASE
WHEN new.work_done > 10
THEN SET new.bonus = 5000;
WHEN new.work_done > 5
THEN SET new.bonus = 2500;
WHEN new.work_done > 2
THEN SET new.bonus = 1000;
ELSE
SET new.bonus = 0;
END CASE;
END
//
delimiter ;
insert into salary (emp_no, work_done,bonus) values (1,15,0);
insert into salary (emp_no, work_done,bonus) values (2,5,0);
select emp_no,work_done,bonus from salary;
delimiter //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t;
END;
//
delimiter ;
CALL simpleproc(@a);
## Stored cursors
# http://dev.mysql.com/doc/refman/5.0/en/cursors.html
delimiter //
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
OPEN cur2;
REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
CLOSE cur2;
END
//
delimiter ;
# Dynamic SQL
set @query = 'select * from salary where emp_no= ? ';
prepare myStat from @query;
set @emp = 1;
EXECUTE myStat USING @emp;