To Search A Record Using Functions

SQL> create table phonebook(phone_no number(10),user_name varchar(20),address varechar(30));
Table created.

SQL> insert into phonebook values(&no,'&name','&add');
Enter value for no: 2249616
Enter value for name: xxx
Enter value for add: asdf
old   1: insert into phonebook values(&no,'&name','&add')
new   1: insert into phonebook values(001,'xxx','vellore')
1 row created.
SQL> /
Enter value for no: 234567
Enter value for name: yyy
Enter value for add: asdfg
old   1: insert into phonebook values(&no,'&name','&add')
new   1: insert into phonebook values(234567,'yyy','madurai')
1 row created.
SQL> /
Enter value for no: 34567
Enter value for name: zzzz
Enter value for add: asdfghjk
old   1: insert into phonebook values(&no,'&name','&add')
new   1: insert into phonebook values(34567,'zzzz','chennai')
1 row created.
SQL> set serveroutput on

// Function decleration
SQL> create or replace function search(no in number) return varchar is ad varchar(30);
  2   begin
  3   select address into ad from phonebook where phone_no=no;
  4   return ad;
  5   end;
  6  /

Function created.

//function call
SQL>  set serveroutput on

SQL>  declare addd varchar(20);

  2   begin

  3   select search(phone_no) into addd from phonebook where phone_no =2249616;

  4   dbms_output.put_line(addd);

  5   end;

  6   /

PL/SQL procedure successfully completed.

SQL> select * from phonebook;

  PHONE_NO USER_NAME            ADDRESS

---------- -------------------- ------------------------------

  2249616  xxx                  vellore