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

one。 Attitude towards stored procedures and functions

Stored procedures and functions should be used as little as possible in real projects for the following reasons:

1. Poor portability, stored procedures in MySQL may not be available when transplanted to sqlsever.

two。 Debugging trouble, reporting an error in db and reporting an error in the application layer is not a concept, it would be a devastating blow, just an error:1045 or something without a clue.

3. The scalability is not high.

So in the Internet age, large projects should try to use (not use) stored procedures and functions as little as possible!

two。 Create a stored procedure

2.1 what is a stored procedure?

Stored procedures and stored functions are a collection of sql statements. These statements are stored in the database as a whole.

2.2 Syntax for creating stored procedures:

Create procedure stored procedure name (parameter list)

Sql statement

. Example 1:

Delimiter / /

Create procedure pro () reads sql databegin select * from stu;end

/ /

So we now have a stored procedure pro, but this stored procedure has no parameters, it just performs a query operation.

Let's now explain the structure of this stored procedure:

Delimiter / / converts semicolons to / / because when he encounters a semicolon during sql execution, he says stop, so we have to convert it to / / until the last line.

Reads sql data explains that the state of characteristic is read-only here, and other modes are: no sql does not have sql statements, ins sql does not contain read and write statements, modifies sql data contains statements that write data, and so on.

Begin / * * / end We must use begin and end when there is a collection of multiple statements in a stored procedure.

/ / end the entire stored procedure

2.3 using stored procedures

Now that we've just created a stored procedure, how do we use it?

Syntax: call stored procedure name ()

Take the example used by the previous stored procedure pro:

Call pro ()

2.4 create a stored procedure with parameters

Parameter list: there are three types of parameters for stored procedures: in,out,inout represents incoming and outgoing parameters, and both incoming and outgoing parameters.

Example: first, let's create two tables: the course schedule is the slave table of the student table.

Create table stu (

Stu_id bigint primary key auto_increment,# student number

Stu_name varchar (10) not null,# name

Stu_major int not null,# professional number

Stu_sex char,# gender

Date of admission to stu_in date,#

Date of birth of stu_birth date,#

Foreign key (stu_major) references major (ma_id) # Professional Foreign key Settings

);

Create table major (

Ma_id int primary key

Ma_name varchar (15)

Ma_boss varchar (10)

);

Insert into major values (1, "fuse", "Zhang San")

Insert into major values (2, "E-commerce", "Li Si")

Insert into stu values (1, "Xiaoming", 1, "male", "2017-09-01", "1998-12-23")

Insert into stu values (2, "Xiao Gao", 1, "male", "2017-09-01", "1998-05-01")

Insert into stu values (3, "Xiao Li", 2, "male", "2017-09-01", "1999-04-01")

Let's create a stored procedure with parameters to find the name of the student's major, as follows:

Delimiter / /

Create procedure pro1 (in sname varchar (10), out ma varchar (10))

Reads sql data

Begin

Select ma_name into ma from major where ma_id = (select stu_major from stu where stu_name=sname)

End

/ /

Use this stored procedure: the code is as follows:

Set @ ma= "before query"

Call pro1 ("Xiao Li", @ ma)

Select @ ma

Explain the code: first use set @ ma to define a global variable, then call the stored procedure using the call stored procedure name syntax, while the value of the global variable ma changes.

three。 Create a storage function

3.1 the difference between stored procedures and stored functions.

1. There must be a return return value in the function

two。 There are three inout inout parameters in the stored procedure, the default is in, but there is only one in type in the function.

3.2 create a function

Syntax: create function function name ()

Return return type

Collection of sql statements

Example 2:

Delimiter / /

Create function fun1 (num int)

Returns int

Begin

Return num+1000

End

/ /

Obviously, the biggest difference between a function and a stored procedure is return

3.3 call function

Using syntax no longer uses the keyword call, but the keyword select, select function name

Example:

Select fun1 (100)

four。 Delete stored functions and stored procedures

Syntax: drop procedure | function stored procedure name or function name

Example:

Drop procedure pro

Note: at this time, the stored procedure or function name is without parentheses!

five。 Using cursors in stored procedures and stored functions

5.1 Why do I need cursors?

When we use stored procedures, we may use multiple pieces of data, so we need to use cursors to store multiple pieces of data.

5.2 points for attention when using cursors

Cursors cannot exist alone and must be used in stored procedures or stored functions.

5.3 use cursors

Syntax:

1. Create a cursor: declare cursor name cursor for select statement

two。 Open cursor: open cursor name

3. Use cursor: fetch cursor name into variable name

4. Close the cursor: close cursor name

Example 3:

Delimiter / /

Create function fun3 (id int)

Returns intreads sql data

Begin

Declare cur cursor for select stu_id from stu

Open cur

Fetch cur into id

Close cur

Return id

End

/ /

Use

Set @ id=0

Select fun3 (@ id)

You can see that the cursor simply gives the first value to the variable.

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.