MySQL Database Promotion-stored procedures and functions

Day06 MySQL database stored procedures and functions

An overview of stored procedures and functions:

Database stored procedures and stored functions refer to the collection of sql statements defined in the database. These sql can be directly used by calling the names of these stored procedures or stored functions directly. Can avoid developers to write a large number of sql statements to operate data, can reduce the client-side and server-side data transmission. Therefore, it is also important to learn stored procedures and stored functions well.

2. Stored procedure [procedure]

1. Create a stored procedure

Create procedure customizes a storage name () begin select statement end; call storage name (); calls stored procedures

2. Delete stored procedures

Drop procedure storage name

3. Stored procedures with parameters

3.1:in parameter, that is, input parameter

Create procedure storage name (in column name data type) begin select statement end; call storage name (value of column name)

3.2:out parameter, that is, output parameter

Create procedure storage name (out column name data type) begin select * into column name from table name; end; call storage name (@ column name)

3.3:inout parameters, that is, input and output parameters

Create procedure storage name (inout column name data type) begin select * into column name from table name; end; set @ column name = a value / / define a variable and assign a call storage name (@ column name)

4. Define variables

4.1: define local variables

Declare an int / / define set astat10; / / assign select a; / / output

4.2: define the global variable "@ as the starting character"

Set @ bread20; / / define the session variable select @ b; / / output

3. Storage function [function]

1. Create an instance of the storage function definition.

Create function function name (an int, b int) returns int begin declare c int; set return c; end; select function name (a numeric value, b numeric value) / / call storage function

Fourth, the use of the cursor

In the MySQL query database is swollen, stored procedures and stored functions can be used to read the records in the result set one by one. It mainly includes the following steps:

1. Declare the cursor:

Declare cursor name cursor select_name translation; select_name: is a select statement that returns one or more rows of data.

Example:

Declare info_student cursor for select sid,name,age from student where sid=1

2. Open the cursor

Open cursor name

3. Use the cursor

Fetch cursor name into column name

4. Close the cursor

Close cursor name

View stored procedures and functions

1. Use the show status statement to view

Show {procedure | function} status [like 'pattern'] translation: procedure: query stored procedure function: query stored function like' pattern': is used to match stored procedure or function name.

2. Use the show create statement to view

Show create {procedure | function} sp_name; translation; procedure: query stored procedure function: query stored function sp_name: indicate stored procedure or function name

3. Modify stored procedures and stored functions

Alter {procedure | function} sp_name {contains sql | no sql | reads sql data | modifies sql data} | sql security {definer | invoker} | comment 'string' translation: procedure | function: indicates whether it is a stored procedure or a stored function sp_name: indicates the name contains sql: indicates that the subroutine contains sql statements But the statement no sql that does not contain read and write data: indicates that the subroutine contains sql statements reads sql data: indicates that the subroutine contains read data modifies sql data: indicates that the subroutine contains write data definer | invoker: indicates that the permission is executed. The former means that the definer can execute it himself. The latter is that the caller can execute comment 'string': comment information

4. Delete stored procedures and stored functions

Drop {procedure | function} sp_name

VI. Concluding remarks:

At this point, stored procedures and stored functions have been learned! Digest it well!