从long到varchar2到clob。和sql该怎么保存clob

羊羊队长
已于 2024-02-29 13:54:15 修改
阅读量336
点赞数
分类专栏： Orcale 文章标签： sql oracle
于 2022-08-22 11:43:09 首次发布
本文链接：https://blog.csdn.net/Noob4321/article/details/126462809
版权
Orcale 专栏收录该内容
2 篇文章 0 订阅
订阅专栏
下面展示一些 内联代码片。
--  把long类型的数据换成varchar2(2000)的数据

-- 大致的意思可以理解为我创建了一个临时字段v1，我把我表中字段数据（long类型）的字段查出来放到c_Select
-- 然后把c_Select放入v1中
-- 最后更新需要复制的表的字段PARAMETER_cs
-- 提交事物 关闭游标
-- remark：cursor c_Select is  是orcale创建游标的写法
declare
   v1 varchar2(4000);
   CURSOR c_Select IS select PARAMETER_TMP from error_http_bus;
begin
   Open c_Select;
   LOOP
      Fetch c_Select into v1;
      Exit when c_Select%NOTFOUND;
      update error_http_bus set PARAMETER_cs = v1;
   END LOOP;
   commit;
   Close c_Select;
END;

--- 把复制的varchar2(2000)的数据复制到clob类型中去


update error_http_bus set PARAMETER=trim(PARAMETER_cs);




--  删除没有用的表，如果clob字段的名字不是parameter的话就用最后一步改名字（前提是删除没用的），如果是了就不用改
alter table error_http_bus drop column PARAMETER_TMP;


alter table error_http_bus drop column PARAMETER_cs;


alter table error_http_bus  rename column PARAMETER to PARAMETER_TMP;






--- 代码中需要体现的

declare
    content clob;
begin
  content := '{"bisred":"N","iinvtype":"0","itm3BalancedetList":[{"nnum":"5.000","ntaxrate":"13.0","nthapptaxmny":"425.00","vbdef1":"1090510030000000000","vinvspec":"普联（TP-LINK） TL-SF1008D","vopencontent":"TP-LINK 8口百兆交换机 监控网络网线分线器 分流器 金属机身 T"},{"nnum":"2.000","ntaxrate":"13.0","nthapptaxmny":"157.68","vbdef1":"1080419990000000000","vinvspec":"固乡 GX-SCLB-01","vopencontent":"固乡【150秒录音喇叭+可插U盘内存卡】充电喇叭电池 大功率"},{"nnum":"1.000","ntaxrate":"13.0","nthapptaxmny":"517.00","vbdef1":"1050201040000000000","vinvspec":"锐达星（REDSING） 文件","vopencontent":"锐达星文件柜办公柜钢制铁皮柜资料柜档案柜储物柜大器械"},{"nnum":"15.000","ntaxrate":"13.0","nthapptaxmny":"144.90","vbdef1":"1070222020000000000","vinvspec":"汰渍 洗衣皂","vopencontent":"汰渍 Tide 全效洁净洗衣皂（柠檬清香）126G*4 超值4块装 透明"},{"nnum":"5.000","ntaxrate":"13.0","nthapptaxmny":"69.05","vbdef1":"1070222020000000000","vinvspec":"汰渍 洗衣粉","vopencontent":"汰渍 Tide 净白去渍洗衣粉1.55KG袋装 全面洁净 强力去油渍污渍"},{"nnum":"4.000","ntaxrate":"13.0","nthapptaxmny":"118.04","vbdef1":"1060512040000000000","vinvspec":"滴畅 滴畅 28cm基础款胶","vopencontent":"滴畅 对折胶棉拖把 吸水海绵拖布 挤水免手洗懒人墩布地拖"},{"nnum":"3.000","ntaxrate":"13.0","nthapptaxmny":"214.02","vbdef1":"1060105040000000000","vinvspec":"心相印（Mind Act Upon Mind","vopencontent":"心相印卷纸 心柔系列 3层/4层180g 27卷卫生纸巾（冲水易降解 "},{"nnum":"5.000","ntaxrate":"13.0","nthapptaxmny":"110.25","vbdef1":"1090603990000000000","vinvspec":"南纵（NANZONG） 其他","vopencontent":"南孚 电池 9V"},{"nnum":"10.000","ntaxrate":"13.0","nthapptaxmny":"198.30","vbdef1":"1090409990000000000","vinvspec":"山泽（SAMZHE） SZW-1080","vopencontent":"山泽 (SAMZHE) SZW-1080 超五类网线蓝色8米"},{"nnum":"1.000","ntaxrate":"13.0","nthapptaxmny":"369.00","vbdef1":"1090511040000000000","vinvspec":"三星（SAMSUNG） MZ-N6E250B","vopencontent":"三星（SAMSUNG）250GB SSD固态硬盘 M.2接口(SATA总线) 860 EVO（MZ-N6E2"},{"nnum":"1.000","ntaxrate":"13.0","nthapptaxmny":"78.01","vbdef1":"1090413010100000000","vinvspec":"南孚 LR6 AA","vopencontent":"南孚(NANFU)5号电池40粒 五号碱性 聚能环3代 适用玩具血压计血"},{"nnum":"1.000","ntaxrate":"13.0","nthapptaxmny":"64.80","vbdef1":"1060105040000000000","vinvspec":"清风 抽纸","vopencontent":"清风盒装抽纸双层200抽3提9盒长抽纸巾硬盒抽面巾纸原木纯品"},{"nnum":"10.000","ntaxrate":"13.0","nthapptaxmny":"34.00","vbdef1":"1090512990000000000","vinvspec":"吉选（GESOBYTE） S240","vopencontent":"吉选（GESOBYTE）S240鼠标垫小号 办公游戏鼠标垫 笔记本电脑办"},{"nnum":"2.000","ntaxrate":"13.0","nthapptaxmny":"137.32","vbdef1":"1080115010000000000","vinvspec":"天喜（TIANXI） 保温壶","vopencontent":"天喜（TIANXI）保温壶1.9L 暖壶玻璃内胆家用保温水壶大容量保"},{"nnum":"1.000","ntaxrate":"13.0","nthapptaxmny":"1950.00","vbdef1":"1090131040000000000","vinvspec":"空调机型:挂机 空调型","vopencontent":"美的空调 KFR-35GW/BP3DN8Y-DH400(3)"},{"nnum":"2.000","ntaxrate":"13.0","nthapptaxmny":"185.40","vbdef1":"1060402010200000000","vinvspec":"得力 S656","vopencontent":"得力(deli)0.5mm黑色直液式走珠签字笔学生考试中性笔 12支/盒 6"},{"nnum":"5.000","ntaxrate":"13.0","nthapptaxmny":"33.50","vbdef1":"1060512040000000000","vinvspec":"庄太太 ZTTSDG854","vopencontent":"庄太太【M】橡胶手套牛筋胶皮洗衣厨房劳保乳胶胶手套洗碗"},{"nnum":"6.000","ntaxrate":"13.0","nthapptaxmny":"428.04","vbdef1":"1060105040000000000","vinvspec":"心相印（Mind Act Upon Mind","vopencontent":"心相印卷纸 心柔系列 3层/4层180g 27卷卫生纸巾（冲水易降解 "},{"nnum":"4.000","ntaxrate":"13.0","nthapptaxmny":"381.20","vbdef1":"1060105040000000000","vinvspec":"霏羽 CS006","vopencontent":"霏羽擦手纸150抽20包3折干手纸抹手纸抽取式纸巾卫生间手纸"},{"nnum":"1.000","ntaxrate":"13.0","nthapptaxmny":"189.00","vbdef1":"1090511040000000000","vinvspec":"联想（Lenovo） SL700 SATA3","vopencontent":"联想（Lenovo)  240GB SSD固态硬盘 SATA3.0接口 SL700闪电鲨系列"},{"nnum":"1.000","ntaxrate":"13.0","nthapptaxmny":"119.00","vbdef1":"1090510030000000000","vinvspec":"普联（TP-LINK） TL-WDR5620","vopencontent":" TP-LINK 易展mesh分布式路由器 AC1200智能5G双频 无线家用穿墙 "},{"nnum":"1.000","ntaxrate":"13.0","nthapptaxmny":"48.60","vbdef1":"1060401990000000000","vinvspec":"晨光（M&G） 办公文具","vopencontent":"晨光M＆G 实力派单强力文件夹 A4资料夹 弹力夹 蓝 ADM95091 5个"},{"nnum":"2.000","ntaxrate":"13.0","nthapptaxmny":"287.98","vbdef1":"1060401990000000000","vinvspec":"得力（deli） 5683","vopencontent":"得力文件盒A4档案盒文件资料盒塑料大容量办公文具 5683 蓝色"},{"nnum":"1.000","ntaxrate":"13.0","nthapptaxmny":"79.50","vbdef1":"1090134040000000000","vinvspec":"优易固（YOUYIGU） 透明6","vopencontent":"优易固（YOUYIGU）高品质透明封箱胶带胶带打包宽胶带透明60mm"},{"nnum":"2.000","ntaxrate":"13.0","nthapptaxmny":"186.00","vbdef1":"1060105040000000000","vinvspec":"霏羽 CS006","vopencontent":"霏羽擦手纸150抽20包3折干手纸抹手纸抽取式纸巾卫生间手纸"},{"nnum":"4.000","ntaxrate":"13.0","nthapptaxmny":"107.56","vbdef1":"1060105040000000000","vinvspec":"心相印（Mind Act Upon Mind","vopencontent":"心相印卷纸 心柔系列卫生纸巾3层/4层180g*10卷筒纸厕纸（新旧"},{"nnum":"20.000","ntaxrate":"13.0","nthapptaxmny":"76.00","vbdef1":"1080419990000000000","vinvspec":"米奇特工（Agents mickey","vopencontent":"米奇特工（Agents mickey）横开挂锁 抽屉锁 仿铜锁 门锁办公室"},{"nnum":"1.000","ntaxrate":"13.0","nthapptaxmny":"42.00","vbdef1":"1090421010000000000","vinvspec":"支架品牌:国产;空调配","vopencontent":"1-1.5匹空调支架"},{"nnum":"1.000","ntaxrate":"13.0","nthapptaxmny":"136.00","vbdef1":"1090421010000000000","vinvspec":"空调安装:拆机","vopencontent":"1-1.5匹空调拆机"}],"pk_customer":"47327","pk_supplier":"A01012001","vcstmaddresphone":"杭州市天目山路358号","vcstmbankaccount":"建行杭州宝石支行","vcstmtaxnumber":"91330000142911125H","vdef13":"FPSQ220818011","vdef15":"33001616135056008666","vdef4":"0571-85027306","vdef7":"748573988@qq.com","vdef8":"15068702530","vmemo":"无"}';
  insert into long_test(field1) values (content);
end;