CODEWITHSUNDEEP
stored-proceduresplsqlcursororacle-apex
Giles Bonner
Giles Bonner

Reputation: 55

Stored procedure that uses cursor

I made a procedure that uses a cursor to generate a report. It is designed to return products with p_qoh > avg(p_qoh).

When i run the cursor on its own outside of the procedure, APEX tells me

PLS-00204: function or pseudo-column 'AVG' may be used inside a SQL statement only

How is this not inside a SQL statement? Im new to SQL just a required class as a comp sci major.

Heres the whole block. If you run just the cursor you will see what I mean.

CREATE OR REPLACE PROCEDURE prod_rep
IS 
  CURSOR cur_qoh IS
    SELECT p_qoh, p_descript, p_code
      FROM xx_product;

  TYPE type_prod IS RECORD(
    prod_qoh    xx_product.p_qoh%TYPE,
    prod_code   xx_product.p_code%TYPE,
    prod_descr  xx_product.p_descript%TYPE);

  rec_prod type_prod;
BEGIN
  OPEN cur_qoh;
  LOOP
    FETCH cur_qoh INTO rec_prod;
    EXIT WHEN cur_qoh%NOTFOUND;

    IF rec_prod.prod_qoh > avg(rec_prod.prod_qoh) THEN
       DBMS_OUTPUT.PUT_LINE(rec_prod.prod_code||' -> '||rec_prod.prod_desc);
    END IF;
  END LOOP;
  CLOSE cur_qoh;
END;

UPDATE: working block

BEGIN 
 FOR cur_r IN (SELECT p_qoh, p_descript, p_code FROM xx_product
        WHERE p_qoh > (SELECT avg(p_qoh) FROM xx_product))
LOOP
  DBMS_OUTPUT.PUT_LINE(cur_r.p_code||' -> '|| cur_r.p_descript);
 END LOOP;
END;

Upvotes: 1

Views: 206

Answers (2)

Alex Zen
Alex Zen

Reputation: 926

At that point you're into PL/SQL, not SQL. You could use the analytic function AVG into your SQL query to get the average:

CURSOR cur_qoh IS
SELECT p_qoh, p_descript, p_code, AVG(p_goh) OVER () as p_goh_avg
  FROM xx_product;

You can add your new field to type_prod:

  TYPE type_prod IS RECORD(
    prod_qoh    xx_product.p_qoh%TYPE,
    prod_code   xx_product.p_code%TYPE,
    prod_descr  xx_product.p_descript%TYPE,
    prod_avg    xx_product.p_qoh%TYPE)
   ;

And then you can use your average into the LOOP:

IF rec_prod.prod_qoh > rec_prod.prod_avg THEN

It doesn't make sense to use AVG inside a PL/SQL loop. PL/SQL is procedural, at that point you're not dealing with the whole set of data: you manage only one row at a time. Whereas SQL deals with sets of data.

Using the analytic function, you get the average of the column on every row, so you can manage it inside the PL/SQL loop.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142710

Well, yes - this is (PL/)SQL, but you just can't use AVG that way. Have a look: this is an example based on Scott's schema.

Average salary is

SQL> select avg(sal) from emp;

  AVG(SAL)
----------
2077,08333

In order to select salaries higher than the average one, you'd use a subquery. Let's call this query the "A" query (for future reference):

SQL> select ename, sal
  2  from emp
  3  where sal > (select avg(sal) from emp);

ENAME             SAL
---------- ----------
JONES            2975
BLAKE            2850
CLARK            2450
KING             5000
FORD             3000

SQL>

Also, no need to declare that much things - a simple cursor FOR loop is easier to maintain:

SQL> create or replace procedure prod_rep as
  2  begin
  3    for cur_r in (select ename, sal from emp
  4                  where sal > (select avg(sal) from emp))
  5    loop
  6      dbms_output.put_line(cur_r.ename ||' '|| to_char(cur_r.sal, '9990'));
  7    end loop;
  8  end;
  9  /

Procedure created.

SQL> begin prod_rep; end;
  2  /
JONES  2975
BLAKE  2850
CLARK  2450
KING  5000
FORD  3000

PL/SQL procedure successfully completed.

SQL>

See? No need to declare a cursor (OK, you do use that SELECT in a loop), type & record of that type, open the cursor, worry when to exit a loop, close the cursor.

Your code, however, doesn't make much sense in Apex environment. There's no DBMS_OUTPUT there, and it is rather unusual to create a report (either a classic or interactive one) using a procedure; I've never done that. I've used a function (which is a PL/SQL code) that returns a SQL query and based reports on that.

Your problem is a simple one, so - use the Wizard, create a report and - as its source - use the "A" query (I mentioned earlier). That's all you should do.

Upvotes: 1

Related Questions