Functions (Cont.)

Question: Write a function that finds the cname and city in table customers for a given cid and returns the information in a formatted string such as “Tiptop(Duluth).” 

 SQL> CREATE TABLE  customers1 (
   2    cid    CHAR(8),
   3    cname  CHAR(32),
   4    city   CHAR(32) );
 Table created.

 SQL> INSERT INTO  customers1  VALUES (
   2    '1234', 'Kids Next Door', 'Grand Forks' );
 1 row created.

 SQL> CREATE FUNCTION  namecity( custid CHAR )  RETURN CHAR AS
   2    custname  CHAR(32);
   3    custcity  CHAR(32);
   4  BEGIN
   5    SELECT  cname, city  INTO  custname, custcity
   6      FROM  customers1  WHERE  cid = custid;
   7    RETURN  rtrim( custname ) || ' (' || rtrim( custcity ) || ')';
   8  END;
   9  /
 Function created.

 SQL> VARIABLE  X  CHAR(64);

 SQL> BEGIN  :x := namecity( '1234' );  END;
   2  /
 PL/SQL procedure successfully completed.

 SQL> PRINT  :x;

 X
 ----------------------------------------------------
 Kids Next Door (Grand Forks)