Mysq Stored Procedure

View the stored procedures in the MySQL database

 

Method 1: Select 'name' from mysql. Proc where DB = 'your _ db_name 'and 'type' = 'Procedure'

Method 2: Show procedure status;

 

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.

Run the SQL script file in the MySQL database.

Method 1: If the database is not connected, enter mysql-H localhost-u root-P 123456 <D:/book. SQL and press Enter;
Method 2: When the database has been connected, the command prompt is mysql>. Enter source D:/book. SQL or/. D:/book. SQL and press Enter.

 

MySQL Stored Procedure Call Method

The calling method of the MySQL stored procedure is frequently used. The following describes the calling method of the MySQL stored procedure. It is helpful for you to learn about the MySQL stored procedure.

First enter the MySQL command line Environment Create a function and select a database. Delimiter $ is set $ as the command termination symbol, instead of the semicolon, because the semicolon has other functions Now restore the semicolon as the Terminator and call the function. Delete A Function In this way, PHP calls mysql_query ("select quqiufeng ('field name ')"); Stored Procedure Delimiter first $ Call quqiufeng (); // call the MySQL Stored Procedure PHP call Method 3, 1. Create a simple stored procedure in MySQL Create procedure students_copy_proc (in ref_note varchar (255 )) Begin Replace into students_backup Select *, ref_note from students; End | 2. Is the comparison between the root @ % and root @ localhost accounts different? Of course, the usage is also different. When we set the MySQL connection, these two accounts are required (server) (User); the server is the IP address, domain name, or machine name of your MySQL server, and the user is the account connecting to the SQL database, as shown below ----------------------------------------------------------- Account @ server address ----------------------------------------------------------- Root @ % root @ localhost ----------------------------------------------------------- The key lies in the server address. In this address, @ % indicates that any address can be connected. @ localhost indicates that you can only use localhost as the local address for connection, when installing MySQL 4.1.12, there is only one root @ localhost account. It can be said that MySQL 4.1.12 has enhanced security. If my MySQL server is installed on the machine 192.168.0.100, if I only have the account root @ localhost, I can only connect to the MySQL database at 192.168.0.100 with the localhost ID. If I have a root @ % account, I can use 127.0.0.1 \ 192.168.0.100 can connect to the database, so sometimes we need to create an account when we need to connect to other machines! 3. Execute the stored procedure in the PHP File When PHP executes the MySQL stored procedure, the following error occurs: commands out of sync; you can't run this command now solve the problem by calling the MySQL stored procedure in the same thing in PHP and then executing another or multiple commands again (or executing multiple commands in the same thing stored Procedures ), if you use the query method of mysqli to obtain the result, you will get an error: commands out of sync; you can't run this command now SSS first to provide the Code:

View the stored procedures in the MySQL database

 

Method 1: Select 'name' from mysql. Proc where DB = 'your _ db_name 'and 'type' = 'Procedure'

Method 2: Show procedure status;

 

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.

Run the SQL script file in the MySQL database.

Method 1: If the database is not connected, enter mysql-H localhost-u root-P 123456 <D:/book. SQL and press Enter;
Method 2: When the database has been connected, the command prompt is mysql>. Enter source D:/book. SQL or/. D:/book. SQL and press Enter.

 

MySQL Stored Procedure Call Method

The calling method of the MySQL stored procedure is frequently used. The following describes the calling method of the MySQL stored procedure. It is helpful for you to learn about the MySQL stored procedure.

First enter the MySQL command line Environment Create a function and select a database. Delimiter $ is set $ as the command termination symbol, instead of the semicolon, because the semicolon has other functions Now restore the semicolon as the Terminator and call the function. Delete A Function In this way, PHP calls mysql_query ("select quqiufeng ('field name ')"); Stored Procedure Delimiter first $ Call quqiufeng (); // call the MySQL Stored Procedure PHP call Method 3, 1. Create a simple stored procedure in MySQL Create procedure students_copy_proc (in ref_note varchar (255 )) Begin Replace into students_backup Select *, ref_note from students; End | 2. Is the comparison between the root @ % and root @ localhost accounts different? Of course, the usage is also different. When we set the MySQL connection, these two accounts are required (server) (User); the server is the IP address, domain name, or machine name of your MySQL server, and the user is the account connecting to the SQL database, as shown below ----------------------------------------------------------- Account @ server address ----------------------------------------------------------- Root @ % root @ localhost ----------------------------------------------------------- The key lies in the server address. In this address, @ % indicates that any address can be connected. @ localhost indicates that you can only use localhost as the local address for connection, when installing MySQL 4.1.12, there is only one root @ localhost account. It can be said that MySQL 4.1.12 has enhanced security. If my MySQL server is installed on the machine 192.168.0.100, if I only have the account root @ localhost, I can only connect to the MySQL database at 192.168.0.100 with the localhost ID. If I have a root @ % account, I can use 127.0.0.1 \ 192.168.0.100 can connect to the database, so sometimes we need to create an account when we need to connect to other machines! 3. Execute the stored procedure in the PHP File When PHP executes the MySQL stored procedure, the following error occurs: commands out of sync; you can't run this command now solve the problem by calling the MySQL stored procedure in the same thing in PHP and then executing another or multiple commands again (or executing multiple commands in the same thing stored Procedures ), if you use the query method of mysqli to obtain the result, you will get an error: commands out of sync; you can't run this command now SSS first to provide the Code: