Click on “SQL database development”, \
Set it as “top or star mark” to deliver dry goods in the first time
People often ask me how to write this stored procedure. As someone who has been there before, I also had this problem at the beginning. Today I will tell you how to create and use this stored procedure. \
What is a stored procedure
A stored procedure is a set of programmable functions. It is a set of SQL statements that are created by compilation and stored in a database. Users can call and execute the stored procedure by specifying its name and parameters (if necessary).
Key words: programmable, specific function, call
Creating a stored procedure
Taking the customers table as an example, we can query the specific information of customers by passing the value of their ID:
Customers table
Example:
CREATE PROCEDURE sp_customers(IN cusid INT)
BEGIN
SELECT * FROM customers WHERE ` customer ID `=cusid;
END;
Copy the codeThis is a relatively simple stored procedure, the main function is to query customer information. Here’s a brief explanation:
CREATE PROCEDURE: **** This is a fixed syntax for creating stored procedures.
Sp_customers: This is the name of the stored procedure. After we execute the stored procedure, the system will present a stored procedure with this name that can be customized.
IN: **** this is the meaning of the input parameter, of course, there is also the output parameter keyword OUT, also can not define the parameter, directly leave the parameter blank.
Cusid INT: **** This is the definition of the parameter name and type. Here we define a parameter name named cusid of type INT.
BEGIN … END: **** This is the fixed syntax of the stored procedure body, and the SQL function you need to execute is written in the middle of it.
Calling a stored procedure
Once we have created the stored procedure above, we can call it. The syntax for calling a stored procedure is simple:
CALL sp_name([parameter])
Let’s call the above stored procedure sp_customers\
CALL sp_customers(1);
Copy the codeExplanation:
The code above means passing the data with the customer ID of 1 to the sp_customers stored procedure, which is called to execute it via CALL.
The result is:
As you may have noticed, this is a simple WHERE query statement. Yes, stored procedures don’t have to be mysterious when you first start using them. The more mysterious they are, the more difficult they become. Complex things are simplified before they can be further mastered.
The process of body
- The body of the procedure is the SQL statement that we must execute when calling, and the SELECT query above is a simple body of the procedure.
- The procedure body contains DML and DDL statements, if-then-else and while-do statements, declare statements that declare variables, and so on
- The format of the procedure body is also demonstrated above, starting with BEGIN and ending with END (which can be nested).
Such as:
BEGIN BEGIN BEGIN -- SQL code; END END ENDCopy the codeNote: each nested block and each SQL statement within it must be preceded by a semicolon (;). The end. A begin-end block (also known as compound statement) that represents the END of a procedure body, that is, the END, does not require a semicolon.
The label
Tags are usually used with begin-end to make code more readable. Grammar:
[label_name:] BEGIN
[statement_list]
END [label_name]
For example: \
Label1: BEGIN Label2: BEGIN label3: BEGIN --SQL code; END label3 ; END label2; END label1Copy the codeThis function is not commonly used, you can understand. \
Parameters of the stored procedure
Above we have looked briefly at the stored procedure parameter definitions. Now we will go into more detail about how parameters can be used.
The parameter types
-
IN input parameter: Indicates that the caller passes a value to the procedure (the value can be a literal or a variable)
-
OUT output parameter: indicates that the procedure sends a value to the caller (multiple values can be returned) (the outgoing value can only be a variable) \
-
INOUT Input/output parameters: indicates that both the caller passes a value to the procedure and the procedure passes a value to the caller (the value can only be a variable)
IN input parameter
The above example is an example of an input parameter and will not be described here.
OUT The output parameter \
CREATE PROCEDURE sp_customers_out(OUT cusname VARCHAR(20))
BEGIN
SELECT cusname;
SELECT ` name ` INTO cusname FROM customers WHERE ` customer ID `=1;
SELECT cusname;
END
Copy the codeCall the above stored procedure:
CALL sp_customers_out(@cusname);
Copy the codeThe result is:
Results 1
The results of 2
Above we define a parameter whose output parameter is cusName (where the parameter type must have a length, if any).
In the body of the procedure, we output the result of two parameters. The result 1 is NULL, because our output parameter Cusname has not received any value, so it is NULL.
Result 2 contains the customer name because we passed the customer name with customer ID 1 to the output parameter CusName.
INOUT Input and output parameters
This is not common, but it can be used where the same parameter is both an input parameter and an output parameter. Let’s modify the stored procedure above slightly to see the difference.
CREATE PROCEDURE sp_customers_inout(INOUT cusname VARCHAR(20))
BEGIN
SELECT cusname;
SELECT ` name ` INTO cusname FROM customers WHERE ` customer ID `=2;
SELECT cusname;
END
Copy the codeBefore calling the stored procedure, we give an input parameter: triple \
SET @cusname='Joe';
CALL sp_customers_inout(@cusname);
Copy the codeThe result is: \
Results 1
The results of 2
Above we define an input/output parameter cusName. Then, in the procedure body, we print the result of two parameters:
The first time we pass the “SET @cusName” (SET @cusName = “cusname”) as the input parameter to cusname. After entering the process body, the output result 1 is “Zhang SAN”, and the parameter Cusname is the output parameter.
Then pass the customer name with customer ID 2 to CusName again through the query to change its value. At this time, it is also the output parameter, but the output result is changed.
The above is the usage of the three parameters. Suggestions:
- The IN parameter is used when a value is required;
- The OUT argument is used when a return value is required;
- Use the INOUT parameter sparingly.
-- End -- background reply keyword:1024, to obtain a carefully organized technical dry goods background reply keywords: into the group, take you into the master like clouds of communication group. Recommended reading SQL syntax crash manual carefully organized a set of SQL advanced functions, it is recommended to collect a SQL automatic check artifact, no longer need to worry about SQL errors! SQL statement after the where condition1=1Domestic database modeling tool, see the interface at first glance, conscience! This is a technology can learn the public number, welcome to pay attention toCopy the codeClick "Read original" to learn about SQL BootcampCopy the code