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」という感じ。
プロシージャの中では、他の関数(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行が選択されました。
ちなみに、標準で用意されている関数のソースも見ることが出来る。[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]
引数定義 | 備考 |
---|---|
変数名 型 | 普通の定義方法。 |
変数名 {IN|OUT|INOUT} 型 | その引数の使われ方を明示する。 INが普通に入力用、OUTは呼び出し元の変数に値を返す。 |
変数名 型 := 値 変数名 型 default 値 |
引数の省略時値(既定値)を指定する。 |
省略時値つきの引数は、呼び出すときに省略することが出来る。
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を実行することが出来る。
普通に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プロシージャが正常に完了しました。
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のエラーになる。
SQL> begin
2 EXECUTE IMMEDIATE 'TRUNCATE TABLE test';
3 end;
4 /
PL/SQLプロシージャが正常に完了しました。
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 6 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
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では、ロールによって付与されていたアクセス権限は無効になるんだそうだ。
特にシノニムでテーブルにアクセスしていた場合は、一見すると自分のテーブルと区別がつかないだけに、他者のオブジェクトであることを忘れて このエラーに悩みがち。→ロールによるアクセス権限の確認方法
解決策はいくつかある。