Shulou(Shulou.com)06/01 Report--

Mysql stored procedure programming

Templates written by stored procedures:

Create PROCEDURE PROCEDUREName (IN para mint, …)

Begin

Declare varname type

Statement

End

The above is the template for writing the stored procedure.

Where type can be any type in the table, such as: varchar,bigint,int,decimal,longtext and so on.

The declaration of the cursor is:

Declare cursorName cursor from select statement.

Declare continue handler for not found set varName = 1

When using varName, you need to declare that if the cursor has no data, a varName assignment of 1 indicates that there is no value.

Open cursorName; means to open the cursor.

CLOSE cursorName; means to close the cursor.

FETCH cursorName into varlist; means to fetch a value from a cursor.

If conditional statement:

1. Kind of situation

If conditional then

Statements executed when conditions are met

End if

2. Kind of situation

If conditional then

Statements that meet the conditions of execution

Else

Statements executed that do not meet the conditions

End if

Loop statement:

Out_loop:LOOP

END LOOP out_loop

This is the LOOP loop, where out_loop represents the circular tag of LOOP, similar to the assembled tag.

The statement that ends the LOOP loop is:

LEAVE out_loop;out_loop represents the label of LOOPd

Example:

Create PROCEDURE selectExtratUnit ()

BEGIN

DECLARE id BIGINT

DECLARE na LONGTEXT

DECLARE linkName LONGTEXT

DECLARE notfound INT

DECLARE cursor_avgScore CURSOR for select summary_id,text2 from edoc_summary_extend_send_sj where text2 is not null and text2''

DECLARE CONTINUE HANDLER FOR NOT FOUND SET notfound = 1

OPEN cursor_avgScore

Out_loop:LOOP

If notfound = 1 THEN

LEAVE out_loop

End if

FETCH cursor_avgScore into id,na

Select group_concat (org_name) into linkName from trans_org_sj where org_id in (

SELECT SUBSTRING_INDEX (SUBSTRING_INDEX (na,' |', help_topic_id+1),'|',-1) AS num FROM mysql.help_topic WHERE help_topic_id < LENGTH (na)-LENGTH (REPLACE (na, "|",') + 1)

INSERT into extrat_table (id,orgname) VALUES (id,linkName)

END LOOP out_loop

CLOSE cursor_avgScore

End

Note:

When creating a stored procedure, the choice of the client is different, which will cause some errors in the stored procedure when the syntax is right. It is best to use the black window that comes with Navicat and mysql. If you use the black window that comes with mysql, you need to use the DELIMITER / / command when writing the stored procedure. After the execution is completed, after writing the stored procedure, use / / to indicate the end of the command.

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.