Oracle PL/SQL メモ

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

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

```
set serveroutput [on | off] 
```

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

#コメントのつけ方

- 単一行コメント：-- <br>

```
-- 単一行コメント
```

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

```
/*
    複数行コメント１
    複数行コメント２　
*/
```
# 変数宣言

```
変数名 データ型 [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文<br>
検索データが１行の時に使う。
+ カーソル文<br>
検索データが複数行の時に使う。

## 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 |

+ 問合せ結果は必ず１行にする。<br>
複数行：TOO_MANY_ROWS例外(ORA-01422)が発生する<br>
０件：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データ型](https://docs.oracle.com/cd/E60665_01/db121/LNPLS/datatypes.htm#i10726)

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

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

##### 使用方法
結合配列の要素を参照するには、索引番号を使う。
要素に値を代入するには、代入演算子(:=)や、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;
```

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

```
declare

begin
  declare

  begin

  end;
end;

```


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

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

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

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