SQL> Desc ng_store_sales
The name is empty? Type
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
BUYER_UID VARCHAR2 (6)
STORE_NO NUMBER (5)
SALE_AMOUNT NUMBER
SALE_AMOUNT_BUYER NUMBER
The MONTH NUMBER (2)
The YEAR NUMBER (4)

The cursor is as follows:
Real tsales;
String tbuyer;


Declare buy cursor for
The select buyer_uid, sum (sale_amount)
The from ng_store_sales
Where year=: lsyear and month=: lsmonth
Group by buyer_uid
;

The open buy;
The do while the sqlca. Sqlcode=0;
The fetch buy into: tbuyer, : tsales;
The update ng_store_sales set SALE_AMOUNT_BUYER=: tsales
Where buyer_uid=: tbuyer and year=: lsyear and month=: lsmonth;
The fetch buy into: tbuyer, : tsales;
Loop;
The close buy;

commit;

By buyer_uid collect ng_store_sales exterior-interior sale_amount field data, update sale_amount_buyer field

SQL> The select buyer_uid, sum (sale_amount)
2 the from ng_store_sales group by buyer_uid;

BUYER_ SUM (SALE_AMOUNT)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
F1 1570627.17
F2 3445516.92
F3 763568.04
F4 715882.3
F5 5093196.31

After the cursor execution, and the results are as the following:

Select distinct buyer_uid, sale_amount_buyer from ng_store_sales;

BUYER_ SALE_AMOUNT_BUYER
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
F1 1570627.13
F2 3445517
F3 0
F4 715882.313
F5 0


Why the data update only half?

CodePudding user response:

Year=: lsyear and month=: lsmonth
Update and year and the month, the last query, and without the two conditions, the results may be different, of course

CodePudding user response:

1, it is recommended that: you this completely without cursor, cursor to realize such a function is very slow, you can use an update statement to update
2, the problem: you this cursor execution SQL problem may come up completely, open cursor, sqlcode 0, but can be fine when you fetch a sqlcode is not zero, of course you update statement may not success!
The open buy;
The do while the sqlca. Sqlcode=0;
The fetch buy into: tbuyer, : tsales;
If the sqlca. Sqlcode & lt;> 0 then
The rollback using sqlca;
MessageBox (" prompt ", "failure", stopSign!)
Return
End the if
The update ng_store_sales set SALE_AMOUNT_BUYER=: tsales
Where buyer_uid=: tbuyer and year=: lsyear and month=: lsmonth;
If the sqlca. Sqlcode & lt;> 0 then
The rollback using sqlca;
MessageBox (" prompt ", "failure", stopSign!)
Return
End the if
The fetch buy into: tbuyer, : tsales;
Loop;
The close buy;

CodePudding user response:

refer to the second floor new4everlau response:

1, suggestion: you this completely without cursor, cursor to realize such a function is very slow, you can use an update statement to update
2, the problem: you this cursor execution SQL problem may come up completely, open cursor, sqlcode 0, but can be fine when you fetch a sqlcode is not zero, of course you update statement may not success!
The open buy;
The do while the sqlca. Sqlcode=0;
The fetch buy into...

For your first suggestion is,
For your second, seems to be wrong

CodePudding user response:

reference 1st floor xys_777 response:

year=: lsyear and month=: lsmonth
Update and year and the month, the last query, and without the two conditions, the result may be different of course

Query is not to add the two conditions, because the list is a month of data, so the query didn't add, has nothing to do with this, however, still thank you

CodePudding user response:

Mention it!
1, you select add, update also added, are all the same conditions, the update statement is absolutely no problem
2, for the second, as to why judgment, under the thinking

CodePudding user response:

reference 5 floor new4everlau reply:

welcome!
1, you select add, update also added, are all the same conditions, the update statement is absolutely no problem
2, for the second, as to why judgment, thinking?

If it is complicated calculations update, update a statement may not, or to use the trigger?

For the second, also can perform originally, combined with the judgment, you don't perform, forgot to say, the database is oracle 8 I

CodePudding user response:

According to your question, is not a problem for all;
If add a judge cannot perform declarative statement has a problem, if no problem, at least also show different ORACLE and MSSQL

CodePudding user response:

Found the reason, thanks for your enthusiasm
There are two other place upside down
The do while...
The fetch...