mysqluc-2006-tutorial-sp
47 Pages
English

mysqluc-2006-tutorial-sp

Downloading requires you to have access to the YouScribe library
Learn all about the services we offer

Description

MySQL 5.1Past, Present and F utureJan KneschkeMySQL ABAgenda• Past• S Q L Trees m eets D ynam ic S Q L• P resent• E vents• P artitioning• Future• V ertical P artitioningAbout the Presenter●Jan Kneschke●Senior Developer in the MySQL Network Team● jan@mysql.c om●Drives the development of the high-performance web-server lighttpd●Was trainer and consultant at MySQL before becoming a developerRaise your hands●Who is using MySQL 4.1 or older ?●Who is using MySQL 5.0, 5.1 or later ?●Who uses●Prepared Statements●Stored Procedures●Partitioning●Who is using one of the features with another vendors RDBMS ?Back in Time●Last years tutorial concentrated on ●Stored Procedures, VIEWs and Triggers●Examples can be found at http://jan.kneschke.de/projects/mysql/sp/●Who has attended last years tutorial ?Prepared Statements• F irst available in MySQL 4.1• S plit up the execution of S Q L statem ents into a P R E P A R E and a E X E C U TE phase• A t best P R E P A R E once, E X E C U TE m ultiple tim es• P revents S Q L-injectionPrepared StatementsPREPARE s FROM 'SELECT * FROM tbl WHERE id = ?';EXECUTE s USING @id;DEALLOCATE PREPARE s;Stored Procedures• A programming language running in the context of the DBMS• U ses the syntax defined in the SQ L:2003 standard• Provides C ontrol-Flow , Loops, Exceptions, C ursors, ...• SPs are used by Triggers, Events, FunctionsStored ProceduresCREATE PROCEDURE fill_table ()BEGIN DECLARE n INT ...

Subjects

Informations

Published by
Reads 50
Language English
MySQL 5.1 Past, Present and Future
Jan Kneschke MySQL AB
Agenda
Past
• S Q L T r e e s m
P r e s e n t
• E v e n t s
Partitioning
F u t u r e
e e t s D
Vertical Partitioning
y n a m
ic S
Q
L
About the Presenter
Jan Kneschke
Senior Developer in the MySQL Network Team
jan@mysql.com
Drives the development of the high-performance web-serverlighttpd
Was trainer and consultant at MySQL before becoming a developer
Raise your hands
Who is using MySQL 4.1 or older ?
Who is using MySQL 5.0, 5.1 or later ?
Who uses
Prepared Statements
Stored Procedures
Partitioning
Who is using one of the features with another vendors RDBMS ?
Back in Time
Last years tutorial concentrated on
Stored Procedures, VIEWs and Triggers
Examples can be found at http://jan.kneschke.de/projects/mysql/sp/
Who has attended last years tutorial ?
Prepared Statements
First available in MySQL 4.1
S p lit u p t h e e x e c u t io n o f S Q L s t a t e m e n t s in t o a P R E P A R E a n d a E X E C U T E p h a s e
A t b e s t P R E P A R E o n c e , E X E C U T E m u lt ip le t im e s
P r e v e n t s S Q L -in je c t io n
Prepared Statements
PREPARE s FROM 'SELECT * FROM tbl WHERE id = ?';
EXECUTE s USING @id;
DEALLOCATE PREPARE s;
Stored Procedures
A programming language running in the context of the DBMS
U s e s t h e s y n t a x d e f in e d in t h e S Q L :2 0 0 3 s t a n d a r d
P r o v id e s C o n t r o l-F lo w , L o o p s , E x c e p t io n s , C u r s o r s , ...
S P s a r e u s e d b y T r ig g e r s , E v e n t s , F u n c t io n s
Stored Procedures
_ CREATE PROCEDURE fill table () BEGIN  DECLARE n INT DEFAULT 1000;  ins_loop: LOOP  INSERT INTO tbl VALUES (n);  SET n = n - 1;  IF (NOT n) THEN  _ p; LEAVE ins loo  END IF;  END LOOP ins loop; _ END$$
Dynamic SQL
fill_table() works only against one table
SQL Statements in SPs are static
P r e p a r e d S t a t e m e n t s c a n o n ly u s e p la c e h o ld e r s f o r v a lu e s , n o t f o r p a r t s o f t h e SQL statement itself
PREPARE takes a constant string or (since MySQL 5.0.13) a user variable
Dynamic SQL
SET @s = CONCAT(
_  "INSERT INTO ", tbl name,
_  " (", field name,") VALUES (?)");
PREPARE s FROM @s;
SET @n = n;
EXECUTE s USING @n;
DEALLOCATE PREPARE s;