| 1 |
A stored procedure to query the phone-list database by a person's last or first name may look like this:
|
| 2 |
CREATE PROCEDURE get_phoneno_by_name
|
| 3 |
(name IN VARCHAR2) IS
|
| 4 |
CURSOR person_cur(cname IN VARCHAR2) IS
|
| 5 |
SELECT last_name,first_name,phone_no,phone_type
|
| 6 |
from person_info_table,phone_list_table
|
| 7 |
WHERE (person_info_table.person_id =
-
phone_list_table.person_id)
|
| 8 |
AND ( last_name LIKE ('%' || LOWER(cname) || '%')
|
| 9 |
OR first_name LIKE ('%' || LOWER(cname) || '%'));
|
| 10 |
Note that above we have SQL not PL/SQL and variables are column names from database
|
| 11 |
Argument cname of CURSOR will be supplied when we invoke later!
|
