SAK 図書館
SAK 図書館
Oracle PL/SQL 編5 - カーソル、カーソル for ループ、例外識別子、例外処理
■カーソル操作 (フェッチ、カーソル移動)
set serveroutput on;
declare
jcrec 受注m%rowtype;
cursor cu is select * from 受注m where 品番 = 'a001';
begin
open cu;
loop
fetch cu into jcrec;
exit when cu%notfound;
dbms_output.put_line (jcrec.受注番号);
end loop;
close cu;
end;
/
■カーソル変数操作 (フェッチ、カーソル移動)
set serveroutput on;
declare
type tpjcrec is ref cursor return 受注m%rowtype;
--type tpjcrec is ref cursor; /* 制限的でない弱い型 */
cv tpjcrec;
jcrec 受注m%rowtype;
begin
open cv for select * from 受注m where 品番 = 'a001';
loop
fetch cv into jcrec;
exit when cv%notfound;
dbms_output.put_line (jcrec.受注番号);
end loop;
close cv;
end;
/
■カーソル for ループ (カーソルループ、カーソル移動)
set serveroutput on;
declare
cursor cu is select * from 受注m where 品番 = 'a001';
begin
for cu_rec in cu loop
dbms_output.put_line (cu_rec.受注番号);
end loop;
end;
/
■カーソル for ループ (副問い合わせ、副問合わせ)
set serveroutput on;
declare
begin
for cu_rec in (select * from 受注m) loop
dbms_output.put_line (cu_rec.受注番号);
end loop;
end;
/
■レコードグルーブシーケンス番号を付与して、CSV 形式で問い合わせ
set serveroutput on;
declare
cursor c1 is select 得意先CD, 品番
from 受注m order by 得意先CD, 品番;
ct number;
svtkcd varchar2(20) := ' ';
begin
dbms_output.put_line ('start...');
for rec in c1 loop
if svtkcd != rec.得意先CD then
ct := 0;
svtkcd := rec.得意先CD;
end if;
ct := ct + 1;
dbms_output.put_line (
'"' || ct || '","'
|| rec.得意先CD || '","'
|| rec.品番 || '"'
);
end loop;
dbms_output.put_line ('end');
exception
when OTHERS then
dbms_output.put_line ('** エラー **');
end;
/
start...
"1","1000","a001"
"2","1000","b002"
"3","1000","c003"
"1","2000","a001"
"2","2000","b002"
end
■カーソル属性
カーソル%found fetch が行を戻せば TRUE
カーソル%isopen オープンされていれば TRUE
カーソル%notfound fetch が行を戻さなければ TRUE
カーソル%rowcount fetch された行数
■暗黙カーソル属性
sql%found insert、update、delete が 1 行以上処理すれば TRUE
sql%isopen 常に FALSE
sql%notfound insert、update、delete が処理対象なしの場合、TRUE
sql%rowcount insert、update、delete が処理したレコード数
sql%%bulk_rowcount forall での処理レコード数
** 暗黙カーソル属性は、レコード追加件数、レコード修正件数、
レコード削除件数など、レコード更新件数取得に使用可能です。
■テーブルロック
lock table 受注m in row share mode nowait; 行共有モード
■例外識別子
ACCESS_INTO_NULL 未初期化オブジェクトに代入した ORA-06530
COLLECTION_IS_NULL コレクション未初期化 ORA-06531
CURSOR_ALREADY_OPEN カーソルが既にオープンされている ORA-06511
DUP_VAL_ON_INDEX キーが重複した ORA-00001
INVALID_CURSOR カーソルがオープンされていない ORA-01001
INVALID_NUMBER 数値が正しくない ORA-01722
LOGIN_DENIED ログインできない ORA-01017
NO_DATA_FOUND SELECT INTO レコードなし ORA-01403
NOT_LOGGED_ON ログインできていない ORA-01012
PROGRAM_ERROR PL/SQL 内部エラー ORA-06501
ROWTYPE_MISMATCH 互換性なし ORA-06504
STORAGE_ERROR PL/SQL メモリ不足 ORA-06500
SUBSCRIPT_BEYOND_COUNT コネクション要素オーバ ORA-06533
SUBSCRIPT_OUTSIDE_LIMIT 有効範囲外添字 ORA-06532
TIMEOUT_ON_RESOURCE タイムアウト ORA-00051
TOO_MANY_ROWS 複数行あり ORA-01422
VALUE_ERROR 算術エラー ORA-06502
ZERO_DIVIDE ゼロの割り算 ORA-01476
・SQLCODE としては、ORA-06511 の値がマイナスで返る。
ORA-06511 なら、-6511。
但し、ORA-01403 は、SQLCODE として、+100 が返るので注意する。
・SQLERRM では、SQLCODE に対応するエラーメッセージを取得できる。
errmsg := sqlerrm(-6511);
■例外処理(EXCEPTION)
・others で、なにかエラーがあれば、rollback する。
declare
jcsu number(12, 2);
begin
jcsu := 100;
insert into 受注m values ('111111-011', 'a001', jcsu, '1000',
'sak', '2001.08.25', '07:02:00');
insert into 受注m values ('111111-011', 'a001', jcsu, '1000',
'sak', '2001.08.25', '07:02:00');
commit;
exception
when others then
rollback;
end;
/
・特定のエラーと、その他エラーで対処が異なる場合は、次のようにする。
declare
jcsu number(12, 2);
begin
jcsu := 100;
insert into 受注m values ('111111-011', 'a001', jcsu, '1000',
'sak', '2001.08.25', '07:02:00');
insert into 受注m values ('111111-011', 'a001', jcsu, '1000',
'sak', '2001.08.25', '07:02:00');
commit;
exception
when dup_val_on_index then
...
...
rollback;
when others then
rollback;
end;
/
・正常、エラーにかかわらず、共通の処理がある場合は、次のようにする。
declare
jcsu number(12, 2);
begin
...
...
begin
jcsu := 100;
insert into 受注m values ('111111-011', 'a001', jcsu, '1000',
'sak', '2001.08.25', '07:02:00');
insert into 受注m values ('111111-011', 'a001', jcsu, '1000',
'sak', '2001.08.25', '07:02:00');
commit;
exception
when dup_val_on_index then
...
...
rollback;
when others then
rollback;
end;
...
...
end;
/
■例外呼び出し(RAISE、RAISE_APPLICATION_ERROR)
・ユーザ例外を定義し利用するには、次のようにする。
declare
USER_ERR_TEST exception;
begin
...
if ... then
raise USER_ERR_TEST;
end if;
...
exception
when USER_ERR_TEST then
...
when others then
...
end;
/
・ユーザ例外エラーを呼び出しもとに返すには、次のようにする。
呼び出しもとは、pragma exception_init(err_call, -20001); を使用して、
exception when err_call then... のように処理することができる。
declare
...
begin
...
if ... then
raise_application_error(-20001, 'ストアドエラー');
end if;
...
end;
/
■Oracle PL/SQL 編資料
■SQL 基礎実地編資料
■SQL 基礎編資料
■SQL チューニング編資料
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■MySQL 編資料
Copyright (C) 1999-2018 Y.SAK