Mega Code Archive
Select the first names for the Doe family from the Worker table
<![CDATA[
SQL>
SQL> create table employee (
2 id number,
3 employee_type_id number,
4 external_id varchar2(30),
5 first_name varchar2(30),
6 middle_name varchar2(30),
7 last_name varchar2(30),
8 name varchar2(100),
9 birth_date date ,
10 gender_id number );
Table created.
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
2
3 v_first_name employee.first_name%TYPE;
4 n_id employee.id%TYPE;
5
6 FUNCTION get_first_name(
7 aion_id in out employee.id%TYPE,
8 aiv_last_name in employee.last_name%TYPE)
9 return employee.first_name%TYPE is
10
11 v_first_name employee.first_name%TYPE;
12
13 begin
14 select id,first_name
15 into aion_id,v_first_name
16 from employee
17 where id > aion_id
18 and last_name like aiv_last_name||'%'
19 and rownum = 1;
20
21 return v_first_name;
22 exception
23 when NO_DATA_FOUND then
24 return v_first_name;
25 when OTHERS then
26 raise_application_error(-20001, SQLERRM||' on select employee'||' in show_worker');
27 end get_first_name;
28
29 begin
30 n_id := 0;
31 loop
32 v_first_name := get_first_name(n_id, 'DOE');
33 if v_first_name is NULL then
34 exit;
35 end if;
36 DBMS_OUTPUT.PUT_LINE(v_first_name);
37 end loop;
38 end;
39 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table employee;
Table dropped.
]]>