OraFAQ Forum: SQL & PL/SQL » cursor for loop syntax issue

Home » SQL & PL/SQL » SQL & PL/SQL » cursor for loop syntax issue (oracle 10g release 2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

cursor for loop syntax issue [message #310954]

Wed, 02 April 2008 23:43

horax
Messages: 34
Registered: March 2008

Member

I have written the following code, and I know it's not great yet...I'm just trying to get the code to execute first, then I'll fine tune it for performance.

Anyway, let me show you the code, then I'll explain the error I'm receiving and hopefully you can help me understand why I'm receiving it.

DECLARE
  CURSOR cur_sal IS
    SELECT job, sal
    FROM employee
    FOR UPDATE NOWAIT;
  lv_newsal employee.sal%type;
  lv_raise_num  NUMBER(6,2):=.06;
  
BEGIN
 FOR rec_sal IN cur_sal LOOP
   IF rec_sal.job='President' THEN lv_raise_num:=0 AND lv_newsal:=rec_sal.sal;
   END IF;
   IF rec_sal.job='Manager' THEN lv_newsal:=((rec_sal.sal*lv_raise_num)+rec_sal.sal);
   END IF;
   IF rec_sal.job='Salesman' THEN lv_newsal:=((rec_sal.sal*lv_raise_num)+rec_sal.sal);
   END IF;
   IF rec_sal.job='Clerk' THEN lv_newsal:=((rec_sal.sal*lv_raise_num)+rec_sal.sal);
   END IF;
   IF rec_sal.job='Analyst' THEN lv_newsal:=((rec_sal.sal*lv_raise_num)+rec_sal.sal);
   END IF;
 UPDATE employee
   SET sal=lv_newsal
   WHERE CURRENT OF cur_sal;
 END LOOP;
END;
/

Errors:
IF rec_sal.job='President' THEN lv_raise_num:=0 AND lv_newsal=rec_sal.sal;
*
ERROR at line 11:
ORA-06550: line 11, column 50:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 36:
PL/SQL: Statement ignored

Why would it say that lv_raise_num:=0 is of wrong type? I've assigned it as a NUM...isn't 0 a num?

Report message to a moderator

Re: cursor for loop syntax issue [message #310957 is a reply to message #310954]

Wed, 02 April 2008 23:53

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

IF rec_sal.job='President' THEN lv_raise_num:=0 AND lv_newsal:=rec_sal.sal;

You are using and operation in b/w to statement

try this:-

IF rec_sal.job='President' THEN 
lv_raise_num:=0 ;
lv_newsal:=rec_sal.sal;

Report message to a moderator

Re: cursor for loop syntax issue [message #310958 is a reply to message #310954]

Wed, 02 April 2008 23:53

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

check the if statement perfectly


Errors:
IF rec_sal.job='President' THEN lv_raise_num:=0 AND lv_newsal=rec_sal.sal;
*
ERROR at line 11:
ORA-06550: line 11, column 50:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 36:
PL/SQL: Statement ignored

Report message to a moderator

Re: cursor for loop syntax issue [message #310959 is a reply to message #310954]

Wed, 02 April 2008 23:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>IF rec_sal.job='President' THEN lv_raise_num:=0 AND lv_newsal=rec_sal.sal;
Exactly what in words do you think will happen as a result of the line above?

Report message to a moderator

Re: cursor for loop syntax issue [message #310961 is a reply to message #310959]

Wed, 02 April 2008 23:59

horax
Messages: 34
Registered: March 2008

Member

yeah, I saw that messed up := and fixed that right after posting.

As far as what I think it will do?

IF rec_sal.job='President' THEN lv_raise_num:=0 AND lv_newsal:=rec_sal.sal;

If the variable rec_sal.job returns the value of "president," then the raise will be equal to 0. His new salary will be assigned the value of his old salary.

Report message to a moderator

Re: cursor for loop syntax issue [message #310965 is a reply to message #310961]

Thu, 03 April 2008 00:01

horax
Messages: 34
Registered: March 2008

Member

The first response helped! I replaced the AND operator with a simple ; to end the line and taht was all it took.

Does this mean that you cannot do AND oeprators in a cursor for loop?

Report message to a moderator

Re: cursor for loop syntax issue [message #310967 is a reply to message #310954]

Thu, 03 April 2008 00:02

flyboy
Messages: 1903
Registered: November 2006

Senior Member

IF rec_sal.job='President' THEN lv_raise_num:=0 AND lv_newsal=rec_sal.sal;

Read that line again. What is the AND supposed to do there?
Yes, it is syntax error, AND is used in logical conditions, it is not valid as a part of the statement.

Just logical remark: you do not ORDER by query and change the value LV_RAISE_NUM inside the LOOP. As the result, not only employees with 'President' job, but all other employees updated after him, will use LV_RAISE_NUM = 0, not the initial .06 Seems as a bug, as this behaviour is not deterministic (for the same data, you may get different result).

> then I'll fine tune it for performance.
Best for performance would be throwing all the PL/SQL code away and UPDATE it in single SQL.

Report message to a moderator

Re: cursor for loop syntax issue [message #310968 is a reply to message #310954]

Thu, 03 April 2008 00:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>then the raise will be equal to 0. His new salary will be assigned the value of his old salary.
HUH? "AND"? to denote two different assignments?
What pigment of your imagination made up this construct?

Report message to a moderator

Re: cursor for loop syntax issue [message #310969 is a reply to message #310965]

Thu, 03 April 2008 00:05

horax
Messages: 34
Registered: March 2008

Member

DECLARE
  CURSOR cur_sal IS
    SELECT job, sal
    FROM employee
    FOR UPDATE NOWAIT;
  lv_newsal employee.sal%type;
  lv_raise_num  NUMBER(6,2):=.06;
  
BEGIN
 FOR rec_sal IN cur_sal LOOP
   IF rec_sal.job='President' THEN lv_raise_num:=0;
   lv_newsal:=rec_sal.sal;
   
   ELSIF rec_sal.job='Manager' THEN lv_newsal:=((rec_sal.sal*lv_raise_num)+rec_sal.sal);
  
   ELSIF rec_sal.job='Salesman' THEN lv_newsal:=((rec_sal.sal*lv_raise_num)+rec_sal.sal);
   
   ELSIF rec_sal.job='Clerk' THEN lv_newsal:=((rec_sal.sal*lv_raise_num)+rec_sal.sal);
   
   ELSIF rec_sal.job='Analyst' THEN lv_newsal:=((rec_sal.sal*lv_raise_num)+rec_sal.sal);
   END IF;
 UPDATE employee
   SET sal=lv_newsal
   WHERE CURRENT OF cur_sal;
 END LOOP;
END;
/

I tried thsi instead to simplify it a bit, and I get the same output. Unforutnately, when I query the orignal table after the update, NOW all the salaries are=0 across teh board...doesnt' matter what their position is!

EDIT: I wrote this rsponse prior to seeing the latest from you guys. Thanks for your help! And please lay off the pseudo bashing, I'm new at PL/SQL...

[Updated on: Thu, 03 April 2008 00:07]

Report message to a moderator

Re: cursor for loop syntax issue [message #310974 is a reply to message #310954]

Thu, 03 April 2008 00:18

horax
Messages: 34
Registered: March 2008

Member

Dangit...now, even AFTER rebuilding the table EMPLOYEE all the values change to 0 in the SAL column!

Here's the code I used:

DECLARE
  CURSOR cur_sal IS
    SELECT job, sal
    FROM employee
    FOR UPDATE NOWAIT;
  lv_newsal employee.sal%type;
  lv_raise_num  NUMBER(6,2):=.06;
  
BEGIN
 FOR rec_sal IN cur_sal LOOP
   
   IF rec_sal.job='Analyst' THEN lv_newsal:=((rec_sal.sal*lv_raise_num)+rec_sal.sal); 
 
   ELSIF rec_sal.job='Manager' THEN lv_newsal:=((rec_sal.sal*lv_raise_num)+rec_sal.sal);
  
   ELSIF rec_sal.job='Salesman' THEN lv_newsal:=((rec_sal.sal*lv_raise_num)+rec_sal.sal);
   
   ELSIF rec_sal.job='Clerk' THEN lv_newsal:=((rec_sal.sal*lv_raise_num)+rec_sal.sal);
   
   ELSIF rec_sal.job='President' THEN lv_newsal:=rec_sal.sal;

   END IF;

 UPDATE employee
   SET sal=lv_newsal
   WHERE CURRENT OF cur_sal;
 END LOOP;
END;
/

Why woudl it do that?

Report message to a moderator

Re: cursor for loop syntax issue [message #310982 is a reply to message #310974]

Thu, 03 April 2008 02:58

dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai

Senior Member

Why do you want to use PL/SQL when the same thing can be achived by SQL ?try using UPADATE with CASE statement.

regards,

Report message to a moderator

Re: cursor for loop syntax issue [message #310984 is a reply to message #310954]

Thu, 03 April 2008 03:01

flyboy
Messages: 1903
Registered: November 2006

Senior Member

> Why woudl it do that?

It seems there are no employees with 'Analyst', 'Manager', 'Salesman', 'Clerk' nor 'President' job. Be aware, Oracle comparision is ordinarily case sensitive, so eg. 'Analyst' and 'ANALYST' are different strings. You either have to specify its case accurately or use one of LOWER, UPPER and INITCAP functions.

Report message to a moderator

Re: cursor for loop syntax issue [message #311147 is a reply to message #310984]

Thu, 03 April 2008 10:06

horax
Messages: 34
Registered: March 2008

Member

Ah, gotcha. I didn't realize it was case sensitive. I'm using PL/SQL as it has been requested that I do so since I am taking a PL/SQL class right now. Smile

Here's the code I'm using now:

--CREATE BIND VARIABLE NAMED g_raise_num 
 VARIABLE g_raise_num  NUMBER

--INITIALIZE THE VALUE FOR THE BIND VARIABLE
 BEGIN
 :g_raise_num:=.06;
 END;
 /

--BEGIN THE PL/SQL BLOCK OF CODE
 DECLARE
   CURSOR cur_sal IS
     SELECT job, sal, empno
     FROM employee
     FOR UPDATE NOWAIT;
   lv_oldsal employee.sal%type;
   lv_newsal employee.sal%type;
   lv_perc_num  NUMBER;
   
 BEGIN   
  FOR rec_sal IN cur_sal LOOP
    --DISPLAYS THE CALCULATED SALARIES FOR ALL EMPLOYEES SANS PRESIDENT
    IF rec_sal.empno<>'7839' THEN 
      --DISCOVERS OLD SALARY
      lv_oldsal:=rec_sal.sal*12;
      --DISCOVERS RAISE AMOUNT AND SETS LIMIT OF 2000
      lv_perc_num:=lv_oldsal*:g_raise_num;
        IF lv_perc_num>2000 THEN lv_perc_num:=2000;  END IF;
      --DISCOVERS NEW SALARY
      lv_newsal:=lv_perc_num+lv_oldsal;

      --DISCOVERS TOTAL AMOUNT OF RAISES GIVEN
      ENTER CODE HERE!!!

    --DISPLAY THE DESIRED OUTPUTS
    DBMS_OUTPUT.PUT_LINE('Emp. Number: '||rec_sal.empno);
    DBMS_OUTPUT.PUT_LINE('Old Salary: '||lv_oldsal);
    DBMS_OUTPUT.PUT_LINE('Raise Amount: '||lv_perc_num);
    DBMS_OUTPUT.PUT_LINE('New Salary: '||lv_newsal);

    DBMS_OUTPUT.PUT_LINE('Total Amount of Increase: '||

    END IF;
  
  UPDATE employee
    SET sal=lv_newsal
    WHERE CURRENT OF cur_sal;
  END LOOP;


 END;
 /

There are statements that are not yet finished. I need to show the total of all raises given (which I labeled lv_perc_num). Can you do a sum like that? It's not like adding a column, I'm trying to add the sum of a certain variable in the block.

Report message to a moderator

Re: cursor for loop syntax issue [message #311159 is a reply to message #310954]

Thu, 03 April 2008 10:54

flyboy
Messages: 1903
Registered: November 2006

Senior Member

> I need to show the total of all raises given (which I labeled lv_perc_num).

So, in other words, you want the SUM of lv_perc_num for all updated employees?
If so, you may sum it in the block into new variable. However this value will be available after update of all employees.
It does not seem reasonable to write it after each updated employee, as it would not change.

Report message to a moderator

Previous Topic:	I want simple example of UTL_FILE package
Next Topic:	How to add days to a date without warnings?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 11 09:50:03 CST 2025