处理在PHP中返回记录的PL/SQL函数 [英] Handle a PL/SQL function returning a record in PHP
问题描述
我一直在寻找互联网,但没有找到答案,所以我在这里.
我有一个返回记录的PL/SQL函数:
create or replace package pck_test is
TYPE coord_geo is record (coord_x float, coord_y float);
function ArrondiGeo(coord_x float, coord_y float) return coord_geo;
end pck_test;
/
create or replace package body pck_test is
FUNCTION ArrondiGeo(coord_x FLOAT,coord_y FLOAT) RETURN coord_geo
IS
temp_x FLOAT(24);
temp_y FLOAT(24);
rec_coord coord_geo;
BEGIN
temp_x := ROUND(coord_x,4)/5;
temp_y := ROUND(coord_y,4)/5;
temp_x := ROUND((temp_x*5),3);
temp_y := ROUND((temp_y*5),3);
rec_coord.coord_x := temp_x;
rec_coord.coord_y := temp_y;
RETURN rec_coord;
END;
END pck_test;
/
我想在PHP函数中使用它,但是我真的不知道如何...
我试过了,但是没用:
public function get_tronc($x,$y)
{
$q = oci_parse($this->_db, 'begin :r := pck_test.ArrondiGeo(:x,:y); end;');
oci_bind_by_name($q, ':x', $x);
oci_bind_by_name($q, ':y', $y);
oci_bind_by_name($q, ':r', $r);
oci_execute($q);
return $r;
}
错误是:
Warning: oci_execute(): ORA-06550: line 1, column 13: PLS-00382: expression is of wrong type ORA-06550: line 1, column 7: PL/SQL: Statement ignored in /users/info/il3/jboeglin/Bureau/BDD/site/models/userManager.php on line 77
这是一个自我解释的错误,但我仍然不知道如何使用它.
谢谢.
对于命名数据类型,您可能需要绑定指定SQLT_NTY
类型的参数:
$r = oci_new_collection($this->db, 'COORD_GEO');
oci_bind_by_name($q, ':r', $r, -1, SQLT_NTY);
...
oci_execute($q);
// do whatever you need with your data
$data = $elem = $collection->getElem(1);
// then discard it
$r->free();
有关详细信息,请参见 oci_bind_by_name 的手册.
对于普通 PL/SQL记录,您可能不走运:根据 Oracle 12c + OCI8 2.0.7删除了以前不允许返回布尔值.
I've been looking on the Internet and I didn't find an answer so here I am.
I've a PL/SQL function returning a record :
create or replace package pck_test is
TYPE coord_geo is record (coord_x float, coord_y float);
function ArrondiGeo(coord_x float, coord_y float) return coord_geo;
end pck_test;
/
create or replace package body pck_test is
FUNCTION ArrondiGeo(coord_x FLOAT,coord_y FLOAT) RETURN coord_geo
IS
temp_x FLOAT(24);
temp_y FLOAT(24);
rec_coord coord_geo;
BEGIN
temp_x := ROUND(coord_x,4)/5;
temp_y := ROUND(coord_y,4)/5;
temp_x := ROUND((temp_x*5),3);
temp_y := ROUND((temp_y*5),3);
rec_coord.coord_x := temp_x;
rec_coord.coord_y := temp_y;
RETURN rec_coord;
END;
END pck_test;
/
And I want to use it in a PHP function but I don't really know how ...
I tried this but it doesn't work :
public function get_tronc($x,$y)
{
$q = oci_parse($this->_db, 'begin :r := pck_test.ArrondiGeo(:x,:y); end;');
oci_bind_by_name($q, ':x', $x);
oci_bind_by_name($q, ':y', $y);
oci_bind_by_name($q, ':r', $r);
oci_execute($q);
return $r;
}
The error is :
Warning: oci_execute(): ORA-06550: line 1, column 13: PLS-00382: expression is of wrong type ORA-06550: line 1, column 7: PL/SQL: Statement ignored in /users/info/il3/jboeglin/Bureau/BDD/site/models/userManager.php on line 77
So that's a self explaining error but I still can't figure how I can use it.
Thank you.
For a named datatype, you probably need to bind your parameter specifying the SQLT_NTY
type:
$r = oci_new_collection($this->db, 'COORD_GEO');
oci_bind_by_name($q, ':r', $r, -1, SQLT_NTY);
...
oci_execute($q);
// do whatever you need with your data
$data = $elem = $collection->getElem(1);
// then discard it
$r->free();
See the manual of oci_bind_by_name for details.
For plain PL/SQL records, you're probably out of luck: according to Oracle's documentation you can't use OCI to fetch a record. As of Oracle 11g:
The following two types are internal to PL/SQL and cannot be returned as values by OCI:
- Boolean,
SQLT_BOL
- Record,
SQLT_REC
If this is correct (I'm not a regular PHP user), you will probably have to wrap your function at PL/SQ level:
- either, in a procedure with the required number of
OUT
parameters; - or maybe, depending your needs, in a table function.
For sake of completeness, please note that Oracle 12c + OCI8 2.0.7 remove the restriction that had previously not allowed to return a boolean value.
这篇关于处理在PHP中返回记录的PL/SQL函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!