oracle存储过程实例,mybatis调oracle存储过程

最新推荐文章于 2023-12-08 22:04:29 发布

xfl4629712

最新推荐文章于 2023-12-08 22:04:29 发布

阅读量400

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/xfl4629712/article/details/54616134

数据库专栏收录该内容

16 篇文章

订阅专栏

本文提供了一个Oracle存储过程`INSERT_MAT_ID_PROCEDURE`的详细实例，该过程根据输入参数进行数据操作。同时展示了如何使用MyBatis调用这个存储过程，并获取OUT参数的方法。在MyBatis中，通过`<select>`标签定义存储过程调用，并在Service层传入Map参数来接收OUT参数的返回值。

----存储过程 INSERT_MAT_ID_PROCEDURE实例

create or replace procedure INSERT_MAT_ID_procedure (IN_PRD_PLAN_ID IN VARCHAR2,IN_MAT_ID IN VARCHAR2 ,results out varchar2)

IS
v_str FLOAT;
v_count number(10);
v_results VARCHAR2(2000);
v_order_num VARCHAR2(20);
v_ORDER_ITEM_NUM VARCHAR2(20) default '-1';

CURSOR ORDER_ITEM_NUM(v_PRD_PLAN_ID VARCHAR2)
IS

SELECT ORDER_ITEM_NUM
FROM SC_ORDER_PLAN
WHERE PRD_PLAN_ID = v_PRD_PLAN_ID;

CURSOR SC_PLAN_DETAIL_standard(v_IN_MAT_ID VARCHAR2)
IS

SELECT PRD_STANDARD
FROM SC_PLAN_DETAIL SPD
WHERE SPD.MAT_ID = (SELECT SP.PRD_PLAN_PRODUCTION_PRD_ID
FROM SC_PRD SP
WHERE SP.PRD_ID = v_IN_MAT_ID);

BEGIN
--把投料的材料号插入SC_ORDER里

IF UPPER(SUBSTR(IN_MAT_ID,1,2))!=UPPER('KC') THEN
BEGIN
SELECT count(distinct PRD_STANDARD) into v_count FROM SC_ORDER WHERE ORDER_PK=IN_MAT_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_count := 0;
END;

IF v_count=0 THEN

SELECT distinct order_num
INTO v_order_num
FROM SC_ORDER_PLAN
WHERE PRD_PLAN_ID = IN_PRD_PLAN_ID;


FOR v_SC_prd_standard IN SC_PLAN_DETAIL_standard(IN_MAT_ID) LOOP

begin
SELECT ORDER_ITEM_NUM
INTO v_ORDER_ITEM_NUM
FROM SC_order
WHERE order_num = v_order_num
AND prd_standard = v_SC_prd_standard.PRD_STANDARD
AND (order_pk IS NULL or order_pk='')
AND ROWNUM = 1;

EXCEPTION
WHEN NO_DATA_FOUND THEN
v_ORDER_ITEM_NUM := '-1';
END;

if v_ORDER_ITEM_NUM !='-1' then
update sc_order set order_pk=IN_MAT_ID
where ORDER_ITEM_NUM=v_ORDER_ITEM_NUM;
results:='OK';
end if;
END LOOP;

else
results:='NO';

END IF;

else
results:='NO';

end IF;


END INSERT_MAT_ID_procedure;

----mybatis调用orcle存储过程,参数类型是map

{call INSERT_MAT_ID_PROCEDURE(#{inPrdPlanId,mode=IN,jdbcType=VARCHAR},#{inMatId,mode=IN,jdbcType=VARCHAR},#{results,mode=OUT,jdbcType=VARCHAR})}

</select>

---Mybatis调用存储过程并获取out参数

mapper调用存储过程

<select id="updateName" statementType="CALLABLE" parameterType="java.util.Map" resultType="java.util.Map">
call UPDATE_PRO_NAME(#{oldName,mode=IN,jdbcType=VARCHAR},
#{newName,mode=IN,jdbcType=VARCHAR},
#{results,mode=OUT,jdbcType=VARCHAR})
</select>

service层传参并获取out参数

//测试调用存储过程
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("oldName","张三");
map.put("newName","李四");
map.put("results","");//out参数,传一个空串

//这里如果再用一个map来接收返回值,返回值会是null,直接调用就行了,过程会将out参数输出到传入的map参数里

firstProductMapper.updateName(map);
String restrString = (String) map.get("results");