PL/SQLメモ

OracleのPL/SQLについて。

概要

PL/SQLは、SQLをプログラムのように書いて実行できる、Oracleの独自言語。

一時的に実行するだけでなく、関数やプロシージャ（といったサブルーチン）の形にして保存して何度でも実行できる。
そうして保存したサブルーチンの事をRDBの世界では(?)ストアドプロシージャと呼ぶ。
「ストアド」とは「stored」の事なので、日本語としては「ストアード」と言うべきだと思うのだが「ストアド」と書かれる（少なくとも切り方は「スト・アド」ではなく「ストア・ド」）。メモリーをメモリと書く人も多いので、その流れか。

（一時的な）実行方法

SQL*Plusから入力して実行できる。

SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE('test');
  3  END;
  4  /
test

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

DBMS_OUTPUT.PUT_LINE()は文字列を出力する関数だが、SQL*Plus上に表示する為には、事前に「set serveroutput on」を実行しておく必要がある。
BEGIN〜ENDがPL/SQLの文を書くもの。SQLと同じく、大文字でも小文字でもよい。
最後に「/」を書いてEnterを押すと実行される。

変数を使いたい場合は、DECLAREを使ってローカル変数を定義する。
変数への 代入は「:=」を使う。

SQL> DECLARE
  2    v1 NUMBER; v2 NUMBER;
  3  BEGIN
  4    v1 := 123; v2 := 456;
  5    DBMS_OUTPUT.PUT_LINE(v1+v2);
  6  END;
  7  /
579

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

定数は「c CONSTATNT NUMBER := 1;」という形で定義できる。

一行だけの単純なPL/SQLを実行したい場合は、BEGIN〜ENDの代わりにEXECUTE（短縮形はEXEC）が使える。
これは、実際にはBEGIN〜ENDに囲まれて実行される（わざとエラーを起こしてみれば、BEGIN〜ENDが付いているのが分かる）。
ただし、BEGIN〜ENDと異なり、SQL*Plusのバッファには入らない。すなわちBEGIN〜ENDはLで入力内容を見たりRで再実行したり出来るが、EXECは出来ない。

SQL> EXEC DBMS_OUTPUT.PUT_LINE('test');
test

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

関数やプロシージャをただ呼び出すだけの場合は、CALL文が使える。[2007-12-28]
（EXECはSQL*Plusのコマンド（だからバッファに入らない）だがCALLはANSIで定義されているものらしい）

SQL> CALL DBMS_OUTPUT.PUT_LINE('test');
test

コールが完了しました。

ストアドプロシージャ

PL/SQLの文を関数やプロシージャとしてDBに保存（ストア）し、他のPL/SQLやSQL文から呼び出すことが出来る。
（小文字の名前で作っても、実際には大文字で保存される）

SQL> CREATE OR REPLACE PROCEDURE test_p(n char)
  2  AS
  3    v CHAR(6);
  4  BEGIN
  5    v := n || n;
  6    DBMS_OUTPUT.PUT_LINE(v);
  7  END;
  8  /

プロシージャが作成されました。

SQL> exec test_p('ABC');
ABCABC

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

SQL> call test_p('ABC');
ABCABC

コールが完了しました。

SQL> CREATE OR REPLACE FUNCTION test_f(n char) RETURN CHAR
  2  IS
  3    v CHAR(6);
  4  BEGIN
  5    v := n || n;
  6    RETURN v;
  7  END;
  8  /

ファンクションが作成されました。

SQL> exec dbms_output.put_line(test_f('ABC'));
ABCABC

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

SQL> call dbms_output.put_line(test_f('ABC'));
ABCABC

コールが完了しました。

SQL> select test_f('ABC') from dual;

TEST_F('ABC')
----------------------------------------------------------
ABCABC

SQL> var v1 char(6)
SQL> call test_f('ABC') into :v1;

コールが完了しました。

SQL> print v1

V1
--------------------------------
ABCABC

引数の無いプロシージャを作る場合は、括弧自体を書かない。「CREATE FUNCTION func RETURN NUMBER」という感じ。

2行目は、「AS」でも「IS」でも構わないようだ。

プロシージャの中では、他の関数（to_char()・to_date()等や自作の関数）が普通に呼べる。

CREATEによってエラーが出た場合、「show err」もしくは「select * from user_errors;」によってエラーの内容が分かる。

SQL> show err
エラーはありません。

ソース表示

現在どんな関数が作られているかはuser_proceduresビューを使って調べることが出来る。
（テーブル名はproceduresだが、関数もプロシージャもどちらもここに入っている）

SQL> select object_name from user_procedures;

OBJECT_NAME
------------------------------
TEST_F
TEST_P

FUNCTION・PROCEDUREのソースはuser_sourceビューを使って確認することが出来る。
（ユーザー定義型・Javaのソースも見られる[2005-05-15]）

SQL> select text from user_source
  2  where name='TEST_F'
  3  order by line;

TEXT
--------------------------------------------------------------------------------
FUNCTION test_f(n char) RETURN CHAR
IS
  v CHAR(6);
BEGIN
  v := n || n;
  RETURN v;
END;

7行が選択されました。

→DBMS_METADATA.GET_DDL

ちなみに、標準で用意されている関数のソースも見ることが出来る。[2007-09-22]

select * from dba_source
where name='STANDARD' and
text like '%function%SYSDATE%'
order by line
;

select text from dba_source
where name='STANDARD' and
type='PACKAGE' and
line between 787 and 790
order by line
;

ほとんどpragmaだったりするようだけど＾＾； どちらかと言えば、どんな関数があるのか調べるのに使えるかな。

関数名やプロシージャ名をdescに指定すると、引数（と戻り値の型）の情報を見ることが出来る。[2008-01-27]

SQL> desc test_f
FUNCTION test_f RETURNS CHAR
 引数名                         タイプ                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 N                              CHAR                    IN

user_argumentsビューで確認することも出来る が、descの方が分かり易い。

引数の定義・指定

関数やプロシージャの引数の定義方法。[2007-12-28]

省略時値つきの引数は、呼び出すときに省略することが出来る。

SQL> create function test_default(a1 char, a2 char := 'ZZZ') 〜;

SQL> select test_default('ABC', 'DEF') from dual;
SQL> select test_default('ABC') from dual;

関数やプロシージャを呼び出す際には、（基本的に）引数が定義された順番通りに値を指定する。（位置表記法：positional notation）
しかしPL/SQL内から呼び出す場合には引数名を指定して（順不同で）値を指定することが出来る。（名前表記法：named notation）

SQL> create procedure test_noseq(a1 char, a2 char, a3 char) 〜;

SQL> exec test_noseq('ABC', 'FOO', 'ZZZ');             ←位置表記法
SQL> exec test_noseq(a3=>'ZZZ', a1=>'ABC', a2=>'FOO'); ←名前表記法
SQL> exec test_noseq('ABC', a3=>'ZZZ', a2=>'FOO');     ←混合表記法

この「=>」による指定方法はselect文で関数を呼び出すときには使えないが、Oracle11gからは使えるらしい。

SQLの実行

プロシージャ内に直接SQL文を書くことによってSQLを実行することが出来る。

SELECT

普通にSELECT文を書いても、出力先が無いのでエラーになる。（PLS-00428「INTO句はこのSELECT文に入ります」…変な日本語だな(苦笑)）

結果が1行になるSELECTでは、INTOを使って変数に代入するのが便利。

SQL> declare
  2    cnt number;
  3    sal number;
  4  begin
  5    select count(empno), max(sal)
  6    into   cnt,          sal
  7    from emp;
  8  
  9    dbms_output.put_line('count=' || cnt || ', max_salary=' || sal);
 10  end;
 11  /
count=14, max_salary=5000

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

INSERT・UPDATE・DELETE

SQL> begin
  2    update emp set sal = sal + 10;
  3    dbms_output.put_line('更新件数=' || SQL%ROWCOUNT);
  4    commit;
  5  end;
  6  /
更新件数=14

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

“更新を伴うSQLを含んでいる”関数をselect文から呼び出すとORA-14551のエラーになる。

→PL/SQLのinsert

SQL文字列

SQL> begin
  2    EXECUTE IMMEDIATE 'TRUNCATE TABLE test';
  3  end;
  4  /

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

→EXECUTE IMMEDIATE

例外の捕捉

ENDの前に、捕捉したい例外とその処理を書く。

	begin
		〜
	exception when others then
		dbms_output.put_line('error' || SQLCODE || ':' || SQLERRM);
		rollback;
		return -1;
	end;

othersは、全ての例外を捕捉する指定。
SQLCODEはエラーコード、SQLERRMはエラーメッセージ。

→独自例外の定義方法と発生方法

定義者権限と実行者権限

関数やプロシージャの作成時に「AUTHID」というオプションを指定できる。
これは、そのルーチンを実行する際に「実行するのがどのユーザーであるか」を指定するもの。
値は2種類あり、定義者（DEFINER）と実行者（CURRENT_USER）。省略した場合のデフォルトは定義者権限。

例えばscottが作った関数を別ユーザー（hishidama）が実行する場合、
定義者権限で作った場合： その関数内のテーブルはscottのアクセス権限で読み書きされる。
実行者権限で作った場合： その関数内のテーブルはhishidamaのアクセス権限で読み書きされる。
つまり、scott.deptテーブルを参照する関数でhishidamaにdeptのアクセス権限が無い場合、実行者権限ではエラーになるが、定義者権限なら問題なく実行できる。

scottユーザーにて：

SQL> create or replace function dept_count_c return number
  2  AUTHID CURRENT_USER
  3  is
  4    ct number;
  5  begin
      select count(deptno) into ct from dept;
  7    return ct;
  8  end;
  9  /

ファンクションが作成されました。

SQL> create or replace function dept_count_d return number
  2  AUTHID DEFINER
  3  is
  4    ct number;
  5  begin
  6    select count(deptno) into ct from dept;
  7    return ct;
  8  end;
  9  /

ファンクションが作成されました。

hishidamaユーザー（scottの関数の実行権限は与えておく）にて：

SQL> select scott.dept_count_c() from dual;	…実行者権限の関数
select scott.dept_count_c() from dual
       *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。
ORA-06512: "SCOTT.DEPT_COUNT_C", 行6		…hishidamaではscottのdeptテーブルにアクセスできないので、エラーになる

SQL> select scott.dept_count_d() from dual;	…定義者権限の関数

SCOTT.DEPT_COUNT_D()
--------------------
                   4

PL/SQL関連エラー

更新系SQLの使用箇所の制限

INSERT・UPDATE・DELETEといった更新系のSQL（≒DML）を実行している関数は、SELECT（＝問合せ）で使うことが出来ない。

SQL> select add_emp_sal(1) from dual;
select add_emp_sal(1) from dual
       *
行1でエラーが発生しました。:
ORA-14551: 問合せの中でDML操作を実行することはできません。

更新系の関数のテストを行うなら、EXECで実行する。

同様に、テーブルを更新している関数は、同一テーブルを更新するDMLから使うことは出来ない。

SQL> update emp set sal=50000/add_emp_sal(1);
update emp set sal=50000/add_emp_sal(1)
                         *
行1でエラーが発生しました。:
ORA-04091: 表SCOTT.EMPは変更しています。トリガー/関数は見ることができません

同様に、DDL（CREATE文）実行やCOMMIT・ROLLBACKを行っている関数をINSERT・UPDATE・DELETEで使うことは出来ない。

SQL> update test set val=add_emp_sal(1);
update test set val=add_emp_sal(1)
                    *
行1でエラーが発生しました。:
ORA-14552:
DDL、コミットまたはロールバックは問合せまたはDML文中では実行できません。

※『DML』は本来データ操作系のSQL（SELECT・INSERT・UPDATE・DELETE）の事のはずなのだが、これらのエラーではSELECTを問合せ（query）、他をDMLと表現しているらしい。

関数内の権限

SQL*Plusからは普通に実行できるSQLが、関数の中から実行しようとすると「テーブルまたはビューが見つからない」エラーになることがある。

SQL> show user
ユーザーは"HISHIDAMA"です。

SQL> create or replace function emp_count return number
  2  is
  3    ct number;
  4  begin
  5    select count(empno) into ct from emp;	empはシノニムで作っており、SQL*PlusからこのSELECT文は実行可能
  6    return ct;
  7  end;
  8  /

警告: ファンクションが作成されましたが、コンパイル・エラーがあります。

SQL> show err
FUNCTION EMP_COUNTのエラーです。

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: SQL Statement ignored
5/36     PL/SQL: ORA-00942: 表またはビューが存在しません。

これは、そのテーブル（ビュー）が他のスキーマ（ユーザー）のオブジェクトで、さらにそのオブジェクトへのアクセス権限がロールによって付与されていた場合に起こる。

PL/SQLでは、ロールによって付与されていたアクセス権限は無効になるんだそうだ。
特にシノニムでテーブルにアクセスしていた場合は、一見すると自分のテーブルと区別がつかないだけに、他者のオブジェクトであることを忘れて このエラーに悩みがち。→ロールによるアクセス権限の確認方法

解決策はいくつかある。

• ロールが無効になるだけなので、そのテーブルへの個別のアクセス権限を付与する。
  あるいは全テーブルへのアクセス権限を付与してしまうという荒業も…。
• 関数をそのテーブルの持ち主のユーザーで定義者権限で作り、使いたいユーザーに作った関数の実行権限を与える。
  （関数の作り主を変えないまま AUTHIDをCURRENT_USERに変えても解決にならない）

参考：

• biz.rivius.jpさんのORA-00942、PL/SQLの実行者権限