Oracle PL/SQL メモ

記憶が微かになってきたので、再学習したことのメモ。
覚えては忘れて検索するので。。

serveroutput

serveroutputを有効にしないと、dbms_output.put_lineの出力がコンソールに出力されない。たまに忘れててはまる。。

set serveroutput [on | off] 

on　じゃなくて、バッファサイズや、UNLIMITEDを指定もできる。
昔（9iぐらい）は、MAXのバッファサイズを指定していた。いつからか制限なしも選べるようになってて驚いた。出力が多すぎても、問題になることがあるので程々がいいのかな。。

コメントのつけ方

• 単一行コメント：--
  

-- 単一行コメント

• 複数行コメント：/* */

/*
    複数行コメント１
    複数行コメント２　
*/

変数宣言

変数名 データ型 [NOT NULL] [{ := | DEFAULT } <値> };

--例
v_no NUMBER(2);
v_no NUMBER(2) := 10;
v_no NUMBER(2) NOT NULL := 0;
v_no NUMBER(2) NOT NULL DEFAULT 1;

• NOT NULL を指定した場合、初期値も設定する。
• データ型のサイズを基本指定する。しないとデフォルト値？。VARCHAR2はサイズ指定が必要

定数宣言

定数名 CONSTANT データ型 [NOT NULL] [{ := | DEFAULT } <初期値> };

制御文

IF文

IF <条件式> THEN

ELSIF <条件式> THEN

ELSE

END IF;

CASE文

CASE文

CASE <選択子式>
  WHEN <条件式１> THNE
  WHEN <条件式２> THNE
  ELSE
END CASE;

--例
declare
  val number(1) := 1;
begin
  case val
    when 0 then
      DBMS_OUTPUT.PUT_LINE('CASE0');
    when 1 then
      DBMS_OUTPUT.PUT_LINE('CASE1');
    else
      DBMS_OUTPUT.PUT_LINE('CASE ELSE');
  end case;
end;

• ELSEは必須
• 選択子式は、文字でも数でも大丈夫

検索CASE文

CASE
  WHEN <条件式１> THNE
  WHEN <条件式２> THNE
  ELSE
END CASE;

--例
declare
  val varchar2(2) := 'AA';
begin
  case
    when val = 'AA' then
      DBMS_OUTPUT.PUT_LINE('CASEAA');
    when val = 'BB' then
      DBMS_OUTPUT.PUT_LINE('CASEBB');
    else
      DBMS_OUTPUT.PUT_LINE('CASE ELSE');
  end case;
end;

• 検索CASE文かIF文を使うかは、好み

LOOP文

基本LOOP文

種類	概要
EXIT文	無条件にLOOPを終了
EXIT WHEN文	LOOP文の終了条件を記述、条件を満たせば終了

LOOP

END LOOP;

-- 例
declare
  val number(2) := 0;
begin
  loop
    exit when val = 2;
    dbms_output.put_line(val);
    val := val + 1;
  end loop;
end;

FOR-LOOP文

FOR <ループカウンタ> IN [REVERSE] <初期値> .. <終了値> LOOP

END LOOP;

--例
begin
  for i in 1..3 loop
    dbms_output.put_line(i);
  end loop;
end;

ループカウンタについて

• 宣言部で定義不要
• そのループ文の中でのみ参照可能

WHILE-LOOP文

WHILE <条件式> LOOP

END LOOP;

--例

declare
  val number(1) := 0;
begin
  while val < 3 loop
    dbms_output.put_line(val);
    val := val + 1;
  end loop;
end;

CONTINUE文

Oracle11g R11.1から追加された機能
条件式を満たすときだけ、処理を中断し、LOOPの最初に移動する。

CONTINUE WHEN <条件式>

--例

begin
  for i in 0..5 loop
    CONTINUE WHEN i = 2;
    dbms_output.put_line(i);
  end loop;
end;

--実行結果
0
1
3
4
5

NULL文

何もしないことを明確にしたいときに使う

--例１
declare
  val number(1) := 0;
begin
  if val = 0 then
    dbms_output.put_line('hoge');
  else
    null;
  end if;
end;

--例２
declare
  val number(1) := 0;
begin
  null;
end;

SELECT INTO文、カーソル文

よく使う

• SELECT INTO文
  検索データが１行の時に使う。
• カーソル文
  検索データが複数行の時に使う。

SELECT INTO文

SELECT <列名> [,<>] INTO <変数> [,<>] FROM <表名>;

--例 行が複数返却される
declare
  m_no member.no%type;
  m_name member.name%type;
begin
  select no, name into m_no, m_name from member;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
end;
-- 結果
ORA-01422: 完全フェッチがリクエストよりも多くの行を戻しました

--例　０件
declare
  m_no member.no%type;
  m_name member.name%type;

begin

  select no, name into m_no, m_name from member where no is null;

exception 
  when others then
    dbms_output.put_line(sqlerrm);

end;
--結果
ORA-01403: データが見つかりません。

-- 例　正常終了
declare
  m_no member.no%type;
  m_name member.name%type;

begin

  select no, name into m_no, m_name from member where no = '000001';

exception 
  when others then
    dbms_output.put_line(sqlerrm);

end;

SELECT INTO文で使用したmemberテーブルは以下のとおり。

NO	NAME	CLASS_CODE	AGE
000001	田中　太郎	1	18
000002	轟　太郎	1	18
000004		1	18
000003	伊藤　太郎	1	18

• 問合せ結果は必ず１行にする。
  複数行：TOO_MANY_ROWS例外(ORA-01422)が発生する
  ０件：NO_DATA_FOUND例外(ORA-01403)が発生する
• 列と変数名は一致させる。

カーソル文

カーソルの使い方

declare
  ①カーソルの定義
  CURSOR <カーソル名> IS <SELECT文>;
begin
  ②カーソルのオープン
  OPEN <カーソル名>; -- SQLが実行され、結果セット(SQLの検索結果)ができている
  loop
    ③データの取り出し --結果セットからデータを１件ずつ取り出す。
    FETCH <カーソル名> INTO <変数名>; 
  end loop;
  ④カーソルのクローズ
  CLOSE <カーソル名>;
end;

--例
declare
  cursor cs is select no, name from member;
  -- レコード型の定義
  type rec is record (
  wk_no member.no%type,
  wk_name member.name%type
  );
  -- レコード型の宣言
  wk_rec rec;
begin
  open cs;
  loop
    fetch cs into wk_rec;
    exit when cs%notfound;
      dbms_output.put_line('['||wk_rec.wk_no||'].['||wk_rec.wk_name||']');

  end loop;
  close cs;
exception
  when others then
    dbms_output.put_line(SQLERRM);
end;

カーソル変数

• 特定のSELECT文しか実行できないカーソルとは違い、動的なSQLを実行できる。
• データ型の１つである「FOR CURSOR型」で定義した変数を、カーソル変数という。

書き方

1. カーソル変数の定義
2. OPEN FOR文の実行
3. カーソル処理

宣言

1. REF CURSOR型の定義
TYPE <カーソル型> IS REF CURSOR;

2. カーソルの定義
<カーソル変数名> <カーソル型>;

実行

カーソル変数に、問合せ文を紐づける

OPEN <カーソル変数名> FOR <問合せ文>;

例１：基本

declare
  type cs is ref cursor;
  wk_cs cs;

  mem member%rowtype;
  i number(2) := 0;
begin
  open wk_cs for select * from member;
  loop
    fetch wk_cs into mem;
    i := i + 1;
    exit when wk_cs%notfound;
    dbms_output.put_line(i||':'||mem.no);
    dbms_output.put_line(i||':'||mem.name);
  end loop;
  close wk_cs;
exception
  when others then
    dbms_output.put_line(SQLERRM);

end;
--結果
1:000001
1:田中　太郎
2:000002
2:轟　太郎
3:000004
3:
4:000003
4:伊藤　太郎

例２：パラメータを使用したカーソル

declare
  type csPPP is ref cursor;
  cs_p csPPP;
  cRec classMaster%rowtype;

  i number(2) := 0;

  cursor csSSS( code in varchar2 ) is
    select
       no
      ,name
      ,class_code
    from member
    where
      class_code = code;
begin
  open cs_p for select * from classMaster;
  loop
    fetch cs_p into cRec;
    exit when cs_p%notfound;
      i := i + 1;
      dbms_output.put_line(i||':'||cRec.class_code||':'||cRec.class_name);

      for cs_s in csSSS(cRec.class_code) loop
        dbms_output.put_line(cRec.class_code||':'||cs_s.no);
        dbms_output.put_line(cRec.class_code||':'||cs_s.name);
      end loop;
  end loop;
  close cs_p;
exception
  when others then
    dbms_output.put_line(SQLERRM);
end;

--結果
1:1:一般社員
1:000001
1:田中　太郎
1:000002
1:轟　太郎
1:000004
1:
1:000006
1:岡　太郎
2:2:課長
2:000003
2:伊藤　太郎
3:3:部長

レコード、コレクション

レコード

• %ROWTYEP：既存表の定義を使った、レコード変数
• ユーザ定義レコード：ユーザが定義したレコード変数

ユーザ定義レコード

特徴

• フィールドのデータ型に、%TYPE,%ROWTYPEを使える
• フィールドにNOT NULLを使える
• フィールドにデフォルト値を指定できる
• 型の定義、変数の宣言が必要

1. レコード型の定義
TYEP <レコード型名> IS RECORD
(
  <フィールド名> <データ型> [ NOT NULL {:= | DEFAULT} <デフォルト値>]
  [, <>]
);
2. 変数の定義
<レコード名> <レコード型名>

レコード型の使い方

同じレコード型の変数同士は、そのまま代入できる

レコード変数1.項目名 := レコード変数2.項目名 のように１つ１つ代入しなくてもいい

declare
  cursor cs is select no, name from member where rownum = 1;
  -- レコード型の定義
  type def_rec is record (
  wk_no member.no%type,
  wk_name member.name%type
  );
  -- レコード型の宣言
  rec1 def_rec;
  rec2 def_rec;
begin
  open cs;
  loop
    fetch cs into rec1;
    exit when cs%notfound;
      dbms_output.put_line('rec1['||rec1.wk_no||'].['||rec1.wk_name||']');
  end loop;
  close cs;
  rec2 := rec1;
  dbms_output.put_line('rec2['||rec2.wk_no||'].['||rec2.wk_name||']');
exception
  when others then
    dbms_output.put_line(SQLERRM);
end;
-- 結果
rec1[000001].[田中　太郎]
rec2[000001].[田中　太郎]

パラメータ、ファンクションの戻り値に使える

create or replace procedure printRecord( rec member%rowtype)
is
begin
  dbms_output.put_line(rec.no||'.'||rec.name);

end;

declare
  row member%rowtype;
begin
  select * into row from member where rownum = 1;
  printRecord(row);

end;

--結果
000001.田中　太郎

挿入と、更新

挿入

INSERT INTO <テーブル名> VALUES <レコード名>

• フィールドの項目属性は相互でそろえる

declare
  row1 member%rowtype;
  row2 member%rowtype;

begin
  select * into row1 from member where rownum = 1;

  row1.no := '000006';
  insert into member values row1;
  commit;

  select * into row2 from member where no = '000006';
  dbms_output.put_line('no:'||row2.no);
  dbms_output.put_line('name:'||row2.name);
  dbms_output.put_line('class_code:'||row2.class_code);

end;

--結果
no:000006
name:田中　太郎
class_code:1

更新

UPDATE <表名> SET ROW = <レコード名>;

ROWの注意点

• 副問合せと一緒には使えない
• 許可されるSET句は１つのみ

declare
  row1 member%rowtype;
  row2 member%rowtype;
begin
  select * into row1 from member where no = '000006';

  row1.name := '岡　太郎';
  update member set row = row1 where no = '000006';
  commit;

  select * into row2 from member where no = '000006';
  dbms_output.put_line('no:'||row2.no);
  dbms_output.put_line('name:'||row2.name);
  dbms_output.put_line('class_code:'||row2.class_code);

end;

-- 結果
no:000006
name:岡　太郎
class_code:1

コレクション

レコード型と、コレクション型の違い。

レコード型

フィールド名１	フィールド名２

コレクション型

索引	要素
1
2
n

コレクションの種類

• 結合配列（＝索引付き表、PL/SQL表）
• ネストした表
• VARRAY

結合配列の使用が一般的らしい。

結合配列

結合配列を使用するためには以下の２段階を踏む。

1. 型の定義
2. 変数の定義

宣言

型の定義

TABLE型を定義する。

TYPE <テーブル型> IS TABLE OF <データ型> [NOT NULL]
  INDEX BY {
    BINARY_INTEGER | PLS_INTEGER | <文字データ型> (<上限サイズ>)
  };

BINARY_INTEGERと、PLS_INTEGERは同じものらしい。
範囲は、「-2,147,648,467〜2,147,648,467」
PLS_INTEGERおよびBINARY_INTEGERデータ型

結合配列の定義

定義したテーブル型と結合配列を紐づける。

<結合配列名> <テーブル型名>;

使用方法

結合配列の要素を参照するには、索引番号を使う。
要素に値を代入するには、代入演算子(:=)や、SELECT INTO や、FETCH INTO を使う。

<結合配列名> (<索引番号>);

declare
  type tab is table of varchar2(10) index by pls_integer;
  wk_tab tab;

begin
  wk_tab(1) := 'hoge1';
  wk_tab(2) := 'hoge2';
  wk_tab(3) := 'hoge3';

  for i in 1..3 loop
    dbms_output.put_line(wk_tab(i));
  end loop;

exception
  when others then
    dbms_output.put_line(SQLERRM);

end;

---結果
hoge1
hoge2
hoge3

データ型には、ユーザ定義レコード型や%ROWTYPEが使える
要素へのアクセスは

<結合配列名>(<索引番号>).<フィールド名>

declare
  type myrec is record
  (
    no member.no%TYPE,
    temp varchar2(20)
  );

  wkrec myrec;

  type tab is table of myrec index by pls_integer;
  wk_tab tab;

begin
  wk_tab(1).no := '000009';
  wk_tab(1).temp := 'hoge1';
  wk_tab(2).no := '000010';
  wk_tab(2).temp := 'hoge2';

  for i in 1..2 loop
    dbms_output.put_line(wk_tab(i).no);
    dbms_output.put_line(wk_tab(i).temp);
  end loop;

exception
  when others then
    dbms_output.put_line(SQLERRM);

end;

--結果
000009
hoge1
000010
hoge2

declare

  type tab is table of number(2) index by varchar2(15);
  wk_tab tab;

begin
  wk_tab('AA') := 1;
  wk_tab('BB') := 2;

  dbms_output.put_line(wk_tab('AA'));
  dbms_output.put_line(wk_tab('BB'));

exception
  when others then
    dbms_output.put_line(SQLERRM);

end;

--結果
1
2

無名ブロック

とっても便利

基本構文

declare --宣言部
begin -- 実行部
exception -- 例外処理
end

• 実行部のbegin~end;が最低限あればよい

begin
   null;
end;

• 実行部にネストができる
  ネストの上限は、２５５個らしい。

declare

begin
  declare

  begin

  end;
end;

ストアド・サブプログラム

メモ

NOCOPYオプション

プロシージャで、OUT/IN OUTモードのパラメータにNOCOPYオプションを指定すると、通常は引数がメモリにコピーされて使用されるのを、アドレス参照にすることができるらしい。
大量のデータをやり取りする場合は、便利かも。

--呼び側
PROCEDURE xxx(p OUT NOCOPY tab);

--呼ばれる側
PROCEDURE xxx(p OUT NOCOPY tab) IS