Prying into Mysql stored procedure details _mysql

stored Procedures , you can think of the SQL statement that we need special processing into a function, when needed we just call this function can achieve the operation we want, this process we can call the stored procedure. Of course, the definition of a real stored procedure is not like this. But we can simply understand the stored procedure.

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,
     type varchar)
;

Then we need to insert 100,000 data into this table, and this time 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 Memory blog ', ' ONMPW ');
     -->set n = n+1;
     -->end while;
     -->end
     -->//
mysql> delimiter;
Mysql> call AddData ();

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

With these small examples, let's talk about how to create a stored procedure.

Creating a Stored Procedure

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

CREATE PROCEDURE procedure_name (in/out/inout parameter TYPE)
BEGIN
     procedure_body
End

This process is relatively simple.

In the small example above we saw that delimiter//; was used before the creation of the stored procedure, and the command delimiter was used again after the creation was completed.

Delimiter is the defining character, and we know that in the MySQL command-line client, a semicolon (;) is used to define whether a command is complete. We use the semicolon more than once in the stored procedure, but that doesn't mean the end of the command, so we need to use the delimiter command to change the qualifier.

Mysql> delimiter//;  Change the qualifier to//
mysql> delimiter; re-change the definition to a semicolon

So if we use the MySQL command line to create the stored procedure, we must use the above command to change the definition before we create the stored procedure.

Next we see the in/out/inout in Procedure_name (), what does that mean?

An in-type parameter passes a value to the stored process, which is the parameter of our custom function in the programming language. If the parameter is not previously specified as In/out/inout, then the default will be in, looking at the following example:

Mysql>delimiter//
mysql> CREATE PROCEDURE in_proced (in Param VARCHAR (MB))
      -->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 argument.

Here we look out, and the parameter specified as out passes a value to the caller from the stored procedure, that is, out can assume that this parameter is the return value in our custom function.

Mysql> delimiter//
mysql> CREATE PROCEDURE out_proced (out param INT)
     -->begin
     COUNT (*) into the param from proced;
     -->end
     -->//
mysql>delimiter;
Mysql> call out_proced (@a);
Mysql>select @a;
+------+
| @a |
+------+
| 3   |
+------+

The last is inout, it is obvious that the specified parameter is initialized by the caller, whose value can be modified in 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 the param from proced where id>param;
     --> End
     -->//
mysql>delimiter;
Mysql>set @a = 3;
Mysql>call inout_proced (@a);
Mysql>select @a; To see if the value of a variable changes

The above is the way to create a simple stored procedure.

deleting stored procedures

To delete the syntax for a stored procedure:

DROP PROCEDURE IF EXISTS procedure_name

The following are examples of usages:

Mysql>drop procedure if exists proced;

modifying stored procedures

Changes to a stored procedure cannot alter the SQL statements within the stored procedure, only their properties can be changed, and 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 stored procedures or modify stored procedures, you must make sure that you modify or delete stored procedures that are not used by other stored procedures, such as you have stored procedure A, and stored procedure B. A is used in B, if we want to modify a or delete a, we have to make sure that a is no longer used in B, otherwise if we delete a, we will make an error when we call B.

As 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

The above is a simple introduction to stored procedures, I hope to learn from the MySQL stored procedures help.