PHP stored routines and stored procedures introductory tutorial

php tutorial stored routines, stored procedure introductory tutorial

What is a stored routine?
A stored routine is a set of sql statements stored in the database tutorial server, by calling a Specify the name to 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 to complete data access. Suppose there is an application that contains these two types. Now we want to modify one of the query SQL statements. Then we may have to modify the corresponding query SQL statements in them at the same time. Problems arise when our application is large and complex. This is not easy to maintain! In addition, placing SQL query statements in our web programs or desktops can easily be damaged by SQL injection. Stored routines can help us solve these problems.
The difference between stored procedures, stored routines, and stored functions
Mysql stored routines actually include stored procedures and stored functions, which are collectively called stored routines.
Among them, the stored procedure mainly completes the work of obtaining records, inserting records, updating records or deleting records, that is, completing the work of select insert delete update and so on. The stored function only completes the query work, accepts input parameters and returns a result.

Create stored procedures and stored functions

create procedure stored procedure name (parameters)
stored procedure body

create function stored function name (parameters)
storage Function body
Assume that there is now 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', '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 in terminal and call

from the screenshot above We see how to call our stored procedure

call stored procedure
call stored procedure name();

Above we created a simple stored procedure, of course our application does not Maybe using 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. Let’s learn about the parameters of the stored procedure.
Parameters of stored procedures
Usually stored procedures accept user parameters and return results to the calling user.
The mysql tutorial stipulates that for stored procedure parameters, each parameter must declare its parameter name, data type and whether the parameter is an input parameter, used to return information, or both. For stored functions, php only supports input parameters. .
When declaring parameters, it is required to use the keywords IN, OUT, and INOUT.
Among them:
IN: used for input parameters
OUT: used for return parameters
INOUT: used to pass parameter values to the stored procedure, and return if the value changes
In addition, for sound For parameters named 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 called outside the procedure. Next, we modify the above stored procedure to pass the information number to the stored procedure select_news, and return the corresponding information title to Let's 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 the terminal and call

Pay attention to the calling format
call select_news(1,@title);
where 1 is the information number we want to pass in, and title is the corresponding information number to be returned The title, since it is OUT, must be called outside the procedure by adding @ in front of it when calling again. (See the 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 returning 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 a parameter return value for title.

Let’s take a look at the storage function
Still taking the db_news table as an example, but we add some other knowledge about stored routines to this storage function to introduce what we are going to talk about this time learning object.

drop function if exists count_news;
delimiter //
create function count_news(hits int) returns int
comment 'Count the number of information exceeding this number of clicks based on the number of incoming clicks'
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 function we used the declaration of stored routine variables, setting their values, judgment statements, the unique return value type of the stored function and how to describe the stored routine ( comment).

The difference from the return parameters of stored procedures is that the stored function does not directly declare which variable is the return parameter when defining it, but only uses returns to declare the data type of the return parameter. The return parameter is in The function body uses the form return to return the data variable to be returned. What needs to be noted is:
The stored function only supports input parameters, and there is no IN or INOUT before the input parameters.

Return to the above stored function, let us 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 to delete the stored procedure or function
drop procedure|function [if exists] stored procedure name|stored function Name;
delimiter //
Use delimiter to change mysql's default use of semicolon (;) and use a new end symbol to end the current statement. After using delimiter, the original default semicolon (;) end 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 are only input parameters and no IN or INOUT is declared before. When returning, only the data type to be returned is declared.
comment 'According to the incoming The number of clicks counts the number of information exceeding this number of clicks'
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 a value to the variable
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, please note 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

PHP stored routines and stored procedures introductory tutorial_PHP tutorial