View mysql stored procedure details and view mysql Stored Procedure

View mysql stored procedure details and view mysql Stored Procedure

Stored ProcedureIn this way, we can encapsulate SQL statements that require special processing into functions. When necessary, we only need to call this function to implement the operations we want, this process is called a stored procedure. Of course, this is not the definition of a stored procedure. However, we can simply understand the stored procedure.

The following is a simple example of using a stored procedure.

First, create a new table proced:

create table proced(     id int(5) primary key auto_increment,     name varchar(50),     type varchar(50));

Then we need to insert 0.1 million pieces of data into this table. In this case, we need to use the stored procedure to implement this function.

Mysql> delimiter // mysql> create procedure adddata () --> begin --> declare n int default 0; --> while n <100000 --> do --> insert into proced (name, type) values ('trace blog ', 'onmpw'); --> set n = n + 1; --> end while; --> end --> mysql> delimiter; mysql> call adddata ();

With the above stored procedure, We can insert 0.1 million pieces of data into the proced table.

With the help of the above example, we will explain how to create a stored procedure.

Create a stored procedure

First, let's look at the syntax for creating a stored procedure:

CREATE PROCEDURE procedure_name(IN/OUT/INOUT parameter TYPE)BEGIN     procedure_bodyEND

This process is relatively simple.

In the preceding example, we can see that delimiter //; is used before the storage process is created, and the command delimiter; is used again after the storage process is created ;.

Delimiter is the delimiter. We know that on the mysql command line client, a semicolon (;) is used to determine whether a command is completed. In the stored procedure, we use semicolons multiple times, but this does not mean the end of the command. Therefore, we need to use the delimiter command to change the delimiter.

Mysql> delimiter //; change the delimiter to // mysql> delimiter; change the delimiter to a semicolon.

Therefore, if we use the mysql command line to create a stored procedure, we must use the above command to change the delimiter before creating the stored procedure.

Next we can see IN/OUT/inout in procedure_name (). What does this mean?

A parameter of the IN type will pass a value to the stored procedure, that is, the parameter of the function customized IN programming language. If IN/OUT/INOUT is not specified before the parameter, the default value is IN. See the following example:

mysql>delimiter //mysql> create procedure in_proced(IN param VARCHAR(100))      -->begin      -->insert into proced(name,type) values(param,'onmpw');      -->end      -->//mysql>delimiter ;mysql> call in_proced(‘onmpw.com');

This is the meaning of specifying IN before the parameter.

Next we can see that the OUT parameter will pass a value from the stored procedure to the caller, that is, the OUT parameter can be considered as the return value in our custom function.

mysql> delimiter //mysql> create procedure out_proced(OUT param INT)     -->begin     -->select count(*) into param from proced;     -->end     -->//mysql>delimiter ;mysql> call out_proced(@a);mysql>select @a;+------+| @a |+------+| 3   |+------+

The last step is INOUT. Obviously, the INOUT parameter is initialized by the caller, and its value can be modified during the stored procedure. Any changes are visible to the caller.

See the following example:

Mysql> delimiter // mysql> create procedure inout_proced (INOUT param INT) --> begin --> select count (*) into param from proced where id> param; --> end --> // mysql> delimiter; mysql> set @ a = 3; mysql> call inout_proced (@ a); mysql> select @ a; check whether the variable value has changed

The above describes how to create a simple stored procedure.

Delete stored procedure

Syntax for deleting a stored procedure:

Drop procedure if exists procedure_name

The following is an example:

Mysql> drop procedure if exists proced;

Modify Stored Procedure

When a stored procedure is modified, the SQL statement in the stored procedure cannot be changed, but its attributes can only be changed. The syntax is as follows:

ALTER PROCEDURE proc_name [characteristic ...]characteristic:  COMMENT 'string'  | LANGUAGE SQL  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }  | SQL SECURITY { DEFINER | INVOKER }

Summary:Whether you delete A stored procedure or modify A stored procedure, you must ensure that the stored procedure you want to modify or delete is not used by other stored procedures. For example, you have stored procedure A and stored procedure B. A is used in B. If we want to modify A or delete A, we must ensure that A is no longer used in B. Otherwise, an error will be reported when we call B after deleting.

For example:

mysql>delimiter //mysql>create procedure A(IN pa1 INT,OUT pa2 INT)     -->begin     -->select count(*) into pa2 from proced where id>pa1;     -->end     -->//mysql>create procedure B(INOUT pa INT)     -->begin     -->declare v int;     -->call A(pa,v);     -->set pa = v;     -->end     -->//mysql>delimiter ;mysql>drop procedure A;mysql>set @a=5;mysql>call B(@a);ERROR 1305 (42000): PROCEDURE test.A does not exists

The above is a brief introduction to the stored procedure, hoping to help you learn about the mysql stored procedure.

Articles you may be interested in:

• Definition and value assignment of variables in mysql stored procedures
• Example of a MySQL stored procedure (including transactions, output parameters, and nested calls)
• Mysql stored procedure details
• Usage of mysql Stored Procedure cursor Loop
• Example of where id in (, 3 ,...)
• MySQL Stored Procedure usage example
• Instances that use cursors in mysql stored procedures
• How to store MySql
• Example of jumping out and continuing operations of the cursor loop in the MySQL Stored Procedure
• Mysql Stored Procedure nested Using cursor sample code