SELECT * FROM tra_access_manage AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '2' DAY)
?
查询前2天表的数据
?
?
2.
创建一个存储过程，让id自增长
?
CREATE OR REPLACE Procedure pro_getid(
??? TableName IN varchar2,????? -- 类型：输入表名
??? id OUT varchar2
?) IS
--
-- Purpose: 根据类型返回不同类型的ID,包括提交事务
--????????? 触发器如果调用本过程，小心处理事务。
-- MODIFICATION HISTORY
-- ---------?? ------? -------------------------------------------
?? -- Declare program variables as shown above
?? sTableName?? VARCHAR2(30);
?? sSequence??? VARCHAR2(10);
?? sTempSequence??????? VARCHAR2(20);?? -- 临时输出流水号
BEGIN
??? sTableName := UPPER(TableName);
??? -- 取序列
??? CASE WHEN sTableName = 'CUSTOMERBASEINFO' THEN
?????? -- 年（1位）＋ 月（1位英文字母）＋ 日（1位英文字母） + 序列（6位）
?????? sTempSequence := fun_conver_date(SYSDATE);
?????? SELECT seq_cust.NEXTVAL INTO sSequence FROM dual;
?????? sSequence := Substr('00000'||sSequence, -6, 6);
?????? id := sTempSequence||sSequence;
??? WHEN sTableName = 'DVS' THEN
??????? pro_get_voicefile('A',id);
??? WHEN sTableName = 'ORDERS' THEN
???????? SELECT seq_order.nextval INTO sSequence FROM dual;
??????? ??? sSequence := Substr('00000'||sSequence, -6, 6);
?????? ??? id := to_char(sysdate,'YYYYMMDDHH24MISS')||sSequence;
??? WHEN sTableName = 'ORDER' THEN
???????? SELECT seq_gway.nextval INTO sSequence FROM dual;
???????? sSequence := Substr('00000'||sSequence, -6, 6);
?????? ??? ?id := to_char(sysdate,'YYYYMMDDHH24MISS')||sSequence;
??? WHEN? sTableName = 'ORDERSCHEDULE' THEN
???????? SELECT seq_schedule.nextval INTO sSequence FROM dual;
???????? sSequence := Substr('00000'||sSequence, -6, 6);
?????? ??? id := to_char(sysdate,'YYYYMMDDHH24MISS')||sSequence;
?? WHEN? sTableName = 'SMSOUTBOX' OR sTableName = 'SHORTMESSAGEOUTBOX' THEN
???????? SELECT seq_smsoutbox.nextval INTO sSequence FROM dual;
????????? sSequence := Substr('00000'||sSequence, -6, 6);
?????? ??? id := to_char(sysdate,'YYYYMMDDHH24MISS')||sSequence;
?? WHEN? sTableName = 'SMSENDBOX' THEN
???????? SELECT seq_smsendbox.nextval INTO sSequence FROM dual;
???????? sSequence := Substr('00000'||sSequence, -6, 6);
???????? id := to_char(sysdate,'YYYYMMDDHH24MISS')||sSequence;
??? WHEN? sTableName = 'FLOWERORDERSUMA' THEN
???????? SELECT seq_flower.nextval INTO sSequence FROM dual;
???????? id := 'T'||to_char(sysdate,'YYMMDD')||'-'||sSequence;
??? WHEN? sTableName = 'FLOWERCOMPLAINT' THEN
???????? SELECT SEQ_FLOWER_COMPL.NEXTVAL INTO sSequence FROM dual;
???????? id := ''||sSequence;
??? WHEN? sTableName = 'RECORD_ID' THEN
???????? SELECT seq_record.nextval INTO sSequence FROM dual;
???????? sSequence := Substr('00000'||sSequence, -6, 6);
???????? id := to_char(sysdate,'YYYYMMDDHH24MISS')||sSequence;
??? WHEN sTableName = 'SENDSMS12580SERVICEIMPL' THEN--统一订单二维码发送
???????? SELECT seq_log.NEXTVAL INTO sSequence FROM dual;
???????? sSequence := Substr('00000'||sSequence, -6, 6);
???????? id := to_char(sysdate,'YYMMDD')||sSequence;
??? ELSE
???????? SELECT seq_log.NEXTVAL INTO sSequence FROM dual;
???????? sSequence := Substr('00000'||sSequence, -6, 6);
???????? id := to_char(sysdate,'YYYYMMDDHH24MISS')||sSequence;
?? END CASE;
?EXCEPTION
??? WHEN others THEN
?????? SELECT seq_log.NEXTVAL INTO sSequence FROM dual;
?????? sSequence := Substr('00000'||sSequence, -6, 6);
?????? id := to_char(sysdate,'YYYYMMDDHH24MISS')||sSequence;
END; -- Procedure PRO_GETID
?