MySQL5.0 new feature tutorial stored procedure _ MySQL

Introduction to the stored procedure 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 (if you are not reading an electronic version, 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.

Therefore, stored procedures are reusable components! Imagine if you change the host language, this will not affect the stored procedure, because it is a database logic rather than an application. Stored procedures can be transplanted! When you use SQL to write a stored procedure, you will know that it can run on any platform supported by Mysql. you do not need to add additional runtime environment packages, you do not need to set the permission for the program in the operating system, or the stored procedure for your different models of computers will be saved! If you have compiled a program, such as displaying the withdrawal of check in bank transaction processing, you can find your program for anyone who wants to know the check.

It is stored in the database as source code. This will make a meaningful association between data and the process of data processing. This may be the same as what you said in the planning theory you have heard in your class. Stored procedures can be migrated!

Mysql fully supports the SQL 2003 standard. Some databases (such as DB2 and Mimer) are also supported. However, some of them are not supported, such as Oracle and SQL Server. We will provide enough help and tools to make it easier for the code written for other DBMS to be transferred to Mysql.

Setting up with MySQL 5.0 Setting and starting MySQL 5.0 service

Pass
Mysql_fix_privilege_tables

Or

~ // Mysql-5.0/scripts/mysql_install_db

To start the MySQL service
As part of our exercise preparation, I assume MySQL 5.0 has been installed. If you do not have a database administrator to install the database and other software for you, you need to install it on your own. But one thing you can easily forget is that you need a table named mysql. proc.

After the latest version is installed, you must run

Mysql_fix_privilege_tables

Or

Mysql_install_db

(You only need to run one of them) -- otherwise, the stored procedure will not work. I also enable an informal SQL script after the root identity, as shown below:

Mysql> source/home/fig/mysql-5.0/scripts/mysql_prepare_privilege_tables_for_5. SQL

Starting the MySQL Client starts the MySQL Client

This is how I start the mysql client. You may use other methods. if you are using a binary or Windows computer, you may run the following programs in other subdirectories:

Easy @ PHPv: ~> /Usr/local/mysql/bin/mysql -- user = root
Welcome to the MySQL monitor. Commands end with; or/g.
Your MySQL connection id is 1 to server version: 5.0.3-alpha-debug
Type 'help; 'or'/h' for help. type'/C' to clear the buffer.

In the demo, I will display the results returned by the mysql client after login as root, which means that I have great privileges.
Check for the Correct Version
To confirm that the MySQL version is correct, we need to query the version. I have two methods to confirm that I am using version 5.0:

Show variables like 'version ';

Or

Select version ();

For example:

Mysql> show variables like 'version ';
+ --------------- + ------------------- +
| Variable_name | Value |
+ --------------- + ------------------- +
| Version | 5.0.3-alpha-debug |
+ --------------- + ------------------- +
1 row in set (0.00 sec)
Mysql> select version ();
+ ------------------- +
| VERSION () |
+ ------------------- +
| 5.0.3-alpha-debug |
+ ------------------- +
1 row in set (0.00 sec)

When you see the number '5. 0. X', you can confirm that the stored procedure works on this client.

The Sample "Database" Sample Database

The first thing to do now is to create a new database and set it as the default database to implement the SQL statement for this step.
The statement is as follows:

Create database db5;
USE db5;

For example:

Mysql> create database db5;
Query OK, 1 row affected (0.00 sec)
Mysql> USE db5;
Database changed

Avoid using the actual database with important data here and then create a simple worksheet.
SQL statement for this step
The statement is as follows:

Mysql> create database db5;
Query OK, 1 row affected (0.01 sec)
Mysql> USE db5;
Database changed
Mysql> create table t (s1 INT );
Query OK, 0 rows affected (0.01 sec)
Mysql> insert into t VALUES (5 );
Query OK, 1 row affected (0.00 sec)

You will find that only one column is inserted in the table. The reason for this is that I want to keep the table simple, because here I don't need to show the data query skills, but to teach the stored procedure without using large data tables, because it is complicated enough.

This is the example database. we will select the separator for Pick a Delimiter from the table whose name is t and only contains one column.
Now we need a separator. the SQL statement to implement this step is as follows:

DELIMITER //

For example:

Mysql> DELIMITER //

The delimiter is the character or string symbol that notifies the mysql client that you have entered an SQL statement. We have always used semicolons (;), but in the stored procedure, this may cause many problems, because there are many statements in the stored procedure, therefore, each one requires a semicolon, so you need to select a string that is unlikely to appear in your statement or program as the separator. I have used the double slash "//" and some people have used the vertical line "| ". I have seen the use of the "@" symbol in DB2 programs, but I do not like it. You can choose based on your preferences, but in this course, you 'd better choose the same as me to make it easier to understand. If you want to use ";" (semicolon) as the separator in the future, enter the following statement:

"DELIMITER ;//".
Create procedure Example
Create procedure p1 () SELECT * FROM t ;//

Maybe this is the first stored procedure you created using Mysql. If so, you 'd better remember this important milestone in your diary.

Create procedure p1 () SELECT * FROM t; // <--

The first part of the SQL statement stored PROCEDURE is "CREATE PROCEDURE ":

Create procedure p1 () SELECT * FROM t; // <--

The second part is the process name. The new stored procedure name above is p1. Digression: Legal Identifiers: the name of a problematic stored procedure with a valid identifier is case insensitive. Therefore, 'p1' and 'p1' are the same name, in the same database, you cannot give two stored procedures the same name, because this will cause heavy load. Some DBMS support reload (Oracle support), but MySQL does not .).

You can use a compromise such as "database name. Stored Procedure name", such as "db5.p1 ". The stored procedure names can be separated. they can contain space characters. the length is limited to 64 characters, but do not use the MySQL built-in function name. if so, the following situations will occur during the call:

Mysql> CALL pi (); Error 1064 (42000 ):

You have a syntax error. mysql> CALL pi ();

Error 1305 (42000): PROCEDURE does not exist.

In the first example above, I call a function called pi, but you must add a space after the called function name, as in the second example. Create procedure p1 () SELECT