CREATE PACKAGE BODY EXAMPLE is
-
CREATE PROCEDURE get_phoneno_by_name(name IN VARCHAR2) IS
-
CURSOR person_cur(cname IN VARCHAR2) IS
-
SELECT last_name,first_name,phone_no,phone_type
-
from person_info_table,phone_list_table WHERE
-
(person_info_table.person_id = phone_list_table.person_id) AND
-
(last_name LIKE ('%' || LOWER(cname) || '%' ) OR
-
first_name LIKE ('%' || LOWER(cname) || '%'));
-
lname person_info_table.last_name%TYPE; -- a variable to hold last name
-
fname person_info_table.first_name%TYPE; -- a variable to hold first name
-
phone phone_list_table.phone_no%TYPE; -- a variable to hold phone no.
-
ptype phone_list_table.phone_type%TYPE; -- a variable to hold phone type
|
