Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL - Update in Loop (Oracle 11g)
PLSQL - Update in Loop [message #660529] Mon, 20 February 2017 05:40 Go to next message
wmpl
Messages: 2
Registered: February 2017
Junior Member
Hello experts,

please help me to solve such exercise: I would like to update salary of one employee (ex. ID 100) by 10% until it reaches 40.000 and would like to see all updates. I would like to do it using 'IF ... then' and basic loop. I finished basic traning in SQL/PLSQL, know how to code basic loops and DML, but don't know how to use it both. I simplified this exercise, the original one is more complicated (will try to do it later). I think I do sth wrong with salary / v_salary. I declare variables for loop, but use direct atributes for Update. How to connect it ? When I tried to use in 'begin section' just atribute 'salary' (without dbms_output..., I would check the final salary using SQL), error occured, that salary needs to be declared. Please help.


set serveroutput on;
declare
 v_salary employees.salary%TYPE;
begin
 select salary into v_salary from employees where employee_id = 100;
 if v_salary >= 40000 then null;
   else
    loop
      update employees
       set v_salary = v_salary * 1.1;
         exit when v_salary >= 40000;
     dbms_output.put_line ('Salary updated to :'||to_char(v_salary));
    end loop;
 end if;
end;
/




Error report -
ORA-06550: line 9, column 13:
PL/SQL: ORA-00904: "V_SALARY": invalid identifier
ORA-06550: line 8, column 8:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
Re: PLSQL - Update in Loop [message #660531 is a reply to message #660529] Mon, 20 February 2017 06:26 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
update employees
set v_salary = v_salary * 1.1;


SY.
Re: PLSQL - Update in Loop [message #660533 is a reply to message #660531] Mon, 20 February 2017 06:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
And what if salary is less than 40,000 while salary * 1.1 is > 40,000? Where do we stop then. Anyway, there is no need to update in a loop. Depending to answer to my question:

set serveroutput on;
declare
    v_salary     employees.salary%TYPE;
begin
    select  salary
      into  v_salary
      from  employees
      where employee_id = 100;
    dbms_output.put_line ('Initial salary is: ' || to_char(v_salary));
    if v_salary < 40000
      then
        loop
          exit when v_salary > 40000;
          v_salary := v_salary * 1.1;
        end loop;
        update employees
           set salary = v_salary;
        dbms_output.put_line ('Salary updated to: ' || to_char(v_salary));
    end if;
end;
/
Initial salary is: 24000
Salary updated to: 42517.46

PL/SQL procedure successfully completed.

SQL> 

set serveroutput on;
declare
    v_salary     employees.salary%TYPE;
begin
    select  salary
      into  v_salary
      from  employees
      where employee_id = 100;
    dbms_output.put_line ('Initial salary is: ' || to_char(v_salary));
    if v_salary < 40000
      then
        loop
          exit when v_salary * 1.1 > 40000;
          v_salary := v_salary * 1.1;
        end loop;
        update employees
           set salary = v_salary;
        dbms_output.put_line ('Salary updated to: ' || to_char(v_salary));
    end if;
end;
/
Initial salary is: 24000
Salary updated to: 38652.24

PL/SQL procedure successfully completed.

SQL> 

SY.

[Updated on: Mon, 20 February 2017 06:50]

Report message to a moderator

Re: PLSQL - Update in Loop [message #660597 is a reply to message #660533] Tue, 21 February 2017 20:46 Go to previous messageGo to next message
wmpl
Messages: 2
Registered: February 2017
Junior Member
Hey Solomon, THX for help ! set salary = v_salary , it was the key. I only put "exit when v_salary >= 40000;" at the end. First i receive info about all updates, second launch returns the info, that salary is higher than 40k and can't be updated. I did it also with while loop.

Unclear for me is: in dbms_output.put_line , should I always put "to_char" ? I've read, that dbms_output returns VARCHAR. But i can see that it also works with NUMBER Confused

Second thing. If I write code, for example, for circle area: I lanuch it and need to choose radius (&n), where should i include &n ? In declare or begin section ? Both works, but when i test it in declare section, a window of radius appears twice lol (or sth is wrong with my sql developer).

Declare
    pi constant number(3,2) := 3.14;
    radius number(7) := &n;      <<<<<<------------
    area number(13,2);
    perimeter number(13,2);
Begin
    radius := &n;   <<<<<<---------------
    perimeter:=2*pi*radius;
    area := pi* power(radius,2);
DBMS_OUTPUT.PUT_LINE('Area'||area||' '||'perimeter'||perimeter);
end;
/


set serveroutput on;
declare
  v_salary employees.salary%TYPE;
begin
  select salary into v_salary from employees where employee_id = 100;
  if v_salary >= 40000 then null;
     dbms_output.put_line ('Salary is: '||to_char(v_salary)||'. Update not possible.'); 
    else
      loop
       v_salary := v_salary * 1.1;
         update employees
         set salary = v_salary;
           dbms_output.put_line ('Updated salary :'||to_char(v_salary));
        exit when v_salary >= 40000;
      end loop;
  end if;
end;
/



set serveroutput on;
declare
  v_salary employees.salary%TYPE;
begin
  select salary into v_salary from employees where employee_id = 100;
     if v_salary >= 40000 then null;
      dbms_output.put_line ('Salary is: '||to_char(v_salary)||'. Update not possible.'); 
     else
       while v_salary < 40000 loop
         v_salary := v_salary * 1.1;
           update employees
             set salary = v_salary;
           dbms_output.put_line ('Updated salary :'||to_char(v_salary));
       end loop;
     end if;
end;
/
Re: PLSQL - Update in Loop [message #660600 is a reply to message #660597] Tue, 21 February 2017 23:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8898
Registered: November 2002
Location: California, USA
Senior Member
wmpl wrote on Tue, 21 February 2017 18:46

...If I write code, for example, for circle area: I lanuch it and need to choose radius (&n), where should i include &n ? In declare or begin section ? Both works, but when i test it in declare section, a window of radius appears twice lol (or sth is wrong with my sql developer).

You can put it in either place, but not both. When testing, even if the line is commented out, if there is an & it will prompt for input.

SCOTT@orcl_12.1.0.2.0> Declare
  2  	 pi constant number(3,2) := 3.14;
  3  	 radius number(7) := &n;
  4  	 area number(13,2);
  5  	 perimeter number(13,2);
  6  Begin
  7  	 perimeter:=2*pi*radius;
  8  	 area := pi* power(radius,2);
  9  	 DBMS_OUTPUT.PUT_LINE('Area'||area||' '||'perimeter'||perimeter);
 10  end;
 11  /
Enter value for n: 10
old   3:     radius number(7) := &n;
new   3:     radius number(7) := 10;
Area314 perimeter62.8

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> Declare
  2  	 pi constant number(3,2) := 3.14;
  3  	 radius number(7);
  4  	 area number(13,2);
  5  	 perimeter number(13,2);
  6  Begin
  7  	 radius := &n;
  8  	 perimeter:=2*pi*radius;
  9  	 area := pi* power(radius,2);
 10  	 DBMS_OUTPUT.PUT_LINE('Area'||area||' '||'perimeter'||perimeter);
 11  end;
 12  /
Enter value for n: 10
old   7:     radius := &n;
new   7:     radius := 10;
Area314 perimeter62.8

PL/SQL procedure successfully completed.

Re: PLSQL - Update in Loop [message #660601 is a reply to message #660597] Tue, 21 February 2017 23:23 Go to previous message
Barbara Boehmer
Messages: 8898
Registered: November 2002
Location: California, USA
Senior Member
wmpl wrote on Tue, 21 February 2017 18:46

...Unclear for me is: in dbms_output.put_line , should I always put "to_char" ? I've read, that dbms_output returns VARCHAR. But i can see that it also works with NUMBER :?...

In many cases, Oracle will do implicit conversions from one data type to another. So, it may convert the number to varchar2. However, as a general rule, most recommend not relying on implicit conversions and explicitly converting to the proper data type.
Previous Topic: Flashback techniues
Next Topic: Update based on grouping
Goto Forum:
  


Current Time: Tue Oct 23 10:48:17 CDT 2018