Shulou(Shulou.com)05/31 Report--

This article mainly explains the "mysql stored procedure example analysis", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "mysql stored procedure example analysis" bar!

(1)。 Format

Format of MySQL stored procedure creation: CREATE PROCEDURE procedure name ([procedure parameters [,...]])

[features.] Process body

Here's an example:

Mysql > DELIMITER / /

Mysql > CREATE PROCEDURE proc1 (OUT s int)

-> BEGIN

-> SELECT COUNT (*) INTO s FROM user

-> END

-> / /

Mysql > DELIMITER

Note:

(1) what we need to pay attention to here are DELIMITER / / and DELIMITER; in two sentences, DELIMITER means a delimiter, because MySQL defaults to ";". If we do not declare a delimiter, the compiler will treat the stored procedure as a SQL statement, and the compilation process of the stored procedure will report an error, so the current segment delimiter must be declared with the DELIMITER keyword in advance, so that MySQL will set the " "as code in a stored procedure, the code will not be executed and the delimiter will be restored when it is used up.

(2) the stored procedure may have input, output, input and output parameters as needed, here is an output parameter s, the type is int, if there are multiple parameters separated by ",".

(3) the beginning and end of the process body are marked by BEGIN and END.

In this way, one of our MySQL stored procedures is completed, isn't it easy? It doesn't matter if you don't understand it. Next, we'll explain it in detail.

(2)。 Declare delimiter

In fact, with regard to the declaration separator, the above comments have been written very clearly, there is no need to say more, just a little note is: if you are using MySQL's Administrator management tool, you can directly create, no longer need to declare.

(3)。 Parameters.

The parameters of MySQL stored procedures are used in the definition of stored procedures. There are three parameter types, IN,OUT,INOUT, in the form of:

CREATE PROCEDURE ([[IN | OUT | INOUT] parameter name data class.])

IN input parameter: indicates that the value of this parameter must be specified when calling the stored procedure. The value that modifies this parameter in the stored procedure cannot be returned and is the default value.

OUT output parameter: this value can be changed inside the stored procedure and can be returned

INOUT input and output parameters: specified when called, and can be changed and returned

DELIMITER $$

DROP PROCEDURE IF EXISTS getUserInfo $$

CREATE PROCEDURE getUserInfo (in date_day datetime)

--

-- examples

-- the MYSQL stored procedure is named: getUserInfo

-- Parameter: date_day date format: 2008-03-08

--

BEGIN

Declare _ userName varchar (12);-- user name

Declare _ chinese int;-Chinese

Declare _ math int;-- Mathematics

Declare done int

-- define cursors

DECLARE rs_cursor CURSOR FOR SELECT username,chinese,math from userInfo where datediff (createDate, date_day) = 0

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;-- end syntax

-- get yesterday's date

If date_day is null then

Set date_day = date_add (now (), interval-1 day)

End if

Open rs_cursor

Cursor_loop:loop # fixed syntax. Cusor_loop is a random name.

FETCH rs_cursor into _ userName, _ chinese, _ math;-- fetch data

If done=1 then

Leave cursor_loop

End if

-- update the table

Update infoSum set total=_chinese+_math where UserName=_userName

End loop cursor_loop

Close rs_cursor

END$$

DELIMITER

The case I wrote:

1. The total number of visits to each piece of data

DELIMITER $$

CREATE PROCEDURE test.get_data_lunzheng ()

BEGIN

DECLARE loop_id INT

DECLARE done INT DEFAULT 0

DECLARE id_cursor CURSOR FOR SELECT id FROM test2.lunzheng

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1

OPEN id_cursor

Select_loop: LOOP

FETCH id_cursor INTO loop_id

IF done=1 THEN

LEAVE select_loop

END IF

SELECT info_id,COUNT (*) FROM (SELECT a.infoarchiidjournal a.id FROM liuhe.`pvlogs _ back_05_ bak`a

WHERE a.info_id=loop_id UNION ALL SELECT b.infoarchiidreb. ID FROM liuwenhe.`pvlogs _ back_08_ bak`b WHERE b.info_id=loop_id) c GROUP BY info_id into outfile'C:\ Users\ manet\ Desktop\ loop_id.txt'

END LOOP

CLOSE id_cursor

END $$

DELIMITER

Thank you for your reading, the above is the content of "mysql stored procedure example Analysis". After the study of this article, I believe you have a deeper understanding of the problem of mysql stored procedure example analysis, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.