Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about how to store MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

I. stored procedure

A stored procedure is a set of SQL statements that are stored in a database in order to perform specific functions in a large database system. After the stored procedure is compiled for the first time, it is called again without compilation, and the user can use it by specifying the stored procedure name and giving some parameters defined by the stored procedure. A stored procedure is one or more SQL statements saved for later use, which is similar to a function and is generally used less.

Benefits:

Complex operations can be simplified by encapsulating the processing in an easy-to-use unit.

two。 There is no need to establish a series of processing steps repeatedly, thus ensuring the consistency of the data.

3. Simplifies the management of changes, an extension of which is security.

4. Stored procedures are usually stored in compiled form, so DBMS requires less work to process commands and improves performance.

Disadvantages:

1. Poor portability

two。 For simple SQL statements, stored procedures have no advantage

4. If only one user uses the database, stored procedures have no impact on security.

5. Team development needs to unify the standard first, otherwise the maintenance cost is high in the later stage.

6. In the case of large concurrency access, it is not appropriate to write too many stored procedures involving operations.

7. When the business logic is complex, especially when it comes to operating on large tables, it is better to simplify the business logic at the front end.

Second, stored procedure syntax format

General form

Create procedure [procedure name] ([in | out | inout] [parameter name] [parameter type],...) begin [SQL statement set;] end

When defining parameters, in represents incoming parameters and reads external variable values assigned to internal parameters, but the scope of internal parameters is limited to stored procedures; out represents outgoing parameters and passes internal parameters to external variables; inout has both the function of in and the function of out, which belongs to the combination of in and out. If nothing is written, the in option is used by default.

However, because the SQL statement set section also uses a semicolon (;) as the delimiter, use delimiter to redefine the delimiter before creating the stored procedure and restore the delimiter after creation. Delimiter [new delimiter]

Complete format

Delimiter [new delimiter] create procedure [procedure name] ([in | out | inout] [parameter name] [parameter type],...) begin SQL statement set; end [new delimiter] delimiter

3. Examples of stored procedure creation:

1. Create a single SQL stored procedure select_id (), (it doesn't make any sense, just an example)

Delimiter / / create procedure select_id (inout id_inout int) begin select user_id from user where user_id=id_inout; end//delimiter;## create stored procedure select_id ()

two。 After the stored procedure select_id () is created, it is saved in the database. If a stored procedure with the same name will report an error, if you want to create a stored procedure with the same name, you need to delete it before you create it.

PROCEDURE select_id already exists

Create multiple SQL stored procedures select_ids ()

Delimiter $$create procedure select_ids () begin select user_id from user; select customer_id from user; end$$delimiter

Fourth, call stored procedures

Calling a stored procedure is simple: call [procedure name] (@ [external parameter name],.

Call select_id ()

Set@user_id=62356;call select_id (@ user_id); or call select_id (62356)

Call select_ids ()

Call select_ids ()

5. View stored procedures

1. View the specific contents of the stored procedure

Show create procedure test.select_id

two。 View stored procedure status

Show PROCEDURE status

Delete stored procedures

Drop procedure [procedure name]

Drop procedure test.select_id

VII. Description of the extension of SQL statement set

1) define the internal variables of the stored procedure

Define variable statement: declare [variable name,...] [variable type] [default default]

Assignment statement: set [variable name] = [expression]

You can define multiple variables of the same type at a time and set initial values.

2) Multi-layer nesting

Mysql > delimiter / / mysql > create procedure select3 ()-> begin-> declare name varchar (20) default 'student';-> select name;-> begin-> declare name varchar (20) default 'teacher';-> select name;-> end;-> end / / Query OK, 0 rows affected (0.00 sec) mysql > delimiter

3) conditional statement

If statement

If [judgment statement] then [execution content] end if;if [judgment statement] then [execution content] else [execution content] end if

Case statement

Case [variable name] when [value] then [execution content] when [value] then [execution content]... else [execution content] end case

Loop statement

While statement, judge first and then run

While [judgment statement] do [execution content] end whilemysql > create procedure `while` (in num int)-> begin-> declare var int;-> set var=0;-> while var set var=var+1;-> end while;-> select var;-> end / /

Repeat statement, run first and then judge

Repeat [execution content] until [judgment statement] end repeat;mysql > create procedure `repeat` (in num int)-> begin-> declare var int;-> set var=0;-> repeat-> repeat-> set var=var+2;-> until var > num-> end repeat;-> select var;-> end / /

Loop statement, run until leave is encountered

[tag name]: loop [execution content] leave [signature] [execution content] end loop;mysql > create procedure `loop` (in num int)-> begin-> declare var int;-> set var=0;-> label1:loop-> label1:loop-> set var=var+2;-> if var > num then-> leave label1;-> end if;-> end loop;-> select var -> end / / after reading the above, do you have any further understanding of how to store MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.