MySQL declaration variables and stored procedure analysis, mysql Stored Procedure

MySQL declaration variables and stored procedure analysis, mysql Stored Procedure

Declare Variables

Set global variables

Set @ a = 'new variable ';

Declear variables used in functions and stored procedures

Declear a int unsigned default 1;

This type of variable needs to be set and only exists in the begin. end section.

Select... into... directly assign values to the specified variable.

Select name, bid into @ a, @ B from bank limit 1;

Note that the variable name cannot be the same as the field name.

Stored Procedure

Stored Procedures encapsulate a common operation so that different platforms can share the same information.

The stored procedure has no return value, and cannot be called using SQL statements. It can only be called without returning a result set. The execution is complete.

It should be noted that the SQL statement needs to be used during the storage process. The default terminator of this system must be set to another one. Otherwise, the system will mistakenly identify the program to terminate and report an error in half of the write process.

Change the end command to $

Delimiter $ + press ENTER or abbreviated as \ d $ + press ENTER

Show all stored procedures

Show procedure status;

Deletes a specified stored procedure.

Drop procedure process name;

Stored Procedure demo'

\ D $1 create procedure yanshi (in arg tinyint) begindeclare age tinyint default 0; set age = arg; if age <20 thenselect 'Number less than 20 '; elseif age> 20 thenselect 'Number greater than 20'; end if; end $ // call process set @ num = 12 $ call yanshi (@ num) $ call yanshi (21) $

Determine the stage of the number entered into the Stored Procedure

Passing parameters in the Stored ProcedureIn, out, inoutThree Types

In can output variables passed in from the outside without changing the original values of the passed variables.

Create procedure a (in id int) begin select id; set id = 100; end $ set @ id = 1 $ call a (@ id) $ // output 1 refers to the value of @ id passed in from the external. select $ id $ // output 1 indicates that the passed value is not changed in the stored procedure.

Out cannot output a value that is passed in from the outside. It will change the original value of the input variable.

Create procedure B (out id int) begin select id; set id = 100; end $ set @ id = 1 $ call B (@ id) $ // enter nullselect @ id $ // output 100

Inout means that the input variable can be output and the input variable can be changed.

It's time to test the performance of your computer's hardware.

Do you still remember the bank table of that year? That is, keep it and execute the following command:

create procedure addbank()begin  declare i int default 0;  set i = 5000000;  while i > 0 do  insert into bank (name) values (i);  set i = i - 1;  end while;end$call addbank()$

Good luck

Summary

The above is all about MySQL declaration variables and stored procedure analysis. I hope it will be helpful to you. If you are interested, refer to: several important MySQL variable MySQL prepare principle detailed explanation of oracle SQL statement optimization Technical Points Analysis, etc. If you have any questions, please feel free to leave a message. The editor will reply to you in time. Thank you for your support for the website!