1 / 18

CSE 3330 Database Concepts

Discover how to create, optimize, and utilize stored procedures in MySQL databases for efficient data management. Learn about the advantages, security benefits, and performance enhancements that stored procedures offer. Explore examples, functions, cursors, backup, and restore processes, and delve into triggers to automate actions based on database events.

garson
Télécharger la présentation

CSE 3330 Database Concepts

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CSE 3330 Database Concepts Stored Procedures

  2. How to create a user CREATE USER.. http://dev.mysql.com/doc/refman/5.1/en/create-user.html GRANT PRIVILEGE http://dev.mysql.com/doc/refman/5.1/en/grant.html

  3. Find list of users select user from mysql.user; • A wealth of information exists in mysql database use mysql; Show tables; Desc user;

  4. Stored Procedure • SP is a code written in SQL that is compiled and stored on the DB server. • Used for repetitive tasks. • You can use programming language constructs likevariables, loops, assignments, cursors, etc • Pre-compiled => Efficient

  5. Background Good background http://code.tutsplus.com/articles/an-introduction-to-stored-procedures-in-mysql-5--net-17843 Advantages: • Share logic • Grant users permissions to SP rather than tables • Security • Improved Performance, reduces network traffic

  6. Simple SP

  7. Simple SP DELIMITER $$ -- Create a procedure in Oracle. CREATE PROCEDURE hello_world() BEGIN -- Print the phrase and a line return. SELECT 'Hello World!'; END; $$ -- Reset the delimiter back to a semicolon to work again. DELIMITER ; -- Call the procedure. SELECT 'CALL hello_world' AS "Statement"; CALL hello_world();

  8. Simple SP Why do we change the delimiter? DELIMITER ;

  9. Calling SP

  10. SP Examples

  11. SP

  12. Calling SP from PDO http://www.mysqltutorial.org/php-calling-mysql-stored-procedures/ http://www.php.net/manual/en/mysqli.quickstart.stored-procedures.php http://www.joeyrivera.com/2009/using-mysql-stored-procedures-with-php-mysqlmysqlipdo/

  13. Functions Function MUST return a value, Procedure does not have to. Function invoked within an expression, Procedure invoked with Call

  14. Looping Constructs http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.pdf#page=21&zoom=auto,0,792

  15. Cursors http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.pdf#page=35&zoom=auto,0,792

  16. How to backup a db MySQL has functions for backing up entire db - includes tables + procedures + functions + .. http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/ Backup: $ mysqldump-u root -p sugarcrm > sugarcrm.sql Learn to backup your db regularly

  17. How to restore a db http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/ Restore: $ mysql -u root -p sugarcrm < /tmp/sugarcrm.sql

  18. Triggers A trigger is a SQL statement that is executed (or “fired”) when another event occurs. For example, a trigger may fire when you insert data into a table, update a table, delete a row, etc. Work through the examples: https://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

More Related