Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to understand the stored procedures and cursors of 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.

Stored procedure and cursor of MySQL

I. stored procedure

T-sql programming

Add, delete, modify, check-"sentence

MySQL is a real language T-sql is similar to the function function

1. Stored procedure:

Create a format:

Create procedure procedure name (parameter 1, parameter 2,... (.)

Begin

Sql code

End

Begin... And is equivalent to the previous {code snippet}.

Precompile, execute once (compile), and then use the direct call to compile the result, which is fast

Create procedure spade ()

Begin

Select * from books

End

/ /

Mysql encounters by default; executes,; executes symbols

Delimiter symbols modify execution symbols while the stored procedure is running

Calls to stored procedures:

Call procedure name () / / execute once (compile)

2. Parameters in the stored procedure:

In passes in parameters to pass the value in.

Return value of out outgoing parameter

Inout input and output parameters are inefficient

How many records should be found when in input id?

Create procedure one (in id int)

Begin

Select * from books where booksId=id

End

/ /

Call one (3); / /

A return value for the out parameter

Create procedure two (out spade int)

Begin

Select count (*) into spade from books

End

/ /

Note: assign into in the sql statement

Variables outside the mysql procedure require @ a

Call thr (@ a) / /

Select @ a / / View variables

Note:

Variables in mysql

Out of procedure @ variable name

In-process: declaration

Declare variable name data type

Find books of website type

Find the id of the type

Find the corresponding book in the book list according to the type of id

Create procedure toto ()

Begin

Declare btid int

Set btid= (select cid from category where cName= Chronicle of events)

Select * from category where cid=btid

End

/ /

3. Conditions and procedures

Execute certain programs under certain conditions

Declare handler_type handler for conditionValue sq_statement

Handler_type operation type

Continue, continue.

Exit exit

Undo ignore

Wrong number of conditionValue sql

02000 incorrect number of FETCH variables

23000 disk is full

Sq_statement performs the corresponding operation

Declare continue handler for sqlstate "02000" set done=1

When a 02000 error occurs, the program continues to execute after setting done=1

Second, the cursor grabs the data

1. Declaration cursor

Declare cursor name cursor for sql statement (select)

2. Open the cursor

Open cursor name

3. Grab data from the cursor

Fetch Light Meter name into

4. Close the cursor

Close cursor name

/ / grab the data in btype

Create procedure sex ()

Begin

Declare id int

Declare name varchar (30)

Declare done int default 0

Declare spade cursor for select * from books

Declare continue handler for sqlstate'02000'set done=1

Open spade

Repeat

Fetch spade into id,name

Select id

Until done

End repeat

Close spade

End

/ /

Until false / / termination condition is not met

Until true termination conditions are met

Add:

If conditional then

Execute statement

Else if conditional then

Execute statement

Else if conditional then

Execute statement

...

None of the above conditions of else are satisfied.

End if

Mysql stored procedures are generally used to improve the running efficiency of sql.

Php calls stored procedures

Create procedure getDate ()

Begin

Select * from btype

End

Delete stored procedure

Drop procedure procedure name

View stored procedures

Show create procedure procedure name

After reading the above, do you have any further understanding of how to understand MySQL stored procedures and cursors? 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.