Php storage routine and stored procedure advanced learning

What is a storage routine? The storage routine is a set of SQL statements stored in the database tutorial server. you can call a specified name in the query to execute these SQL statement commands. why should you use the stored procedure? We all know that there are two types of applications: web-based, and web-based... 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, it is easy to place SQL query statements in our web programs or desktops, which may be 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 stored functions, which are collectively referred to as storage routines. the stored procedures are mainly completed in obtaining or inserting records or updating or deleting records, that is, the select insert delete update and so on are completed, while the storage function only performs the query. the input parameter is acceptable and a result is returned.

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 db_info table structure in the omcmc database 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');

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.

Call the 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, in order to return the results data we need, the following describes the parameters of the stored procedure.

Stored Procedure parameters:

The stored procedure usually accepts the user's parameters and returns the results to the calling User. the mysql tutorial requires that each parameter of the stored procedure be declared with its parameter name, the data type and whether the parameter is an input parameter or is used to return information or both. for the stored function php Tutorial, 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, it is returned, INOUT parameters when calling the stored procedure, 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 to be passed in, and title is the title of the corresponding information number to be returned. because it is OUT, you must add @ in front of it for calling outside the process. See the following code:

Drop procedure if exists getNum;

Create procedure getNum (OUT num int)

Select 100 into num;

// Open source code phprm.com

Call getNum (@ num );

Select @ num;

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;

We now use this method to call the select_news we created above to see why the returned title is null, instead of calling select_news (1, @ title) the First App of the data corresponding to our database number is returned, 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 represented in the form of return data variables to be returned in the function body.

It should be noted that the storage function only supports input parameters, and there is no IN or INOUT before the input parameters. the above storage function is returned. 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 //

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 end of the declaration is restored.

Create function count_news (hits int) returns int

Note that only the input parameters of the stored function are not declared as IN or INOUT. only the data type to be returned needs to be declared.

Comment: calculates the number of clicks that exceed this number based on the number of incoming clicks. comment is used to describe the function information of the stored procedure or function. the format is comment to describe the string.

Begin: Use begin to limit a processing module declare total_news int; // declare the variable format declare variable name data type [default value] declare hits_num int default 0; // declare the variable format declare variable name data type [default value] if hits> = 0 then set hits_num = hits; // assign a select count (id) value to the variable using set) into total_news from db_news where db_news.hits> hits_num; // the SQL statement can be used to assign else set total_news = 0; end if to the variable using into; // 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