この章では、PHPおよびOracle Databaseを使用してストアド・プロシージャおよびストアド・ファンクションを実行する方法について説明します。内容は次のとおりです。
各従業員の報酬を計算するPL/SQLファンクションおよび従業員レコードの参照カーソルを戻すPL/SQLプロシージャでAnycoアプリケーションを拡張します。
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;
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
ディレクトリを作成し、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> </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」をクリックします。
「Employees」ページで、従業員の報酬が最後の列に表示されます。
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」をクリックします。
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」をクリックします。
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」部門ページにナビゲートします。
「Marketing」部門ページで、「Show Employees」をクリックします。
「Employees」ページに、従業員のページが以前と同様に表示されます。