Home » SQL & PL/SQL » SQL & PL/SQL » cursor (how to reduce the query)
cursor [message #299000] Fri, 08 February 2008 08:50 Go to next message
sudharshan
Messages: 48
Registered: November 2006
Member
I WANT SEPERATE MESSGAGES TO BE PUT. SO I AM WRITING IT.IS THIS CORRECT

PROCEDURE unload_missing
IS
CURSOR C1
IS
SELECT *
FROM customer_stg
WHERE billing_country is null;

CURSOR C2
IS
SELECT *
FROM customer_stg
WHERE billing_state is null;


CURSOR C3
IS
SELECT *
FROM customer_stg
WHERE billing_street is null;


CURSOR C4
IS
SELECT *
FROM customer_stg
WHERE shipping_country is null;

CURSOR C5
IS
SELECT *
FROM customer_stg
WHERE shipping_state is null;

CURSOR C6
IS
SELECT *
FROM customer_stg
WHERE shipping_state is null;



BEGIN

OPEN C1;

IF C1%FOUND THEN
UPDATE customer_stg
SET error ='E',add_attr1 ='BILLING COUNTRY IS MISSING';
END IF;

CLOSE C1;


OPEN C2;

IF C2%FOUND THEN
UPDATE customer_stg
SET error ='E',add_attr1 ='BILLING STATE IS MISSING';
END IF;

CLOSE C2;


OPEN C3;

IF C3%FOUND THEN
UPDATE customer_stg
SET error ='E',add_attr1 ='BILLING STREET IS MISSING';
END IF;

CLOSE C3;


OPEN C4;

IF C4%FOUND THEN
UPDATE customer_stg
SET error ='E',add_attr1 ='SHIPPING COUNTRY IS MISSING';
END IF;

CLOSE C4;


OPEN C5;

IF C4%FOUND THEN
UPDATE customer_stg
SET error ='E',add_attr1 ='SHIPPING STATE IS MISSING';
END IF;

CLOSE C5;



OPEN C6;

IF C4%FOUND THEN
UPDATE customer_stg
SET error ='E',add_attr1 ='SHIPPING STREET IS MISSING';
END IF;

CLOSE C6;


END unload_missing;

Re: cursor [message #299002 is a reply to message #299000] Fri, 08 February 2008 08:55 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Not really correct, and also not that optimal as well.

But why don't you try this:

UPDATE customer_stg
SET error ='E',add_attr1 ='BILLING COUNTRY IS MISSING'
WHERE billing_country is null ;


I hope you can figure out the rest Wink
Re: cursor [message #299003 is a reply to message #299000] Fri, 08 February 2008 08:57 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
Thanks man you're right.
Re: cursor [message #299004 is a reply to message #299000] Fri, 08 February 2008 09:02 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
when C4 is open it will updates the all the corresponding values if it is found any.

Then what is the use of the rest of the cursors.


OPEN C5;

IF C4%FOUND THEN
UPDATE customer_stg 
SET error ='E',add_attr1 ='SHIPPING STATE IS MISSING';
END IF;

CLOSE C5;



OPEN C6;

IF C4%FOUND THEN
UPDATE customer_stg 
SET error ='E',add_attr1 ='SHIPPING STREET IS MISSING';
END IF;

CLOSE C6;
Re: cursor [message #299005 is a reply to message #299000] Fri, 08 February 2008 09:08 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read and follow 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.
Remove useless blank lines.
Use the "Preview Message" button to verify.

Always post your Oracle version (4 decimals).

DON'T POST IN CAPITALS.

Regards
Michel
Previous Topic: eliminate duplicate rows (merged same question by two different users)
Next Topic: data access path in oracle
Goto Forum:
  


Current Time: Thu Dec 08 00:08:42 CST 2016

Total time taken to generate the page: 0.18818 seconds