Mysql and mysql download

Mysql and mysql download

Cursor is not used much in my current project, but its function is very powerful.

Dynamic SQL has never been used before. It was learned by colleagues from Foxconn (not Zhang quanegao's rich earth Kang. it is quite useful. I learned a lot from him about my database. thank you for choosing Jianhua.

Transactions have actually appeared in the previous chapter. This seems to be a little more useful in the program.

Since I have encountered a scenario in my previous work and used the cursor, dynamic SQL, and transaction, I also made an example and thought about another scenario, it is irrelevant to the scenario at work and does not disclose company business secrets.

Let's take a look at the example first. Then, I will add the syntax later.

I. Example

1. Create a table

For example, you can't forget to create a table. It starts from 0.

Create table Goods (Id int not null PRIMARY key auto_increment, Code varchar (50) comment 'code', Name varchar (20) comment 'name', Count int comment' qty ', brand varchar (20) comment 'brand') default charset = utf8 comment 'commodity table'; create table GoodDetails (Id int not null PRIMARY key auto_increment, GId int not null comment 'goods table id ',
Name varchar (20) comment 'name', Code varchar (50) comment 'encoding details', Remark varchar (100) comment 'note ') default charset = utf8 comment 'item details ';

 

2. Add basic data

 

3. Introduction to virtual scenarios

The company recently entered a batch of items, that is, the Goods table above, and prepared to encode each item (the encoding rule is to add a serial number to the Code of the Goods table to generate the Code ), and add the barcode.

The function here is to generate product details and serial numbers. one-click generation is usually implemented in two ways:

Method 1: Program Generation

In the program, read the data to be generated, for example, the above four, and then cycle each one, and encode the data to generate 12 data records in total, stored in the detail table. it's okay when the amount of data is small, and it's acceptable. But if the amount of data is large, the speed and slowness will lead to the impulse to hit the computer. several pieces of data in the GoodDetails table can be hundreds or even thousands or tens of thousands.

Method 2: Database generation

If you do not want to read the data and insert it again, and the logic processing is not much, you can use the database to generate it if it is not complex. It is very convenient and the speed is greatly improved.

Here, I will only introduce method 2, method 1, but it is a little more troublesome.

 

4. Script:

Delimiter $ drop PROCEDURE if EXISTS p_autocreate; create procedure 'P _ autocreate '(IN g_ids VARCHAR (1000), IN nolength INT) begin declare res_code INT; DECLARE res_msg VARCHAR (50 ); /* Number of temporary tables */DECLARE t_count INT;/** use the variable ** begin **/DECLARE g_id INT In the cursor; DECLARE g_code VARCHAR (50 ); DECLARE d_code VARCHAR (50); DECLARE g_count INT (11); DECLARE g_name VARCHAR (20 ); /** use the variable ** end ** In the cursor // ** cursor position **/DECLARE v_index int default 1; DECLARE done bit default 0; /* DECLARE the CURSOR */DECLARE g_cursor cursor for select id, CODE, NAME, count from temp_goods;/* If the next CURSOR cannot be found, done is SET to 1 */declare continue handler for not found set done = 1;/** create temporary TABLE ** begin **/drop table if exists temp_goods; create temporary table temp_goods (Id int not null, -- PRIMARY key auto_increment, code varchar (50), name varchar (20), count int) default charset = utf8; /** create temporary table ** end ** // ** initialization return value ** begin **/SET res_code: = "-99"; SET res_msg: = "OK "; /** initialization return value ** end **/IF (g_ids is not null or length (g_ids)> 0) THEN
SET @ v_ SQL: = CONCAT ("INSERT INTO temp_goods (Id, Code, Name, Count)", "select Id, Code, Name, Count from Goods", "where ", "find_in_set (id,", CHAR (34), g_ids, CHAR (34), ")> 0;");/* pre-compile this dynamic SQL statement, and save it to stmt */PREPARE stmt FROM @ v_ SQL;/* EXECUTE this dynamic SQL, which extracts valid data FROM Goods */EXECUTE stmt; /* release this resource */deallocate prepare stmt; select count (1) INTO t_count FROM temp_goods; start transaction; -- start transaction if (t_count> 0) THEN/* OPEN cursor */OPEN g_cursor; REPEAT/* The order here must be consistent with the previous order */FETCH g_cursor INTO g_id, g_code, g_name, g_count; if not done then set v_index: = 1; IF (IFNULL (g_count, 0)> 0) then while (v_index <= g_count) do set d_code: = CONCAT (g_code, LPAD (v_index, nolength, "0"); insert into GoodDetails (GId, NAME, CODE) VALUES (g_id, g_name, d_code); SET v_index: = v_index + 1; end while; end if; UNTIL done end repeat; -- END repeat loop CLOSE g_cursor;/* CLOSE cursor */COMMIT; -- submit transaction else rollback; -- roll back the transaction SET res_code: = "10"; SET res_msg: = "no related records exist in the system. "; end if; else set res_code: =" 5 "; SET res_msg: =" SELECT the record to be generated "; end if; drop table if exists temp_goods; SELECT res_msg; END $ delimiter;

 

5. Results:

Execute this stored procedure

call p_autocreate('1,2,3,4', 3);

OK. The execution is successful. Next, let's take a look at the data in the GoodDetails table:

My example here is already the simplest example. In actual use, it may be more complicated and more data.

But when it comes to the data volume, I don't mind doing another experiment.

 

6. Experiment

I changed the data volume to 10000. For example, 40000 data records are generated and inserted into the table. if you use a program to insert data into the database, it will be slower.

　　

The database method is indeed much faster. For example, it takes less than 4 seconds to generate 4 million pieces of data and insert it into the GoodDetails table.

　　

OK. Next, let's introduce their syntax.

 

Ii. cursor

1. Syntax

1.1 declare a cursor

Declare cursor name cursor for select column name from table

1.2 open cursor

Open cursor name

1.3 forward cursor

Fetch cursor name into variable a, variable B...

1.4 close the cursor

Close cursor name

2. Note

Since the cursor execution method is like a loop, when will this loop end.

In this example, declare continue handler for not found set done = 1; which means that when the cursor cannot find the next data, change the variable done to 1.

Iii. dynamic SQL

1. Syntax

1.1 prepare SQL Variables

This SQL variable must be in the string format. In this way, the SQL statement to be executed can be dynamically generated.

1.2 Pre-Compilation

PREPARE stmt FROM @ v_ SQL;

Here, stmt is a variable and its name is used by itself.

1.3 execution

EXECUTE stmt;

1.4 release resources

Deallocate prepare stmt;

 

Iv. Transactions

1. Syntax

Now, I suddenly don't know what to say. Let's give a brief introduction.

1.1 start transaction

Start transation;

1.2 submit a transaction

Commit;

1.3 roll back transactions

Rollback;