create table employees as select * from hr.employees
Table created.
Cursor for loop to get name and return it
create or replace function emp_last_name (
p_employee_id in number
) return varchar2 is
begin
for rec in (
select last_name
from employees
where employee_id = p_employee_id
) loop
return rec.last_name;
end loop;
end;
Function created.
The more usual select into to get the name
create or replace function emp_last_name (
p_employee_id in number
) return varchar2 is
l_last_name employees.last_name%type;
begin
select last_name
into l_last_name
from employees
where employee_id = p_employee_id;
return l_last_name;
end;
Function created.
Or you could use an explicit cursor
create or replace function emp_last_name (
p_employee_id in number
) return varchar2 is
cursor last_name_cur is
select last_name
from employees
where employee_id = p_employee_id;
l_last_name employees.last_name%type;
begin
open last_name_cur;
fetch last_name_cur into l_last_name;
return l_last_name;
end;
Function created.
But what about no_data_found and too_many_rows?
create or replace function emp_last_name (
p_employee_id in number
) return varchar2 is
begin
for rec in (
select last_name
from employees
where employee_id = p_employee_id
) loop
return rec.last_name;
-- no way to detect too many rows!
end loop;
-- ?
raise no_data_found;
end;
Function created.
create or replace function emp_last_name (
p_employee_id in number
) return varchar2 is
l_last_name employees.last_name%type;
begin
select last_name
into l_last_name
from employees
where employee_id = p_employee_id;
return l_last_name;
end;
Function created.
create or replace function emp_last_name (
p_employee_id in number
) return varchar2 is
cursor last_name_cur is
select last_name
from employees
where employee_id = p_employee_id;
l_last_name employees.last_name%type;
l_last_name2 employees.last_name%type;
begin
open last_name_cur;
fetch last_name_cur into l_last_name;
if last_name_cur%notfound then
raise no_data_found;
end if;
fetch last_name_cur into l_last_name2;
if last_name_cur%found then
raise too_many_rows;
end if;
return l_last_name;
end;
Function created.