DECLARE CURSOR について…

質問

VB6からOracleにつないでいるのですが、はじめたばかりで意味がよくわかりません。以下のものをフローチャートにおこしたいのです。
'空番号検索
SqlTxt = ""
SqlTxt = SqlTxt & "DECLARE "
SqlTxt = SqlTxt & " CURSOR C1 IS "
SqlTxt = SqlTxt & " SELECT @1 FROM " & MyTable & " WHERE @1 BETWEEN @2 AND @3 ORDER BY CODE; "
SqlTxt = SqlTxt & " PREC C1%ROWTYPE; "
SqlTxt = SqlTxt & " i NUMBER; "
SqlTxt = SqlTxt & " NEWCODE NUMBER; "
SqlTxt = SqlTxt & "BEGIN "
SqlTxt = SqlTxt & " :RetValue := -1; "
SqlTxt = SqlTxt & " I := @2 - 1; "
SqlTxt = SqlTxt & " NEWCODE := -1; "
SqlTxt = SqlTxt & " OPEN C1; "
SqlTxt = SqlTxt & " LOOP "
SqlTxt = SqlTxt & " FETCH C1 INTO PREC; "
SqlTxt = SqlTxt & " EXIT WHEN C1%NOTFOUND; "
SqlTxt = SqlTxt & " i := i + 1; "
SqlTxt = SqlTxt & " IF PREC.CODE <> i THEN "
SqlTxt = SqlTxt & " NEWCODE := i; "
SqlTxt = SqlTxt & " EXIT; "
SqlTxt = SqlTxt & " END IF; "
SqlTxt = SqlTxt & " END LOOP; "
SqlTxt = SqlTxt & " CLOSE C1; "
SqlTxt = SqlTxt & " :RetValue := NEWCODE; "
SqlTxt = SqlTxt & "EXCEPTION "
SqlTxt = SqlTxt & " WHEN OTHERS THEN "
SqlTxt = SqlTxt & " :RetValue := -1; "
SqlTxt = SqlTxt & "END; "
SqlTxt = fChangeStr(SqlTxt, "@1", "CODE")
SqlTxt = fChangeStr(SqlTxt, "@2", fSqlStr(t_Code, fSqlStr_Long))
SqlTxt = fChangeStr(SqlTxt, "@3", fSqlStr(SysMaxCode, fSqlStr_Long))
OraDb.ExecuteSQL (SqlTxt)
NewCode = fToNumber(OraDb.Parameters("RetValue").Value)
OraDb.Parameters.Remove ("RetValue")
暇なときにでもアドバイスいただけたら幸いです。

回答No.1

steerboard

余りに丸投げなご質問なのでちょっと閉口。。。

至って簡単なPL/SQLなのでネットで構文調べれば分かるかと思います。
一応インデント付けて読みやすくしてみました。

DECLARE
　CURSOR C1 IS
　　SELECT @1 FROM " & MyTable & " WHERE @1 BETWEEN @2 AND @3 ORDER BY CODE;

　PREC C1%ROWTYPE;
　i NUMBER;
　NEWCODE NUMBER;

BEGIN
　:RetValue := -1;
　I := @2 - 1;
　NEWCODE := -1;

　OPEN C1;
　LOOP
　　FETCH C1 INTO PREC;
　　EXIT WHEN C1%NOTFOUND;

　　i := i + 1;
　　IF PREC.CODE <> i THEN
　　　NEWCODE := i;
　　　EXIT;
　　END IF;
　END LOOP;
　CLOSE C1;

　:RetValue := NEWCODE;

EXCEPTION
　WHEN OTHERS THEN
　:RetValue := -1;
END;