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

I. definition of cursors:

Create procedure p12 ()

Begin

Declare row_name varchar (20)

Declare row_num int

Declare myCursor cursor for select name,num from goods;// defines cursor myCursor

Open myCursor;// opens the cursor myCursor

Fetch myCursor into row_name,row_num;// uses the cursor myCursor to get the first line

Select row_name, row_num

Fetch myCursor into row_name,row_num;// uses the cursor myCursor to get the second row; each fetch cursor automatically moves downstream.

Select row_name, row_num

Close myCursor;// closes cursor myCursor

End

2. Cursor + repeat loop-- > implement traversal through lines:

Create procedure p13 ()

Begin

Declare row_gid int

Declare row_name varchar (20)

Declare row_num int

Declare row_count int

Declare i int default 0

Declare myCursor cursor for select gid,name,num from goods

Select count (1) into row_count from goods

Open myCursor

Repeat

Fetch myCursor into row_gid,row_name,row_num

Select row_gid,row_name,row_num

Set i=i+1

Until I > row_count end repeat

Close myCursor

End

3. Cursor + continue handler to traverse lines:

Continue handler when fetch triggers this handler, the following statements continue to execute.

So the select row_gid,row_name,row_num will be executed one more time.

This handler is commonly used.

Create procedure p15 ()

Begin

Declare row_gid int

Declare row_name varchar (20)

Declare row_num int

Declare you int default 1

Declare myCursor cursor for select gid,name,num from goods

Declare continue handler for NOT FOUND set you=0

Open myCursor

Repeat

Fetch myCursor into row_gid,row_name,row_num

Select row_gid,row_name,row_num

Until you=0 end repeat

Close myCursor

End

4. Cursor + exit handler to traverse lines:

Exit handler when fetch triggers this handler, the following statements are no longer executed.

So select row_gid,row_name,row_num; will no longer be executed.

This handler is not commonly used.

Create procedure p16 ()

Begin

Declare row_gid int

Declare row_name varchar (20)

Declare row_num int

Declare you int default 1

Declare myCursor cursor for select gid,name,num from goods

Declare exit handler for NOT FOUND set you=0

Open myCursor

Repeat

Fetch myCursor into row_gid,row_name,row_num

Select row_gid,row_name,row_num

Until you=0 end repeat

Close myCursor

End

5. Cursor + while to traverse lines:

Create procedure p15 ()

Begin

Declare row_gid int

Declare row_name varchar (20)

Declare row_num int

Declare over int default 1

Declare myCursor cursor for select gid,name,num from goods

Declare continue handler for NOT FOUND set over = 0

Open myCursor

Fetch myCursor into row_gid,row_name,row_num

While over do

Select row_gid,row_name,row_num

Fetch myCursor into row_gid,row_name,row_num

End while

Close myCursor

End

6. Cursor + loop to traverse lines:

-- loop and leave,iterate to implement the loop

-- the loop flag bit unconditional loop; leave is similar to the Java break statement, jumping out of the loop, that is, jumping out of begin end

Iterate is similar to java continue, ending this cycle and moving on to the next.

The advantage of loop is that it can end the cycle or jump out of the cycle according to the condition.

Create procedure p17 ()

Begin

Declare row_gid int

Declare row_name varchar (20)

Declare row_num int

Declare over int default 0

Declare myCursor cursor for select gid,name,num from goods

Declare continue handler for NOT FOUND set over=1

Open myCursor

Cursor_loop:loop

Fetch myCursor into row_gid,row_name,row_num

If over then

Leave cursor_loop

End if

Select row_gid,row_name,row_num

End loop cursor_loop

Close myCursor

End

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.