在Oracle函数中返回多个值 [英] Return Multiple Value in Oracle Function

问题描述

亲爱的，



i无法返回多个值。



i wan返回tmpVar ，tmpVar1，tmpVar2，tmpVar3来自我的下面提到的功能



可以有人帮帮我。



我的代码如下



创建或替换功能ESUPPORT.TRAINING_SCORE（train_id NUMBER）返回号码是

tmpVar NUMBER;

tmpVar1 NUMBER;

tmpVar2 NUMBER;

tmpVar3 NUMBER;



v_no_of_participant number：= 0;

v_sum_of_responsce_prog_cont number：= 0;

v_sum_of_responsce_prog_facu number：= 0;

v_sum_of_responsce_prog_logi number：= 0;

v_sum_of_responce_prog_lear number：= 0;



BEGIN

从ESUPPORTV_FB_TRAINING_FEEDBACK中选择COUNT（*）到v_no_of_participant，其中FB_TRAINING_ID = train_id;

选择sum（A1）+ sum（B1）+ sum（C1）+ sum（D1）到v_sum_of_responsce_prog_cont FROM ESUPPOR TV_FB_TRAINING_FEEDBACK WHERE FB_TRAINING_ID = train_id;

选择总和（A2）+总和（B2）+总和（C2）+总和（D2）到v_sum_of_responsce_prog_facu来自ESUPPORTV_FB_TRAINING_FEEDBACK WHERE FB_TRAINING_ID = train_id;

选择sum（A1）+ sum（B1）+ sum（C1）+ sum（D1）到v_sum_of_responsce_prog_logi FROM ESUPPORTV_FB_TRAINING_FEEDBACK WHERE FB_TRAINING_ID = train_id;

选择总和（A1）+总和（B1）+总和（C1）+ sum（D1）到v_sum_of_responce_prog_lear FROM ESUPPORTV_FB_TRAINING_FEEDBACK WHERE FB_TRAINING_ID = train_id;

tmpVar：= v_sum_of_responsce_prog_cont /（4 * v_no_of_participant）;

tmpVar1：= v_sum_of_responsce_prog_facu /（ 4 * v_no_of_participant）;

tmpVar2：= v_sum_of_responsce_prog_logi /（4 * v_no_of_participant）;

tmpVar3：= v_sum_of_responce_prog_lear /（4 * v_no_of_participant）;



返回tmpVar;

EXCEPTION

当NO_DATA_FOUND那么

NULL;

当其他人那么

- 考虑记录错误然后重新加注

RAISE;

END TRAINING_SCORE;



感谢你

Dear All,

i am not able to return multiple value.

i wan to return tmpVar,tmpVar1,tmpVar2,tmpVar3 from my below mention function

can anybody help me out.

My code is as follows

CREATE OR REPLACE FUNCTION ESUPPORT.TRAINING_SCORE (train_id NUMBER) RETURN number IS
tmpVar NUMBER ;
tmpVar1 NUMBER ;
tmpVar2 NUMBER ;
tmpVar3 NUMBER ;

v_no_of_participant number :=0;
v_sum_of_responsce_prog_cont number :=0;
v_sum_of_responsce_prog_facu number :=0;
v_sum_of_responsce_prog_logi number :=0;
v_sum_of_responce_prog_lear number :=0;

BEGIN
select COUNT(*) into v_no_of_participant FROM ESUPPORTV_FB_TRAINING_FEEDBACK WHERE FB_TRAINING_ID = train_id;
select sum(A1)+sum(B1)+sum(C1)+sum(D1) into v_sum_of_responsce_prog_cont FROM ESUPPORTV_FB_TRAINING_FEEDBACK WHERE FB_TRAINING_ID = train_id;
select sum(A2)+sum(B2)+sum(C2)+sum(D2) into v_sum_of_responsce_prog_facu FROM ESUPPORTV_FB_TRAINING_FEEDBACK WHERE FB_TRAINING_ID = train_id;
select sum(A1)+sum(B1)+sum(C1)+sum(D1) into v_sum_of_responsce_prog_logi FROM ESUPPORTV_FB_TRAINING_FEEDBACK WHERE FB_TRAINING_ID = train_id;
select sum(A1)+sum(B1)+sum(C1)+sum(D1) into v_sum_of_responce_prog_lear FROM ESUPPORTV_FB_TRAINING_FEEDBACK WHERE FB_TRAINING_ID = train_id;
tmpVar := v_sum_of_responsce_prog_cont/(4*v_no_of_participant);
tmpVar1 := v_sum_of_responsce_prog_facu/(4*v_no_of_participant);
tmpVar2 := v_sum_of_responsce_prog_logi/(4*v_no_of_participant);
tmpVar3 := v_sum_of_responce_prog_lear/(4*v_no_of_participant);

RETURN tmpVar;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END TRAINING_SCORE;

Thanking you

推荐答案

为什么不简单地创建5个函数？一个用于获取参与者数量，另外四个用于获取response_prog值。查询或完全不相关，并且不需要彼此的值来工作。拆分成函数也为以后的其他实现留出了空间，而无需其他专门的存储函数。例如，仅显示参与者的数量，或者某人是否只对这4个值中的一个感兴趣。



祝你好运！

Why not simply create 5 functions? One for getting the number of participants and 4 others to get the response_prog values. The queries or completely unrelated and do not need each others value to work. Splitting up into functions also leaves room for other implementations later on without the need for other specialized stored functions. For example, just showing the number of participants, or if someone is only interested in one of those 4 values.

Good luck!

这篇关于在Oracle函数中返回多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！