本文以代码从前往后的顺序逐条讲解ORACLE存储过程及多个带参数游标的使用。
最后附完整代码。
创建存储过程，参数定义
定义第一个游标
定义第二个游标
游标变量定义，声明变量
存储过程开始，变量赋值，第一个游标开始
if elsif else end if条件判断语句
第一个游标结束，第二个游标开始
执行完存储过程返回结果
CREAT OR REPLACE PROCEDRUE P_TEST1(V_PROJECT_ID IN VARCHAR2--传入参数
V_RETCODE OUT VARCHAR2,--返回码
V_RETINFO OUT VARCHAR2,--返回信息
V_ROW_NUM OUT INTEGER --影响的行数
) IS
--声明带参数的游标
CURSOR CUSOR_1(P_PROJECT_ID varchar2) IS --声明显式游标
select T1.PERSON_NAME,
T1.PERSON_ID,
T1.LOGIN_NAME,
SUM(T1.SCHEME_VALUE1 + T1.SCHEME_VALUE2
) project_flag_sum,
from (SELECT T.PERSON_NAME,
T.LOGIN_NAME,
T.AUDIT_ORG_ID,
NVL(SUM(T.SCORE_VALUE_SYS), 0) SCORE_ALL_SYS,
'1' RATIO_ALL,
(CASE
WHEN T.PROJECT_AUDIT_ORG = '1000' THEN
'0'
ELSE
'1'
END) project_flag,
NVL(SUM(CASE
WHEN T.score_type_code = '100' and
T.SCORE_ITEM_CODE = '105' THEN
T.SCORE_VALUE_FINAL
ELSE
0
END),
0) VALUE
FROM T_TEST_1 T
WHERE T.PROJECT_NUM = P_PROJECT_ID
GROUP BY T.PERSON_NAME,
T.PERSON_ID,
T.LOGIN_NAME;
) T1
GROUP BY T1.PERSON_NAME,
T1.PERSON_ID,
T1.LOGIN_NAME;
--声明第二个带参数的游标
CURSOR CUSOR_2(P_PROJECT_ID varchar2) IS --声明显式游标
select distinct (T3.PERSON_ID)
FROM T_TEST_2 T3
WHERE T3.PROJECT_NUM = P_PROJECT_ID;
C_ROW_1 CUSOR_1%ROWTYPE; --定义游标变量，该变量的类型为基于游标C_EMP的记录
C_ROW_2 CUSOR_2%ROWTYPE;
v_person_id NUMBER(20);
v_data_count number; --表中是否已有记录
v_project_flag varchar2(10);
v_flag varchar2(10);
v_qurey_person_id T_TEST_1.PERSON_ID%type;--与表中字段类型相同
v_value_all NUMBER(10, 2);
BEGIN --存储过程开始标记
--变量赋值
V_ROW_NUM := 0;
--no_person_id := 0;
BEGIN--第一个游标开始标记
--Fetch 循环
OPEN CUSOR_1(V_PROJECT_ID); --必须要明确的打开和关闭游标
LOOP
FETCH CUSOR_1
INTO C_ROW_1;
EXIT WHEN CUSOR_1%NOTFOUND;
v_person_id := C_ROW_1.person_id;
v_project_flag := C_ROW_1.project_flag;
DBMS_OUTPUT.PUT_LINE(v_person_id);
SELECT count(*)
into v_data_count
FROM T_TEST_1
WHERE person_id = v_person_id
AND project_flag = v_project_flag;
COMMIT;
DBMS_OUTPUT.PUT_LINE(v_data_count);
IF (v_data_count= 0) THEN
/**
插入数据
**/
--DBMS_OUTPUT.PUT_LINE('插入数据');
V_ROW_NUM := V_ROW_NUM + SQL%ROWCOUNT; --获取记录条数
COMMIT;
ELSIF (v_data_count= 1) THEN
/**
更新数据的处理语句
**/
--DBMS_OUTPUT.PUT_LINE('更新数据');
V_ROW_NUM := V_ROW_NUM + SQL%ROWCOUNT; --获取记录条数
COMMIT;
ELSE--可以不写ELSE语句块
NULL;
END IF;
END LOOP;
CLOSE CUSOR_1;
BEGIN --第二个游标开始标记
OPEN CUSOR_2(V_PROJECT_ID);
LOOP
FETCH CUSOR_2
INTO C_ROW_2;
EXIT WHEN CUSOR_2%NOTFOUND;
/**
数据处理
**/
COMMIT;
END LOOP;
CLOSE CUSOR_2;
END; --第二个游标结束标记
END;--第一个游标结束标记
-- 执行成功后的返回信息
V_RETCODE := 'SUCCESS';
V_RETINFO := '结束';
--异常处理
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
ROLLBACK;
END P_TEST1; --存储过程开始标记