Shulou(Shulou.com)06/01 Report--

The Introduction introduction to the new features of MySQL 5.0 tutorial is written for regular MySQL users who need to know about the new features of version 5.0. To put it simply, I introduce stored procedures, triggers, views, information architecture views, and I intend to serialize this tutorial on a daily basis. If you want to see the PDF version of the tutorial at once, please download it at down.phpv.net. I would like to thank the translator Chen Pengyi for her efforts. I hope this book can have a dialogue with you like an expert, and let you learn the knowledge you need with simple questions and examples. In order to achieve this goal, I will slowly build a concept for you from every detail, and finally I will show you a larger practical example. Before learning, you may think that this use case is very difficult, but as long as you follow the course, I believe I can grasp it soon. [@ more@] MySQL 5.0new features tutorial part 1: lecture 1

Conventions and Styles conventions and programming styles

Every time I want to demonstrate the actual code, I adjust the code that appears on the screen of the mysql client to change the font to Courier to make them look different from plain text.

Let me give you an example: mysql > DROP FUNCTION f * * query OK, 0 rows affected (0.00 sec)

If the example is large, you need to add comments between some lines and paragraphs, and I will use the "" and "- >" system to show off, you can copy the code directly to the mysql client program (if what you are reading is not an electronic version, you can download the relevant script on the mysql.com website) all the examples have passed the test on Suse 9.2 Linux and Mysql 5.0.3.

By the time you read this book, Mysql has a higher version and supports more OS, including Windows,Sparc,HP-UX. So the example here will work well on your computer. But if the operation still fails, you can consult the experienced Mysql users you know for long-term support and help.

Definition and example of A Definition and an Example

Definition and instance stored procedures are procedures stored in the library (just like subroutines in a regular language). To be exact, MySQL supports two kinds of "routines (routines)": one is the stored procedure we say, and the other is the function that can return values in other SQL statements (the same as the Mysql preloaded function, such as pi ()). I will use stored procedures more often in this book, because this is our past habit and I believe everyone will accept it.

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

There are new grammatical definitions for local variables, exception handling, loop control, and IF conditionals.

The following is an example declaration that includes a stored procedure: (translation note: for ease of reading, subsequent programs do not add any Chinese comments)

CREATE PROCEDURE procedure1 / * name stored procedure name * /

(IN parameter1 INTEGER) / * parameters parameter * /

BEGIN / * start of block statement block * /

DECLARE variable1 CHAR (10); / * variables variable declaration * /

IF parameter1 = 17 THEN / * start of start of IF IF condition * /

SET variable1 = 'birds'; / * assignment assignment * /

ELSE

SET variable1 = 'beasts'; / * assignment assignment * /

END IF; / * end of IF IF ends * /

INSERT INTO table1 VALUES (variable1); / * statement SQL statement * /

END / * end of end of block statement block * /

Below I will introduce all the details of what you can do with stored procedures. At the same time, we will introduce a new database object-triggers, because the association between triggers and stored procedures is inevitable.

Why does Why Stored Procedures use stored procedures?

Since stored procedures are new to MySQL, you naturally need to be more careful when using them.

After all, no one has ever used it before, and there aren't a lot of experienced users to take you the way they did. However, you should start thinking about transferring existing programs (perhaps in server applications, user-defined functions (UDF), or scripts) to stored procedures. You don't need a reason to do this, you have to do it.

Because stored procedures are certified technologies! Although it is new in Mysql, functions with the same functionality 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.

The storage process will make the system run faster! Although we can't prove this advantage on Mysql for the time being, the user experience is also different. All we can say is that the Mysql server makes improvements in the caching mechanism, just as Preparedstatements (preprocessing statements) does. Because there is no compiler, SQL stored procedures do not run as fast as programs written in external languages such as C #. But the main way to improve the speed is whether it can reduce the network information flow. If you need to deal with repetitive tasks that require checking, looping, multiple statements but no user interaction, you can use stored procedures saved on the server to do so. In this way, there is not so much information between the server and the client at each step of the task. For reprint, please indicate translator Chen Pengyi and reprint from: www.phpv.net

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 database logic, not an application. Stored procedures are portable! When you write a stored procedure in SQL, you know that it can run on any platform supported by Mysql, without adding additional runtime environment packages, setting licenses for program execution in the operating system, or saving stored procedures for different models of your computer! If you have written a program, such as showing check cancellation in bank transactions, people who want to know about checks can find your program.

It is saved in the database in the form of source code. This will make a meaningful connection between the data and the process of processing the data, which may be the same as the planning theory you heard in class. Stored procedures can be migrated!

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

Setting up with MySQL 5.0setup and start the MySQL 5.0service

Pass through

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 there is no database administrator to install the database and other software for you, you will need to install it yourself. But it's easy to forget that you need to have a watch called mysql.proc.

After installing the latest version, you must run

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 an informal SQL script to be run after root identity, as follows:

Mysql > source/home/pgulutzan/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, and if you are using a binary version or a 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 demonstration, I will show the results returned by the mysql client after logging in as root, which means I have great privileges. For reprint, please indicate translator Chen Pengyi and reprint from: www.phpv.net

Check for the Correct Version check version

To make sure that the version of MySQL you are using is correct, we need to query the version. I have two ways 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 properly 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 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

Here we want to avoid using actual databases with important data and then we create a simple worksheet.

The SQL that implements this step

The statement is as follows:

Mysql > CREATE DATABASE db5

Query OK, 1 row affected (0.01sec)

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 I have only inserted one 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 skills of querying data here, but to teach stored procedures, there is no need to use large data tables, because it is already complex enough.

This is the sample database, and we will start with the one-column table named t and Pick a Delimiter select the delimiter

Now we need a delimiter, and the SQL statement to implement this step is as follows:

DELIMITER / /

For example:

Mysql > DELIMITER / /

A delimiter is a character or string symbol that you notify the mysql client that you have finished entering a SQL statement. We have always used the semicolon ";", but in stored procedures, this can cause a lot of problems, because there are many statements in the stored procedure, so each needs a semicolon, so you need to choose a string that is unlikely to appear in your statement or program as a delimiter. I have used a double slash "/", and some people have used a vertical bar "|". I've seen the "@" symbol used in DB2 programs, but I don't like it. You can choose according to your preferences, but in this course, in order to make it easier to understand, you'd better choose to be like me. If you want to resume using ";" (semicolon) as the delimiter later, enter the following statement:

"DELIMITER; /".

CREATE PROCEDURE Example creates a program instance

CREATE PROCEDURE p1 () SELECT * FROM t; / /

Maybe this is the first stored procedure you created using Mysql. If so, it is best to write down this important milestone in your diary.

CREATE PROCEDURE p1 () SELECT * FROM t; / / CALL pi ()

Error 1305 (42000): PROCEDURE does not exist.

In the first example above, I called a function named pi, but you had to put a space after the name of the called function, just like in the second example.

CREATE PROCEDURE p1 () SELECT * FROM t; / / CALL p1 () / /

+-+

| | S1 |

+-+

| | 5 |

+-+

1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

Because the statement in the procedure is

SELECT * FROM t;

2. Let me say that again, another way.

Other ways of implementation

Mysql > CALL p1 () / /

The execution effect is the same as the following statement:

Mysql > SELECT * FROM t; / /

So, when you call the p1 procedure, you execute the following statement:

"SELECT * FROM t;"

Well, the main knowledge point "creating and invoking procedure methods" is clear. I hope you can say to yourself that this is quite simple. But soon we will have a series of exercises, each time adding a clause, or changing an existing clause. That way we will have a lot of clauses available before writing complex parts. For reprint, please indicate translator Chen Pengyi and reprint from: www.phpv.net

Characteristics Clauses characteristic clause

1.

CREATE PROCEDURE p2 ()

LANGUAGE SQL CREATE PROCEDURE p5 (p INT) SET @ x = p / /

Query OK, 0 rows affected (0.00 sec)

Mysql > CALL p5 (12345) / /

Query OK, 0 rows affected (0.00 sec)

Mysql > SELECT @ xUnip /

+-+

| | @ x |

+-+

| | 12345 |

+-+

1 row in set (0.00 sec)

This IN example demonstrates the process of having input parameters. In the process body, I set the session variable x to the value of parameter p. The procedure is then called, passing 12345 in the parameter p. Choosing to display the session variable @ x proves that we have passed in the parameter value of 12345.

Example of OUT example output

Mysql > CREATE PROCEDURE p6 (OUT p INT)

-> SET p =-5 / /

Mysql > CALL p6 (@ y) / /

Mysql > SELECT @ yplink /

+-+

| | @ y |

+-+

| |-5 |

+-+

This is another example. This time p is the output parameter, and then the value of p is passed into the session variable @ y in the procedure call. For reprint, please indicate translator Chen Pengyi and reprint from: www.phpv.net

In the process body, we assign a value of-5 to the parameter, and after the call, we can see that OUT tells DBMS that the value is coming from the procedure.

Similarly, we can use sentences.

"SET @ y =-5;"

To achieve the same effect.

Compound Statements compound statement

Now let's take a detailed analysis of the process body:

CREATE PROCEDURE p7 ()

BEGIN

SET @ a = 5

SET @ b = 5

INSERT INTO t VALUES (@ a)

SELECT S1 * @ a FROM t WHERE S1 > = @ b

END; / / * I won't CALL this.

This statement will not be called

, /

The construction of the finished process body is the BEGIN/END block. This BEGIN/END statement block is basically the same as the BEGIN/END in the Pascal language and very similar to the framework of the C language. We can use blocks to encapsulate multiple statements. In this example, we used several statements to set session variables, and then completed some insert and select statements. If you have more than one statement in the body of your procedure, you need a BEGIN/END block. BEGIN/END blocks are also known as compound statements, where you can do variable definition and flow control.

Lesson 3: new SQL statement, Loops Loop statement

The New SQL Statements New SQL statement

Variables variable

The instruction to declare a variable in a compound statement is DECLARE.

(1) Example with two DECLARE statements

Two examples of DECLARE statements

CREATE PROCEDURE p8 ()

BEGIN

DECLARE an INT

DECLARE b INT

SET a = 5

SET b = 5

INSERT INTO t VALUES (a)

SELECT S1 * a FROM t WHERE S1 > = b

END; / / * I won't CALL this * /

The variables defined in the process are not really defined, you just define them in the BEGIN/END block.

Note that these variables are different from session variables. You cannot use the modifier @ you must clearly declare variables and their types in the BEGIN/END block.

Once a variable is declared, you can use it anywhere you can use session variables, text, and column names.

(2) Example with no DEFAULT clause and SET statement

No examples of default clauses and setting statements

CREATE PROCEDURE p9 ()

BEGIN

DECLARE an INT / * there is no DEFAULT clause * /

DECLARE b INT / * there is no DEFAULT clause * /

SET a = 5; / * there is a SET statement * /

SET b = 5; / * there is a SET statement * /

INSERT INTO t VALUES (a)

SELECT S1 * a FROM t WHERE S1 > = b

END; / / * I won't CALL this * /

There are many ways to initialize variables. If there is no default clause, the initial value of the variable is NULL. You can use the set statement to assign values to variables at any time.

(3) Example with DEFAULT clause

An example with a DEFAULT clause

CREATE PROCEDURE p10 ()

BEGIN

DECLARE a, b INT DEFAULT 5

INSERT INTO t VALUES (a)

SELECT S1 * a FROM t WHERE S1 > = b

END; / /

We have made some changes here, but the result is still the same. The DEFAULT clause is used here to set the beginning

Starting value, which eliminates the need to separate the implementation of the DECLARE and SET statements.

(4) Example of CALL

An example of a call

Mysql > CALL p10 () / /

+-+

| | S1 * a |

+-+

| | 25 |

| | 25 |

+-+

2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

The results show that the process can work properly.

(5) Scope

Scope

CREATE PROCEDURE p11 ()

BEGIN

DECLARE x1 CHAR (5) DEFAULT 'outer'

BEGIN

DECLARE x1 CHAR (5) DEFAULT 'inner'

SELECT x1

END

SELECT x1

END; / /

Now let's talk about the scope. There are nested BEGIN/END blocks in the example, which is legal, of course. It also contains two variables, both with the name x1, which is also legal. Internal variables have higher priority in their scope. When the END statement is executed, the internal variable disappears, it is already outside its scope, and the variable is no longer visible, so the declared variable can no longer be found outside the stored procedure, but you can save its value through the OUT parameter or by assigning its value to the session variable.

The procedure for invoking the scope example:

Mysql > CALL p11 () / /

+-+

| | x1 |

+-+

| | inner |

+-+

+-+

| | x1 |

+-+

| | outer |

+-+

When we see the result, the first SELECT statement retrieves the innermost variable, and the second retrieves the second variable

Conditions and IF-THEN-ELSE conditional and IF-THEN-ELSE

1.

Now we can write some examples that contain conditions:

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT

SET variable1 = parameter1 + 1

IF variable1 = 0 THEN

INSERT INTO t VALUES (17)

END IF

IF parameter1 = 0 THEN

UPDATE t SET S1 = S1 + 1

ELSE

UPDATE t SET S1 = S1 + 2

END IF

END; / /

Here is a procedure that contains IF statements. There are two IF statements, one is the IF statement END IF, and the other is the IF statement ELSE statement END IF. We can use complex processes here, but I'll try to make it simple so that you can figure it out more easily.

two。

CALL p12 (0) / /

We call this procedure, passing in a value of 0, so that the value of parameter1 will be 0.

3.

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT

SET variable1 = parameter1 + 1; SELECT * FROM t /

+-+

| | S1 |

+-+

| | 6 |

| | 6 |

| | 19 |

+-+

3 rows in set (0.00 sec)

After the procedure is executed, the value 1 is passed, as in the example above, and the value 19 is inserted into table t.

Question

problem

Question: what is the purpose of CALL p13 (NULL) / /?

Another: what actions does this CALL statement do?

You can observe what SELECT does after execution, or you can judge from the code and make it in 5 seconds.

Answer

Answer

Mysql > CALL p13 (NULL) / /

Query OK, 1 row affected (0.00 sec)

Mysql > SELECT * FROM tbat /

+-+

| | S1 |

+-+

| | 6 |

| | 6 |

| | 19 |

| | 19 |

+-+

4 rows in set (0.00 sec)

The answer is that when you call p13, MySQL inserts another record with a value of 19. The reason is that the variable variable1 is executed when its value is the ELSE part of the NULL,CASE statement. I hope this means something to all of you. If you can't answer, there's no question, we can keep going.

Loops loop statement

WHILE... END WHILE

LOOP... END LOOP

REPEAT... END REPEAT

GOTO

Next we will create some loops. We have three standard loops:

WHILE loop, LOOP loop and REPEAT loop. There is also a non-standard way to cycle: GO TO.

WHILE... END WHILE

CREATE PROCEDURE p14 ()

BEGIN

DECLARE v INT

SET v = 0

WHILE v

< 5 DO INSERT INTO t VALUES (v); SET v = v + 1; END WHILE; END; // 这是WHILE循环的方式。我很喜欢这种方式，它跟IF语句相似，因此不需要掌握很多新的语法。这里的INSERT和SET语句在WHILE和END WHILE之间，当变量v大于5的时候循环将会退出。使用 "SET v = 0;" 语句使为了防止一个常见的错误，如果没有初始化，默认变量值为NULL，而NULL和任何值操作结果都为NULL。 WHILE ... END WHILE example mysql>

CALL p14 () / /

Query OK, 1 row affected (0.00 sec)

This is the result of calling procedure p14 without paying attention to whether the system returns "one row affected" or "five rows affected", because the count here only counts the last INSERT action.

WHILE... END WHILE example: CALL

Mysql > select * from t; / /

+-+

| | S1 |

+-+

....

| | 0 |

| | 1 |

| | 2 |

| | 3 |

| | 4 |

+-+

9 rows in set (0.00 sec)

After the call, you can see that the program inserts five rows into the database.

REPEAT... END REPEAT

CREATE PROCEDURE p15 ()

BEGIN

DECLARE v INT

SET v = 0

REPEAT

INSERT INTO t VALUES (v)

SET v = v + 1

UNTIL v > = 5

END REPEAT

END; / /

This is an example of a REPEAT loop with the same function as the previous WHILE loop. The difference is that it checks the results after execution, while WHILE checks before execution. (translator: it may be equivalent to DO WHILE)

REPEAT... The role of END REPEAT: look at the UNTIL: UNTIL

CREATE PROCEDURE p15 ()

BEGIN

DECLARE v INT

SET v = 0

REPEAT

INSERT INTO t VALUES (v)

SET v = v + 1

UNTIL v > = 5 CALL p15 () / /

Query OK, 1 row affected (0.00 sec)

Mysql > SELECT COUNT (*) FROM tUnip /

+-+

| | COUNT (*) |

+-+

| | 14 |

+-+

1 row in set (0.00 sec)

We can see that five rows of records are inserted after calling the p15 procedure

LOOP... END LOOP

CREATE PROCEDURE p16 ()

BEGIN

DECLARE v INT

SET v = 0

Loop_label: LOOP

INSERT INTO t VALUES (v)

SET v = v + 1

IF v > = 5 THEN

LEAVE loop_label

END IF

END LOOP

END; / /

The above is an example of a LOOP loop.

The LOOP loop does not require an initial condition, which is similar to the WHILE loop, but it also does not need an end condition like the REPEAT loop.

LOOP... END LOOP: with IF and LEAVE LOOP loop containing IF and LEAVE

CREATE PROCEDURE p16 ()

BEGIN

DECLARE v INT

SET v = 0

Loop_label: LOOP

INSERT INTO t VALUES (v)

SET v = v + 1

IF v > = 5 THEN CALL p16 () / /

Query OK, 1 row affected (0.00 sec)

Mysql > SELECT COUNT (*) FROM tUnip /

+-+

| | COUNT (*) |

+-+

| | 19 |

+-+

1 row in set (0.00 sec)

After calling procedure p16, the result is that the other five rows are inserted into table t.

Labels label

CREATE PROCEDURE p17 ()

Label_1: BEGIN

Label_2: WHILE 0 = 1 DO LEAVE label_2; END

WHILE

Label_3: REPEAT LEAVE label_3; UNTIL 0 = 0

END REPEAT

Label_4: LOOP LEAVE label_4; END LOOP

END; / /

I used the statement label in the last loop example. Now here is an example of a procedure that contains four statement labels. We can use statement labels before BEGIN, WHILE, REPEAT, or LOOP statements, and statement labels can only be used before legitimate statements. So "LEAVE label_3" means leaving a statement or compound statement whose label name is defined as label_3.

End Labels label Terminator

CREATE PROCEDURE p18 ()

Label_1: BEGIN

Label_2: WHILE 0 = 1 DO LEAVE label_2; END

WHILE label_2

Label_3: REPEAT LEAVE label_3; UNTIL 0 = 0

END REPEAT label_3

Label_4: LOOP LEAVE label_4; END LOOP

Label_4

END label_1; / /

You can also use the statement label at the end of the statement, just as you do at the beginning. These label terminators are not very useful.

They are optional. If you need them, they must be the same as the label names defined at the beginning, of course, in order to have good programming habits.

To make it easy for others to read, it is best to use a label Terminator.

LEAVE and Labels pop-out and labeling

CREATE PROCEDURE p19 (parameter1 CHAR)

Label_1: BEGIN

Label_2: BEGIN

Label_3: BEGIN

IF parameter1 IS NOT NULL THEN

IF parameter1 ='a 'THEN

LEAVE label_1

ELSE BEGIN

IF parameter1 ='b' THEN

LEAVE label_2

ELSE

LEAVE label_3

END IF

END

END IF

END IF

END

END

END;//

LEAVE

Statement causes the program to jump out of complex compound statements.

ITERATE

Iteration if the goal is the ITERATE statement, the level statement must be used.

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT

SET v = 0

Loop_label: LOOP

IF v = 3 THEN

SET v = v + 1

ITERATE loop_label

END IF

INSERT INTO t VALUES (v)

SET v = v + 1

IF v > = 5 THEN

LEAVE loop_label

END IF

END LOOP

END; / /

ITERATE

(iterative) statement, like the LEAVE statement, is also a circular reference within the loop, it is a bit like the "Continue" in the C language, and it can also appear in the compound statement, referring to the compound statement label, ITERATE (iteration) means to restart the compound statement.

So let's start and observe the following loop, which is a loop that requires an iterative process:

ITERATE: Walking through the loop

Deep cycle

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT

SET v = 0

Loop_label: LOOP = 5 THEN

LEAVE loop_label

END IF

END LOOP

END; / /

Get this labeled loop running.

ITERATE: Walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT

SET v = 0

Loop_label: LOOP

IF v = 3 THEN = 5 THEN

LEAVE loop_label

END IF

END LOOP

END; / /

V becomes 3, and then we increase it to 4.

ITERATE: walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT

SET v = 0

Loop_label: LOOP

IF v = 3 THEN

SET v = v + 1

ITERATE loop_label; = 5 THEN

LEAVE loop_label

END IF

END LOOP

END; / /

Then start the ITERATE (iterative) process.

ITERATE: walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT

SET v = 0

Loop_label: LOOP = 5 THEN

LEAVE loop_label

END IF

END LOOP

END; / /

The ITERATE here brings the loop back to the head of the loop.

ITERATE: walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT

SET v = 0

Loop_label: LOOP

IF v = 3 THEN

SET v = v + 1

ITERATE loop_label

END IF

INSERT INTO t VALUES (v)

SET v = v + 1

IF v > = 5 THEN

LEAVE loop_label; = 5 THEN

LEAVE loop_label

END IF

END LOOP

END; / / CREATE TABLE t2

S1 INT, PRIMARY KEY (S1))

Engine=innodb;//

Mysql > CREATE TABLE T3 (S1 INT, KEY (S1)

FOREIGN KEY (S1) REFERENCES T2 (S1))

Engine=innodb;//

Mysql > INSERT INTO T3 VALUES (5); / /

...

ERROR 1216 (23000): Cannot add or update a child row: a foreign key

Constraint fails (here is the error message of the system)

I started to create a primary key table and a foreign key table. We are using InnoDB, so the foreign key association check is called

It's open. Then when I insert a value from the non-primary key table into the foreign key table, the action will fail. Of course, under these conditions, it can be very

Find the error number 1216. For reprint, please indicate translator Chen Pengyi and reprint from: www.phpv.net

3. Sample Problem: Log Of Failures

CREATE TABLE error_log (error_message

CHAR (80) / /

The next step is to create a table that stores errors when an insert goes wrong.

4. Sample Problem: Log Of Errors

CREATE PROCEDURE p22 (parameter1 INT)

BEGIN

DECLARE EXIT HANDLER FOR 1216

INSERT INTO error_log VALUES

(CONCAT ('Time:', current_date)

Play. Foreign Key Reference Failure For

Value =', parameter1))

INSERT INTO T3 VALUES (parameter1)

END;//

That's our program. The first statement here, DECLARE EXIT HANDLER, is used to handle exceptions. Meaning

The idea is that if error 1215 occurs, the program will insert a row in the error record table. EXIT means to exit the compound statement when the action is successfully submitted. For reprint, please indicate translator Chen Pengyi and reprint from: www.phpv.net

5. Sample Problem: Log Of Errors

CALL p22 (5) / /

Calling this stored procedure will fail, which is normal because the value of 5 does not appear in the primary key table. But there is no error message

Return is already included in the process because of the error. Nothing is added to the T3 table, but it is recorded in the error_ log table

Some information is given, which tells us that the INSERT into table T3 action failed.

DECLARE HANDLER syntax declares the syntax for exception handling

DECLARE

{EXIT | CONTINUE}

HANDLER FOR

{error-number | {SQLSTATE error-string} | condition}

SQL statement

The above is the use of error handling, that is, a piece of code that automatically triggers when a program goes wrong. MySQL allows two processors

One is EXIT processing, which is what we just used. The other is what we're going to demonstrate, CONTINUE processing.

It is similar to EXIT processing, except that after it is executed, the original main program continues to run, so the compound statement is not issued.

I have a mouth.

1. DECLARE CONTINUE HANDLER example CONTINUE processing example

CREATE TABLE T4 (S1 int,primary key (S1)); / /

CREATE PROCEDURE p23 ()

BEGIN

DECLARE CONTINUE HANDLER

FOR SQLSTATE '23000' SET @ x2 = 1

SET @ x = 1

INSERT INTO T4 VALUES (1)

SET @ x = 2

INSERT INTO T4 VALUES (1)

SET @ x = 3

END;//

This is an example of CONTINUE processing in the MySQL reference manual, and this example is very good, so I copied it here.

From this example we can see how CONTINUE processing works.

2. DECLARE CONTINUE HANDLER declares CONTINUE exception handling

CREATE TABLE T4 (S1 int,primary key (S1)); / /

CREATE PROCEDURE p23 ()

BEGIN

DECLARE CONTINUE HANDLER

FOR SQLSTATE '23000' SET @ x2 = 1; DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END

-> DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END

-> DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END

-> END;//

Query OK, 0 rows affected (0.00 sec)

Here are three pre-declared conditions: NOT FOUND (line not found), SQLEXCEPTION (error), and SQLWARNING (

Warnings or comments). Because they are predeclared, they can be used without declaring conditions. But if you make a statement like "DECLARE SQLEXCEPTION CONDITION...", you will get an error message.

Cursors cursor

Summary of cursor implementation functions:

DECLARE cursor-name CURSOR FOR SELECT...

OPEN cursor-name

FETCH cursor-name INTO variable [, variable]

CLOSE cursor-name

Now we're starting to focus on cursors. Although the cursor syntax in our stored procedure has not been fully implemented,

You can already complete basic transactions such as declaring cursors, opening cursors, reading from cursors, and closing cursors.

1. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)

BEGIN

DECLARE a,b INT

DECLARE cur_1 CURSOR FOR SELECT s1 FROM t

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET b = 1

OPEN cur_1

REPEAT

FETCH cur_1 INTO a

UNTIL b = 1

END REPEAT

CLOSE cur_1

SET return_val = a

END;//

Let's look at a new example of a stored procedure that contains cursors.

2. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)

BEGIN

DECLARE a,b INT

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.