Home » SQL & PL/SQL » SQL & PL/SQL » what should i do?? - hints?
what should i do?? - hints? [message #200728] Wed, 01 November 2006 01:57 Go to next message
hiip
Messages: 78
Registered: October 2006
Member
some hints please////...

procedure delete_campaign(ctitle in campaing.title%Type)

suppose that campaign has been completed and paid for. also suppose that all the detaails about the campaign have been recorded in some other archival database(which does not concern me). every now and then the databasae is checked and such campaigns are deleted from the databse using this procedure. the following action is what i should do...

1, delete the relevant advert records,
2, delete the relevant workson records and
3, delete the campaign record.

if the given campaign does not exist, the procedure deals with the corresponding no_data_found exception. if the given campaign has a null datepaid/ campaignfinishdate value, it raises and deals with a user-defined campaign_not_finished exception. if it fails for any other reason(for example during update) it deals with a catch_all exception(others).

here is my relational data:

campaign(title, campaignfinishdate, estimatedcost, actualcost, datepaid, companyname, managerstaffno)

advert(adtitle, type, targetdate, completeddate, estimatedcost, actualcost, campaigntitle)

staffworksoncampaign(staffno, title, date, hour)

client(companyname, companyaddress, contactname, contactemail, staffno)

...

here is my starting of my code


--------------------end of procedure end_campaigns--------------------
	PROCEDURE delete_campaign(ctitle IN campaign.title%Type)
	IS
		CURSOR delete_campaign_cur
		IS
			SELECT ....
--------------------procedure end_campaigns--------------------
Re: what should i do?? - hints? [message #200792 is a reply to message #200728] Wed, 01 November 2006 06:10 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Consider avoiding cursor, and doing by just sql, if possible, like:

SQL> begin
     delete from emp;
end;
 /


I think it should be possible as your requirement is to just delete some data from some tables...you can work out the details..
Re: what should i do?? - hints? [message #200810 is a reply to message #200792] Wed, 01 November 2006 07:34 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
but in the question means, if campaign has been completed and paid for... we should provide those 3 steps of delete..

beside i dont get you.. can you make a good example please

or should i make a cursor .. lie this

cursor.....
select title
from campaign
where campaignfinishdate != ' ';.....
Re: what should i do?? - hints? [message #200813 is a reply to message #200810] Wed, 01 November 2006 07:39 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
I would in turn ask you to post sample data and table structure and clear requirement...
can you post table structure of all table involved, and sample data of one such campain to be deleted..(i mean data for one such case from ALL relevant tables..)
Re: what should i do?? - hints? [message #200827 is a reply to message #200728] Wed, 01 November 2006 08:38 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
ok i will upload my DMl for the table and my data in the table..
  • Attachment: new 3.sql
    (Size: 7.06KB, Downloaded 92 times)
Re: what should i do?? - hints? [message #200831 is a reply to message #200728] Wed, 01 November 2006 08:57 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
can this work ?

PROCEDURE delete_campaign(ctitle IN campaign.title%Type)
IS
DELETE FROM advert
where campaigntitle = ctitle;
if sql%notfound then
raise_application_error(-20011, 'Invalid campaign title: ' || ctitle);
end if;
end delete_campaign;
    
Re: what should i do?? - hints? [message #200842 is a reply to message #200831] Wed, 01 November 2006 10:01 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Yes, that is what I meant...you will have to check that , in order to delete something, which are the child tables or related tables, all of which you should include...but other than that, that is what I meant..the code u gave works...like:

  1  CREATE OR REPLACE PROCEDURE delete_campaign(ctitle IN campaign.title%Type)
  2  IS
  3  BEGIN
  4  DELETE FROM advert
  5  where campaigntitle = ctitle;
  6  if sql%notfound then
  7  raise_application_error(-20011, 'Invalid campaign title: ' || ctitle);
  8  end if;
  9* end delete_campaign;
SQL> /

Procedure created.

SQL> EXEC DELETE_CAMPAIGN('ABC');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM ADVERT;

ADTITLE    TYPE     TARGETDAT COMPLETED ESTIMATEDCOST ACTUALCOST CAMPAIGNTITLE
---------- -------- --------- --------- ------------- ---------- ------------------------------
SSS        Tv       14-JUN-06 14-OCT-06          1500       1600 CDC
DDD        Magazine 15-JUL-06 15-DEC-06          1700       1800 DEW
AAA        Poster   16-JUN-06 16-OCT-06          1900       2000 NOM
BBB        Online   16-JAN-06 16-MAY-06          2000       2300 CCC
FFF        Tv       14-FEB-06 14-JAN-06          1100       1000 CDC
GGG        Magazine 15-JUL-06 15-DEC-06          1800       1900 DEW
HHH        Poster   16-OCT-06 16-SEP-06          1900       2100 NOM
III        Online   16-FEB-06 16-APR-06          2000       2300 CCC

8 rows selected. --ABC is gone from ADVERT TABLE..


& you have avoided cursor, which do row by row process..
Re: what should i do?? - hints? [message #200843 is a reply to message #200728] Wed, 01 November 2006 10:05 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
I am assuming that the advert and workson tables have a child relationship to the campaign table. If this is true, then those tables should have a foreign key back to the campaign table and the relationship should be setup with a cascade delete. If this is done, then a simple delete of the campaign record will delete all related rows in all other tables. Otherwise, delete the child tables first and then the parent.
Re: what should i do?? - hints? [message #200906 is a reply to message #200728] Wed, 01 November 2006 18:26 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
how could i modify the following code so that i can delete it row by row instead ot deleting all the related title that being called...know what i mean????

for example.....
delete('ABC')

and i want to delete one by one instad of all campaigntitle of 'ABC'

and also
i dot really sure what this means..

'I am assuming that the advert and workson tables have a child relationship to the campaign table. If this is true, then those tables should have a foreign key back to the campaign table and the relationship should be setup with a cascade delete. If this is done, then a simple delete of the campaign record will delete all related rows in all other tables. Otherwise, delete the child tables first and then the parent.'
Re: what should i do?? - hints? [message #200907 is a reply to message #200728] Wed, 01 November 2006 18:31 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
[quote title=hiip wrote on Wed, 01 November 2006 01:57]some hints please////...

procedure delete_campaign(ctitle in campaing.title%Type)

suppose that campaign has been completed and paid for. also suppose that all the detaails about the campaign have been recorded in some other archival database(which does not concern me). every now and then the databasae is checked and such campaigns are deleted from the databse using this procedure. the following action is what i should do...

1, delete the relevant advert records,
2, delete the relevant workson records and
3, delete the campaign record.

if the given campaign does not exist, the procedure deals with the corresponding no_data_found exception. if the given campaign has a null datepaid/ campaignfinishdate value, it raises and deals with a user-defined campaign_not_finished exception. if it fails for any other reason(for example during update) it deals with a catch_all exception(others).



as in my title, it says delete the campaign has been completed and the campaign is being paid ,, so we delete it..

and i want to delete three tables data....

1, advert
2, workson
3, campaign

as they are related to each others....
so what should i add into my code?
Re: what should i do?? - hints? [message #200934 is a reply to message #200907] Thu, 02 November 2006 00:19 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
I guess it would be good to begin from the beginning...please go through the concepts of constraints at:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c22integ.htm#3191

and then if questions are still remaining, post them..
Re: what should i do?? - hints? [message #200941 is a reply to message #200728] Thu, 02 November 2006 00:38 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
thnx for the link ... reading it now ..
Re: what should i do?? - hints? [message #200951 is a reply to message #200842] Thu, 02 November 2006 01:06 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
nirav_hyd wrote on Wed, 01 November 2006 10:01
Yes, that is what I meant...you will have to check that , in order to delete something, which are the child tables or related tables, all of which you should include...but other than that, that is what I meant..the code u gave works...like:

  1  CREATE OR REPLACE PROCEDURE delete_campaign(ctitle IN campaign.title%Type)
  2  IS
  3  BEGIN
  4  DELETE FROM advert
  5  where campaigntitle = ctitle;
  6  if sql%notfound then
  7  raise_application_error(-20011, 'Invalid campaign title: ' || ctitle);
  8  end if;
  9* end delete_campaign;
SQL> /

Procedure created.

SQL> EXEC DELETE_CAMPAIGN('ABC');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM ADVERT;

ADTITLE    TYPE     TARGETDAT COMPLETED ESTIMATEDCOST ACTUALCOST CAMPAIGNTITLE
---------- -------- --------- --------- ------------- ---------- ------------------------------
SSS        Tv       14-JUN-06 14-OCT-06          1500       1600 CDC
DDD        Magazine 15-JUL-06 15-DEC-06          1700       1800 DEW
AAA        Poster   16-JUN-06 16-OCT-06          1900       2000 NOM
BBB        Online   16-JAN-06 16-MAY-06          2000       2300 CCC
FFF        Tv       14-FEB-06 14-JAN-06          1100       1000 CDC
GGG        Magazine 15-JUL-06 15-DEC-06          1800       1900 DEW
HHH        Poster   16-OCT-06 16-SEP-06          1900       2100 NOM
III        Online   16-FEB-06 16-APR-06          2000       2300 CCC

8 rows selected. --ABC is gone from ADVERT TABLE..


& you have avoided cursor, which do row by row process..



hi, this is my new code,,,,

	PROCEDURE delete_campaign(ctitle IN campaign.title%Type)
	IS
		v_datepaid campaign.datepaid%TYPE;
		v_campaignfinishdate campaign.campaignfinishdate%Type;
		v_title campaign.title%Type;
		campaign_not_finished EXCEPTION;
	BEGIN
		DELETE FROM advert
		WHERE campaigntitle = ctitle;
			
		DELETE FROM staffcampaign
		WHERE title = ctitle;
			
		DELETE FROM campaign
		WHERE title = ctitle;
		
		SELECT title, datepaid, campaignfinishdate
		INTO v_title, v_datepaid, v_campaignfinishdate
		FROM campaign;
		
		IF v_datepaid IS NULL THEN
			RAISE campaign_not_finished;
		END IF;
		IF v_campaignfinishdate IS NULL THEN
			RAISE campaign_not_finished;
		END IF;
	EXCEPTION 
		WHEN NO_DATA_FOUND THEN
			DBMS_OUTPUT.PUT_LINE
			('Invalid campaign title');
		WHEN campaign_not_finished THEN
			DBMS_OUTPUT.PUT_LINE
			('Campaign not finished');
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE
			('It Fails for other reason');	
	COMMIT;
	END delete_campaign;
--------------------procedure end_campaigns--------------------


what do you think of it ?? does this code meet the requirement for the question on the very top..???
Re: what should i do?? - hints? [message #200966 is a reply to message #200951] Thu, 02 November 2006 01:34 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Perhaps not..because in the SELECT clause below:

Quote:

SELECT title, datepaid, campaignfinishdate
INTO v_title, v_datepaid, v_campaignfinishdate
FROM campaign;


don't you have to add a where condition? otherwise it will raise an error when campaign table has more than 1 record. please be clear conceptually and test all your outputs!


Re: what should i do?? - hints? [message #201002 is a reply to message #200728] Thu, 02 November 2006 04:00 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
this is my another new code....


	PROCEDURE delete_campaign(ctitle IN campaign.title%Type)
	IS
		v_datepaid campaign.datepaid%TYPE;
		v_campaignfinishdate campaign.campaignfinishdate%Type;
		v_title campaign.title%Type;
		campaign_not_finished EXCEPTION;
	BEGIN
		SELECT title, datepaid, campaignfinishdate
		INTO v_title, v_datepaid, v_campaignfinishdate
		FROM campaign
		WHERE datepaid IS NOT NULL 
		AND campaignfinishdate IS NOT NULL;
		
		DELETE FROM advert
		WHERE campaigntitle = ctitle;
			
		DELETE FROM staffcampaign
		WHERE title = ctitle;
			
		DELETE FROM campaign
		WHERE title = ctitle;

		IF v_datepaid IS NULL THEN
			RAISE campaign_not_finished;
		END IF;
		IF v_campaignfinishdate IS NULL THEN
			RAISE campaign_not_finished;
		END IF;
	EXCEPTION 
		WHEN NO_DATA_FOUND THEN
			DBMS_OUTPUT.PUT_LINE
			('Invalid campaign title');
		WHEN campaign_not_finished THEN
			DBMS_OUTPUT.PUT_LINE
			('Campaign not finished');
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE
			('It Fails for other reason');	
	COMMIT;
	END delete_campaign;


is this right??
Re: what should i do?? - hints? [message #201003 is a reply to message #201002] Thu, 02 November 2006 04:04 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Please tell me the following:

Is your application designed in such a way that,when
datepaid IS NOT NULL AND campaignfinishdate IS NOT NULL , then only one such record is there in the table, campaign? if so then that code will work, else it will break..hope you see the reason..just try to run the code against the application and check it out..
Re: what should i do?? - hints? [message #201017 is a reply to message #201003] Thu, 02 November 2006 04:29 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
For example see the code below:


SQL> select * from staff;

   STAFFNO STAFFNAME                      LINEMANAGERSTAFFNO
---------- ------------------------------ ------------------
         1 Amir                                            6
         2 John                                            6
         3 Brian                                           5
         4 Patel                                           5
         5 Deborah
         6 Sen

6 rows selected.


SQL> ed
Wrote file afiedt.buf

  1   declare
  2    v number;
  3   begin
  4    select staffno into v from staff;
  5*  end;
SQL> /
 declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

--WHY THIS ERROR? BECOZ STAFF TABLE HAS MORE THAN ONE RECORD..

Re: what should i do?? - hints? [message #201034 is a reply to message #200728] Thu, 02 November 2006 05:23 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
ok.. so which part did i go wrong?
Re: what should i do?? - hints? [message #201036 is a reply to message #201034] Thu, 02 November 2006 05:28 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Certainly this part:

SELECT title, datepaid, campaignfinishdate
		INTO v_title, v_datepaid, v_campaignfinishdate
		FROM campaign
		WHERE datepaid IS NOT NULL 
		AND campaignfinishdate IS NOT NULL;


why: because if there are more than one records that get selected in this select condition then your code will break..right?
Re: what should i do?? - hints? [message #201037 is a reply to message #200728] Thu, 02 November 2006 05:30 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
you are right.. as it only handle single return...so what you mean is just the where clause is wrong??

[Updated on: Thu, 02 November 2006 05:30]

Report message to a moderator

Re: what should i do?? - hints? [message #201062 is a reply to message #201037] Thu, 02 November 2006 07:11 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Yes.
Re: what should i do?? - hints? [message #201064 is a reply to message #200728] Thu, 02 November 2006 07:15 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
so what should i replace it to as you said i miss where clause from earlier reply...
change and to or??
Re: what should i do?? - hints? [message #201068 is a reply to message #201064] Thu, 02 November 2006 07:22 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
I can not answer that question as I do not know your application...I suggest you have a review with someone else at your work who is supposed to know more about it..
Re: what should i do?? - hints? [message #201075 is a reply to message #200728] Thu, 02 November 2006 07:40 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
if i supply my table, would you be able to help me? please!!!
Re: what should i do?? - hints? [message #201079 is a reply to message #201075] Thu, 02 November 2006 07:49 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Unfortunately No! This does not require knowing the table or its columns, it requires knowing the application...we can help technically not functionally...again I suggest you talk to your senior collegue..
Re: what should i do?? - hints? [message #201244 is a reply to message #201036] Fri, 03 November 2006 04:39 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
am i on the right track

	PROCEDURE delete_campaign(ctitle IN campaign.title%Type)
	IS
		v_datepaid campaign.datepaid%TYPE;
		v_campaignfinishdate campaign.campaignfinishdate%Type;
		v_title campaign.title%Type;
		campaign_not_finished EXCEPTION;
	BEGIN
		SELECT title, datepaid, campaignfinishdate
		INTO v_title, v_datepaid, v_campaignfinishdate
		FROM campaign
		WHERE datepaid = v_datepaid
		AND campaignfinishdate = v_campaignfinishdate;
		
		DELETE FROM advert
			WHERE campaigntitle = ctitle;
			
		DELETE FROM staffcampaign
			WHERE title = ctitle;
			
		DELETE FROM campaign
			WHERE title = ctitle;

		IF v_datepaid IS NULL THEN
			RAISE campaign_not_finished;
		END IF;
		IF v_campaignfinishdate IS NULL THEN
			RAISE campaign_not_finished;
		END IF;
	EXCEPTION 
		WHEN NO_DATA_FOUND THEN
			DBMS_OUTPUT.PUT_LINE
			('Invalid campaign title');
		WHEN campaign_not_finished THEN
			DBMS_OUTPUT.PUT_LINE
			('Campaign not finished');
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE
			('It Fails for other reason');	
	COMMIT;
	END delete_campaign;

Re: what should i do?? - hints? [message #201284 is a reply to message #201244] Fri, 03 November 2006 06:47 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
is this oracle code?buddy i think you are on the right track on the wrong train
Re: what should i do?? - hints? [message #201292 is a reply to message #201284] Fri, 03 November 2006 07:09 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is not Oracle PL/SQL procedure? What is it, then?
Re: what should i do?? - hints? [message #201305 is a reply to message #201284] Fri, 03 November 2006 07:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
amul wrote on Fri, 03 November 2006 13:47
is this oracle code?buddy i think you are on the right track on the wrong train

could you point 5 things out that are NOT oracle-code??
3 ??
1 ??

ok, it may not be very elegant code, but it sure looks like PL/SQL to me.
And you might even fool Oracles PL/SQL parser!

[Updated on: Fri, 03 November 2006 07:50]

Report message to a moderator

Re: what should i do?? - hints? [message #201965 is a reply to message #201244] Tue, 07 November 2006 10:41 Go to previous message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

Why do you raise exception RAISE campaign_not_finished, instead of simply leaving the procedure? Do you want to handle such situation?
If you leave it simply, it will come out without issue. If you raise exception, you can not commit wherever the procedure is called unless you handle it exclusively.

Jayg
Fun Programming with Oracle & Visual C++

[Updated on: Wed, 08 November 2006 04:18]

Report message to a moderator

Previous Topic: surrogate key
Next Topic: Tricky sql query help needed
Goto Forum:
  


Current Time: Fri Dec 09 19:09:40 CST 2016

Total time taken to generate the page: 0.10183 seconds