MySQL 5.0 new features tutorial stored procedures: First Lecture

mysql| Stored Procedures | tutorials

Author: MySQL AB; translation: Chen Bong

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 Stored procedure name * *

(in Parameter1 INTEGER)/* Parameters parameter * *

BEGIN/* Start of block statement size * *

DECLARE variable1 CHAR (10); /* Variables Variable declaration * *

If Parameter1 = THEN/* Start of IF if condition starts * *

SET variable1 = ' birds '; /* Assignment Assigned value * *

ELSE

SET variable1 = ' beasts '; /* Assignment Assigned value * *

End IF; /* End of If if ending/

INSERT into table1 VALUES (variable1);/* Statement SQL statement * *

End/* Ends of block statement blocks closed */

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!

MySQL fully supports the SQL 2003 standard. Some databases (such as DB2, Mimer) are also supported. However, there are also parts that are not supported, such as Oracle, SQL Server does not support. We will give you enough help and tools to make code written for other DBMS easier to transfer to MySQL.

Setting up with MySQL 5.0 setup and start 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 the preparation for our exercise, I assume that MySQL 5.0 is already installed. If you don't have a database administrator to install your database and other software, you'll need to install it yourself. But it's easy to forget one thing, that you need to have a table called Mysql.proc.

After installing the latest version, you must run the

Mysql_fix_privilege_tables

Or
mysql_install_db

(just run one of them is enough)--otherwise the stored procedure will not work. I also enable running an informal SQL script after root, as follows:
Mysql>source/home/pgulutzan/mysql-5.0/scripts/mysql_prepare_privilege_tables_for_5.sql

Starting the MySQL client to start MySQL clients

This is how I start the MySQL client. You may use other methods, if you are using a binary version or a Windows system 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 show the results of the MySQL client returning as root, which means I have great privileges.

Check for the correct version check in
To confirm that the version of MySQL used is correct, we want to query the version. I have two ways of confirming that I am using the 5.0 version:
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 will work correctly on this client.

The sample Database

The first thing to do now is to create a new database and then set the SQL to implement this step for the default database
Statement 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 and then we'll create a simple worksheet.
The SQL that implements this step
Statement 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'll find I just inserted a column in the table. The reason for this is that I want to keep the table simple, because there is no need to show the technique of querying the data, but rather to teach the stored procedure without using a large data table because it is already complex enough.

So this is the sample database, and we'll start pick a Delimiter Select the separator from the table that contains only one column named T
Now we need a separator, the SQL statement that implements this step is as follows:
DELIMITER//

For example:
Mysql> DELIMITER//

The separator is that you notify the MySQL client that you have finished typing a character or string symbol for an SQL statement. We have always used semicolons ";", but in stored procedures, this can cause a lot of problems because there are many statements in the stored procedure, so each one needs a semicolon so you need to choose a string that is unlikely to appear in your statement or program as a separator. I used a double slash "//", and someone used a vertical "|". I have seen the use of the "@" symbol in the DB2 program, but I don't like it. You can choose according to your preferences, but in this course you'd better choose the same as me in order to make it easier to understand. If you want to restore the use of ";" (semicolon) As a separator, enter the following statement:
"DELIMITER//".
Create PROCEDURE Example Creating a program instance
CREATE PROCEDURE p1 () SELECT * from T; //

Maybe this is the first stored procedure you've created with MySQL. If that's the case, it's best to write down 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 name of the new stored procedure above is P1.

Digression:legal Identifiers topic: The question of legal identifiers

Stored procedure names are insensitive to capitalization, so ' P1 ' and ' P1 ' are the same name, and in the same database you will not be able to give the same name to two stored procedures, because this will cause overloading. Some DBMS allow overloading (Oracle support), but MySQL does not support the translator: I hope to support it later. ）。

You can take a compromise such as "DB5.P1", such as "database name. Stored procedure name". The stored procedure name can be separated, it can include spaces, its length is limited to 64 characters, but be careful not to use the name of the MySQL built-in function, if so, the following will occur when the call occurs:
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, I call a function called PI, but you have to add the top of the function name to the call, as in the second example.
CREATE PROCEDURE p1 () SELECT * from T; <--

where "()" is the "argument list."
CREATE PROCEDURE

The third part of the statement is the argument list. It is often necessary to add parameters within parentheses. The stored procedure in the example has no parameters, so the argument list is empty-so I just need to type in the empty parentheses, but this is necessary.
CREATE PROCEDURE p1 () SELECT * from T; <--
"SELECT * from T;"

is the body of the stored procedure.
Then to the last part of the statement, which is the body of the stored procedure, is the general SQL statement. The statement in the procedure body
"SELECT * from T;"

Contains a semicolon, which may not be written if the statement closing symbol (//) is followed.

If you remember that I call this part of the program the subject would be a good thing, because the word (body) is a technical term used by everyone. Typically we don't use a SELECT statement in a stored procedure, just for demonstration purposes. So using such a statement, you can better see whether the program is working properly when called.

Not to be continued ...