Home » SQL & PL/SQL » SQL & PL/SQL » Cursors
Cursors [message #235972] Tue, 08 May 2007 09:43 Go to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
In the script below the objective is to return the two unmatched amounts in tables transaction and tax_sub_trans. Can anyone tell me if my script will be executed successfully? Or is there anything I can to do the script for improvement?

SET SERVEROUTPUT ON
DECLARE
v_doc_no tax_transaction.doc_no%TYPE;
v_tax_trans_total tax_transaction.amount%TYPE;
v_sub_trans_total tax_sub_trans.amount%TYPE;

CURSOR doc_no_cursor is
SELECT distinct(doc_no)
from tax_transaction 
where enter_date = '07-MAY-2007';

CURSOR trans_total_cursor is
select amount
from tax_transaction;

CURSOR sub_trans_total_cursor is
select sum(amount)
from tax_sub_trans
where charge_type_no in (2,4);

BEGIN
OPEN trans_total_cursor;
LOOP
FETCH trans_total_cursor INTO v_tax_trans_total;
CLOSE trans_total_cursor;
OPEN sub_trans_total_cursor;
FETCH sub_trans_total_cursor INTO v_sub_trans_total;
EXIT WHEN v_tax_trans_total <> v_sub_trans_total;
DBMS_OUTPUT.PUT_LINE('The non-matching transaction is ' ||v_doc_no ||','||  v_tax_trans_total ||','|| v_sub_trans_total);
END LOOP;
CLOSE sub_trans_total_cursor;
END;
/
Re: Cursors [message #235973 is a reply to message #235972] Tue, 08 May 2007 09:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your PL/SQL block is wrong.

Regards
Michel
Re: Cursors [message #235974 is a reply to message #235973] Tue, 08 May 2007 09:53 Go to previous messageGo to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
OK. so tell me what's wrong.
Re: Cursors [message #235976 is a reply to message #235974] Tue, 08 May 2007 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tell us what you want to do.
...and your full Oracle version

Regards
Michel
Re: Cursors [message #235979 is a reply to message #235976] Tue, 08 May 2007 10:19 Go to previous messageGo to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
I did say what I what to do before the script. Oracle 10g
Re: Cursors [message #235984 is a reply to message #235979] Tue, 08 May 2007 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Full version means 4 figures like 10.2.0.3

Now explain in words I can understand.
You said:
Quote:
objective is to return the two unmatched amounts in tables transaction and tax_sub_trans

Why there is a SUM in your code?
Why there is a date in one and charge_type in another one?
Why there are 3 cursors and you only use 2?
What is a matched amount?

Do you think you clearly explain things.

Regards
Michel


Re: Cursors [message #236098 is a reply to message #235984] Wed, 09 May 2007 00:56 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You obviously did not run your script prior to asking here, or you would (most likely) have gotten a ORA-01001: invalid cursor error.
Why don't you test your script yourself to see if it does what it should, instead of asking us to tell you??
I can understand that you'd ask if there are improvements possible once you got it doing what it should do.
Previous Topic: rounding/ codums/ etc ahh!
Next Topic: count of commas
Goto Forum:
  


Current Time: Sat Dec 03 12:12:14 CST 2016

Total time taken to generate the page: 0.12512 seconds