LoginSignup
0
0

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 3 years have passed since last update.

@rinChome(rin chome)

勉強メモ27_PL/SQLについて（つどつど更新）

Posted at

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ログイン時のコマンドライン
0
0
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
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?