Oracle PL/SQL

概要 概要 言語要素 文 例外
変数 レコード 表 NULL 値 変数配列
サブプログラム 関数 プロシージャ パッケージ トリガー
機能 演算子 カーソル 例外ハンドラ
SQL 動的 SQL


組込み関数 数値関数 文字関数 日付関数 その他関数
組み込みパッケージ dbms_job dbms_sql dbms_output utl_file
dbms_utilty


デバッグ ツール


外部プロシージャ ヘッダ部 c java エラー対処法
その他 例 エラー対処法 FAQ 参考書

概要

概要

PL/SQL はブロック構造で各ブロックの構造は以下のとおりです。 なお必須のセクションはヘッダ部と実行可能セクションだけです。

部 無名ブロック 名前つきブロック
ヘッダ部DECLARE CREATE OR RELPLACE PROCEDURE 名前 is
宣言部　　宣言セクション 　　宣言セクション
実行部BEGIN
　　実行可能セクション		 BEGIN
　　実行可能セクション
例外処理部EXCEPTION
　　例外処理セクション
END;
/		 EXCEPTION
　　例外処理セクション
END;
/

DECLARE ではじまる PL/SQL ブロックは無名ブロックと呼ばれ、毎回コンパイルされます。
DECLARE の代わりに CREATE OR RELPLACE PROCEDURE プロシージャ名 is とすれば、 ストアードプロシージャにできます。
またCREATE OR RELPLACE FUNCTION 関数名 is とすれば、ストアード関数となります。

ストアードプロシージャとストアード関数をまとめてサブプログラムと呼びます。 いずれもヘッダ部と実行部は必須です。




言語要素

識別子

識別子は、定数、変数、例外、プロシージャ、カーソルなどの PL/SQL オブジェクトの名前です。 識別子には以下の条件があります。

  1. 長さは最大 30 文字
  2. 空白類は含めません（スペース、タブ、改行など）
  3. 先頭は英字ではじまらなければなりません
  4. $_# などを含めることができる
  5. 大文字、小文字は区別されません
  6. 予約語は使えません

    PL/SQL の予約語は以下のとおりです。 

    ABORT ACCEPT ACCESS ADD ALL ALTER AND ANY ARRAY ARRAYLEN AS ASC ASSERT ASSIGN AT AUDIT AUTHORIZATION AVG
BASE_TAVBLE BEGIN BETWEEN BINARY_INTEGER BODY BOOLEAN BY
CASE CHAR CHAR_BASE CHECK CLOASE CLUSTER CLUSTERSCOLAUTH COLUMN COLMNS COMMENT COMMIT COMPRESS CONNECT CONSTANT 
CRASH CREATE CURRENT CURRVAL CURSOR
DATABASE DATA_BASE DATE DBA DEBUGOFF DEBUGON DECIMALDECLARE DEFAULT DEFINITION DELAY DELETE DELTA DESC DIFGITS 
DISPOSE DISTINCT DO DROP
ELSE ELSIF END ENTRY EXCEPTION EXCEPTION_INIT EXISTS EXIT 
FALSE FETCH FILE FLOAT FOR FORM FROM FUNCTION
GENERIC GOTO GRANT GROUP HAVING
IDENTIFIED IF IMMEDIATE IN INCREMENT INDEX INDEXES INDICATORINITIAL INSERT INETERFACE INTEGER INTERSECT INTO IS 
LEVEL LIKE LIMITED LOCK LONG LOOP MAX MAXEXTENTS MIN MINUS MLSLABEL MOD MODE MODIFY
NATURAL NATURALN NEW NEXTVAL NOAUDIT NOCOMPRESS NOT NOWAIT NULL NUMBER NUMBER_BASE
OF OFFLINE ON ONLINE OPEN OPTION OR ORDER OTHERS OUT
PACKAGE PARTITION PCTFREE PLS_INTEGER POSITIVE PRAGMA PRIOR PRIVATE PRIVILEGES PROCEDURE PUBLIC
RAW RAISE RANGE REAL RECORD REF RELEASE REMR RENAME RESOURCE RETURN REVERSE REVOKE ROLLBACK ROW ROWID ROWLABEL 
ROWNUMROWS ROWTYPE RUN
SAVEPOINT SCHEMA SELECT SEPARATE SET SHARE SIZE SMALLINT SPACE SQL SQLCODE SQLERRM START STATEMENT STDDEV SUBTYPE 
SUCCESSFUL SUM SYNONYM SYSDATE
TABAUTH TABLE TABLES TASK TERMINATE THEN TO TRIGGER TRUE TYPE
UID UNION NUNIQUE UPDATE USE USER 
VALIDATE VALUES VARCHAR VARCHAR2 VARIANCE VIEW VIEWS WHEN WHENEVER WHERE WHILE WITH WORK WRITE XOR




文

実行文の書き方はつぎのとおりです。

文種類 書式例
コメント -- コメント
rem コメント
/* ....... */ 		-- this is a comment
rem コメント
/* コメント */
選択文 if (式) then
文;
end if; 
declare
  i        PLS_INTEGER := 1;
  MAX_LOOP PLS_INTEGER := 99;
begin
   if i >= MAX_LOOP then
      dbms_output.put_line('False !' || i);
   else
      dbms_output.put_line('True !' || i);
   end if;
end;
if (式) then　文;
else
　文;
end if;
if (式) then　文;
elsif (式) then
　文;
else
　文;
end if; 
if deleting then
   dbms_output.put_line('delete ' || :old.ename );
elsif inserting then
   dbms_output.put_line('inert ' || :old.ename );
else
   dbms_output.put_line('update fr:' || 
       :old.ename || ' to:' || :new.ename);
end if;
case 選択肢
when 値 then
　文;
：
else 　文;
end case;		 
create or replace procedure getdept
is
begin
declare
   type cur_type is ref cursor return emp%ROWTYPE;
   dept_cur cur_type;
   dept_rec emp%ROWTYPE;
   i         PLS_INTEGER := 1;
   empno     PLS_INTEGER ;
 begin
   open dept_cur for select * from emp;

   loop
      fetch dept_cur into dept_rec;
      if  dept_cur%NOTFOUND then
         exit;
      end if;

      case dept_rec.deptno
      when  10 then
        dbms_output.put_line(dept_rec.empno || ' : ' ||
           dept_rec.ename || ' : ' || dept_rec.deptno);
      else
        dbms_output.put_line('Other dept ' || 
           dept_rec.deptno);
      end case;
   end loop;
   close dept_cur;
 end;
end getdept;
/


実行結果
SQL> exec getdept
Other dept 20
Other dept 30
Other dept 30
Other dept 20
Other dept 30
Other dept 30
7782 : CLARK : 10
Other dept 20
7839 : KING : 10
Other dept 30
Other dept 20
Other dept 30
Other dept 20
7934 : MILLER : 10
1 : taka : 10

PL/SQLプロシージャが正常に完了しました。
繰り返し文 loop
　文;
end loop;		 
loop
   if no < MAX_LOOP  then
      prt_no(no); 
   else
      exit;
   end if;
end loop;
数値 for ループ変数 in [reverse] MIN .. MAX
loop
　文;
end loop; 
for i in 1 .. MAX_LOOP
loop
  dbms_output.put_line('i=' || i);
end_loop;
while 条件
loop
　文;
end loop;		 
while (i>MAX_LOOP)
loop
  i := i + 1;
  dbms_output.put_line('i=' || i);
end loop;
for カーソル変数 in カーソル名
loop
　文
end loop; 
declare 
  cursor sales_cur is 
    select ename,sal,empno from emp where job like 'SALES%';
  sales_rec  sales_cur%ROWTYPE;
  begin
    for sales_rec in sales_cur
    loop
      sales_rec.sal := sales_rec.sal * 1.2;
    end loop;
  end;
制御移行文 exit [when 論理条件]; LOOP を終了
return 式; 式値が返される
null; なにも処理しない。例外内で書くことが多い
例外ハンドラ 名前つきシステム例外 invalid_cursor,value_error,no_data_found
名前つきプログラマ例外
名前なしシステム例外
名前なしプログラマ例外 
if no_faults > MAX_FAULTS
then
    raise too_many_faults;
end if;

exception
   when too_many_faults
   then 
      dbms_output.put_line('Too manay errors !!');
      exit;
end;
raise [例外名]; 
例外を起こす  
create or replace function b_name
(name_in IN VARCHAR2,sex_in IN VARCHAR2) return VARCHAR2 is
begin
declare
   name_out VARCHAR2(100) := '';
begin
   if sex_in = 'M' then
      name_out := 'Mr. ' || name_in;
   elsif sex_in = 'F' then
      name_out := 'Mrs. ' || name_in;
   else
      raise value_error;
   end if;
   return (name_out);
exception
   when others then
      raise;
end;
end;
/
declare
   name varchar2(100);
begin
   dbms_output.put_line(b_name('Takahashi','M'));
   dbms_output.put_line(b_name('Takahashi','F'));
   dbms_output.put_line(b_name('Takahashi',NULL));
exception
   when others then
      dbms_output.put_line(sqlerrm);
end;
/


実行結果
SQL> @func1

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

Mr. Takahashi
Mrs. Takahashi
ORA-06502: PL/SQL: 数値または値のエラーが発生しました

PL/SQLプロシージャが正常に完了しました。
raise_application_error(エラー番号,エラーメッセージ);
エラー番号:-20999〜-20000 
例外を起こす
create or replace function b_name
(name_in IN VARCHAR2,sex_in IN VARCHAR2) return VARCHAR2 is
begin
declare
   name_out VARCHAR2(100) := '';
begin
   if sex_in = 'M' then
      name_out := 'Mr. ' || name_in;
   elsif sex_in = 'F' then
      name_out := 'Mrs. ' || name_in;
   else
      raise_application_error(-20000,'Invalid parameter');
   end if;
   return (name_out);
exception
   when others then
      raise;
end;
end;
/

declare
   name varchar2(100);
begin
   dbms_output.put_line(b_name('Takahashi','M'));
   dbms_output.put_line(b_name('Takahashi','F'));
   dbms_output.put_line(b_name('Takahashi',NULL));
exception
   when others then
      dbms_output.put_line(sqlerrm);
end;
/


実行結果
SQL> @func11

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

Mr. Takahashi
Mrs. Takahashi
ORA-20000: Invalid parameter

PL/SQLプロシージャが正常に完了しました。
データベース
関連		 selectselect ename from emp where empno = 7499;
insertinsert into emp values (1,'taka','enginer',7222,sysdate,10.0,0,1);
updateupdate emp set sal = sal*1.2 where empno = 7499;
commitcommit;
rollbackrollback;
実行 コンパイルエラーを知るshow errors
出力端末に設定 set serveroutput on;
sqlplus から実行 exec ストアードプロシージャ名;

例外

生成

例外は３つの方法で生成されます。

  1. PL/SQL ランタイムエンジンを使う（明示的に書く必要はありません）
  2. raise 文で明示的に生成する
  3. raise_application_error を呼び出す

例外ハンドラは、PL/SQL ブロックの実行部で発生している例外だけを処理します。 宣言部や例外処理部で生成された例外は、自動的に外部ブロックに渡されます。

有効範囲

PL/SQL ブロックで発生した例外は、例外処理部で処理されなかったり、再発生されたりすると、 外部のブロックに伝播します。 この伝播は、例外が処理されるか、最も外側のブロックまで続けられます。 呼び出し元に到達した場合、例外は未処理となり次のように動作します。
  1. 呼び元のプログラムを停止します。
  2. 任意の未確定トランザクションをロールバックします。

例外は処理された時点で完了し、それ以上は伝播しません。

例外の例

CREATE OR REPLACE PACKAGE xijdop001 IS
        E_PROGRAM_ERR           EXCEPTION;
END xijdop001;
/

begin
   raise xijdop001.E_PROGRAM_ERR;
   dbms_output.put_line('Normal');
exception
   when xijdop001.E_PROGRAM_ERR then
      dbms_output.put_line('E_PROGRAM_ERR !!!');
   when others then
      dbms_output.put_line('Other exception !');
end;
/

上記を except.sql に書いて実行すると以下のようになります。 

SQL> @except

パッケージが作成されました。

E_PROGRAM_ERR !!!

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

oracle の例外

oracle で宣言されている例外は以下のとおりです。

エラー番号 名前つき例外 エラー番号 名前つき例外
ORA-00001DUP_VAL_ON_INDEX ORA-00051TIMEOUT_ON_RESOURCE
ORA-00061TRANSACTION_BACKED_OUT ORA-01001INVALID_CURSOR
ORA-01012NOT_LOGGED_ON ORA-01017LOGIN_DENIED
ORA-01403NO_DATA_FOUND ORA-01410SYS_INVALID_ROWID
ORA-01422TOO_MANY_ROWS ORA-01476ZERO_DIVIDE
ORA-01725USERENV_COMMMITSCN_ERROR ORA-01722INVALID_NUMBER
ORA-06500STORAGE_ERROR ORA-06501PROGRAM_ERROR
ORA-06502VALUE_ERROR ORA-06504ROWTYPE_MISMATCH
ORA-06511CURSOR_ALREADY_OPEN ORA-06530ACCESS_INTO_NULL
ORA-06531COLLECTIOMN_IS_NULL ORA-06532SUBSCRIPT_OUTSIDE_LIMIT
ORA-06533SUBSCRIPT_BEYOND_COUNT ORA-09592CASE_NOT_FOUND
ORA-30625SELF_IS_NULL ORA-29280INVALID_PATH
ORA-29281INVALID_MODE ORA-29282INVALID_FILEHANDLE
ORA-29283INVALID_OPERATION ORA-29284READ_ERROR
ORA-29285WRITE_ERROR ORA-29286INTERNAL_ERROR
ORA-29287INVALID_MAXLINESIZE ORA-29288INVALID_FILENAME
ORA-29289ACCESS_DENIED ORA-29290INVALID_OFFSET
ORA-29291DELETE_DFAILED ORA-29292RENAME_FAILED

変数

変数

変数のデータ型をまとめます。変数は使うまえに宣言しなければなりません。 また変数を宣言するとき、デフォルト値を設定できます。

変数は CONSTANT として宣言しない限り、プログラムの中で値を変更できます。

%TYPE 宣言属性を使って、ある変数のデータ型に PL/SQL の変数や表の列などのデータ構造体を付与できます。 これをアンカーつき宣言といいます。

項目 宣言の形式 例
変数の宣言変数名 データ型 [:= 初期値]; bno BINNARY_INTEGER := 3;
変数名 データ型 DEFAULT 値; bno BINNARY_INTEGER DEFAULT 3;
定数の宣言定数名 CONSTANT データ型 := 値; MAX_LOOP CONSTANT BINNARY_INTEGER :=100;
アンカーつき宣言変数名 データ型%TYPE [:= 初期値]; total_sal NUMBER(20,2);
monthly_sal total_sal%TYPE;
表属性宣言変数名 表名%ROWTYPE ; monthly_sal emp%ROWTYPE;
型のグループ データ型例
数値 BINNARY_INTEGER,PLS_INTEGERbno BINNARY_INTEGER := 3;
FLOATfno FLOAT;
NUMBER[(P,S)]　　　P:精度 S:スケールno NUMBER(10,3);
文字 CHAR(L)　　　　　 L:文字列最大長name CHAR(8) := 'TARO';
VARCHAR2(L)　　　L:文字列最大長vname VARCHAR2(128);
RAW(L)　　　　　 　L:バイト最大長bdata RAW(256);
真理値 BOOLEAN　　　TRUE,FALSE,NULL の３値のみをもつflag BOOLEAN := TRUE;
日時 DATE　　　　　　to_date() で代入、to_char() で表示today DATE;
LOB BFILE,BLOB,CLOB,NCLOBclob CLOB;

レコード

PL/SQL レコードは、フィールドと呼ばれる複数の情報の断片で構成されるデータ構造です。 レコードを使用するには、まずレコードを定義し、その型の変数を宣言します。

レコードには、表に基づくレコード、カーソルに基づくレコード、プログラマ定義の３つがあります。

参照レコード名.フィールド名
作成表に基づくレコードレコード名　表名%ROWTYPE;
カーソルに基づくレコードレコード名　カーソル名%ROWTYPE;
ユーザ定義のレコード 
TYPE 型名 IS RECORD
(  フィールド名　　データ型,
      :
);


例１
DECLARE
  TYPE def_rectype is RECORD (
    first_name varchar2(30),
    sur_name   varchar2(30),
    suffix     varchar2(10));
例２
DECLARE
  TYPE def_emp is RECORD (
    name       emp.e_name%TYPE,
    deptno     emp.deptno%TYPE,
    salary     emp.salary%TYPE);

表

表はc の配列に似ています。 表を使う場合は、まず表の型を定義したあとで、その型をもつ変数を宣言する必要があります。

データベース内の構造をあらわす複合型もデータ構造です。

TYPE の定義TYPE 型名 IS TABLE OF データ型 [NOT NULL] INDEX BY BINARY_INTEGER;
作成
create table lob_tbl (
   c1   LONG;
   c2   NUMBER;
);

表操作用のファンクションとプロシージャ

ファンクション
プロシージャ		説明例
COUNT 要素数を返す emp.COUNT
DELETE 行を削除する emp.DELETE emp.DELETE(4)
EXISTS インデックスが定義されているか？emp.EXISTS
FIRST インデックスの最小値 emp.FIRST
LAST インデックスの最大値 emp.LAST
NEXT 次インデックスを返す emp.NEXT
PRIOR 前インデックスを返す emp.PRIOR
例
t_tbl.sql
declare
   type  a_table is table of varchar2(10) index by binary_integer;
   v_table a_table;
   i             pls_integer ;
   S_LOOP        pls_integer := 1;
   MAX_LOOP      pls_integer := 5;
 begin
   dbms_output.put_line(' -- v_table datas --');
   for i in  S_LOOP .. MAX_LOOP
   loop
      v_table(i) := 'Takano' || i;
      dbms_output.put_line(i || '=' || v_table(i));
   end loop;

   v_table.delete(5,16);
   dbms_output.put_line(' -- v_table datas : after update --');
   for i in  S_LOOP .. MAX_LOOP
   loop
      dbms_output.put_line(i || '=' || v_table(i));
   end loop;
end;
/

実行結果
-- v_table datas --
1=Takano1
2=Takano2
3=Takano3
4=Takano4
5=Takano5
-- v_table datas : after update --
1=Takano1
2=Takano2
3=Takano3
4=Takano4
declare
*
行１でエラーが発生しました。：
ORA-01403: データが見つかりません。




NULL 値

PL/SQL では NULL 値は変数が値をもっていないことを示します。NULL 値は直接比較することはできません。 専用の関数を使います。

関数構文例
変数 IS NULL
変数 IS NOT NULL		 
if up_date IS NULL then
    dbms_output.put_line('更新日が入っていません');
end if;
NVL(p1,p2)　　　p1 が NULL なら p2 を返す
myValue := NVL(yourValue,'DEFAULT');

変数配列

構文例
create type 変数配列名 as varray(上限) of 型名; create type a_varray as varray(10) of number;
表の中に変数配列を作成する 
create table a_table
    (col1       number,
     col2       a_varray);
変数配列に値を入れる 
declare
   MAX_LOOP pls_integer := 10;
   va_array   v_arry;
begin
   for i in 1 .. MAX_LOOP
   loop
      va_varry(i) := i;
   end_loop;
end;

変数配列に使用できるメソッドは次のとおりです。

メソッド名機能例
exists要素が定義されていれば true,そうでなければ false
count 配列要素数を返す。定義したときは０です
limit 仮想配列の指定された上限を返す
first 変数配列のはじめの要素番号を返す。要素が未定義の場合は NULL を返す
last 変数配列の最後の要素番号を返す。要素が未定義の場合は NULL を返す
prior 指定した要素番号より１つ小さい要素番号を返す
next 指定した要素番号より１つ大きい要素番号を返す
delete変数配列の要素を削除する
extend変数配列の終わりに新しいプレースホルダーを作成する
trim 変数配列の終わりから要素を削除する

サブプログラム

概要

サブプログラムには、ストアードプロシージャ、ストアード関数、トリガーがありますが、 定義等は同一形式です。

  1. user_objects ビューには全てのオブジェクトに関する情報があります。
  2. user_source ビューには該当するオブジェクトの元のソースコードがあります。
  3. user_errors ビューにはコンパイルエラーに関する情報があります。

PL/SQL では、任意の宣言部の中で、同じ名前で引数の型や個数が異なる複数のプログラムを定義できます。
これをオーバーロードといいます。ただしなんらかの違いがなければなりません。 次の場合はオーバーロードできません。

  1. 関数の return 句のデータ型だけが異なるもの。
  2. パラメータのデータ型の種別がおなじもの。（char と varchar2、number と integer）
  3. パラメータのモードだけが異なるもの。

関数やプロシージャを宣言するときは、その引数のモードを指定します。

仮引数のモード

モード 説明 使い方
IN 入力 モジュールから参照のみ
OUT 出力 モジュールから値設定のみ
IN OUT 入出力 モジュールから参照、変更ができる

IN/IN OUT パラメータにはデフォルト値を設定できます。

関数やプロシージャの削除

関数やプロシージャはデータディクショナリに格納されます。

  1. user_objects というビューに情報があります。
  2. user_source というビューには、ソースコードがあります。
  3. user_errors というビューには、コンパイルエラー情報があります。

構文 例
drop procedure プロシージャ名;drop procedure t_for;
drop function 関数名; drop function t_for;
drop tigger トリガー名; drop trigger updemp;

関数やプロシージャの参照 

パッケージ名.関数名
パッケージ名.プロシージャ名

サブプログラムのオーバーロード

同じ名前で引数が異なるサブプログラムを複数書くことができます。




関数

関数は戻り値をもつ名前付の PL/SQL ブロックです。

ヘッダ
PL/SQL 
create or replace function 関数名
[(argument {IN|OUT} type )]  return 型;
/ 
create or replace function func1
  (id INTEGER, amount REAL) return INTEGER;
/
ヘッダ
c 
create or replace function 関数名
[(argument {IN|OUT} type )]  return 型
is external
   name     関数名 
   library  ライブラリ名
   parameters (....)
   language c;
/ 
function func1 (vardata In OUT VARCHAR2) return VARCHAR2 
is external
  name "updvar"  library upd_lib
  parameters(vardata STRING,vardata MAXLEN SB4,return STRING)
  language c;
/
本体 
function 関数名
[(argument {IN|OUT} type )]  return 型 is
begin
関数本体
end 関数名;
/
function func1 (id INTEGER, amount REAL) return INTEGER 
is
  work_var REAL;
BEGIN

EXCEPTION
  exception handler
END  func1;
/
例 
tst_func2.sql
declare
   name varchar2(100);
begin
   dbms_output.put_line(b_name('Takahashi','M'));
   dbms_output.put_line(b_name('Takahashi','F'));
exception
   when others then
      dbms_output.put_line(sqlerrm);
end;
/

実行結果
SQL> @tst_func2
Mr. Takahashi
Mrs. Takahashi




プロシージャ

プロシージャは戻り値をもたない名前付の PL/SQL ブロックです。

ヘッダ
PL/SQL 
create or replace procedure プロシージャ名
[(argument {IN|OUT} type )] ;
/ 
create or replace procedure proc1
 (id INTEGER, amount REAL) ；
 /
ヘッダ
c 
create or replace procedure 関数名
[(argument {IN|OUT} type )] 
is external
   name     関数名 
   library  ライブラリ名
   parameters (....)
   language c;
/ 
procedure func1 (vardata In OUT VARCHAR2)
is external
  name "updvar"  library upd_lib
  parameters(vardata STRING,vardata MAXLEN SB4,return STRING)
  language c;
/
本体 
procedure プロシージャ名
[(argument {IN|OUT} type )]  is
begin
プロシージャ本体
end プロシージャ名;
/
procedure proc1 (id INTEGER, amount REAL) is
  work_var REAL;
BEGIN

EXCEPTION
  exception handler
END  proc1
/
例
create or replace procedure get1
is
begin
declare
   cursor sales_cur is
      select ename,sal,empno from emp where job like 'SALES%';
   sales_rec sales_cur%ROWTYPE;
   i         PLS_INTEGER := 1;
   empno     PLS_INTEGER ;
 begin
   open sales_cur;

   loop
      fetch sales_cur into sales_rec;
      if  sales_cur%NOTFOUND then
         exit;
      end if;
      sales_rec.sal := sales_rec.sal * 1.2;
      empno         := sales_rec.empno;
      update emp set sal = sales_rec.sal 
         where empno = sales_rec.empno;
      dbms_output.put_line('count=' || i ||
          ' empno=' || empno ||
          'emp=' || sales_rec.ename);
      i := i + 1;
   end loop;
   close sales_cur;
 end;
end get1;
/

実行結果
SQL> exec get1
count=1 empno=7499 emp=ALLEN
count=2 empno=7521 emp=WARD
count=3 empno=7654 emp=MARTIN
count=4 empno=7844 emp=TURNER

パッケージ

パッケージは名前つき宣言セクションのことで、プロシージャ、関数、カーソル、変数などを記述できます。
パッケージは、パブリックに利用できる要素をリストする仕様と、 プロシージャや関数の実装を含む本体の２つから構成されます。

パッケージ仕様は必須で、パッケージ外からでも参照できるように公開します。

パッケージ本体には、仕様に含まれるプロシージャ、関数、カーソルなどを実装するすべてのコードが含まれます。

パッケージ仕様
CREATE OR REPLACE 
PACKAGE パッケージ名
IS
変数と型の宣言
カーソル仕様
モジュール仕様
END パッケージ名;
create or replace package getrefpkg is
procedure prtdata (
   cnt       IN number,
   emono     IN number,
   ename     IN varchar2,
   sal       IN number);
procedure getref;
end getrefpkg;
/
パッケージ本体
CREATE OR REPLACE
PACKAGE BODY パッケージ名
IS
変数と型の宣言
カーソル仕様と SELECT 文
モジュール仕様と本体
BEGIN
  :
END パッケージ名;
create or replace package body getrefpkg is
procedure prtdata (
   cnt    IN  number,
   empno  IN  number,
   ename  IN  varchar2,
   sal    IN  number)
is
begin
   dbms_output.put_line('count=' || cnt || ' empno=' || empno
               || ' salary=' || sal || ' emp=' || ename);
end prtdata;

procedure getrefpk
is
begin
declare
   type cur_type is ref cursor return emp%ROWTYPE;
   sales_cur cur_type;
   sales_rec emp%ROWTYPE;
   i         PLS_INTEGER := 1;
   empno     PLS_INTEGER ;
 begin
   open sales_cur for select * from emp where job like 'SALES%';

   loop
      fetch sales_cur into sales_rec;
      if  sales_cur%NOTFOUND
      then
         exit;
      end if;
      sales_rec.sal := sales_rec.sal * 1.2;
      empno         := sales_rec.empno;
      update emp set sal = sales_rec.sal where empno = sales_rec.empno;
      i := sales_cur%ROWCOUNT ;
      prtdata(i,empno,sales_rec.ename,sales_rec.sal);
   end loop;
   commit;
   close sales_cur;
 end;
end getrefpk;
end getrefpkg;
/
パッケージの実行
BEGIN
パッケージ名.プロシージャ名();
END;
 
begin
   getrefpkg.getrefpk();
end;
/


実行結果
count=1 empno=7499 salary=2304 emp=ALLEN
count=2 empno=7521 salary=1800 emp=WARD
count=3 empno=7654 salary=1800 emp=MARTIN
count=4 empno=7844 salary=2160 emp=TURNER

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

トリガー

トリガーは名前つき PL/SQL ブロックである点はプロシージャに似ていますが、 他のブロックから契機となるイベントが発生するたびに暗黙的に実行されます。 トリガーの実行の契機となるイベントには次の３つがあります。

  1. DML イベントは、INSERT,UPDATE,DELETE 文を実行した場合。
  2. DDL イベントは、CREATE,ALTER,DROP 文を実行した場合。
  3. データベース イベントは、あらかじめ定義されているイベントが発生した場合。

トリガーの用途

  1. 表への変更を記録したり、通知できます。
  2. 複雑な整合性制約を維持できます。

トリガーの制限事項

  1. commit,rollback,savepoint を発行してはいけません。
  2. long,longraw の変数を宣言できません。 また long,longraw の列を :new :old で参照できません。
  3. トリガー本体からアクセスできる表には制限があります。

機能構文例確認
定義
create or replace trigger トリガー名
{before|after} トリガーイベント on 表名
[for each row [when トリガー条件]
   トリガー本体;

create or replace trigger updemp
   after insert or delete or update on emp for each row
declare
   emprec emp%ROWTYPE;
begin
   if deleting then
      dbms_output.put_line('delete ' || :old.ename );
   elsif inserting then
      dbms_output.put_line('inert ' || :old.ename );
   else
      dbms_output.put_line('update fr:' || :old.ename || ' to:'
                            || :new.ename);
   end if;
end updemp;
/


実行結果
SQL> begin
  2  getrefpkg.getrefpk();
  3  end;
  4  /
update fr:ALLEN to:ALLEN
count=1 empno=7499 salary=2764.8 emp=ALLEN
update fr:WARD to:WARD
count=2 empno=7521 salary=2160 emp=WARD
update fr:MARTIN to:MARTIN
count=3 empno=7654 salary=2160 emp=MARTIN
update fr:TURNER to:TURNER
count=4 empno=7844 salary=2592 emp=TURNER

PL/SQLプロシージャが正常に完了しました。
user_triggers
削除drop tigger トリガー名;
alter trigger トリガー名 disable;		 drop trigger updemp;
alter trigger updemp disable;		 user_triggers
例
create table emp_log (
   emp_no   NUMBER(4),
   who      varchar2(10),
   operation char(1));

create or replace trigger emp_log
   after insert or update or delete on emp for each row
declare
   dmltype char(1);
begin
   if inserting then
      dmltype := 'I';
      insert into emp_log(emp_no,who,operation) values
         (:new.empno,USER,dmltype);
   elsif updating then
      dmltype := 'U';
      insert into emp_log(emp_no,who,operation) values
         (:new.empno,USER,dmltype);
   end if;
end;
/

実行結果
SQL> @trig3

トリガーが作成されました。

SQL> begin
  2  getrefpkg.getrefpk();
  3  end;
  4  /
count=1 empno=7499 salary=4777.57 emp=ALLEN
count=2 empno=7521 salary=3732.48 emp=WARD
count=3 empno=7654 salary=3732.48 emp=MARTIN
count=4 empno=7844 salary=4478.98 emp=TURNER

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

SQL> select * from emp_log;

    EMP_NO WHO        O
---------- ---------- -
      7499 TAKA2      U
      7521 TAKA2      U
      7654 TAKA2      U
      7844 TAKA2      U

トリガーイベント

イベント説明
insert表またはビューに行が追加されると発行する
update表またはビューの行が変更されると発行する。OF 句で特定の列の更新だけに限定できる
delete表またはビューの行が削除されると発行する
alter alter 文でデータベースオブジェクトが変更されると発行する
drop drop 文でデータベースからオブジェクトが削除されると発行する
servererrorサーバのエラーメッセージがログにきろくされると発行する
logon 表またはビューに行が追加されると発行する
logoff表またはビューに行が追加されると発行する
startupデータベースがオープンされると発行する
shutdownデータベースがクローズされると発行する

:old と :new

行レベルのトリガーについては :old :new の擬似レコードにそれぞれの値が設定されます。 ただし条件式 (when) のなかで書く場合は、コロンをつけません。

文種:old:new
insertNULL該当文実行後の値
update変更前の値該当文実行後更新した値
delete実行前の値NULL
述語動作
insertingトリガーの契機となる文が insert の場合は true そうでない場合は false
updatingトリガーの契機となる文が update の場合は true そうでない場合は false
deletingトリガーの契機となる文が delet の場合は true そうでない場合は false

機能

演算子

演算子説明例
**指数B := A**2
NOT論理的否定if (NOT b) then str := 'FALSE';end if;
+,-符号B := -A;
*,/乗算、除算B := A/2;
+,-加算、減算B := A + 10;
||文字列連結STR := 'AAA' || 'BBB';
=,!=,<,>,<=,>=論理的比較if (A = B) then ans := 1; end if;
AND,OR論理積、論理和if ((A=B) AND (B!=C)) then str := 'OK'; end if;

カーソル

カーソルの使い方

カーソルは、データベース内の表へのポインタと考えられます。 まず宣言してオープンすると使えます。 fetch でポインタをデータベースのレコードに一致させ、要素を取り出せます。 終了後にはクローズします。暗黙のカーソルは SQL です。

宣言 CURSOR カーソル名 [(引数)] [RETURN 戻り値] is SELECT 文; cursor emp_cur is select * from emp;
openOPEN カーソル名 [(引数)]; open emp_cur;
fetchFETCH カーソル名 INTO レコード/変数リスト; fetch emp_cur into emp_rec;
closeCLOSE カーソル名; close emp_cur;

カーソルの属性

カーソルには以下の４つの属性があります。

名前 説明例
%FOUND レコードが取れると真、失敗すると偽 SQL%FOUND
%NOTFOUND レコードが取れないと真、成功すると偽 SQL%NOTFOUND
%ROWCOUNT その時点までのレコード数を返す SQL%ROWCOUNT
%ISOPEN カーソルがオープンされていると真、そうでないと偽 SQL%ISOPEN
例
if dept_cur%NOTFOUND then
   exit;
end if;

カーソル変数

カーソル変数は、カーソルオブジェクトを参照するデータ構造です。
カーソルオブジェクトとは、カーソルの結果セットを指します。
oracle では SGA の作業領域へのポインタです。

カーソル変数 (ref cursor) の機能
  1. カーソル変数は、引数としてプロシージャに渡せます。
  2. カーソルの内容を別のカーソル変数に代入できます。

定義TYPE カーソル名 IS REF CURSOR [RETURN レコードの型];

RETURN 句を指定しない場合は、弱い型の REF CURSOR を宣言することになります。
弱い型の REF CURSOR は、実行時に任意の問い合わせに関連付けられます。		 
declare
   type emp_cur is ref cursor return emp%ROWTYPE;
   emp_rec  emp_cur;
begin
   open emp_cur for select * from emp;
   fetch emp_cur into emp_rec;
   close emp_cur;
end;
openOPEN カーソル変数名 for select 文; open emp_cur for select * from emp;
fetchFETCH カーソル変数名 INTO レコード/変数リスト; fetch emp_cur into emp_rec;
closeCLOSE カーソル変数名; close emp_cur;
例
getrefp.sql
set serveroutput on;
create or replace procedure prtdata (
   cnt    IN  number,
   empno  IN  number,
   ename  IN  varchar2,
   sal    IN  number)
is
begin
   dbms_output.put_line('count=' || cnt || ' empno=' || empno
               || ' salary=' || sal || ' emp=' || ename);
end prtdata;
/
show errors
create or replace procedure getrefp
is
begin
declare
   type cur_type is ref cursor return emp%ROWTYPE;
   sales_cur cur_type;
   sales_rec emp%ROWTYPE;
   i         PLS_INTEGER := 1;
   empno     PLS_INTEGER ;
 begin
   open sales_cur for select * from emp where job like 'SALES%';

   loop
      fetch sales_cur into sales_rec;
      if  sales_cur%NOTFOUND
      then
         exit;
      end if;
      sales_rec.sal := sales_rec.sal * 1.2;
      empno         := sales_rec.empno;
      update emp set sal = sales_rec.sal where empno = sales_rec.empno;
      i := sales_cur%ROWCOUNT ;
      prtdata(i,empno,sales_rec.ename,sales_rec.sal);
   end loop;
   close sales_cur;
 end;
end getrefp;
/
 
実行結果
SQL> exec getrefp
count=1 empno=7499 salary=3981.31 emp=ALLEN
count=2 empno=7521 salary=3110.4 emp=WARD
count=3 empno=7654 salary=3110.4 emp=MARTIN
count=4 empno=7844 salary=3732.48 emp=TURNE

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

例外ハンドラ

構文は以下のとおりです。 定義済みの関数である sqlcode と sqlerrm を使うと oracle のエラー番号とエラーメッセージを調べられます。

構文例
EXCEPTION
   WHEN 例外 THEN
      処理部
   
   WHEN 例外 THEN
      処理部
   
   WHEN OTHERS THEN 
END;

create or replace procedure fio_tst is
begin
declare
  config_file utl_file.file_type;
  FPASS       varchar2(20) := 'OUT_DIR';
  FNAME       varchar2(20) := 'out.log';
  iomode      varchar2(20) := 'a';
  line_in     varchar2(128) := 'sample data 1';
begin
  config_file := utl_file.fopen(FPASS,FNAME,iomode);
  utl_file.put(config_file,line_in);
  utl_file.new_line(config_file);
  utl_file.fclose(config_file);
exception
  when utl_file.invalid_path then
     dbms_output.put_line(FPASS || FNAME || ': invalid path or name:open ');
  when utl_file.invalid_mode then
     dbms_output.put_line(FPASS || FNAME || ': invalid mode :open ');
  when utl_file.invalid_operation then
     dbms_output.put_line(FPASS || FNAME || ': invalid ope:open ');
  when utl_file.write_error then
     dbms_output.put_line(FPASS || FNAME || ': write error ');
  when others then
     dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
end;
end fio_tst;
/


実行結果
SQL> exec fio_tst

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

SQL> !ls -l /tmp/taka2/out.log
-rw-r--r--   1 ora92032 dba           42 12月  2日  13:58 /tmp/taka2/out.log

また例外が発生したとき、その処理後は呼び元に返ります。 

set serveroutput on;
create or replace package pk_a is
  procedure proc_a1(arg in varchar2);
end;
/
create or replace package pk_b is
  procedure proc_b1(argb in varchar2);
end;
/

create or replace package body pk_b is
  procedure proc_b1(argb in varchar2) is
    buf number;
  begin
    dbms_output.put_line('B : before to_number');
    buf := to_number(argb);
    dbms_output.put_line('B : after  to_number');
  exception
    when others then
      dbms_output.put_line(sqlerrm);
  end;
end;
/

create or replace package body pk_a is
  procedure proc_a1(arg in varchar2) is
  begin
    dbms_output.put_line('A : before proc_b1');
    pk_b.proc_b1(arg);
    dbms_output.put_line('A : after  proc_b1');
  end;
end;
/
exec pk_a.proc_a1('123');
exec pk_a.proc_a1('aaa');


実行結果
A : before proc_b1
B : before to_number
B : after  to_number
A : after  proc_b1

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

A : before proc_b1
B : before to_number
ORA-06502: PL/SQL: 数値または値のエラー:
文字から数値への変換エラー。が発生しました
A : after  proc_b1

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

SQL

PL/SQL 内で発行できる SQL 文は次のとおりです。

構文例 参照
データ操作言語(DML)select,insert,update,delete,set transaction,explain plan dbms_sql
データ定義言語(DDL)drop,create,alter,grant,revoke
トランザクション制御commit,rollback,savepoint
セッション制御commit,rollback,savepoint
システム制御alter system
組み込み SQLconnect,declare cursor,allocate

selectselect SELECT_LIST into 変数 from 表 [where 句];
insertinsert into 表 [(カラム名)] values (値リスト);
updateupdate 表 SET (カラム名) SELECT_LIST into 変数 from 表 [where 句];
deletedelete from where 句;

動的 SQL

構文 例
execute immediate SQL_STRING using 
begin
   ececute immediate 'drop table emp';
end;
/
open CURSOR_VAR for QUERY_STRING using
例
create or replace procedure runplsql (
   progname  IN  varchar2,
   inval     IN  number,
   outval1   OUT varchar2,
   outval2   OUT varchar2) is
begin
   execute immediate
     'begin ' || progname || '(:val1, :val2, :val3);end;'
     using inval, OUT outval1, OUT outval2;
end;
/

組込み関数

数値関数 説明 例結果
length(str) str の長さを返す length('XYXYXShort')10
mod(x,y) x/y の剰余を求める mod(23,5)3
文字関数 説明 例結果
lpad(str1,len,[str2]) str1 の左側を str2 で埋め全体長が len になるようにします lpad('Short',10,XY)XYXYXShort
ltrim(str1,str2) str1 の左側にある str2 を削除します ltrim('XYXYXShort',XY)Short
replace(str,srh_str,[rep_str]) str の srh_str を rep_str でおきかえます replace(This and That','Th','B')Bis and Bat
rpad(str1,len,[str2]) str1 の右側を str2 で埋め全体長が len になるようにします rpad('Short',10,XY)ShortXYXYX
rtrim(str1,str2) str1 の右側にある str2 を削除します rtrim('ShortXYXYX',XY)Short
substr(str,a,[b]) str の中の a 以降 b 文字を返します substr(abc123def,4,4)123d
日付関数 説明 例結果
add_months(day,n) day に n 日を足した日付を返す add_months('02-FEB-91',1)02-MAR-92
last_day(day) day の月の最終日を返す last_day('02-FEB-91')28-FEB-91
months_between(day1,day2) day1 と day2 の間の月数を返す months_between('02-FEB-91','02-FEB-90')12
next_day(date,day) date の次の day を返す next_day('02-FEB-91','friday')8-FEB-91
trunc(date[,format]) date を format 書式で返す trunc(sysdate,'YYYY MM DD')2005 12 19
to_char(date[,format]) date を varchar2 文字列で返す to_char(sysdate)2005 年12 月 19 日
その他関数 説明 例結果
nvl(exp1,exp2) exp1 が NULL の場合は exp2 を返す nvl(NULL,'OK')OK

組み込みパッケージ

dbms_job 説明 例
broken 指定したジョブに中断マークをつける。以降実行されない
intervalキューに登録されたジョブの実行間隔を変更する
isubmit 指定したジョブ番号を持つキューにジョブを投入する
remove ジョブをキューから削除する
run 指定したジョブの即時実行を強制する
submit ジョブをキューに送り、そのジョブ番号を返す
dbms_sql 説明 例
bind_variable 特定の値をホスト変数にバインドする
close_cursor カーソルをクローズする
column_value カーソルからローカル変数に値を取り出す
define_column カーソルをクローズする
execute 関数 カーソルを実行する
fetch_rows 関数 カーソルから行を取り出す
is_open 関数 カーソルがオープンされているとき真を戻す
open_cursor 関数 カーソルをオープンする
parse プロシージャ 指定された SQL 文を構文解析する
variable_value カーソル内の変数値を取得する
dbms_output 説明 例
初期設定 set serveroutput on; 端末に出力する
disable dbms_output バッファを画面にフラッシュさせない
enable dbms_output バッファを画面にフラッシュさせる
get_line dbms_output バッファから１行を受け取る
get_linesdbms_output バッファから複数行を受け取り PL/SQL 表に渡す
new_line dbms_output バッファに改行を挿入する
put dbms_output バッファに情報を書き込む
put_line dbms_output バッファに情報を書き込み改行を追加する
utl_file 説明 例
前処理 1 
SQL> CREATE DIRECTORY OUT_DIR AS '/tmp/taka2';
SQL> GRANT READ ON DIRECTORY OUT_DIR TO DBA;
select * from all_directories;    これで登録できたことを確認
                                  以降このディレクトリ名 (OUT_DIR) を使う
前処理 2 init.ora の utl_file_dir=* を入れるとパーミッションが無効化されます
fclose 指定ファイルをクローズする fclose(UTL_FILE.FILE_TYPE)
fclose_all オープンした全ファイルをクローズする fclose_all
fflush 全データをファイルに書き出す fflush(FILE_TYPE)
fopen 指定ファイルをオープンする UTL_FILE.FILE_TYPE=fopen(pass,name,mode)
get_line ファイルから次行を取り出す get_line(UTL_FILE.FILE_TYPE,buf)
is_open ファイルがすでにオープンされている場合、真を返す bool=is_open(UTL_FILE.FILE_TYPE)
new_line 現在行に改行マークを入れる new_line(UTL_FILE.FILE_TYPE,nline)
put テキストをバッファに書き込む put(UTL_FILE.FILE_TYPE,line)
put_line １行分のテキストをファイルに書き込む put_line(UTL_FILE.FILE_TYPE,line)
putf 書式化されたテキストをバッファに書き込む putf(UTL_FILE.FILE_TYPE,fmt,item_lst)
例1
declare
  config_file utl_file.file_type;
  FPASS       varchar2 := 'OUT_DIR';
  FNAME       varchar2 := 'out.log';
  MODE        varchar2 := 'A';
begin
  config_file := utl_file.fopen(FPASS,FNAME,MODE);
  utl_file.put(config_file,line_in);
  utl_file.new_line(config_file);
  utl_file.fclose(config_file);
exception
  when utl_file.invalid_path then
     dbms_output.put_line(FPASS || FNAME || ' invalid pass ');
  when utl_file.invalid_mode then
     dbms_output.put_line(FPASS || FNAME || ' invalid mode ');
  when utl_file.write_error then
     dbms_output.put_line(FPASS || FNAME || ' do not to wtite ');
  when others then
     dbms_output.put_line(FPASS || FNAME || ' other error ');
end;
/


SQL> exec fio_tst

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

SQL> !ls -l /tmp/taka2/out.log
-rw-r--r--   1 ora92032 dba           42 12月  2日  13:58 /tmp/taka2/out.log
例2 
fred.sql
create or replace procedure fread is
begin
declare
  config_file utl_file.file_type;
  FPASS       varchar2(20) := '/tmp/taka2/';
  FPASS5      varchar2(20) := 'OUT_DIR';
  FNAME       varchar2(20) := 'jpn.data';
  iomode      varchar2(20) := 'r';
  line_in     varchar2(128) ;
begin
  config_file := utl_file.fopen(FPASS5,FNAME,iomode);
  utl_file.get_line(config_file,line_in);
  dbms_output.put_line(line_in);
  utl_file.fclose(config_file);
exception
  when utl_file.invalid_path then
     dbms_output.put_line(FPASS || FNAME || ': invalid path or name:open ');
  when utl_file.invalid_mode then
     dbms_output.put_line(FPASS || FNAME || ': invalid mode :open ');
  when utl_file.invalid_operation then
     dbms_output.put_line(FPASS || FNAME || ': invalid ope:open ');
  when utl_file.write_error then
     dbms_output.put_line(FPASS || FNAME || ': write error ');
  when others then
     dbms_output.put_line(FPASS || FNAME || ': other error ');
end;
end fread;
/


SQL> exec fread
日本語のデータです

PL/SQLプロシージャが正常に完了しました。
dbms_utilty 説明 例
format_call_stack call stack を varchar2 値として返す cstk := dbms_utility.format_call_stack;

デバッグ

ツール

debug.sql に debug/reset があります。 debug_tbl に情報がはいります。 

create or replace package debug is
-- select debug_str from debug_tbl order by lineno;
   procedure debug(i_desc in varchar2,value in varchar2);
   procedure reset;
end debug;
/
show errors

create or replace package body debug is
  lno NUMBER;
procedure debug(i_desc in varchar2,value in varchar2) is
begin
  insert into debug_tbl(debug_str,lineno) values
     (i_desc || '* '|| value,lno);
  commit;
  lno := lno + 1;
end debug;

procedure reset is
begin
  lno := 1;
  delete from debug_tbl;
end reset;

begin
  reset;
end debug;
/
show errors




外部プロシージャ

PL/SQL でかけない部分を c や java で書き、それらを呼び出す手段として外部プロシージャがあります。 PL/SQL からの呼び出し方について説明します。

外部プロシージャをよびだすためには、外部プロシージャ専用のリスナーを起動し tnsnames.ora に記述する 必要があります。それらについては外部プロシージャコールを参照してください。

ヘッダ部

構文例
create or replace library ライブラリID is'フルパス名';
create or replace function PL/SQL 関数名
   [(引数名 IN/OUT 型名)]
   return 型名 is external
   name "c プログラムの関数名"
   library ライブラリID
   parameters (変数名 型名, ... ,return 型名)
   languae [c,java];
/
set serveroutput on;
create or replace library upd_lib as
'/tmp/update.so';
/
create or replace function updvar (vardata IN OUT VARCHAR2)
return VARCHAR2
is external
  name "updvar"    library upd_lib
  parameters (vardata STRING,vardata MAXLEN SB4,return STRING)
  language c;
/
show errors

create or replace procedure gg
is
  vardata  VARCHAR2(32767);
  retdata  VARCHAR2(32767);
  no       PLS_INTEGER ;
  i        PLS_INTEGER ;
  MAX_LOOP PLS_INTEGER := 4;
begin
  i := 0;
  while i<MAX_LOOP loop
     select c1,c2 into vardata,no from vartbl where c2 = i;
     dbms_output.put_line('before : no='||no ||' vardata='||vardata);
     retdata := updvar(vardata);
     dbms_output.put_line('after  : no='||no ||' vardata='||vardata);
     dbms_output.put_line('return : no='||no ||' retdata='||retdata);
     update vartbl set c1 = retdata where c2 = i;
     i := i+1;
  end loop;
  commit;
end gg;
/
show errors

c

開発手順

  1. c でプログラムを作る
  2. c プログラムから共有ライブラリを作る
    cc -G プログラム名 -o ライブラリ名
  3. 外部プロシージャを呼び出し実行する
    SQL> exec 外部プロシージャ名

  1. c でプログラムを作る

    2. #include <stdio.h>
char *prt(void);
char msg[]="Hello World !!";

char *prt()  {
   return msg;
}

  2. c プログラムから共有ライブラリを作る

    3. cc -G prt.c -o libprt.so
cp libprt.so /tmp

  3. 外部プロシージャを呼び出し実行する

    4. create or replace library prt_lib as
'/tmp/libprt.so';
/
create or replace function prtfunc
return VARCHAR2
is external
  name "prt"    library prt_lib
  language c;
/
show errors

create or replace procedure prt
is
begin
   dbms_output.put_line(prtfunc());
end;
/

    
実行
SQL> exec prt
Hello World !!

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




java

java プログラムの開発手順を説明しますが、詳細は java 講座のストアド・プロシージャを参照してください。

開発手順

  1. java でプログラムを作成しコンパイルする
  2. loadjava でコンパイルしたクラスを oracle に取り込む
  3. PL/SQL で java のプログラムを実行する

  1. java のプログラムの作成

    2. public class hel  {
   static final String msg = "Hello World !!";
   public static String prt() {
      return msg;
   }
}

コンパイル
> javac hel.java

  2. loadjava でコンパイルしたクラスを oracle に取り込む

    3. > loadjava -resolve -user taka/taka hel.class

  3. PL/SQL で java のプログラムを実行する

    4. create or replace function extjava return varchar2 is
  language java
  name 'hel.prt() return java.lang.String';
/

set serveroutput on size 10000;
call dbms_java.set_output(1000);

begin
   dbms_output.put_line(extjava);
end;
/

    
実行
SQL> @hel
ファンクションが作成されました

コールが完了しました

Hello World !!

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




エラー対処法

エラーメッセージ 対処法
ORA-1405:
フェッチした列の値がNULLです
  1. 列の該当する値がない
  2. PL/SQL から呼ぶ外部プロシージャへの引数に値が設定されていない
ORA-28575:
リモートエージェントへの RPC 接続が失われました		 extproc が異常終了し実行不可となったためです。
実行中の外部プロシージャのライブラリが異常終了した場合などにおこります。 その場合は $ORACLE_HOME/rdbms/log 下に core があります。
ORA-6525:
CHARまたはRAWデータの長さが一致しません		 PL/SQL 側の宣言と外部プロシージャのライブラリ側の宣言が一致していない場合などにおこります
ORA-28595:
Extproc agent : Invalid DLL Path 		oracle9i からセキュリティ強化のため以下を設定する必要があります
(ENVS="EXTPROC_DLLS=ANY",
 ENVS="LD_LIBRARY_PATH=$ORACLE_HOME/lib:外部プロシージャのライブラリの場所")
ただし $ORACLE_HOME/lib　はフルパスをそのまま記述すること
KROWN 13162,14244 参照




その他

例

課題 プログラム例実行結果
50 までの 3 で割り切れる数を表示する

div3.sql		 
declare
   no       PLS_INTEGER;
   MAX_LOOP CONSTANT PLS_INTEGER := 30;
begin
for no in 1 .. MAX_LOOP
loop
   if mod(no,3) = 0  then
      dbms_output.put_line(no);
   end if;
end loop;
end;
/
 
SQL> @div3
3
6
9
12
15
18
21
24
27
30
boolean のテスト

bool.sql		 
declare
   total_sales NUMBER := 1001;
   no_revenue  BOOLEAN;
   msg         varchar2(12) := 'FALSE';
begin
   no_revenue := total_sales <= 0;
   if (no_revenue)  then
      msg := 'TRUE';
   end if;
   dbms_output.put_line(msg);
end;
/
 
SQL> @bool
FALSE

エラー対処法

エラーの内容 対処法
endif; と書いた end if; とする
仮パラメータに長さや精度、スケールの制約を書いた 仮パラメータは型だけを指定します
プロシージャ、関数が見つからない パッケージ名.プロシージャまたはパッケージ名.関数と書けばよい

FAQ

参考書

本の題名出版社価格
ORACLE PL/SQL programming 基礎編 オライリージャパン4500
ORACLE PL/SQL programming 応用編 オライリージャパン4300
ORACLE PL/SQL ワークブック オライリージャパン5800
ORACLE PL/SQL プログラミング 翔泳社5600

【　関連項目　】