PHP:oci_fetch_array()的用法_Oracle函数

oci_fetch_array

推荐整理分享PHP:oci_fetch_array()的用法_Oracle函数，希望有所帮助，仅作参考，欢迎阅读内容。

文章相关热门搜索词:,内容如对您有帮助，希望把文章链接给更多的朋友！

(PHP 5, PECL OCI8 >= 1.1.0)

oci_fetch_array — Returns the next row from a query as an associative or numeric array

说明 array oci_fetch_array ( resource $statement [, int $mode ] )

Returns an array containing the next result-set row of a query. Each array entry corresponds to a column of the row. This function is typically called in a loop until it returns FALSE, indicating no more rows exist.

If statement corresponds to a PL/SQL block returning Oracle Database c Implicit Result Sets, then rows from all sets are consecutively fetched. If statement is returned by oci_get_implicit_resultset(), then only the subset of rows for one child query are returned.

要获取 OCI8扩展进行数据类型映射的细节，请参见驱动所支持的数据类型。

参数

statement

有效的 OCI8 报表标识符由 oci_parse() 创建，被 oci_execute()或 REF CURSOR statement 标识执行。

Can also be a statement identifier returned by oci_get_implicit_resultset().

mode

An optional second parameter can be any combination of the following constants: oci_fetch_array() Modes Constant Description OCI_BOTH Returns an array with both associative and numeric indices. This is the same as OCI_ASSOC + OCI_NUM and is the default behavior. OCI_ASSOC Returns an associative array. OCI_NUM Returns a numeric array. OCI_RETURN_NULLS Creates elements for NULL fields. The element values will be a PHP NULL. OCI_RETURN_LOBS Returns the contents of LOBs instead of the LOB descriptors.

The default mode is OCI_BOTH.

Use the addition operator "+" to specify more than one mode at a time.

返回值

Returns an array with associative and/or numeric indices. If there are no more rows in the statement then FALSE is returned.

By default, LOB columns are returned as LOB descriptors.

DATE columns are returned as strings formatted to the current date format. The default format can be changed with Oracle environment variables such as NLS_LANG or by a previously executed ALTER SESSION SET NLS_DATE_FORMAT command.

Oracle&#;s default, non-case sensitive column names will have uppercase associative indices in the result array. Case-sensitive column names will have array indices using the exact column case. Use var_dump() on the result array to verify the appropriate case to use for each query.

The table name is not included in the array index. If your query contains two different columns with the same name, use OCI_NUM or add a column alias to the query to ensure name uniqueness, see example #7. Otherwise only one column will be returned via PHP.

范例

Example #1 oci_fetch_array() with OCI_BOTH

<?php$conn=oci_connect('hr','welcome','localhost/XE');if(!$conn){$e=oci_error();trigger_error(htmlentities($e['message'],ENT_QUOTES),E_USER_ERROR);}$stid=oci_parse($conn,'SELECTdepartment_id,department_nameFROMdepartments');oci_execute($stid);while(($row=oci_fetch_array($stid,OCI_BOTH))!=false){//Usetheuppercasecolumnnamesfortheassociativearrayindicesecho$row[0]."and".$row['DEPARTMENT_ID']."arethesame<br>n";echo$row[1]."and".$row['DEPARTMENT_NAME']."arethesame<br>n";}oci_free_statement($stid);oci_close($conn);?>

Example #2 oci_fetch_array() with OCI_NUM

<?php/*Beforerunning,createthetable:CREATETABLEmytab(idNUMBER,descriptionCLOB);INSERTINTOmytab(id,description)values(1,'Averylongstring');COMMIT;*/$conn=oci_connect('hr','welcome','localhost/XE');if(!$conn){$e=oci_error();trigger_error(htmlentities($e['message'],ENT_QUOTES),E_USER_ERROR);}$stid=oci_parse($conn,'SELECTid,descriptionFROMmytab');oci_execute($stid);while(($row=oci_fetch_array($stid,OCI_NUM))!=false){echo$row[0]."<br>n";echo$row[1]->read()."<br>n";//thiswilloutputfirstbytesfromDESCRIPTION}//

Example #3 oci_fetch_array() with OCI_ASSOC

<?php/*Beforerunning,createthetable:CREATETABLEmytab(idNUMBER,descriptionCLOB);INSERTINTOmytab(id,description)values(1,'Averylongstring');COMMIT;*/$conn=oci_connect('hr','welcome','localhost/XE');if(!$conn){$e=oci_error();trigger_error(htmlentities($e['message'],ENT_QUOTES),E_USER_ERROR);}$stid=oci_parse($conn,'SELECTid,descriptionFROMmytab');oci_execute($stid);while(($row=oci_fetch_array($stid,OCI_ASSOC))!=false){echo$row['ID']."<br>n";echo$row['DESCRIPTION']->read()."<br>n";//thiswilloutputfirstbytesfromDESCRIPTION}//

Example #4 oci_fetch_array() with OCI_RETURN_NULLS

<?php$conn=oci_connect('hr','welcome','localhost/XE');if(!$conn){$e=oci_error();trigger_error(htmlentities($e['message'],ENT_QUOTES),E_USER_ERROR);}$stid=oci_parse($conn,'SELECT1,nullFROMdual');oci_execute($stid);while(($row=oci_fetch_array($stid,OCI_ASSOC))!=false){//IgnoreNULLsvar_dump($row);}/*Theabovecodeprints:array(1){[1]=>string(1)"1"}*/$stid=oci_parse($conn,'SELECT1,nullFROMdual');oci_execute($stid);while(($row=oci_fetch_array($stid,OCI_ASSOC+OCI_RETURN_NULLS))!=false){//FetchNULLsvar_dump($row);}/*Theabovecodeprints:array(2){[1]=>string(1)"1"["NULL"]=>NULL}*/?>

Example #5 oci_fetch_array() with OCI_RETURN_LOBS

<?php/*Beforerunning,createthetable:CREATETABLEmytab(idNUMBER,descriptionCLOB);INSERTINTOmytab(id,description)values(1,'Averylongstring');COMMIT;*/$conn=oci_connect('hr','welcome','localhost/XE');if(!$conn){$e=oci_error();trigger_error(htmlentities($e['message'],ENT_QUOTES),E_USER_ERROR);}$stid=oci_parse($conn,'SELECTid,descriptionFROMmytab');oci_execute($stid);while(($row=oci_fetch_array($stid,OCI_ASSOC+OCI_RETURN_LOBS))!=false){echo$row['ID']."<br>n";echo$row['DESCRIPTION']."<br>n";//thiscontainsallofDESCRIPTION//Inaloop,freeingthelargevariablebeforethe2ndfetchreducesPHP'speakmemoryusageunset($row);}//

Example #6 oci_fetch_array() with case sensitive column names

<?php/*Beforerunning,createthetable:CREATETABLEmytab("Name"VARCHAR2(),cityVARCHAR2());INSERTINTOmytab("Name",city)values('Chris','Melbourne');COMMIT;*/$conn=oci_connect('hr','welcome','localhost/XE');if(!$conn){$e=oci_error();trigger_error(htmlentities($e['message'],ENT_QUOTES),E_USER_ERROR);}$stid=oci_parse($conn,'select*frommytab');oci_execute($stid);$row=oci_fetch_array($stid,OCI_ASSOC+OCI_RETURN_NULLS);//Because'Name'wascreatedasacase-sensitivecolumn,thatsame//caseisusedforthearrayindex.Howeveruppercase'CITY'must//beusedforthecase-insensitivecolumnindexprint$row['Name']."<br>n";//printsChrisprint$row['CITY']."<br>n";//printsMelbourneoci_free_statement($stid);oci_close($conn);?>

Example #7 oci_fetch_array() with columns having duplicate names

<?php/*Beforerunning,createthetables:CREATETABLEmycity(idNUMBER,nameVARCHAR2());INSERTINTOmycity(id,name)values(1,'Melbourne');CREATETABLEmycountry(idNUMBER,nameVARCHAR2());INSERTINTOmycountry(id,name)values(1,'Australia');COMMIT;*/$conn=oci_connect('hr','welcome','localhost/XE');if(!$conn){$e=oci_error();trigger_error(htmlentities($e['message'],ENT_QUOTES),E_USER_ERROR);}$sql='SELECTmycity.name,mycountry.nameFROMmycity,mycountryWHEREmycity.id=mycountry.id';$stid=oci_parse($conn,$sql);oci_execute($stid);$row=oci_fetch_array($stid,OCI_ASSOC);var_dump($row);//Outputonlycontainsone"NAME"

Example #8 oci_fetch_array() with DATE columns

<?php$conn=oci_connect('hr','welcome','localhost/XE');if(!$conn){$e=oci_error();trigger_error(htmlentities($e['message'],ENT_QUOTES),E_USER_ERROR);}//Setthedateformatforthisconnection.//Forperformancereasons,considerchangingtheformat//inatriggerorwithenvironmentvariablesinstead$stid=oci_parse($conn,"ALTERSESSIONSETNLS_DATE_FORMAT='YYYY-MM-DD'");oci_execute($stid);$stid=oci_parse($conn,'SELECThire_dateFROMemployeesWHEREemployee_id=');oci_execute($stid);$row=oci_fetch_array($stid,OCI_ASSOC);echo$row['HIRE_DATE']."<br>n";//prints--oci_free_statement($stid);oci_close($conn);?>

Example #9 oci_fetch_array() with REF CURSOR

<?php/*CreatethePL/SQLstoredprocedureas:CREATEORREPLACEPROCEDUREmyproc(p1OUTSYS_REFCURSOR)ASBEGINOPENp1FORSELECT*FROMall_objectsWHEREROWNUM<;END;*/$conn=oci_connect('hr','welcome','localhost/XE');if(!$conn){$e=oci_error();trigger_error(htmlentities($e['message'],ENT_QUOTES),E_USER_ERROR);}$stid=oci_parse($conn,'BEGINmyproc(:rc);END;');$refcur=oci_new_cursor($conn);oci_bind_by_name($stid,':rc',$refcur,-1,OCI_B_CURSOR);oci_execute($stid);//ExecutethereturnedREFCURSORandfetchfromitlikeastatementidentifieroci_execute($refcur);echo"<tableborder='1'>n";while(($row=oci_fetch_array($refcur,OCI_ASSOC+OCI_RETURN_NULLS))!=false){echo"<tr>n";foreach($rowas$item){echo"<td>".($item!==null?htmlentities($item,ENT_QUOTES):"&nbsp;")."</td>n";}echo"</tr>n";}echo"</table>n";oci_free_statement($refcur);oci_free_statement($stid);oci_close($conn);?>

Example # Pagination with oci_fetch_array() using a LIMIT-like query

<?php$conn=oci_connect('hr','welcome','localhost/XE');if(!$conn){$e=oci_error();trigger_error(htmlentities($e['message'],ENT_QUOTES),E_USER_ERROR);}//Findtheversionofthedatabasepreg_match('/Release([0-9]+)./',oci_server_version($conn),$matches);$oracleversion=$matches[1];//Thisisthequeryyouwantto"page"through$sql='SELECTcity,postal_codeFROMlocationsORDERBYcity';if($oracleversion>=){//MakeuseofOraclecOFFSET/FETCHNEXTsyntax$sql=$sql.'OFFSET:offsetROWSFETCHNEXT:numrowsROWSONLY';}else{//OlderOracleversionsneedanestedqueryselectingasubset//from$sql.Or,iftheSQLstatementisknownatdevelopment//time,considerusingarow_number()functioninsteadofthis//nestedsolution.Inproductionenvironments,becarefulto//avoidSQLInjectionissueswithconcatenation.$sql="SELECT*FROM(SELECTa.*,ROWNUMASmy_rnumFROM($sql)aWHEREROWNUM<=:offset+:numrows)WHEREmy_rnum>:offset";}$offset=0;//skipthismanyrows$numrows=5;//return5rows$stid=oci_parse($conn,$sql);oci_bind_by_name($stid,':numrows',$numrows);oci_bind_by_name($stid,':offset',$offset);oci_execute($stid);while(($row=oci_fetch_array($stid,OCI_ASSOC+OCI_RETURN_NULLS))!=false){echo$row['CITY']."".$row['POSTAL_CODE']."<br>n";}//

Example # oci_fetch_array() with Oracle Database c Implicit Result Sets

<?php$conn=oci_connect('hr','welcome','localhost/pdborcl');if(!$conn){$e=oci_error();trigger_error(htmlentities($e['message'],ENT_QUOTES),E_USER_ERROR);}//RequiresOCI.0andOracleDatabasec//Alsoseeoci_get_implicit_resultset()$sql='DECLAREc1SYS_REFCURSOR;BEGINOPENc1FORSELECTcity,postal_codeFROMlocationsWHEREROWNUM<4ORDERBYcity;DBMS_SQL.RETURN_RESULT(c1);OPENc1FORSELECTcountry_idFROMlocationsWHEREROWNUM<4ORDERBYcity;DBMS_SQL.RETURN_RESULT(c1);END;';$stid=oci_parse($conn,$sql);oci_execute($stid);//Note:oci_fetch_allandoci_fetch()cannotbeusedinthismannerecho"<table>n";while(($row=oci_fetch_array($stid,OCI_ASSOC+OCI_RETURN_NULLS))!=false){echo"<tr>n";foreach($rowas$item){echo"<td>".($item!==null?htmlentities($item,ENT_QUOTES|ENT_SUBSTITUTE):"&nbsp;")."</td>n";}echo"</tr>n";}echo"</table>n";//

注释

Note:

Associative array indices need to be in uppercase for standard Oracle columns that were created with case insensitive names.

Note:

查询返回巨大数量的数据行时，通过增大oci8.default_prefetch值或使用 oci_set_prefetch() 可显著提高性能。

Note:

The function oci_fetch_array() is insignificantly slower than oci_fetch_assoc() or oci_fetch_row(), but is more flexible.

参见

oci_fetch() - Fetches the next row into result-buffer oci_fetch_all() - 获取结果数据的所有行到一个数组 oci_fetch_assoc() - Returns the next row from a query as an associative array oci_fetch_object() - Returns the next row from a query as an object oci_fetch_row() - Returns the next row from a query as a numeric array oci_set_prefetch() - 设置预提取行数

PHP:oci_field_name()的用法_Oracle函数 oci_field_name(PHP5,PECLOCI8=1.1.0)oci_field_name返回字段名说明stringoci_field_name(resource$statement,int$field)oci_field_name()返回与字段数字索引（从1开始）相对应的字段名

PHP:oci_field_is_null()的用法_Oracle函数 oci_field_is_null(PHP5,PECLOCI8=1.1.0)oci_field_is_null检查字段是否为NULL说明booloci_field_is_null(resource$statement,mixed$field)如果statement语句结果中返回的field字段的值是NU

PHP:oci_fetch_object()的用法_Oracle函数 oci_fetch_object(PHP5,PECLOCI8=1.1.0)oci_fetch_objectReturnsthenextrowfromaqueryasanobject说明objectoci_fetch_object(resource$statement)Returnsanobjectcontainingthenextresult-setrowofaquery.Eachattr