英文:
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中的人授予点赞。
解决方案是:
- 修改查询以创建正确类型的表类型
- 创建该表类型的变量并将数据传递给该变量
- 最终将该数据提供给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
- modify query to create table type of correct type
- create variable of that table type and feed data into that variable
- 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);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论