Advanced Search

Function to get first record from query and return single value

/ Published in: PL/SQL
Save to your folder(s)
Nothing amazing here, just an efficient way to get the first row.

Be sure to set your ORDER BY clause properly.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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. BEGIN
  11.     OPEN the_cur(l_person);
  12.  
  13.     FETCH the_cur
  14.         INTO ret_value;
  15.  
  16.     CLOSE the_cur;
  17.  
  18.     RETURN ret_value;
  19. EXCEPTION
  20.     WHEN NO_DATA_FOUND THEN
  21.         RETURN ret_value;
  22.     WHEN OTHERS THEN
  23.         DBMS_OUTPUT.PUT_LINE('EXCEPTION IN get_first_address - ' || SQLCODE || ': ' || SQLERRM);
  24.         RETURN ret_value;
  25. END get_first_address;

Report this snippet


Comments

You need to login to view comments.