Snoop on mysql stored procedure details

A stored procedure, in other words, encapsulates the sql statement that we need to process in a special way into a function that we can call when we need it to do what we want, a procedure we can call a stored procedure. Of course, this is not the definition of a real stored procedure. But we can understand stored procedures as simply as this.

Let's look at a simple example of using a stored procedure.

First, let's 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 100,000 pieces of data into the table, and we need to implement this function with stored procedures.

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

Using the above stored procedure, we can insert 100,000 pieces of data into the proced table.

Using the above small example, let's talk about how to create a stored procedure.

Create stored procedures

First let's look at the syntax for creating stored procedures in 1:

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

The process is relatively simple.

In the small example above we saw that delimiter // was used before creating the stored procedure; After the creation, the command delimiter is used again. .

delimiter is the delimiter, as we know, on the mysql command line client, by the semicolon (;) To determine whether a command is complete. Semicolons are used multiple times during stored procedures, but this does not represent the end of the command, so we need to change this delimiter using the delimiter command.

mysql> delimiter //;   Change the delimiter to  //
mysql> delimiter ;  Rechange the delimiter to semicolon 

So if we were to create a stored procedure using the mysql command line, we would have to change the delimiter using the above command before creating the stored procedure.

Next we see IN/OUT/INOUT in procedure_name(). What does this stand for?

An argument of type IN will pass a value to the stored procedure, which is the argument to the custom function in the programming language. If the parameter is not specified as IN/OUT/INOUT, the default 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 what it means to specify IN before the parameter.

Now let's look at OUT. The parameter specified as OUT will pass a value from the stored procedure to the caller, that is, OUT can think of this parameter 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   |
+------+

Finally, INOUT, it is clear that the parameters specified by INOUT are initialized by the caller, their values can be modified during the stored procedure, and any changes are visible to the caller.

Look at 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 to see if the value of the variable has changed 

That's how you create a simple stored procedure.

Delete stored procedure

Delete the stored procedure syntax:

DROP PROCEDURE IF EXISTS procedure_name

Here are some examples of usage:

mysql > drop procedure if exists proced;

Modify stored procedure

When the stored procedure is modified, the sql statement in the stored procedure cannot be changed, only its properties can be changed, and its 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 are deleting a stored procedure or modifying a stored procedure, make sure that the stored procedure you are modifying or deleting is not used by other stored procedures, such as if you have stored procedures A and B. A is used in B. If we want to modify A or delete A, we must ensure that A is not used in B. Otherwise, if we delete A, we will get an error when we call B again.

Here's an 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

This is a brief introduction to stored procedures and I hope it will be helpful for you to learn mysql stored procedures.