PHP storage routines, stored procedure Advanced Learning

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

Line these SQL statement commands.
Why should I use a stored procedure?
We all know that there are two kinds of applications, one is web-based, the other is based on the desktop, they are with the database to make

Each other to complete the data access work. Suppose that there is an application now that contains both, and now you want to modify one of the

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

When the problem is very complicated, it is difficult to maintain! In addition, put SQL query statements in our Web program or desktop

is easily compromised by SQL injection. And the storage routines can help us solve these problems.
Stored procedures (stored procedure), storage routines (store routine), storage function differences
The MySQL storage routines actually contain stored procedures and stored functions, which are collectively called storage routines.
Where the stored procedure completes the select by fetching the record or inserting the record or updating the record or deleting the record.

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

a result.

Creating stored procedures, stored functions

Create procedure stored procedure name (parameters)
Stored Procedure Body

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

SET foreign_key_checks=0
------------------------------
--Table structure for db_news
-- ----------------------------
DROP TABLE IF EXISTS ' db_news ';
CREATE TABLE ' db_news ' (
  ' id ' int (a) not NULL auto_increment,
  ' title ' varchar ' null,   ' editor ' varchar () default NULL,
  ' origin ' varchar () default NULL,
  ' tags ' varchar (200) Default NULL,
  ' content ' text not NULL,
  ' hits ' int (a) default ' 0 ',
  ' IP ' varchar not N ull,
  ' time ' int (a) not NULL,
  PRIMARY key  (' id ')
) Engine=myisam auto_increment=2 DEFAULT Charset=latin1;

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

' detail. ', ', ' 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 screenshot above we see how to invoke our stored procedure

Calling stored procedures
Call stored procedure name ();

Above we created a simple stored procedure, of course, our application can not use such a simple stored procedure

, what we need is the ability to pass parameters to the stored procedure to return to the desired result data. Let's understand the next deposit

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

Input parameters are still used to return information or both, and only input parameters are supported for the stored functions PHP tutorial.
When you declare a parameter, you specify that you want to use the keyword in,out,inout.
which
In: For input parameters
Out: Used to return parameters
INOUT: Used to pass parameter values to a stored procedure, or return if the value changes
In addition, we need to add @ to the parameter name when we call the stored procedure to ensure that the argument is out,inout.

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

Return the corresponding information title to 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 number that we want to pass in, title is the corresponding information number heading to be returned, because it is out so call again

Before adding @ to the procedure. (see the following example)
drop procedure if exists getnum;
CREATE PROCEDURE getnum (out num int)
SELECT INTO Num;

Call Getnum (@num);
Select @num;
Screenshots:

PHP storage routines, stored procedure Advanced Learning II
Then the previous article, and then invoke the return value of the previous example we can use the following methods to invoke the stored procedure:
SELECT @ return parameters;
We now use this method to invoke the select_news we created above, to see

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

Our database number corresponds to the data of the A, which is because our stored procedure body does not return the argument to the title

return value.

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

To introduce the learning object we are going to talk about this time.

Drop function if exists count_news;
Delimiter//
Create function count_news (hits int) returns INT
Comment ' The number of messages that exceed this number of clicks based on the number of clicks passed '
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 above storage function we use the declaration of the stored routine variable, set its value, Judgment statement, storage function Special

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

Unlike stored procedure return parameters, a stored function does not define a direct declaration of which variable is a return parameter, but only

Use returns to declare the data type that the return parameter belongs to, and return the argument by using return in the function body

Returns the form of the data variable to be represented. It should be noted that:
The stored function only supports input parameters, and the input parameters are not previously in or inout.

To return to the above stored function, let's explain the code meaning:
Drop function if exists count_news;
If a stored function count_news is present, the stored function is deleted, and as stored procedures we can use the following statement

To delete a stored procedure or function
Drop procedure|function [if exists] stored procedure name | Stores the name of the function;
Delimiter//
Use delimiter to change MySQL by default using semicolons (;) ends the current statement with a new ending symbol, using the

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

Type
Comment ' The number of messages that exceed this number of clicks based on the number of clicks passed '
Use comment to describe the functionality information for the stored procedure or stored function. Use the Format comment ' description string

'
begin
   use begin to qualify a processing module
declare total_news int;
DECLARE variable format declare variable name data type [default defaults]
   declare hits_num int default 0;
DECLARE variable format declare variable name data type [default defaults]
   if hits>=0 then
      set hit S_num=hits;
      Assign value to variable using set
Select COUNT (id) into total_news from Db_news where db_news.hits>h Its_num;
SQL statements can be assigned 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 semicolon (;)
   return total_news;
   use return returns the value of the stored function to be returned, noting that the value can only be one value.
End;
//
Delimiter
synthesizes Our conceptual knowledge Above:
1.  The parameters of the stored function are different from the stored procedure.
2.  How to change the default closing symbol for MySQL in a storage routine. The
3.  How to describe functionality information in a storage routine. The
4.  declares, sets the variable. The
5. begin-end statement block.
6.  conditions to determine if If-else if-else-end if or if-else-end if or if-end if