PHP storage routines, stored procedures Advanced Learning _php Tutorial

What is a storage routine?
A storage routine is a set of SQL statements that is stored in a database tutorial server, by invoking a specified name in the query to

Line these SQL statement commands.
Why use stored procedures?
We all know that there are two kinds of applications, one is web-based, the other is desktop-based, and they both interact with the database

To complete the data access work. Suppose you now have an application that contains both of these, and now you want to modify one of these

Query SQL statements, then we may want to modify their corresponding query SQL statements at the same time, when our application is very

Big very complex when the problem arises this, difficult to maintain! Also put SQL query statements in our Web program or desktop

is susceptible to SQL injection corruption. And the storage routines just can help us solve these problems.
Stored procedures (stored procedure), stored routines (store routine), storage function differences
The MySQL storage routines actually contain stored procedures and stored functions, which are collectively referred to as storage routines.
Where the stored procedure is mostly done when you get a record or insert a record or update a record or delete a record, which completes the Select

Insert Delete Update, and so on. The storage function only completes the query, accepts the input parameters and returns a

a result.

Creating stored procedures, stored functions

Create procedure stored procedure name (parameter)
Stored Procedure Body

Create function store functions name (parameter)
Storage function Body
Suppose you now have a table DB_INFO table structure in database OMCMC as follows:

SET foreign_key_checks=0;
-- ----------------------------
--Table structure for Db_news
-- ----------------------------
DROP TABLE IF EXISTS ' db_news ';
CREATE TABLE ' Db_news ' (
' id ' int (ten) is not NULL auto_increment,
' title ' varchar ($) Not NULL,
' Editor ' varchar default NULL,
' origin ' varchar default NULL,
' Tags ' varchar (+) default NULL,
' Content ' text is not NULL,
' Hits ' int (Ten) default ' 0 ',
' IP ' varchar (not NULL),
' Time ' int (ten) is not NULL,
PRIMARY KEY (' id ')
) Engine=myisam auto_increment=2 DEFAULT charset=latin1;

-- ----------------------------
--Records
-- ----------------------------
INSERT into ' db_news ' VALUES (' 1 ', first App ', ' xqbar.com ', ' xqbar.com ', ' OMCMC ',

' Detail. ', ' 100 ', ' 127.0.0.1 ', ' 1215051225 ');

Using the table above we create a simple stored procedure
CREATE PROCEDURE Select_news ()
Select Title,hits from Db_news;

Run at the terminal and call

From the above we see how to call our stored procedure

Call a stored procedure
Call stored procedure name ();

Above we have created a simple stored procedure, and of course it is impossible for our application to use such a simple stored procedure

, what we need is the ability to pass parameters to the stored procedure to return the result data we need. Here's a look at the storage

Parameters of the stored procedure.
Parameters of the stored procedure
Typically the stored procedure takes the user's arguments and returns the result to the calling user.
The MySQL tutorial states that for a stored procedure parameter, each parameter must declare its parameter name, the data type, and the parameter is

Input parameters are also used to return information or both, and for stored functions the PHP tutorial only supports input parameters.
Declaring a parameter specifies that you want to use the keyword in,out,inout.
which
In: For input parameters
Out: For returning parameters
INOUT: Used to pass a parameter value to a stored procedure and return if the value changes
It is also stipulated that for the Out,inout parameter, when we call the stored procedure, we need to add @ before the parameter name to ensure that the parameter

Number in the process, we modify the stored procedure above to pass the information number to the stored procedure select_news, return to

Back to the corresponding information title for us to view.

drop procedure if exists select_news;
CREATE PROCEDURE select_news (in ID int,out title varchar (200))
Select Db_news.title from Db_news where Db_news.id=id;

Run at the terminal and call

Note Call format
Call Select_news (1, @title);
Where 1 is the information we want to pass in the number, title is to return the corresponding information number header, because it is out, and then call

At the top of the process. (see example below)
drop procedure if exists getnum;
CREATE PROCEDURE getnum (out num int)
SELECT INTO Num;

Call Getnum (@num);
Select @num;
：

PHP storage routines, stored procedures Advanced Learning Two
Then the previous article, then called with the return value of the previous example we can call the stored procedure using the following method:
SELECT @ Returns the parameter;
We now use this method to invoke the select_news we created above to see

One might wonder why the title returned is null, not the same as call Select_news (1, @title).

Our database number corresponds to the data first App, which is due to the fact that our stored procedure body does not return a parameter to the title

return value.

Now let's take a look at the storage function
Still take the Db_news table as an example, but in this storage function we add some other knowledge about the storage routines

Knowledge to introduce the subjects we are going to talk about.

Drop function if exists count_news;
Delimiter//
Create function count_news (hits int) returns INT
Comment ' Count the number of messages over this click ' based on the number of clicks passed in
Begin
declare total_news int;
DECLARE hits_num int default 0;
If Hits>=0 Then
Set hits_num=hits;
Select COUNT (id) into total_news from Db_news where db_news.hits>hits_num;
Else
Set total_news=0;
End If;
return total_news;
End
//
delimiter;

In the storage function above we used to store the declaration of the routine variable, set its value, Judgment statement, storage function Special

Some return value types and how the stored routines are described (comment).

Unlike a stored procedure return parameter, a stored function does not have a direct declaration of which variable is the return parameter when defined, but only

Using returns to declare the data type to which the return parameter belongs, the return parameter is returned by using return in the body of the function

Back to the form of the data variable to be represented. It is important to note that:
The store function only supports input parameters, and there is no in or inout before the input parameters.

To return to the storage function above, let's explain what the code means:
Drop function if exists count_news;
If there is a storage function count_news delete the stored function, as with the stored procedure we can use the following statement

Delete a stored procedure or function
Drop procedure|function [if exists] stored procedure name | The name of the stored function;
Delimiter//
Use delimiter to change MySQL by default using semicolons (;) use the new end symbol to end the current statement, using the

After delimiter, the default semicolon (;) closing sentence symbol no longer works until after the declaration terminator is resumed.
Create function count_news (hits int) returns INT
Note that the parameters of the stored function are only input parameters and are no longer declared in or inout, returning simply declaring the data class to return

Type
Comment ' Count the number of messages over this click ' based on the number of clicks passed in
Use comment to describe the function information of the stored procedure or stored function. Use the Format comment ' description string

'
Begin
Use begin to qualify a processing module
declare total_news int;
Declaring variable format declare variable name data type [default defaults]
DECLARE hits_num int default 0;
Declaring variable format declare variable name data type [default defaults]
If Hits>=0 Then
Set hits_num=hits;
Assigning a value to a variable using set
Select COUNT (id) into total_news from Db_news where db_news.hits>hits_num;
SQL statements can use into to assign values to variables
Else
Set total_news=0;
End If;
Above is a judgment statement, note that the format is if-else-end if or if–else if-else-end if
Note the semicolon (;)
return total_news;
Use return to return the value of the stored function to return, note that the value can only be a value.
End
//
delimiter;
To synthesize our conceptual knowledge above:
1. The parameters of the stored function are different from the stored procedure.
2. How to change the default end symbol for MySQL in the storage routines.
3. How the feature information is described in the storage routines.
4. Declare, set variables.
5. Begin-end statement block.
6. Conditional Judgment If-else If-else-end if or if-else-end if or if-end if

http://www.bkjia.com/PHPjc/630749.html www.bkjia.com true http://www.bkjia.com/PHPjc/630749.html techarticle what is a storage routine? A storage routine is a set of SQL statements stored in a database tutorial server that executes these SQL statement commands by invoking a specified name in the query. Why ...?

