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  |
 |
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 #660533 is a reply to message #660531] |
Mon, 20 February 2017 06:45  |
Solomon Yakobson
Messages: 3270
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 |
|
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 
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 |
 |
Barbara Boehmer
Messages: 9086
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 |
|
Barbara Boehmer
Messages: 9086
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.
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 16:47:08 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
