| Oracle® Database 2日でPHP開発者ガイド 12c リリース1 (12.1) B71348-02 |
|
 前 |
 次 |
この章では、前の章で作成した「AnyCo Corp. Employees List」ページで従業員名の横にある「Show」リンクをクリックしたときに実行されるレポートを作成します。
前の章ではSQL問合せからデータをフェッチする方法を説明しました。この章では、PHPでREF CURSORを使用する方法について説明します。従業員に支給された備品の名前をREF CURSORでフェッチします。
この章のトピックは、次のとおりです。
PL/SQLパッケージには、関連する項目が単一の論理エンティティとして格納されます。パッケージは、次の2つの部分で構成されます。
パッケージ仕様部は、パッケージの内容物を定義します。C++言語などのヘッダー・ファイルに似ています。仕様部はすべてのパブリック項目を定義します。仕様部はパッケージへの公開インタフェースです。
パッケージ本体には、仕様部で定義したプロシージャおよびファンクションのコードと、仕様部で宣言していないプライベート・プロシージャおよびファンクションのコードが含まれます。プライベート・コードはパッケージ本体内でのみ確認できます。
パッケージ仕様部とパッケージ本体は、データ・ディクショナリに別々のオブジェクトとして格納され、user_sourceビューで確認できます。仕様部はPACKAGE型として格納され、本体はPACKAGE BODY型として格納されます。
一連のパブリック定数を宣言する場合と同様、本体のない仕様部を保持することはできますが、仕様部のない本体を保持することはできません。
ストアド・プロシージャとは、ある操作を実行するために設計された一連のPL/SQL文を1つにまとめて名前を付けたものです。ストアド・プロシージャはデータベース内に格納されます。これは、クライアント・アプリケーションがデータベース・オブジェクトと直接対話できるようにするものではなく、データベースのプログラミング・インタフェースを定義するものです。一般的にストアド・プロシージャを使用するのは、データを検証する場合や、複数のSQL問合せを組み合せた大規模で複雑な処理の指示をカプセル化する場合です。
ストアド・ファンクションは、戻り値パラメータを1つとります。ファンクションとは異なり、プロシージャは値を戻す場合と戻さない場合があります。
REF CURSORを使用することは、Oracle Databaseからの問合せ結果をクライアント・アプリケーションに戻す最も強力かつ柔軟で、拡張性のある方法の1つです。
REF CURSORはPL/SQLデータ型であり、この値はデータベース上の問合せ作業領域のメモリー・アドレスです。つまり、REF CURSORは、データベース上にある結果セットへのポインタまたはハンドルとなります。
REF CURSORには次の特性があります。
REF CURSORは、データベース上のメモリー・アドレスを参照します。したがって、クライアントがデータベースにアクセスするには、REF CURSORの存続中はデータベースに接続している必要があります。
REF CURSORにより、追加のデータベース・ラウンドトリップが発生します。REF CURSORがクライアントに戻されても、クライアントがREF CURSORをオープンしてデータをリクエストするまで、実際のデータは戻されません。ユーザーがその読取りを試行するまで、データは取得されません。
REF CURSORは更新できません。REF CURSORで表される結果セットは読取り専用です。REF CURSORを使用してデータベースを更新することはできません。
REF CURSORは後方にスクロールできません。REF CURSORで表される結果セットは、前進専用で順次アクセスされます。結果セット内のレコードをランダムにポイントするためにREF CURSOR内にレコード・ポインタを配置することはできません。
REF CURSORはPL/SQLデータ型です。PL/SQLコード・ブロック内でREF CURSORを作成して戻すことができます。
このマニュアルの例では、AnyCo Corpが各従業員に職務に必要な備品を支給するというシナリオを使用します。EQUIPMENT表には、備品名と支給先の従業員が格納されます。
SQL*Plusで、HRユーザーとして接続し、次のスクリプトを実行します。
sqlplus hr/welcome@localhost
CREATE TABLE equipment(
id NUMBER PRIMARY KEY,
employee_id REFERENCES employees(employee_id) ON DELETE CASCADE,
equip_name VARCHAR2(20) NOT NULL);
CREATE SEQUENCE equipment_seq;
CREATE TRIGGER equipment_trig BEFORE INSERT ON equipment FOR EACH ROW
BEGIN
:NEW.id := equipment_seq.NEXTVAL;
END;
/
PL/SQLシーケンスおよびトリガーによって、各新規従業員レコードが挿入される際、レコードに一意のキーが割り当てられます。
これらの文をNetBeansなどのSQLエディタで実行する場合は、CREATE TRIGGER文の末尾のスラッシュ('/')を省略します。SQL*Plus内でスラッシュは文の終わりを示すインジケータであり、データベースで実行する文の一部ではありません。
サンプル・データを作成します。
-- Sample Data INSERT INTO equipment (employee_id, equip_name) VALUES (100, 'pen'); INSERT INTO equipment (employee_id, equip_name) VALUES (100, 'telephone'); INSERT INTO equipment (employee_id, equip_name) VALUES (101, 'pen'); INSERT INTO equipment (employee_id, equip_name) VALUES (101, 'paper'); INSERT INTO equipment (employee_id, equip_name) VALUES (101, 'car'); INSERT INTO equipment (employee_id, equip_name) VALUES (102, 'pen'); INSERT INTO equipment (employee_id, equip_name) VALUES (102, 'paper'); INSERT INTO equipment (employee_id, equip_name) VALUES (102, 'telephone'); INSERT INTO equipment (employee_id, equip_name) VALUES (103, 'telephone'); INSERT INTO equipment (employee_id, equip_name) VALUES (103, 'computer'); INSERT INTO equipment (employee_id, equip_name) VALUES (121, 'computer'); INSERT INTO equipment (employee_id, equip_name) VALUES (180, 'pen'); INSERT INTO equipment (employee_id, equip_name) VALUES (180, 'paper'); INSERT INTO equipment (employee_id, equip_name) VALUES (180, 'cardboard box'); COMMIT;
SQL*Plusで、HRとしてプロシージャを作成します。
CREATE OR REPLACE PROCEDURE get_equip(eid_p IN NUMBER, RC OUT SYS_REFCURSOR) AS
BEGIN
OPEN rc FOR SELECT equip_name
FROM equipment
WHERE employee_id = eid_p
ORDER BY equip_name;
END;
/
PHPでは、匿名PL/SQLブロックを実行することで、このプロシージャをコールできます。
BEGIN get_equip(:id, :rc); END;
:idバインド変数は前述のバインドと同じように使用されます。get_equip()のWHERE句に使用するため、PHP変数からの値をデータベースに渡します。バインド変数:rcはこれと異なり、後述するとおりequip_name()から返された問合せ結果を格納します。
従業員の備品リストを表示するため、新規のPHPファイルac_show_equip.phpを作成します。
<?php /** * ac_show_equip.php: Show an employee's equipment * @package ShowEquipment */ session_start(); require('ac_db.inc.php'); require('ac_equip.inc.php'); $sess = new \Equipment\Session; $sess->getSession(); if (!isset($sess->username) || empty($sess->username) || !isset($_GET['empid'])) { header('Location: index.php'); exit; } $empid = (int) $_GET['empid']; $page = new \Equipment\Page; $page->printHeader("AnyCo Corp. Show Equipment"); $page->printMenu($sess->username, $sess->isPrivilegedUser()); printcontent($sess, $empid); $page->printFooter(); // Functions ?>
これはac_emp_list.phpとほぼ同じ構造です。今回は、$sess->getSession()の後の検証テストでも従業員識別子をチェックします。この値は、ac_emp_list.phpのprintrecords()関数からURLパラメータとして渡されます。
... <a href='ac_show_equip.php?empid=$eid'>Show</a> ...
識別子の値はac_show_equip.phpで、PHPの$_GETスーパーグローバル配列をとおしてアクセスされます。配列エントリが設定されていない場合、ac_show_equip.phpが不適切にコールされたものと見なされ、ユーザーはログイン・ページindex.phpにリダイレクトされます。
$_GET['empid']値を整数にキャストし、SQLインジェクションの危険性を極力少なくしています。この値をバインドしますが、すべてのユーザー入力を一貫してフィルタすることをお薦めします。$_GET['empid']になんらかの理由でアルファベットのテキストが格納された場合、PHPのキャスト・ルールの結果、$empidに数字0が格納されます。テキストの先頭が数字であった場合、$empidはその数字になりますが、少なくともそれに続くテキストは破棄されます。
ファイルのメイン・コンテンツに入る前に、ac_show_equip.phpのFunctionsセクションに、小さいヘルパー関数getempname()を追加します。
/**
* Get an Employee Name
*
* @param Db $db
* @param integer $empid
* @return string An employee name
*/
function getempname($db, $empid) {
$sql = "SELECT first_name || ' ' || last_name AS emp_name
FROM employees
WHERE employee_id = :id";
$res = $db->execFetchAll($sql, "Get EName", array(array(":id", $empid, -1)));
$empname = $res[0]['EMP_NAME'];
return($empname);
}
これはスクリプトを実行する従業員識別子を受け取り、一致する従業員名をルック・アップします。演習として、問合せで行が返されなかった場合の処置を加えてください。
メインのprintcontent()関数をac_show_equip.phpに追加します。
/**
* Print the main body of the page
*
* @param Session $sess
* @param integer $empid Employee identifier
*/
function printcontent($sess, $empid) {
echo "<div id='content'>\n";
$db = new \Oracle\Db("Equipment", $sess->username);
$empname = htmlspecialchars(getempname($db, $empid), ENT_NOQUOTES, 'UTF-8');
echo "$empname has: ";
$sql = "BEGIN get_equip(:id, :rc); END;";
$res = $db->refcurExecFetchAll($sql, "Get Equipment List",
"rc", array(array(":id", $empid, -1)));
if (empty($res['EQUIP_NAME'])) {
echo "no equipment";
} else {
echo "<table border='1'>\n";
foreach ($res['EQUIP_NAME'] as $item) {
$item = htmlspecialchars($item, ENT_NOQUOTES, 'UTF-8');
echo "<tr><td>$item</td></tr>\n";
}
echo "</table>\n";
}
echo "</div>"; // content
}
これは、新規のメソッドDb::refcurExecFetchAll()をコールします。レコードの配列が返され、慣習的なループの方法で出力されます。
REF CURSOR バインド・パラメータ:rcは、特別にバインドする必要があります。バインド変数名は文テキストの任意の場所で任意に選択または配置できるため、その名前は単独でrefcurExecFetchAll()に渡され、通常のバインド変数の配列には格納されません。
ac_db.inc.phpを編集しrefcurExecFetchAll()メソッドを作成し、これをDbクラスに追加します。
/**
* Run a call to a stored procedure that returns a REF CURSOR data
* set in a bind variable. The data set is fetched and returned.
*
* Call like Db::refcurexecfetchall("begin myproc(:rc, :p); end",
* "Fetch data", ":rc", array(array(":p", $p, -1)))
* The assumption that there is only one refcursor is an artificial
* limitation of refcurexecfetchall()
*
* @param string $sql A SQL string calling a PL/SQL stored procedure
* @param string $action Action text for End-to-End Application Tracing
* @param string $rcname the name of the REF CURSOR bind variable
* @param array $otherbindvars Binds. Array (bv_name, php_variable, length)
* @return array Returns an array of tuples
*/
public function refcurExecFetchAll($sql, $action, $rcname,
$otherbindvars = array()) {
$this->stid = oci_parse($this->conn, $sql);
$rc = oci_new_cursor($this->conn);
oci_bind_by_name($this->stid, $rcname, $rc, -1, OCI_B_CURSOR);
foreach ($otherbindvars as $bv) {
// oci_bind_by_name(resource, bv_name, php_variable, length)
oci_bind_by_name($this->stid, $bv[0], $bv[1], $bv[2]);
}
oci_set_action($this->conn, $action);
oci_execute($this->stid);
oci_execute($rc); // run the ref cursor as if it were a statement id
oci_fetch_all($rc, $res);
$this->stid = null;
return($res);
}
$rcname内のREF CURSORバインド・パラメータは、通常のPHP変数ではなくoci_new_cursor()で作成されたカーソルにバインドされます。型OCI_B_CURSORを指定する必要があります。
追跡するアクションのテキストを設定した後、PL/SQL文が実行されます。この例では、get_equip()がコールされ、問合せのカーソルをオープンして返します。これで、$rc内のREF CURSORをoci_parse()コールから返された識別子のように、PHP文の識別子として扱うことが可能になりました。フェッチはここから行われます。問合せ結果は、$resで関数のコール元に返されます。
すべてのファイルを保存し、ブラウザでアプリケーションを実行します。SimonまたはAdministratorとしてログインします。「Steven King」の横の「Show」リンクをクリックします。その個人が所有する備品が表示されます。
