Edo::World::Blog

〜Linux/Solaris関連の作業メモ〜

SQL

[Oracle] PL/SQL(その8: ストアド・ファンクション)

ファンクションは、結果を1つだけ返すサブプログラムで

SQL内で利用できる。

代表的なファンクションsysdate関数を以下に示す。

SQL> select sysdate from dual;   ※SQL内で利用できる。

SYSDATE
--------
20110923


構文

create [ or replace ] function ファンクション名
  ...
return データ型
begin
  ...
  return 戻り値
end ファンクション名;


作成・実行

[作成]

前回のSQLを改変

create or replace procedure function print_sum(val_1 in number default 1,
                                     val_2 in number default 2,
                                     val_sum out number)    ※outは不要
return number
is
  val_sum number;   ※値を変数に入れずに、そのままreturnするので不要となった
begin
  return val_sum :=  (val_1 + val_2);
  dbms_output.put_line(val_sum);
end print_sum;
/

※ストアド・プロシージャと同名のファンクションは作成できないので注意

declare
  i number;
begin
  i := print_sum(10,20);    ※ファンクションの結果を変数にセット
  dbms_output.put_line(i);  ※変数にセットされた値を表示
end;
/
30

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

[Oracle] PL/SQL(その5: ストアド・サブプログラム)

ストアド・サブプログラムの良いところ

  • SQLをバッチ化して共有化できる。
    • プログラムに対して権限を委譲することで、オブジェクト権限でなく限定した操作のみ実行権限が与えられるのでセキュリティ的にも◎
  • パフォーマンス向上
    • コンパイルされデータベースに格納されるので、オーバーヘッドが少ない
    • メモリ上でも共有利用されるので、メモリアロケーションの重い処理が不要になる


作成・実行

[作成]

  画面に'hoge'と出力する簡易プログラム

SQL> create procedure print_hoge    ※最後の;(セミコロン)は不要
     is
     begin
       dbms_output.put_line('hoge');
     end;
     /

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

[実行]

SQL> begin
  2    print_hoge;
  3  end;
  4  /
hoge

executeでもOK

SQL> execute print_hoge;
hoge

ストアド・サブプログラムの共有化

実行権限さえあれば、他のユーザでも実行可能。下記例では"foo"さんに先ほど作成した"print_hoge"を使用可能とした。

grant execute on print_hoge to foo


登録状態確認

ストアド・サブプログラムは、user_source表に格納されSQLは"TEXT"カラムを参照することで確認可能。

SQL> desc user_source;
 名前                                                            NULL?    型
 ----------------------------------------------------------------- -------- --------------------------------------------
 NAME									    VARCHAR2(30)
 TYPE									    VARCHAR2(12)
 LINE									    NUMBER
 TEXT									    VARCHAR2(4000)

SQL> select text from user_source where name = 'print_hoge';

レコードが選択されませんでした。  ※小文字指定はできないらしい・・・

SQL> select text from user_source where name = 'PRINT_HOGE';

TEXT
------------------------------------------------------------------------------------------------------------------------
procedure print_hoge
is
begin
  dbms_output.put_line('hoge');
end;


削除

drop procedure文で実行

SQL> drop procedure PRINT_HOGE;

プロシージャが削除されました。


デバッグ

あとでやる。とりあえずプロシージャ作成時にコンパイルエラーとなったら以下コマンドを実行する。

SQL> show err

※以下のようにエラー箇所が表示される
PROCEDURE PRINT_HOGEのエラーです。

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/34     PLS-00103: 記号"("が見つかりました。 次のうちの1つが入るとき:
	 := . ) , @ % default character
         記号":=" は続行のために"("に代わりました。

マニュアル:

Oracle Databaseアドバンスト・アプリケーション開発者ガイド 11gリリース1(11.1)-ストアド・サブプログラムのデバッグ


[Oracle] PL/SQL(その4: カーソル属性)

カーソルの種類

区分 説明
明示カーソル カーソルにユーザーが名前を付けているもの
暗示カーソル カーソルにユーザーが名前を付けていないもの。(SELECT INTO、UPDATE、INSERTなど


カーソル属性

属性名 説明
%NOTFOUND 空の場合TRUEを返す
%ROWCOUNT FETCH文が実行された行数


明示カーソル


[使用例]

事前確認

SQL> select count(*) from emp where deptno = 30;

  COUNT(*)
----------
	 6

実行

declare
  cursor emp_cur is select empno,ename from emp where deptno = 30;
begin
  for emp_rec in emp_cur loop
    exit when emp_cur%NOTFOUND;
    dbms_output.put_line(emp_cur%ROWCOUNT);
  end loop;
end;
/

1
2
3
4
5
6

暗示カーソル


begin
  delete from emp where deptno = 30;
  dbms_output.put_line(SQL%ROWCOUNT);
end;
/
6

[Oracle] PL/SQL(その3: カーソル)


概要

昨日のエントリーに記載した"insert into"は検索結果が1行の場合のみ利用できたが、カーソルを使用することで複数行(0行以上)でも利用可能となる。

本エントリーでは、一般的なカーソルの使用法(カーソルを作ってOPEN->FETCH->CLOSE)は端折ってカーソルFORループに進む。


カーソルFORループ構文

カーソルを作ってFOR文で展開すると自動的にカーソルのOPEN->FETCH->CLOSEを処理してくれる。

ループで定義した索引名はループ内のみ有効なローカル変数となり、"ループ索引名.カラム名"で値を操作できる。

declare
  cursor カーソル名 is select文
begin
  for ループ索引名 in カーソル名 loop
    ...ループ索引名.カラム名...
  end loop;
end


[使用例]

テストに使用する構文

SQL> select empno,ename from emp where deptno=30;

     EMPNO ENAME
---------- ----------
      7499 ALLEN
      7521 WARD
      7654 MARTIN
      7698 BLAKE
      7844 TURNER
      7900 JAMES

6行が選択されました。

カーソルFORループを使用

SQL> declare
       cursor emp_cur is select empno,ename from emp where deptno = 30;
     begin
       for emp_rec in emp_cur loop
         dbms_output.put_line(emp_rec.empno||','||emp_rec.ename);
       end loop;
     end;
     /
7499,ALLEN
7521,WARD
7654,MARTIN
7698,BLAKE
7844,TURNER
7900,JAMES


FOR UPDATE + WHERE CURRENT OF

FOR UPDATE文を利用する時は、ROWIDを利用したデータアクセスを行う"WHERE CURRENT OF"を利用すると処理が高速化できる。

declare
  cursor emp_cur is select sal from emp
  where job = 'MANAGER' for update;
begin
  for emp_rec in emp_cur loop
    if emp_rec.sal > 2800 then
      update emp set sal = sal - 300
        where current of emp_cur;
    end if;
  end loop;
  commit;
end;
/

[Oracle] PL/SQL(その2: SELECT INTO文)

PL/SQLで最初に躓くポイントだと思われる"SELECT INTO文"

ぱっと見、selectした結果をどうにかする操作に見えるが変数への格納を行う代物。ただ一度理解すれば明快。


まずはテスト用のテーブル状態を確認。検索結果が1行のみ表示されること。

SQL> show user
ユーザーは"SCOTT"です。
SQL> select ename,job from emp where empno = 7782;

ENAME	   JOB
---------- ---------
CLARK	   MANAGER

SQL> 

SELECT INTO文を実行

    "select"の後のカラム名と"from"の後の変数名が対になっていて、select文の結果が変数にセットされるという代物。

なお、検索結果が1行の時しか"SELECT INTO文"は利用できない。複数行を操作したい場合は[Oracle] PL/SQL(その3: カーソル)を参照

SQL> declare
  2    var_ename emp.ename%type;
  3    var_job   emp.job%type;
  4  begin
  5    select ename,job into var_ename, var_job from emp
  6    where empno = 7782;
  7    dbms_output.put_line(var_ename||' '||var_job);
  8  end;
  9  /
CLARK MANAGER  ※変数にセットされた値が出力されていることを確認
プロフィール

edworld

記事検索
アクセスカウンター
  • 今日:
  • 昨日:
  • 累計:

  • ライブドアブログ