BiuBiu

# Oracle

  • 查询一个表中的总数据条数

如果没有数据，说明该表没有被分析过（可以执行下面的手动分析）

select nvl(t.NUM_ROWS, 0)
  from user_tables t
 where table_name = upper('tb_tag_marketSearch_Phone');
1
2
3
  • 分析表
analyze table crmdb.tb_tag_marketSearch_Phone compute statistics;
1

查询更新状态

select * from orderdb.t_aswitch_canceloverdueorder t
where t.orderid = 10260922;

update orderdb.t_aswitch_canceloverdueorder t
set t.flag = 1 where t.orderid = 10260922;
1
2
3
4
5
  1. 消息查询
select * from orderdb.t_message_type
select * from orderdb.t_message_bind
select * from orderdb.t_message_template
1
2
3
  1. 新建模板
insert into orderdb.t_message_type
  (ID, MESSAGE_CODE, MESSAGE_DES)
  select seq_message_type.nextval, 'MESSAGE_71', '定制服务提交成功通知' from dual;

insert into orderdb.t_message_type
  (ID, MESSAGE_CODE, MESSAGE_DES, PARENT_CODE)
values
  (seq_message_type.nextval,
   'MESSAGE_71_01',
   '定制服务提交成功通知',
   'MESSAGE_71');

insert into orderdb.T_MESSAGE_TEMPLATE
  (ID, TEMPLATE_CODE, TEMPLATE_NAME, TEMPLATE_CONTENT)
values
  (seq_message_template.nextval,
   'TEMPLATE_112',
   '定制服务提交成功通知',
   '客人提交定制需求啦！$需求ID demandId，$人均预算：budget');

insert into orderdb.T_MESSAGE_BIND
  (ID, MESSAGE_CODE, CHANNEL_CODE, TEMPLATE_CODE, STATUS)
values
  (seq_message_bind.nextval,
   'MESSAGE_71_01' ， 'WECHAT',
   'TEMPLATE_112',
   1);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
  1. 新增字段
alter table productdb.tb_product_group add auditstatus number(1);
comment on column productdb.tb_product_group.auditstatus is '1:已审核，0未审核';

alter table productdb.tb_product_group add grouptype number(5);
comment on column productdb.tb_product_group.grouptype is '分组类型';

alter table productdb.tb_product_group add companyId number(8);
comment on column productdb.tb_product_group.companyId is '分社Id';

alter table productdb.tb_product_group add departmentid number(8);
comment on column productdb.tb_product_group.departmentid is '部门Id';
1
2
3
4
5
6
7
8
9
10
11
  1. 合并数据
merge into productdb.tb_product_group g
using (select u.companyid, u.id from onlinedb.tb_auth_user u) u1
on (g.createuser = u1.id)
when matched then update set g.companyid = u1.companyid


merge into productdb.tb_product_group g
using (select u.departmentid, u.id from onlinedb.tb_auth_user u) u1
on (g.createuser = u1.id)
when matched then update set g.departmentid = u1.departmentid
1
2
3
4
5
6
7
8
9
10
11
12
13

← Redis →