MySQL 5.0 new features tutorial stored procedure: Lecture 1)

Introduction to Introduction

The new MySQL 5.0 feature tutorial is intended for old MySQL users who need to know the new features of MySQL 5.0. Simply put, I introduced the "Stored Procedure, trigger, view, and Information Architecture View". I would like to thank the translator Chen Pengyi for his efforts.

I hope this book will be able to talk to you like an expert, so that you can learn the required knowledge with simple questions and examples. In order to achieve this purpose, I will gradually establish concepts for everyone from every detail, and finally show you a large practical example, before learning, you may think that this case is very difficult, but as long as you follow the course, I believe you will be able to master it soon.

Conventions and Styles Conventions and programming Styles

Every time I want to demonstrate the actual code, I will adjust the code displayed on the screen of the mysql client and change the font to Courier to make them look different from the normal text.

Here is an example: mysql> drop function f; Query OK, 0 rows affected (0.00 sec)

If the instance is large, you need to add comments between some rows and paragraphs. At the same time, I will place 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. You can copy the Code directly to the mysql client program, you can download related scripts on the mysql.com website. Therefore, all the examples have passed the test on Suse 9.2 Linux and Mysql 5.0.3 public edition.

When you read this book, Mysql has a higher version and supports more operating systems, including Windows, Linux, and HP-UX. So the example here will run normally on your computer. However, if the operation still fails, you can consult a senior Mysql user you know for long-term support and help.

A Definition and an Example Definition and Example

Definition and instance stored procedure is a program stored in the library, just like a subroutine in a regular language). To be precise, MySQL supports two types of "routines: the first is the stored procedure, and the second is the function that can return values in other SQL statements, which is the same as the Mysql pre-loaded function, such as pi ()). I will use stored procedures more frequently in this book, because this is our past habit and I believe everyone will accept it.

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

Here, a new syntax is defined for local variables, exception handling, loop control, and IF clauses.

The following is an example of a stored procedure statement)

Create procedure procedure1/* name stored PROCEDURE name */(IN parameter1 INTEGER)/* parameters parameter */BEGIN/* start of block statement header */DECLARE variable1 CHAR (10 ); /* variables variable Declaration */IF parameter1 = 17 THEN/* start of IF condition start */SET variable1 = 'birds '; /* assignment value */ELSESET variable1 = 'beasts';/* assignment value */end if;/* end of if end */insert into table1 VALUES (variable1 ); /* statement SQL statement */END/* end of block statement block END */

Next I will introduce all the details about the work you can do using stored procedures. At the same time, we will introduce the new database object-trigger, because the association between the trigger and the stored procedure is inevitable.

Why does Why Stored Procedures use Stored Procedures?

Because the stored procedure is a new feature for MySQL, you need to pay more attention to it during use.

After all, no one has used it before, and there are not many experienced users to take you through their path. However, you should start to consider transferring existing programs in server applications, user-defined function udfs, or scripts to the stored procedures. You do not need to do this for any reason.

Because stored procedures are already certified technologies! Although it is new in Mysql, functions of the same function already exist in other DBMS, and their syntax is always the same. So you can get these concepts from others, there are many experienced users you can consult or hire, and there are many third-party documents for you to read.