PL/SQLのプロシージャについて

Question

実現したいこと

テーブル（任意_TAB）の番号（NO）順に順次読み込み、点数（TENSU）を次の人の点数（TENSU）に加算していくプロシージャを作成したい。

発生している問題・エラーメッセージ

Warning:Procedure created with compilation errors.

該当のソースコード

CREATED OR REPLACE PROCEDURE C1. 
IS. 
   CURSOR C1cur IS SELECT NO, NAME,   TENSU FROM OTHER_TAB ORDER BY NO FOR UPDATE;   
   orec C1cur%ROWTYPE;  
BEGIN. 
 OPEN C1cur;  
 LOOP.   
 FETCH C2cur INTO orec;  
 EXIT WHEN C1cur%NOTFOUND;  
 DBMS_OUTPUT.LINE( orec.NO || NAME || orec.TENSU );  
END LOOP;  
CLOSE C1cur;  
END;  
/

補足情報（FW/ツールのバージョンなど）

そもそも「加算していく」の部分があっているか自身がないので、
SELECT NO, NAME, TENSU FROM OTHER_TAB ORDER BY NO FOR UPDATE;

を試したところ、ERROR at line1:
ORA-009923: FROM keyword not found where expected. と表示されました。

Comments

[m.ts10806]

SQLServerは本件とどう関係するのでしょうか。
PL/SQLであればタグ説明にあるようにOracleだと思うのですが。
https://teratail.com/tags/PL%2FSQL
SQLServerであれば同列なのはTransact-SQLですかね。
私が知らないだけかもしれませんが、キーワードなので結構大事です。

[sazi]

エラーメッセージがoracleのものなので、oracleでしょうね。

[XiiTuzi]

コンパイル可能な状態にして、まずは今のソースでどう出力されるのかを確認したほうが良いと思います。
想像しているのとなんか違う・・・と感じるはずなので、「なんか違う」点を小分けにして解消していくのが一番の近道でしょう。

[FULLSAT_NOWTHEY]

@m.ts10806様
コメントありがとうございます。
>SQLServerは本件とどう関係するか
正直に申し上げますとその辺りの知識が無いため、関連がありそうなものをとりあえず並べた、次第であります。
誤解を招いてしまい、申し訳ありません。

[m.ts10806]

質問は編集できますので

Answer 1

問題点は複数あります。
1つ目のエラーは「PL/SQLの構文おかしいよ」と言っています。
基本構文を学習しましょう。（基本構文が趣旨ではないと思うので、割愛します）

2つ目のエラーは「カーソル内のSQLがおかしいよ」と言っています。
エラー内容を翻訳する所から始めましょう。

最後に、そもそも加算処理がありません。
加算処理についてのみ、以下に回答します。

SQLにて集計する方法は他の方が回答されていますが、
変数を定義し、LOOP内で変数に加算していく方法もあります。

PL/SQLを使用するのであれば、加算の条件等の「なんらかの処理」も必要になってくる場合があるので、
後の要件追加も考慮してLOOP内で加算処理を実装する方が良いでしょう。

以下は簡素なサンプルです。※手元に検証環境がないため、動作確認はしていません。

declare
  l_num number;
begin
  l_num := 0;
--
  for i in 1 .. 10
  loop
    l_num := l_num +1;
  end loop;
  --
  dbms_output.put_line(l_num);
end;

Comments

[dameo]

l_num が初期化されてないのでこれだとNULLで何も出力されません。
確認できない状況でコードを貼るのは混乱の元です。

[dameo]

@質問者さん
一応質問文のコードを修正したものを入れておきます。コピペするのではなく、自分のコードを修正する際の参考にしてください

-- ■準備
-- サーバー出力を表示する設定
SET SERVEROUTPUT ON;
-- テーブル定義がないと質問者さん以外の環境で動かないので記述(適当)
create table other_tab(no number(20) primary key, name varchar2(100), tensu number(20));

-- ダミーデータを1行追加
insert into other_tab values(1000000, 'example', 50);
-- oracleは普通auto commitじゃないのでcommit
commit;
-- 表示して内容を確認
select * from other_tab;

-- ■プロシージャの登録
CREATE OR REPLACE PROCEDURE C1
IS
-- カーソル宣言(クエリにFOR UPDATEがついてますが、普通は使いません。厳密な集計だとあっても可)
CURSOR C1cur IS SELECT NO, NAME, TENSU FROM OTHER_TAB ORDER BY NO FOR UPDATE;
-- レコード(1行文のデータ)を記憶する変数
orec C1cur%ROWTYPE;
BEGIN
-- カーソルをオープン
OPEN C1cur;
-- 無条件にループ
LOOP
-- 1行文のデータをオープンしたカーソルから取得
FETCH C1cur INTO orec;
-- データが取得できなかったらループを抜ける
EXIT WHEN C1cur%NOTFOUND;
-- データが取得できたら中身を表示する(本当は区切り記号とか欲しい)
DBMS_OUTPUT.PUT_LINE( orec.NO || orec.NAME || orec.TENSU );
-- 本当はココに加算処理が欲しい
END LOOP;
-- カーソルをクローズ
CLOSE C1cur;
END;
/

-- ■プロシージャの呼び出し
call c1();

[XiiTuzi]

@dameo
ご指摘ありがとうございます。仰る通り、これは学習において弊害となるコードでした。
修正しました。

[FULLSAT_NOWTHEY]

@XiiTuzi様、コメントありがとうございます。
1、>SQLの構文おかしいよ〜
宣言部が抜けておりました。

2、>カーソル内のSQLがおかしいよ〜
日頃からGoogle翻訳を使用し、何を言っているのかは分かるのですが、解決の仕方が分からないので調べながら進めております。

>そもそも加算処理がない
サンプルの提供ありがとうございます。参考にさせて頂きます。

[FULLSAT_NOWTHEY]

@dameo様、コメントありがとうございます。
コードの提供、1行ごとの解説、ご丁寧にありがとうございます。
参考にさせて頂きます。

また、
>--本当はココに加算処理が欲しい
というのは、DBMS〜とEND LOOP;の間に入れるという認識で間違い無いでしょうか？

Answer 2

CREATE PROCEDURE文の最後の行にあるピリオド(.)は意図的なものですか。削除でどう表示変わりますか？
かつ、SELECT文の末尾にFOR UPDATEを追加することで、SELECT文で取得したレコードが更新対象になるようになります。これにより、他のトランザクションから同時に更新されることが防止されます。

Comments

[dameo]

まずは文法エラーであることを言い切らないと伝わらないと思います。
悪くはない回答だと思うのですが手を付けるにしては中途半端です。

[FULLSAT_NOWTHEY]

@hiroki-sys様、コメントありがとうございます。
文末の.(ピリオド)についてですが、MarkDown記法を使い、改行の際にスペースを2つ入れたところ自動で入力されたのでそのままにしてしまいました。

Answer 3

累計ならWindow関数が利用できます。
分析関数の世界への扉を開く

select *, sum(tensu) over(order by no) as 累計 from 任意_TAB

尚、累計値をテーブルに保持するのは正規化の観点からお勧めしません。

Comments

[dameo]

そういうレベルの話ではない上に、累計値は締め処理など入力値が変わらない前提の集計テーブルなどでよく使用されたりするので、前提を明確にして説明しましょう(したければ)。

[FULLSAT_NOWTHEY]

@sazi様、コメントありがとうございます。
｢分析関数の世界への扉を開く｣は、今回の件を調べるにあたって、よく見かけたページでもあるので、チェックしながら作業を進めております。
情報提供ありがとうございます。