MySQL5.0 new feature tutorial stored procedure: Lecture 1

Introduction to Introduction The new feature tutorial of MySQL5.0 is written for old MySQL users who need to understand the new features of MySQL 5.0. Simply put, we have introduced "stored procedures, triggers, views, and information architecture views". I would like to thank the translator Chen Pengyi for his efforts.

Introduction to Introduction

The new features tutorial for MySQL 5.0 is intended for old MySQL users who need to understand the new features of MySQL 5.0. Simply put, we have introduced "stored procedures, triggers, views, and information architecture views". I would like to thank the translator Chen Pengyi for his efforts.

Looking forward to reading this book to talk to you like an expert, and use simple titles and examples to help you learn the required knowledge. In order to achieve this goal, I will start from every detail and gradually establish a concept for everyone. Finally, I will show you a large practical example, before learning, you may think that this case is very difficult, but as long as you keep learning the course, trust will soon be able to grasp.

Conventions and Styles agreement and programming style

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 common text.

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

If the instance is relatively large, you need to add comments between some rows and paragraphs. at the same time, I will place the "<--" symbol on the right of the page to exaggerate.

For example:

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

Sometimes I will display the 'MySQL> 'and'-> 'systems in the example, and you can copy the code directly to the mysql client program (if what you are reading is not an electronic version, the related scripts can be downloaded from the mysql.com website. Therefore, all the examples have been tested in Suse 9.2 Linux and Mysql 5.0.3.

When you browse 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 familiar senior Mysql User for long-term support and help.

A Definition and an Example Definition and Example

Definition and instance stored procedures are a program stored in the library (just like a subroutine in the proverbs). To be precise, MySQL supports the "routines (routine) there are two types: one is the stored procedure, and the other is the function that can be returned in other SQL statements (the same as the pre-loaded function in Mysql, such as pi ()). In this book, I will apply stored procedures more often. as this is our past habit, I trust everyone to accept it.

A stored procedure contains 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 */