Syntax for Arrays, Loops in Oracle PL/SQL

Question

I am trying to solve a problem in PL/SQL, but I am new to the language. I decided to solve it as I would solve it in another language, and so I learned something about how SQL declares and utilizes arrays in this thread:

Oracle PL/SQL - How to create a simple array variable?

I followed that example, but my code doesn't run. The error message is unhelpful. In particular it references line 21 whereas my code block begins on line 54. Here it is, in case it makes sense by error code, somehow:

ORA-06550: line 21, column 7: PLS-00103: Encountered the symbol "IN" when expecting one of the following: := . ( @ % ; 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error.

And here is my code block. I am sure I made a bunch of mistakes since I am learning by code example here. If you could please point out any syntax or other errors I would very much appreciate it. Thank you

declare
    vISBNa books.ISBN%type := '1059831198';
    vISBNb books.ISBN%type := '0401140733';
    vISBNc books.ISBN%type := '4981341710';
    vISBNd books.ISBN%type := '8843172113';
    vCATEGORYtemp books.CATEGORY%type;
    vRETAILtemp books.RETAIL%type;
    type arry is varray(4) of books.ISBN%type;
    array arry := arry(vISBNa, vISBNb, vISBNc, vISBNd);
begin
   for i in 1..array.count loop
        select category
            into vCATEGORYtemp
            from books
            where ISBN = i;
        select retail
            into vRETAILtemp
            from books
            where ISBN = i;
        IF vCATEGORYtemp = 'COMPUTER' THEN
          vRETAILtemp := vRETAILtemp * 0.7;
        ELSIF vCATEGORYtemp = 'FITNESS' THEN
          vRETAILtemp := vRETAILtemp * 0.6; 
        ELSIF vCATEGORYtemp = 'BUSINESS' THEN
          vRETAILtemp := vRETAILtemp * 0.8;
        ELSE
          vRETAILtemp := vRETAILtemp * 0.9;
        END IF;            
       dbms_output.put_line(vRETAILtemp);
   end loop;
end;

Answer 1

The problem certainly has a trivial SQL solution, but since this is PL/SQL practice (specifically), here are a few suggestions for improvement. Note that the code is almost correct; in particular, it is not clear what, if anything, throws the exact error you mentioned.

In the loop, you can assign to both local variables in a single select statement, as shown below. Notice also the where clause, where you must compare ISBN from the table to array(i), not to i. (That is the only error I found!)

for i in 1 .. array.count loop
  select category, retail
    into vCATEGORYtemp, vRETAILtemp
  from   books
  where  ISBN = array(i);

Finally, the assignment to vRETAILtemp can be simplified, using a case expression (not a case statement, which is a construct very similar to the if... then... elsif... else... end construct):

vRETAILtemp := vRETAILtemp * case vCATEGORYtemp when 'COMPUTER' then 0.7
                                                when 'FITNESS'  then 0.6
                                                when 'BUSINESS' then 0.8
                                                                else 0.9 end;

Answer 2

There are plenty of errors in there :

• You have a type called arry but you have also a variable with the same name. PL/SQL doesn't like that.

• In the for loop : You are trying to loop over the type and not the array

  for i in 1..array.count loop 
  

it should be :

   for i in 1..arry.count loop

Generally speaking it's not common to use arrays in Oracle. Although it's possible but it's much more efficient to use SQL which is very simple and straightforward. Your code can be simplified to :

    declare         
        Cursor Cur is select case 
                            when vCATEGORYtemp = 'COMPUTER' then 0.7 
                            when vCATEGORYtemp = 'FITNESS' then 0.6
                            when vCATEGORYtemp = 'BUSINESS' then 0.8
                            else vCATEGORYtemp = 'COMPUTER' then 0.9
                          end * vRETAILtemp as Output
                    from books 
                    where ISBN in ('1059831198','0401140733','4981341710','8843172113');
        R Cur%Rowtype;
    begin
        Open Cur;
        loop
            fetch Cur into R;
            exit when Cur%notfound;
            
            dbms_output.put_line(R.Output);
        end loop;
        Close Cur;
    end ;

I think you have to review the basics of coding which are :

• Making the efforts of naming the objects properly. When you have two things called array and arry you save 0.5 seconds writing your code but you have great chances to spend hours trying to investigate what the problem is in case of a bug.

• In coding, we almost never write a big code and then check if it compiles. Is should be incremental especially as beginner. Write two lines and check it compiles, etc. Otherwise, it's complicated to know where the problem is coming from.

• In Oracle, you can use SQL Developer, and put your code inside a Function/Procedure or Package. At least in case of a compilation problem, it will tell you the right line which has a problem.

Hope this helps.

Answer 3

You can do this more simply (but still using PL/SQL for this learning exercise):

begin
    for r in (
        select * from books
    )
    loop
        r.retail := r.retail *
            case r.category
                when 'COMPUTER' then 0.7
                when 'FITNESS'  then 0.6
                when 'BUSINESS' then 0.8
                else 0.9
            end;

        dbms_output.put_line(r.isbn||' '||rpad(r.category,10)||' '||r.retail);
    end loop;
end;

Or using an array:

declare
    type book_tt is table of books%rowtype;
    l_books book_tt;
begin
    select *
    bulk collect into l_books
    from   books b;

    for i in 1..l_books.last loop
        l_books(i).retail := l_books(i).retail *
            case l_books(i).category
                when 'COMPUTER' then 0.7
                when 'FITNESS'  then 0.6
                when 'BUSINESS' then 0.8
                else 0.9
            end;

        dbms_output.put_line(l_books(i).isbn||' '||rpad(l_books(i).category,10)||' '||l_books(i).retail);
    end loop;
end;

(I've cheated a little bit by using select * and books%rowtype, because this is just an example and I am lazy. For real code it's usually better to list the actual columns you want explicitly.)

Test data:

create table books
( isbn varchar2(10)
, category varchar2(20)
, retail number(6,2) );

insert all
    into books values ( '1059831198', 'COMPUTER', 10)
    into books values ( '0401140733', 'CATS', 10)
    into books values ( '4981341710', 'FITNESS', 10)
    into books values ( '8843172113', 'BUSINESS', 10)
select * from dual;

Output:

1059831198 COMPUTER   7
0401140733 CATS       9
4981341710 FITNESS    6
8843172113 BUSINESS   8