从oracle中选择记录 [英] selecting record from oracle

问题描述

要选择最后一条记录，我在数据库为MySQL时使用了该记录:

To select last record, I was using this when my database was MySQL:

$result = mysql_query("SELECT Id 
                         FROM test 
                     ORDER BY LENGTH(Id), Id ASC");

$count = mysql_numrows($result);

if($count != 0) {    
  $lastid = mysql_result($result,$count-1,"Id");    
}

...而且效果很好.
但是现在我的表在Oracle数据库中-我写道:

...and it works fine.
But now my table is in an Oracle database - I wrote:

$stid = oci_parse($conn, "SELECT Id 
                            FROM test 
                        ORDER BY LENGTH(Id), Id ASC");

oci_execute($stid);

$p = oci_parse($conn, "SELECT COUNT(ID) 
                         FROM test");

oci_execute($p);

$count = oci_fetch_array($p);

if($count[0] != 0) {    
  $lastid = oci_result($stid, $count[0]-1);
}

它不起作用-我没有得到最后的记录.

It doesn't work - I am not getting the last record.

推荐答案

如果您阅读了 oci_result ，您会发现您错误地使用了第二个参数.

If you read the documentation for oci_result, you'll find you're using the second parameter incorrectly.

第二个参数是用来检索列的参数-它可以是列名或序数值.有序值是一个数字，该数字基于SELECT子句中指定的列从一(1)开始.不建议您使用序号，因为如果查询发生更改-如果您忘记更新序号引用，则检索值会很麻烦.实际上，后续的COUNT查询返回的值大于第一个查询SELECT子句中的列数.

The second parameter is what you use to retrieve the column - it can be the column name, or the ordinal value. Ordinal value is a number, starting at one (1) based on the columns specified in the SELECT clause. Ordinals aren't a recommended practice, because if the query changes -- if you forget to update the ordinal reference your retrieval of values is screwed. Effectively, the subsequent COUNT query returns a value higher than the number of columns in the first queries SELECT clause.

$stid = oci_parse($conn, "SELECT Id 
                            FROM test 
                        ORDER BY LENGTH(Id), Id ASC");

oci_execute($stid);

$lastid = oci_result($stid, 1);

oci_result缺少mysql_result提供的第三个参数.

oci_result lacks the third parameter that mysql_result provides.

要使事情在Oracle中运行，请使用:

To make things work in Oracle, use:

$stid = oci_parse($conn, "SELECT x.id
                            FROM (SELECT Id 
                                    FROM test 
                                ORDER BY LENGTH(Id) DESC, Id DESC) x
                           WHERE ROWNUM = 1");

oci_execute($stid);

$lastid = oci_result($stid, 1);

更新后的查询将返回一行，最新的是基于反转您先前使用的ORDER BY.

The updated query will return one row, the latest based on reversing the ORDER BY you were using previously.

这篇关于从oracle中选择记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！