mysql< transactions and stored Procedures >

transactions and stored procedures Transaction Management

The concept of a transaction

A transaction is a set of operations against a database, which can consist of one or more SQL statements, and the operation of the same transaction has the characteristics of synchronization, that is, the statements in the transaction are either executed or not executed.

Use of transactions

Open transaction Start TRANSACTION;

Execute SQL statement

Commit a transaction commit;

Cancel Transaction (rollback)

Commit a transaction

The action statements in a transaction need to be committed manually using a commit statement, and only after the transaction is committed does the action take effect.

Rollback of a transaction

If you do not want to commit the current transaction, use the Rollback statement to cancel the current transaction.

The rollback statement can only perform a rollback operation on uncommitted transactions, and committed transactions cannot be rolled back.

Isolation level of a transaction

REPEATABLE READ (Repeatable Read)

READ UNCOMMITTED (not submitted)

Read COMMITTED (Reading commit)

SERIALIZABLE (Serializable)

defining attributes of a transaction

Atomic Nature

Atomicity means that a transaction must be treated as an indivisible minimum unit of work, and only if all of the database operations in the transaction are successful, the entire transaction executes successfully.

Consistency

Consistency refers to a transaction that transforms a database from one state into the next consistent state.

Isolation of

Isolation can also be referred to as concurrency control, serializable, locks, etc., when multiple users concurrently access the database, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.

Durability

Once a transaction is committed, its modifications are persisted to the database, and should not have any effect even if the database fails.

The persistence of a transaction cannot be 100% durable and can only be guaranteed to be permanent from the perspective of the transaction itself, while some external causes cause the database to fail, such as a hard disk corruption, and all committed data may be lost.

creation of stored procedures What is a stored procedure

A stored procedure is a collection of one or more SQL statements that, when a series of complex operations are performed on a database, can encapsulate these complex operations into a single block of code for reuse, greatly reducing the workload of the database developer.

create a stored procedure

CREATE PROCEDURE sp_name ([Proc_parameter])

[Characteristics ...] Routine_body

Create PROCEDURE: The keyword used to create the stored procedure.

Sp_name: The name of the stored procedure.

Proc_parameter: The parameter list for the specified stored procedure.

Characteristics: The attribute used to specify the stored procedure.

Use of Variables

Defined

In MySQL, variables can be declared in subroutines to hold values in the process of data processing, which are scoped to begin ... End program.

DECLARE Var_name[,varname]...date_type[default value];

Var_name: The name of the local variable.

The default value: clause gives the variable the value that can be declared as a constant or an expression. If there is no default clause, the initial value of the variable is null.

Assigning a value to a variable using the SET statement

SET Var_name =

Expr[,var_name = expr] ...;

Use SELECT ... into assigns a value to one or more variables

SELECT col_name[...]

Into var_name[...] table_expr;

defining conditions and handlers

Defining a condition refers to defining a problem that is encountered during program execution

DECLARE condition_name condition for [condition_type];

Two forms of Condition_type:

[Condition_type]:

Sqlstate[value] Sqlstate_value|mysql_error_code

Handlers define how you should handle problems encountered during program execution, and ensure that stored procedures can continue processing when they encounter warnings or errors using the Declare statement definition

DECLARE Handler_type handler for condition_value[,...] Sp_statement

Handler_type:

continue| Exit| UNDO

Condition_value:

|condition_name

| SQLWarning

| Not FOUND

| SQLEXCEPTION

|mysql_error_code

Handler_type: For error handling, parameter values

There are 3: CONTINUE, Exit, and Undo.

CONTINUE: Indicates that an error has been encountered and is not processed, continue execution;

Exit: Exit immediately if you encounter an error.

UNDO: Indicates that the operation was not supported in MySQL until it was recalled after the error was encountered. sp_statement: A parameter is a program statement segment that represents a stored procedure that needs to be executed when a defined error is encountered.

Condition_value: Indicates the type of error.

Use of Cursors

When you write a stored procedure, the query statement may return multiple records, and if the amount of data is very large, you need to use the cursor to read the records in the query result set one by one. Cursors are a mechanism for easy processing of multiple rows of data.

Statement

Syntax: DECLARE cursor_name cursor for select_statement

Example: DECLARE cursor_student cursor FOR select S_name,s_gender from student;

Use

Syntax: OPEN cursor_name FETCH cursor_name into Var_name[,var_name] ...

Example: FETCH cursor_student into S_name, S_gender;

Shut down

CLOSE cursor_name

use of Process Control

Process Control statements in stored procedures are used to divide or combine multiple SQL statements into code blocks that conform to business logic, with 7 process control statements in MySQL

1. If statement

The syntax format is as follows:

IF Expr_condition then Statement_list

[ELSEIF Expr_condition then statement_list]

[ELSE Statement_list]

END IF

2. Case statement

The syntax format is as follows:

Case case_expr

When When_value then Statement_list

[When When_value then Statement_list] ...

[ELSE Statement_list]

END case

3. Loop statement

The syntax format is as follows:

[Loop_label:] LOOP

Statement_list

END LOOP [Loop_label]

4. Leave statement

The syntax format is as follows:

LEAVE lable

5. Iterate statement

The syntax format is as follows:

Iterate lable

6. Repeat statement

The syntax format is as follows:

[Repeat_lable:] REPEAT

Statement_list

UNTIL expr_condition

END Repeat[repeat_lable]

7. While statement

The syntax format is as follows:

[[while_lable:] While expr_condition do

Statement_list

END while [while_lable]

use of stored procedures call a stored procedure

Call Sp_name ([parameter[,...])

Call: The keyword for the calling stored procedure.

Sp_name: The name of the stored procedure.

Parameter: The parameters for the stored procedure.

Viewing stored procedures

1. Use the Show status statement

Call Sp_name ([parameter[,...])

2. Use the show Create statement

SHOW create{procedure| FUNCTION} sp_name

3, Information_schema. View in Routines Table

SELECT * from INFORMATION_SCHEMA. Routines

WHERE routine_name= ' CountProc1 '

and routine_type= ' PROCEDURE ' \g

Modifying stored Procedures

ALTER {procedure| FUNCTION} sp_name[characteristic ...]

Sp_name: Represents the name of a stored procedure or function. Characteristic: Indicates which part of the stored procedure to modify, and the value of characteristic is divided into 8 parts.

To Delete a stored procedure

drop{procedure| FUNCTION}[if EXISTS] Sp_name

Integrated case-Stored procedure application

mysql< transactions and stored Procedures >