MySQL 5.0 new features tutorial stored procedures: First Lecture

Introduction Introduction

The MySQL 5.0 new features tutorial is written for old MySQL users who need to understand the new features of version 5.0. The simple introduction of "stored procedures, triggers, views, information architecture view," Thanks to the translator Chen Bong efforts.

Hopefully this book will talk to you like an expert, with simple questions and examples to help you learn the knowledge you need. In order to achieve this goal, I will start from every detail to build a concept for everyone, and finally will show you a larger practical example, before the study may be thought that this use case is difficult, but as long as the course to learn, I believe that will soon be mastered.

Conventions and Styles conventions and programming styles

Every time I want to demonstrate the actual code, I adjust the code that appears on the MySQL client's screen, changing the font to courier so that they look different from normal text.

Here's an example:mysql> DROP FUNCTION F; Query OK, 0 rows Affected (0.00 sec)

If the instance is larger, you need to annotate some lines and paragraphs, and I will use the "<--" symbol on the right side of the page to emphasize.

For example:

mysql> CREATE PROCEDURE p ()
-> BEGIN
-> /* This procedure does nothing */ <--
-> END;//Query OK, 0 rows affected (0.00 sec)

Sometimes I will remove the "mysql>" and "->" systems in the example, and you can copy the code directly to the MySQL client (if you are not reading the electronic version, you can download the relevant script on the MySQL.com website), so the examples are already in SuSE 9.2 Linux, Mysql 5.0.3 public version of the test pass.

As you read this book, MySQL already has a higher version and can support more OS, including Windows,sparc,hp-ux. So the example here will be able to run normally on your computer. However, if the operation still fails, you can consult the senior MySQL users you know to get long-term support and help.

Definition and example of A definition and an Example

The definition and instance stored procedure is a program stored in the stack (like a subroutine in a regular language), and, to be exact, there are two types of MySQL-supported "routines (routines)": One is the stored procedure we say, The second is a function that can return a value in other SQL statements (using the same functions as MySQL preloaded, such as Pi ()). I will use the stored procedure more often in this book, because this is our past habit, I believe we will accept it.

A stored procedure includes a name, a list of arguments, and a set of SQL statements that can include many SQL statements.

There is a new syntax definition for local variables, exception handling, loop control, and if conditional sentences.

The following is an instance declaration that includes a stored procedure: (in order to facilitate reading, the program does not add any Chinese comments)

CREATE PROCEDURE procedure1 /* name存储过程名*/
(IN parameter1 INTEGER) /* parameters参数*/
BEGIN /* start of block语句块头*/
DECLARE variable1 CHAR(10); /* variables变量声明*/
IF parameter1 = 17 THEN /* start of IF IF条件开始*/
SET variable1 = 'birds'; /* assignment赋值*/
ELSE
SET variable1 = 'beasts'; /* assignment赋值*/
END IF; /* end of IF IF结束*/
INSERT INTO table1 VALUES (variable1);/* statement SQL语句*/
END /* end of block语句块结束*/

Below I will explain all the details of the work you can do with the stored procedures. We will also introduce new database objects-triggers, because the association of triggers and stored procedures is inevitable.

Why Stored Procedures Why do you use stored procedures

Because stored procedures are new to MySQL, it's natural that you need to be more careful when you use them.

After all, no one has ever used it before, and not a lot of experienced users have come to take you along the path they have traveled. However, you should start thinking about moving existing programs (which may be in a server application, in a user-defined function (UDF), or in a script) to a stored procedure. There's no reason to do this, you have to do it.

Because the stored procedures are already certified technology! Although it is new in MySQL, functions of the same function are already present in other DBMS, and their syntax is the same. So you can get these concepts from other people, there are a lot of experience users you can consult or hire, and a lot of third-party documents for you to read.

Stored procedures can make the system run faster! Although we can not prove this advantage on MySQL at the moment, the user experience is not the same. What we can say is that the MySQL server has improved the caching mechanism, as preparedstatements (preprocessing statements) do. Because there is no compiler, SQL stored procedures do not run as quickly as programs written by external languages such as C. But the main method of lifting speed is whether the network information flow can be reduced. If you need to work with repetitive tasks that require checking, looping, and multiple statements without user interaction, you can use stored procedures saved on the server to do so. This means that there is less information between the server and the client at each step of the task.

So the stored procedure is a reusable component! Imagine that if you change the language of the host, this will have no effect on the stored procedure because it is a database logic rather than an application. Stored procedures can be ported! When you write a stored procedure in SQL, you know that it can run on any platform supported by MySQL, does not require you to add an additional running environment package, does not require a Setup license for the program in the operating system, or a stored procedure for your different computer models will be saved! If you write a program, such as a check cancellation in a bank transaction, the person who wants to know the check can find your program.

It is saved in the database as a source code. This will make a meaningful connection between the data and the process that processes the data, which may be the same as the planning theory you heard in class. Stored procedures can migrate!