CODEWITHSUNDEEP
oracle-databaseplsqloracle11gstored-functions
Artur Ferreira
Artur Ferreira

Reputation: 11

Function in Oracle that returns 2 different columns of a table

I'm trying to create a function that returns 2 different columns in a single table, can anyone help me with that?

I've already tried this way:

CREATE FUNCTION return_id_grade(subjectId IN NUMBER, semesterYear IN DATE , n IN INT, option IN INT)
RETURN NUMBER
                IS studentId NUMBER(5),
                IS studentGrade NUMBER(2,1);
   BEGIN
                SELECT DISTINCT student_id INTO studentId,
                       grade INTO studentGrade
                    FROM (SELECT studentId, grade, dense_rank() over (ORDER BY grade desc) rank FROM old_students) 
                        WHERE subject_id = subjectId 
                            AND semester_year = semesterYear
                            AND rank = n
                            AND rownum <= 1
    CASE 
                WHEN option = 1 then RETURN(student_id)
                WHEN option = 2 then RETURN(grade)
    END;

END;

I expected to output the n'NTH grade of an university class and the student Id, but the actual can just output the option received on parameter field.

Upvotes: 0

Views: 178

Answers (3)

Shomeek Basu
Shomeek Basu

Reputation: 49

a.You cant use Select colum1 INTO variable1 , colum2 INTO variable2 . It has to be like : Select column1 , column2 INTO variable1 , variable 2

b.Create an object type and use it as out parameter in a procedure

c.Have the option condition after the procedure is called.

Sample Code:

      CREATE OR REPLACE TYPE ty_obj_idgrade AS OBJECT
      (studentId    NUMBER(5)
      ,studentGrade    NUMBER(2,1)
       );



      CREATE OR REPLACE PROCEDURE return_id_grade(
                                        subjectId IN NUMBER, 
                                        semesterYear IN DATE , 
                                        n IN INT, 
                                       -- options IN INT
                                        ,p_idgrade OUT ty_obj_idgrade) IS
       BEGIN
            SELECT DISTINCT student_id --INTO studentId,
                            ,grade --INTO studentGrade
                       INTO p_idgrade.studentId
                            ,p_idgrade.grade
                       FROM (SELECT studentId
                                    ,grade
                                    ,dense_rank() over (ORDER BY grade desc) rank 
                                    ,subject_id
                                    ,semester_year
                               FROM old_students )
                WHERE  subject_id = subjectId
                  AND  semester_year = semesterYear
                  AND  rank = n
                  AND  rownum <= 1;

EXCEPTION
WHEN OTHERS THEN
    dbms_output.put_line('we are inside when others -->'||sqlerrm);

END;

Call your procedure.

Since options was used as IN parameter , it should be availabe outside the prc/fnc So this can be done after the prc/fnc call If options = 1 THEN value := p_idgrade.conatct ELSE value := p_idgrade.grade END IF;

Hope it helps.

Upvotes: 1

Popeye
Popeye

Reputation: 35910

You can directly try to use OPTION in query as following:

CREATE FUNCTION RETURN_ID_GRADE (
    SUBJECTID      IN             NUMBER,
    SEMESTERYEAR   IN             DATE,
    N              IN             INT,
    OPTION         IN             INT
) RETURN NUMBER IS
    LV_RETURN_NUMBER   NUMBER(6, 1);
BEGIN
    -- QUERY TO FETCH REQUIRED DATA ONLY
    SELECT -- DISTINCT -- DISTINCT IS NOT NEEDED AS ROWNUM <= 1 IS USED
        CASE
            WHEN OPTION = 1 THEN STUDENT_ID
            ELSE GRADE
        END AS LV_RETURN_NUMBER
    INTO LV_RETURN_NUMBER -- STORE VALUE BASED ON OPTION
    FROM
        (
            SELECT
                STUDENTID,
                GRADE,
                DENSE_RANK() OVER(
                    ORDER BY
                        GRADE DESC
                ) RANK
            FROM
                OLD_STUDENTS
        )
    WHERE
        SUBJECT_ID = SUBJECTID
        AND SEMESTER_YEAR = SEMESTERYEAR
        AND RANK = N
        AND ROWNUM <= 1;

    RETURN LV_RETURN_NUMBER; -- RETURN THE VARIABLE

END;

Cheers!!

Upvotes: 0

OldProgrammer
OldProgrammer

Reputation: 12169

Did not try compiling it, but something like this should be close.

CREATE FUNCTION return_id_grade(subjectId IN NUMBER, semesterYear IN DATE , n IN INT, option IN INT)
RETURN NUMBER IS
studentId NUMBER(5),
studentGrade NUMBER(2,1);
   BEGIN
                SELECT DISTINCT student_id, grade
                INTO studentId, studentGrade
                    FROM (SELECT studentId, grade, dense_rank() over (ORDER BY grade desc) rank FROM old_students) 
                        WHERE subject_id = subjectId 
                            AND semester_year = semesterYear
                            AND rank = n
                            AND rownum <= 1;
    IF option = 1 then 
         RETURN studentId ;
    ELSE
        RETURN studentGrade ;
    END IF;
    END;

END;

However, this function is really not a good design. A function should perform a single task. If you want to return both items, create a PL/SQL record type, and use a stored procedure with an OUT parameter and return that in the procedure.

Upvotes: 0

Related Questions