MySQL Basics Get started learning "13" stored procedures

The SQL command---the MySQL engine first parses the SQL command we entered, and looks at whether the statement we have entered is--and syntax--compiles to the MySQL engine to recognize the command--and execute the result-- > returns to the client.

If we omit the syntax analysis and compilation, the efficiency of MySQL execution will improve.

A "stored procedure" is a precompiled collection of SQL statements and control statements, stored in a name and processed as a unit;

Stored procedures are stored in the database, can have application calls to execute, and allow the user to declare variables and control of the process;

A stored procedure can accept parameters, accept parameters for input and output types, and can have multiple return values.

"Advantages of stored Procedures": 1. Enhanced the function and flexibility of SQL statement: In the stored procedure can write control statements, with strong flexibility, can complete complex judgment, etc.

2. Faster execution Speed: If an operation contains a large number of SQL statements, these statements will be performed by the MySQL Engine syntax analysis, compilation, execution, the efficiency is relatively low;

And the stored procedure is precompiled, when the client first call this stored procedure, the MySQL engine will parse it, compile and so on, and then store this compilation results in memory, and then the client calls this stored procedure directly from memory execution, more efficient, faster

3. Reduce network traffic: If we let the server execute each individual SQL statement by the client, the amount of data submitted over the HTTP protocol is relatively large;

In the application of stored procedures, we only need to pass the name of the stored procedure, the required numeric value, the amount of data submitted to the server is relatively small.

"Create Stored Procedure":

CREATE

[definer = {User | Current_User}] Creator, default user currently logged on to the MySQL client

PROCEDURE sp_name ([proc_parameter[,...]]) The name of the stored procedure can have 0~ multiple parameters

[Characteristic ...] Routine_body attribute (same as custom function), process body

Proc_parameter:

[In | Out | INOUT] Param_name Type

[Parameter]:

In: Indicates that the value of this parameter must be specified when the stored procedure is called;

Out: Indicates that the value of this parameter can be changed by code in the stored procedure and can be returned;

INOUT: Indicates that the parameter is specified at invocation time and can be changed and returned to the caller by the procedure body of the stored procedure.

[Properties]:

COMMENT ' String '

| {CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}

| SQL SECURITY {definer | INVOKER}

which

COMMENT: Notes

CONTAINS sql: Statements that contain SQL statements but do not contain read or write data

No sql: does not contain SQL statements

READS SQL Data: Statements that contain read data

Modifies SQL data: Statements that contain write data

SQL SECURITY {definer | INVOKER}: Indicates who has permission to execute

[Process body]:

The process body is composed of legitimate SQL statements;

can be "arbitrary" (for record increment, delete, change, check, multi-table connection operation) SQL statements (we can not create a data table through the stored procedure, it is not possible to create a database through the stored procedure);

Process body if it is a composite structure (more than two statements) use BEGIN ... End statement;

You can declare variables within a compound structure statement, you can use Process Control statements (if statements, when statements), while loops, and so on.

"Example of creating a stored procedure without parameters":

"Calling stored procedure" call Sp_name ([parameter[,...]) or called sp_name[()]

If the stored procedure has no parameters at the time of encapsulation, then () may or may not, or () cannot be omitted if there are parameters in the stored procedure encapsulation process;

Example 2--create a stored procedure with an in type parameter:

If we frequently delete records from a data table, delete from tbl_name WHERE xxx, we can encapsulate this process as a stored procedure:

！！！ Attention!!! When giving parameters, the name of the parameter cannot be the same as the record name in the data table, otherwise all records will be deleted.

"Modify stored Procedure" ALTER PROCEDURE sp_name [characteristic ...]

COMMENT ' String '

| {CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}

| SQL SECURITY {definer | INVOKER}

The above method can only modify comments, content types, etc., and cannot modify the process body.

Only the current stored procedure can be deleted and rebuilt.

"Delete Stored procedure": Drop PROCEDURE [IF EXISTS] Sp_name

The correct creation method is as follows:

Example 3--creating a stored procedure with in and out type parameters

Deletes a record with an ID that is not fixed from the data table, and returns the remaining number of records:

@nums is actually a variable. Variables can also be at begin ... Declared in end.

Differentiate: By declare declaration (the statement must be at begin). The variable for the first row of the end block is a local variable , scoped only at begin. End statement blocks.

Pass (SELECT ... into or) the variable declared by the SET @ statement We call the user variable, bound to the MySQL client. Variables declared in this way are only valid for the client used by the current user.

Example 4--creating a stored procedure with multiple out type parameters

Delete users based on the Age field, return the number of deleted users, and the number of users remaining:

SELECT Row_count (); Gets the number of rows, which is exactly the total number of records affected to get inserted, deleted, updated

"The difference between a stored procedure and a custom function":

1. The functions of the stored procedure implementation are more complex, and the functions are more targeted: Generally, we seldom use functions to manipulate tables, but we often use stored procedures to manipulate tables.

2. A stored procedure can return multiple values, whereas a function may have only one return value.

3. Stored procedures are generally independent to execute (call Sp_name ...) , and the function is mostly seen as part of other SQL statements (and the built-in functions are the same).

We seldom use functions in practical applications, but we can often encapsulate complex processes into stored procedures.

Stored procedures can only modify simple attributes and cannot modify the process body. If you want to modify the process body, you can only delete the stored procedure before creating it.

MySQL Basics Get started learning "13" stored procedures