15年以上前の PL/SQL

やたらと長いですが、重要な部分は二つです。

1) SAVEPOINTROLLBACK の関係
2) UPDATE で 対象データが無い場合に IF SQL%NOTFOUND THEN で、INSERT を実行

001./*
002./*=================================
003.        受注集計処理
004.=================================*/
005.CREATE or REPLACE PROCEDURE JHUPD
006.        (
007.         ERR            IN OUT  NUMBER
008.        ,P区分            IN      VARCHAR2        -- 1:作成,2:変更,3:集計,4:SZ03,SZ04集計
009.        ,P組織            IN      VARCHAR2        -- 入力組織
010.        ,P受注番号  IN      NUMBER
011.        ,P行             IN      NUMBER          -- 登録行
012.        ,P数量1           IN      NUMBER
013.        ,P数量2           IN      NUMBER
014.        )
015.        AS
016. 
017.        EXIT_FLG        NUMBER;
018.        ERR_FLG         NUMBER;
019.        V数量             NUMBER;
020.        V数量2            NUMBER;
021.        V入出荷数量2 NUMBER;
022.        V完了             VARCHAR2(1);
023.        V在庫組織   VARCHAR2(4);
024. 
025.        V大分類            VARCHAR2(2);
026.        V商品コード VARCHAR2(20);
027.        V場所区分   VARCHAR2(1);
028.        V場所コード VARCHAR2(8);
029.        V単品コード VARCHAR2(15);
030.        V認識             VARCHAR2(1);
031. 
032.CURSOR cur_TR01 is
033.                select 識別A   from TR01
034.                 where "識別A" = '1' AND "識別C" = ' ' AND "入力組織" = P組織  AND  "受発注番号" = P受注番号;
035.BEGIN
036./*=================================
037.   初期処理
038.=================================*/
039.DBMS_OUTPUT.PUT_LINE('JHUPD START');
040.        SAVEPOINT UPD_START;
041.        ERR_FLG := ERR;
042.        ERR_FLG := 0;
043.        EXIT_FLG := 0;
044. 
045.        BEGIN
046.                select 在庫組織 into V在庫組織 from NA10 where 組織 = P組織;
047.        EXCEPTION
048.                WHEN OTHERS THEN
049.                        V在庫組織 := P組織;
050.        END;
051.        BEGIN
052.                select
053.                        大分類,
054.                        商品コード,
055.                        場所区分,
056.                        場所コード,
057.                        単品コード,
058.                        入力数量2,
059.                        入出荷数量2
060.                into
061.                        V大分類,
062.                        V商品コード,
063.                        V場所区分,
064.                        V場所コード,
065.                        V単品コード,
066.                        V数量2,
067.                        V入出荷数量2
068.                from TR07
069.                        WHERE
070.                                "識別A" = '7' AND
071.                                "識別C" = ' ' AND
072.                                "入力組織" = P組織 AND
073.                                "伝票番号" = P受注番号 AND
074.                                "登録行" = P行;
075.        EXCEPTION
076.                WHEN OTHERS THEN
077.                        EXIT_FLG := 1;
078.                        ERR := 9;
079.        END;
080. 
081.        IF P区分 = '5' THEN
082.                V数量2 := V数量2 * -1;
083.        END IF;
084. 
085. 
086. 
087.  
088./*=================================
089.   主処理
090.=================================*/
091./*   作成  */
092.        ERR_FLG := 1;
093.        IF P区分 = '1'  and EXIT_FLG = 0 THEN
094.                UPDATE TR07 SET
095.                         "入出荷数量1"               = 0
096.                        ,"入出荷数量2"               = 0
097.                        ,"残高数量1"                = P数量1
098.                        ,"残高数量2"                = P数量2
099.                WHERE   "識別A" = '7' AND "識別C" = ' ' AND "入力組織" = P組織 AND "伝票番号" = P受注番号 AND "登録行" = P行;
100.                IF SQL%NOTFOUND THEN
101.                        ERR := ERR_FLG;
102.                ELSE
103.                        LOOP
104.                                UPDATE SZ03 SET
105.                                        "現在現物数量2"               = "現在現物数量2"     - P数量2,
106.                                        "更新日"           = sysdate,
107.                                        "在庫更新日"         = sysdate
108.                                WHERE   "組織" = V在庫組織 AND "大分類" = V大分類 AND "商品コード" = V商品コード;
109.                                IF SQL%NOTFOUND THEN
110.                                        INSERT INTO SZ03 VALUES
111.                                        (V在庫組織,V大分類,V商品コード
112.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
113.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
114.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
115.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
116.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
117.                                        ,0,0,0,0,0,0,0,0,0
118.                                        ,NULL,NULL,NULL,NULL,NULL,NULL,sysdate,NULL,sysdate,0,0
119.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
120.                                        ,0,0,0);        /* 142 */
121.                                ELSE
122.                                        EXIT;
123.                                END IF;
124.                        END LOOP;
125.                        LOOP
126.                                UPDATE SZ04 SET
127.                                        "現在現物数量2"               = "現在現物数量2"     - P数量2,
128.                                        "更新日"           = sysdate,
129.                                        "在庫更新日"         = sysdate
130.                                WHERE   "組織" = V在庫組織 AND "大分類" = V大分類 AND "商品コード" = V商品コード AND "場所区分" = NVL(V場所区分,' ') AND "場所コード" = NVL(V場所コード,' ') AND "単品コード" = NVL(V単品コード,' ');
131.                                IF SQL%NOTFOUND THEN
132.                                        INSERT INTO SZ04 VALUES
133.                                        (V在庫組織
134.                                        ,NVL(V場所区分,' ')
135.                                        ,NVL(V場所コード,' ')
136.                                        ,V大分類
137.                                        ,V商品コード
138.                                        ,NVL(V単品コード,' ')
139.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0
140.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
141.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
142.                                        ,0,0,0,0,0,0,0,0
143.                                        ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
144.                                        ,sysdate,NULL,sysdate,0);       /* 83 */
145.                                ELSE
146.                                        EXIT;
147.                                END IF;
148.                        END LOOP;
149.                END IF;
150. 
151.        END IF;
152. 
153./*   変更  */
154.        ERR_FLG := 2;
155.        IF P区分 = '2'  and EXIT_FLG = 0 THEN
156.                UPDATE TR07 SET
157.                         "残高数量1"                = 入力数量1 - "入出荷数量1"
158.                        ,"残高数量2"                = 入力数量2 - "入出荷数量2"
159.                WHERE   "識別A" = '7' AND "識別C" = ' ' AND "入力組織" = P組織 AND "伝票番号" = P受注番号 AND "登録行" = P行;
160.                IF SQL%NOTFOUND THEN
161.                        ERR := ERR_FLG;
162.                ELSE
163.                        LOOP
164.                                UPDATE SZ03 SET
165.                                        "現在現物数量2"               = "現在現物数量2"     - P数量2,
166.                                        "更新日"           = sysdate,
167.                                        "在庫更新日"         = sysdate
168.                                WHERE   "組織" = V在庫組織 AND "大分類" = V大分類 AND "商品コード" = V商品コード;
169.                                IF SQL%NOTFOUND THEN
170.                                        INSERT INTO SZ03 VALUES
171.                                        (V在庫組織,V大分類,V商品コード,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
172.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
173.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
174.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
175.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
176.                                        ,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL,NULL
177.                                        ,NULL,NULL,sysdate,NULL,sysdate,0,0
178.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
179.                                        ,0,0,0);        /* 142 */
180.                                ELSE
181.                                        EXIT;
182.                                END IF;
183.                        END LOOP;
184.                        LOOP
185.                                UPDATE SZ04 SET
186.                                        "現在現物数量2"               = "現在現物数量2"     - P数量2,
187.                                        "更新日"           = sysdate,
188.                                        "在庫更新日"         = sysdate
189.                                WHERE   "組織" = V在庫組織 AND "大分類" = V大分類 AND "商品コード" = V商品コード AND "場所区分" = NVL(V場所区分,' ') AND "場所コード" = NVL(V場所コード,' ') AND "単品コード" = NVL(V単品コード,' ');
190.                                IF SQL%NOTFOUND THEN
191.                                        INSERT INTO SZ04 VALUES
192.                                        (V在庫組織,NVL(V場所区分,' '),NVL(V場所コード,' '),V大分類,V商品コード
193.                                        ,NVL(V単品コード,' ')
194.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0
195.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
196.                                        ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
197.                                        ,0,0,0,0,0,0,0,0
198.                                        ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
199.                                        ,sysdate,NULL,sysdate,0);       /* 83 */
200.                                ELSE
201.                                        EXIT;
202.                                END IF;
203.                        END LOOP;
204.                END IF;
205.        END IF;
206. 
207./*   集計  */
208.        ERR_FLG := 3;
209.        IF P区分 = '3'  and EXIT_FLG = 0 THEN
210.                UPDATE TR07 SET
211.                         "入出荷数量1"               = "入出荷数量1" + P数量1
212.                        ,"入出荷数量2"               = "入出荷数量2" + P数量2
213.                        ,"残高数量1"                = "残高数量1" - P数量1
214.                        ,"残高数量2"                = "残高数量2" - P数量2
215.                WHERE   "識別A" = '7' AND "識別C" = ' ' AND "入力組織" = P組織 AND "伝票番号" = P受注番号 AND "登録行" = P行;
216.                IF SQL%NOTFOUND THEN
217.                        ERR := ERR_FLG;
218.                ELSE
219.                        SELECT NVL("残高数量2",0),完了 INTO V数量,V完了  FROM TR07
220.                                WHERE
221.                                "識別A" = '7'
222.                                AND "識別C" = ' '
223.                                AND "入力組織" = P組織
224.                                AND "伝票番号" = P受注番号
225.                                AND "登録行" = P行;
226.                        IF V完了 is NULL THEN
227.                                IF V数量 <= 0 THEN
228.                                        UPDATE TR07 SET "完了" = '1'
229.                                        WHERE
230.                                        "識別A" = '7'
231.                                        AND "識別C" = ' '
232.                                        AND "入力組織" = P組織
233.                                        AND "伝票番号" = P受注番号
234.                                        AND "登録行" = P行;
235.                                END IF;
236.                        ELSE
237.                                IF V数量 > 0 THEN
238.                                        UPDATE TR07 SET "完了" = NULL
239.                                        WHERE  
240.                                        "識別A" = '7'
241.                                        AND "識別C" = ' '
242.                                        AND "入力組織" = P組織
243.                                        AND "伝票番号" = P受注番号
244.                                        AND "登録行" = P行;
245.                                END IF;
246.                        END IF;
247.                END IF;
248.        END IF;
249. 
250.        ERR_FLG := 4;
251.        IF ( P区分 = '4' or P区分 = '5' ) and EXIT_FLG = 0 THEN
252.                LOOP
253.                        UPDATE SZ03 SET
254.                                "現在現物数量2"               = "現在現物数量2"     + V数量2,
255.                                "更新日"           = sysdate,
256.                                "在庫更新日"         = sysdate
257.                        WHERE   "組織" = V在庫組織 AND "大分類" = V大分類 AND "商品コード" = V商品コード;
258.                        IF SQL%NOTFOUND THEN
259.                                INSERT INTO SZ03 VALUES
260.                                (V在庫組織,V大分類,V商品コード
261.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
262.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
263.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
264.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
265.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
266.                                ,0,0,0,0,0,0,0,0,0
267.                                ,NULL,NULL,NULL,NULL,NULL,NULL,sysdate,NULL,sysdate,0,0
268.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
269.                                ,0,0,0);        /* 142 */
270.                        ELSE
271.                                EXIT;
272.                        END IF;
273.                END LOOP;
274.                LOOP
275.                        UPDATE SZ04 SET
276.                                "現在現物数量2"               = "現在現物数量2"     + V数量2,
277.                                "更新日"           = sysdate,
278.                                "在庫更新日"         = sysdate
279.                        WHERE  
280.                                "組織" = V在庫組織
281.                                AND "大分類" = V大分類
282.                                AND "商品コード" = V商品コード
283.                                AND "場所区分" = NVL(V場所区分,' ')
284.                                AND "場所コード" = NVL(V場所コード,' ')
285.                                AND "単品コード" = NVL(V単品コード,' ');
286.                        IF SQL%NOTFOUND THEN
287.                                INSERT INTO SZ04 VALUES
288.                                (V在庫組織,NVL(V場所区分,' '),NVL(V場所コード,' '),V大分類,V商品コード
289.                                ,NVL(V単品コード,' ')
290.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0
291.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
292.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
293.                                ,0,0,0,0,0,0,0,0
294.                                ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
295.                                ,sysdate,NULL,sysdate,0);       /* 83 */
296.                        ELSE
297.                                EXIT;
298.                        END IF;
299.                END LOOP;
300.        END IF;
301. 
302.        ERR_FLG := 6;
303.        IF ( P区分 = '6' ) and EXIT_FLG = 0  THEN
304.        OPEN cur_TR01;
305. 
306.                FETCH   cur_TR01 INTO
307.                        V認識;
308.                if cur_TR01%FOUND then
309.                        V認識 := 2;
310.                else
311.                        V認識 := 1;
312.                end if;
313.        CLOSE cur_TR01;
314.        IF  V認識 = '1' THEN
315. 
316.                LOOP
317. 
318.                 
319.                        UPDATE SZ03 SET
320.                                "現在現物数量2"               = "現在現物数量2"     + V数量2,
321.                                "更新日"           = sysdate,
322.                                "在庫更新日"         = sysdate
323.                        WHERE   "組織" = V在庫組織 AND "大分類" = V大分類 AND "商品コード" = V商品コード;
324.                        IF SQL%NOTFOUND THEN
325.                                INSERT INTO SZ03 VALUES
326.                                (V在庫組織,V大分類,V商品コード
327.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
328.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
329.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
330.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
331.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
332.                                ,0,0,0,0,0,0,0,0,0
333.                                ,NULL,NULL,NULL,NULL,NULL,NULL,sysdate,NULL,sysdate,0,0
334.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
335.                                ,0,0,0);        /* 142 */
336.                        ELSE
337.                                EXIT;
338.                        END IF;
339.                END LOOP;
340.                LOOP
341.                        UPDATE SZ04 SET
342.                                "現在現物数量2"               = "現在現物数量2"     + V数量2,
343.                                "更新日"           = sysdate,
344.                                "在庫更新日"         = sysdate
345.                        WHERE  
346.                                "組織" = V在庫組織
347.                                AND "大分類" = V大分類
348.                                AND "商品コード" = V商品コード
349.                                AND "場所区分" = NVL(V場所区分,' ')
350.                                AND "場所コード" = NVL(V場所コード,' ')
351.                                AND "単品コード" = NVL(V単品コード,' ');
352.                        IF SQL%NOTFOUND THEN
353.                                INSERT INTO SZ04 VALUES
354.                                (V在庫組織,NVL(V場所区分,' '),NVL(V場所コード,' ')
355.                                ,V大分類,V商品コード,NVL(V単品コード,' ')
356.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0
357.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
358.                                ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
359.                                ,0,0,0,0,0,0,0,0
360.                                ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
361.                                ,sysdate,NULL,sysdate,0);       /* 83 */
362.                        ELSE
363.                                EXIT;
364.                        END IF;
365.                 
366.                END LOOP;
367.         
368.        END IF;
369.        END IF;
370./*=================================
371.   終了処理
372.=================================*/
373.DBMS_OUTPUT.PUT_LINE('JHUPD NORMAL END');
374. 
375.EXCEPTION
376.        WHEN OTHERS THEN
377.--              ERR_FLG := 1;
378.                ERR := ERR_FLG;
379.                DBMS_OUTPUT.PUT_LINE('ERROR AT JHUPD');
380.                ROLLBACK TO UPD_START;
381.END JHUPD;
382./