I'm using Oracle Database 11g Release 11.2.0.3.0.
I'd like to know the best practice for doing something like this in a PL/SQL block:
DECLARE
v_student_id student.student_id%TYPE;
BEGIN
SELECT student_id
INTO v_student_id
FROM student
WHERE last_name = 'Smith'
AND ROWNUM = 1;
END;
Of course, the problem here is that when there is no hit, the NO_DATA_FOUND exception is raised, which halts execution. So what if I want to continue in spite of the exception?
Yes, I could create a nested block with EXCEPTION section, etc., but that seems clunky for what seems to be a very simple task.
I've also seen this handled like this:
DECLARE
v_student_id student.student_id%TYPE;
CURSOR c_student_id IS
SELECT student_id
FROM student
WHERE last_name = 'Smith'
AND ROWNUM = 1;
BEGIN
OPEN c_student_id;
FETCH c_student_id INTO v_student_id;
IF c_student_id%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('not found');
ELSE
(do stuff)
END IF;
CLOSE c_student_id;
END;
But this still seems like killing an ant with a sledge hammer.
What's the best way?
Thanks for any help you can give.
Wayne