First some exciting stuff about MySQL
QUICK LOOKS: (MySQL 5.0 Whitepaper)
- MySQL has six million active installations worldwide
- 40,000 MySQL downloads occur every day.
Countless corporations, educational facilities, and government agencies rely on MySQL to manage their critical data assets and service anywhere from hundreds to billions of requests per day. With over six million active installations worldwide and 40,000 downloads occurring every day, the popularity and prominence of MySQL continue to rise.
reliability, performance and ease-of-use are "the three priorities of the MySQL database server."
ACID Transactions – MySQL supports ACID (Atomic, Consistent, Isolated, Durable) transaction model that ensures the integrity and validity of every entered transaction. Full COMMIT and ROLLBACK, with redo logs and undo (rollback) segment areas to ensure data consistency during crash recovery operations.
• Server-Based Data Integrity – Invalid data (bad dates, invalid numbers, etc.) can be automatically rejected at the server, with column-level rules enforcement being possible. In addition, full foreign-key support is provided so that complete data referential integrity is guaranteed.
• Low Contention Issues – Unlimited row-level locking is provided within InnoDB as is automatic deadlock detection, custom isolation levels (READ COMMITTED, etc.), to virtually eliminate locking issues. In addition, InnoDB supports multi-version concurrency control so that those reading data see a snapshot of the requested information as it existed at the moment their query was issued. This ability ensures that readers don’t block writers and vice-versa, and leads to extremely high levels of data availability.
- Support for stored procedures
- Fast performance
- Easier Security Administration
- Fast Performance
- Reduced Network Traffic
Using MySQL 5.0 Stored procedures
- very easy to use
- adhere to the ANSI SQL 2003 specification
- standoutput ability for SELECT statements
Creating MySQL Stored Procedure
create procedure myproc()
select * .....;
create function broker_name(id int)
declare broker_name(VARCHAR (50)
select * into broker_name from broker .....;
- "SHOW PROCEDURE STATUS " is now possible
- mysql.proc can be queried
- 5.0 INFORMATION_SCHEMA data dictionary
- SHOW CREATE PROCEDURE
function (to create DDL)