MySQL variable declaration and stored procedure analysis

Declaring variables

Setting Global Variables

set @a='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 within the begin..end section.

select .. into.. directly assigns the contents of the table to the specified variable

select name,bid into @a,@b from bank limit 1;

One thing to note is that the variable name cannot be the same as the field name

Stored Procedures

The stored procedure encapsulates a common operation so that it can be used on different platforms.

The stored procedure has no return value and cannot be called by SQL statements. It can only be called by call and does not return a result set. It is executed when executed.

It should be noted that the system default terminator must be reset to something else when executing SQL statements in a stored procedure. Otherwise, the system will mistakenly recognize the program as terminated and report an error halfway through the writing process.

Change the end command character to $

delimiter$+Enter or abbreviated as \d $+Enter

Show all stored procedures

show procedure status;

Delete the specified stored procedure

drop procedure procedure name;

Stored procedure demo'

\d $ 1 create procedure yanshi(in arg tinyint)
begin
declare age tinyint default 0;
set age=arg;
if age<20 then
select 'number less than 20';
elseif age>20 then
select 'number greater than 20';
end if;
end
$
//Calling procedure set @num=12$
call yanshi(@num)$
call yanshi(21)$

Determine which stage the number entered into the stored procedure belongs to

There are three types of parameter passing in stored procedures : in, out, and inout

in can output variables passed in from the outside without changing the original value of the variable passed in

create procedure a(in id int)
begin
  select id;
  set id = 100;
end
$
set @id=1$
call a(@id)$ //output 1, which is the value of @id passed in from outside select $id$ //output 1, indicating that the value passed in has not been changed during the storage process

out cannot output the value passed in from the outside and will change the original value of the variable passed in

create procedure b(out id int)
begin
  select id;
  set id = 100;
end
$
set @id=1$
call b(@id)$ // input null
select @id$ //output 100

inout can both output the passed variable and change the passed variable

Now it's time to check your computer's hardware performance

Remember the bank table from that year? He saved it and executed 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

Summarize

The above is all the content of this article about MySQL variable declaration and stored procedure analysis. I hope it will be helpful to everyone. Interested friends can refer to: Several important MySQL variables, detailed explanation of MySQL prepare principles, analysis of key points of ORACLE SQL statement optimization techniques, etc. If you have any questions, you can leave a message at any time and the editor will reply to you in time. Thank you friends for your support of 123WORDPRESS.COM!