0 はじめに

・PL/SQLに詳しくないので、一旦、以下の本を読みながら記事を更新していく
　プロとしてのOracle PL/SQL入門

1 はじめてのPL/SQL

1-1 PL/SQLとは

・PL/SQLは、Oracle独自のプログラミング言語
・SQL単体では行うことのできない複雑な処理（手続き型の処理）を実行できる

1-2 PL/SQLの簡単な機能の紹介

・下記みたいな感じで動かす
※set serveroutput onをsqlPlus起動時に実行しないと、DBMS_OUTPUT.PUT_LINEの実行結果が表示されないので注意する

SQL*Plusログイン時のコマンドライン

SQL> set serveroutput on
SQL>
SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE('OK');
  3  END;
  4  /
OK

PL/SQLプロシージャが正常に完了しました。

SQL>

・エディターも起動できる

SQL*Plusログイン時のコマンドライン

--メモ帳が開く。保存する、「sakura.sql」という名前で保存される
--保存先は、「sqlplus」コマンド実行時のカレントパスに保存される
SQL> edit sakura　

SQL>

・ファイルの実行もできる
１、sample1ファイルを作成

SQL*Plusログイン時のコマンドライン

SQL>edit sample1

２、sample1ファイルを編集し、保存

set serveroutput on
BEGIN
    DBMS_OUTPUT.PUT_LINE('OK');
END;
/

３、sample1ファイルを実行する(ファイル名の前に＠をつける)

SQL*Plusログイン時のコマンドライン

SQL> @sample1
OK

PL/SQLプロシージャが正常に完了しました。

SQL>

・デフォルトのエディターを変更する
１、デフォルトのエディターを確認する

SQL*Plusログイン時のコマンドライン

SQL> define _EDITOR
DEFINE _EDITOR         = "Notepad" (CHAR)
SQL>

２、デフォルトのエディタ(Notepad)からsakuraエディタに変更する

SQL*Plusログイン時のコマンドライン

SQL> DEFINE _EDITOR         = "C:\Program Files (x86)\sakura\sakura.exe"

SQL>

３、edit sample1を実行し、さくらエディターで開く

SQL*Plusログイン時のコマンドライン

SQL> edit sample1

SQL>

・ファイル実行時に、ソースコードの内容も表示する（実行前にset echo onを使う）
以下はソースコード

SQL*Plusログイン時のコマンドライン

SQL> set echo on
SQL>
SQL> @sample1
SQL> set serveroutput on
SQL> BEGIN
  2      DBMS_OUTPUT.PUT_LINE('OK');
  3  END;
  4  /
OK

PL/SQLプロシージャが正常に完了しました。

SQL>

・ソースコードのコメント行の書き方（1行と複数）

--1行のコメント
set serveroutput on
/*
複数行のコメントの書き方
*/
BEGIN
    DBMS_OUTPUT.PUT_LINE('OK');
END;
/

・以降の記事を読む前に、SQL*Plusで以下のSQLを流す

demopl.sql

DROP TABLE EMP;
DROP TABLE DEPT;
drop table salgrade;
drop table audit_table;
drop sequence dept_seq;
drop sequence audit_seq;


CREATE TABLE DEPT (
 DEPTNO              NUMBER(2) NOT NULL,
 DNAME               varchar2(14),
 LOC                 varchar2(13));

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

CREATE TABLE EMP (
 EMPNO               NUMBER(4) not null,
 ENAME               varchar2(10),
 JOB                 varchar2(9),
 MGR                 NUMBER(4),
 HIREDATE            DATE,
 SAL                 NUMBER(7,2),
 COMM                NUMBER(7,2),
 DEPTNO              NUMBER(2) );

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,TO_DATE('17-12-1980','DD-MM-YY'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,TO_DATE('20-02-1981','DD-MM-YY'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('22-02-1981','DD-MM-YY'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,TO_DATE('2-04-1981','DD-MM-YY'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('28-09-1981','DD-MM-YY'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('1-05-1981','DD-MM-YY'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('9-06-1981','DD-MM-YY'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('09-12-1982','DD-MM-YY'),3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,TO_DATE('17-11-1981','DD-MM-YY'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('8-09-1981','DD-MM-YY'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('12-01-1983','DD-MM-YY'),1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,TO_DATE('3-12-1981','DD-MM-YY'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,TO_DATE('3-12-1981','DD-MM-YY'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,TO_DATE('23-01-1982','DD-MM-YY'),1300,NULL,10);

CREATE TABLE SALGRADE (
 GRADE               NUMBER,
 LOSAL               NUMBER,
 HISAL               NUMBER);

INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

create table audit_table
(tablename varchar2(30),
 action    varchar2(10),
 mod_val   varchar2(20));

create sequence audit_seq;

create sequence dept_seq
increment by 10
start with 50;

上記のファイルを実行結果

SQL*Plusログイン時のコマンドライン

SQL> @demopl.sql

SQL> DROP TABLE EMP;
DROP TABLE EMP
           *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。


SQL> DROP TABLE DEPT;
DROP TABLE DEPT
           *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。


SQL> drop table salgrade;
drop table salgrade
           *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。


SQL> drop table audit_table;
drop table audit_table
           *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。


SQL> drop sequence dept_seq;
drop sequence dept_seq
              *
行1でエラーが発生しました。:
ORA-02289: 順序が存在しません。


SQL> drop sequence audit_seq;
drop sequence audit_seq
              *
行1でエラーが発生しました。:
ORA-02289: 順序が存在しません。


SQL>
SQL>
SQL> CREATE TABLE DEPT (
  2   DEPTNO              NUMBER(2) NOT NULL,
  3   DNAME               varchar2(14),
  4   LOC                 varchar2(13));

表が作成されました。

SQL>
SQL> INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');

1行が作成されました。

SQL> INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');

1行が作成されました。

SQL> INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');

1行が作成されました。

SQL> INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

1行が作成されました。

SQL>
SQL> CREATE TABLE EMP (
  2   EMPNO               NUMBER(4) not null,
  3   ENAME               varchar2(10),
  4   JOB                 varchar2(9),
  5   MGR                 NUMBER(4),
  6   HIREDATE            DATE,
  7   SAL                 NUMBER(7,2),
  8   COMM                NUMBER(7,2),
  9   DEPTNO              NUMBER(2) );

表が作成されました。

SQL>
SQL> INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,TO_DATE('17-12-1980','DD-MM-YY'),800,NULL,20);

1行が作成されました。

SQL> INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,TO_DATE('20-02-1981','DD-MM-YY'),1600,300,30);

1行が作成されました。

SQL> INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('22-02-1981','DD-MM-YY'),1250,500,30);

1行が作成されました。

SQL> INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,TO_DATE('2-04-1981','DD-MM-YY'),2975,NULL,20);

1行が作成されました。

SQL> INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('28-09-1981','DD-MM-YY'),1250,1400,30);

1行が作成されました。

SQL> INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('1-05-1981','DD-MM-YY'),2850,NULL,30);

1行が作成されました。

SQL> INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('9-06-1981','DD-MM-YY'),2450,NULL,10);

1行が作成されました。

SQL> INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('09-12-1982','DD-MM-YY'),3000,NULL,20);

1行が作成されました。

SQL> INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,TO_DATE('17-11-1981','DD-MM-YY'),5000,NULL,10);

1行が作成されました。

SQL> INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('8-09-1981','DD-MM-YY'),1500,0,30);

1行が作成されました。

SQL> INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('12-01-1983','DD-MM-YY'),1100,NULL,20);

1行が作成されました。

SQL> INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,TO_DATE('3-12-1981','DD-MM-YY'),950,NULL,30);

1行が作成されました。

SQL> INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,TO_DATE('3-12-1981','DD-MM-YY'),3000,NULL,20);

1行が作成されました。

SQL> INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,TO_DATE('23-01-1982','DD-MM-YY'),1300,NULL,10);

1行が作成されました。

SQL>
SQL> CREATE TABLE SALGRADE (
  2   GRADE               NUMBER,
  3   LOSAL               NUMBER,
  4   HISAL               NUMBER);

表が作成されました。

SQL>
SQL> INSERT INTO SALGRADE VALUES (1,700,1200);

1行が作成されました。

SQL> INSERT INTO SALGRADE VALUES (2,1201,1400);

1行が作成されました。

SQL> INSERT INTO SALGRADE VALUES (3,1401,2000);

1行が作成されました。

SQL> INSERT INTO SALGRADE VALUES (4,2001,3000);

1行が作成されました。

SQL> INSERT INTO SALGRADE VALUES (5,3001,9999);

1行が作成されました。

SQL>
SQL> create table audit_table
  2  (tablename varchar2(30),
  3   action    varchar2(10),
  4   mod_val   varchar2(20));

表が作成されました。

SQL>
SQL> create sequence audit_seq;

順序が作成されました。

SQL>
SQL> create sequence dept_seq
  2  increment by 10
  3  start with 50;

順序が作成されました。

SQL>

2 PL/SQLのブロック概要

3 変数と定数

・変数の代入

SQL*Plusログイン時のコマンドライン

SQL> DECLARE
  2    var NUMBER;
  3  BEGIN
  4    var :=10;
  5    DBMS_OUTPUT.PUT_LINE(var);
  6    var := var + 10;
  7    DBMS_OUTPUT.PUT_LINE(var);
  8  END;
  9  /
10
20

PL/SQLプロシージャが正常に完了しました。

SQL>

・変数の定義と代入

SQL*Plusログイン時のコマンドライン

SQL> DECLARE
  2    var NUMBER(5);
  3    var2 VARCHAR2(10) DEFAULT 'SCOTT';
  4  BEGIN
  5    var :=10;
  6    DBMS_OUTPUT.PUT_LINE(var);
  7    DBMS_OUTPUT.PUT_LINE(var2);
  8  END;
  9  /
10
SCOTT

PL/SQLプロシージャが正常に完了しました。

SQL>

・スカラー型

・コンポジット型

・参照型

・%TYPE、%ROWTYPEについて

１、%TYPE属性を使用したデータの定義

SQL*Plusログイン時のコマンドライン

SQL> DECLARE
  2    var dept.deptno%TYPE;
  3  BEGIN
  4    SELECT deptno INTO var FROM dept
  5      WHERE loc = 'NEW YORK';
  6      DBMS_OUTPUT.PUT_LINE(var);
  7  END;
  8  /
10

PL/SQLプロシージャが正常に完了しました。

SQL>

２、%TYPE、%ROWTYPEの利点について
　・列の定義が変更されても、ソースコードの修正が必要ない
　・列の正確な定義がわからなくても変数を定義できる
　・%TYPEは特定の表の列に定義されているデータ型を参照します
　・%ROWTYPEは表（またはビュー）の行構造を参照します

３、%ROWTYPE属性の使用例

SQL*Plusログイン時のコマンドライン

SQL> DECLARE
  2    d_row dept%ROWTYPE;
  3  BEGIN
  4    SELECT deptno,dname,loc INTO d_row FROM dept
  5      WHERE deptno = 10;
  6      DBMS_OUTPUT.PUT_LINE(d_row.dname);
  7  END;
  8  /
ACCOUNTING

PL/SQLプロシージャが正常に完了しました。

SQL>

・定数について（CONSTANTを利用）

SQL*Plusログイン時のコマンドライン

SQL> DECLARE
  2    const_num CONSTANT NUMBER(8) := 5;
  3  BEGIN
  4
  5      DBMS_OUTPUT.PUT_LINE(const_num);
  6  END;
  7  /
5

PL/SQLプロシージャが正常に完了しました。

SQL>

4 制御構造について

・IF文の実行例

SQL*Plusログイン時のコマンドライン

SQL> DECLARE
  2    var NUMBER := 10;
  3  BEGIN
  4    IF var = 10 THEN
  5      DBMS_OUTPUT.PUT_LINE('値は10');
  6    ELSE
  7      DBMS_OUTPUT.PUT_LINE('値は10以外');
  8    END IF;
  9  END;
 10  /
値は10

PL/SQLプロシージャが正常に完了しました。

SQL>

・IF-THENの実行例

SQL*Plusログイン時のコマンドライン

SQL> DECLARE
  2    var NUMBER := 20;
  3  BEGIN
  4    IF var = 10 THEN
  5      DBMS_OUTPUT.PUT_LINE('値は10');
  6    END IF;
  7  END;
  8  /

PL/SQLプロシージャが正常に完了しました。

SQL>

・IF-THEN-ELSEIF文の実行例

SQL*Plusログイン時のコマンドライン

SQL> DECLARE
  2    var NUMBER := 15;
  3  BEGIN
  4    IF var < 10 THEN
  5      DBMS_OUTPUT.PUT_LINE('値は10より小さい');
  6    ELSIF var < 20 THEN
  7      DBMS_OUTPUT.PUT_LINE('値は20より小さい');
  8    ELSIF var < 30 THEN
  9      DBMS_OUTPUT.PUT_LINE('値は30より小さい');
 10    ELSE
 11      DBMS_OUTPUT.PUT_LINE('それ以外');
 12    END IF;
 13  END;
 14  /
値は20より小さい

PL/SQLプロシージャが正常に完了しました。

SQL>

・CASE文の実行例

SQL*Plusログイン時のコマンドライン

SQL> DECLARE
  2    var NUMBER := 20;
  3  BEGIN
  4    CASE var
  5      WHEN 10 THEN
  6        DBMS_OUTPUT.PUT_LINE('値は10');
  7      WHEN 20 THEN
  8        DBMS_OUTPUT.PUT_LINE('値は20');
  9      WHEN 30 THEN
 10        DBMS_OUTPUT.PUT_LINE('値は30');
 11      ELSE
 12        DBMS_OUTPUT.PUT_LINE('値はそれ以外');
 13    END CASE;
 14
 15  END;
 16  /
値は20

PL/SQLプロシージャが正常に完了しました。

SQL>

・検索CASE文の実行例

SQL*Plusログイン時のコマンドライン

SQL> DECLARE
  2    var NUMBER := 15;
  3  BEGIN
  4    CASE
  5      WHEN var < 10 THEN
  6        DBMS_OUTPUT.PUT_LINE('値は10より小さい');
  7      WHEN var < 20 THEN
  8        DBMS_OUTPUT.PUT_LINE('値は20より小さい');
  9      WHEN var < 30 THEN
 10        DBMS_OUTPUT.PUT_LINE('値は30より小さい');
 11      ELSE
 12        DBMS_OUTPUT.PUT_LINE('値はそれ以外');
 13    END CASE;
 14
 15  END;
 16  /
値は20より小さい

PL/SQLプロシージャが正常に完了しました。

SQL>

・AND演算子の実行例

SQL*Plusログイン時のコマンドライン

SQL> DECLARE
  2    var1 NUMBER :=3;
  3    var2 NUMBER :=2;
  4  BEGIN
  5    IF var1 = 1 AND var2 = 2 THEN
  6      DBMS_OUTPUT.PUT_LINE('TRUE');
  7    ELSE
  8      DBMS_OUTPUT.PUT_LINE('FALSE');
  9    END IF;
 10  END;
 11  /
FALSE

PL/SQLプロシージャが正常に完了しました。

SQL>

・LOOP文の実行例

SQL*Plusログイン時のコマンドライン

SQL> BEGIN
  2    FOR r IN 1..3 LOOP
  3      DBMS_OUTPUT.PUT_LINE('OK');
  4    END LOOP;
  5  END;
  6  /
OK
OK
OK

PL/SQLプロシージャが正常に完了しました。

SQL>

・無限LOOPの書き方（無限ループなので、SQL*Plusで実行はしない）

BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('OK');
  END LOOP;
END;
/

・EXIT WHEN文の実行例
会う条件を満たしたときにLOOPを終了させたい場合

SQL*Plusログイン時のコマンドライン

SQL> DECLARE
  2    c_count NUMBER := 0;
  3  BEGIN
  4    LOOP
  5      EXIT WHEN c_count = 3;
  6        c_count := c_count + 1;
  7        DBMS_OUTPUT.PUT_LINE('OK');
  8    END LOOP;
  9  END;
 10  /
OK
OK
OK

PL/SQLプロシージャが正常に完了しました。

SQL>

・FOR－LOOP文の実行例

SQL*Plusログイン時のコマンドライン

SQL> BEGIN
  2    FOR r IN 1..3 LOOP
  3
  4      DBMS_OUTPUT.PUT_LINE('OK');
  5    END LOOP;
  6  END;
  7  /
OK
OK
OK

PL/SQLプロシージャが正常に完了しました。

SQL>

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

現在の進捗 p45まで実施

・の実行例

SQL*Plusログイン時のコマンドライン