Home » SQL & PL/SQL » SQL & PL/SQL » about pl/sql - help me please...urgent needed
about pl/sql - help me please...urgent needed [message #200192] Sun, 29 October 2006 01:03 Go to next message
hiip
Messages: 78
Registered: October 2006
Member
hi everyone, hope can give me some idea or correct my error...

this is what i want to create..

procedure list_campaigns(cname in campaign.companyname%type)

for a given company, this procedure prints out information on all the campaigns run for that company, for each campaign, it should print out as a minimum the campaign title and the estimated and the actual cost of the campaign. must use a cursor for loop

and this is my sample relational data model

campaign(title, campaignfinishdate, estimatedcost, actualcost,datepaid, companyname, managerstaffno)
primary key : title
foreign key : companyname refers to companyname in client
foreign key : managerstaffno refers to staffno in staff..


and this is my code
--Procedure list_campaigns(cname in campaign.companyname%Type)--
CREATE OR REPLACE PROCEDURE list_campaign
	(cname IN campaign.companyname%Type)
IS
	CURSOR list_campaign_cur IS
		SELECT title, estimatedcost, actualcost
		FROM campaign;
BEGIN
	FOR list_campaign_rec IN list_campaign_cur LOOP
		IF cname = campaign.companyname THEN
			DBMS_OUTPUT.PUT_LINE( list_campaign_cur.TITLE || ' ' || 'Estimated Cost: ' || list_campaign_cur.estimatedcost || ' ' ||
			' Actual Cost: ' || list_campaign_cur.actualcost );
		END IF;
	END LOOP;
END;
/
Re: about pl/sql - help me please...urgent needed [message #200193 is a reply to message #200192] Sun, 29 October 2006 01:09 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
What is the error that you want to sort out?
Re: about pl/sql - help me please...urgent needed [message #200197 is a reply to message #200193] Sun, 29 October 2006 01:42 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
i just dont know is this correct for that question.... can you check it for me ?
does this code meet the question?

the error is like this

5/3 PL/SQL: SQL Statement ignored
7/21 PL/SQL: ORA-00920: invalid relational operator

[Updated on: Sun, 29 October 2006 02:05]

Report message to a moderator

Re: about pl/sql - help me please...urgent needed [message #200199 is a reply to message #200197] Sun, 29 October 2006 02:26 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you should read more carefuly cursors concept (check this page for some examples); you can't output CURSOR value, but CURSOR VARIABLE ones (the ones you select data into). Try such a code and see will it bring any improvement:
CREATE OR REPLACE PROCEDURE list_campaign (cname IN campaign.companyname%TYPE)
IS
   CURSOR list_campaign_cur
   IS
      SELECT title, estimatedcost, actualcost, companyname
        FROM campaign;
BEGIN
   FOR list_campaign_rec IN list_campaign_cur
   LOOP
      IF cname = list_campaign_rec.companyname
      THEN
         DBMS_OUTPUT.put_line (   list_campaign_rec.title
                               || ' '
                               || 'Estimated Cost: '
                               || list_campaign_rec.estimatedcost
                               || ' '
                               || ' Actual Cost: '
                               || list_campaign_rec.actualcost
                              );
      END IF;
   END LOOP;
END;
Re: about pl/sql - help me please...urgent needed [message #200201 is a reply to message #200192] Sun, 29 October 2006 02:32 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
thank you very much for the correction,, and the most appreciate is the link of the sample.. will print it out for that...
and also the lesson that you gave..
Re: about pl/sql - help me please...urgent needed [message #200309 is a reply to message #200192] Mon, 30 October 2006 05:44 Go to previous messageGo to next message
ajaybabu.yaleti
Messages: 11
Registered: October 2006
Junior Member
Hi,

Try this

Use where condition in the cursor, it fetches records for the corresponding .

Regards,
Ajay


Re: about pl/sql - help me please...urgent needed [message #200483 is a reply to message #200199] Tue, 31 October 2006 01:43 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
An improvement, given that you've got a requirement to use a cursor for loop, would be this (Based on @Littlefoot's code)

CREATE OR REPLACE PROCEDURE list_campaign (cname IN campaign.companyname%TYPE) IS
   CURSOR list_campaign_cur (p_company IN campaign,companyname%type) IS
     SELECT title, estimatedcost, actualcost, companyname
     FROM   campaign
     WHERE  companyname = p_company;
BEGIN
   FOR list_campaign_rec IN list_campaign_cur(cname) LOOP
     DBMS_OUTPUT.put_line (   list_campaign_rec.title
                           || ' '
                           || 'Estimated Cost: '
                           || list_campaign_rec.estimatedcost
                           || ' '
                           || ' Actual Cost: '
                           || list_campaign_rec.actualcost
                          );
   END LOOP;
END;

You spend less time retrieving records from the database that you aren't going to output if you move the check on company name up into the cursor.
Previous Topic: Update repeated data
Next Topic: regarding dbms_job
Goto Forum:
  


Current Time: Tue Dec 06 10:25:01 CST 2016

Total time taken to generate the page: 0.17093 seconds