MySQL Stored procedure issues

Background

recently do search function, it is a headache. MySQL full-text index toss the light, and finally did not understand how. And then I took a look at the function and stored procedure programming. Originally was a database this course, but the teacher said after all is superficial, use up only to find each course is like an iceberg, know is just a corner .... Take it easy. This summarizes the problems encountered during the process of writing stored procedures.

Question 1: What is the variable with the @ symbol?

MySQL in front of the variable with the @ character, such as: @a, represents a global variable, a process to define the assignment, other processes can be used directly. Relative to ordinary variables, it is a local variable and can only be used in the process itself.

question 2: Only one result set can be returned in the procedure. What if I want to return multiple result sets? First of all, return a single result set:

 delimiter // create  procedure  ' Test ' (out str  varchar  (20   begin  select  bookname into  str  from  books limit 1   end  // delimiter;  

A single result set can be returned to a variable by a select INTO, but it is not feasible for multiple result sets. Only cursors can be used .

With cursors, this is more of a problem ....

the definition of a cursor :

CREATE PROCEDURE' Test ' ()BEGIN--variables that receive cursor data need to be defined, type cursor content type is consistent  DECLAREACHAR(Ten); --Traverse data End Flag  DECLAREEndingINT DEFAULTFALSE; --Cursors  DECLARECurCURSOR  for SELECTI fromTableName; --binding the end flag to a cursor  DECLARE CONTINUEHANDLER for  notFOUNDSETEnding=TRUE; --Open Cursor  OPENcur; --using cursor Code   --Close Cursors   CLOSEcur;END

It is important to note that the declare order is immutable, that is, other variable declarations are placed at the top, and the declarations of cursors and end bindings are placed behind, and no other type statements are allowed before the declaration . Otherwise there will be a symbolic error .

Cursors Use method of:

REPEAT -- The cursor stores a column    FETCH  into A;     -- Cursors Store multiple columns      FETCH  into a,b; UNTIL endingEND REPEAT;

About the end flag:

The end flag is a fetch into and then a check to see if there is any content in the cursor, and if nothing is done SET ending = TRUE; (defined in the code) and then jump out of the loop by ending's judgment.

　　

　　

MySQL Stored procedure issues