将PL / SQL块游标结果放在php变量上

Question

I need to put the query cursor result on a PHP variable. The result of select is a number that I need to use later. Is there any way to do this? ( I'm using oracle 11g )

$query2 = "DECLARE
CURSOR cursore IS
SELECT example FROM etable FOR UPDATE of example;
BEGIN
  OPEN cursore;
FETCH cursore INTO v_consulta;
UPDATE etable SET example = example + 1;
COMMIT;
  CLOSE cursore;
END;";

---

With this query I need to select a column, get its value and put it in a variable PHP. And then I need to lock it to prevent concurrent queries from getting the same value before the update. This was the best way that I have found. But I can't take the result of the select.

---

Following is the whole code with FETCH and oci_bind_by_name

<?php

    include "config.php";    
    $query2 = "DECLARE
    CURSOR cursore IS
    SELECT example FROM etable FOR UPDATE of example;
    BEGIN
      OPEN cursore;
    FETCH cursore INTO v_consulta;
    UPDATE etable SET example = example + 1;
    COMMIT;
      CLOSE cursore;
    END;";

    $s2 = oci_parse($c, $query2);
    if (!$s2) {
        $m2 = oci_error($c);
        trigger_error('Could not parse statement: ' . $m['message'], E_USER_ERROR);
    } //prepara para a execução

    $r2 = oci_execute($s2);
    if (!$r2){
        $m2 = oci_error($s2);
        trigger_error('Could not execute statement: ' . $m['message'], E_USER_ERROR);
    }// executa a consulta

while (oci_fetch($s2)) {
    echo oci_result($s2, 'v_consulta');
}

?>

---

I need to take the value of the FETCH and put ir in a PHP variable. Is it possible?
Maybe there is another way of doing the query. ( I'm using oracle 11g ).

I get this error:

Warning: oci_fetch(): ORA-24374: define not done before fetch or execute and fetch in C:\xampp\htdocs\ProjectO
umorca.php on line 30

line 30 is:

while (oci_fetch($s2)) {
    echo oci_result($s2, 'v_consulta');
}

---

Is anyone able to help out with this request? Thanks.

Answer 1

I need to select a column, get its value and put it in a variable php

in posgresql you can 'UPDATE [...] RETURNING [...]' and avoid cursor:

pg_query($dbconn, 'BEGIN TRANSACTION;');

pg_send_query($dbconn,'UPDATE etable SET example = example + 1 RETURNING example;');
$res = pg_get_result($dbconn);
$array = pg_fetch_assoc($res);
echo 'example=' . $array['example'] . "
";
pg_query($dbconn, 'COMMIT;');

Between "BEGIN TRANSACTION" and "COMMIT" nobody could select/update/delete etable, so it could be a bottle neck.