动态SQL现实一个表中求多列的和-白红宇

动态SQL现实一个表中求多列的和

阅读量：5128 次

发布时间：2019-06-13

本文共 22226 字，大约阅读时间需要 74 分钟。

1、建表（注：96DATA_VALUE字段分别为一天每15分钟的监测数据，避免一天一个用户产生96条数据，可以减少表的数据量，因为全国用电客户巨大）

-- Create tablecreate table EESMP.R_H_CURVE_E(  MS_ID          NUMBER(9) not null,  DATA_ITEM_CODE VARCHAR2(16) not null,  DATA_DATE      VARCHAR2(8) not null,  RECORD_NO      NUMBER(5) not null,  CURVE_DENSITY  VARCHAR2(8),  DATA_VALUE1    NUMBER(12,4),  DATA_VALUE2    NUMBER(12,4),  DATA_VALUE3    NUMBER(12,4),  DATA_VALUE4    NUMBER(12,4),  DATA_VALUE5    NUMBER(12,4),  DATA_VALUE6    NUMBER(12,4),  DATA_VALUE7    NUMBER(12,4),  DATA_VALUE8    NUMBER(12,4),  DATA_VALUE9    NUMBER(12,4),  DATA_VALUE10   NUMBER(12,4),  DATA_VALUE11   NUMBER(12,4),  DATA_VALUE12   NUMBER(12,4),  DATA_VALUE13   NUMBER(12,4),  DATA_VALUE14   NUMBER(12,4),  DATA_VALUE15   NUMBER(12,4),  DATA_VALUE16   NUMBER(12,4),  DATA_VALUE17   NUMBER(12,4),  DATA_VALUE18   NUMBER(12,4),  DATA_VALUE19   NUMBER(12,4),  DATA_VALUE20   NUMBER(12,4),  DATA_VALUE21   NUMBER(12,4),  DATA_VALUE22   NUMBER(12,4),  DATA_VALUE23   NUMBER(12,4),  DATA_VALUE24   NUMBER(12,4),  DATA_VALUE25   NUMBER(12,4),  DATA_VALUE26   NUMBER(12,4),  DATA_VALUE27   NUMBER(12,4),  DATA_VALUE28   NUMBER(12,4),  DATA_VALUE29   NUMBER(12,4),  DATA_VALUE30   NUMBER(12,4),  DATA_VALUE31   NUMBER(12,4),  DATA_VALUE32   NUMBER(12,4),  DATA_VALUE33   NUMBER(12,4),  DATA_VALUE34   NUMBER(12,4),  DATA_VALUE35   NUMBER(12,4),  DATA_VALUE36   NUMBER(12,4),  DATA_VALUE37   NUMBER(12,4),  DATA_VALUE38   NUMBER(12,4),  DATA_VALUE39   NUMBER(12,4),  DATA_VALUE40   NUMBER(12,4),  DATA_VALUE41   NUMBER(12,4),  DATA_VALUE42   NUMBER(12,4),  DATA_VALUE43   NUMBER(12,4),  DATA_VALUE44   NUMBER(12,4),  DATA_VALUE45   NUMBER(12,4),  DATA_VALUE46   NUMBER(12,4),  DATA_VALUE47   NUMBER(12,4),  DATA_VALUE48   NUMBER(12,4),  DATA_VALUE49   NUMBER(12,4),  DATA_VALUE50   NUMBER(12,4),  DATA_VALUE51   NUMBER(12,4),  DATA_VALUE52   NUMBER(12,4),  DATA_VALUE53   NUMBER(12,4),  DATA_VALUE54   NUMBER(12,4),  DATA_VALUE55   NUMBER(12,4),  DATA_VALUE56   NUMBER(12,4),  DATA_VALUE57   NUMBER(12,4),  DATA_VALUE58   NUMBER(12,4),  DATA_VALUE59   NUMBER(12,4),  DATA_VALUE60   NUMBER(12,4),  DATA_VALUE61   NUMBER(12,4),  DATA_VALUE62   NUMBER(12,4),  DATA_VALUE63   NUMBER(12,4),  DATA_VALUE64   NUMBER(12,4),  DATA_VALUE65   NUMBER(12,4),  DATA_VALUE66   NUMBER(12,4),  DATA_VALUE67   NUMBER(12,4),  DATA_VALUE68   NUMBER(12,4),  DATA_VALUE69   NUMBER(12,4),  DATA_VALUE70   NUMBER(12,4),  DATA_VALUE71   NUMBER(12,4),  DATA_VALUE72   NUMBER(12,4),  DATA_VALUE73   NUMBER(12,4),  DATA_VALUE74   NUMBER(12,4),  DATA_VALUE75   NUMBER(12,4),  DATA_VALUE76   NUMBER(12,4),  DATA_VALUE77   NUMBER(12,4),  DATA_VALUE78   NUMBER(12,4),  DATA_VALUE79   NUMBER(12,4),  DATA_VALUE80   NUMBER(12,4),  DATA_VALUE81   NUMBER(12,4),  DATA_VALUE82   NUMBER(12,4),  DATA_VALUE83   NUMBER(12,4),  DATA_VALUE84   NUMBER(12,4),  DATA_VALUE85   NUMBER(12,4),  DATA_VALUE86   NUMBER(12,4),  DATA_VALUE87   NUMBER(12,4),  DATA_VALUE88   NUMBER(12,4),  DATA_VALUE89   NUMBER(12,4),  DATA_VALUE90   NUMBER(12,4),  DATA_VALUE91   NUMBER(12,4),  DATA_VALUE92   NUMBER(12,4),  DATA_VALUE93   NUMBER(12,4),  DATA_VALUE94   NUMBER(12,4),  DATA_VALUE95   NUMBER(12,4),  DATA_VALUE96   NUMBER(12,4),  DATA_TYPE      VARCHAR2(8) not null)tablespace DATA_TEST  pctfree 10  initrans 1  maxtrans 255  storage  (    initial 64K    next 1M    minextents 1    maxextents unlimited  );-- Add comments to the table comment on table EESMP.R_H_CURVE_E  is '1) 记录历史曲线数据，包括I类数据和II类数据，各数据类型通过数据项代码区分，每天更新，最新数据为昨天数据。2) 数据来源于前置机上传，实时数据域转换，以及其它系统接口数据导入。3) 该实体用于企业用能监测，企业用能分析等。';-- Add comments to the columns comment on column EESMP.R_H_CURVE_E.MS_ID  is '监测点标识';comment on column EESMP.R_H_CURVE_E.DATA_ITEM_CODE  is '数据项代码';comment on column EESMP.R_H_CURVE_E.DATA_DATE  is '数据日期';comment on column EESMP.R_H_CURVE_E.RECORD_NO  is '记录序号,默认为0';comment on column EESMP.R_H_CURVE_E.CURVE_DENSITY  is '曲线采样密度,单位分钟 1分钟,5分钟,10分钟,15分钟,30分钟,60分钟';comment on column EESMP.R_H_CURVE_E.DATA_VALUE1  is '数据值1 异常数据用空值表示';comment on column EESMP.R_H_CURVE_E.DATA_VALUE2  is '数据值2';comment on column EESMP.R_H_CURVE_E.DATA_VALUE3  is '数据值3';comment on column EESMP.R_H_CURVE_E.DATA_VALUE4  is '数据值4';comment on column EESMP.R_H_CURVE_E.DATA_VALUE5  is '数据值5';comment on column EESMP.R_H_CURVE_E.DATA_VALUE6  is '数据值6';comment on column EESMP.R_H_CURVE_E.DATA_VALUE7  is '数据值7';comment on column EESMP.R_H_CURVE_E.DATA_VALUE8  is '数据值8';comment on column EESMP.R_H_CURVE_E.DATA_VALUE9  is '数据值9';comment on column EESMP.R_H_CURVE_E.DATA_VALUE10  is '数据值10';comment on column EESMP.R_H_CURVE_E.DATA_VALUE11  is '数据值11';comment on column EESMP.R_H_CURVE_E.DATA_VALUE12  is '数据值12';comment on column EESMP.R_H_CURVE_E.DATA_VALUE13  is '数据值13';comment on column EESMP.R_H_CURVE_E.DATA_VALUE14  is '数据值14';comment on column EESMP.R_H_CURVE_E.DATA_VALUE15  is '数据值15';comment on column EESMP.R_H_CURVE_E.DATA_VALUE16  is '数据值16';comment on column EESMP.R_H_CURVE_E.DATA_VALUE17  is '数据值17';comment on column EESMP.R_H_CURVE_E.DATA_VALUE18  is '数据值18';comment on column EESMP.R_H_CURVE_E.DATA_VALUE19  is '数据值19';comment on column EESMP.R_H_CURVE_E.DATA_VALUE20  is '数据值20';comment on column EESMP.R_H_CURVE_E.DATA_VALUE21  is '数据值21';comment on column EESMP.R_H_CURVE_E.DATA_VALUE22  is '数据值22';comment on column EESMP.R_H_CURVE_E.DATA_VALUE23  is '数据值23';comment on column EESMP.R_H_CURVE_E.DATA_VALUE24  is '数据值24';comment on column EESMP.R_H_CURVE_E.DATA_VALUE25  is '数据值25';comment on column EESMP.R_H_CURVE_E.DATA_VALUE26  is '数据值26';comment on column EESMP.R_H_CURVE_E.DATA_VALUE27  is '数据值27';comment on column EESMP.R_H_CURVE_E.DATA_VALUE28  is '数据值28';comment on column EESMP.R_H_CURVE_E.DATA_VALUE29  is '数据值29';comment on column EESMP.R_H_CURVE_E.DATA_VALUE30  is '数据值30';comment on column EESMP.R_H_CURVE_E.DATA_VALUE31  is '数据值31';comment on column EESMP.R_H_CURVE_E.DATA_VALUE32  is '数据值32';comment on column EESMP.R_H_CURVE_E.DATA_VALUE33  is '数据值33';comment on column EESMP.R_H_CURVE_E.DATA_VALUE34  is '数据值34';comment on column EESMP.R_H_CURVE_E.DATA_VALUE35  is '数据值35';comment on column EESMP.R_H_CURVE_E.DATA_VALUE36  is '数据值36';comment on column EESMP.R_H_CURVE_E.DATA_VALUE37  is '数据值37';comment on column EESMP.R_H_CURVE_E.DATA_VALUE38  is '数据值38';comment on column EESMP.R_H_CURVE_E.DATA_VALUE39  is '数据值39';comment on column EESMP.R_H_CURVE_E.DATA_VALUE40  is '数据值40';comment on column EESMP.R_H_CURVE_E.DATA_VALUE41  is '数据值41';comment on column EESMP.R_H_CURVE_E.DATA_VALUE42  is '数据值42';comment on column EESMP.R_H_CURVE_E.DATA_VALUE43  is '数据值43';comment on column EESMP.R_H_CURVE_E.DATA_VALUE44  is '数据值44';comment on column EESMP.R_H_CURVE_E.DATA_VALUE45  is '数据值45';comment on column EESMP.R_H_CURVE_E.DATA_VALUE46  is '数据值46';comment on column EESMP.R_H_CURVE_E.DATA_VALUE47  is '数据值47';comment on column EESMP.R_H_CURVE_E.DATA_VALUE48  is '数据值48';comment on column EESMP.R_H_CURVE_E.DATA_VALUE49  is '数据值49';comment on column EESMP.R_H_CURVE_E.DATA_VALUE50  is '数据值50';comment on column EESMP.R_H_CURVE_E.DATA_VALUE51  is '数据值51';comment on column EESMP.R_H_CURVE_E.DATA_VALUE52  is '数据值52';comment on column EESMP.R_H_CURVE_E.DATA_VALUE53  is '数据值53';comment on column EESMP.R_H_CURVE_E.DATA_VALUE54  is '数据值54';comment on column EESMP.R_H_CURVE_E.DATA_VALUE55  is '数据值55';comment on column EESMP.R_H_CURVE_E.DATA_VALUE56  is '数据值56';comment on column EESMP.R_H_CURVE_E.DATA_VALUE57  is '数据值57';comment on column EESMP.R_H_CURVE_E.DATA_VALUE58  is '数据值58';comment on column EESMP.R_H_CURVE_E.DATA_VALUE59  is '数据值59';comment on column EESMP.R_H_CURVE_E.DATA_VALUE60  is '数据值60';comment on column EESMP.R_H_CURVE_E.DATA_VALUE61  is '数据值61';comment on column EESMP.R_H_CURVE_E.DATA_VALUE62  is '数据值62';comment on column EESMP.R_H_CURVE_E.DATA_VALUE63  is '数据值63';comment on column EESMP.R_H_CURVE_E.DATA_VALUE64  is '数据值64';comment on column EESMP.R_H_CURVE_E.DATA_VALUE65  is '数据值65';comment on column EESMP.R_H_CURVE_E.DATA_VALUE66  is '数据值66';comment on column EESMP.R_H_CURVE_E.DATA_VALUE67  is '数据值67';comment on column EESMP.R_H_CURVE_E.DATA_VALUE68  is '数据值68';comment on column EESMP.R_H_CURVE_E.DATA_VALUE69  is '数据值69';comment on column EESMP.R_H_CURVE_E.DATA_VALUE70  is '数据值70';comment on column EESMP.R_H_CURVE_E.DATA_VALUE71  is '数据值71';comment on column EESMP.R_H_CURVE_E.DATA_VALUE72  is '数据值72';comment on column EESMP.R_H_CURVE_E.DATA_VALUE73  is '数据值73';comment on column EESMP.R_H_CURVE_E.DATA_VALUE74  is '数据值74';comment on column EESMP.R_H_CURVE_E.DATA_VALUE75  is '数据值75';comment on column EESMP.R_H_CURVE_E.DATA_VALUE76  is '数据值76';comment on column EESMP.R_H_CURVE_E.DATA_VALUE77  is '数据值77';comment on column EESMP.R_H_CURVE_E.DATA_VALUE78  is '数据值78';comment on column EESMP.R_H_CURVE_E.DATA_VALUE79  is '数据值79';comment on column EESMP.R_H_CURVE_E.DATA_VALUE80  is '数据值80';comment on column EESMP.R_H_CURVE_E.DATA_VALUE81  is '数据值81';comment on column EESMP.R_H_CURVE_E.DATA_VALUE82  is '数据值82';comment on column EESMP.R_H_CURVE_E.DATA_VALUE83  is '数据值83';comment on column EESMP.R_H_CURVE_E.DATA_VALUE84  is '数据值84';comment on column EESMP.R_H_CURVE_E.DATA_VALUE85  is '数据值85';comment on column EESMP.R_H_CURVE_E.DATA_VALUE86  is '数据值86';comment on column EESMP.R_H_CURVE_E.DATA_VALUE87  is '数据值87';comment on column EESMP.R_H_CURVE_E.DATA_VALUE88  is '数据值88';comment on column EESMP.R_H_CURVE_E.DATA_VALUE89  is '数据值89';comment on column EESMP.R_H_CURVE_E.DATA_VALUE90  is '数据值90';comment on column EESMP.R_H_CURVE_E.DATA_VALUE91  is '数据值91';comment on column EESMP.R_H_CURVE_E.DATA_VALUE92  is '数据值92';comment on column EESMP.R_H_CURVE_E.DATA_VALUE93  is '数据值93';comment on column EESMP.R_H_CURVE_E.DATA_VALUE94  is '数据值94';comment on column EESMP.R_H_CURVE_E.DATA_VALUE95  is '数据值95';comment on column EESMP.R_H_CURVE_E.DATA_VALUE96  is '数据值96';comment on column EESMP.R_H_CURVE_E.DATA_TYPE  is '数据类型，区分统一数据项代码的不同曲线数据,引用标准代码曲线数据类型，01-示值，02-能量，99-其他。';-- Create/Recreate primary, unique and foreign key constraints alter table EESMP.R_H_CURVE_E  add constraint PK_R_H_CURVE_1 primary key (MS_ID, DATA_ITEM_CODE, DATA_DATE, RECORD_NO, DATA_TYPE)  using index ;

2、插数据（只插入几条测试数据）

insert into R_H_CURVE_E (MS_ID, DATA_ITEM_CODE, DATA_DATE, RECORD_NO, CURVE_DENSITY, DATA_VALUE1, DATA_VALUE2, DATA_VALUE3, DATA_VALUE4, DATA_VALUE5, DATA_VALUE6, DATA_VALUE7, DATA_VALUE8, DATA_VALUE9, DATA_VALUE10, DATA_VALUE11, DATA_VALUE12, DATA_VALUE13, DATA_VALUE14, DATA_VALUE15, DATA_VALUE16, DATA_VALUE17, DATA_VALUE18, DATA_VALUE19, DATA_VALUE20, DATA_VALUE21, DATA_VALUE22, DATA_VALUE23, DATA_VALUE24, DATA_VALUE25, DATA_VALUE26, DATA_VALUE27, DATA_VALUE28, DATA_VALUE29, DATA_VALUE30, DATA_VALUE31, DATA_VALUE32, DATA_VALUE33, DATA_VALUE34, DATA_VALUE35, DATA_VALUE36, DATA_VALUE37, DATA_VALUE38, DATA_VALUE39, DATA_VALUE40, DATA_VALUE41, DATA_VALUE42, DATA_VALUE43, DATA_VALUE44,DATA_VALUE45, DATA_VALUE46, DATA_VALUE47, DATA_VALUE48, DATA_VALUE49, DATA_VALUE50, DATA_VALUE51, DATA_VALUE52, DATA_VALUE53, DATA_VALUE54, DATA_VALUE55, DATA_VALUE56,DATA_VALUE57, DATA_VALUE58, DATA_VALUE59, DATA_VALUE60, DATA_VALUE61, DATA_VALUE62, DATA_VALUE63, DATA_VALUE64, DATA_VALUE65, DATA_VALUE66, DATA_VALUE67, DATA_VALUE68,DATA_VALUE69, DATA_VALUE70, DATA_VALUE71, DATA_VALUE72, DATA_VALUE73, DATA_VALUE74, DATA_VALUE75, DATA_VALUE76, DATA_VALUE77, DATA_VALUE78, DATA_VALUE79, DATA_VALUE80,DATA_VALUE81, DATA_VALUE82, DATA_VALUE83, DATA_VALUE84, DATA_VALUE85, DATA_VALUE86, DATA_VALUE87, DATA_VALUE88, DATA_VALUE89, DATA_VALUE90, DATA_VALUE91, DATA_VALUE92,DATA_VALUE93, DATA_VALUE94, DATA_VALUE95, DATA_VALUE96, DATA_TYPE)values (1040, '9101', '20130424', 0, '15', 12.4000, 6.8900, 60.4300, 57.6900, 61.4900, 50.9000, 18.7400, 52.6800, 68.3600, 0.7200, 45.7500, 28.7200, 49.3600, 34.6200, 11.9700, 55.1400, 14.5900,69.2400, 43.1900, 27.5500, 1.3800, 4.8000, 21.1500, 54.9500, 16.2000, 48.7700, 57.6800, 10.1300, 65.1500, 33.7000, 2.8600, 55.5900, 30.6200, 30.5100, 28.1200, 4.1600, 55.8500, 43.7200, 32.2500,54.6000, 54.6800, 11.7500, 27.7000, 7.6300, 13.7700, 18.7600, 22.0600, 28.8400, 34.1800, 3.7900, 44.2600, 32.3100, 51.4600, 46.2200, 14.8500, 32.5100, 7.4800, 59.7400, 66.2800, 13.3100, 62.0500,6.6200, 59.1500, 25.2500, 3.0300, 49.3300, 33.3100, 33.3600, 26.9700, 17.8900, 23.2700, 36.6900, 8.6700, 15.6400, 26.4800, 61.5300, 22.9900, 27.5500, 45.1200, 61.1500, 58.0400, 28.6700, 0.7400,24.9200, 53.2700, 2.7800, 9.9100, 33.4300, 59.5100, 56.6600, 67.5600, 59.5200, 14.0200, 28.2000, 44.9800, 7.3400, '1');insert into R_H_CURVE_E (MS_ID, DATA_ITEM_CODE, DATA_DATE, RECORD_NO, CURVE_DENSITY, DATA_VALUE1, DATA_VALUE2, DATA_VALUE3, DATA_VALUE4, DATA_VALUE5, DATA_VALUE6, DATA_VALUE7, DATA_VALUE8, DATA_VALUE9, DATA_VALUE10, DATA_VALUE11, DATA_VALUE12, DATA_VALUE13, DATA_VALUE14, DATA_VALUE15, DATA_VALUE16, DATA_VALUE17, DATA_VALUE18, DATA_VALUE19, DATA_VALUE20, DATA_VALUE21, DATA_VALUE22, DATA_VALUE23, DATA_VALUE24, DATA_VALUE25, DATA_VALUE26, DATA_VALUE27, DATA_VALUE28, DATA_VALUE29, DATA_VALUE30, DATA_VALUE31, DATA_VALUE32, DATA_VALUE33, DATA_VALUE34, DATA_VALUE35, DATA_VALUE36,DATA_VALUE37, DATA_VALUE38, DATA_VALUE39, DATA_VALUE40, DATA_VALUE41, DATA_VALUE42, DATA_VALUE43, DATA_VALUE44, DATA_VALUE45, DATA_VALUE46, DATA_VALUE47, DATA_VALUE48, DATA_VALUE49, DATA_VALUE50,DATA_VALUE51, DATA_VALUE52, DATA_VALUE53, DATA_VALUE54, DATA_VALUE55, DATA_VALUE56, DATA_VALUE57, DATA_VALUE58, DATA_VALUE59, DATA_VALUE60, DATA_VALUE61, DATA_VALUE62, DATA_VALUE63, DATA_VALUE64,DATA_VALUE65, DATA_VALUE66, DATA_VALUE67, DATA_VALUE68, DATA_VALUE69, DATA_VALUE70, DATA_VALUE71, DATA_VALUE72, DATA_VALUE73, DATA_VALUE74, DATA_VALUE75, DATA_VALUE76, DATA_VALUE77, DATA_VALUE78,DATA_VALUE79, DATA_VALUE80, DATA_VALUE81, DATA_VALUE82, DATA_VALUE83, DATA_VALUE84, DATA_VALUE85, DATA_VALUE86, DATA_VALUE87, DATA_VALUE88, DATA_VALUE89, DATA_VALUE90, DATA_VALUE91, DATA_VALUE92,DATA_VALUE93, DATA_VALUE94, DATA_VALUE95, DATA_VALUE96, DATA_TYPE)values (1040, '9101', '20130425', 0, '15', 12.4000, 6.8900, 60.4300, 57.6900, 61.4900, 50.9000, 18.7400, 52.6800, 68.3600, 0.7200, 45.7500, 28.7200, 49.3600, 34.6200, 11.9700, 55.1400, 14.5900, 69.2400, 43.1900, 27.5500, 1.3800, 4.8000, 21.1500, 54.9500, 16.2000, 48.7700, 57.6800, 10.1300, 65.1500, 33.7000, 2.8600, 55.5900, 30.6200, 30.5100, 28.1200, 4.1600, 55.8500, 43.7200, 32.2500, 54.6000, 54.6800, 11.7500, 27.7000, 7.6300, 13.7700, 18.7600, 22.0600, 28.8400, 34.1800, 3.7900, 44.2600, 32.3100, 51.4600, 46.2200, 14.8500, 32.5100, 7.4800, 59.7400, 66.2800, 13.3100, 62.0500,6.6200, 59.1500, 25.2500, 3.0300, 49.3300, 33.3100, 33.3600, 26.9700, 17.8900, 23.2700, 36.6900, 8.6700, 15.6400, 26.4800, 61.5300, 22.9900, 27.5500, 45.1200, 61.1500, 58.0400, 28.6700, 0.7400,24.9200, 53.2700, 2.7800, 9.9100, 33.4300, 59.5100, 56.6600, 67.5600, 59.5200, 14.0200, 28.2000, 44.9800, 7.3400, '1');insert into R_H_CURVE_E (MS_ID, DATA_ITEM_CODE, DATA_DATE, RECORD_NO, CURVE_DENSITY, DATA_VALUE1, DATA_VALUE2, DATA_VALUE3, DATA_VALUE4, DATA_VALUE5, DATA_VALUE6, DATA_VALUE7, DATA_VALUE8, DATA_VALUE9,DATA_VALUE10, DATA_VALUE11, DATA_VALUE12, DATA_VALUE13, DATA_VALUE14, DATA_VALUE15, DATA_VALUE16, DATA_VALUE17, DATA_VALUE18, DATA_VALUE19, DATA_VALUE20, DATA_VALUE21, DATA_VALUE22, DATA_VALUE23, DATA_VALUE24, DATA_VALUE25, DATA_VALUE26, DATA_VALUE27, DATA_VALUE28, DATA_VALUE29, DATA_VALUE30, DATA_VALUE31, DATA_VALUE32, DATA_VALUE33, DATA_VALUE34, DATA_VALUE35, DATA_VALUE36, DATA_VALUE37,DATA_VALUE38, DATA_VALUE39, DATA_VALUE40, DATA_VALUE41, DATA_VALUE42, DATA_VALUE43, DATA_VALUE44, DATA_VALUE45, DATA_VALUE46, DATA_VALUE47, DATA_VALUE48, DATA_VALUE49, DATA_VALUE50, DATA_VALUE51,DATA_VALUE52, DATA_VALUE53, DATA_VALUE54, DATA_VALUE55, DATA_VALUE56, DATA_VALUE57, DATA_VALUE58, DATA_VALUE59, DATA_VALUE60, DATA_VALUE61, DATA_VALUE62, DATA_VALUE63, DATA_VALUE64, DATA_VALUE65,DATA_VALUE66, DATA_VALUE67, DATA_VALUE68, DATA_VALUE69, DATA_VALUE70, DATA_VALUE71, DATA_VALUE72, DATA_VALUE73, DATA_VALUE74, DATA_VALUE75, DATA_VALUE76, DATA_VALUE77, DATA_VALUE78, DATA_VALUE79,DATA_VALUE80, DATA_VALUE81, DATA_VALUE82, DATA_VALUE83, DATA_VALUE84, DATA_VALUE85, DATA_VALUE86, DATA_VALUE87, DATA_VALUE88, DATA_VALUE89, DATA_VALUE90, DATA_VALUE91, DATA_VALUE92, DATA_VALUE93,DATA_VALUE94, DATA_VALUE95, DATA_VALUE96, DATA_TYPE)values (1040, '9101', '20130426', 0, '15', 12.4000, 6.8900, 60.4300, 57.6900, 61.4900, 50.9000, 18.7400, 52.6800, 68.3600, 0.7200, 45.7500, 28.7200, 49.3600, 34.6200, 11.9700, 55.1400, 14.5900,69.2400, 43.1900, 27.5500, 1.3800, 4.8000, 21.1500, 54.9500, 16.2000, 48.7700, 57.6800, 10.1300, 65.1500, 33.7000, 2.8600, 55.5900, 30.6200, 30.5100, 28.1200, 4.1600, 55.8500, 43.7200, 32.2500,54.6000, 54.6800, 11.7500, 27.7000, 7.6300, 13.7700, 18.7600, 22.0600, 28.8400, 34.1800, 3.7900, 44.2600, 32.3100, 51.4600, 46.2200, 14.8500, 32.5100, 7.4800, 59.7400, 66.2800, 13.3100, 62.0500,6.6200, 59.1500, 25.2500, 3.0300, 49.3300, 33.3100, 33.3600, 26.9700, 17.8900, 23.2700, 36.6900, 8.6700, 15.6400, 26.4800, 61.5300, 22.9900, 27.5500, 45.1200, 61.1500, 58.0400, 28.6700, 0.7400,24.9200, 53.2700, 2.7800, 9.9100, 33.4300, 59.5100, 56.6600, 67.5600, 59.5200, 14.0200, 28.2000, 44.9800, 7.3400, '1');insert into R_H_CURVE_E (MS_ID, DATA_ITEM_CODE, DATA_DATE, RECORD_NO, CURVE_DENSITY, DATA_VALUE1, DATA_VALUE2, DATA_VALUE3, DATA_VALUE4, DATA_VALUE5, DATA_VALUE6, DATA_VALUE7, DATA_VALUE8, DATA_VALUE9, DATA_VALUE10, DATA_VALUE11, DATA_VALUE12, DATA_VALUE13, DATA_VALUE14, DATA_VALUE15, DATA_VALUE16, DATA_VALUE17, DATA_VALUE18, DATA_VALUE19, DATA_VALUE20, DATA_VALUE21, DATA_VALUE22,DATA_VALUE23, DATA_VALUE24, DATA_VALUE25, DATA_VALUE26, DATA_VALUE27, DATA_VALUE28, DATA_VALUE29, DATA_VALUE30, DATA_VALUE31, DATA_VALUE32, DATA_VALUE33, DATA_VALUE34, DATA_VALUE35, DATA_VALUE36,DATA_VALUE37, DATA_VALUE38, DATA_VALUE39, DATA_VALUE40, DATA_VALUE41, DATA_VALUE42, DATA_VALUE43, DATA_VALUE44, DATA_VALUE45, DATA_VALUE46, DATA_VALUE47, DATA_VALUE48, DATA_VALUE49, DATA_VALUE50,DATA_VALUE51, DATA_VALUE52, DATA_VALUE53, DATA_VALUE54, DATA_VALUE55, DATA_VALUE56, DATA_VALUE57, DATA_VALUE58, DATA_VALUE59, DATA_VALUE60, DATA_VALUE61, DATA_VALUE62, DATA_VALUE63, DATA_VALUE64,DATA_VALUE65, DATA_VALUE66, DATA_VALUE67, DATA_VALUE68, DATA_VALUE69, DATA_VALUE70, DATA_VALUE71, DATA_VALUE72, DATA_VALUE73, DATA_VALUE74, DATA_VALUE75, DATA_VALUE76, DATA_VALUE77, DATA_VALUE78,DATA_VALUE79, DATA_VALUE80, DATA_VALUE81, DATA_VALUE82, DATA_VALUE83, DATA_VALUE84, DATA_VALUE85, DATA_VALUE86, DATA_VALUE87, DATA_VALUE88, DATA_VALUE89, DATA_VALUE90, DATA_VALUE91, DATA_VALUE92,DATA_VALUE93, DATA_VALUE94, DATA_VALUE95, DATA_VALUE96, DATA_TYPE)values (1040, '9101', '20130427', 0, '15', 12.4000, 6.8900, 60.4300, 57.6900, 61.4900, 50.9000, 18.7400, 52.6800, 68.3600, 0.7200, 45.7500, 28.7200, 49.3600, 34.6200, 11.9700, 55.1400, 14.5900, 69.2400, 43.1900, 27.5500, 1.3800, 4.8000, 21.1500, 54.9500, 16.2000, 48.7700, 57.6800, 10.1300, 65.1500, 33.7000, 2.8600, 55.5900, 30.6200, 30.5100, 28.1200, 4.1600, 55.8500, 43.7200, 32.2500, 54.6000, 54.6800, 11.7500, 27.7000, 7.6300, 13.7700, 18.7600, 22.0600, 28.8400, 34.1800, 3.7900, 44.2600, 32.3100, 51.4600, 46.2200, 14.8500, 32.5100, 7.4800, 59.7400, 66.2800, 13.3100, 62.0500, 6.6200, 59.1500, 25.2500, 3.0300, 49.3300, 33.3100, 33.3600, 26.9700, 17.8900, 23.2700, 36.6900, 8.6700, 15.6400, 26.4800, 61.5300, 22.9900, 27.5500, 45.1200, 61.1500, 58.0400, 28.6700, 0.7400, 24.9200, 53.2700, 2.7800, 9.9100, 33.4300, 59.5100, 56.6600, 67.5600, 59.5200, 14.0200, 28.2000, 44.9800, 7.3400, '1');insert into R_H_CURVE_E (MS_ID, DATA_ITEM_CODE, DATA_DATE, RECORD_NO, CURVE_DENSITY, DATA_VALUE1, DATA_VALUE2, DATA_VALUE3, DATA_VALUE4, DATA_VALUE5, DATA_VALUE6, DATA_VALUE7, DATA_VALUE8, DATA_VALUE9, DATA_VALUE10, DATA_VALUE11, DATA_VALUE12, DATA_VALUE13, DATA_VALUE14, DATA_VALUE15, DATA_VALUE16, DATA_VALUE17, DATA_VALUE18, DATA_VALUE19, DATA_VALUE20, DATA_VALUE21, DATA_VALUE22, DATA_VALUE23, DATA_VALUE24, DATA_VALUE25, DATA_VALUE26, DATA_VALUE27, DATA_VALUE28, DATA_VALUE29, DATA_VALUE30, DATA_VALUE31, DATA_VALUE32, DATA_VALUE33, DATA_VALUE34, DATA_VALUE35, DATA_VALUE36, DATA_VALUE37, DATA_VALUE38, DATA_VALUE39, DATA_VALUE40, DATA_VALUE41, DATA_VALUE42, DATA_VALUE43, DATA_VALUE44, DATA_VALUE45, DATA_VALUE46, DATA_VALUE47, DATA_VALUE48, DATA_VALUE49, DATA_VALUE50, DATA_VALUE51, DATA_VALUE52, DATA_VALUE53, DATA_VALUE54, DATA_VALUE55, DATA_VALUE56, DATA_VALUE57, DATA_VALUE58, DATA_VALUE59, DATA_VALUE60, DATA_VALUE61, DATA_VALUE62, DATA_VALUE63, DATA_VALUE64, DATA_VALUE65, DATA_VALUE66, DATA_VALUE67, DATA_VALUE68, DATA_VALUE69, DATA_VALUE70, DATA_VALUE71, DATA_VALUE72, DATA_VALUE73, DATA_VALUE74, DATA_VALUE75, DATA_VALUE76, DATA_VALUE77, DATA_VALUE78, DATA_VALUE79, DATA_VALUE80, DATA_VALUE81, DATA_VALUE82, DATA_VALUE83, DATA_VALUE84, DATA_VALUE85, DATA_VALUE86, DATA_VALUE87, DATA_VALUE88, DATA_VALUE89, DATA_VALUE90, DATA_VALUE91, DATA_VALUE92, DATA_VALUE93, DATA_VALUE94, DATA_VALUE95, DATA_VALUE96, DATA_TYPE)values (1040, '9101', '20130428', 0, '15', 12.4000, 6.8900, 60.4300, 57.6900, 61.4900, 50.9000, 18.7400, 52.6800, 68.3600, 0.7200, 45.7500, 28.7200, 49.3600, 34.6200, 11.9700, 55.1400, 14.5900, 69.2400, 43.1900, 27.5500, 1.3800, 4.8000, 21.1500, 54.9500, 16.2000, 48.7700, 57.6800, 10.1300, 65.1500, 33.7000, 2.8600, 55.5900, 30.6200, 30.5100, 28.1200, 4.1600, 55.8500, 43.7200, 32.2500, 54.6000, 54.6800, 11.7500, 27.7000, 7.6300, 13.7700, 18.7600, 22.0600, 28.8400, 34.1800, 3.7900, 44.2600, 32.3100, 51.4600, 46.2200, 14.8500, 32.5100, 7.4800, 59.7400, 66.2800, 13.3100, 62.0500, 6.6200, 59.1500, 25.2500, 3.0300, 49.3300, 33.3100, 33.3600, 26.9700, 17.8900, 23.2700, 36.6900, 8.6700, 15.6400, 26.4800, 61.5300, 22.9900, 27.5500, 45.1200, 61.1500, 58.0400, 28.6700, 0.7400, 24.9200, 53.2700, 2.7800, 9.9100, 33.4300, 59.5100, 56.6600, 67.5600, 59.5200, 14.0200, 28.2000, 44.9800, 7.3400, '1');

3、现实需求的PL/SQL块

DECLARE  I          NUMBER;  RESULT     NUMBER := 0;  A          VARCHAR2(20);  SUNLINE    VARCHAR2(4000);  DATA_VALUE VARCHAR2(20);BEGIN  FOR I IN 1 .. 3 LOOP    a := 'DATA_VALUE' || I;    EXECUTE IMMEDIATE 'SELECT sum(' || A ||                      ') FROM R_H_CURVE_E a WHERE substr(a.data_date,1,6)=201304 AND a.data_item_code=9101 AND ms_id=1040'      INTO SUNLINE;    RESULT := RESULT + SUNLINE;    DBMS_OUTPUT.PUT_LINE(RESULT);  END LOOP;END;

转载于:https://www.cnblogs.com/Automation_software/archive/2013/06/05/3120067.html

你可能感兴趣的文章

Data truncation: Out of range value for column 'Quality' at row 1

查看>>

字符串处理

查看>>

HtmlUnitDriver 网页内容动态抓取

查看>>