数据库clob类型sql导入

妖狐艹你老母 2022-05-11 19:03 254阅读 0赞

oracle 数据库clob sql

1.声明变量法，这种方法用DbVisualizer 9.5导入会报错，用Navicat for Oracle和plsql developer导入没问题。

DECLARE  
a CLOB :='jjjjjjj';

b CLOB := 'kkkkkk';  
begin  
INSERT INTO "TBL\_RE\_aaaa\_DEF" (aaa, aaa, aaa, aaaa, aaa, aaa, aaa) VALUES ('dd5f0cf4113441e68065c8ace8b08258', '1', '1', '2', a,'admin','admin');

INSERT INTO "TBL\_RE\_aaaa\_DEF" (aaaa, aaaa, aaaa, aaaa, aaaa, aaaa, aaaa) VALUES ('2d5890bbcdc14048a62b6b2c862f6484', '1', '1', '1', b, 'admin','admin');

INSERT INTO "TBL\_RE\_aaaa\_DEF" (aaaa, aaaa, aaaa, aaaa, aaaa, aaaa, aaaa) VALUES ('483b6fc1f46741e78aaf7fe1751addb6', '1', '0', '0', 'aaaa','admin', 'admin');

INSERT INTO "TBL\_RE\_aaaa\_DEF" (aaaa, aaaa, aaaa, aaaa, aaaa, aaaa, aaaa) VALUES ('c22e7ebd16574d39b70d2001a3dbba3c', '1', '1', '3', 'aaaa','admin','admin');

INSERT INTO "TBL\_RE\_aaaa\_DEF" (aaaa, aaaa, aaaa, aaaa, aaaa, aaaa, aaaa) VALUES ('45707bab7a174bcd85e55d8b177faa70', '1', '1', '4', 'aaaaa','admin','admin');

commit;  
end;

2.1.字符串拼接法，这种方法用DbVisualizer 9.5、Navicat for Oracle和plsql developer导入没问题。  
INSERT INTO "TBL\_RE\_aaaa\_DEF" (aaaa, aaaa, aaaa, aaaa, aaaa, aaaa, aaaa) VALUES (sys\_guid(), '1', '1', '4', 'aaaa','admin','admin');

update usertable set name='abcdefg' where userId = 'aaa';  
update usertable set name = name||'hijklmn' where userId = 'aaa';

if exists (select 1 from "TBL\_RE\_aaaa\_DEF" where aaaa = 'dd5f0cf4113441e68065c8ace8b08258')

begin  
       update "TBL\_RE\_aaaa\_DEF"  
       set aaaa = 'aaa,'  
       where aaaa = 'dd5f0cf4113441e68065c8ace8b08258';  
         
       update "TBL\_RE\_aaaa\_DEF"   
       set aaaa = aaaa||'aaaa,';  
         
       update "TBL\_RE\_aaaa\_DEF"   
       set aaaa = aaaa||'aaaa,'   
       where aaaa = 'dd5f0cf4113441e68065c8ace8b08258';  
         
       update "TBL\_RE\_aaaa\_DEF"   
       set aaaa = aaaa||'aaaa,'   
       where aaaa = 'dd5f0cf4113441e68065c8ace8b08258';  
         
       update "TBL\_RE\_aaaa\_DEF"   
       set aaaa = aaaa||'aaaa'   
       where aaaa = 'dd5f0cf4113441e68065c8ace8b08258';    
    end  
      
else  
    begin  
       INSERT INTO "TBL\_RE\_aaaa\_DEF" (aaaa, aaaa, aaaa, aaaa, aaaa, aaaa, aaaa) VALUES ('dd5f0cf4113441e68065c8ace8b08258', '1', '1', '2', 'a','admin','admin');  
    end  
      
      
      
    INSERT INTO "TBL\_RE\_aaaa\_DEF" (aaaa, aaaa, aaaa, aaaa, aaaa, aaaa, aaaa) VALUES ('dd5f0cf4113441e68065c8ace8b08258', '1', '1', '2', 'a','admin','admin');  
      
       update "TBL\_RE\_aaaa\_DEF"  
       set aaaa = 'aaaaa,'  
       where aaaa = 'dd5f0cf4113441e68065c8ace8b08258';  
         
       update "TBL\_RE\_aaaa\_DEF"   
       set aaaa = aaaa||'aaaa,';  
         
       update "TBL\_RE\_aaaa\_DEF"   
       set aaaa = aaaa||'aaaa,'   
       where aaaa = 'dd5f0cf4113441e68065c8ace8b08258';  
         
       update "TBL\_RE\_aaaa\_DEF"   
       set aaaa = aaaa||'aaaa,';  
         
       update "TBL\_RE\_aaaa\_DEF"   
       set aaaa = aaaa||'aaaa';

参考：

1) oracle ORA-01704问题（clob字段insert报错）

[https://blog.csdn.net/Vi\_error/article/details/77873912?utm\_source=blogxgwz7][https_blog.csdn.net_Vi_error_article_details_77873912_utm_source_blogxgwz7]

[https_blog.csdn.net_Vi_error_article_details_77873912_utm_source_blogxgwz7]: https://blog.csdn.net/Vi_error/article/details/77873912?utm_source=blogxgwz7