How to Write Lookup Query in PL/SQL

Here is a lookup query that is written in PL/SQL using concepts of cursor and an Associative array.

Here is Lookup PL/SQL Query

DECLARE
  CURSOR c1 IS
    SELECT prod_id,
           cust_id,
           time_id,
           amount_sold
    FROM   sales
    WHERE  amount_sold > 100;

l_country_names COUNTRY_NAMES_TYPE;
l_country_id countries.country_id%TYPE;
l_country_name countries.country_name%TYPE;
l_cust_first_name customers.cust_first_name%TYPE;
l_cust_last_name customers.cust_last_name%TYPE;
TYPE country_names_type
IS
  TABLE OF VARCHAR2(40) INDEX BY PLS_INTEGER;
  l_country_names COUNTRY_NAMES_TYPE;
BEGIN
  FOR c1_rec IN c1
  LOOP
    -- Query customer details
    SELECT cust_first_name,
           cust_last_name,
           country_id
    INTO   l_cust_first_name,
           l_cust_last_name,
           l_country_id
    FROM   customers
    WHERE  cust_id=c1_rec.cust_id;
    
    -- Check array first before executing a SQL statement
    IF ( l_country_names.EXISTS(l_country_id)) THEN
      l_country_name := L_country_names(l_country_id);
    ELSE
      SELECT country_name
      INTO   l_country_name
      FROM   countries
      WHERE  country_id = l_country_id;
      
      -- Store in the array for further reuse
      L_country_names(l_country_id) := l_country_name;
    END IF;
    --
    -- Insert in to target table
    --
    INSERT INTO top_sales_customers
                (
                            prod_id,
                            cust_id,
                            time_id,
                            cust_first_name,
                            cust_last_name,
                            amount_sold,
                            country_name
                )
                VALUES
                (
                            c1_rec.prod_id,
                            c1_rec.cust_id,
                            c1_rec.time_id,
                            l_cust_first_name,
                            l_cust_last_name,
                            c1_rec.amount_sold,
                            l_country_name
                );
  
  END LOOP;
  COMMIT;
END;/PL/SQL
PROCEDURE successfully completed.

The explantion

Here is a complete explanation for the above PL/SQL query.

• First, we declare a cursor on the Sales table. Later, we added an Array (Associative). We did these steps in DECLARE part.
• In the BEGIN section, what we did is we read records from the CUSTOMER table and put them into the Associative array.
• The IF condition validates country_id. If country_id is not there, it gets from the country_id table.
• Later, it inserts into the top_sales_customers table (records from the SALES and Associative Array).
• Overall it is tricky PL/SQL. And you can learn many points here.
• The Commit helps to complete the transaction.
• The END LOOP completes the loop, and it comes out of the loop when it is executed.

Related Posts

The Road to $1 Trillion and Beyond