Creating the Procedure
You can create a stored program with the CREATE PROCEDURE , CREATE FUNCTION , or CREATE TRIGGER statement. It is possible to enter these statements directly at the MySQL command line, but this is not practical for stored programs of more than trivial length, so the best thing for us to do is to create a text file containing our stored program text. Then we can submit this file to the database using the command-line client or another tool.
We will use the MySQL Query Browser as a text editor in this example. If you don’t have this tool, you can download it fromhttp://dev.mysql.com/downloads/. Alternately, you could use an OS text editor such as vi, emacs, or Notepad. We like the MySQL Query Browser because of its built-in help system, syntax highlighting, ability to run SQL statements, and lots of other features.
Follow these steps:
Run the MySQL Query browser. On Windows, from the Start menu select Programs → MySQL → MySQL Query Browser. On Linux, type mysql-query-browser from the command line.
Select File → New Script tab from the menu to create a blank script window.
Enter your stored program command text.
Figure 2-1 shows our first stored procedure.
We then use the File → Save As menu option to save our file so that we can execute it from the mysql client.
Figure 2-1. A first stored procedure
This first stored procedure is very simple, but let’s examine it line by line to make sure you understand it completely:
[td]
Line
| Explanation |
1
| Issue the DELIMITER command to set '$$' as the end of a statement. Normally, MySQL regards ";" as the end of a statement, but since stored procedures contain semicolons in the procedure body, we need to use a different delimiter. |
3
| Issue a DROP PROCEDURE IF EXISTS statement to remove the stored procedure if it already exists. If we don’t do this, we will get an error if we then try to re-execute this file with modifications and the stored procedure exists. |
4
| The CREATE PROCEDURE statement indicates the start of a stored procedure definition. Note that the stored procedure name "HelloWorld" is followed by an empty set of parentheses "( )". If our stored procedure had any parameters, they would be defined within these parentheses. This stored procedure has no parameters, but we need to include the parentheses anyway, or we will get a syntax error. |
5
| The BEGIN statement indicates the start of the stored procedure program. All stored programs with more than a single statement must have at least one BEGIN and END block that defines the start and end of the stored program. |
6
| This is the single executable statement in the procedure: a SELECT statement that returns "Hello World" to the calling program. As you will see later, SELECT statements in stored programs can return data to the console or calling program just like SELECT statements entered at the MySQL command line. |
7
| The END statement terminates the stored procedure definition. Note that we ended the stored procedure definition with $$ so that MySQL knows that we have completed the CREATE PROCEDURE statement. |
With our definition stored in a file, we can now use the mysql client to create and then execute the HelloWorld stored procedure, as shown in Example 2-2.
发帖
雷达卡
MySQL Stored Procedure Programming.rar
(4.81 MB, 需要: 20 个论坛币)
本附件包括：
京公网安备 11010802022788号
