如何使用%ROWTYPE获取游标值 [英] How to fetch cursor value with %ROWTYPE
问题描述
我进行如下所示的操作,我想获取游标值并从其他循环中提取这些值,告诉我最好的方法是我的代码
i make a procedure as shown below i want to fetch cursor values and extract these values from other loop tell me best way here is my code
CREATE OR REPLACE PROCEDURE TEST (P_EMP_ID NUMBER,
TRIGER_BY VARCHAR2)
AS
CURSOR TO_HOD
IS
SELECT EMP.EMPLOYEE_CODE,
EMP.EMP_NAME,
APR.LEFT_DT,
APR.RESIGN_TYPE
FROM FSC_APPROVAL APR, CHR_ALL_EMPLOYEE_BI_V EMP
WHERE APR.HOD_APPR = 'Y'
AND APR.ZONE_HD_APPR IS NULL
AND EMP.EMPLOYEE_ID = APR.EMP_ID;
CURSOR TO_ZONE
IS
SELECT EMP.EMPLOYEE_CODE,
EMP.EMP_NAME,
APR.LEFT_DT,
APR.RESIGN_TYPE
FROM FSC_APPROVAL APR, CHR_ALL_EMPLOYEE_BI_V EMP
WHERE APR.HOD_APPR = 'Y'
AND APR.ZONE_HD_APPR = 'Y'
AND APR.TIM_OFC_APPR IS NULL
AND EMP.EMPLOYEE_ID = APR.EMP_ID;
CUR_VAL TO_HOD%ROWTYPE;
CUR_VAL_FOR_LOOP TO_HOD%ROWTYPE;
从这里开始程序
BEGIN
IF TRIGER_BY = 'HOD'
THEN
OPEN TO_HOD;
LOOP
FETCH TO_HOD INTO CUR_VAL;
EXIT WHEN TO_HOD%NOTFOUND;
END LOOP;
ELSIF TRIGER_BY = 'ZONE'
THEN
OPEN TO_ZONE;
LOOP
FETCH TO_ZONE INTO CUR_VAL;
EXIT WHEN TO_ZONE%NOTFOUND;
END LOOP;
END IF;
上面的代码中
只是获取我的数据并放入%rowtype的 cur_val 之后,我在新循环中获取这些值,但没有意义并显示错误 PLS-00456:项目'CUR_VAL'不是游标
in above code just fetch my data and put in cur_val that was %rowtype after that i fetch these values in new loop but its not make sense and show error PLS-00456: item 'CUR_VAL' is not a cursor
LOOP
***FETCH CUR_VAL INTO CUR_VAL_FOR_LOOP;***
DBMS_OUTPUT.PUT_LINE (CUR_VAL_FOR_LOOP.EMPLOYEE_CODE);
END LOOP;
END;
/
我在此行FETCH CUR_VAL INTO CUR_VAL_FOR_LOOP;
上遇到错误
如果您有更好的选择,请按照我的意愿指导我如何执行此过程,如果您对我的问题有进一步的疑问,我将热烈欢迎,然后在这里我向您描述
i am face error on this line FETCH CUR_VAL INTO CUR_VAL_FOR_LOOP;
please guide me how i make this procedure as i desire if you have better option then i warmly welcome if you have further query about my Question then i will here to describe you
推荐答案
为什么要使用两个变量?您可以做的更短(未经测试):
Why do you use two variables? You can do it shorter (not tested):
CURSOR TO_ALL(hd APR.ZONE_HD_APPR%TYPE) IS
SELECT EMP.EMPLOYEE_CODE,
EMP.EMP_NAME,
APR.LEFT_DT,
APR.RESIGN_TYPE
FROM FSC_APPROVAL APR,
JOIN CHR_ALL_EMPLOYEE_BI_V EMP ON EMP.EMPLOYEE_ID = APR.EMP_ID
WHERE APR.HOD_APPR = 'Y'
AND NVL(APR.ZONE_HD_APPR, 'NULL') = NVL(hd, 'NULL')
AND EMP.EMPLOYEE_ID = APR.EMP_ID;
CUR_VAL TO_ALL%ROWTYPE;
BEGIN
OPEN TO_ALL(CASE TRIGER_BY WHEN 'HOD' THEN 'NULL' ELSE 'Y');
LOOP
FETCH TO_ALL INTO CUR_VAL;
...
EXIT WHEN TO_ALL%NOTFOUND;
END LOOP;
END;
这篇关于如何使用%ROWTYPE获取游标值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!