Home » SQL & PL/SQL » SQL & PL/SQL » CUSROR%FOUND
CUSROR%FOUND [message #357570] Wed, 05 November 2008 20:16 Go to next message
srihari1974
Messages: 12
Registered: November 2008
Location: atlanta
Junior Member
I got three table
x,y,z
x and z has got almost same feilds, y is like history table

i have to query x and y for matched records

1) if match found i have update table Y with data= sysdate
2) if not found i have to insert into Z

how can i achive this by using cursor
Re: CUSROR%FOUND [message #357571 is a reply to message #357570] Wed, 05 November 2008 21:14 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi,
Can you explain your requirement more clearly?
Re: CUSROR%FOUND [message #357573 is a reply to message #357571] Wed, 05 November 2008 21:34 Go to previous messageGo to next message
srihari1974
Messages: 12
Registered: November 2008
Location: atlanta
Junior Member
well i got three tables

1)transactions
2)today_trans
3)pending_trans

today_trans andpending_trans have almost same columns
except the p.k.

daily data i will load into today_trans
i will search today_trans with transactions
if i find a matching records
then i will update transactions with sysdate and match_ind = 'Y'

if matches are not found i have to put the remaining records
into pending_trans

suppose if i have 1000 records in my today_trans
and i found 800 matches.then, i will update 800 records in
transactions and remaing 200 unmatched records
i have to insert into pending tables
for next day transactions





Re: CUSROR%FOUND [message #357574 is a reply to message #357571] Wed, 05 November 2008 21:51 Go to previous messageGo to next message
srihari1974
Messages: 12
Registered: November 2008
Location: atlanta
Junior Member
Hi Lakshmi,
My store proc look something like this

PROCEDURE SEARCH_TRANS IS
TYPE MY_ARRAY IS TABLE OF ACH_PENDING%ROWTYPE
INDEX BY PLS_INTEGER;
V_DATA MYARRAY;
C1 REFCURSOR;

BEGIN
OPEN c1 for
select tt.* from today_trans tt, transactions t wheer tt.id = t.id;
IF sql%notfound THEN
begin
loop
fetch c1 bulk collect into v_data limit 10000;
insert into pending values v_Data(i);
exit when c1%notfound;
end loop;
exceptions when others then
-------

end;
ELSE
FOR cur1 IN (select * from today_trans)
LOOP
update transactions
set tt_ind = 'Y'
,tdate = sysdate;
where t.id = cur1.id;
exit when c1%notfound;
end loop;
exceptions when others then
-------

end;
end SEARCH_TRANS;

Re: CUSROR%FOUND [message #357596 is a reply to message #357570] Thu, 06 November 2008 00:26 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Didn't go to your script in detail.

But Some Point you might have to understand .

1. sql%notfound will work of the single transaction. It will return 'YES' if the previous statement returns not even a single row.

2. FOR ALL is needed fro Bulk inserting.

Anyway .. My Suggestion is for you to look for MERGE


Smile
Rajuvan.
Re: CUSROR%FOUND [message #357607 is a reply to message #357574] Thu, 06 November 2008 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Just an insert and an update are sufficient, you don't need all these loops.

Regards
Michel
Re: CUSROR%FOUND [message #357628 is a reply to message #357570] Thu, 06 November 2008 01:36 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:
Just an insert and an update are sufficient, you don't need all these loops



Doesn't MERGE a better solution in Oracle 10g rather than using an insert and an update ?

Smile
Rajuvan.

[Updated on: Thu, 06 November 2008 01:36]

Report message to a moderator

Re: CUSROR%FOUND [message #357657 is a reply to message #357628] Thu, 06 November 2008 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Insert and update are not in the same table so you can't use merge.

Regards
Michel
Re: CUSROR%FOUND [message #357672 is a reply to message #357570] Thu, 06 November 2008 03:53 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Oooops !!! My mistake

Sad
Rajuvan
Re: CUSROR%FOUND [message #357750 is a reply to message #357596] Thu, 06 November 2008 08:10 Go to previous messageGo to next message
srihari1974
Messages: 12
Registered: November 2008
Location: atlanta
Junior Member
I am sorry about the format micheal,
If i have to update i should have some where condition or it will update the whole table
Re: CUSROR%FOUND [message #357752 is a reply to message #357750] Thu, 06 November 2008 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you should have a where clause.

Regards
Michel
Re: CUSROR%FOUND [message #357755 is a reply to message #357596] Thu, 06 November 2008 08:18 Go to previous messageGo to next message
srihari1974
Messages: 12
Registered: November 2008
Location: atlanta
Junior Member
I am sorry about the format micheal,
if i dont use where condition the whole table gets updated

create or replace
PROCEDURE test AS
BEGIN
FOR rec in (select * from x) LOOP
LOOP UPDATE Y SET SDATE = SYSDATE
WHERE a = rec.a
AND b = rec.b;
IF SQL%FOUND THEN
DBMS_OUTPUT.put_line('Updated ' ||rec.a);
END IF;
IF SQL%NOTFOUND THEN
INSERT INTO z(a,b) VALUES (rec.a,rec.b);
DBMS_OUTPUT.put_line('Inserted ' || rec.a);
END IF;
END LOOP;
END;
Re: CUSROR%FOUND [message #357757 is a reply to message #357755] Thu, 06 November 2008 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read my previous post.
And read forum guide to know how to use code tags.

Regards
Michel
Re: CUSROR%FOUND [message #357761 is a reply to message #357757] Thu, 06 November 2008 08:27 Go to previous messageGo to next message
srihari1974
Messages: 12
Registered: November 2008
Location: atlanta
Junior Member
CREATE OR REPLACE PROCEDURE Ins_Upd
IS
BEGIN
FOR rec IN (SELECT *
FROM x)
LOOP
UPDATE y
SET yDate = SYSDATE
WHERE a = rec.a
AND b = rec.b;

IF SQL%FOUND THEN
dbms_Output.Put_Line('Updated '
||rec.a);
END IF;

IF SQL%NOTFOUND THEN
INSERT INTO z
(a,
b)
VALUES (rec.a,
rec.b);

dbms_Output.Put_Line('Inserted '
||rec.a);
END IF;
END LOOP;
END Ins_Upd;
Re: CUSROR%FOUND [message #357785 is a reply to message #357573] Thu, 06 November 2008 09:52 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
hi, I think even this works.......

SQL> desc today_tran;
Name Null? Type
-------------------- -------- ----------------------------
COLA NUMBER
COLB NUMBER(5)

SQL> desc tran;
Name Null? Type
----------------------------------------- -------- ------------
COL1 NOT NULL NUMBER
COL2 NUMBER(5)

SQL> desc pending_tran;
Name Null? Type
----------------------------------------- -------- -------------
TRAN_DATE DATE
STATUS VARCHAR2(1)
COLA NUMBER(5)


declare
cursor c_today_tran is select colA from today_tran;
c_colA tran.col2%type;

begin
open c_today_tran;
loop
fetch c_today_tran into c_colA;
update pending_tran set tran_date=sysdate, status ='Y' where pending_tran.colA=c_colA;
if sql%found then dbms_output.put_line('Updated');
else
insert into pending_tran(cola) values (c_colA);
end if;
exit when c_today_tran%notfound;
end loop;
close c_today_tran;
end;

Re: CUSROR%FOUND [message #357787 is a reply to message #357785] Thu, 06 November 2008 09:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@lakshmis

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: CUSROR%FOUND [message #357790 is a reply to message #357787] Thu, 06 November 2008 10:05 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi Michel,
Thanks for your mail. I have gone through the guidelines
and hope not repeating this.
Re: CUSROR%FOUND [message #357801 is a reply to message #357790] Thu, 06 November 2008 12:22 Go to previous messageGo to next message
srihari1974
Messages: 12
Registered: November 2008
Location: atlanta
Junior Member
Hi lakhsmi,
Its taking for ever
Re: CUSROR%FOUND [message #357812 is a reply to message #357801] Thu, 06 November 2008 13:31 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi,
Sorry, I misunderstood your requirement.
Hope this may work.
But the problem is, whenever i use cursors,
last line always repeats and even now this happened.
If you have a solution please provide.


declare
cursor c_today_tran is
select colA from today_tran;
c_colA today_tran.colA%type;

begin
open c_today_tran;
loop
fetch c_today_tran into c_colA;
update transactions set tran_date=sysdate,
status='Y'where col2=c_colA;
if sql%found then
dbms_output.put_line('Updated');
else
insert into pending_tran values (c_colA);
end if;
exit when c_today_tran%notfound;
end loop;
close c_today_tran;
end;
Re: CUSROR%FOUND [message #357817 is a reply to message #357812] Thu, 06 November 2008 14:23 Go to previous messageGo to next message
srihari1974
Messages: 12
Registered: November 2008
Location: atlanta
Junior Member
Hi Lakshmi,
This will do only updates.
I wanted to insert as well the unmatched records in PENDING
It will never go to insert block.
Re: CUSROR%FOUND [message #357821 is a reply to message #357817] Thu, 06 November 2008 15:24 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
srihari1974,

Its working Fine for me. Hope the following codes proves it(Its just an example):
SQL> SELECT * FROM TEST_TABLE;

     COL_A COL_B
---------- --------------------
         3 C
         1 A
         2 B
         4 D
         5 E
         6 F
         7 G
         8 H
         9 I
        10 J
        11 K

     COL_A COL_B
---------- --------------------
        12 L
        13 M

13 rows selected.

SQL> SELECT * FROM TARGET_TABLE;

     COL_C COL_D
---------- --------------------
         3 C
         1 A
         2 B

3 rows selected.

SQL> declare
  2  cursor c_test_tab is 
  3  select col_A, col_B from test_table;
  4  my_rec c_test_tab%rowtype;
  5   
  6  upd_count number := 0;
  7  ins_count number := 0;
  8  c_colA test_table.col_A%type;
  9  c_colB test_table.col_B%TYPE;
 10  begin
 11  open c_test_tab;
 12  loop
 13  fetch c_test_tab into my_rec;
 14  c_colA := my_rec.COL_A;
 15  c_colB := my_rec.COL_B;
 16  
 17  update TARGET_TABLE set COL_C=c_COLA,
 18  COL_D = C_COLB
 19  where col_C=my_rec.COL_A;
 20  if sql%found then
 21  upd_count := upd_count + 1; 
 22  else
 23  insert into TARGET_TABLE values (c_COLA, c_COLB);
 24  ins_count := ins_count + 1;
 25  end if;
 26  exit when c_test_tab%notfound;
 27  end loop;
 28  dbms_output.put_line('Updated : ' ||to_char(upd_count));
 29  dbms_output.put_line('Inserted : ' ||to_char(ins_count));
 30  close c_test_tab;
 31  end;
 32  /
Updated : 4
Inserted : 10

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TARGET_TABLE;

     COL_C COL_D
---------- --------------------
         3 C
         1 A
         2 B
         4 D
         5 E
         6 F
         7 G
         8 H
         9 I
        10 J
        11 K

     COL_C COL_D
---------- --------------------
        12 L
        13 M

13 rows selected.



Copy paste your SQL*Plus Session as above so that we can know whats gone wrong with your query.

Hope this helps,

Regards,
Jo

[***Added: Only made minor modifications in the block posted by @lakshmi. Didn't really look into the proc though on why 4 rows are updated instead of 3]

[Updated on: Thu, 06 November 2008 15:39]

Report message to a moderator

Re: CUSROR%FOUND [message #357842 is a reply to message #357570] Thu, 06 November 2008 21:49 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

By the way ,

Why can't you try to implement the same without any loops as suggested by Michel ? Its just a matter of an update and an insert.

Smile
Rajuvan.
Re: CUSROR%FOUND [message #357977 is a reply to message #357842] Fri, 07 November 2008 08:07 Go to previous messageGo to next message
srihari1974
Messages: 12
Registered: November 2008
Location: atlanta
Junior Member
If i do without loops.
The whole table gets updated
I should have where condition
Re: CUSROR%FOUND [message #357983 is a reply to message #357977] Fri, 07 November 2008 08:26 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
srihari1974 wrote on Fri, 07 November 2008 15:07
If i do without loops.
The whole table gets updated
I should have where condition

So, as I already said, add a where condition.

Regards
Michel

Previous Topic: dbms_job to run for every 30 seconds daily
Next Topic: Print Sentence Vertically
Goto Forum:
  


Current Time: Sun Dec 04 18:56:01 CST 2016

Total time taken to generate the page: 0.09737 seconds