MySQL存储过程编程基础与实战

5星 · 超过95%的资源需积分: 10 2 浏览量更新于2024-07-26 收藏 5.35MB PDF 举报

"MySQL存储过程编程" 本书深入浅出地介绍了MySQL中的存储过程编程，由Guy Harrison与Steven Feuerstein合著，旨在帮助开发者掌握这一重要技术。存储过程是数据库中的预编译语句集合，可以提高性能、简化复杂操作并增强数据库的安全性。第一部分：存储程序基础在第一章中，作者解释了什么是存储程序，并通过一个快速的概述展示了其基本功能。书中提到了开发者使用存储程序的各种资源，并给出了针对开发者的实用建议，强调了在设计和实现过程中应考虑的问题。第二章是MySQL存储程序教程，从最基础的部分开始，如创建第一个存储过程。这一章详细介绍了变量的使用、参数传递、条件执行、循环结构、错误处理以及如何与数据库交互。此外，还涵盖了在存储程序内部调用其他存储程序以及如何创建和使用存储函数。对于数据库的自动化操作，触发器（triggers）也是重要一环，本书对此有详尽阐述。最后，通过一个实例展示了如何从PHP中调用存储过程，为Web应用开发提供实际指导。第三章探讨了语言基础，包括变量、字面量、参数和注释，以及各种运算符的使用。表达式、内置函数和数据类型的选择对于高效编程至关重要。特别是数据类型的讨论，有助于开发者理解如何选择最适合的数据结构。此外，书中还提到了MySQL 5的“严格”模式，这种模式对数据验证更加严格，有助于避免潜在的数据不一致问题。第四章关注块、条件语句和迭代编程。存储程序的块结构是控制流程的关键，这一章详细介绍了如何使用IF-THEN-ELSE和CASE语句进行条件判断，以及WHILE和FOR循环来实现迭代逻辑。这些结构使得在存储过程中实现复杂的业务逻辑成为可能。通过以上内容，读者将能够系统地学习到MySQL存储过程的编写、调用和管理，从而在实际项目中更有效地利用这一强大的数据库功能。无论你是初学者还是经验丰富的开发者，本书都提供了丰富的知识和实践经验，帮助你在MySQL存储过程编程方面达到新的高度。

This is the Title of the Book, eMatter Edition

A Quick Tour

Let’s take a more detailed look at this code in the following table:

Stored Functions

A stored function is a stored program that returns a single value and that can be used

whenever a built-in function can be used—for example, in a SQL statement.

Example 1-3 returns the age of a person in years when provided with a date of birth.

8 payout_loop:LOOP

9 SET l_balance_remaining = account_balance(account_id_in);

11 IF l_balance_remaining < 1000 THEN

12 LEAVE payout_loop;

14 ELSE

15 CALL apply_balance(account_id_in, l_balance_remaining);

16 END IF;

18 END LOOP;

20 END

Line(s) Explanation

1-3 This is theheader of our procedure; line2 contains the parameter listof the procedure, which inthis case consists

of a single incoming value (the identification number of the account).

6 Declare a variable to hold the remaining balance for an account.

8-18 This simple loop (named so because it is started simply with the keyword

LOOP, as opposed to WHILE or

REPEAT) iterates until the account balance falls below 1000. In MySQL, we can name the loop (line 8, payout_

loop

), which then allows us to use the LEAVE statement (see line 12) to terminate that particular loop. After

leaving a loop, the MySQL engine will then proceed to the next executable statement following the

END LOOP;

statement (line 18).

9 Call the

account_balance function (which must have been previously defined) to retrieve the balance for

this account. MySQL allows you to call a stored program from within another stored program, thus facilitating

reuse of code.Since this program is afunction,it returns a value andcan therefore be called fromwithina MySQL

SET assignment.

11-16 This

IF statement causes the loop to terminate if the account balance falls below $1,000. Otherwise (the ELSE

clause), it applies the balance to the next charge. You can construct much more complex Boolean expressions

with

ELSEIF clauses, as well.

15 Call the

apply_balance procedure. This is an example of code reuse; rather than repeating the logic of

apply_balance in this procedure, we call a common routine.

Example 1-3. A stored function to calculate age from date of birth

1 CREATE FUNCTION f_age (in_dob datetime) returns int

2 NO SQL

3 BEGIN

4 DECLARE l_age INT;

Example 1-2. Stored procedure with control and conditional logic (continued)

This is the Title of the Book, eMatter Edition

Chapter 1: Introduction to MySQL Stored Programs

Let’s step through this code in the following table:

We can use our stored function wherever a built-in function would be permitted—

within another stored program, in a

SET statement, or, as shown in Example 1-4,

within a SQL statement.

When Things Go Wrong

Even if our programs have been thoroughly tested and have no bugs, user input can

cause errors to occur in our code. The MySQL stored program language offers a

powerful mechanism for handling errors. In Example 1-5, we create a procedure that

5 IF DATE_FORMAT(NOW( ),'00-%m-%d') >= DATE_FORMAT(in_dob,'00-%m-%d') THEN

6 -- This person has had a birthday this year

7 SET l_age=DATE_FORMAT(NOW( ),'%Y')-DATE_FORMAT(in_dob,'%Y');

8 ELSE

9 -- Yet to have a birthday this year

10 SET l_age=DATE_FORMAT(NOW( ),'%Y')-DATE_FORMAT(in_dob,'%Y')-1;

11 END IF;

12 RETURN(l_age);

END;

Lines(s) Explanation

1 Define the function: its name, input parameters (a single date), and return value (an integer).

2 This function contains no SQL statements. There’s some controversy about the use of this clause —see Chapters

3 and 10 for more discussion.

4 Declare a local variable to hold the results of our age calculation.

5-11 This

IF-ELSE-END IF block checks to see if the birth date in question has occurred yet this year.

7 If the birthdate has, in fact,passedin the current year, wecan calculate the age bysimplysubtracting the year of

birth from the current year.

10 Otherwise (i.e., the birth date is yet to occur this year), we need to subtract an additional year from our age calculation.

12 Return the age as calculated to the calling program.

Example 1-4. Using a stored function within a SQL statement

mysql> SELECT firstname,surname, date_of_birth, f_age(date_of_birth) AS age

-> FROM employees LIMIT 5;

+-----------+---------+---------------------+------+

+-----------+---------+---------------------+------+

| LUCAS | FERRIS | 1984-04-17 07:04:27 | 21 |

| STAFFORD | KIPP | 1953-04-22 06:04:50 | 52 |

| GUTHREY | HOLMES | 1974-09-12 08:09:22 | 31 |

| TALIA | KNOX | 1966-08-14 11:08:14 | 39 |

| JOHN | MORALES | 1956-06-22 07:06:14 | 49 |

+-----------+---------+---------------------+------+

Example 1-3. A stored function to calculate age from date of birth (continued)

This is the Title of the Book, eMatter Edition

A Quick Tour

creates new product codes or—if the product code already exists—updates it with a

new name. The procedure detects an attempt to insert a duplicate value by using an

exception handler. If the attempt to insert fails, the error is trapped and an

UPDATE is

issued in place of the

INSERT. Without the exception handler, the stored program

execution is stopped, and the exception is passed back unhandled to the calling

program.

Let’s take a more detailed look at the error-handling aspects of this code:

Example 1-5. Error handling in a stored program

1 CREATE PROCEDURE sp_product_code

2 (in_product_code VARCHAR(2),

3 in_product_name VARCHAR(30))

5 BEGIN

7 DECLARE l_dupkey_indicator INT DEFAULT 0;

8 DECLARE duplicate_key CONDITION FOR 1062;

9 DECLARE CONTINUE HANDLER FOR duplicate_key SET l_dupkey_indicator =1;

11 INSERT INTO product_codes (product_code, product_name)

12 VALUES (in_product_code, in_product_name);

14 IF l dupkey_indicator THEN

15 UPDATE product_codes

16 SET product_name=in_product_name

17 WHERE product_code=in_product_code;

18 END IF;

20 END

Line(s) Explanation

1-4 This is the header of the stored procedure, accepting two

IN parameters: product code and product name.

7 Declare a variable that we will use to detect the occurrence of a duplicate key violation. The variable is initial-

ized with a value of 0 (false); subsequent code will ensure that it gets set to a value of 1 (true) only if a dupli-

cate key violation takes place.

8 Define a named condition,

duplicate_key, that is associated with MySQL error 1062. While this step is not

strictly necessary, we recommend that you define such conditions to improve the readability of your code (you

can now reference the error by name instead of by number).

9 Define an error handler that will trap the duplicate key error and then set the value of the variable

dupkey_indicator

to 1 (true) if a duplicate key violation is encountered anywhere in the subsequent

code.

11-12 Insert a new product with the user-provided code and name.

14 Check the value of the

l_dupkey_indicator variable. If it is still 0, then the INSERT was successful and

we are done. If the value has been changed to 1 (true), we know that there has been a duplicate key violation.

We then run the

UPDATE statement in lines 15-17 to changethename of the product withthespecified code.

This is the Title of the Book, eMatter Edition

Chapter 1: Introduction to MySQL Stored Programs

Error handling is a critical aspect of writing robust, maintainable MySQL stored pro-

grams. Chapter 6 takes you on an extensive tour of the various error-handling mech-

anisms in MySQL stored programs.

Triggers

A trigger is a stored program that is automatically invoked in response to an event

within the database. In the MySQL 5 implementation, triggers are invoked only in

response to DML activity on a specific table. The trigger can automatically calculate

derived or denormalized values. Example 1-6 shows a trigger that maintains such a

derived value; whenever an employee salary is changed, the value of the

contrib_401K

column is automatically set to an appropriate value.

The following table explains this fairly simple and short trigger:

There is, of course, much more that can be said about the MySQL stored program

language—which is why you have hundreds more pages of material to study in this

book! These initial examples should, however, give you a good feel for the kind of

code you will write with the stored program language, some of its most important

syntactical elements, and the ease with which you can write—and read—the stored

program language code.

Example 1-6. Trigger to maintain a derived column value

1 CREATE TRIGGER employees_trg_bu

2 BEFORE UPDATE ON employees

3 FOR EACH ROW

4 BEGIN

5 IF NEW.salary <50000 THEN

6 SET NEW.contrib_401K=500;

7 ELSE

8 SET NEW.contrib_401K=500+(NEW.salary-50000)*.01;

9 END IF;

10 END

Line(s) Explanation

1 A trigger has a unique name. Typically, you will want to name the trigger so as to reveal its nature. For

example, the “

bu” in the trigger’s name indicates that this is a BEFORE UPDATE trigger.

2 Define theconditions that willcause the triggerto fire. Inthis case, thetrigger code willexecute prior toan

UPDATE statement on the employees table.

FOR EACH ROW indicates that the trigger code will be executed once for each row being affected by the

DML statement. This clause is mandatory in the current MySQL 5 trigger implementation.

4-10 This

BEGIN-END block defines the code that will run when the trigger is fired.

5-9 Automatically populate the

contrib_401K column in the employees table. If the new value for the

salary columnis less than50000, the contrib._401Kcolumn will beset to 500.Otherwise, the value

will be calculated as shown in line 8.

This is the Title of the Book, eMatter Edition

Resources for Developers Using Stored Programs

The introduction of stored programs in MySQL 5 is a significant milestone in the

evolution of the MySQL language. For any new technology to be absorbed and lever-

aged fully, users of that technology need lots of support and guidance in how best to

utilize it. Our objective is to offer in this book complete and comprehensive cover-

age of the MySQL stored program language.

We are certain, however, that you will need help in other ways, so in the following

sections we describe additional resources that either complement this book (by pro-

viding information about other MySQL technologies) or provide community-based

support or late-breaking news. In these sections we provide quick summaries of

many of these resources. By taking full advantage of these resources, many of which

are available either free or at a relatively low cost, you will greatly improve the qual-

ity of your MySQL development experience—and your resulting code.

Books

Over the years, the MySQL series from O’Reilly has grown to include quite a long list

of books. Here we list some of the books currently available that we feel could be perti-

nent to the MySQL stored program developer, as well as relevant books from other

publishers. Please check out the MySQL area of the O’Reilly OnLAMP web site (http://

www.onlamp.com/onlamp/general/mysql.csp) for more complete information.

MySQL Stored Procedure Programming, by Guy Harrison with Steven Feuerstein

This is the book you are holding now (or maybe even viewing online). This book

was designed to be a complete and comprehensive guide to the MySQL stored

program language. However, this book does not attempt complete coverage of

the MySQL server, the SQL language, or other programming languages that you

might use with MySQL. Therefore, you might want to complement this book

with one or more other topics from the O’Reilly catalog or even—heaven for-

bid—from another publisher!

MySQL in a Nutshell, by Russell Dyer

This compact quick-reference manual covers the MySQL SQL language, utility

programs, and APIs for Perl, PHP, and C. This book is the ideal companion for

any MySQL user (O’Reilly).

Web Database Applications with PHP and MySQL, by Hugh Williams and David Lane

This is a comprehensive guide to creating web-based applications using PHP and

MySQL. It covers PEAR (PHP Extension and Application Repository) and pro-

vides a variety of complete case studies (O’Reilly).

剩余584页未读，继续阅读

liuyongshuai

粉丝: 1
资源: 14

MySQL存储过程编程基础与实战

MySQL存储过程.pdf

mysql存储过程编程 MySQL.Stored.Procedure.Programming

MySQL Stored Procedure Programming

mysql stored procedure programming PDF

mysql_in_a_nutshell_2nd_edition.pdf 英文全本

mysql_internal.pdf

Mysql

深入理解MySql

Sql for mysql

mysql database 6本书

最新资源