--------------------end of procedure end_campaigns--------------------
	PROCEDURE delete_campaign(ctitle IN campaign.title%Type)
	IS
		CURSOR delete_campaign_cur
		IS
			SELECT ....
--------------------procedure end_campaigns--------------------

SQL> begin
     delete from emp;
end;
 /

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;
    

  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..

	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--------------------

	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;

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..

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

	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;