CREATE OR REPLACE PROCEDURE JLQJ_BFD_GXTZ_SP(p_BFD_ID IN NUMBER,
P_user_id IN number) AS v_JLQJ_BM number;
v_BFD_XTZ number;
BEGIN
SAVEPOINT V_SP_00000; select JLQJ_BM
into v_JLQJ_BM
from jlqj_bfd_tb
where BFD_ID = p_BFD_ID
and BFD_STATE = 5; select BFD_XTZ
into v_BFD_XTZ
from jlqj_bfd_tb
where BFD_ID = p_BFD_ID
and BFD_STATE = 5; update jlqj_jlqj_tb set JLQJ_GLTZ = v_BFD_XTZ where JLQJ_BM = v_JLQJ_BM; update jlqj_bfd_tb
set BFD_STATE = 99, U_USERID = P_user_id, U_OPTIME = sysdate
where BFD_ID = p_BFD_ID
and BFD_STATE = 5; COMMIT;EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO SAVEPOINT V_SP_00000;
RAISE_APPLICATION_ERROR(-20001,
'STS_ERROR: JLQJ_BFD_GXTZ_SP ERROR HAPENED' ||
SQLERRM);
END JLQJ_BFD_GXTZ_SP;
BFD_STATE的值是:0,1,2,3,4,5这6个值,上面我要取两个值JLQJ_BM和BFD_XTZ,条件语句都是BFD_STATE=5,我现在要做的判断是:如果jlqj_bfd_tb表里面没有满足条件的数据,要求报个错,该怎么写啊,本人是新手,谢谢了。
P_user_id IN number) AS v_JLQJ_BM number;
v_BFD_XTZ number;
BEGIN
SAVEPOINT V_SP_00000; select JLQJ_BM
into v_JLQJ_BM
from jlqj_bfd_tb
where BFD_ID = p_BFD_ID
and BFD_STATE = 5; select BFD_XTZ
into v_BFD_XTZ
from jlqj_bfd_tb
where BFD_ID = p_BFD_ID
and BFD_STATE = 5; update jlqj_jlqj_tb set JLQJ_GLTZ = v_BFD_XTZ where JLQJ_BM = v_JLQJ_BM; update jlqj_bfd_tb
set BFD_STATE = 99, U_USERID = P_user_id, U_OPTIME = sysdate
where BFD_ID = p_BFD_ID
and BFD_STATE = 5; COMMIT;EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO SAVEPOINT V_SP_00000;
RAISE_APPLICATION_ERROR(-20001,
'STS_ERROR: JLQJ_BFD_GXTZ_SP ERROR HAPENED' ||
SQLERRM);
END JLQJ_BFD_GXTZ_SP;
BFD_STATE的值是:0,1,2,3,4,5这6个值,上面我要取两个值JLQJ_BM和BFD_XTZ,条件语句都是BFD_STATE=5,我现在要做的判断是:如果jlqj_bfd_tb表里面没有满足条件的数据,要求报个错,该怎么写啊,本人是新手,谢谢了。
P_user_id IN NUMBER) AS
v_JLQJ_BM NUMBER;
v_BFD_XTZ NUMBER;
BEGIN
SAVEPOINT V_SP_00000;
SELECT JLQJ_BM
INTO v_JLQJ_BM
FROM jlqj_bfd_tb
WHERE BFD_ID = p_BFD_ID
AND BFD_STATE = 5;
SELECT BFD_XTZ
INTO v_BFD_XTZ
FROM jlqj_bfd_tb
WHERE BFD_ID = p_BFD_ID
AND BFD_STATE = 5;
UPDATE jlqj_jlqj_tb SET JLQJ_GLTZ = v_BFD_XTZ WHERE JLQJ_BM = v_JLQJ_BM;
UPDATE jlqj_bfd_tb
SET BFD_STATE = 99, U_USERID = P_user_id, U_OPTIME = SYSDATE
WHERE BFD_ID = p_BFD_ID
AND BFD_STATE = 5;
COMMIT;
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20001, '没有满足条件的数据');
ROLLBACK TO SAVEPOINT V_SP_00000;
WHEN OTHERS THEN
ROLLBACK TO SAVEPOINT V_SP_00000;
RAISE_APPLICATION_ERROR(-20001, '其它未知错误');
END JLQJ_BFD_GXTZ_SP;