Pl/sql code for queries

5 pts.
Tags:
Oracle development
PL/SQL
Hi , I have written 4 sql queries.2 select commands and 2 delete commands.now I want to write a pl/sql block for this.i.e after 2 select statements if the given condition satisfies then 1st delete command applicable. same way for 2nd delete command.I want to write cusor for those 2 select statements. the queries are a sfollows [code] select cpd1.cost_change ||','|| cpd1.supplier ||','|| cpd1.origin_country_id ||','|| cpd1.item ||','|| cpd1.bracket_value1 ||','|| cpd1.bracket_uom1 ||','|| cpd1.bracket_value2 ||','|| cpd1.unit_cost ||','|| cpd1.recalc_ord_ind ||','|| cpd1.default_bracket_ind ||','|| cpd1.dept ||','|| cpd1.sup_dept_seq_no from cost_sup_detail cpd1 where cpd1.cost_change in (select cph.cost_change from item_country isc, cost_sup_detail cpd, cost_sup_head cph where cph.active_date=(select bdate from units) and cph.cost_change = cpd.cost_change and cpd.item = isc.item and cpd.supplier = isc.supplier and cpd.origin_country_id = isc.origin_country_id and cpd.unit_cost = isc.unit_cost) [/code] same way for 2 nd query.. now the condition is [code] if active_date=bdate+5 then 1st delete command... applicable.. for this I need to write PL/SQL program either procedure or function what ever it may be... please write the pl/sql block for this..... Thank you.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Use a for loop with an implicit cursor

create or replace delete_procedure is

begin
For cost_rec in (first select statment)
loop
if cost_rec.active_date = cost_rec.bdate+5 then
first delete statement;
end if;
end loop;

For cost_rec in (second select)
loop
if (2nd delete criteria) then
2nd delete statement;
end if;
end loop;

commit;

end delete_procedure;

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following