ORACLE多表查询保存到新表,id自增触发器
参考
https://blog.csdn.net/sinat_29774479/article/details/75220077?locationNum=6&fps=1
- 创建序列
新增数据的表IMR_Y_COLLECTOR_STATUS,创建序列
CREATE SEQUENCE seq_IMR_Y_COLLECTOR_STATUS START WITH 1 INCREMENT BY 1;
- 创建触发器
每次插入数据时都自动引用序列的id进行添加
CREATE
OR REPLACE TRIGGER tri_IMR_Y_COLLECTOR_STATUS BEFORE INSERT ON IMR_Y_COLLECTOR_STATUS FOR EACH ROW
BEGIN
SELECT
seq_IMR_Y_COLLECTOR_STATUS.nextval INTO:new.id
FROM
dual ;
END ;
- 创建列并给默认值
alter table IMR_Y_COLLECTOR_STATUS add content varchar(30) default '集中器离线';
oralce加减日期
参考
https://blog.csdn.net/hyeidolon/article/details/8290724
sysdate+1 加一天
sysdate+1/24 加1小时
sysdate+1/(2460) 加1分钟
sysdate+1/(2460*60) 加1秒钟
oracle case用法
参考
https://www.cnblogs.com/huangbiquan/p/8001742.html
此处列举更新表数据:
update imr_y_collector c set c.online_state = (
case c.online_state when 1 then 0
else 0
end
),c.connecttime = sysdate
where c.id in(
select t.id from imr_y_collector t where t.connecttime+1/24<sysdate and t.isvalid = 1
)
C# switch语句数据类型不同赋值问题
public int ONLINE_STATE
{
get
{
switch (ONLINE_STATE)
{
case 0:
return "离线";
case 1:
return "在线";
default:
return "未知";
}
}
}
online_state int,return 会报数据类型错误。解决方法是新增一个属性用来装最终返回的结果:
public string ONLINE_STATE_Name
{
get
{
switch (ONLINE_STATE)
{
case 0:
return "离线";
case 1:
return "在线";
default:
return "未知";
}
}
}