My Oracle SQL Code is compiling my block of code that uses a cursor for a loop from two tables but t-CodePudding

I am trying to write a block of SQL code that compares two dates from two tables. One table shows when an item was acquired (ci_acquired_date). Another table shows when an item was an assigned to an employee (date_acquired). My goal is to use a loop that loops through the inventory and compare the dates and see if any of the dates assigned to an employee is less than the date the item was acquired (because it is impossible to assign an employee an item that was never bought) and use DBMS to show me which item ID it is and the dates it was acquired and assigned.

This is my code:

declare
cursor task_five is 
select a.date_assigned, a.ci_inv_id, b.ci_acquired_date 
from jaherna42.employee_ci a 
join jaherna42.ci_inventory b
on a.ci_inv_id = b.ci_inv_id
where a.user_or_support = 'USER';

row_one jaherna42.employee_ci%rowtype;
row_two jaherna42.ci_inventory%rowtype;
begin
    for row_one in task_five 
    loop
    
        if(row_one.date_assigned < row_two.ci_acquired_date)
    then 
    dbms_output.put_line('The error is: ' || row_one.ci_inv_id);
    dbms_output.put_line('The date assigned is: ' || row_one.date_assigned);
    dbms_output.put_line('The date acquired is: ' || row_two.ci_acquired_date);
    end if;
end loop;
end;

When I run it, the script output box would show

PL/SQL procedure successfully completed.

But there would be nothing showing up in my dbms output box. What is the error?

CodePudding user response：

row_two is a local variable that is declared but no value is ever assigned to it. Thus, every field in the record is always null. When you compare a value row_one.date_assigned against a null, the comparison will always evaluate to false. So your if statement is always false and nothing will ever be printed.

I'm a bit surprised that the code you posted compiles. row_one is declared as a jaherna42.employee_ci%rowtype but the query you've defined doesn't appear to return all the columns from employee_ci. Perhaps you're just lucky that the order and types of the columns in your table matches the order and types in your query even though that query pulls data from multiple tables. But that's a lucky accident.

If you want to keep the cursor in the declaration section, you'd almost certainly want your local variable to be declared against the cursor's %rowtype not the table's. So something like this is potentially what you're really after.

declare
  cursor task_five 
  is 
    select a.date_assigned, a.ci_inv_id, b.ci_acquired_date 
      from jaherna42.employee_ci a 
           join jaherna42.ci_inventory b
             on a.ci_inv_id = b.ci_inv_id
     where a.user_or_support = 'USER';

  row task_five%rowtype;
begin
  for row in task_five 
  loop
    if(row.date_assigned < row.ci_acquired_date)
    then 
      dbms_output.put_line('The error is: ' || row.ci_inv_id);
      dbms_output.put_line('The date assigned is: ' || row.date_assigned);
      dbms_output.put_line('The date acquired is: ' || row.ci_acquired_date);
    end if;
  end loop;
end;

Unless it's a requirement of your assignment, though, it would probably make sense to avoid the declarations entirely and just use an implicit cursor in the code. And to do the comparison in SQL rather than PL/SQL

begin
  for row in (select a.date_assigned, a.ci_inv_id, b.ci_acquired_date 
                from jaherna42.employee_ci a 
                     join jaherna42.ci_inventory b
                       on a.ci_inv_id = b.ci_inv_id
               where a.user_or_support = 'USER'
                 and a.date_assigned < b.ci_acquired_date)
  loop
    dbms_output.put_line('The error is: ' || row.ci_inv_id);
    dbms_output.put_line('The date assigned is: ' || row.date_assigned);
    dbms_output.put_line('The date acquired is: ' || row.ci_acquired_date);
  end loop;
end;

I'd also generally suggest using meaningful aliases in your query. Aliasing tables to a and b is like using single character variable names-- unlikely to help someone reading the code understand what it is doing. It would almost certainly be more useful to use some consistent naming convention where, say, employee_ci is always aliased to emp and ci_inventory is always aliased to inv.