本文以代码从前往后的顺序逐条讲解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; --存储过程开始标记