A Quick Tour
Let's look at a few quick examples that demonstrate some key elements of both the structure and the functionality of MySQL's stored program language. For a full tutorial, see Chapter 2.
Integration with SQL
One of the most important aspects of MySQL's stored program
language is its tight integration with SQL. You don't need to rely on intermediate
software "glue," such as ODBC (Open DataBase Connectivity) or JDBC
(Java DataBase Connectivity), to construct and execute SQL
statements in your stored program language programs. Instead, you
simply write the UPDATE, INSERT, DELETE, and SELECT statements directly into your code,
as shown in Example
1-1.
Example 1-1. Embedding SQL in a stored program
1 CREATE PROCEDURE example1( )
2 BEGIN
3 DECLARE
l_book_count INTEGER;
4
5 SELECT COUNT(*)
6 INTO l_book_count
7 FROM books
8 WHERE author LIKE '%HARRISON,GUY%';
9
10 SELECT CONCAT('Guy has written (or co-written) ',
11 l_book_count ,
12 ' books.');
13
14 -- Oh, and I changed my name, so...
15 UPDATE books
16 SET author = REPLACE (author, 'GUY', 'GUILLERMO')
17 WHERE author LIKE '%HARRISON,GUY%';
18
19 ENDLet's take a more detailed look at this code in the following table:
Line(s) | Explanation |
|---|---|
1 | This section, the header of the
program, defines the name ( |
2 | This |
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access