Oracle正在比较同一行中的块

Question

我有一个包含一些列和两个CLOBS的表。

在某些情况下，两个CLOBS都包含相同的精确值，而在其他情况下，它们包含不同的值。或者一个或两个CLOBS可以是空的或NULl。

我创建了一个函数，我想调用它来比较两个CLOBS，以确定值是"相同"还是不同。

CREATE or REPLACE FUNCTION HASH_SHA512 (
psINPUT IN VARCHAR2
) RETURN VARCHAR2 AS
rHash RAW (512);
BEGIN
rHash := DBMS_CRYPTO.HASH (TO_CLOB (psINPUT), 
dbms_crypto.HASH_SH512);
RETURN (LOWER (RAWTOHEX (rHash)));
END HASH_SHA512;
/

CREATE table table_x(
seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
val NUMBER, 
clob1 CLOB,
clob2 CLOB);

insert into table_x (val, clob1, clob2) values (1,'aaaaaaaaaa','aaaaaaaaaa');
insert into table_x (val, clob1, clob2) values (1,'aaaaa','aaaaaaaaaa');
insert into table_x (val, clob1, clob2) values (2,'Xaaaaaaaaa','aaaaaaaaaa');
Expected output 
SEQ_NUM    VAL    CLOB1    CLOB2
1    1    aaaaaaaaaa    aaaaaaaaaa  SAME
2    1    aaaaa    aaaaaaaaaa DIFFERENT 
3    2     Xaaaaaaaaa    aaaaaaaaaa DIFFERENT 

根据上面查询的输出，如果SHA512输出显示的差异相同，我想在下面的新表中插入1行。如果输出不同，我想在下表中插入2行。

CREATE table table_z(
seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
val NUMBER, 
hash_val VARCHAR2(1000) not NULL PRIMARY KEY, 
clob_val CLOB);

Answer 1

编写自己的函数来比较两个LOB没有多大意义，因为Oracle在DBMS_LOB包中已经有了COMPARE函数。它可以很容易地在您的查询中使用，如下所示：

SELECT x.*,
CASE DBMS_LOB.compare (x.clob1, x.clob2) WHEN 0 THEN 'SAME' ELSE 'DIFFERENT' END    AS comparison
FROM table_x x;

SEQ_NUM    VAL         CLOB1         CLOB2    COMPARISON
__________ ______ _____________ _____________ _____________
1      1 aaaaaaaaaa    aaaaaaaaaa    SAME
2      1 aaaaa         aaaaaaaaaa    DIFFERENT
3      2 Xaaaaaaaaa    aaaaaaaaaa    DIFFERENT

要在table_z中插入不同的CLOB，可以使用如下SQL语句：

INSERT INTO table_z (val, hash_val, clob_val)
SELECT x1.val, DBMS_CRYPTO.HASH (x1.clob1, 6                          /*DBMS_CRYPTO.HASH_SH512*/
), x1.clob1
FROM table_x x1
UNION ALL
SELECT x2.val, DBMS_CRYPTO.HASH (x2.clob2, 6                          /*DBMS_CRYPTO.HASH_SH512*/
), x2.clob2
FROM table_x x2
WHERE DBMS_LOB.compare (x2.clob1, x2.clob2) <> 0;

目前，您在table_z上定义的PRIMARY KEY将阻止您提供的样本记录插入，因为同一CLOB出现在多行上。您可以删除该主键，也可以向主键添加其他列以允许插入数据。

Answer 2

您可以在"table_x"；。

https://docs.oracle.com/cd/B13789_01/appdev.101/b10795/adfns_tr.htm

→在该触发器的PL SQL代码中，您可以区分以下情况：；"相同"；以及"；不同"；。

这个触发器应该看起来像：

CREATE OR REPLACE TRIGGER compare_table_x
BEFORE INSERT ON table_x
FOR EACH ROW
DECLARE
compare table_x.compare;
BEGIN
IF compare = 'SAME' THEN
INSERT ...
ELSE
INSERT ... 
INSERT ...
END IF;
END;