SQL Server and MySQL cursor learning

A SQL Server cursor uses

The use of the/*** cursor speaks to the advantages of this multi-cursor, and now we are personally unveiling the mysterious veil of the cursor. The order in which cursors are used: reputation cursors, open cursors, read data, close cursors, delete cursors.

1.3.1 declaring the cursor's simplest cursor declaration: DECLARE < cursor name >cursor for<select statement >;

Where the SELECT statement can be a simple query, or it can be a complex query with successive queries and nested query examples:

[Table 2 Addsalary for example] Declare mycursor Cursor FOR SELECT * FROM addsalary so I declare a cursor to the table addsalary MyCursor

Advanced Notes DECLARE < cursor names > [insensitive] [SCROLL] cursorfor<select statements >

Here I say a downstream standard [insensitive] and [SCROLL] insensitive indicate that MS SQL SERVER will store the data records selected by the cursor definition in a temporary table (built under the tempdb database).

Read operations on the cursor are answered by the temporary table.

Therefore, the modification of the base table does not affect the data that the cursor extracts, that is, the cursor does not change with the contents of the underlying table, nor does it update the base table with the cursor. If the reserved word is not used, updates and deletions to the base table are reflected in the cursor. It should also be noted that the cursor will automatically set the INSENSITIVE option when the following conditions occur.

A. Use the distinct, GROUP by, and having UNION statements in the SELECT statement;

B. Using outer JOIN;

C. Any of the selected tables are not indexed;

D. Treat the real value as the selected column. SCROLL indicates that all extraction operations (such as first, last, PRIOR, NEXT, RELATIVE, ABSOLUTE) are available. If the reserved word is not used, then only the next fetch operation is possible. Thus, SCROLL greatly increases the flexibility of extracting data, and can read any row of data records in the result set without having to close and re-open the cursor.

1.3.2 Opening the cursor is very simple, we will open the cursor we declared just now MyCursor open mycursor

1.3.3 reading data fetch [NEXT | PRIOR | First | Last] The from {cursor name | @ cursor variable name} [into @ variable name [, ...]] parameter description: Next takes down a row of data and puts the next line as the current row (increment). Because the row pointer is before the 1th row of the cursor after the cursor is opened, the first fetch next operation obtains the 1th row of data in the cursor set. Next is the default cursor extraction option.  into @ variable name [,...] The column data for the extraction operation is placed in the local variable. Each variable in the list is associated from left to right with the corresponding column in the cursor result set. The data type of each variable must match the data type of the corresponding result column or the implicit conversion supported by the result column data type. The number of variables must be the same as the number of columns in the cursor selection list. Now let's get the data out of the MyCursor cursor! When the cursor is opened, the row pointer will point to the 1th row of the cursor set, and if you want to read the 1th row of data in the cursor set, you must move the row pointer to the 1th row. For this example, you can read the 1th row of data using the following operations:

Eg:fetch next from MyCursor or Fetch first from mycursor so I take out the data in the cursor, but the light is not enough, we also need to assign the extracted data to the variable

--Declaration of 2 variables declare @O_ID NVARCHAR (20)

Declare @A_Salary float-pass the fetched value to the 2 variables just declared

Fetch next from MyCursor to @ o_id,@ a_salary

1.3.4 closing the cursor close mycursor

1.3.5 Delete cursor deallocate mycursor

1.3.6 Instance Training **/CREATE PROCEDURE pk_test as

--Declaration of 2 variables

declare @O_ID nvarchar (20)

Declare @A_Salary float-declares that the number of parameters in a cursor Mycursor,select statement must be the same as the variable name taken from the cursor

Declare mycursor cursor FOR select o_id,a_salary from Addsalary

--Open cursor

Open MyCursor

--extracting data from Grandperi to the 2 variables we just declared

FETCH NEXT from MyCursor to @O_ID, @A_Salary

--Determine the state of the cursor-

-0 FETCH statement succeeded

---1 FETCH statement failed or the row is not in the result set

---2 rows that are fetched do not exist

while (@ @fetch_status =0) begin

--Shows the value we take out each time we use a cursor

print ' cursor successfully fetched a piece of data '

Print @O_ID

Print @A_Salary

--Use a cursor to remove a record

FETCH NEXT from MyCursor to @O_ID, @A_Salary end-

-Close Cursors

Close MyCursor

--undo cursor

Deallocate mycursor

GO-

---------------------------------------------------------------

CREATE TABLE Testyoubiao (

ID int NOT NULL PRIMARY key,

Names nvarchar (50),

Address nvarchar (50)

)

Select top 0 * into Testyoubiao2 from Testyoubiao

---The Testyoubiao table has 8 records

Create PROCEDURE TESTPP

As

Begin

declare @names nvarchar (20)

declare @address nvarchar (50)

Declare mycursor cursor FOR select names,address from Testyoubiao

--Open cursor

Open MyCursor

--extracting data from Grandperi to the 2 variables we just declared

FETCH NEXT from MyCursor to @names, @address

--while (@ @fetch_status =0)

--begin

--Shows the value we take out each time we use a cursor

print ' 1 '

Print @names

Print @address

--Use a cursor to remove a record

--fetch mycursor into @names, @address

Insert into Testyoubiao2 (names,address) VALUES (@names, @address)

--end

--Close cursor

Close MyCursor

--undo cursor

DEALLOCATE MyCursor End

EXEC TESTPP

--The result of this execution is that the Testyoubiao2 table has only one record, that is, no loops, just the first one.

--fetch next from MyCursor to @names, @address "Next from" This can not write the result is the same

--------------------------------------------------------------------

Create PROCEDURE TESTPP

As Begin

declare @names nvarchar (20)

declare @address nvarchar (50)

Declare mycursor cursor FOR select names,address from Testyoubiao

--Open cursor

Open MyCursor

--extracting data from Grandperi to the 2 variables we just declared

FETCH NEXT from MyCursor to @names, @address

--INSERT INTO TESTYOUBIAO2 (names,address) VALUES (@names, @address) will add one piece of data to the last

while (@ @fetch_status =0)

Begin

--Shows the value we take out each time we use a cursor

print ' 1 '

Print @names

Print @address

--Use a cursor to remove a record

FETCH NEXT from MyCursor to @names, @address

Insert into Testyoubiao2 (names,address) VALUES (@names, @address)

End

--Close cursor

Close MyCursor

--undo cursor

DEALLOCATE MyCursor End

EXEC TESTPP

SELECT * FROM Testyoubiao2

TRUNCATE TABLE Testyoubiao2

Drop PROCEDURE TESTPP

#----------------------------------------------------------------------

Two MySQL cursors use

DELIMITER $$

Create PROCEDURE testp16 (in $id int)

BEGIN DECLARE _uname varchar (20);

DECLARE _uaddress VARCHAR (20);

DECLARE d_cursor cursor FOR select uname,uaddress from Testa where id= $id;

OPEN D_cursor; FETCH NEXT from D_cursor to _uname, _uaddress;

CLOSE D_cursor;

Insert into TESTD (uname,uaddress) values (_uname,_uaddress);

End

-----------------------------------------------------------

DELIMITER $$

Create PROCEDURE testp17 ()

BEGIN DECLARE _uname varchar (20);

DECLARE _uaddress VARCHAR (20);

declare couts int;

declare i int default 0;

DECLARE d_cursor cursor FOR select uname,uaddress from Testa;

Set couts = (select count (id) from Testa);

OPEN D_cursor;

-FETCH NEXT from D_cursor to _uname, _uaddress;

-INSERT INTO TESTD (uname,uaddress) values (_uname,_uaddress); This place is right with these two sentences, but there are 1329 error hints.

While i<couts do--1329-no Data-zero rows fetched, selected, or processed out of range

FETCH NEXT from D_cursor to _uname, _uaddress;

Insert into TESTD (uname,uaddress) values (_uname,_uaddress);

Set i=1+i;

End while;

-FETCH NEXT from D_cursor to _uname, _uaddress;

--INSERT INTO TESTD (uname,uaddress) VALUES (_uname,_uaddress), this place is right with these two sentences but there are 1329 error hints CLOSE d_cursor; Select I; End

SELECT * FROM Testa

Call TESTP17 ();--1329-no data-zero rows fetched, selected, or processed out of range

Call TESTP18 ()

SELECT * FROM TESTD

TRUNCATE Table TESTD

-----------------------------------------

/* For the following understanding, define conditions and handlers to define conditions and handlers that are pre-defined problems that you may encounter during program execution. You can also define ways to resolve these problems in your handlers. This way you can anticipate potential problems in advance and propose solutions. This will enhance the ability of the program to handle problems, and prevent the program from stopping abnormally. In MySQL, the conditions and handlers are defined by the DECLARE keyword.

This section will explain in detail how to define conditions and handlers.

1. Define conditions MySQL can use the DECLARE keyword to define conditions.

The basic syntax is as follows:

DECLARE condition_name condition for condition_value condition_value:

SQLSTATE [VALUE] sqlstate_value | Mysql_error_code

Where the Condition_name parameter indicates the name of the condition;

The Condition_value parameter represents the type of condition, and both the Sqlstate_value parameter and the Mysql_error_code parameter can represent MySQL errors.

For example, in error 1146 (42S02), the Sqlstate_value value is the 42s02,mysql_error_code value is 1146.

"Example 14-6"

The following defines "error 1146 (42S02)", which is named Can_not_find. It can be defined in two different ways, with the following code:

Method One: Use Sqlstate_value DECLARE can_not_find CONDITION for SQLSTATE ' 42S02 ';

Method Two: Use Mysql_error_code DECLARE can_not_find CONDITION for 1146; 2. Defining handlers

You can use the DECLARE keyword to define a handler in MySQL.

The basic syntax is as follows: DECLARE Handler_type handler for condition_value[,...] sp_statement handler_type:

CONTINUE | EXIT | UNDO Condition_value:

SQLSTATE [VALUE] sqlstate_value | Condition_name | sqlwarning | Not FOUND | SQLEXCEPTION | Mysql_error_code

Where the Handler_type parameter indicates how the error is handled, and the parameter has 3 values. These 3 values are continue, exit, and Undo, respectively. Continue indicates that an error has not been processed and continues to execute downward;

Exit indicates that an error has been encountered and quits immediately after it has encountered an error, which is not supported temporarily in MySQL.

Note: Typically, errors encountered during execution should immediately stop executing the following statement and recall the previous action.

However, the undo operation is not currently supported in MySQL. Therefore, it is best to perform an exit operation if an error is encountered. If the error type can be predicted beforehand and processed accordingly, the continue operation can be performed. The Condition_value parameter indicates the type of error, which has 6 values. Sqlstate_value and Mysql_error_code are the same meaning as in conditional definitions. Condition_name is the condition name defined by declare. SQLWarning represents all sqlstate_value values that begin with 01. Not found represents all sqlstate_value values that begin with 02. SqlException represents all sqlstate_value values that are not captured by sqlwarning or not found. Sp_statement represents some execution statements for stored procedures or functions.

"Example 14-7" below are several ways to define handlers.

The code is as follows:

Method One: Capture Sqlstate_value DECLARE CONTINUE HANDLER for SQLSTATE ' 42s02 ' SET @info = ' CAN not FIND ';

Method Two: Capture Mysql_error_code DECLARE CONTINUE HANDLER for 1146 SET @info = ' CAN not FIND ';

Method Three: Define the condition first, then call DECLARE can_not_find CONDITION for 1146;

DECLARE CONTINUE HANDLER for can_not_find SET @info = ' can not find ';

Method Four: Use sqlwarning DECLARE EXIT HANDLER for sqlwarning SET @info = ' ERROR ';

Method Five: Use not FOUND DECLARE EXIT HANDLER for not FOUND SET @info = ' CAN not FIND ';

Method Six: Use SQLEXCEPTION DECLARE EXIT HANDLER for SQLEXCEPTION SET @info = ' ERROR ';

The above code is 6 ways to define handlers.

The first method is to capture the Sqlstate_value value. If you encounter a Sqlstate_value value of 42S02, perform a continue operation and output "CAN not FIND" information.

The second method is to capture the Mysql_error_code value. If a mysql_error_code value of 1146 is encountered, the continue operation is performed and the "CAN not FIND" message is output.

The third method is to define the condition before calling the condition. The can_not_find condition is defined first, and the continue operation is performed with a 1146 error.

The fourth method is to use SQLWarning. SQLWarning captures all Sqlstate_value values starting with 01, then executes the exit operation and outputs the "ERROR" information.

The fifth method is to use not FOUND. Not found captures all sqlstate_value values starting with 02, then performs the exit operation and outputs "CAN not FIND" information.

The sixth method is to use SqlException. SqlException captures all sqlstate_value values that are not captured by the sqlwarning or not found, and then performs the exit operation and outputs the "ERROR" information.

*/ -----------------------------------

DELIMITER $$

CREATE PROCEDURE testp18 ()

BEGIN

--Traversal data End flag

DECLARE done INT DEFAULT FALSE;

--need to define variables that receive cursor data

DECLARE _uname varchar (20);

DECLARE _uaddress VARCHAR (20);

--Cursors

DECLARE d_cursor cursor FOR select uname,uaddress from Testa;

/* Traverse data End Flag

--declare done INT DEFAULT FALSE;

--#放到这里出现这个错误 [ERR] 1337-variable or condition declaration after cursor or handler declaration depending on the error message, the cursor definition is required after the variable/condition,   HANDLER ago. */

--Bind the end flag to the cursor

DECLARE CONTINUE HANDLER for don't FOUND SET done = TRUE;

--Open cursor

OPEN D_cursor;

--fetch d_cursor into _uname, _uaddress;

--insert into TESTD (uname,uaddress) values (_uname,_uaddress); Here, add these two sentences without affecting the data behind it.

--Start cycle

Read_loop:loop

--Extract the data in the cursor, there is only one, and many words are the same;

FETCH next from D_cursor to _uname, _uaddress;--next from can be omitted

-At the end of the declaration

IF do Then

LEAVE Read_loop;

END IF;

--Here's what you want to do with the loop of events

Insert into TESTD (uname,uaddress) values (_uname,_uaddress);

END LOOP;

--FETCH d_cursor into _uname, _uaddress; If you add these two sentences, you'll add a piece of data that's the same as the final data.

--insert into TESTD (uname,uaddress) values (_uname,_uaddress);

--Close cursor

CLOSE D_cursor;

END

--TESTP17 () and TESTP18 () Two effects are the same-

----------------------------------------