6 ストアド・プロシージャおよびストアド・ファンクションの実行

この章では、PHPおよびOracle Databaseを使用してストアド・プロシージャおよびストアド・ファンクションを実行する方法について説明します。内容は次のとおりです。

PL/SQLを使用したビジネス・ロジックの取得
PL/SQL参照カーソルを使用した結果セットの戻し

各従業員の報酬を計算するPL/SQLファンクションおよび従業員レコードの参照カーソルを戻すPL/SQLプロシージャでAnycoアプリケーションを拡張します。

PL/SQLを使用したビジネス・ロジックの取得

Oracle PL/SQLプロシージャおよびファンクションを使用すると、すべてのクライアント・プログラムで使用できるビジネス・ロジックをデータベースに保存できます。これによって、データベースとPHP間で転送するデータ量を減らし、パフォーマンスを向上させることができます。

この項では、各従業員の合計報酬を計算して表示するPL/SQLストアド・ファンクションを作成します。

各従業員の合計報酬を表示するには、次の手順を実行します。

PHPアプリケーションは、HRユーザーとしてデータベースに接続します。DBA権限を持つユーザーとして、HRアカウントをロック解除する必要がある場合があります。HRユーザーをロック解除するには、次の手順を実行します。

SQL Developerを起動し、Oracle Databaseへの接続をオープンします。
systemユーザーとしてOracle Databaseにログインします。
SQLワークシートまたはSQL*Plusを開き、次のgrant文を実行してHRユーザーにcreate procedure権限を割り当てます。
```
grant create procedure to hr;
```
chap6_hrgrantproc.gifの説明
hrユーザーとしてHRサンプル・スキーマにログインします。

SQLワークシートまたはSQL*Plusを開き、次のテキストを入力してcalc_remuneration()関数を作成します。

create or replace function calc_remuneration(
  salary IN number, commission_pct IN number) return number is
begin
  return ((salary*12) + (salary * 12 * nvl(commission_pct,0)));
end;

chap6_hrcalcrem.gifの説明

chap6ディレクトリを作成し、chap5からアプリケーション・ファイルをコピーし、新しく作成したディレクトリに移動します。

Windowsの場合:

mkdir c:\program files\Apache Group\Apache2\htdocs\chap6
cd c:\program files\Apache Group\Apache2\htdocs\chap6
copy ..\chap5\* .

Linuxの場合:

mkdir $HOME/public_html/chap6
cd $HOME/public_html/chap6
cp ../chap5/* .

anyco.phpファイルを編集します。行が戻されるたびにPL/SQLファンクションがコールされるように construct_employees()関数の問合せを変更します。

$query =
 "SELECT employee_id,
         substr(first_name,1,1) || '. '|| last_name as employee_name,
         hire_date,
         to_char(salary, '9999G999D99') as salary,
         nvl(commission_pct,0) as commission_pct,
         to_char(calc_remuneration(salary, commission_pct),'9999G999D99')
           as remuneration
  FROM employees
  WHERE department_id = :did
  ORDER BY employee_id ASC";

anyco_ui.incファイルを編集します。 ui_print_employees()関数で、表に「Remuneration」列を追加し、従業員ごとに「remuneration」フィールドが表示されるようにforeachループを変更します。

echo <<<END
   <form method="post" action="$posturl">
   <table>
   <tr>
     <th>&nbsp;</th>
     <th>Employee<br>ID</th>
     <th>Employee<br>Name</th>
     <th>Hiredate</th>
     <th>Salary</th>
     <th>Commission<br>(%)</th>
     <th>Remuneration</th>
   </tr>
END;

    // Write one row per employee
    foreach ($employeerecords as $emp) {
      echo '<tr>';
      echo '<td><input type="radio" name="emprec"
                 value="'.htmlentities($emp['EMPLOYEE_ID']).'"></td>';
      echo '<td align="right">'.htmlentities($emp['EMPLOYEE_ID']).'</td>';
      echo '<td>'.htmlentities($emp['EMPLOYEE_NAME']).'</td>';
      echo '<td>'.htmlentities($emp['HIRE_DATE']).'</td>';
      echo '<td align="right">'.htmlentities($emp['SALARY']).'</td>';
      echo '<td align="right">'.htmlentities($emp['COMMISSION_PCT']).'</td>';
      echo '<td align="right">'.htmlentities($emp['REMUNERATION']).'</td>';
      echo '</tr>';
    }

アプリケーション・ファイルへの変更を保存します。ブラウザで、次のURLを入力して、アプリケーションをテストします。

Windowsの場合:
```
http://localhost/chap6/anyco.php
```
Linuxの場合:
```
http://localhost/~<username>/chap6/anyco.php
```
「Departments」ページで、「Show Employees」をクリックします。

chap6_stored_proc_test_001.gifの説明

「Employees」ページで、従業員の報酬が最後の列に表示されます。

chap6_stored_proc_test_002.gifの説明

PL/SQL参照カーソルを使用した結果セットの戻し

PL/SQLブロックから参照カーソルとして問合せデータを戻し、PHPに表示できます。この操作は、データセットで複雑な機能が必要な場合または複数のアプリケーション・プログラムで同じ問合せを使用する場合に有効です。

PL/SQLの参照カーソルは、カーソル変数に割り当てられているタイプ定義です。パッケージ仕様部内にPL/SQLタイプを宣言して、パッケージ本体などの他のPL/SQL構造で再利用することがよく行われます。

この項では、参照カーソルを使用して、特定の部門の従業員を取り出します。

PL/SQLパッケージ仕様部およびパッケージ本体を作成し、参照カーソルを使用して特定の部門の従業員を取り出すには、次の手順を実行します。

SQL Developerを起動し、hrユーザーとしてHRサンプル・スキーマにログインします。

SQLワークシートまたはSQL*Plusを開き、次のテキストを入力してPL/SQLパッケージcv_typesを作成します。

CREATE OR REPLACE PACKAGE cv_types AS
  TYPE empinfotyp IS REF CURSOR;
  PROCEDURE get_employees(deptid in number,
                          employees in out empinfotyp);
END cv_types;

「Run」をクリックします。

chap6_hrcreatepack.gifの説明

SQLワークシートで、次のテキストを入力してPL/SQLパッケージ本体cv_typesを作成します。

CREATE OR REPLACE PACKAGE BODY cv_types AS
  PROCEDURE get_employees(deptid in number,
                          employees in out empinfotyp)
  IS
  BEGIN
    OPEN employees FOR
      SELECT employee_id,
        substr(first_name,1,1) || '. '|| last_name as employee_name,
        hire_date,
        to_char(salary, '999G999D99') as salary,
        NVL(commission_pct,0) as commission_pct,
        to_char(calc_remuneration(salary, commission_pct),
                '9999G999D99') as remuneration
      FROM employees
      WHERE department_id = deptid
      ORDER BY employee_id ASC;
  END get_employees;
END cv_types;

「Run」をクリックします。

chap6_hrcreatebody.gifの説明

anyco_db.incファイルを編集します。 PL/SQLパッケージ・プロシージャをコールする新しいPHP関数を作成します。

// Use ref cursor to fetch employee records
// All records are retrieved - there is no paging in this example 
function db_get_employees_rc($conn, $deptid, &$e)
{
  // Execute the call to the stored procedure
  $stmt = "BEGIN cv_types.get_employees($deptid, :rc); END;";
  $stid = @oci_parse($conn, $stmt);
  if (!$stid) {
    $e = db_error($conn, __FILE__, __LINE__);
    return false;
  }
  $refcur = oci_new_cursor($conn);
  if (!$stid) {
    $e = db_error($conn, __FILE__, __LINE__);
    return false;
  }
  $r = @oci_bind_by_name($stid, ':RC', $refcur, -1, OCI_B_CURSOR);
  if (!$r) {
    $e = db_error($stid, __FILE__, __LINE__);
    return false;
  }
  $r = @oci_execute($stid);
  if (!$r) {
    $e = db_error($stid, __FILE__, __LINE__);
    return false;
  }
  // Now treat the ref cursor as a statement resource
  $r = @oci_execute($refcur, OCI_DEFAULT);
  if (!$r) {
    $e = db_error($refcur, __FILE__, __LINE__);
    return false;
  }
  $r = @oci_fetch_all($refcur, $employeerecords, null, null,
                          OCI_FETCHSTATEMENT_BY_ROW);
  if (!$r) {
    $e = db_error($refcur, __FILE__, __LINE__);
    return false;
  }
  return ($employeerecords);
}

db_get_employees_rc()関数は、次の無名（名前が指定されていない）PL/SQLブロックを実行します。

BEGIN cv_types.get_employees($deptid, :rc); END;

BEGIN ENDブロック内のPL/SQL文は、ストアドPL/SQLパッケージ・プロシージャcv_types.et_employees()をコールします。これによって、PHP変数$refcurにOCI_B_CURSOR参照カーソル・バインド変数が戻されます。

$refcur変数は、oci_parse()によって戻される文ハンドルと同様に処理されます。PHPでSQL問合せを実行した場合と同様に、実行操作およびフェッチ操作に使用されます。

anyco.phpファイルを編集します。construct_employees()関数で、問合せテキストおよびバインド引数を削除します。ファイルは、次のようになります。

function construct_employees()
{
  $deptid = $_SESSION['deptid'];
  $conn = db_connect($err);
  if (!$conn) {
    handle_error('Connection Error', $err);
  }
  else {
    $emp = db_get_employees_rc($conn, $deptid, $err);

    if (!$emp) {
      handle_error('Cannot fetch Employees', $err);
    }
    else {
      $deptname = get_dept_name($conn, $deptid);

      ui_print_header('Employees: '.$deptname);
      ui_print_employees($emp, $_SERVER['SCRIPT_NAME']);
      ui_print_footer(date('Y-m-d H:i:s'));
    }
  }
}

アプリケーション・ファイルへの変更を保存します。ブラウザで、次のURLを入力して、アプリケーションをテストします。

Windowsの場合:
```
http://localhost/chap6/anyco.php
```
Linuxの場合:
```
http://localhost/~<username>/chap6/anyco.php
```
「Departments」ページで、「Next」をクリックして「Marketing」部門ページにナビゲートします。

chap6_refcursor_005.gifの説明
「Marketing」部門ページで、「Show Employees」をクリックします。

chap6_refcursor_006.gifの説明

「Employees」ページに、従業員のページが以前と同様に表示されます。

chap6_refcursor_007.gifの説明