1.创建Oracle存储过程完成库存加减操作,如果按equipType1和equipType2查询记录存在,则在原数量基础上进行增减;如果查询记录不存在,则新增一条记录。
存储过程创建如下:
CREATE OR REPLACE
PROCEDURE "UPDATE_EQUIPMENT_STOCK" (P_EQUIP_TYPE1 IN NUMBER, P_EQUIP_TYPE2 IN NUMBER, P_NUM IN NUMBER, P_STATION_ID IN NUMBER, P_RESULT OUT NUMBER)
AS
STOCK_NUM NUMBER DEFAULT 0;
E_COUNT NUMBER DEFAULT 0;
BEGIN
SELECT COUNT(ID) INTO E_COUNT from EQUIPMENT_STOCK WHERE EQUIP_TYPE1=P_EQUIP_TYPE1 AND EQUIP_TYPE2=P_EQUIP_TYPE2;
IF (E_COUNT > 0) THEN
SELECT E_STOCK INTO STOCK_NUM from EQUIPMENT_STOCK WHERE EQUIP_TYPE1=P_EQUIP_TYPE1 AND EQUIP_TYPE2=P_EQUIP_TYPE2;
STOCK_NUM := STOCK_NUM + P_NUM;
UPDATE EQUIPMENT_STOCK SET E_STOCK=STOCK_NUM WHERE EQUIP_TYPE1=P_EQUIP_TYPE1 AND EQUIP_TYPE2=P_EQUIP_TYPE2;
--UPDATE
ELSE
STOCK_NUM:=P_NUM;
INSERT INTO EQUIPMENT_STOCK(ID,EQUIP_T