Mysql5.0 Stored Procedure Implementation and PHP call Stored Procedure

1. Create a stored procedure

1. Basic Syntax:
Delimiter //

Create procedure sp_name ()
Begin
.........
End //

Delimiter;/* exit the declaration code segment form of delimiter. If it is missing, you need to "End //" each time to end code execution /*/

The following is a non-parameter MySQL stored procedure:

The following is a MySQL stored procedure with parameters:

The following is the Stored Procedure for MySQL editing.

2. parameter transfer

Ii. Call the Stored Procedure

1. Basic Syntax: Call sp_name ()
Note: The stored procedure name must be enclosed in parentheses, even if the stored procedure has no parameters

Iii. delete stored procedures

1. Basic Syntax:
Drop procedure sp_name
2. Notes
(1) You cannot delete another stored procedure in one stored procedure. You can only call another stored procedure.

4. blocks, conditions, and loops

1. Block definition, commonly used
Begin
......
End;
You can also create an alias for the block, such:
Lable: Begin
...........
End lable;
You can use leave lable to jump out of the block and execute code after the block.
2. conditional statements

If condition then
Statement
Else
Statement
End if;
3. Loop statements
(1). While Loop

[Label:] While expression do

Statements

End while [label];

(2) loop

[Label:] Loop

Statements

End loop [label];

(3). Repeat until Loop

[Label:] Repeat

Statements

Until expression

End repeat [label];

5. Other Common commands

1.Show procedure status
Displays basic information about all stored procedures in the database, including the database, stored procedure name, and creation time.
2.Show create procedure sp_name
Displays detailed information about a stored procedure.

<? PHP
Define ('client _ multi_results ', 131072); // confirm to return a result set

$ Link = mysql_connect ("127.0.0.1", "root", "", 1, client_multi_results) or die ("cocould not connect:". mysql_error ());
Mysql_select_db ("vs") or die ("cocould not select database ");
?>

<? PHP
$ Result = mysql_query("Call sp_user_insert ('{$ _ post ['txtname']}', '{$ _ post ['txtpwd']}')")Or die ("query failed:". mysql_error ());
While ($ ROW = mysql_fetch_array ($ result, mysql_assoc ))
{
$ Line = '<tr> <TD> <a target = _ blank href = \''. $ row ["url"]. '\'> '. $ row ["title"]. '('. $ row ["page_time"]. ')'. '</a> </TD> </T
R> ';
Echo $ line;
Printf ("\ n ");

}
Mysql_free_result ($ result );
?>

 

<? PHP
Mysql_close ($ link );
?>

Note the differences between MySQL syntax and MSSQL when writing

Abstract:
SQL Server
The parameter name must start.
MySQL
The parameter name is a regular identifier.

SQL Server
Multiple declarations can be made at the same time, for example, "declare V1 [data type], V2 [data type]".
MySQL
Only one statement can be declared at a time, for example, "declare V1 [data type]; declare V2 [data type]".

SQL Server
There is no begin... end in the Stored Procedure body.
MySQL
The begin... end statement must exist.

SQL Server
You do not need to end.
MySQL
';' Must be used as the statement end mark, except for the last statement.

SQL Server
You can set "set nocount" and "If @ rowcount" to determine,
MySQL
None of the above, but you can use found_rows () for judgment.

SQL Server
Use the "while... begin" statement.
MySQL
Use the "while... Do" statement.

SQL Server
Select is allowed.
MySQL
Only set can be assigned.

SQL Server
Allow table access in Functions
MySQL
Table access is not allowed in functions.