如何在 ORACLE 中使用 SQL UPDATE 命令将 BLOB 数据附加/连接到 BLOB 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6570868/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I append/concatenate BLOB data to a BLOB column using SQL UPDATE command in ORACLE
提问by botchedDevil
I need to append data to my BLOB field, how can I do this using an UPDATE command? What i am asking is; is it possible to concatenate blob data so that i can eventually set it to a field like UPDATE BLOB_table SET BLOB_field = BLOB_field + BLOB_data
我需要将数据附加到我的 BLOB 字段，如何使用 UPDATE 命令执行此操作？我要问的是；是否可以连接 blob 数据，以便我最终可以将其设置为像 UPDATE BLOB_table SET BLOB_field = BLOB_field + BLOB_data 这样的字段
I tried using DBMS_LOB.APPEND but it does not return a value; so i created a function which gives me an error of "invalid LOB locator specified"
我尝试使用 DBMS_LOB.APPEND 但它没有返回值；所以我创建了一个函数，它给了我“指定的 LOB 定位器无效”的错误
CREATE OR REPLACE FUNCTION MAKESS.CONCAT_BLOB(A in BLOB,B in BLOB) RETURN BLOB IS
C BLOB;
BEGIN
DBMS_LOB.APPEND(c,A);
DBMS_LOB.APPEND(c,B);
RETURN c;
END;
/
回答by Vincent Malgrat
You need to create a temporary blob with DBMS_LOB.createtemporary:
您需要使用以下命令创建一个临时 blob DBMS_LOB.createtemporary：
SQL> CREATE OR REPLACE FUNCTION CONCAT_BLOB(A IN BLOB, B IN BLOB) RETURN BLOB IS
2 C BLOB;
3 BEGIN
4 dbms_lob.createtemporary(c, TRUE);
5 DBMS_LOB.APPEND(c, A);
6 DBMS_LOB.APPEND(c, B);
7 RETURN c;
8 END;
9 /
Function created
Then you should be able to use it in an update statement:
然后你应该能够在更新语句中使用它：
SQL> CREATE TABLE t (a BLOB, b BLOB, c BLOB);
Table created
SQL> INSERT INTO t VALUES
2 (utl_raw.cast_to_raw('aaa'), utl_raw.cast_to_raw('bbb'), NULL);
1 row inserted
SQL> UPDATE t SET c=CONCAT_BLOB(a,b);
1 row updated
SQL> SELECT utl_raw.cast_to_varchar2(a),
2 utl_raw.cast_to_varchar2(b),
3 utl_raw.cast_to_varchar2(c)
4 FROM t;
UTL_RAW.CAST_TO_VARCHAR2(A UTL_RAW.CAST_TO_VARCHAR2(B UTL_RAW.CAST_TO_VARCHAR2(C
-------------------------- -------------------------- --------------------------
aaa bbb aaabbb
回答by Vadzim
With help of PL/SQL blob can be updated in place with no need for custom function at all:
在 PL/SQL 的帮助下，blob 可以就地更新，根本不需要自定义函数：
BEGIN
FOR c IN (select a, b from t where a is not null for update) LOOP
DBMS_LOB.APPEND(c.a, c.b);
END LOOP;
END;
/
