5 Replies Latest reply: Dec 4, 2012 12:47 AM by Billy~Verreynne RSS

    Testing Procedure

      I have created a procedure to calculate a discount based on the order total. SQL plus tells me that the procedure is created successfully, but I am not getting any output.

      create or replace procedure discount is
      cursor c1 is
      select order_line.o_id, sum(inv_price*ol_quantity)
           from customer,inventory,order_line,orders
           where customer.c_id=orders.c_id
           and orders.o_id=order_line.o_id
           and inventory.inv_id=order_line.inv_id
           group by customer.c_id, c_first,c_mi,c_last;
           total number;
           order_id number;
           original_amt number;
      open c1;

      fetch c1 into order_id, original_amt;
           exit when c1%notfound;
                original_amt between 100 and 200
                total:= original_amt*.9;
                original_amt > 200
                total:= original_amt*.8;
           end if;
           dbms_output.put_line(order_id||' '||original_amt||' '||total);
           end loop;
      end discount;

      The output I am trying to get is:

      Order Id: Original_amt: Total:
      1 379.89 303.912
      2 15.99 0
      3 409.9 327.92
      4 119.9 107.91
      5 89.9 0
      6 349.84 279.872

      Edited by: MikeCoffey on Dec 3, 2012 2:35 PM
        • 1. Re: Testing Procedure
          OK, so you created the procedure. Did you execute the procedure? Did you
          SQL> set serveroutput on size unlimited;
          before executing the procedure to enable the DBMS_OUTPUT output?

          Can you copy and paste from a SQL*Plus session where you
          SQL> set serveroutput on size unlimited;
          SQL> exec discount;
          Of course, if this is real code, you would want your procedure to do something other than write output to the DBMS_OUTPUT buffer since there is no guarantee that the buffer exists or that the client application knows how to read from the buffer or that it has been instructed to read from the buffer.

          • 2. Re: Testing Procedure
            SQL> set serveroutput on size unlimited;
            SQL> exec discount;
            BEGIN discount; END;

            ERROR at line 1:
            ORA-00979: not a GROUP BY expression
            ORA-06512: at "SYSTEM.DISCOUNT", line 3
            ORA-06512: at "SYSTEM.DISCOUNT", line 16
            ORA-06512: at line 1

            I guess you could say that this is not "real" code. I am doing this for a school assignment.

            • 3. Re: Testing Procedure
              OK, that implies that the procedure had syntax errors when you created it. Are you sure that the procedure was created successfully? Or did you get a message after creating it that there were errors? If so, you can type
              SQL> show errors;
              in SQL*Plus to get a list of the errors.

              The query you are using for your cursor appears to be incorrect-- you cannot SELECT columns that you are not grouping by. My guess is that you really wanted the query to be
              select order_line.o_id, sum(inv_price*ol_quantity)
                from customer,inventory,order_line,orders
               where customer.c_id=orders.c_id
                 and orders.o_id=order_line.o_id
                 and inventory.inv_id=order_line.inv_id
               group by order_line.o_id;
              • 4. Re: Testing Procedure
                Thanks, it is working now.
                • 5. Re: Testing Procedure
                  Mike, keep in mind that the cursor-loop-fetch approach is not optimal by any means. It is (less than affectionately) known as slow-by-slow row based processing.

                  There is no need to fetch a row and use PL/SQL to process it - when SQL can do that for you. No PL/SQL in-between rows needed.

                  Have a look at the CASE statement of the SQL language - and consider using it instead of IF conditions in PL/SQL.

                  The basic approach to optimised and scalable Oracle development is:
                  Maximise SQL. Minimise PL/SQL (or Java or .Net).

                  In other words, solve database problem using the SQL language. It is extremely capable and very powerful. Use PL/SQL to deal with things like conditional processing and branching, exception handling, applying business logic and so on. It is should be pretty much an exception to pull a row into the PL/SQL engine at a time, from the SQL engine, to process using PL/SQL.