How to select Top 1 record from Oracle by using PL/SQL functions.

How to get Top 1 record from Oracle by using PL/SQL

This article explains how to use Oracle functions to get top 1 record by using PL/SQL.

The code on this page shows how to use Oracle PL/SQL to retrieve the top 1 record. Here is what we're going to explain.

How to get the top value from a table. Different SQL syntax for Oracle 8i, 9i, 10g, 11g, and above.
How to get a value from a single cell of a table by Oracle functions.
The PL/SQL approach of using implicit cursor and explicit cursor.

To test functions on this page, please use the Oracle Northwind database that was converted from the popular Access Northwind database.

Function 1:

This function uses the SELECT...INTO command to get the latest order date for a customer by using an implicit cursor.

SELECT...INTO command is a type of hidden cursor in Oracle PL/SQL (called implicit cursor) that allows us to skip the steps that explicit cursor uses, eg. declare the cursor, open the cursor, process its rows in a loop, then closed it.

The following function can be used for Oracle 8i and above.

function get_latest_order_date(i_curstomer_id in number) return date
is
         
v_order_date date;
   
begin             
    select Order_Date into v_order_date
    from
    (
        select Order_Date
        from orders
        where customer_id = i_curstomer_id            
        order by Order_Date desc
    )
    where rownum < 2;  -- This guarantees there is only one row is returned.
    
    return v_order_date;

exception
   when no_data_found then return to_date('01/01/1900', 'DD/MM/YYYY');
end;

The following function can be used for Oracle 9i and above.

function get_latest_order_date(i_curstomer_id in number) return date
is
         
v_order_date date;
   
begin         
    select Order_Date into v_order_date
    from
    (
        select Order_Date,
            ROW_NUMBER() over (order by Order_Date desc) as row_number
        from orders
        where customer_id = i_curstomer_id            
        order by Order_Date desc
    )
    where row_number < 2;  -- This guarantees there is only one row is returned.
    
    return v_order_date;

exception
   when no_data_found then return to_date('01/01/1900', 'DD/MM/YYYY');
end;

To test this function, use this code snippet.

declare
v_latest_order_date date;

begin
    v_latest_order_date := pkg_task.get_latest_order_date(35);
    
    dbms_output.put_line (v_latest_order_date);
end;

SELECT...INTO raises an exception when either of the following two conditions occur.

If the underlying query returns more than one row, it raises TOO_MANY_ROWS exception.
The actual Oracle error message is: "ORA-01422: exact fetch returns more than requested number of rows."
If it returns no rows, it raises the NO_DATA_FOUND exception.

Function 2:

This function demonstrates how to get the latest order date by using an explicit cursor. Advantage of using explicit cursor is that we can test if the latest order date is a NULL value. If it is a NULL value, it indicates there is no data found. This is a better and concise way than using no_data_found in exception block as shown in the Function 1 above, especially when your function or procedure is getting very complex. Because we don't use a loop for the explicit cursor in the function, it always returns the first record in the cursor. This is default behavior.

function get_latest_order_date2(i_curstomer_id in number) return date
is
         
v_order_date date;

cursor latest_order_date(i_curstomer_id in number) 
is
    select Order_Date
    from orders
    where customer_id = i_curstomer_id
    order by Order_Date desc;

begin         
    open latest_order_date(i_curstomer_id);
    
    -- No loop is used so it only fetches the first row.
    fetch latest_order_date into v_order_date;
    
    -- Here we test NULL value to see if there are data found.
    if v_order_date is null then
        return to_date('01/01/1900', 'DD/MM/YYYY');
    else
        return v_order_date;
    end if;

    close latest_order_date;
    
    return v_order_date;
end;

To test this function, use the following code snippet.

declare
v_latest_order_date date;

begin
    v_latest_order_date := pkg_task.get_latest_order_date2(3503473);
    
    dbms_output.put_line (v_latest_order_date);
end;

Function 3:

This function is a slightly modified version from function get_latest_order_date2 above. In this example, only one fetch is necessary, so there is no need for a loop. However, the function must test to see if a matching record is found before it returns a value. This is done by using the FOUND attribute.

function get_latest_order_date3(i_curstomer_id in number) return date
is
         
v_order_date date;

cursor latest_order_date(i_curstomer_id in number) 
is
    select Order_Date
    from orders
    where customer_id = i_curstomer_id        
    order by Order_Date desc;

begin             
    open latest_order_date(i_curstomer_id);
    
    -- When no loop is used it always fetches the first row.
    fetch latest_order_date into v_order_date;
    
    -- Test to see if a matching record can be found.
    if latest_order_date%FOUND then 
        return v_order_date;
    else
        return to_date('01/01/1900', 'DD/MM/YYYY');
    end if;

    close latest_order_date;
end;

To test this function, use the following code snippet.

declare
v_latest_order_date date;

begin
    v_latest_order_date := pkg_task.get_latest_order_date3(3503473);
    
    dbms_output.put_line (v_latest_order_date);
end;

Other Recent Articles from the Database SQL category:

1.	Get single records when duplicate records exist
2.	Find duplicate values or non-duplicate values in a table
3.	How to get Top N rows from Oracle by using SQL
4.	How the data types in Access Northwind are converted to Oracle
5.	How to do cross table update in Oracle
6.	Export Northwind Access database to MySQL via ODBC

	Custom Search