「Oracle」ストアドプロシージャを作成してみる

Oracle Database のストアドプロシージャの作成をやってみたので、メモしておきます。
データベース屋ではないので必須知識ではないのですが、簡単なレベルであれば自分でできた方がいいだろうということで、やってみた次第です。

1. テスト用テーブルの作成

---

カラムが id と name だけの簡単なテーブルにしました。

create table t1 (id number(10), name varchar(10), primary key (id));

データもつっこんでおきます。

insert into t1 values (1, 'aaa');
insert into t1 values (2, 'bbb');
insert into t1 values (3, 'ccc');
insert into t1 values (4, 'aaa');
commit;

2. ストアドプロシージャの作成 ( パッケージなし )

---

上記のテーブル t1 のデータを取得 ( 条件は id ) するストアドプロシージャになります。

create or replace procedure select_t1_1 (aa in number, bb out number, cc out varchar) as
  begin
    select id, name into bb, cc from t1 where id = aa;
    dbms_output.put_line('id:' || bb);
    dbms_output.put_line('name:' || cc);
  end;
/

実行は以下の感じで。

set serveroutput on
variable bb number;
variable cc varchar2(10);
execute select_t1_1(1, :bb, :cc)

※ 実行結果

id:1
name:aaa

3. ストアドプロシージャの作成 ( パッケージあり )

---

パッケージも使えるみたいなので、これもやってみました。パッケージ使う利点は以下のドキュメントに情報ありました。

・PL/SQLパッケージ
http://docs.oracle.com/cd/E16338_01/appdev.112/b56260/packages.htm#i2408

パッケージ仕様部と本体をそれぞれ作成するみたいです。
■ 仕様部

create or replace package aaa as
  procedure select_t1_2 (aa in number, bb out number, cc out varchar);
end aaa;
/

■ 本体

create or replace package body aaa as
  procedure select_t1_2 (aa in number, bb out number, cc out varchar) is
  begin
    select id, name into bb, cc from t1 where id = aa;
    dbms_output.put_line('id:' || bb);
    dbms_output.put_line('name:' || cc);
  end;
end aaa;
/

実行は以下の感じ ( プロシージャ名をパッケージ名で修飾する )

set serveroutput on
variable bb number;
variable cc varchar2(10);
execute aaa.select_t1_2(1, :bb, :cc)

※ 実行結果

id:1
name:aaa

以上です。

・ストアドPL/SQLユニット用のSQL文
http://docs.oracle.com/cd/E16338_01/appdev.112/b56260/sqlstatements.htm
・CREATE PROCEDURE文
http://docs.oracle.com/cd/E16338_01/appdev.112/b56260/create_procedure.htm#i2072424
・CREATE PACKAGE文
http://docs.oracle.com/cd/E16338_01/appdev.112/b56260/create_package.htm#i2091914
・CREATE PACKAGE BODY文
http://docs.oracle.com/cd/E16338_01/appdev.112/b56260/create_package_body.htm#i2065383

[ 環境情報 ]
CentOS 5.5
Oracle Database 11g Release 2 ( 11.2.0.1.0 )