oracle数据库

创建sequence 

create sequence hawkeyepredb.tc_report_log_seq minvalue 1 start with 100000  increment by 1 NOCYCLE NOCACHE

SHOW SEQUENCES where name = 'mer_id_seq'

UPDATE SEQUENCES set value='1518593' where name = 'mer_id_seq'

查询大于1的重复记录
select * from 重复记录字段 in ( select 重复记录字段 form  数据表 group by 重复记录字段 having count(重复记录字段)>1)

插入多条数据
declare
-- v_mer_cust_id varchar2(20);
v_sys_seq_id_1 VARCHAR2(8);
v_sys_seq_id_2 VARCHAR2(10);
v_order_id varchar2(20);
i int;
begin
i:=1001;
v_sys_seq_id_1:='21300009';
v_sys_seq_id_2:='2930000009';
v_order_id:='21300000000000000009';

for i in 1001..1002 loop

insert into substitute_pay_log (SYS_DATE, SYS_SEQ_ID, MER_CUST_ID, ORDER_ID, ORDER_DATE, BUSSINESS_ID, BG_RET_URL, TRANS_AMT, FEE_AMT, PURPOSE, REF_BUSI_ID, CREATE_TIME, UPDATE_TIME, MER_PRIV, EXTENSION, TRANS_TYPE, RESP_CODE, RESP_DESC)
values ('20181019', to_char(v_sys_seq_id_1+i), '6666000000028641', to_char(v_order_id+i), '20181019', null, 'http://192.168.0.74:8001/npayCallBack/asyncHandle.json', 0.21, 0.10, '代发到农业银行', null, '18-10月-18 02.29.10.000000 上午', '18-10月-18 02.29.11.000000 上午', 'test_mer_priv', 'test_exte***nsion', null, null, null);

end loop;
commit;
end;

某个时间的值
select * from ELEC_CONTRACT_REQ as of timestamp to_timestamp('2019-2-15 13:47:50','yyyy-mm-dd hh24:mi:ss') where mer_cust_id='6666000000582666'
使用escape关键字去转义特殊字符
select * from TC_100000_CASETEST1201_BIZCHK where TEST_POINT like 'version_\'||'%' escape '\';

oracle和mysql的差异

Mysql date_format(m1.LOGON_TIME,'%Y-%m-%d %T')
Oracle to_char(m1.LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')
Mysql delete 不能带别名
Mysql 连接函数concat，Oracle是||
Nvl 用ifnull 代替
Oracle用 Rownum
Mysql 用 limit