我正在尝试使用ORACLE PL/SQL变量,但我在使用:columnType变量时遇到问题
我的查询是
$doneStatus = 1;
$cuttingUpdateParse = oci_parse($conn, "UPDATE FABRICATION SET :columnType = $doneStatus
WHERE HEAD_MARK = :headmarkToUpdate AND ID = :idToUpdate");
echo 'PASSED VARIABLE COLUMN TYPE: '.$_POST["columnType"].'<br/>';
echo 'PASSED VARIABLE HEADMARK: '.$_POST["headmark"].'<br/>';
echo 'PASSED VARIABLE ID: '.$_POST["headmark_id"].'<br/>';
oci_bind_by_name($cuttingUpdateParse, ":headmarkToUpdate", $_POST["headmark"]);
oci_bind_by_name($cuttingUpdateParse, ":idToUpdate", $_POST["headmark_id"]);
oci_bind_by_name($cuttingUpdateParse, ":columnType", $_POST["columnType"]);
传递的变量列类型:剪切已通过变量标题标记:TEST1传递的变量ID:2
警告:oci_execute():ORA-01747:第38行C:''examplep''htdocs''WeltesInformationCenter''update_bar''process_class.php中的user.table.column、table.column或列规范无效
错误发生在我在sql查询中输入:columnType时。因此,有人对如何动态更改:columnType提出了建议吗?
您试图在运行时使用绑定变量来指定列名-Oracle禁止这样做。它需要明确地知道对象名称。只需使用连接而不是绑定:
SQL> declare
2 column_name varchar2(100) := 'VALUE#';
3 id int := 2;
4 val varchar2(10) := 'XXX';
5 begin
6 execute immediate
7 'update t set :column_name = :column_value where id = :id'
8 using column_name, val, id;
9 end;
10 /
declare
*
error in line 1:
ORA-01747: invalid user.table.column, table.column, or column specification
ORA-06512: in line 6
SQL> declare
2 column_name varchar2(100) := 'VALUE#';
3 id int := 2;
4 val varchar2(10) := 'XXX';
5 begin
6 execute immediate
7 'update t set '||column_name||' = :column_value where id = :id'
8 using val, id;
9 end;
10 /
PL/SQL procedure completed.