带参数的 PHP-OCI 调用存储过程 [英] PHP-OCI Call Stored Procedure with parameters

问题描述

存储过程:

parentchildhrs number(7,0);
childhours number(7,0);

begin

COMBINED_HOURS(122,parentchildhrs,childhours);

end;

以上通过 Oracle SQL Developer 执行时输出正确的预期值，即小时.下面输出

The above when executed via Oracle SQL Developer outputs the correct expected value which are hours. Output below

CHILD HOURS 50
parent task id 122
parentchild HOURS 100

现在当我用 PHP-OCI 调用时，它什么都不输出.

Now when I call with PHP-OCI, it outputs nothing.

$taskID = 122;
$parent_hours = '';
$child_hours = '';
$procedure = "BEGIN TASK_COMBINED_CHILD_HRS(:task_id, :parent_child_hours, :child_hours); END;";
$test = $taskmaster->getHours($procedure, [':task_id' => $taskID,':parent_child_hours' => (int)$sum_parent_child_hours,':child_hours' => (int)$sum_child_hours]);



    public function getHours($query, $params){
           $result_hours = parent::ocibindbyname($query, $params);
           return $result_hours;
    }

public static function customquery($query, $params)
{

  try{  

      $stmt = oci_parse($conn, $query);
      foreach ($params as $key => &$value) {              
        oci_bind_by_name($stmt, $key, $value, 32);
      }
      oci_execute($stmt,OCI_COMMIT_ON_SUCCESS);
      oci_commit($conn);
      oci_free_statement($stmt);
      return $params;
      }catch (Exception  $e){
        print_r($e);
    }               
}

打印结果给出

Array
(
    [:task_id] => 1142
    [:parent_child_hours] => 100
    [:child_hours] => 50
)

1. 如何将 parent_child_hours 和 child_hours 的 PHP 变量绑定为存储过程变量的输出?

1. How can I bind a PHP variable for parent_child_hours and child_hours as output from a stored procedure variable ?

推荐答案

使用 foreach 方法来自 my在处理存储过程时，回答您的另一个问题不是一个好主意.

Using the foreach approach from my answer to another of your questions is not a good idea when dealing with stored procedures.

虽然它确实有效(如您的问题所示，变量在执行后设置在 $params 数组中)，但最大的问题是您必须提供第四个参数(maxlength) 到 oci_bind_by_name.您在代码中使用了 32 的静态值，但当值的长度超过此值时会引发错误.它无法在运行时计算，将其设置为一个非常大的值是低效的(也许这对您的应用程序来说不是问题).

While it does work (as shown in your question, the variables are set in the $params array after execution) the biggest issue is that you must provide the fourth parameter (maxlength) to oci_bind_by_name. You've used a static value of 32 in your code, but an error is raised when the length of a value exceeds this. It can't be calculated at runtime and setting it to a very large value is inefficient (maybe this isn't a problem for your application).

由于您正在运行一个已知的存储过程，您应该在设计时知道输出值的maxlength，这些可以通过放置所有oci_* 函数静态输入在 getHours() 中，而不是尝试抽象对 customExecute() 的所有调用.

Since you are running a known stored procedure, you should know the maxlength of output values at design-time and these can statically entered by putting all the oci_* functions in getHours(), instead of trying to abstract all calls to customExecute().

这篇关于带参数的 PHP-OCI 调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！