mysqluc-2006-tutorial-sp

pages

English

Documents

Lire

Le téléchargement nécessite un accès à la bibliothèque YouScribe Tout savoir sur nos offres

pages

English

Documents

Lire

Le téléchargement nécessite un accès à la bibliothèque YouScribe Tout savoir sur nos offres

Publié par

Zikes

Langue

English

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 ...

Voir

Publié par

Zikes

Langue

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

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$$