Home » SQL & PL/SQL » SQL & PL/SQL » function with varray to take out average of students
function with varray to take out average of students [message #211061] Tue, 26 December 2006 00:33 Go to next message
CHAISH
Messages: 12
Registered: December 2006
Junior Member
i have a varray s_avg;
created obj s_a
varray consists of studentid
using objects of varray i have to create a function to calculate average age of students
following is the code:


To create the varray:

  1  DECLARE
  2  TYPE S_AVG IS VARRAY(20) OF NUMBER(4);
  3  S_A S_AVG;
  4  BEGIN
  5  S_A:=S_AVG(1001,1002,1003,1004);
  6* END;
SQL> /

PL/SQL procedure successfully completed.


To create function? Having errors in function plz help:

CREATE OR REPLACE FUNCTION SAVERAGE(S_A IN S_AVG) RETURN
NUMBER IS STUDAVG NUMBER;
BEGIN
STUDAVG:=0;
FOR ELEM IN 1..S_A.LAST
LOOP
SELECT FIRTNAME,AVG(AGE) AS STUDAVG FROM STUDENT WHERE STUDID=S_A(I);
END LOOP;
DBMS_OUTPUT.PUT_LINE('AVEAGE IS:'||STUDAVG);
RETURN STUDAVG;
END SAVERAGE;


How to use varray elements i function? And how to access in loop?
Re: function with varray to take out average of students [message #211195 is a reply to message #211061] Wed, 27 December 2006 02:37 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Possible solution:

SQL> CREATE TABLE student (
  2    studid   NUMBER PRIMARY KEY,
  3    firtname VARCHAR2(30),
  4    age      NUMBER)
  5  /

Table created.

SQL>
SQL> CREATE OR REPLACE TYPE s_avg AS VARRAY(20) OF NUMBER(4);
  2  /

Type created.

SQL>
SQL> CREATE OR REPLACE FUNCTION saverage(s_a IN S_AVG) RETURN NUMBER IS
  2    studavg NUMBER;
  3    allavg  NUMBER;
  4    i          NUMBER;
  5  BEGIN
  6    FOR elem IN 1..s_a.last LOOP
  7      SELECT AVG(age) INTO studavg
  8        FROM student
  9       WHERE studid = s_a(i);
 10       allavg := allavg + studavg;
 11    END LOOP;
 12    DBMS_OUTPUT.PUT_LINE('Average is:' || allavg);
 13    RETURN allavg;
 14  END saverage;
 15  /

Function created.
Previous Topic: about commit Command
Next Topic: How to send only delta records from table to flat file?
Goto Forum:
  


Current Time: Sun Dec 11 08:26:31 CST 2016

Total time taken to generate the page: 0.08951 seconds