Advanced learning of PHP stored routines and stored procedures

What are stored routines?
A stored routine is a set of sql statements stored in the database tutorial server and executed by calling a specified name in the query

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

Come to each other to complete the data access work. Suppose there is an application that contains these two, and now you want to modify one of them

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

This problem occurs when it is large and complex and difficult to maintain! In addition, put the sql query statement in our web program or desktop

Easy to be damaged by sql injection. Stored routines can help us solve these problems.
The difference between stored procedure, stored routine and stored function
Mysql stored routines actually include stored procedures and stored functions, which are collectively called stored routines.
The stored process is mainly completed in obtaining records or inserting records or updating records or deleting records, that is, completing select

insert delete update etc. work. The storage function only completes the query work, accepts input parameters and returns a

results.

Create stored procedures and stored functions

create procedure stored procedure name (parameter)
Stored procedure body

create function stores function name (parameters)
Store function body
Assume that there is a table db_info in the database omcmc. The table structure is as follows:

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_news` VALUES ('1', First App', 'xqbar.com', 'xqbar.com', 'omcmc',

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

Using the above table we create a simple stored procedure
create procedure select_news()
select title,hits from db_news;

Run in terminal and call

From the screenshot above we see how to call our stored procedure

Call stored procedure
call stored procedure name();

We created a simple stored procedure above. Of course, it is impossible for our application to use such a simple stored procedure

, what we need is to be able to pass parameters to the stored procedure to return the result data we need. Learn about depositing below

Parameters of the stored procedure.
Parameters of stored procedure
Usually stored procedures accept user parameters and return results to the calling user.
The mysql tutorial stipulates that each parameter of a stored procedure must declare its parameter name, data type and the parameter

Input parameters are still used to return information or both. For stored function php tutorial, only input parameters are supported.
When declaring parameters, it is required to use the keywords IN, OUT, and INOUT.
Among them:
IN: used to input parameters
OUT: used to return parameters
INOUT: used to pass parameter values to stored procedures, if the value changes, return
In addition, it is stipulated that for parameters declared as OUT and INOUT, when we call the stored procedure, we need to add @ in front of the parameter name to ensure that the parameters are

The number is called outside the procedure. Next we modify the above stored procedure to pass the information number to the stored procedure select_news and return

Reply to the corresponding information title for us to check.

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 in terminal and call

Pay attention to the calling format
call select_news(1,@title);
Among them, 1 is the information number we want to pass in, and title is the corresponding information number title to be returned. Since it is OUT, we call
again.
should be called outside the procedure by adding @ in front of it. (see example below)
drop procedure if exists getNum;
create procedure getNum(OUT num int)
select 100 into num;

call getNum(@num);
select @num;
Screenshot:

Advanced Learning of PHP Stored Routines and Stored Procedures Part 2
Continuing from the previous article, when calling the previous example with a return value, we can use the following method to call the stored procedure:
select @return parameter;
We now use this method to call the select_news we created above, let’s take a look

Some people may wonder why the title returned is null instead of

like call select_news(1,@title)

The data corresponding to our database number is First App. This is because our stored procedure body does not return the title parameter

Return value.

Let’s take a look at storage functions
Still taking the db_news table as an example, but we add some other knowledge about stored routines to this storage function

Knowledge will introduce the learning objects we are going to talk about this time.

drop function if exists count_news;
delimiter //
create function count_news(hits int) returns int
comment 'Based on the number of incoming clicks, count the number of messages exceeding this number'
begin
declare total_news int;
declare hits_num int default 0;
If hits>=0 then
set hits_num=hits;
Select count(id) – 只有到账速度的怎么办 else
set total_news=0;
end if;
Return total_news;
end;
//
delimiter ;

In the above stored function, we used the declaration of stored routine variables, setting their values, judgment statements, and stored function special features

How to describe some return value types and stored routines (comment).

The difference from the return parameters of stored procedures is that when defining a stored function, there is no need to directly declare which variable is the return parameter, but only

Use returns to declare the data type of the return parameter. The return parameter is returned by using return in the function body

Represented in the form of data variables. What needs to be noted is:

Stored functions only support input parameters, and there is no IN or INOUT before the input parameters.

Return to the above storage function, let’s 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, we can use the following statement

Delete stored procedures or functions

drop procedure|function [if exists] stored procedure name|stored function name;
delimiter //
Use delimiter to change mysql's default use of semicolon (;) to use a new end symbol to end the current statement, use
The original default semicolon (;) ending statement symbol after

delimiter no longer works until the statement ending symbol is restored.

create function count_news(hits int) returns int
Note that the parameters of the stored function only have input parameters and do not declare IN or INOUT before. When returning, you only need to declare the data class to be returned

Type

comment 'Based on the number of incoming clicks, count the number of messages exceeding this number'
Use comment to describe the functional information of the stored procedure or stored function. Use the format comment 'description string

begin
Use begin to limit a processing module
declare total_news int;
Declare variable format declare variable name data type [default default value]
declare hits_num int default 0;
Declare variable format declare variable name data type [default default value]
If hits>=0 then
set hits_num=hits;
Use set to assign values to variables
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;
The above is a judgment statement, please 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 to be returned by the stored function. Note that the value can only be one value.
end;
//
delimiter ;
To summarize our conceptual knowledge above:
1. What is the difference between the parameters of stored functions and stored procedures.
2. How to change the default end symbol of mysql in stored routines.
3. How to describe functional information in stored routines.
4. Declare and 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

Advanced learning of PHP stored routines and stored procedures_PHP tutorial