存储笔记1

下面这是存储实例：

看小节直接往后翻额...

-------------------------------------------------------------------------------------------------
--************************ 根据批号更改单据,进行库存加减帐操作 ************************
-------------------------------------------------------------------------------------------------
PROCEDURE P_INV_FACT_LOTCHANGE(AI_BILL_ID NUMBER,AI_RET OUT NUMBER, AS_ERRM OUT VARCHAR2)
AS
INV_FACT T_INV_FACT;
INV_OP T_INV_OP;
CURSOR LC_LOSORDER IS
SELECT OL.COM_GOODS_ID,
OL.BEFORE_COM_LOT_ID,
OL.COM_LOT_ID,
OL.STOCK_POS_ID,
OL.CHANGE_QTY,
OL.SSC_LOT_CHANGE_LINES_ID,
P.STOCK_STYLE,
OL.COM_INVENTORY_TYPE_ID,
OL.BEFORE_INVENTORY_TYPE_ID,
P.DEPOT_ID
FROM SSC_LOT_CHANGE_LINES OL,
COM_STOCK_POS P
WHERE OL.STOCK_POS_ID = P.STOCK_POS_ID
AND OL.STOCK_POS_ID = P.STOCK_POS_ID
AND OL.SSC_LOT_CHANGE_ID = AI_BILL_ID
AND OL.CHANGE_QTY<>0;
AL_INV_OWNER NUMBER;
AL_INV_USER NUMBER;
AL_INV_STORAGER NUMBER;
AL_COM_LOT_ID NUMBER; --更改后的批号ID
AL_AFTER_INV_TYPE_ID NUMBER; --更改后的库存类型ID
AL_BEFORE_COM_LOT_ID NUMBER; --原始批号ID
AL_BEFORE_INV_TYPE_ID NUMBER; --原始的库存类型ID
AL_STOCK_POS_ID NUMBER;
AL_COM_GOODS_ID NUMBER;
AD_QTY NUMBER(31,8);
AL_SOURCE_LINES_ID NUMBER;
AL_COM_INV_FACT_ID NUMBER;
LL_PF_INV_BILLTYPE_ID NUMBER;
IS_SWITCH VARCHAR2(20);
LS_STOCKSTYLE VARCHAR2(20);
LS_USER_SW VARCHAR2(20);
LL_REF_VENDER_ID NUMBER;
LD_FACT_TAX_PRICE NUMBER(31,8);
LD_FACT_TAX_FREE_PRICE NUMBER(31,8);
LS_CREATE_STYLE VARCHAR2(20);
LL_DEPOT_ID NUMBER;
BEGIN
INV_FACT := T_INV_FACT(0,0,0,0,0,0,0,0,0,'0',0,0,0,0,'0',0,0,0,0,0,0,0,0,0,0,0,'0',0,0,0,0 , 0);
INV_OP := T_INV_OP(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
--查看单据是否存在
AI_RET := -100 ;
AS_ERRM := '批号更改单未找到! ';
SELECT O.PARTY_OWNER_ID,
O.PARTY_USER_ID,
O.MAKE_ORG,
o.create_style
INTO AL_INV_OWNER,AL_INV_USER,AL_INV_STORAGER,LS_CREATE_STYLE
FROM SSC_LOT_CHANGE O
WHERE O.SSC_LOT_CHANGE_ID = AI_BILL_ID;
IF SQL%NOTFOUND OR SQL%ROWCOUNT <1 THEN
RETURN;
END IF;
--查找报损单的业务单据类型
AI_RET := -101 ;
AS_ERRM := '单据类型对应ID未找到! ';
SELECT PF_INV_BILLTYPE_ID
INTO LL_PF_INV_BILLTYPE_ID
FROM PF_INV_BILLTYPE
WHERE LOWER(CLASS_NAME) = LOWER('LOTCHANGE');
IF SQLCODE <> 0 OR SQL%ROWCOUNT <> 1 THEN
RETURN;
END IF;
--判断是否需要业务帐操作
SELECT F_GET_PARTY_RULE(AL_INV_USER,'CTL_INV_OP') INTO IS_SWITCH FROM DUAL;
IF IS_SWITCH IS NULL THEN
IS_SWITCH := 'TRUE';
END IF;

-- 判断是否取库存拥有者对应库存使用者关系
SELECT F_GET_PARTY_RULE(AL_INV_OWNER,'INV_USER_USE') INTO LS_USER_SW FROM DUAL;
IF LS_USER_SW IS NULL OR LS_USER_SW = 'FALSE' THEN
AL_INV_USER := NULL;
END IF;

OPEN LC_LOSORDER;
LOOP
FETCH LC_LOSORDER INTO AL_COM_GOODS_ID,AL_BEFORE_COM_LOT_ID,AL_COM_LOT_ID,AL_STOCK_POS_ID,AD_QTY,AL_SOURCE_LINES_ID,LS_STOCKSTYLE,AL_AFTER_INV_TYPE_ID,AL_BEFORE_INV_TYPE_ID,LL_DEPOT_ID;
EXIT WHEN LC_LOSORDER%NOTFOUND;
--保管帐
INV_FACT.INV_OWNER := AL_INV_OWNER;
INV_FACT.INV_USER := AL_INV_USER;
INV_FACT.WAREHOUSE_ID := AL_INV_STORAGER;
INV_FACT.COM_GOODS_ID := AL_COM_GOODS_ID;
INV_FACT.COM_LOT_ID := AL_BEFORE_COM_LOT_ID;
INV_FACT.STOCK_POS_ID := AL_STOCK_POS_ID;
INV_FACT.COM_INVENTORY_TYPE_ID := AL_BEFORE_INV_TYPE_ID;
INV_FACT.INV_QTY := AD_QTY;
INV_FACT.SOURCE_ID := AI_BILL_ID;
INV_FACT.SOURCE_STYLE := 'LOTCHANGE';
INV_FACT.SOURCE_LINES_ID := AL_SOURCE_LINES_ID;
INV_FACT.COM_INV_FACT_ID := AL_COM_INV_FACT_ID;
INV_FACT.PF_INV_BILLTYPE_ID := LL_PF_INV_BILLTYPE_ID;
INV_FACT.INV_BILL_ID := AI_BILL_ID;
INV_FACT.ONHAND := 0;
INV_FACT.ONAVAILABLE := 0;
INV_FACT.ONRECEIVED := 0;
INV_FACT.ONSEND := 0;
INV_FACT.ONRESERVED := 0;
INV_FACT.ONLOCK := 0;
INV_FACT.LS_STOCKSTYLE := LS_STOCKSTYLE;
INV_FACT.REF_SEND_PARTY_ID := AL_INV_STORAGER;
--保管帐扣帐旧批号扣帐
AI_RET := OF_DIST_OUTLOT(INV_FACT,AS_ERRM);
IF AI_RET <> 1 THEN
CLOSE LC_LOSORDER;
RETURN;
END IF;

--查找批次供应商，批次进价
SELECT I.REF_VENDER_ID,I.FACT_TAX_PRICE,I.FACT_TAX_FREE_PRICE
INTO LL_REF_VENDER_ID,LD_FACT_TAX_PRICE,LD_FACT_TAX_FREE_PRICE
FROM COM_INV_FACT I,HIS_INV_LOSTOP H
WHERE I.COM_INV_FACT_ID = H.COM_INV_GETOP_ID
AND I.COM_LOT_ID = AL_BEFORE_COM_LOT_ID
AND I.PARTY_OWNER_ID = AL_INV_OWNER
AND I.PARTY_STORAGE_ID = AL_INV_STORAGER
AND I.COM_GOODS_ID = AL_COM_GOODS_ID
AND I.COM_INVENTORY_TYPE_ID = AL_BEFORE_INV_TYPE_ID
AND H.SOURCE_LINES_ID = AL_SOURCE_LINES_ID
AND ROWNUM = 1;

--批次进价处理 MODIFY BY TZ @20090808
INV_FACT.REF_VENDER_ID := LL_REF_VENDER_ID;
INV_FACT.FACT_TAX_PRICE := LD_FACT_TAX_PRICE;
INV_FACT.FACT_TAX_FREE_PRICE := LD_FACT_TAX_FREE_PRICE;

--保管帐加帐新批号加帐
INV_FACT.COM_LOT_ID := AL_COM_LOT_ID;
INV_FACT.COM_INVENTORY_TYPE_ID := AL_AFTER_INV_TYPE_ID;
IF UPPER(LS_STOCKSTYLE) = 'NORMAL' THEN --合格品批号更改
INV_FACT.ONHAND := AD_QTY;
INV_FACT.ONAVAILABLE := AD_QTY;
ELSE --不合格品批号更改
INV_FACT.ONHAND := AD_QTY;
INV_FACT.ONLOCK := AD_QTY;
END IF;
AI_RET := OF_DIST_SPILLOPER(INV_FACT,AS_ERRM);
IF AI_RET <> 1 THEN
CLOSE LC_LOSORDER;
RETURN;
END IF;

IF (AL_AFTER_INV_TYPE_ID <> AL_BEFORE_INV_TYPE_ID) AND AL_AFTER_INV_TYPE_ID IS NOT NULL AND AL_BEFORE_INV_TYPE_ID IS NOT NULL THEN
--业务帐批号更改
IF UPPER(IS_SWITCH) <> 'FALSE' THEN --判断是否扣减业务帐
INV_OP.INV_OWNER := AL_INV_OWNER;
INV_OP.INV_USER := AL_INV_USER;
INV_OP.INV_STORAGER := AL_INV_STORAGER;
INV_OP.COM_GOODS_ID := AL_COM_GOODS_ID;
INV_OP.INV_QTY := AD_QTY;
INV_OP.ONHAND := 0;
INV_OP.ONAVAILABLE := 0;
INV_OP.ONRECEIVED := 0;
INV_OP.ONSEND := 0;
INV_OP.ONRESERVED := 0;
INV_OP.ONLOCK := 0;
INV_OP.ORDER_QTY := 0;
INV_OP.COM_DEPOT_ID := LL_DEPOT_ID;
-- 业务帐原始库存类型减帐
IF UPPER(LS_STOCKSTYLE) = 'NORMAL' THEN --合格品批号更改
INV_OP.COM_INVENTORY_TYPE_ID := AL_BEFORE_INV_TYPE_ID;
INV_OP.ONHAND := AD_QTY;
INV_OP.ONAVAILABLE := AD_QTY;
ELSE --不合格品批号更改
INV_OP.COM_INVENTORY_TYPE_ID := AL_BEFORE_INV_TYPE_ID;
INV_OP.ONHAND := AD_QTY;
INV_OP.ONLOCK := AD_QTY;
END IF;
AI_RET := OF_OP_AFFIRMHAND(INV_OP,AS_ERRM);
IF AI_RET <> 1 THEN
CLOSE LC_LOSORDER;
RETURN;
END IF;

--业务帐新的库存类型加帐
IF UPPER(LS_STOCKSTYLE) = 'NORMAL' THEN --合格品批号更改
INV_OP.COM_INVENTORY_TYPE_ID := AL_AFTER_INV_TYPE_ID;
INV_OP.ONHAND := AD_QTY;
INV_OP.ONAVAILABLE := AD_QTY;
ELSE --不合格品批号更改
INV_OP.COM_INVENTORY_TYPE_ID := AL_AFTER_INV_TYPE_ID;
INV_OP.ONHAND := AD_QTY;
INV_OP.ONLOCK := AD_QTY;
END IF;
AI_RET := OF_OP_SPILLOPER(INV_OP,AS_ERRM);
IF AI_RET <> 1 THEN
CLOSE LC_LOSORDER;
RETURN;
END IF;

END IF;
END IF;
END LOOP;

IF LC_LOSORDER%ISOPEN THEN
CLOSE LC_LOSORDER;
END IF;

--生成BC_EDI数据，以方便回传上位系统
--Modify by tz 2013-05-16
IF LS_CREATE_STYLE = 'UI' THEN
p_create_bc_edi_bylotchg(AI_BILL_ID,ai_ret,as_errm);
IF AI_RET <> 1 THEN
RETURN;
END IF;
END IF;

AI_RET := 1 ;
AS_ERRM := '批号更改库存操作成功! ';
EXCEPTION WHEN OTHERS THEN
IF LC_LOSORDER%ISOPEN THEN
CLOSE LC_LOSORDER;
END IF;
AS_ERRM := AS_ERRM||',CODE:'||TO_CHAR(SQLCODE)||'ERRM:'||SQLERRM(SQLCODE);
RETURN;
END
;

--------------------------------------------------

存储小节：
创建存储存储名(输入参数，输出参数1，输出参数2)
as/is (固定写法，下面的begin end是存储的开始和结束)

声明对象(后面存储要用的)

声明游标

CURSOR 游标名 IS

select * from tab;(这里查询出来的集合或者对象放入游标中)

(游标有open close 游标名；打开后，可以使用fetch 游标名 into 变量，这样可以使查询出的游标集合存入变量中，供后续使用)

声明变量(后面存储要用的)

begin

对象声明后，要使用需要先设置默认值；

存储中输出值1 输出值2也需要再 begin后面设置默认输出返回值；

注意这里面常用的给变量赋值方式：select xx,xx into 变量1，变量2 from tab;

( || 表示拼接类似于java里的+ ； := 这是相当于=；<> 这想到与不等于；)

查询结束后，这是基本判断查询是否成功以及返回(这里会返回前面设置的默认输出值1，输出值2)的语法：

IF SQL%NOTFOUND OR SQL%ROWCOUNT <1 THEN
RETURN;
END IF;

--如果查询成功

可以继续往下执行

OPEN LC_LOSORDER;

loop（类似于java里的for循环，自动循环，常用语循环游标集合）

fetch LC_LOSORDER into 变量1,变量2,变量3;
EXIT WHEN LC_LOSORDER%NOTFOUND;

后续在用这些查出来的变量复制给创建的对象，供后续判断：

INV_FACT.INV_OWNER := AL_INV_OWNER;
INV_FACT.INV_USER := AL_INV_USER;
INV_FACT.WAREHOUSE_ID := AL_INV_STORAGER;
INV_FACT.COM_GOODS_ID := AL_COM_GOODS_ID;
INV_FACT.COM_LOT_ID := AL_BEFORE_COM_LOT_ID;

IF AI_RET <> 1 THEN
CLOSE LC_LOSORDER;
RETURN;
END IF;

end loop

（记得游标用完之后要关掉）

IF LC_LOSORDER%ISOPEN THEN
CLOSE LC_LOSORDER;
END IF;

如果发现异常也要关掉游标，返回异常信息，如果没有异常就返回成功参数：

还有记得存储的结束(对应begin的，begin end 还可以支持多重嵌套)：

END;