mysql-Advanced declaration variables/stored Procedures

declaring variables

Setting Global variables

Set @a= ' a new variable ';

Variables used in functions and stored procedures Declear

Declear a int unsigned default 1;

This variable needs to be set to the type of the variable and only exists at begin. End of the paragraph

Select:  Into.. Assign the contents of a table directly to a specified variable

Select Name,bid into @a,@b from bank limit 1;

One thing to note is that the variable name cannot match the field name

Stored Procedures

stored procedures encapsulate a common set of operations so that different platforms can be shared

The stored procedure does not return a value, and cannot be called by the SQL statement, only call calls, and no result set is returned, execution executes

It is important to note that SQL statements are used in the storage process; This system default terminator to reset to another, or half the system in the process of the error recognition program to terminate and then error

Change the end of the command character to $

delimiter$+ Enter or shorthand \d $+ enter

Show all stored Procedures

Show procedure status;

To delete a specified stored procedure

drop procedure process name;

Stored Procedure Demo

0 \d $
1 CREATE PROCEDURE Yanshi (in arg tinyint)2 begin3 DeclareAge tinyintdefault0;4Set age=Arg;5 ifAge<20 Then6Select ' Number less than 20 ';7 ElseIfAge>20 Then8Select ' Number greater than 20 ';9 End if;Ten End One $ ACall procedure -Set @num =12$ -Call Yanshi (@num)$ theCall Yanshi (21) $

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

In-out inout three of parameters in the stored procedure

In can output a variable passed in from outside without changing the value of the passed-in variable

CREATE PROCEDURE a (in ID int)
Begin
Select ID;
Set id = 100;
End
$
Set @id =1$
Call A (@id) $//Output 1 is the value of the @id that comes in from the outside

Select $id $//Output 1 indicates that the passed-in value was not changed in the stored procedure

Out cannot output the value passed in from the outside will change the value passed into the variable

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 is the ability to output incoming variables and change incoming variables.

Here's the time to test your computer's hardware performance.

Do you remember the Bank table of the year? He's the one who kept it. Then 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

mysql-Advanced declaration variables/stored Procedures