Oracle 18c

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.