Oracle 存储过程的使用例子-优快云博客

本文介绍了一个复杂的PL/SQL存储过程，该过程涉及大量数据库操作，包括数据插入、更新及查询等。通过此过程，系统能够实现库存数据的动态管理和更新，并确保数据的一致性和准确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE OR REPLACE Procedure PRCGETV6(
inp_UserCD  IN VARCHAR2,
inp_SEIHIN  IN VARCHAR2,
inp_SOKOCD  IN VARCHAR2,
inp_JITUZAI  IN VARCHAR2,
inp_GYOSU  IN VARCHAR2,
out_KOSHINYMD  OUT VARCHAR2,
out_ERR   OUT VARCHAR2
) Is
CONST_OPERATION_NAME CONSTANT VARCHAR2(30):= 'PRCGETV6'; --操作識別子
-- out_KOSHINYMD VARCHAR2(14);
-- out_ERR  VARCHAR2(10);
varUSERCD VARCHAR2(10);
varSEIHIN VARCHAR2(11);
varSOKOCD VARCHAR2(1);
varKOSHINHI VARCHAR2(14);
numJITUZAI NUMBER;
numKEISANZAI NUMBER;
numGYOSU NUMBER;
numSEQ NUMBER;

CURSOR CUR1 IS
Select
  KBN,
  JUBI,
  YOBI,
  MENO,
  MNYUKO,
  DENPYONO,
  MSYUKO,
  EGYCD,
  ZAISU,
  KBN1,
  KBN2,
  TANMAILADD,
  IOKBN
FROM
(
  Select
   SH_SSTS As KBN,
   SH_DNPHKOB As JUBI,
   V6_IOYMD As YOBI,
--   SUBSTR(V6_MEMO,1,8)||DECODE(TRIM(SH_SSTS),NULL,'',';')||SH_SSTS||DECODE(TRIM(SH_RYCD),NULL,'',';')||SH_RYCD As MENO,
--   SUBSTR(V6_MEMO,1,8)||';'||SH_SSTS||';'||SH_RYCD As MENO,
   SUBSTR(V6_MEMO,1,8)||';'||SH_RYCD As MENO,
   V6_SU As MNYUKO,
   SUBSTR(V6_DENPYO,1,8) As DENPYONO,
   0 As MSYUKO,
   V6_BMN As EGYCD,
   0 As ZAISU,
   DECODE(V6_DATAKBN,'H','発','J','受','ﾖ','予') As KBN1,
   RPAD(V6_R,1,' ')||RPAD(V6_DATAKBN,1,' ')||RPAD(V6_C,1,' ') As KBN2,
--   TRIM(NVL(NS.KNAME,NS.WEB))||'<'||TRIM(NS.WEB)||'@bac.jp>' As TANMAILADD,
   ' ' As TANMAILADD,
   V6_IOKBN As IOKBN
  FROM
   DV6SEINY,DSISHT,MF_NOTES_SYAIN_UN NS
  Where
   V6_SEINO = SUBSTR(RPAD(varSEIHIN,11,' '),1,10) AND
   V6_HZ = SUBSTR(RPAD(varSEIHIN,11,' '),11,1) AND
   V6_IOKBN = 1 AND
   '    ' = SH_DNP_GYOSHA(+) AND
   TO_NUMBER(SUBSTR(V6_DENPYO,1,2)) = SH_DNP_YY(+) AND
   TO_NUMBER(SUBSTR(V6_DENPYO,3,6)) = SH_DNP_NO(+) And
   SH_TANTOU = NS.SNO(+)
  UNION ALL
  Select
   ' ' As KBN,
   H6_JCHYMD As JUBI,
   V6_IOYMD As YOBI,
   V6_MEMO As MENO,
   0 As MNYUKO,
   V6_DENPYO As DENPYONO,
   V6_SU As MSYUKO,
   V6_BMN As EGYCD,
   0 As ZAISU,
   DECODE(V6_DATAKBN,'H','発','J','受','ﾖ','予') As KBN1,
   RPAD(V6_R,1,' ')||RPAD(V6_DATAKBN,1,' ')||RPAD(V6_C,1,' ') As KBN2,
--   TRIM(NVL(NS.KNAME,NS.WEB))||'<'||TRIM(NS.WEB)||'@bac.jp>' As TANMAILADD,
   TRIM(NVL(NS.KNAME,NS.WEB))||'<'||TRIM(NS.WEB)||'@syskai2>' As TANMAILADD,
   V6_IOKBN As IOKBN
  FROM
   DV6SEINY,DH6URHDT,MF_NOTES_SYAIN_UN NS
  Where
   V6_SEINO = SUBSTR(RPAD(varSEIHIN,11,' '),1,10) AND
   V6_HZ = SUBSTR(RPAD(varSEIHIN,11,' '),11,1) AND
   V6_IOKBN = 0 AND
   V6_DATAKBN = 'J' AND
   TO_NUMBER(SUBSTR(V6_DENPYO,1,8)) = H6_JCHNO(+) AND
   H6_JCHTNTCD = NS.SNO(+)
  UNION ALL
  Select
   ' ' As KBN,
   H2_NOKYMD As JUBI,
   V6_IOYMD As YOBI,
   V6_MEMO As MENO,
   0 As MNYUKO,
   V6_DENPYO As DENPYONO,
   V6_SU As MSYUKO,
   V6_BMN As EGYCD,
   0 As ZAISU,
   DECODE(V6_DATAKBN,'H','発','J','受','ﾖ','予') As KBN1,
   RPAD(V6_R,1,' ')||RPAD(V6_DATAKBN,1,' ')||RPAD(V6_C,1,' ') As KBN2,
--   TRIM(NVL(NS.KNAME,NS.WEB))||'<'||TRIM(NS.WEB)||'@bac.jp>' As TANMAILADD,
   TRIM(NVL(NS.KNAME,NS.WEB))||'<'||TRIM(NS.WEB)||'@syskai2>' As TANMAILADD,
   V6_IOKBN As IOKBN
  FROM
   DV6SEINY,DH2MTHDT,MF_NOTES_SYAIN_UN NS
--   DV6SEINY,MF_NOTES_SYAIN_UN NS
--   DV6SEINY
  Where
   V6_SEINO = SUBSTR(RPAD(varSEIHIN,11,' '),1,10) AND
   V6_HZ = SUBSTR(RPAD(varSEIHIN,11,' '),11,1) AND
   V6_IOKBN = 0 AND
   V6_DATAKBN = 'ﾖ' AND
   TO_NUMBER(SUBSTR(V6_DENPYO,1,8)) = H2_MTM_BKNCD(+) AND
   TO_NUMBER(SUBSTR(V6_DENPYO,9,1)) = H2_MTM_PTN(+) AND
   TO_NUMBER(SUBSTR(V6_DENPYO,9,1)) = H2_MTM_GRP(+) AND
   H2_NYRTNTCD = NS.SNO(+)
) SYUKEI;
-- ORDER BY YOBI ASC,V6_IOKBN DESC;
REC1 CUR1%ROWTYPE;

CURSOR CUR2 IS
Select
  TANTOU,
  SYBTM,
  NSEQ,
  PAGE,
  KBN,
  JUBI,
  YOBI,
  MENO,
  MNYUKO,
  DENPYONO,
  MSYUKO,
  EGYCD,
  ZAISU,
  KBN1,
  KBN2,
  TANMAILADD
From
  WK_DSPNYUKOS
ORDER BY YOBI ASC,IOKBN DESC;
REC2 CUR2%ROWTYPE;

Begin

--不要なゴミデータの削除(一日以前のデータ)
Delete From WK_DSPNYUKO Where SYBTM < TO_CHAR(SYSDATE - 1,'YYYYMMDDHH24MISS');
Delete From WK_DSPNYUKOS;
varUSERCD := inp_UserCD;
varSEIHIN := inp_SEIHIN;
varSOKOCD := inp_SOKOCD;
numJITUZAI := TO_NUMBER(inp_JITUZAI);
numKEISANZAI := TO_NUMBER(inp_JITUZAI);
numGYOSU := TO_NUMBER(inp_GYOSU);
numSEQ :=0;

--更新日の取得
Select TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') INTO varKOSHINHI FROM DUAL;

--所見コード変換データ配列作成
--ループ開始
--カーソルオープン
OPEN CUR1;
LOOP
  FETCH CUR1 INTO REC1;
  EXIT WHEN CUR1%NOTFOUND;
   INSERT INTO WK_DSPNYUKOS
   (
    TANTOU,
    SYBTM,
    NSEQ,
    PAGE,
    KBN,
    JUBI,
    YOBI,
    MENO,
    MNYUKO,
    DENPYONO,
    MSYUKO,
    EGYCD,
    ZAISU,
    KBN1,
    KBN2,
    TANMAILADD,
    IOKBN
   )
   VALUES
   (
    varUSERCD,
    TO_NUMBER(varKOSHINHI),
    numSEQ,
    TRUNC(numSEQ / numGYOSU) + 1,
    TRIM(REC1.KBN),
    TO_NUMBER(REC1.JUBI),
    TO_NUMBER(REC1.YOBI),
    SUBSTR(REC1.MENO,1,20),
    TO_NUMBER(REC1.MNYUKO),
    TO_NUMBER(REC1.DENPYONO),
    TO_NUMBER(REC1.MSYUKO),
    TRIM(REC1.EGYCD),
    0,
    TRIM(REC1.KBN1),
    REC1.KBN2,
    TRIM(REC1.TANMAILADD),
    REC1.IOKBN
   );
   numSEQ := numSEQ + 1;
   --numKEISANZAI := numKEISANZAI + REC1.MNYUKO - REC1.MSYUKO;

END LOOP;
CLOSE CUR1;

numGYOSU := TO_NUMBER(inp_GYOSU);
numSEQ :=0;

--所見コード変換データ配列作成
--ループ開始
--カーソルオープン
OPEN CUR2;
LOOP
  FETCH CUR2 INTO REC2;
  EXIT WHEN CUR2%NOTFOUND;
   INSERT INTO WK_DSPNYUKO
   (
    TANTOU,
    SYBTM,
    NSEQ,
    PAGE,
    KBN,
    JUBI,
    YOBI,
    MENO,
    MNYUKO,
    DENPYONO,
    MSYUKO,
    EGYCD,
    ZAISU,
    KBN1,
    KBN2,
    TANMAILADD
   )
   VALUES
   (
    varUSERCD,
    TO_NUMBER(varKOSHINHI),
    numSEQ,
    TRUNC(numSEQ / numGYOSU) + 1,
    TRIM(REC2.KBN),
    TO_NUMBER(REC2.JUBI),
    TO_NUMBER(REC2.YOBI),
    SUBSTR(REC2.MENO,1,20),
    TO_NUMBER(REC2.MNYUKO),
    TO_NUMBER(REC2.DENPYONO),
    TO_NUMBER(REC2.MSYUKO),
    TRIM(REC2.EGYCD),
    numKEISANZAI + REC2.MNYUKO - REC2.MSYUKO,
    TRIM(REC2.KBN1),
    REC2.KBN2,
    TRIM(REC2.TANMAILADD)
   );
   numSEQ := numSEQ + 1;
   numKEISANZAI := numKEISANZAI + REC2.MNYUKO - REC2.MSYUKO;