Getting the corresponding record of a Cursor/Select when in a Cursor LOOP statement-CodePudding

The question seems easy. I have built a package, where there is a quite massive cursor, let's say on all invoices of my company for the whole year.

    CURSOR c_invoices(p_year IN INTEGER) IS
    SELECT     all_invoices.invoicenumber,   
               all_invoices.invoicedate,
               all_invoices.customernumber
    FROM       all_invoices
    WHERE      all_invoices.year = p_year 
    ;

After opening it and using a LOOP statement, I want to get some data from another table (forbidden_customers), but only if the customer is in this very last table.

What I'd like to do, is to open another cursor (or a SELECT ?) at the very beginning of my package, browsing the whole table(forbidden_customers), and then getting to the corresponding record when in my invoices LOOP.

So, something like :

        CURSOR     c_forbidden_customers IS
        SELECT     forbidden_customers.customernumber,   
                   forbidden_customers.customeradress
        FROM       forbidden_customers
        ;

And then :

OPEN    c_invoices(v_year);
        LOOP FETCH c_invoices INTO invoices_cursor;
        BEGIN
        EXIT WHEN c_invoices%NOTFOUND; 
             *IF invoices_cursor.customernumber IS FOUND IN c_forbidden_customers ...
                 THEN ...*

This is what I do meanwhile (I know it is bad):

SELECT COUNT(*)
INTO v_exist /*INTEGER*/
FROM forbidden_customers
WHERE forbidden_customers.customernumber= p_customernumber

IF v_exist <> 0 
    THEN...

I tried to make it as clear as possible. Thank you for your time

CodePudding user response：

Don't do it twice; join both tables in the same cursor and use it. Also, if you switch to a cursor FOR loop, you'll save yourself from some typing as Oracle will do most of boring stuff for you (declaring cursor variable, opening the cursor, closing it, exiting the loop ...):

create or replace procedure p_test (p_year in integer) is
begin
  for c_invoices in 
    (select a.invoicenumber,
            a.invoicedate,
            a.customernumber,
            c.customeraddress
     from all_invoices a join forbidden_customers c on c.customernumber = a.customernumber
     where a.year = p_year)
  loop
    -- do something
  end loop;
end;

CodePudding user response：

If the table forbidden_customers is not large and it will fit oracle's session memory, you can use a pl/sql table to store all id's from forbidden_customers and check it later. The check is done in memory only, so it is much faster than any regular select.

create table all_invoices
(id number,
year number,
customer_number number);

create table forbidden_customers
(customer_number number);

CREATE OR REPLACE TYPE t_number_table IS TABLE OF number
/

CREATE OR REPLACE PROCEDURE test23
IS
    forbidden_customers_list   t_number_table;

    CURSOR c_invoices (p_year IN INTEGER)
    IS
        SELECT all_invoices.customer_number
          FROM all_invoices
         WHERE all_invoices.year = p_year;
BEGIN
   SELECT customer_number
   BULK   COLLECT INTO forbidden_customers_list
   FROM   forbidden_customers;
   
    FOR rec_invoices  in c_invoices(2022) loop
        if forbidden_customers_list.exists(rec_invoices.customer_number) then
    null;
        end if;
    end loop;
end;
/