调用从PHP进行插入/更新的Oracle函数? [英] call an Oracle function that do an insert/update from PHP?

问题描述

我们有一个函数，如果我们使用SELECT进行调用，则会得到错误代码ORA-14551无法在查询中执行DML操作"

we have a function if we call it by using SELECT we get the error code ORA-14551 "Cannot preform a DML operation inside a query"

select pkg_tools.replace_site(1121,3343) from dual;

如何运行此功能并获得结果

how to run this function and get the results

当我们以这种方式在SQL Developer中运行它时:

when we run it in SQL developer in this way:

declare
v_return VRACHAR2(200);
begin
v_return := pkg_tools.replace_site(1121,3343);
end;

没有错误

，我们需要在PHP内部调用此函数

注意:我无法在此处粘贴此函数，因为它太长了，但是它会分配包括插入和更新在内的所有操作！

note: I can not paste this function here, because it's to long, but it does allot of operations including insert and update!

推荐答案

无论调用语言如何，都不能在SELECT语句中调用执行DML的函数.

A function that does DML cannot be called in a SELECT statement regardless of the calling language.

如果要执行DML并返回值，则使用OUT参数而不是使用函数来创建存储过程将更加有意义.

If you want to do DML and return a value, it would make much more sense to create a stored procedure with an OUT parameter rather than using a function. So it would make much more sense to

CREATE OR REPLACE PROCEDURE proc_name( p_1    IN NUMBER,
                                       p_2    IN NUMBER,
                                       p_ret OUT VARCHAR2 )
AS
BEGIN
  p_ret := pkg_tools.replace.site( p_1, p_2 );
END;

，然后从PHP调用该存储过程

$sql = 'BEGIN proc_return( :p_1, :p_2, :p_ret ); END;';

如果您不想这样做，我想您也可以做类似的事情(改编自

If you don't want to do that, my guess is that you could do something like this as well (adapted from one of the scripts on page 164 of the Underground PHP and Oracle Manual)

<?php
$c = oci_connect('hr', 'hrpwd', 'localhost/XE');
$s = oci_parse($c, "begin :ret :=pkg_tools.replace_site(1121,3343); end;");
oci_bind_by_name($s, ':ret', $r, 200);
oci_execute($s);
echo "Result is: ".$r;
?>

这篇关于调用从PHP进行插入/更新的Oracle函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！