PHP: Zend framework and Oracle cursors
Working with Oracle cursors is in Zend very limited. There is only a simple class Zend_Db_Cursor_Oracle that can handle SQL query and return the whole result.
In more complicated situations, we need to bind multiple variables, returning only part of the result, etc..
We will customize class Zend_Db_Adapter_Oracle and prepare to the processing cursors
In first step we will prepare functions which execute query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
/** * Execute cursor - setup all variables and create statement and new cursor * @param string $sql * @param string $cursor_name * @param array $bind */ public function execCursor( $sql, $cursor_name, $bind = array() ) { //create connection $conn = $this->getConnection(); //allocate and return new cursor $this->curs = oci_new_cursor($conn); //prepares an Oracle statement for execution $this->curs_smtp = oci_parse($conn, $sql); //binds a PHP variable to an Oracle placeholder oci_bind_by_name($this->curs_smtp, $cursor_name, $this->curs, -1, OCI_B_CURSOR); foreach ($bind as $key=>&$val) { //bind another PHP variables to an Oracle placeholder oci_bind_by_name($this->curs_smtp, $key, $val, -1, OCI_ASSOC); } return $this; } /** * Execute query before requesting data */ private function execCursorQuery() { //execute a statement oci_execute($this->curs_smtp); //return the last error if ( ($e = oci_error($this->curs_smtp ) ) != false ) { //create exception throw new Zend_Db_Adapter_Oracle_Exception($e, -1234); } //Executes a statement oci_execute($this->curs); return false; } /** * Released variables */ private function freeStatementCursor() { //Frees all resources associated with statement or cursor oci_free_statement($this->curs); oci_free_statement($this->curs_smtp); return false; } |
This function will prepare and execute sql query with cursors. Function execCursor have three parameters:
- $sql – sql query with cursor
- $cursor_name – cursor name
- $bind = array() – variables to binding
In next step we can write functions to return result from sql query. We will prepare four functions for this time:
- fetchCursorWithLimit – Execute the query and returns data in the limit
- fetchCursorPairs – Execute the query and returns the data pairs in the limit
- fetchCursorSingle – Returns the first record of the result
- fetchCursor – Return the first record
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
/** * Returns the next row from a query as an object * @object */ protected function getCursorRow() { //Returns the next row from a query as an object if exists, else return null value return oci_fetch_object( $this->curs ); } /** * Execute the query and returns data in the limit * @param int $count * @param int $offset * @return array */ public function fetchCursorWithLimit($count = -1, $offset = 0) { //Execute query $this->execCursorQuery(); $data = array(); //Fetch all in the limit if (oci_fetch_all($this->curs, $data, $offset, $count, OCI_FETCHSTATEMENT_BY_ROW ) ){ ; } //Release variables $this->freeStatementCursor(); return $data; } /** * Execute the query and returns the data pairs in the limit * @param string $key * @param string $value * @param int $count * @param int $offset * @return array */ public function fetchCursorPairs($key, $value, $count = 1, $offset = 0) { //Execute query $this->execCursorQuery(); $data = $temp = array(); //Fetch all in the limit if (oci_fetch_all($this->curs, $temp, $offset, $count, OCI_FETCHSTATEMENT_BY_ROW ) ){ //Create pairs array foreach( (array)$temp as $v){ $data[$v[$key]] = $v[$value]; } } //Release variables $this->freeStatementCursor(); return $data; } /** * Returns the first record of the result * @return mixed */ public function fetchCursorSingle() { //Execute query $this->execCursorQuery(); //Get next (first) record $data = (array)$this->getCursorRow(); //Release variables $this->freeStatementCursor(); if( is_array( $data )){ return reset($data); } return array(); } /** * Return the first record * @return array */ public function fetchCursor() { //Execute query $this->execCursorQuery(); //Get first record $data = $this->getCursorRow(); //Release variables $this->freeStatementCursor(); return (array)$data; } |
example of the use
Execute the sql query and returns the first record of the result:
|
1 2 3 4 5 6 7 8 9 10 11 |
public function getAllObjects(){ $sql = "DECLARE v_Return sys_refcursor; BEGIN .... END;"; return Zend_Db_Table::getDefaultAdapter() >execCursor($sql, ':v_Return') ->fetchCursorSingle(); } |
Execute the sql query and returns the data pairs in the limit (from 20, limit 10)
|
1 2 3 4 5 6 7 8 9 10 11 |
public function getAllObjects(){ $sql = "DECLARE v_Return sys_refcursor; BEGIN .... END;"; return Zend_Db_Table::getDefaultAdapter() ->execCursor($sql, ':v_Return') ->fetchCursorPairs('ID', 'NAME', 10, 20); } |
The solution again
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 |
class Zend_Db_Adapter_Oracle extends Zend_Db_Adapter_Abstract { ...... /** * start working with cursors * ********************* */ //protected cursor variable protected $curs = array(); //variable maintain smtp protected $curs_smtp = false; /** * Execute cursor - setup all variables and create statement and new cursor * @param string $sql * @param string $cursor_name * @param array $bind */ public function execCursor( $sql, $cursor_name, $bind = array() ) { //create connection $conn = $this->getConnection(); //allocate and return new cursor $this->curs = oci_new_cursor($conn); //prepares an Oracle statement for execution $this->curs_smtp = oci_parse($conn, $sql); //binds a PHP variable to an Oracle placeholder oci_bind_by_name($this->curs_smtp, $cursor_name, $this->curs, -1, OCI_B_CURSOR); foreach ($bind as $key=>&$val) { //bind another PHP variables to an Oracle placeholder oci_bind_by_name($this->curs_smtp, $key, $val, -1, OCI_ASSOC); } return $this; } /** * Execute query before requesting data */ private function execCursorQuery() { //execute a statement oci_execute($this->curs_smtp); //return the last error if ( ($e = oci_error($this->curs_smtp ) ) != false ) { //create exception throw new Zend_Db_Adapter_Oracle_Exception($e, -1234); } //Executes a statement oci_execute($this->curs); return false; } /** * Released variables */ private function freeStatementCursor() { //Frees all resources associated with statement or cursor oci_free_statement($this->curs); oci_free_statement($this->curs_smtp); return false; } /** * Returns the next row from a query as an object * @object */ protected function getCursorRow() { //Returns the next row from a query as an object if exists, else return null value return oci_fetch_object( $this->curs ); } /** * Execute the query and returns data in the limit * @param int $count * @param int $offset * @return array */ public function fetchCursorWithLimit($count = -1, $offset = 0) { //Execute query $this->execCursorQuery(); $data = array(); //Fetch all in the limit if (oci_fetch_all($this->curs, $data, $offset, $count, OCI_FETCHSTATEMENT_BY_ROW ) ){ ; } //Release variables $this->freeStatementCursor(); return $data; } /** * Execute the query and returns the data pairs in the limit * @param string $key * @param string $value * @param int $count * @param int $offset * @return array */ public function fetchCursorPairs($key, $value, $count = 1, $offset = 0) { //Execute query $this->execCursorQuery(); $data = $temp = array(); //Fetch all in the limit if (oci_fetch_all($this->curs, $temp, $offset, $count, OCI_FETCHSTATEMENT_BY_ROW ) ){ //Create pairs array foreach( (array)$temp as $v){ $data[$v[$key]] = $v[$value]; } } //Release variables $this->freeStatementCursor(); return $data; } /** * Returns the first record of the result * @return mixed */ public function fetchCursorSingle() { //Execute query $this->execCursorQuery(); //Get next (first) record $data = (array)$this->getCursorRow(); //Release variables $this->freeStatementCursor(); if( is_array( $data )){ return reset($data); } return array(); } /** * Return the first record * @return array */ public function fetchCursor() { //Execute query $this->execCursorQuery(); //Get first record $data = $this->getCursorRow(); //Release variables $this->freeStatementCursor(); return (array)$data; } /** * ********************* * end working with cursors */ ...... } |