CREATE OR REPLACE PROCEDURE Pro_scada_AUTO_CREATETABLE(collection_time IN VARCHAR2,state OUT NUMBER) IS
/**
*本存储过程作用:
*1、根据解析E文件的数据,自动创建当前月份的表(比如此数据日期是20190801,就会创建sgcm_scada_device_data_201908,将此数据存入此表)
*/
tabName VARCHAR2(200); ----------表名
tabCount NUMBER; ----------此表的出现次数
p_msg VARCHAR2(250); ---------错误消息
collection VARCHAR2(250); --时间的年月
v_sql varchar2(1000);
BEGIN
state := 0; --状态码默认0
--获取月份
collection := to_char(to_date(collection_time,'yyyy-mm-dd hh24:mi:ss'),'yyyymm');
--拼接表
tabName := 'sgcm_scada_device_data_' || collection;
--查看是否存在此表
SELECT COUNT(1) INTO tabCount FROM user_tables WHERE table_name = UPPER(tabName);
if tabCount = 0 THEN
-- Create table
EXECUTE IMMEDIATE 'create table '||tabName||'
(
uuid VARCHAR2(64) not null,
substation_name VARCHAR2(120),
device_name VARCHAR2(256),
device_id VARCHAR2(64),
active_power_value NUMBER(38,5),
active_power_state NUMBER(2),
reactive_power_value NUMBER(38,5),
reactive_power_state NUMBER(2),
collection_time DATE,
apparent_power NUMBER(38,5),
load_rate NUMBER(38,5),
electric_current NUMBER(38,5),
gear_position NUMBER(38,5),
bureau_name VARCHAR2(64),
a_current NUMBER(38,5),
b_current NUMBER(38,5),
c_current NUMBER(38,5),
voltage_level VARCHAR2(64),
rated_capacity NUMBER(38,5) default 100
)
PARTITION BY RANGE (collection_time) INTERVAL (NUMTODSINTERVAL(1,''DAY''))
(
PARTITION TOP_LOG_USER_LOGIN_DETAIL_P1 VALUES LESS THAN (TO_DATE(''2010-05-01'', ''YYYY-MM-DD''))
)';
v_sql := 'comment on table '||tabName||' is ''主变信息''';
execute immediate v_sql;
-- Add comments to the columns
v_sql := 'comment on column '||tabName||'.uuid is ''UUID''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.substation_name is ''厂站名''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.device_name is ''设备名称''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.device_id is ''设备id''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.active_power_value is ''有功值''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.active_power_state is ''有功状态''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.reactive_power_value is ''无功值''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.reactive_power_state is ''无功状态''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.collection_time is ''采集时间''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.apparent_power is ''视在功率''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.load_rate is ''负载率''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.electric_current is ''电流''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.gear_position is ''档位''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.bureau_name is ''供电局''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.a_current is ''a相电流''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.b_current is ''b相电流''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.c_current is ''c相电流''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.voltage_level is ''电压等级(kV)''';
execute immediate v_sql;
v_sql := 'comment on column '||tabName||'.rated_capacity is ''容量''';
execute immediate v_sql;
-- Create/Recreate indexes
v_sql := 'create index INX_BUREAU_NAME'||collection||' on '||tabName||' (BUREAU_NAME)
tablespace GZ_DDW_TBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)';
execute immediate v_sql;
v_sql := 'create index INX_COLLECTION_TIME'||collection||' on '||tabName||' (COLLECTION_TIME)
tablespace GZ_DDW_TBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)';
execute immediate v_sql;
v_sql := 'create index INX_COMBINATION'||collection||' on '||tabName||' (DEVICE_ID, COLLECTION_TIME, APPARENT_POWER, ACTIVE_POWER_VALUE, REACTIVE_POWER_VALUE)
tablespace GZ_DDW_TBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)';
execute immediate v_sql;
v_sql := 'create index INX_DEVICE_ID'||collection||' on '||tabName||' (DEVICE_ID)
tablespace GZ_DDW_TBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)';
execute immediate v_sql;
v_sql := 'create index INX_DEVICE_NAME'||collection||' on '||tabName||' (DEVICE_NAME)
tablespace GZ_DDW_TBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)';
execute immediate v_sql;
v_sql := 'create index INX_SUBSTATION_NAME'||collection||' on '||tabName||' (SUBSTATION_NAME)
tablespace GZ_DDW_TBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)';
execute immediate v_sql;
v_sql := 'create index INX_VOLTAGE_LEVEL'||collection||' on '||tabName||' (DEVICE_ID, COLLECTION_TIME)
tablespace GZ_DDW_TBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)';
execute immediate v_sql;
--表示创建动态表成功
state := 1;
ELSE
--tableCount>0,表示已经存在此月份的表
state := 2;
end if;
EXCEPTION
WHEN OTHERS THEN
--回滚
ROLLBACK;
--打印异常消息
p_msg := sqlcode || sqlerrm;
dbms_output.put_line(p_msg);
--存储过程执行失败,记录失败信息
INSERT INTO ECMS_PROC_LOG
(LOGID, PROC_NAME, DES, EXETIME, ISSUCCESS, ENDTIME)
VALUES
(SYS_GUID(),
'sgcm_scada_device_data',
'修改调度数据失败,异常信息:' || p_msg,
SYSDATE,
-1,
SYSDATE);
COMMIT;
end;
oracle 存储过程根据传入的月份分表(根据时间月份分表),动态创建表,分区,索引
原创
©著作权归作者所有:来自51CTO博客作者wx59da39eb5451d的原创作品,请联系作者获取转载授权,否则将追究法律责任
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
MySQL之存储过程按月创建表
MySQL之存储过程按月创建表
MySQL之存储过程 -
Oracle存储过程创建及调用
在大型数据库系统中,有两个很重要作用的功能,那就是存储过程和触发器。在数据库系统中无论是存储过程还是触发器,都是通过SQL 语句和控制流程语句
存储过程 oracle oracle存储过程 java sql