MySQL5.0 new feature tutorial stored procedure: Lecture 1 _ MySQL

Author: mysqlAB. 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. I 'd like to ask you a simple question: mysql AB.

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 (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.

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 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 Mysql preload 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 */

ELSE

SET 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 begin to consider transferring existing programs (probably in server applications, user-defined functions (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.

Stored procedures make the system run faster! Although we cannot prove this advantage in Mysql for the time being, the user experience is different. What we can say is that the Mysql server has improved the cache mechanism, just like what Preparedstatements does. Because there is no compiler, SQL Stored procedures do not run as quickly as external languages (such as C. However, the main way to speed up is to reduce network information traffic. If you want to handle repetitive tasks that require check, loop, and multi-statement operations without user interaction, you can use the stored procedure on the server. In this way, there will be less information between the server and the client at each step of the task.

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: Question about valid Identifiers

The stored procedure name is case-insensitive. Therefore, 'p1' and 'p1' are the same name. in the same database, you cannot give the two stored procedures the same name, 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 * FROM t; // <--

"()" Is the "parameter list ".
CREATE PROCEDURE

The third part of the statement is the parameter list. You usually need to add parameters in brackets. The stored procedure in this example has no parameters, so the parameter list is empty-so I only need to type empty parentheses, but this is required.
Create procedure p1 () SELECT * FROM t; // <--
"SELECT * FROM t ;"

Is the subject of the stored procedure.
The last part of the statement is the body of the stored procedure, which is a general SQL statement. Statement in process body
"SELECT * FROM t ;"

Contains a semicolon. if there is a statement end symbol (//), you can leave it empty.

If you still remember that it would be a good thing to call this part the subject of a program, because the word body is a technical term used by everyone. Generally, we will not use the SELECT statement in the stored procedure. this is just for demonstration. Therefore, when using such statements, you can better check whether the program works normally during the call.

To be continued...