| net salary calculation [message #651074] |
Tue, 10 May 2016 20:16  |
 |
nath123
Messages: 19
Registered: May 2016
Location: united states
|
Junior Member |
|
|
i want to get the net salary for employees whose id are less than 140
ORA-01422 is the error , how should i alter the code ?
declare
ename varchar(31);
basic number(8,2);
salary number(8,2);
--salary1 number;
PF number;
HRA number;
DA number;
net number;
begin
select last_name,salary into ename, basic from employees where employee_id <140;
basic := salary;
DA := salary*(0.3);
HRA :=salary*(.10);
if (salary <8000) then PF :=salary *(0.07);
elsif( salary>=8000 and salary <=16000) then PF:=salary*(.16);
elsIF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('SQL DATA NOT FOUND');
end if;
net := DA+HRA+basic-PF;
dbms_output.put_line(net);
dbms_output.put_line(ename);
end
;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: net salary calculation [message #651087 is a reply to message #651078] |
Wed, 11 May 2016 00:23  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:I see the plsql procedure is correct ,but no result
First make sure you executed:
Format your code means INDENT it.
If you don't write readable code, you will never write correct code.
Then all your figures are NULL and so you don't see anything:
declare
ename varchar(31); <--- value NULL
basic number(8,2); <--- value NULL
salary number(8,2); <--- value NULL
--salary1 number;
PF number; <--- value NULL
HRA number; <--- value NULL
DA number; <--- value NULL
net number; <--- value NULL
cursor c1 is
select last_name,salary into ename, basic from employees where employee_id <110;
begin
basic := salary; <--- value NULL
DA := salary*(0.3); <--- value NULL
HRA :=salary*(.10); <--- value NULL
for i in c1 loop
if (salary <8000) then PF :=salary *(0.07); <--- test NULL = NOT TRUE
elsif( salary>=8000 and salary <=16000) then PF:=salary*(.16); <--- test NULL = NOT TRUE
elsIF SQL%NOTFOUND THEN <--- test FALSE, you can never reach this
DBMS_OUTPUT.PUT_LINE('SQL DATA NOT FOUND'); as cursor loop only returns existing rows
end if;
net := DA+HRA+basic-PF; <--- value NULL
end loop;
dbms_output.put_line(net); <--- display NULL, so nothing
dbms_output.put_line(ename); <--- display NULL, so nothing
end
;
Values returned by the cursor are found in i record and so are named i.last_name and i.salary.
[Updated on: Wed, 11 May 2016 02:19] Report message to a moderator |
|
|
|
|
|
|
|
| Re: net salary calculation [message #651151 is a reply to message #651125] |
Thu, 12 May 2016 03:15 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
As Michel already pointed out SQL%NOTFOUND can't be true for a FOR loop. Also into is completely pointless in a cursor declartion. And finally all those calculations can be done in one hit in the cursor.
DECLARE
CURSOR c_emp_net IS
SELECT last_name,
salary
+ (salary * 0.3)
+ (salary * 0.1)
- (CASE WHEN i.salary < 4000 THEN salary *(0.07)
WHEN salary >= 4000 AND i.salary <= 24000) THEN salary * (0.16)
ELSE 0
END) AS net
FROM employees WHERE employee_id < 102;
BEGIN
FOR i IN c_emp_net LOOP
dbms_output.put_line(i.net);
dbms_output.put_line(i.last_name);
END LOOP;
END
;
You need to think about what happens if salary is > 24000.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
