Posted By

stews on 09/17/08


Tagged

query first


Versions (?)

Function to get first record from query and return single value


 / Published in: PL/SQL
 

Nothing amazing here, just an efficient way to get the first row.

Be sure to set your ORDER BY clause properly.

  1. -- Person may have multiple addresses. Gets FIRST record only
  2. CREATE OR REPLACE FUNCTION get_first_address(person_id_in IN VARCHAR2) RETURN VARCHAR2 IS
  3. ret_value VARCHAR2(1024) := ' ';
  4.  
  5. CURSOR the_cur(l_person VARCHAR2) IS
  6. SELECT street1 || ', ' || street2
  7. FROM address
  8. WHERE person_id = l_person
  9. ORDER BY date_added;
  10. OPEN the_cur(l_person);
  11.  
  12. FETCH the_cur
  13. INTO ret_value;
  14.  
  15. CLOSE the_cur;
  16.  
  17. RETURN ret_value;
  18. RETURN ret_value;
  19. DBMS_OUTPUT.PUT_LINE('EXCEPTION IN get_first_address - ' || SQLCODE || ': ' || SQLERRM);
  20. RETURN ret_value;
  21. END get_first_address;

Report this snippet  

You need to login to post a comment.