JIYIK CN >

A stored procedure can be thought of as encapsulating a SQL statement that we need to process specially into a function. When needed, we only need to call this function to achieve the desired operation. This process can be called a stored procedure. Of course, the definition of a real stored procedure is not like this. But we can understand the stored procedure in this simple way.

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

First we 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 records into this table. At this time, we need to use stored procedures 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 Memory Blog','onmpw');
         -->set n = n+1;
         -->end while;
         -->end
         -->//
mysql> delimiter ;
mysql> call adddata();

Using the above stored procedure, we can insert 100,000 records into the procedure table.

With the help of the above example, let's talk about how to create a stored procedure.

Creating 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_body
END

The process is relatively simple.

In the example above, we see that delimiter //; is used before creating the stored procedure, and the command delimiter ; is used again after the creation is completed.

Delimiter is a delimiter. As we know, in the MySQL command line client, a semicolon (;) is used to delimit whether a command is completed. In stored procedures, we will use semicolons many times, but this does not mean the end of the command, so we need to use the delimiter command to change this delimiter.

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

So 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 see IN/OUT/INOUT in procedure_name(). What does this mean?

An IN type parameter passes a value to a stored procedure, which is the parameter of a custom function in a programming language. If the parameter is not specified as IN/OUT/INOUT, it will be IN by default, as shown in 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 a parameter.

Next, let's look at OUT. The parameter specified as OUT will pass a value from the stored procedure to the caller. In other words, OUT 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     |
+------+

最后就是INOUT，很明显INOUT指定的参数被调用者初始化，其值在存储过程中可以被修改，并且任何改变对于调用者来说都是可见的。

看下面的例子：

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;  查看变量的值是否改变

以上就是创建一个简单的存储过程的方式。

删除存储过程

删除存储过程的语法：

DROP PROCEDURE IF EXISTS procedure_name

下面是使用实例：

mysql>drop procedure if exists proced;

修改存储过程

存储过程的修改时不能改变存储过程内的sql语句的，只能改变其属性，其语法如下：

ALTER PROCEDURE proc_name [characteristic ...]

characteristic:
    COMMENT 'string'
    | LANGUAGE SQL
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }

总结：无论是删除存储过程还是修改存储过程，必须保证你要修改或者删除存储过程没有被其他存储过程使用，例如你有存储过程A，和存储过程B。A在B中被使用，如果我们想修改A或者删除A，必须确保B中不再使用A，否则如果我们删除A以后，再调用B的时候就会报错。

举个例子：

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

以上就是对存储过程简单的介绍。