Youll use the select statement the most

Chapter 4 ■ Single row proCeSSing

34 and rownum = 1;
35
36 return v_first_name;
37 exception
38 when NO_DATA_FOUND then
39 return v_first_name;
40 when OTHERS then
41 raise_application_error(-20001, SQLERRM|| 42 ' on select WORKERS'||
43 ' in show_worker');
44 end get_first_name;
45
46 begin
47 -- Keep track of the primary key so you
48 -- only retrieve the SELECTed row once
49 n_id := 0;
50 -- Loop until there's NO_DATA_FOUND
51 loop
52 -- get the first name from the local function 53 v_first_name := get_first_name(n_id, 'DOE'); 54 -- detect NO_DATA_FOUND
55 if v_first_name is NULL then
56 exit; -- Exit the loop
57 end if;
58 -- show the first_name
59 pl(v_first_name);
60 end loop;
61 end;

•� Predetect the presence of a matching row

Let’s review what you’ve learned.

Table 4-1. Singleton Results for INSERT, UPDATE, DELETE, and SELECT

When you’re working with the INSERT INTO and SELECT...INTO statements, you may as well expect an exception and write your PL/SQL accordingly. Of the four singletons, you’ll use the SELECT statement the most. After all, you are working with a database.