-- getstore.sql

CREATE OR REPLACE FUNCTION getstore(cty VARCHAR2, st CHAR)
  RETURN NUMBER
AS
  snum NUMBER;
BEGIN
  SELECT store_number
    INTO snum
    FROM store
   WHERE city = cty AND state = st;
  RETURN snum;
END;
/

SELECT id
  FROM employee
 WHERE store_number = getstore('Las Vegas', 'NV')
/


