Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@nanananako

Oracle PL/SQL メモ

Last updated at Posted at 2018-05-23

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

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
2
9
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?