A preface
Mysql > store mysql > store mysql > store mysql > store mysql > store mysql > store mysql > store mysql
SQL- Do you Really Know anything about SQL?
“SQL- Xiaobai best start SQL query 1”
“SQL- The best introduction to SQL query 2”
SQL: Insert, Update and Delete
SQL-SQL transaction Manipulation
Sql-mysql data Types
Sql-mysql View: The Past and present
Public account: Knowledge seeker
Inheriting the spirit of open Source, Spreading technology knowledge;
Ii Storage process
2.1 The concept of stored procedures
We often use SQL query statements are single statements, if you want to use multiple statements to achieve a purpose is inadequate, the stored procedure is to use multiple statements to complete business operations, you can understand Linux scripting similar to, Windows batch file; A simple defined stored procedure is a collection of SQL;
Our use of stored procedures can simplify complex single SQL and greatly improve performance compared to single complex SQL; If the table structure changes, you only need to change the table name used by the stored procedure in the SQL statement. If the business logic changes, you only need to jump to the stored procedure, which has strong flexibility. The establishment of a storage process, can be used, do not need to repeatedly establish, to ensure that developers use the same storage process, to ensure data reliability; In a word, using stored procedures, simple, flexible, safe and reliable, good performance;
2.2 Stored Procedure Syntax
- Creating a stored procedure
Create PROCEDUREStored procedure name (parameter list)beginThe process of bodyend;
Copy the code
- The list of parameters
IN denotes input; The example IN var1 Decimal(6,2) OUT represents the output; The example IN var2 Decimal(6,2) INOUT represents input and output; Example IN var3 Decimal(6,2)Copy the code- variable
declareVariable name Variable type [default value]
Copy the code- Executing stored procedures
callStored procedure nameCopy the code- Deleting stored procedures
DROP PROCEDUREStored procedure nameCopy the code- The assignment
useset 和 select intoStatement assigns a value to a variable.set @var: =20
select sum(price) into total from table_name
Copy the code- If statement
F conditional then expressions [elseif conditional then expressions]... [else expression]end if;
Copy the code- A case statement
CASE value WHEN Matching value THEN result [WHEN matching value THEN result]...... [ELSE result] ENDCopy the code- While statement
[Start tag :]while conditiondoLoop body [closing tag]end while ;
Copy the code- Loop statements
[start tag :] loop body [end tag] end loop;Copy the code- Iterate/leave statement
It can be done through tags; Iterate D. leaveCopy the code- Repeat statement
repeat
- the loop bodyUntil loop conditionend repeat;
Copy the codeTip: If you are learning from the command line, use // when writing multiple lines of SQL.
2.3 Examples of stored procedures
We prepare the table as follows, a list of orders;
CREATE TABLE `oder_detail` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key'.`detail_name` varchar(255) DEFAULT NULL COMMENT 'Order Details'.`price` decimal(10.2) DEFAULT NULL COMMENT 'price'.`oid` int(11) DEFAULT NULL COMMENT 'order id',
PRIMARY KEY (`id`))ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='Order List';
Copy the codePrepare the following data:
INSERT INTO `zszxz`.`oder_detail`(`id`.`detail_name`.`price`.`oid`) VALUES (1.'towel'.20.00.1);
INSERT INTO `zszxz`.`oder_detail`(`id`.`detail_name`.`price`.`oid`) VALUES (2.'toothpaste'.15.00.1);
INSERT INTO `zszxz`.`oder_detail`(`id`.`detail_name`.`price`.`oid`) VALUES (3.'cup'.5.00.1);
INSERT INTO `zszxz`.`oder_detail`(`id`.`detail_name`.`price`.`oid`) VALUES (4.'towel'.15.00.2);
INSERT INTO `zszxz`.`oder_detail`(`id`.`detail_name`.`price`.`oid`) VALUES (5.'cup'.15.00.2);
Copy the codeNo parameter storage procedure
View the order details of all the order names, as shown in the following example.
create procedure slelect_detail()
begin
select detail_name from oder_detail;
end;
Copy the codeLet’s call the stored procedure again
call slelect_detail();
Copy the codeThe following will be printed
Towel toothpaste cup Towel cupCopy the codeDeleting stored procedures
drop procedure slelect_detail;
Copy the codeTake an example of a parameter stored procedure
Now we need to query the names of all order details whose OID is dynamic. Considering that the OID is dynamic and needs to be entered by users themselves, we take OID as the input parameter.
create procedure slelect_detail(IN order_id INT)
begin
select detail_name from oder_detail where oid = order_id;
end;
Copy the codeCall the stored procedure to query only the order details name of the user with OID 1
call slelect_detail(1);
Copy the codePrint the content
Towel toothpaste cupCopy the codeDeleting stored procedures
drop procedure slelect_detail;
Copy the codeExamples of stored procedures for input and output parameters
Query all amounts of any user’s order details; Define the input order ID as order_id and output the total amount as total.
create procedure slelect_toatal_money(IN order_id INT.OUT total DECIMAL(8.2))
begin
select sum(price) into total from oder_detail where oid = order_id;
end;
Copy the codeInvoke the stored procedure example
call slelect_toatal_money(1,@total);
Copy the codeQuery the total amount example as order_id is 1
SELECT @total;
Copy the codeThe output is 40;
Deleting stored procedures
drop procedure slelect_toatal_money;
Copy the code2.4 Example if statement
In the previous section, stored procedures were all single SQL. This time, we started to use control flow to implement complex stored procedures.
The knowledge seeker automatically adds 5 to the input order_id, and then determines whether var is less than 7. If yes, query the order detail price, otherwise query the sum of the order detail price.
Create procedure slelect_toatal_money(IN order_id INT) begin -- Define variable declare var INT; Set var= order_id+5; If var<7 then select price from oder_detail where oid = order_id; else select sum(price) from oder_detail where oid = order_id; end if; end;Copy the codecall
call slelect_toatal_money(1);
Copy the codeThe output
price
20
15
5
Copy the codecall
call slelect_toatal_money(2);
Copy the codeThe output
sum(price)
30
Copy the code2.6 Example while statement
To judge the variable var, if var <7, the query price statement will be executed, and var will be self-increased;
Create procedure slelect_toatal_money(IN order_id INT) begin -- Define variable declare var INT; Set var= order_id+5; -- while while var<7 do select price from oder_detail where oid = order_id; set var = var + 1; end while; end;Copy the codeInvoke the sample
call slelect_toatal_money(1);
Copy the codeThe output
price
20
15
5
Copy the code2.7 Case Statement Examples
The following statement implements the same effect as the if statement above;
create procedure slelect_toatal_money(IN order_id INT)
begin
Define variables
declare var int;
- assignment
set var:= order_id;
-- case determines a match
case var
when 1 then
select price from oder_detail where oid = order_id;
when 2 then
select sum(price) from oder_detail where oid = order_id;
end case;
end;
Copy the codeInvoke the sample
call slelect_toatal_money(2);
Copy the codeThe output
sum(price)
30
Copy the codeChange the parameter to 1 to see what happens
2.8 the loop statement
If var is less than 3, calculate price + var;
Create procedure slelect_toatal_money(IN order_id INT) begin -- Define variable declare var INT; Set var:= order_id; -- loop select_loop : loop select price+var from oder_detail where oid = order_id; set var = var +1; If var > 3 then leave select_loop; end if; end loop; end;Copy the codeInvoke the sample
call slelect_toatal_money(1);
Copy the codeThree sets of results are printed
2.7 repeat
Repeat differs from while in that while checks the condition before execution, but actually checks the condition after execution;
Create procedure slelect_toatal_money(IN order_id INT) begin -- Define variable declare var INT; Set var= order_id+5; -- repeat loop repeat select price from oder_detail where oid = order_id; set var = var + 1; until var>7 end repeat; end;Copy the codeInvoke the sample
call slelect_toatal_money(1);
Copy the codeTwo sets of the same results will be output;
price
20
15
5
Copy the codeTip: loop,while, repeat, iterate are all loops, loop,while, repeat function almost the same; Iterate can be called as a tag, the same way as the leave statement;
Focus on knowledge seekers:
