oracle序列设置

1
2
3
4
5
create sequence SE_R_DOCUMENTDETAIL_1356
  minvalue 1
    nomaxvalue start with 1
      increment by 1
        noorder nocycle cache 20;

设置oracle外键关联

1
ALTER TABLE R_RECTIFICATIONSTAGE_1151 ADD CONSTRAINT SYS_C00110337 FOREIGN KEY(P_RECTIFICATIONSTAGEID) REFERENCES R_RECTIFICATIONSTAGE_1151(RECTIFICATIONSTAGEID);

设置新的最大连接数

set GLOBAL max_connections=200;

结束当前进程

kill 加进程ID

查看当前所有用户的连接

1
show full processlist;

我们来查看mysql的最大连接数

1
show variables like '%max_connections%';

查看序列下一个号码

1
select ACHILLES.SE_Q_CMN_OPPTAUDITDETAIL_558.nextval from dual

查询统计档案查询SQL语句

1
2
3
4
5
6
7
8
9
10
11
select opt20.operatepointid operatepointid,opt20.operatepointname operatepointname,opt20.OPERATEPOINTADDRESS OPERATEPOINTADDRESS,ind90.industrytypename industrytypename,
      un18.LEGALPERSON LEGALPERSON,opt20.OPPTRESPONSIBLEPERSON OPPTRESPONSIBLEPERSON,cmn1123.currentsupervisedptid currentsupervisedptid,
       opt20.opptresponsiblepersonphone opptresponsiblepersonphone,cmn1123.currentsuperviseopptid currentsuperviseopptid,
       replace(replace(opt21.operatepointname,'安全生产监察队',''),'浦东新区安全生产监察大队','安监大队') operatepointname1,opt20.longitude,opt20.latitude,cmn431.istemparchived,opt20.businessstatusid
       from q_cmn_operatepoint_20 opt20,d_industrytype_90 ind90,q_cmn_operatepoint_20 opt21,q_cmn_pandbunitsspecial_431 cmn431,
                       q_cmn_unit_18 un18,Q_CMN_OPTCURRENTSUPERVISE_1123 cmn1123
        where opt20.isdeleted=0
                      and cmn431.isdeleted = 0 and cmn431.ISNEEDREPORT=1 and cmn431.pandbunitsspecialid = cmn1123.pandbunitsspecialid
                      and cmn1123.CURRENTSUPERVISEYPE = 1 and cmn1123.isdeleted = 0 and opt20.operatepointid = cmn431.operatepointid and un18.isdeleted=0
                      --and opt20.businessstatusid between 1 and 2
                      and cmn1123.currentsuperviseopptid=opt21.operatepointid and opt20.industrytypeid=ind90.industrytypeid and opt20.unitid=un18.unitid;

如果查询出来的是null则用0代替

NVL(A.VERIFYNODEID, 0) VERIFYNODEID