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

Brief introduction

A stored procedure is a statement string (statement collection) of SQL statements and control flow statements. It can not only have input

Parameters can also have output parameters, and the stored procedure can return the result set to the caller by introducing the parameters, and the format of the result set is determined by the caller. Returns a status value to the caller indicating whether the call succeeded or failed, including operation statements against the database, and can call another stored procedure in one stored procedure.

Advantages over SQL statement stored procedures:

1. The stored procedure allows the component to be programmed. After being created, the stored procedure can be called many times in the program without having to rewrite the SQL statement of the stored procedure, thus improving the portability of the program.

2. The stored procedure can achieve faster storage speed.

3. Stored procedures can reduce network traffic. For the same operation against database objects such as query, modify the table if the SQL statement involved in this operation is organized into a stored procedure, then when the computer calls the stored procedure, only the calling statement, rather than multiple SQL statements, is transmitted in the network, thus greatly increasing the network traffic at the bottom of the network load.

4. Stored procedures can be used as a security mechanism.

I. the creation of stored procedures

1.1 stored procedure syntax:

Create procedure (parameter 1, parameter 2,...) beginsql;end

Before creating a stored procedure, we must use delimiter to modify the default Terminator of the MySQL statement, otherwise it cannot be created successfully.

Comments for stored procedure statements:

Two styles of MySQL comments

"- -": single-line comment

/ *. * /: generally used for multiline comments

Syntax:

Delimiter delimiter / / changes the default Terminator to / /.

1.2 call stored procedure

Call (parameter 1, parameter 2,...)

1.3 stored procedure parameter types

1.3.1 IN parameters

Function: read the value of external variables, and the valid range is limited to the inside of the stored procedure

Mysql > delimiter / / mysql > create procedure pin (in p_in int)-> begin-> select pairing;-> set pairing 2;-> select pairing;-> end;-> / /

Equivalent to set @ p_in=1

Compare the results of call pin (2) and select @ p_in

Simple creation and invocation of instance 1 stored procedure

Mysql > delimiter% mysql > create procedure selcg ()-> begin-> select * from category -> end% Query OK 0 rows affected (0.00 sec) mysql > call selcg ()% +-+ | bTypeId | bTypeName | +-+-+ | 1 | windows App | | 2 | website | | 3 | 3D Animation | | 4 | linux Learning | | | 5 | Delphi Learning | | 6 | * * | | 7 | Network Technology | | 8 | Security | | 9 | plane | | 10 | AutoCAD Technology | +-+-+ 10 rows in set (0.01sec) Query OK | 0 rows affected (0.01 sec)

Define the stored procedure getonebook. After entering the ID of the book, you can call the corresponding book record.

Mysql > delimiter / / mysql > create procedure getonebook (in id int)-> begin-> select * from books where bId=id;-> end//Query OK, 0 rows affected (0.00 sec) mysql > delimiter; mysql > call getonebook (4)-> +-+- + | bId | bName | bTypeId | publishing | price | pubDate | author | ISBN | +-+-- + -+ | 4 | pagemaker 7.0short-term training course | 9 | China Power Press | 43 | 2005-01-01 | Sun Liying | 7121008947 | + -+-+-- + 1 row in set (0.00 sec) Query OK 0 rows affected (0.00 sec) mysql >

1.3.2 Out parameters

Function: not all external variable values are removed, and the new values are retained after the stored procedure is executed.

Example 2.

Mysql > delimiter / / mysql > create procedure pout (out p_out int)-> begin-> select paired output;-> set paired outbound 2;-> select paired outout;-> end;-> / / Query OK, 0 rows affected (0.00 sec) mysql > set @ p_out=1

After executing the call @ p_out stored procedure, the second use of select @ p_out displays 2; it means that the variable @ P_out has been assigned 2

1.3.3 how to call the return value of the stored procedure out type

Example 4. Edit the stored procedure so that the return value is related to the title of the book.

Mysql > delimiter / / mysql > create procedure demo (out pa varchar)-> begin-> select bName into pa from books where bId=2;-> end//Query OK, 0 rows affected (0.00 sec) mysql > delimiter; mysql > call demo (@ a); Query OK, 1 row affected (0.00 sec) mysql > select @ a +-+ | @ a | +-+ | * and network security | +-+ 1 row in set (0.00 sec)

1.3.4 Inout parameters

Function: read external variables and keep new values after the stored procedure is executed

Mysql > create procedure pinout (inout p_inout int)-> begin-> select poured out;-> set paired inout 2;-> select paired inout;-> end;-> / /

1.3.5 stored procedures with no parameters

If the storage country constant does not specify the parameter type when it is created, you need to specify the parameter value when calling it.

Mysql > create table T1 (id int (10)); Query OK, 0 rows affected (0.03 secmysql > create procedure T2 (N1 int)-> begin-> set @ xroom0;-> repeat set @ x;-> insert into T2 values (@ x);-> until @ x > N1-> end repeat -> end;-> / / Query OK, 0 rows affected (0.00 sec) mysql > create table T2 (id int (10)); Query OK, 0 rows affected (0.03 sec) mysql > call T2 (10); cycle 10 times Query OK, 1 row affected (0.03 sec) results verify mysql > select * from T2

Second, the use of stored procedure variables

2.1 use declare for variable definition

Variable definition: declare variable_name [, variable_name.]

Datatype [default value]; datatype is the data type of MySQL, such as int,float,date,varchar (length). Variable assignment can inherit create procedure decl () mysql > delimiter / / mysql > create procedure decl ()-> begin-> declare name varchar (20) in different stored procedures;-> set name= (select bName from books where bI=12);-> select name;-> end//Query OK, 0 rows affected (0.00 sec)

Flow control statements of stored procedures

2.1 BEGIN.END statement

Defines a block made up of sequentially executed SQL statements.

Syntax format:

BEGINStatement BlockEND

2.2 IF...ELSE statement

This statement is used to define certain statements that are conditionally executed, where the ELSE statement is optional

Syntax format:

IF Boolean_ expressionstatement[ELSE [IF boolean_expression] statentent]

2.3 Loop statement

1 、 while.end while:

While 1 do... If * then break;end while

2 、 repeat .end repeat:

Check the results after performing the operation, while while checks before execution

3 、 loop.end loop:

The loop loop does not need initialization conditions, just like the while loop, while the repeat loop does not need the end condition. The meaning of the leave statement is inseparable from the loop.

4. LABLES label

It can be used before begin repeat while or loop statements, and statement labels can only be used before legitimate statements. You can jump out of the loop to make the run instruction match the final async of the statement

5. ITERATE iteration

Restart the compliance statement by referencing the label of the match statement

View stored procedures:

Show create procedure demo\ G

View all stored procedures

Mysql > show procedure status\ G

Modify the stored procedure:

Use the alter statement to modify

Alter {procedure | function} sp_name [characteristic...]

Characteristic:

{contains SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}

| | SQL SECURITY {DEFINER | INVOKER} |

| | COMMENT 'string' |

The sp_name parameter represents the name of a stored procedure or function

The characreristic parameter specifies the properties of the stored procedure function.

CONTAINS SQL means that the child process contains SQL statements, but not statements that read or write data

NO SQL indicates that the subroutine does not contain SQL statements

READSSQL DATA represents the statement of the subroutine Boahan to write data.

SQL SECURITY {DEFINER | INVOKER} indicates who has permission to execute

DEFINER says that only the definer can execute it.

INVOKER indicates that the caller can execute

COMMENT 'string' is the comment message

Delete stored procedure

Grammar 1: drop procedure sp_name

Grammar 2: drop procedure if exists sp_name

Note: you cannot delete another stored procedure in one stored procedure, only another stored procedure can be called.

A transaction is a logical processing unit consisting of a set of SQL statements that either succeed or fail.

Transaction processing: you can ensure that multiple operations of non-transactional units can be completed successfully, otherwise the data resources will not be updated.

The default transaction of the database is auto-commit, that is, a sql is sent and it executes one. If you want multiple sql to be executed in a single transaction, you need to use a transaction for processing. When we open a transaction and do not commit, mysql automatically rolls back the transaction. Or we can roll back the transaction manually using the rollback command.

Function: transactions make the program more reliable and simplify error recovery

Four characteristics:

Autmic: transactions are being executed, either all or not.

Consistency (Consistency): a transaction must make the database guide from one consistency state to another, and consistency is closely related to atomicity. The integrity of the database is not compromised before and after the transaction begins.

Isolation: the execution of one transaction cannot be interfered with by other transactions. And the operations and data used within a transaction are isolated from other concurrent transactions, and the transactions executed concurrently cannot interfere with each other, which are usually implemented by locking.

Durability: once a transaction is committed, its changes to the data in the data should be permanent, and other operations or failures that follow should not have any effect on alignment.

Mysql transaction processing example

There are two main methods of transaction processing in MYSQL.

1. Using begin,rollback,commit to realize

Begin starts a transaction

Rollback transaction rollback

Commit transaction confirmation

two。 Directly use set to change the auto-commit mode of mysql

Mysql is automatically submitted by default, that is, if you submit a query, it will be executed directly! Can be passed through

Set autocommit = 0 forbids automatic submission

Set autocommit = 1 to enable auto-commit

Note, however, that when you use set autocommit = 0, all your future sql will be processed as transactions until you confirm with commit or rollback, and note that when you end the transaction, you also open a new transaction! Follow the first method to only make the current as a transaction!

MYSQL supports transactions only for datasheets of type INNODB and BDB, and other types are not supported!

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.