1.1 What is a stored procedure
A SQL statement with logic. Conditional judgment, there are loops, with flow control.
Client: SQLyog, Navicat
1.2 Characteristics of stored procedures
Advantage: Execution is efficient because the stored procedure is performed on the database server.
Cons: MySQL stored procedures cannot be performed in Oracle or SQL Server, and the portability is poor.
1.3 Creating a Database
Create Databasedb20171213;//execute
Usedb20171213;//execute
Create TABLEEMP (depno varchar (12);
****);
1.4 Creating a stored procedure
Specify an end flag in advance when creating a stored procedure
Delimiter? $
Create Proceduretest_pro1 (can take parameters (including input, output parameters))
Begin
Select
from EMP;
End? $
1.5 Calling a stored procedure
Keywords: call
Calltest_pro1 ();
?
1.6 Stored procedures with parameters
In: Indicates the input parameter, can carry the data to the stored procedure to go;
Out: Represents the return parameter, which returns the result from the stored procedure;
InOut: Indicates input and output parameters, both input and output.
1.6.1 with input parameters:
Delimiter? $
CREATE PROCEDURE test_pro_in (in ID int)//in cannot be omitted (keyword)
Begin
SelectFrom emp where empno = ID;
End? $
Call Test_pro_in (10);//must take the reference
? with multiple input parameters
CREATE PROCEDURE test_pro_in (in Id1 int, in ID2 int)
1.6.2 with output parameters
Delimiter? $
CREATE PROCEDURE test_pro_out (out num int)
Begin
/
assigns a value to the output parameter, the keyword set/
Set? num = 10;
End? $
keyword suggested capitalization
?
Call Test_pro_out (@val)//@ define variable val,val the output value of the receive stored procedure
Select @val?? Querying variable values
The output parameter must be set to the initial of the corresponding type, otherwise the result is a null value regardless of how it is computed.
1.6.3 with input and output parameters
Keyword: inout
Delimiter $
CREATE PROCEDURE test_pro_inout (inout n int)
Begin
Set n = n + 100;
End $
Set @nn = 8
Call Test_pro_inout (@nn)
Select @nn
1.7 The variables in MySQL
Global variables, local variables, session variables (sessions variables)
1.7.1 Global Variables
It's not what we can define, it's a MySQL definition, although it can't be defined, but it can change its value.
What global variables are queried
Show variables;//shows all the global variables
Show variables like ' char% ';? Fuzzy matching Query
Query a single global variable cannot be used with show, should use Select
SELECT @ @character_set_client;
Assign a value to a global variable:
SET @ @character_set_client = GBK;?? Valid only in the current link (that is, the current client)
1.7.2 Session variable br/> session variable starts with @
Set @vall = 10;
Must you specify a value when defining a session variable?
Scope of existence:
Session (a client link). Valid only on the current client
CMD login: Mysql-uroot-proot
? Use db20171213?? Switch to a specific database
Inquire:
Select @vall;
1.7.2 Local Variables
Location of the local variable: inside the stored procedure.
Keyword: declare
Delimiter? $
CREATE PROCEDURE Test_pro_loc
Begin
declare i int default 1;
SELECT * from EMP;
End? $
Delete stored procedure: Drop proceduretest_pro_inout;
Above is a discussion on MySQL database stored procedure and trigger principle more technical articles and experience Exchange we can add group 788692365, Ali P8 in this group, together learn the experience of communication architecture. Https://www.gupaoedu.com/video.html here also has the relevant Java architecture free Learning video to watch.
This Tanabata bubble College send you 5,207 XI Confession Gold speed to collect it!
Easy access to MySQL database stored procedures and trigger principles
