For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Hi, We have a requirement to align the functions and procedures in the below format.
i dont have much knowledge about arbori coding. I Would appreciate any help here.
Hello d670...
I would try this code:
DECLARE p_studentid NUMBER; v_result VARCHAR2(20); BEGIN p_studentid := 1; FOR rec_result IN (SELECT students.firstname , test_history.score , test_id.test_name , test_id.passing_grade FROM students JOIN test_history ON students.STUDENT_ID = test_history.student_id JOIN test_id ON test_id.test_id = test_history.test_id WHERE students.student_id = p_studentid AND test_history.SCORE < test_id.passing_grade --> This row has to be deleted! ) LOOP IF rec_result.score < rec_result.passing_grade THEN v_result := 'Fail'; ELSE v_result := 'Pass'; END IF; DBMS_OUTPUT.PUT_LINE (rec_result.firstname || ' ' || TO_CHAR(rec_result.score) || ' ' || rec_result.test_name || ' ' || TO_CHAR(rec_result.passing_grade) || ' ' || 'Result = ' || v_result); END LOOP; END; /
DECLARE
p_studentid NUMBER;
v_result VARCHAR2(20);
BEGIN
p_studentid := 1;
FOR rec_result IN (SELECT students.firstname
, test_history.score
, test_id.test_name
, test_id.passing_grade
FROM students
JOIN test_history
ON students.STUDENT_ID = test_history.student_id
JOIN test_id
ON test_id.test_id = test_history.test_id
WHERE students.student_id = p_studentid
AND test_history.SCORE < test_id.passing_grade --> This row has to be deleted!
)
LOOP
IF rec_result.score < rec_result.passing_grade
THEN v_result := 'Fail';
ELSE v_result := 'Pass';
END IF;
DBMS_OUTPUT.PUT_LINE (rec_result.firstname || ' ' || TO_CHAR(rec_result.score) || ' ' || rec_result.test_name || ' ' || TO_CHAR(rec_result.passing_grade) || ' ' || 'Result = ' || v_result);
END LOOP;
END;
/
I hope it helps.
Regards, David
However, if the student has passed it just returns 'Result = Pass' and fails to return their name, tests, grade and passing grade.
If you find yourself wanting a function that returns multiple values, you would generally want to either - create a procedure that has multiple OUT parameters - create a function that returns an object type that encapsulates the values you want to return
If you find yourself wanting a function that returns multiple values, you would generally want to either
- create a procedure that has multiple OUT parameters
- create a function that returns an object type that encapsulates the values you want to return
can functions return multiple values?
CREATE OR REPLACE TYPE user_data_type AS OBJECT(val1 NUMBER(15), val2 NUMBER(15));/CREATE OR REPLACE FUNCTION GET_EMPLOYEE_AND_USER(in_login IN fnd_user.user_name%TYPE) RETURN user_data_type AS out_var user_data_type; CURSOR buffer_cur IS SELECT f.user_id, f.employee_id FROM Fnd_User f WHERE f.user_name = in_login;BEGIN OPEN buffer_cur; FETCH buffer_cur INTO out_var.val1, out_var.val2; CLOSE buffer_cur; RETURN out_var;END GET_EMPLOYEE_AND_USER;
plsql - Returning multiple values from PL/SQL function - Stack Overflow
Hi Folks
Thank you very much for your help. I should have mentioned that i needed to do this in a parameterised cursor. Despite your answers working perfectly, i realised i could just move the DBMS_OUTPUT.PUT_LINE inside the loop and i ended up solving my own problem.
Your code looks much better though!!!
Thanks again
Ben
i dont have much knowledge about arbori coding.