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;"