MySQL 5.0 new features tutorial stored procedures: Second lecture

Why MySQL statements are Legal in a Procedure body

What is a MySQL statement that is legal in the stored procedure body?

What kind of SQL statement is legitimate in a MySQL stored procedure? You can create a statement that contains inserts, Update,delete, SELECT, DROP, create, replace, and so on. The only thing you need to keep in mind is that if the code contains MySQL extensions, then the code will not be ported. In standard SQL statements: Any database definition language is legal, such as:

CREATE PROCEDURE P () DELETE from T; //

SET, Commit, and rollback are also legal, such as:

CREATE PROCEDURE P () SET @x = 5; //

MySQL's additional functionality: Any data manipulation language statements will be legal.

CREATE PROCEDURE P () DROP TABLE t; //

MySQL Extensions: Direct Select is also legal:

CREATE PROCEDURE P () SELECT ' a '; //

Incidentally, I call the functionality of the DDL statement included in the stored procedure the MySQL add-on because it is defined as non-core in the SQL standard and the component can be selected.

There is a constraint in the process body that cannot have a database operation statement for a routine or table operation. For example, the following example is illegal:

CREATE PROCEDURE P1 ()

CREATE PROCEDURE p2 () DELETE from T; //

The following are the new statements to MySQL 5.0来 that are illegal in the process body:

Create PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, create FUNCTION,

Drop FUNCTION, CREATE TRIGGER, drop TRIGGER.

But you can use

CREATE PROCEDURE db5.p1 () DROP DATABASE db5//

But similar

"Use Database"

Statements are also illegal because MySQL assumes that the default database is the work place for the process.

Call the Procedure the stored procedure

1.

Now we can call a stored procedure, all you need to enter is called and your procedure name and one bracket again, the parentheses are necessary when you call the P1 procedure inside the example, the result is that the screen returns the contents of the T table.

Mysql> call P1 ()//

+------+

| S1 |

+------+

| 5 |

+------+

1 row in Set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

Because the statements in the procedure are

"SELECT * from T;"

2. Let me say that again, another way.

Other ways to implement

Mysql> call P1 ()//

As in the following statement:

Mysql> SELECT * from T; //

So, when you call the P1 process, you execute the following statement:

"SELECT * from T;"

Well, the main point of knowledge "creating and invoking process methods" is clear. I hope you can tell yourself it's quite simple. But soon we will have a series of exercises, each time adding a note, or changing the existing clause. That way we'll have a lot of clauses available before we write complex parts.

Characteristics clauses feature clause

1.

CREATE PROCEDURE P2 ()

LANGUAGE SQL <--

Not deterministic <--

SQL Security Definer <--

COMMENT ' A Procedure ' <--

SELECT current_date, RAND () from t//

Here I give you some clauses that reflect the characteristics of the stored procedure. The contents of the clause are preceded by parentheses before the body. These clauses are optional, what are their roles?

2.

CREATE PROCEDURE P2 ()

LANGUAGE SQL <--

Not deterministic

SQL Security Definer

COMMENT ' A Procedure '

SELECT current_date, RAND () from t//

Well, this language SQL clause doesn't work. Only to illustrate that the body of the following procedure is written in the SQL language. This is the default for the system, but it is useful to declare it here because some DBMS (IBM's DB2) need it, and if you are concerned about DB2 compatibility, it is best to use it. In addition, stored procedures that are supported by languages other than SQL may appear in the future.

3.

CREATE PROCEDURE P2 ()

LANGUAGE SQL

Not deterministic <--

SQL Security Definer

COMMENT ' A Procedure '

SELECT current_date, RAND () from t//

The next clause, not deterministic, is the information that is passed to the system. The definition of a process here is the same program that outputs the same output every time it is entered. In this case, since the body contains a SELECT statement, the return must be unknown so we call it not deterministic. But MySQL's built-in optimizer won't pay attention to this, at least not now.

4.

CREATE PROCEDURE P2 ()

LANGUAGE SQL

Not deterministic

SQL Security Definer <--

COMMENT ' A Procedure '

SELECT current_date, RAND () from t//

The next clause is SQL security, which can be defined as SQL Security Definer or SQL security invoker.

This goes into the realm of permission control, and of course we will have examples of test permissions later on.

SQL Security Definer

means that the permissions of the user who created the procedure are checked at call time (another option is sqlsecurity invoker).

For now, use

SQL Security Definer

Instructions tell the MySQL server to check the user of the creation process, and when the procedure has been invoked, the user who executes the calling procedure is not checked. The other option (Invoker) is to tell the server to check the caller's permissions in this step.

5.

CREATE PROCEDURE P2 ()

LANGUAGE SQL

Not deterministic

SQL Security Definer

COMMENT ' A Procedure ' <--

SELECT current_date, RAND () from t//

COMMENT ' A procedure '

is an optional annotation description.

Finally, the annotation clause is stored with the procedure definition. This is not a fixed standard, I will point out that there are no fixed standard statements, but fortunately these are very few in our standard SQL.

6.

CREATE PROCEDURE P2 ()

LANGUAGE SQL

Not deterministic

SQL Security Definer

COMMENT '

SELECT current_date, RAND () from t//

The above procedure is equivalent to the following statement:

CREATE PROCEDURE P2 ()

SELECT current_date, RAND () from t//

A feature clause also has a default value, which, if omitted, is equivalent to:

LANGUAGE SQL not deterministic SQL security Definer COMMENT '

Digressions some digression

Digression:

The result of calling P2 ()//

Mysql> call P2 ()//

+--------------+-----------------+

| current_date | RAND () |

+--------------+-----------------+

| 2004-11-09 | 0.7822275075896 |

+--------------+-----------------+

1 row in Set (0.26 sec)

Query OK, 0 rows affected (0.26 sec)

When the calling procedure P2, a SELECT statement is executed to return the random number we expect to get.

Digression:sql_mode unchanging

It won't change.

Sql_mode

mysql> set sql_mode= ' ANSI '//

Mysql> CREATE PROCEDURE P3 () Select ' A ' | | B '//

Mysql> set sql_mode= '//

Mysql> Call P3 ()//

+------------+

| ' A ' | | ' B ' |

+------------+

| AB |

+------------+

MySQL automatically maintains a running environment when the process is created. For example, we need to use two vertical bars to connect strings but this is only legal when SQL mode is ANSI. If we change SQL mode to Non-ansi, don't worry, it still works, as long as it works the first time it is used.

Exercise Practice

Question

Problem

If you don't mind practicing, try to deal with these requests without looking at the answer below.

Create a process that displays ' Hello world '. Take about 5 seconds to think about it, and since you've learned it, this should be easy. When you think about the problem, we randomly select some of the things we have just said review:

Deterministic

A (deterministic) clause is a clause that reflects the output and input dependency characteristics ... The calling procedure uses the call Procedure name (argument list). Well, I guess the time is up.

Answer

Answer

Well, the answer is to include in the process body

"Select ' Hello, World '"

Statement

Mysql

Mysql> CREATE PROCEDURE P4 () SELECT ' Hello, World '/

Query OK, 0 rows Affected (0.00 sec)

Mysql> call P4 ()//

+--------------+

| Hello, World |

+--------------+

| Hello, World |

+--------------+

1 row in Set (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Parameters parameters

Let's take a further look at how to define parameters in stored procedures

1. CREATE PROCEDURE P5

() ...

2. CREATE PROCEDURE P5

([in] name Data-type) ...

3. CREATE PROCEDURE P5

(out name Data-type) ...

4. CREATE PROCEDURE P5

(INOUT name Data-type) ...

Recall that the previous argument list must be in parentheses after the stored procedure name. The argument list in the first example above is empty, and the second example has an input parameter. The word here is optional because the default parameter is in (input).

The third example has an output parameter, and the fourth example has a parameter that can be either input or output.

Examples of input in example

Mysql> 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 @x//

+-------+

| @x |

+-------+

| 12345 |

+-------+

1 row in Set (0.00 sec)

This in example demonstrates the process of having input parameters. In the procedure body I set the session variable x to the value of the parameter p. Then call the procedure and pass 12345 to the argument p. Choose to display the session variable @x, proving that we have passed the parameter value 12345.

Example of out example output

Mysql> CREATE PROCEDURE P6 (out P INT)

-> SET p =-5//

Mysql> call P6 (@y)//

Mysql> SELECT @y//

+------+

| @y |

+------+

| -5 |

+------+

This is another example. This time p is an output parameter, and then the value of P is passed into the session variable @y in the procedure call.

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

Also we can use the statement

"SET @y =-5;"

To achieve the same effect.

Compound Statements Compound statement

Now let's start with 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 invoked

*/

The construction of the process body is begin/end block. This begin/end statement block is essentially the same as the begin/end in Pascal, and the framework of the C language is very similar. We can use blocks to encapsulate more than one statement. In this example, we used several statements that set session variables and then completed some inserts and select statements. If you have more than one statement in your process body, you need to begin/end the block. Begin/end blocks are also called compound statements, where you can make variable definitions and process controls.

Not to be continued ...