Php storage routine, stored procedure Getting Started Tutorial

Php tutorials

What is a storage routine?
A storage routine is a set of SQL statements stored on the database tutorial server. You can run these SQL statement commands by calling a specified name in the query.
Why use stored procedures?
We all know that there are two types of applications: web-based and desktop-based. They interact with databases to access data. Assume that an application contains these two types of SQL statements. To modify one of the query SQL statements, you may need to modify the corresponding query SQL statements, this problem occurs when our applications are very large and complex, and it is not easy to maintain! In addition, placing SQL query statements in our web programs or desktops can be easily damaged by SQL injection. The storage routine can help us solve these problems.
Differences between stored procedures, storage routines, and storage functions
Mysql storage routines actually contain stored procedures and storage functions, which are collectively referred to as storage routines.
The stored procedure is mainly used to obtain or insert records or update or delete records, that is, to complete select insert delete update and so on. The storage function only performs the query. It can accept input parameters and return a result.

Create stored procedures and functions

Create procedure stored procedure name (parameter)
Stored Procedure body

Create function storage function name (parameter)
Storage function body
Assume that the table db_info in the database omcmc has the following structure:
SET FOREIGN_KEY_CHECKS = 0;
------------------------------
-- Table structure for db_news
------------------------------
Drop table if exists 'DB _ news ';
Create table 'DB _ News '(
'Id' int (10) not null auto_increment,
'Title' varchar (200) not null,
'Editor' varchar (20) default NULL,
'Origin' varchar (20) default NULL,
'Tags' varchar (200) default NULL,
'Content' text not null,
'Hits 'Int (10) default '0 ',
'IP' varchar (15) not null,
'Time' int (10) not null,
Primary key ('id ')
) ENGINE = MyISAM AUTO_INCREMENT = 2 default charset = latin1;

------------------------------
-- Records
------------------------------
Insert into 'DB _ new' VALUES ('1', First app', 'xqbar. Com', 'xqbar. Com', 'ommc ', and 'detail .. ', '123', '192. 0.0.1', '123 ');

Use the above table to create a simple stored procedure
Create procedure select_news ()
Select title, hits from db_news;

Run and call on the terminal
 
From the screenshot above, we can see how to call our stored procedure.

Call stored procedure
Call stored procedure name ();

We have created a simple stored procedure. Of course, our application cannot use such a simple stored procedure. What we need is to pass parameters to the stored procedure, to return the expected result data to us. The following describes the parameters of the stored procedure.
Stored Procedure parameters
Generally, stored procedures accept user parameters and return results to the caller.
The mysql tutorial specifies that each parameter of a stored procedure must be declared with its parameter name, data type, and whether the parameter is an input parameter or is used to return information or both, for the storage function php, only the input parameters are supported.
When declaring a parameter, the keyword IN, OUT, and INOUT must be used.
Where:
IN: used to input parameters.
OUT: used to return parameters.
INOUT: used to pass a parameter value to a stored procedure. If this value changes, the system returns
In addition, it is stipulated that when we call the stored procedure for the parameter named "OUT", we need to add @ Before the parameter name to ensure that the parameter is called outside the process, next we modify the above stored procedure to pass the information number to the stored procedure select_news, and return 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 and call on the terminal
 
Note the Call Format
Call select_news (1, @ title );
Among them, 1 is the information number we want to pass in, and title is the title of the corresponding information number to be returned. Because it is OUT, we need to add @ in front of it for calling outside the process. (See the following example)
Drop procedure if exists getNum;
Create procedure getNum (OUT num int)
Select 100 into num;

Call getNum (@ num );
Select @ num;
Screenshot:
 

Php storage routine and stored procedure (2)
Next, in the previous article, we can call the stored procedure using the following method when calling the stored procedure with a returned value:
Select @ return parameter;
Now we use this method to call the select_news we created above. Let's take a look.
 
Some people may wonder why the returned title is null, instead of returning the data First App corresponding to our database number like call select_news (1, @ title, this is because our stored procedure body does not return the parameter return value to the title.

Next let's take a look at the storage functions
We still use the db_news table as an example. However, in this storage function, we add other knowledge about storage routines to introduce the learning objects we will talk about this time.

Drop function if exists count_news;
Delimiter //
Create function count_news (hits int) returns int
Comment 'Count the number of incoming clicks that exceed this click'
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 stored functions, we use the declaration of storing routine variables, setting their values, judging statements, storage function-specific return value types, and how to describe storage routines (comment ).

Unlike the stored procedure response parameters, the stored function does not directly declare which variable is the response parameter during definition, but uses returns to declare the data type of the response parameter, return parameters are expressed in the form of return data variables to be returned in the function body. Note that:
The storage function only supports input parameters, and there is no IN or INOUT before the input parameters.

Return to the above storage function to explain the meaning of the code:
Drop function if exists count_news;
If the stored function count_news exists, delete the stored function. Like the stored procedure, you can use the following statement to delete the stored procedure or function.
Drop procedure | function [if exists] Stored procedure name | stored function name;
Delimiter //
Using delimiter to change mysql uses semicolon (;) by default to end the current statement with a new ending symbol. After using delimiter, the original default semicolon (;) concluding sentence symbol does not work anymore, until the declaration Terminator is restored.
Create function count_news (hits int) returns int
Note that the stored function parameters only include input parameters and are not declared IN or INOUT before. To return a function, you only need to declare the data type to be returned.
Comment 'Count the number of incoming clicks that exceed this click'
Use comment to describe the function information of the stored procedure or function. Description string in the format of comment'
Begin
Use begin to limit a processing module
Declare total_news int;
Declare the data type of the declare variable name in the variable format [default value]
Declare hits_num int default 0;
Declare the data type of the declare variable name in the variable format [default value]
If hits> = 0 then
Set hits_num = hits;
Assign values to variables using set
Select count (id) into total_news from db_news where db_news.hits> hits_num;
You can use into to assign values to variables in SQL statements.
Else
Set total_news = 0;
End if;
The above is a judgment statement. Note that the format is if-else-end if or if-else-end if.
Note the semicolon (;)
Return total_news;
Use return to return the value to be returned by the stored function. Note that this value can only be one value.
End;
//
Delimiter;
Take a look at our above concepts:
1. What are the differences between the parameters of the stored function and the stored procedure.
2. How to change the default ending symbol of mysql in the storage routine.
3. How to describe the function information in the storage routine.
4. Declare and set variables.
5. begin-end statement block.
6. Conditional judgment if-else-end if, if-else-end if, or if-end if