Problem description: There are two tables,
Table
Table B
Now, we want to get the o_saloary in Table A and the_salary in Table B, which is the current total salary and updated to Table.
You can use a view to connect tables.
Now let's take the SQL statement into consideration. The cursor is a good method.
Principle: The cursor extracts the corresponding dataset according to the specified requirements, and then processes data one by one.
1.1 cursor allows you to access the result set returned by SQL Server row by row. A major reason for using cursor is to convert the set operation into a single record processing method. After retrieving data from a database using SQL, the results are stored in a memory area, and the results are often a collection containing multiple records. The cursor mechanism allows you to access these records row by row in SQL Server and display and process these records as you wish. 1.2 advantages of a cursor the following advantages can be obtained from the cursor definition. These advantages make the cursor play an important role in practical application:
1) Allow the program to perform the same or different operations on each row in the row set returned by the SELECT statement, rather than the same operation on the entire row set.
2) provides the ability to delete and update rows in a table based on the cursor position.
3) cursors serve as a bridge between a collection-oriented database management system (RDBMS) and a row-oriented program design. These two processing methods are communicated through cursors. The use of 1.3 cursors demonstrates the advantages of this multi-game, and now we will unveil the secret of the cursors. The order in which the cursor is used: Name cursor, open cursor, read data, close cursor, delete cursor. 1.3.1 declare the simplest cursor Declaration: declare <cursor Name> cursor for <SELECT statement>. The SELECT statement can be a simple query, it can also be complicated Successive queries and nested queries. 1.3.2 It is very easy to open the cursor. We will open the cursor mycursor open mycursor we just declared.
1.3.3 read data fetch [next | prior | first | last] from {cursor name | @ cursor variable name} [into @ variable name [,…] ]
Parameter description: Next removes the data of a row and uses the next row as the current row (incrementing ). Since the row pointer is directed to the cursor before the cursor is opened, the first execution of the fetch next operation will obtain 1st rows of data in the cursor set. Next is the default cursor extraction option. Into @ variable name [,…] Put the extracted column data in a local variable.
The variables in the list are associated with the corresponding columns in the cursor result set from left to right.
The data type of each variable must match the data type of the corresponding result column or the implicit conversion supported by the data type of the result column. The number of variables must be the same as the number of columns in the cursor selection list.
1.3.4 close the cursor close mycursor 1.3.5 Delete the cursor deallocate mycursor
A specific example is provided:
Declare @ ID nvarchar (20) -- Define a variable to save the ID number
Declare @ a float -- define variables to save values
Declare mycursor cursor for select * From tb_c -- specify a cursor for the obtained Dataset
Open mycursor -- open the cursor
Fetch next from mycursor into @ ID, @ A -- start to capture the first data
While (@ fetch_status = 0) -- if there is always data in the dataset
Begin
Select tb_ B .name,(tb_ B .gz + @ A) from tb_ B where tb_ B .id = @ ID -- start to do what you want (what updates, delete)
Fetch next from mycursor into @ ID, @ A -- jump to the next data
End
Close mycursor -- close the cursor
Deallocate mycursor -- delete a cursor
