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 code

Tip: 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 code

Prepare 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 code

No 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 code

Let’s call the stored procedure again

call slelect_detail();
Copy the code

The following will be printed

Towel toothpaste cup Towel cupCopy the code

Deleting stored procedures

drop procedure slelect_detail;
Copy the code

Take 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 code

Call the stored procedure to query only the order details name of the user with OID 1

call slelect_detail(1);
Copy the code

Print the content

Towel toothpaste cupCopy the code

Deleting stored procedures

drop procedure slelect_detail;
Copy the code

Examples 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 code

Invoke the stored procedure example

call slelect_toatal_money(1,@total);
Copy the code

Query the total amount example as order_id is 1

SELECT @total;
Copy the code

The output is 40;

Deleting stored procedures

drop procedure slelect_toatal_money;
Copy the code

2.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 code

call

call slelect_toatal_money(1);
Copy the code

The output

price
20
15
5
Copy the code

call

call slelect_toatal_money(2);
Copy the code

The output

sum(price)
30
Copy the code

2.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 code

Invoke the sample

call slelect_toatal_money(1);
Copy the code

The output

price 
20
15
5
Copy the code

2.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 code

Invoke the sample

call slelect_toatal_money(2);
Copy the code

The output

sum(price)
30
Copy the code

Change 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 code

Invoke the sample

call slelect_toatal_money(1);
Copy the code

Three 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 code

Invoke the sample

call slelect_toatal_money(1);
Copy the code

Two sets of the same results will be output;

price
20
15
5
Copy the code

Tip: 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:

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

SQL-mysql stored procedures

A preface

Ii Storage process

2.1 The concept of stored procedures

2.2 Stored Procedure Syntax

2.3 Examples of stored procedures

2.4 Example if statement

2.6 Example while statement

2.7 Case Statement Examples

2.8 the loop statement

2.7 repeat

SQL-mysql stored procedures

A preface

Ii Storage process

2.1 The concept of stored procedures

2.2 Stored Procedure Syntax

2.3 Examples of stored procedures

2.4 Example if statement

2.6 Example while statement

2.7 Case Statement Examples

2.8 the loop statement

2.7 repeat

Related Posts

1ms delay, 10Gbps rate… Interpretation of 5G communication technology

Multi-platform container image building is just a look at this article

Xxl-job source code parsing