By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 |
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | 0 |
create table employee (ssn varchar2(4), salary numeric(6,2) );
create table works_hour (emp_ssn integer, pid integer, hours integer);
insert into employee(ssn, salary)
select '1011', 1000 from dual union all
select '1012', 1200 from dual union all
select '1013', 1300 from dual union all
select '1014', 1400 from dual union all
select '1015', 1500 from dual union all
select '1015', 1600 from dual ;
6 rows affected
insert into works_hour(emp_ssn, pid, hours)
select '1011',80, 60 from dual union all
select '1012',90, 40 from dual union all
select '1013',80, 70 from dual union all
select '1013',90, 65 from dual union all
select '1015',90, 80 from dual;
5 rows affected
commit;
create or replace
procedure increase_salary_for_excessive_hours(p_ssn in employee.ssn%type) as
begin
update employee e
set salary = salary * 1.1
where e.ssn = p_ssn
and exists (select null
from works_hour w
where w.emp_ssn = e.ssn
and w.hours >= 60
);
if sql%rowcount < 1 then
raise no_data_found;
elsif sql%rowcount > 1 then
raise too_many_rows;
end if;
end increase_salary_for_excessive_hours;
/
select * from employee;
| SSN | SALARY |
|---|---|
| 1011 | 1000 |
| 1012 | 1200 |
| 1013 | 1300 |
| 1014 | 1400 |
| 1015 | 1500 |
| 1015 | 1600 |
begin
increase_salary_for_excessive_hours('1011');
end ;
/
1 rows affected
begin
increase_salary_for_excessive_hours('1012');
end ;
/
ORA-01403: no data found
ORA-06512: at "FIDDLE_TEWXKMPBKZFLDBKTAQVL.INCREASE_SALARY_FOR_EXCESSIVE_HOURS", line 13
ORA-06512: at line 2
begin
increase_salary_for_excessive_hours('1013');
end ;
/
1 rows affected
begin
increase_salary_for_excessive_hours('1014');
end ;
/
ORA-01403: no data found
ORA-06512: at "FIDDLE_TEWXKMPBKZFLDBKTAQVL.INCREASE_SALARY_FOR_EXCESSIVE_HOURS", line 13
ORA-06512: at line 2
begin
increase_salary_for_excessive_hours('1015');
end ;
/
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "FIDDLE_TEWXKMPBKZFLDBKTAQVL.INCREASE_SALARY_FOR_EXCESSIVE_HOURS", line 15
ORA-06512: at line 2
select * from employee;
| SSN | SALARY |
|---|---|
| 1011 | 1100 |
| 1012 | 1200 |
| 1013 | 1430 |
| 1014 | 1400 |
| 1015 | 1500 |
| 1015 | 1600 |
-- reset direst call update
rollback;
-- Test harnest routine for calling increase_salary_for_excessive_hours with display
create or replace
procedure test_salary_increase(p_ssn in employee.ssn%type) as
cursor c_emp_60_or_more is
select e.ssn, e.salary, w.hours
from employee e
join works_hour w -- inner join ON not natural join
on (w.emp_ssn = e.ssn)
where e.ssn = p_ssn;
begin
dbms_output.put_line('*** Before Procedure call:');
for cur_rec in c_emp_60_or_more
loop
dbms_output.put_line('Employee_ssn :' || cur_rec.ssn);
dbms_output.put_line('Employee_sal :' || cur_rec.salary);
dbms_output.put_line('Work_hours :' || cur_rec.hours);
end loop;
-- call increase_salary_for_excessive_hours
increase_salary_for_excessive_hours(p_ssn);
dbms_output.put_line('*** After Procedure call:');
for cur_rec in c_emp_60_or_more
loop
dbms_output.put_line('Employee_ssn :' || cur_rec.ssn);
dbms_output.put_line('Employee_sal :' || cur_rec.salary);
dbms_output.put_line('Work_hours :' || cur_rec.hours);
end loop;
exception
when no_data_found then
dbms_output.put_line('No data found.');
when too_many_rows then
dbms_output.put_line('Many rows fetched.');
end test_salary_increase;
begin
dbms_output.enable(); --- for fiddle
test_salary_increase('1011');
end ;
/
1 rows affected
dbms_output:
*** Before Procedure call:
Employee_ssn :1011
Employee_sal :1000
Work_hours :60
*** After Procedure call:
Employee_ssn :1011
Employee_sal :1100
Work_hours :60
begin
dbms_output.enable(); --- for fiddle
test_salary_increase('1012');
end ;
/
1 rows affected
dbms_output:
*** Before Procedure call:
Employee_ssn :1012
Employee_sal :1200
Work_hours :40
No data found.
begin
dbms_output.enable(); --- for fiddle
test_salary_increase('1013');
end ;
/
1 rows affected
dbms_output:
*** Before Procedure call:
Employee_ssn :1013
Employee_sal :1300
Work_hours :70
Employee_ssn :1013
Employee_sal :1300
Work_hours :65
*** After Procedure call:
Employee_ssn :1013
Employee_sal :1430
Work_hours :70
Employee_ssn :1013
Employee_sal :1430
Work_hours :65
begin
dbms_output.enable(); --- for fiddle
test_salary_increase('1014');
end ;
/
1 rows affected
dbms_output:
*** Before Procedure call:
No data found.
begin
dbms_output.enable(); --- for fiddle
test_salary_increase('1015');
end ;
/
1 rows affected
dbms_output:
*** Before Procedure call:
Employee_ssn :1015
Employee_sal :1500
Work_hours :80
Employee_ssn :1015
Employee_sal :1600
Work_hours :80
Many rows fetched.