更新ORACLE表并返回更新行的ID(主键)给PHP。

huangapple go评论61阅读模式
英文:

Update ORACLE table and return IDs (PK) of updated rows to PHP

问题

我正在尝试使用UPDATE查询对Oracle(12g)数据库执行,并使用“RETURNING col INTO :var”或“RETURNING col BULK COLLECT INTO :var”子句,并将其用于获取已更新的所有ID(PK列)列表,存入PHP变量。

$db = oci_connect($db_user, $db_password, $db_connection_string);
$bids = '1,2';
$sql = "UPDATE sch.table_name
        SET processing_status = '050',
                printed_by = :username,
                printed_date = current_date,
                change_date = sysdate,
                change_by = :username
            WHERE batch_id IN (" . $bids . ")
            RETURNING batch_id BULK COLLECT INTO :updated_batch_ids";
$stid = oci_parse($db, $sql);
oci_bind_by_name($stid, ':username', $username, -1, SQLT_CHR);
oci_bind_array_by_name($stid, ':updated_batch_ids', $updated_batches, 1000, 50);

oci_execute($stid, OCI_COMMIT_ON_SUCCESS);
oci_free_statement($stid);
//期望在$updated_batches中得到数据,但它是空的

帮助将不胜感激。

英文:

I am trying to use UPDATE query towards Oracle(12g) database and use "RETURNING col INTO :var" or "RETURNING col BULK COLLECT INTO :var" clasule and use it to get list of all IDs (PK column) which were updated, into PHP variable.
I was able to get one, the last, but not a list.

I've already played and researched with using oci_bind_array_by_name but received error "Warning: oci_execute(): ORA-01484: arrays can only be bound to PL/SQL statements" and received array in PHP was empty.

Example (stripped of error handling) which throws above error:

$db = oci_connect($db_user,$db_password,$db_connection_string);
$bids = '1,2';
$sql = "UPDATE sch.table_name
        SET processing_status = '050',
                printed_by= :username,
                printed_date=current_date,
                change_date = sysdate,
                change_by = :username
            WHERE batch_id in (" . $bids . ")
            RETURNING batch_id  BULK COLLECT INTO :updated_batch_ids";
$stid = oci_parse($db, $sql);
oci_bind_by_name($stid,':username', $username, -1, SQLT_CHR);
oci_bind_array_by_name($stid,':updated_batch_ids',$updated_batches,1000,50);

oci_execute($stid,OCI_COMMIT_ON_SUCCESS);
oci_free_statement($stid);

//expecting data in $updated_batches, but its empty

I've also try to use oci_fetch_array or oci_fetch_all (statement), but that resulted in error: "oci_fetch_all(): ORA-24374: define not done before fetch or execute and fetch".

Help is greatly appreciated.
One last note out of my frustration: This would be much easier/available/documented/solved with Postgre, but have to use Oracle.

Research and tries/tests oci_fetch..., oci_bind_array_by_name, oci_new_collection did not gain desirable results.

I would like DB to return into PHP variable list or array of IDs (PK - batch_ids) which were really updated by query.

答案1

得分: 0

因此,在进一步研究后,我得出了这个解决方案。它将一个列以索引方式(非关联)返回到PHP数组中,但能够完成任务。我将向任何能够找出如何将多列、不同类型(例如数字和varchar)的数据以关联数组形式存储在PHP中的人授予点赞。

解决方案是:

  1. 修改查询以创建正确类型的表类型
  2. 创建该表类型的变量并将数据传递给该变量
  3. 最终将该数据提供给PHP中的绑定变量。

OCI的另一个问题是要设置绑定数组的大小(在下面的代码中为1000),这对我来说有效,但对于所有应用程序可能不适用。

在下面的代码中,我通过连接构建查询,因为我的环境中($bids的来源)是安全的,并且在SQL插入之前经过检查。此外,代码中已剥离错误处理。

查询构造:

$sql = "DECLARE
    --声明表类型
	TYPE batch_id_array IS TABLE OF myschema.mytable.batch_id%TYPE INDEX BY BINARY_INTEGER;
	l_batch_ids batch_id_array; --用于返回的变量
BEGIN 
	UPDATE myschema.mytabe
		SET processing_status = '050',
			printed_by= :username,
			printed_date=current_date,
			WHERE batch_id in " . $bids  . "
		RETURNING
			batch_id
		BULK COLLECT INTO l_batch_ids;
	:batch_ids := l_batch_ids; --设置绑定变量
END;";

连接、解析、绑定、执行和释放:

$stid = oci_parse($db, $sql);
oci_bind_by_name($stid,'username', $username, -1, SQLT_CHR);
#绑定数组 - 输出变量
oci_bind_array_by_name($stid, ':batch_ids', $batch_ids, 1000,-1, SQLT_INT);
#执行并释放语句
oci_execute($stid,OCI_COMMIT_ON_SUCCESS);
oci_free_statement($stid);

# $batch_bids现在是包含已更新的batch_ids的整数索引数组
var_dump($batch_bids);
英文:

So, after some more digging I came up with this. It returns one column, indexed (not associative) array into PHP, but does the job. I will grant upvote to anybody who will figure out how to get more than one column, different types (e.g. number and varchar) into associative array in PHP.

Solution is to

  1. modify query to create table type of correct type
  2. create variable of that table type and feed data into that variable
  3. Ultimately provide that data to binded variable in PHP.

One more problem is OCI's requirement to set size of binded array (1000 in below code) - works for me but may not for all applications.

In below code I construct query by concatenation, as in my environment (source of $bids) is secure and SQL inception checked before. Also the code is stripped of error handling.

Query construction:

$sql = "DECLARE
    --declare table type
	TYPE batch_id_array IS TABLE OF myschema.mytable.batch_id%TYPE INDEX BY BINARY_INTEGER;
	l_batch_ids batch_id_array; --variable used to RETURN into
BEGIN 
	UPDATE myschema.mytabe
		SET processing_status = '050',
			printed_by= :username,
			printed_date=current_date,
			WHERE batch_id in " . $bids  . "
		RETURNING
			batch_id
		BULK COLLECT INTO l_batch_ids;
	:batch_ids := l_batch_ids; --set binded variable
END;";

Connect, Parse, Bind, Execute and Free:

$stid = oci_parse($db, $sql);
oci_bind_by_name($stid,':username', $username, -1, SQLT_CHR);
#bind array - output variable
oci_bind_array_by_name($stid, ':batch_ids', $batch_ids, 1000,-1, SQLT_INT);
#execute and free statement
oci_execute($stid,OCI_COMMIT_ON_SUCCESS);
oci_free_statement($stid);

# $batch_bids is now indexed array of integers with updated batch_ids
var_dump($batch_bids);

huangapple
  • 本文由 发表于 2023年4月19日 18:38:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/76053547.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定