![Page 1: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/1.jpg)
Using Stored Routines for MySQL Administration
Giuseppe Maxia, [email protected] Developer, MySQL AB
![Page 2: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/2.jpg)
Giuseppe Maxia
• MySQL QA Developer
• Based in Sardinia, Italy
![Page 3: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/3.jpg)
Giuseppe Maxia
according to Google Maps, excellent swimmer
![Page 4: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/4.jpg)
Giuseppe Maxia
according to Google Maps, excellent swimmer
![Page 5: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/5.jpg)
Agenda
• Overview
• Stored routines language
• Execution environment
• Stored routines and INFORMATION_SCHEMA
• Using dynamic SQL
• Event scheduler
• Stored routines compared to client side languages
• Resources
• Q&A
![Page 6: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/6.jpg)
Agenda
• Overview
• Stored routines language
• Execution environment
• Stored routines and INFORMATION_SCHEMA
• Using dynamic SQL
• Event scheduler
• Stored routines compared to client side languages
• Resources
• Q&A
![Page 7: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/7.jpg)
MySQL Stored routines Overview
![Page 8: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/8.jpg)
• Stored on server
MySQL Stored routines Overview
![Page 9: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/9.jpg)
• Stored on server
• Belong to database
MySQL Stored routines Overview
![Page 10: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/10.jpg)
• Stored on server
• Belong to database
• based on standard SQL specification
MySQL Stored routines Overview
![Page 11: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/11.jpg)
Agenda
• Overview
• Stored routines language
• Execution environment
• Stored routines and INFORMATION_SCHEMA
• Using dynamic SQL
• Event scheduler
• Stored routines compared to client side languages
• Resources
• Q&A
![Page 12: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/12.jpg)
MySQL Stored routines language
![Page 13: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/13.jpg)
• PROCEDURE
MySQL Stored routines language
![Page 14: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/14.jpg)
• PROCEDURE
• FUNCTION
MySQL Stored routines language
![Page 15: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/15.jpg)
• PROCEDURE
• FUNCTION
• TRIGGER
MySQL Stored routines language
![Page 16: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/16.jpg)
MySQL Stored routines language - procedure
![Page 17: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/17.jpg)
• PARAMETERS
MySQL Stored routines language - procedure
![Page 18: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/18.jpg)
• PARAMETERS
• IN
MySQL Stored routines language - procedure
![Page 19: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/19.jpg)
• PARAMETERS
• IN
• OUT
MySQL Stored routines language - procedure
![Page 20: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/20.jpg)
• PARAMETERS
• IN
• OUT
• INOUT
MySQL Stored routines language - procedure
![Page 21: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/21.jpg)
• PARAMETERS
• IN
• OUT
• INOUT
• may return one or more data sets
MySQL Stored routines language - procedure
![Page 22: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/22.jpg)
• PARAMETERS
• IN
• OUT
• INOUT
• may return one or more data sets
• can use dynamic SQL
MySQL Stored routines language - procedure
![Page 23: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/23.jpg)
MySQL Stored routines language - function
![Page 24: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/24.jpg)
• only input PARAMETERS
MySQL Stored routines language - function
![Page 25: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/25.jpg)
• only input PARAMETERS
• MUST return one value of a given type
MySQL Stored routines language - function
![Page 26: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/26.jpg)
• only input PARAMETERS
• MUST return one value of a given type
• can't use dynamic SQL
MySQL Stored routines language - function
![Page 27: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/27.jpg)
• only input PARAMETERS
• MUST return one value of a given type
• can't use dynamic SQL
• can't return data sets
MySQL Stored routines language - function
![Page 28: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/28.jpg)
MySQL Stored routines language - trigger
![Page 29: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/29.jpg)
• associated to table events (INSERT, UPDATE,DELETE)
MySQL Stored routines language - trigger
![Page 30: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/30.jpg)
• associated to table events (INSERT, UPDATE,DELETE)
• its parameters depend on the event
MySQL Stored routines language - trigger
![Page 31: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/31.jpg)
• associated to table events (INSERT, UPDATE,DELETE)
• its parameters depend on the event
• does not return anything
MySQL Stored routines language - trigger
![Page 32: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/32.jpg)
• associated to table events (INSERT, UPDATE,DELETE)
• its parameters depend on the event
• does not return anything
• can't use dynamic SQL
MySQL Stored routines language - trigger
![Page 33: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/33.jpg)
• associated to table events (INSERT, UPDATE,DELETE)
• its parameters depend on the event
• does not return anything
• can't use dynamic SQL
• can't return data sets
MySQL Stored routines language - trigger
![Page 34: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/34.jpg)
MySQL Stored routines language
![Page 35: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/35.jpg)
• CREATE PROCEDURE
MySQL Stored routines language
![Page 36: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/36.jpg)
• CREATE PROCEDURE
• CREATE FUNCTION
MySQL Stored routines language
![Page 37: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/37.jpg)
• CREATE PROCEDURE
• CREATE FUNCTION
• BEGIN .. END blocks
MySQL Stored routines language
![Page 38: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/38.jpg)
• CREATE PROCEDURE
• CREATE FUNCTION
• BEGIN .. END blocks
• IF ... THEN ... ELSE ... END IF
MySQL Stored routines language
![Page 39: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/39.jpg)
• CREATE PROCEDURE
• CREATE FUNCTION
• BEGIN .. END blocks
• IF ... THEN ... ELSE ... END IF
• CASE ... THEN ... THEN ... ELSE ... END CASE
MySQL Stored routines language
![Page 40: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/40.jpg)
• CREATE PROCEDURE
• CREATE FUNCTION
• BEGIN .. END blocks
• IF ... THEN ... ELSE ... END IF
• CASE ... THEN ... THEN ... ELSE ... END CASE
• WHILE ... END WHILE
MySQL Stored routines language
![Page 41: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/41.jpg)
• CREATE PROCEDURE
• CREATE FUNCTION
• BEGIN .. END blocks
• IF ... THEN ... ELSE ... END IF
• CASE ... THEN ... THEN ... ELSE ... END CASE
• WHILE ... END WHILE
• LOOP ... END LOOP
MySQL Stored routines language
![Page 42: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/42.jpg)
• CREATE PROCEDURE
• CREATE FUNCTION
• BEGIN .. END blocks
• IF ... THEN ... ELSE ... END IF
• CASE ... THEN ... THEN ... ELSE ... END CASE
• WHILE ... END WHILE
• LOOP ... END LOOP
• DECLARE
MySQL Stored routines language
![Page 43: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/43.jpg)
MySQL Stored routines language
![Page 44: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/44.jpg)
• HANDLERS
MySQL Stored routines language
![Page 45: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/45.jpg)
• HANDLERS
• CURSORS
MySQL Stored routines language
![Page 46: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/46.jpg)
MySQL Stored routines language - procedure
![Page 47: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/47.jpg)
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
MySQL Stored routines language - procedure
![Page 48: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/48.jpg)
MySQL Stored routines language - procedure
![Page 49: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/49.jpg)
CALL how_is_it(6);+-------------+| answer |+-------------+| 6 is higher | +-------------+
CALL how_is_it(2);+------------+| answer |+------------+| 2 is lower | +------------+
MySQL Stored routines language - procedure
![Page 50: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/50.jpg)
MySQL Stored routines language - function
![Page 51: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/51.jpg)
CREATE FUNCTION is_bigger (x INT)RETURNS CHAR(3)BEGIN IF (x > 5) THEN RETURN 'YES'; ELSE RETURN 'NO'; END IF;END
MySQL Stored routines language - function
![Page 52: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/52.jpg)
MySQL Stored routines language - function
![Page 53: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/53.jpg)
SELECT is_bigger(6);+--------------+| is_bigger(6) |+--------------+| YES | +--------------+
SELECT is_bigger(2);+--------------+| is_bigger(2) |+--------------+| NO | +--------------+
MySQL Stored routines language - function
![Page 54: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/54.jpg)
MySQL Stored routines language - triggers
![Page 55: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/55.jpg)
CREATE TRIGGER salary_biBEFORE INSERT ON salaryFOR EACH ROWBEGIN 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
MySQL Stored routines language - triggers
![Page 56: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/56.jpg)
MySQL Stored routines language - triggers
![Page 57: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/57.jpg)
insert into salary (emp_no, work_done,bonus) values (1,15,0);Query OK, 1 row affected (0.00 sec)
insert into salary (emp_no, work_done,bonus) values (2,5,0);Query OK, 1 row affected (0.00 sec)
select emp_no,work_done,bonus from salary;+--------+-----------+-------+| emp_no | work_done | bonus |+--------+-----------+-------+| 1 | 15 | 5000 | | 2 | 5 | 1000 | +--------+-----------+-------+
MySQL Stored routines language - triggers
![Page 58: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/58.jpg)
MySQL Stored routines language - cursors
continuehandler
flag variable
values for cursor
columns
values for cursor
columns
variables for cursor
columns
cursor
![Page 59: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/59.jpg)
MySQL Stored routines language - cursors
open
loop
check flag
fetch
process data
close
handlersets
flag variable
variablesvariables
variables
![Page 60: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/60.jpg)
MySQL Stored routines language - cursors
![Page 61: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/61.jpg)
DECLARE done BOOLEAN DEFAULT FALSE;DECLARE my_bonus INT; DECLARE get_it CURSOR FOR SELECT bonus FROM salary;DECLARE CONTINUE HANDLER FOR SQLSTATE NOT FOUND SET done = TRUE;
OPEN CURSOR get_it;GETTING:LOOP FETCH get_it INTO my_bonus; IF done THEN LEAVE GETTING; END IF # do something with my_bonus END LOOP;CLOSE get_it;
MySQL Stored routines language - cursors
![Page 62: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/62.jpg)
Agenda
• Overview
• Stored routines language
• Execution environment
• Stored routines and INFORMATION_SCHEMA
• Using dynamic SQL
• Event scheduler
• Stored routines compared to client side languages
• Resources
• Q&A
![Page 63: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/63.jpg)
Execution environment
![Page 64: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/64.jpg)
Execution environment
• DEFINER
![Page 65: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/65.jpg)
Execution environment
• DEFINER
• SQL MODE stored at definition time
![Page 66: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/66.jpg)
Execution environment
• DEFINER
• SQL MODE stored at definition time
• SESSION VARIABLES shared with the external environment
![Page 67: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/67.jpg)
Execution Environment
![Page 68: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/68.jpg)
SET SQL_MODE = 'STRICT_ALL_TABLES';
CREATE PROCEDURE put (e int, w int) INSERT INTO salary (emp_no, work_done) values (e,w);ALTER TABLE salary MODIFY emp_no TINYINT;
SELECT definer, sql_mode,security_type FROM information_schema.routines WHERE routine_name = 'put' AND routine_schema = SCHEMA()\G
definer: gmax@% sql_mode: STRICT_ALL_TABLESsecurity_type: DEFINER
Execution Environment
![Page 69: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/69.jpg)
Execution Environment
![Page 70: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/70.jpg)
SET SQL_MODE = '';
INSERT INTO SALARY (emp_no,work_done) VALUES (1,10);Query OK, 1 row affected (0.00 sec)
call put(1,10);Query OK, 1 row affected (0.00 sec)
Execution Environment
![Page 71: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/71.jpg)
Execution Environment
![Page 72: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/72.jpg)
SET SQL_MODE = '';
INSERT INTO SALARY (emp_no,work_done) VALUES (300,10);Query OK, 1 row affected, 1 warning (0.00 sec)Warning (Code 1264): Out of range value adjusted for column 'emp_no' at row 1
select emp_no,work_done,bonus from salary;+--------+-----------+-------+| emp_no | work_done | bonus |+--------+-----------+-------+| 1 | 10 | 2500 | | 1 | 10 | 2500 | | 127 | 10 | 2500 | +--------+-----------+-------+
Execution Environment
![Page 73: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/73.jpg)
Execution Environment
![Page 74: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/74.jpg)
SET SQL_MODE = '';
DELETE FROM salary WHERE emp_no= 127;
call put(301,10);ERROR 1264 (22003): Out of range value adjusted for column 'emp_no' at row 1
select emp_no,work_done,bonus from salary;+--------+-----------+-------+| emp_no | work_done | bonus |+--------+-----------+-------+| 1 | 10 | 2500 | | 1 | 10 | 2500 | +--------+-----------+-------+
Execution Environment
![Page 75: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/75.jpg)
Agenda
• Overview
• Stored routines language
• Execution environment
• Stored routines and INFORMATION_SCHEMA
• Using dynamic SQL
• Event scheduler
• Stored routines compared to client side languages
• Resources
• Q&A
![Page 76: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/76.jpg)
INFORMATION SCHEMA = METADATA !
![Page 77: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/77.jpg)
INFORMATION SCHEMA
![Page 78: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/78.jpg)
INFORMATION SCHEMA
• metadata about (among other things)
![Page 79: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/79.jpg)
INFORMATION SCHEMA
• metadata about (among other things)
• tables attributes
![Page 80: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/80.jpg)
INFORMATION SCHEMA
• metadata about (among other things)
• tables attributes
• columns
![Page 81: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/81.jpg)
INFORMATION SCHEMA
• metadata about (among other things)
• tables attributes
• columns
• relationships
![Page 82: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/82.jpg)
INFORMATION SCHEMA
• metadata about (among other things)
• tables attributes
• columns
• relationships
• users
![Page 83: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/83.jpg)
INFORMATION SCHEMA
• metadata about (among other things)
• tables attributes
• columns
• relationships
• users
• views
![Page 84: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/84.jpg)
INFORMATION SCHEMA
• metadata about (among other things)
• tables attributes
• columns
• relationships
• users
• views
• routines
![Page 85: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/85.jpg)
INFORMATION SCHEMA
![Page 86: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/86.jpg)
INFORMATION SCHEMA
![Page 87: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/87.jpg)
INFORMATION SCHEMA
![Page 88: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/88.jpg)
# manuallySHOW TABLE STATUS LIKE 'table_name'\G
# look at the information
INFORMATION SCHEMA
![Page 89: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/89.jpg)
# manuallySHOW TABLE STATUS LIKE 'table_name'\G
# look at the information
INFORMATION SCHEMA
# within a stored routineSELECT ENGINE, table_rowsFROM information_schema.tables WHERE table_name = 'table_name' AND table_schema = SCHEMA()\G
# use the information
![Page 90: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/90.jpg)
Agenda
• Overview
• Stored routines language
• Execution environment
• Stored routines and INFORMATION_SCHEMA
• Using dynamic SQL
• Event scheduler
• Stored routines compared to client side languages
• Resources
• Q&A
![Page 91: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/91.jpg)
Dynamic SQL
![Page 92: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/92.jpg)
Text converted to a query
set @query = 'select * from salary where emp_no= ? ';
prepare myStat from @query;
set @emp = 1;
EXECUTE myStat USING @emp;
Dynamic SQL
![Page 93: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/93.jpg)
Dynamic SQL
![Page 94: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/94.jpg)
example: storing queries on tables
INSERT INT QTABLE VALUES ( 1, 'select * from salary where emp_no= ? ');
and later ...
set @query = (select query from QTABLE where query_id = 1);
prepare myStat from @query;set @emp = 1;EXECUTE myStat USING @emp;
Dynamic SQL
![Page 95: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/95.jpg)
Why dynamic SQL?
![Page 96: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/96.jpg)
Why dynamic SQL?
• When applying the same action to several objects
![Page 97: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/97.jpg)
Why dynamic SQL?
• When applying the same action to several objects
• To create flexible queries with variable metadata
![Page 98: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/98.jpg)
Why dynamic SQL?
• When applying the same action to several objects
• To create flexible queries with variable metadata
• To overcome SQL limits
![Page 99: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/99.jpg)
Why dynamic SQL?
• When applying the same action to several objects
• To create flexible queries with variable metadata
• To overcome SQL limits
• Especially useful for administration
![Page 100: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/100.jpg)
Dynamic SQL - a complex example
![Page 101: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/101.jpg)
getting a list of tables with current row number
# by hand ....SELECT 'City' AS t, COUNT(*) as records FROM world.City UNION SELECT 'Country' AS t, COUNT(*) as records FROM world.Country UNION SELECT 'CountryLanguage' AS t, COUNT(*) as records FROM world.CountryLanguage;
Dynamic SQL - a complex example
![Page 102: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/102.jpg)
Dynamic SQL - a complex example
![Page 103: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/103.jpg)
# with a routine ... SET GROUP_CONCAT_MAX_LEN=10000; SET @QUERY = CONCAT("SET @Q2 = ( SELECT GROUP_CONCAT(concat(\"SELECT '\", TABLE_NAME, \"' AS t, COUNT(*) as records FROM \", table_schema, \".\", table_name) SEPARATOR ' UNION ' ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ? )"); prepare query1 from @query; set @db = db_name; execute query1 using @db; prepare query2 from @Q2; execute query2;
Dynamic SQL - a complex example
![Page 104: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/104.jpg)
Dynamic SQL - a complex example
![Page 105: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/105.jpg)
# with a routine ...
call table_list('world');+-----------------+---------+| t | records |+-----------------+---------+| City | 4079 | | Country | 239 | | CountryLanguage | 984 | +-----------------+---------+
Dynamic SQL - a complex example
![Page 106: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/106.jpg)
Dynamic SQL - a more complex example
![Page 107: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/107.jpg)
# creating a list of CRC values from a table
CREATE FUNCTION column_list( p_db_name varchar(50), p_table_name varchar(50))RETURNS varchar(10000)BEGIN SET GROUP_CONCAT_MAX_LEN=10000; RETURN (select GROUP_CONCAT(column_name ) from INFORMATION_SCHEMA.COLUMNS WHERE table_name = p_table_name AND table_schema = p_db_name);END
Dynamic SQL - a more complex example
![Page 108: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/108.jpg)
# creating a list of CRC values from a table
CREATE FUNCTION column_list( p_db_name varchar(50), p_table_name varchar(50))RETURNS varchar(10000)BEGIN SET GROUP_CONCAT_MAX_LEN=10000; RETURN (select GROUP_CONCAT(column_name ) from INFORMATION_SCHEMA.COLUMNS WHERE table_name = p_table_name AND table_schema = p_db_name);END
Dynamic SQL - a more complex example
It works. But it will fail on NULL values
![Page 109: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/109.jpg)
Dynamic SQL - a more complex example
![Page 110: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/110.jpg)
# creating a list of CRC values from a table # using a safer column list
SET GROUP_CONCAT_MAX_LEN=10000; RETURN (select GROUP_CONCAT( if(is_nullable = 'yes', concat('COALESCE(', column_name,', "")'), column_name ) ) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = p_table_name AND table_schema = p_db_name);
Dynamic SQL - a more complex example
![Page 111: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/111.jpg)
Dynamic SQL - a more complex example
![Page 112: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/112.jpg)
# creating a list of CRC values from a table CREATE PROCEDURE do_table_row ( p_db_name varchar(50), p_table_name varchar(50), func_name varchar(20))BEGIN declare col_list varchar(10000); set col_list = safe_column_list(p_db_name, p_table_name); set @query = CONCAT('SELECT ',func_name,'(CONCAT(', col_list, ')) FROM ', p_db_name, '.', p_table_name ); prepare q from @query; execute q;END
Dynamic SQL - a more complex example
![Page 113: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/113.jpg)
Dynamic SQL - a more complex example
![Page 114: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/114.jpg)
# creating a list of CRC values from a table - using the routine
create table t1 ( id int not null, dt datetime not null, who varchar(20));
insert into t1 values (1, now(), null), (2, now() - interval 2 hour, 'abc');
Query OK, 2 rows affected (0.04 sec)Records: 2 Duplicates: 0 Warnings: 0
Dynamic SQL - a more complex example
![Page 115: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/115.jpg)
Dynamic SQL - a more complex example
![Page 116: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/116.jpg)
# creating a list of CRC values from a table - using the routine
call do_table_row(schema(),'t1','md5');+--------------------------------------+| md5(CONCAT(id,dt,COALESCE(who, ""))) |+--------------------------------------+| 0d52834f2092fac10076532ccd819c66 | | 59b194794ae90a808ab5c1c92e3e36c2 | +--------------------------------------+call do_table_row(schema(),'t1','SHA1');+------------------------------------------+| SHA1(CONCAT(id,dt,COALESCE(who, ""))) |+------------------------------------------+| adf27a69dd20f2466bbec6b19aa781b195471c2a | | 74dcb0f32c5668d347695101e847620c67ebd928 | +------------------------------------------+
Dynamic SQL - a more complex example
![Page 117: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/117.jpg)
Agenda
• Overview
• Stored routines language
• Execution environment
• Stored routines and INFORMATION_SCHEMA
• Using dynamic SQL
• Event scheduler
• Stored routines compared to client side languages
• Resources
• Q&A
![Page 118: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/118.jpg)
Event scheduler
![Page 119: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/119.jpg)
Event scheduler
• Available in MySQL 5.1
![Page 120: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/120.jpg)
Event scheduler
• Available in MySQL 5.1
• temporal triggers
![Page 121: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/121.jpg)
Event scheduler
• Available in MySQL 5.1
• temporal triggers
• execute "AT" a given time or "EVERY" x time intervals
![Page 122: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/122.jpg)
Event scheduler
• Available in MySQL 5.1
• temporal triggers
• execute "AT" a given time or "EVERY" x time intervals
• associated to a given schema
![Page 123: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/123.jpg)
Event scheduler
• Available in MySQL 5.1
• temporal triggers
• execute "AT" a given time or "EVERY" x time intervals
• associated to a given schema
• run with definer grants
![Page 124: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/124.jpg)
EVENT SCHEDULER syntax
![Page 125: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/125.jpg)
CREATE EVENT event_nameON SCHEDULE AT NOW() + INTERVAL 1 HOURDO CALL some_nice_procedure();
CREATE EVENT event_nameON SCHEDULE EVERY 3 minutesDO CALL check_if_everything_OK();
EVENT SCHEDULER syntax
![Page 126: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/126.jpg)
Event scheduler - when and how
![Page 127: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/127.jpg)
Event scheduler - when and how
• data warehouse import (with federated tables)
![Page 128: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/128.jpg)
Event scheduler - when and how
• data warehouse import (with federated tables)
• data consolidation
![Page 129: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/129.jpg)
Event scheduler - when and how
• data warehouse import (with federated tables)
• data consolidation
• sanity checks
![Page 130: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/130.jpg)
Event scheduler - when and how
• data warehouse import (with federated tables)
• data consolidation
• sanity checks
• user profiles
![Page 131: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/131.jpg)
Event scheduler - when and how
• data warehouse import (with federated tables)
• data consolidation
• sanity checks
• user profiles
• tables
![Page 132: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/132.jpg)
Event scheduler - when and how
• data warehouse import (with federated tables)
• data consolidation
• sanity checks
• user profiles
• tables
• creation of time-dependent objects
![Page 133: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/133.jpg)
EVENT SCHEDULER in depth
![Page 134: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/134.jpg)
EVENT SCHEDULER in depth
Using Triggers and Events for MySQL Administration and Auditing
![Page 135: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/135.jpg)
EVENT SCHEDULER in depth
Using Triggers and Events for MySQL Administration and Auditing
Tobias Asplund, MySQL AB
![Page 136: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/136.jpg)
EVENT SCHEDULER in depth
Using Triggers and Events for MySQL Administration and Auditing
Tobias Asplund, MySQL AB
Date: Thursday, April 26
![Page 137: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/137.jpg)
EVENT SCHEDULER in depth
Using Triggers and Events for MySQL Administration and Auditing
Tobias Asplund, MySQL AB
Date: Thursday, April 26
Time: 2:30pm - 3:15pm
![Page 138: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/138.jpg)
EVENT SCHEDULER in depth
Using Triggers and Events for MySQL Administration and Auditing
Tobias Asplund, MySQL AB
Date: Thursday, April 26
Time: 2:30pm - 3:15pm
Location: Ballroom B
![Page 139: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/139.jpg)
Agenda
• Overview
• Stored routines language
• Execution environment
• Stored routines and INFORMATION_SCHEMA
• Using dynamic SQL
• Event scheduler
• Stored routines compared to client side languages
• Resources
• Q&A
![Page 140: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/140.jpg)
performance overview
task standard SQL
Stored routines
external programs
finding prime numbers
N/A extremely slow fast
fetching and modifying records
extremely fast (*)
fast slow
self joining a table slow fast extremely slow
update correlated query
slow fast slow
(*) when applicable
![Page 141: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/141.jpg)
Number crunching (don't do that)
(*) from the book MySQL Stored Procedure Programming
![Page 142: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/142.jpg)
Fetching records across the network
(*) from the book MySQL Stored Procedure Programming
![Page 143: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/143.jpg)
Optimizing a complex self join
(*) from the book MySQL Stored Procedure Programming
![Page 144: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/144.jpg)
Optimizing a correlated nested query
(*) from the book MySQL Stored Procedure Programming
![Page 145: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/145.jpg)
Agenda
• Overview
• Stored routines language
• Execution environment
• Stored routines and INFORMATION_SCHEMA
• Using dynamic SQL
• Event scheduler
• Stored routines compared to client side languages
• Resources
• Q&A
![Page 146: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/146.jpg)
Resources
• MySQL manual http://dev.mysql.com/doc
![Page 147: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/147.jpg)
Resources
• MySQL stored procedure programming
http://www.oreilly.com/catalog/mysqlspp
![Page 148: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/148.jpg)
Resources
• MySQL stored routines library (http://forge.mysql.com)
• global variables
• simple and complex data structures
• loops
• syntax helpers
• named parameters
• test units
![Page 149: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/149.jpg)
Resources
• blogs
![Page 150: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/150.jpg)
Resources
• MySQL Forge
http://forge.mysql.com/wiki/Category:MySQLDevelopmentTutorials
![Page 151: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/151.jpg)
Resources
When NOT to use stored routines http://www.oreilly.com/SQL Cookbook The Art of SQL MySQL Cookbook
![Page 152: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/152.jpg)
Agenda
• Overview
• Stored routines language
• Execution environment
• Stored routines and INFORMATION_SCHEMA
• Using dynamic SQL
• Event scheduler
• Stored routines compared to client side languages
• Resources
• Q&A
![Page 153: Using Stored Routines for MySQL Administration - The Data Charmer](https://reader030.vdocuments.us/reader030/viewer/2022021212/62065fac8c2f7b17300720c2/html5/thumbnails/153.jpg)
Questions & Answers
Any questions?