从表中随机取4条记录:
select * from (select * from 表 order by dbms_random.random) where rownum < 5
伪列2type:
select * from (
select ai.activityId,ai.owner,ai.creatorName, ai.subject,ai.createDate,2type
from ActivityInfo ai
union
select aj.activityId,aj.joinId as owner,aj.joinName as creatorName, ai.subject,aj.attendDate as createDate,aj.type from ActivityJoin aj
INNER JOIN ActivityInfo ai ON aj.activityId=ai.activityId
order by createDate desc
)
where rownum < 5
日期:
(1)
string dateTo = DateTime.Now.AddDays(-1).ToShortDateString();
dateTo = "to_date('" + dateTo + "','yyyy-mm-dd') ";
string dateFrom = DateTime.Now.AddDays(1).ToShortDateString();
dateFrom = "to_date('" + dateFrom + "','yyyy-mm-dd') ";
sql += " and f.DateFrom < " + dateFrom + " and f.DateTo > " + dateTo;
(2)
update 表set 字段=to_date('2010-2-17 14:36:27','yyyy-mm-dd hh24:mi:ss')
where name='abc88'
系统GUID:sys_guid()系统日期:sysdate
触发器例子1:
CREATE SEQUENCE S_TAB
INCREMENT BY 1
START WITH 1
MAXVALUE 99999999
CREATE TRIGGER TR_TAB BEFORE
INSERT ON TAB
FOR EACH ROW begin
SELECT S_TAB.NEXTVAL
INTO :NEW.ID
FROM DUAL;
End;
触发器例子2:
CREATE OR REPLACE TRIGGER TR_TAB
BEFORE INSERT OR UPDATE ON TAB
FOR EACH ROW
DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
begin
if inserting then
SELECT S_TAB.NEXTVAL
INTO :NEW.ID
FROM DUAL;
end if;
if updating then
if (:NEW.FRESULT = '2' and trunc(:NEW.LASTOPERDATE,'dd') > (:NEW.FDATE + 1)) then
update TAB set XUZUOBACKSTATE='1',
BACKDATE=:NEW.LASTOPERDATE
WHERE TYPE='4';
end if;
end if;
COMMIT;
End;
查看表每条记录的创建时间
select to_char(scn_to_timestamp(ORA_ROWSCN),'yyyy-mm-dd hh24:mi:ss') insert_time, t.* from tb t;
另外:
pl/sql导出表结构:
Tools-->Export User Objects -->选择要导出的表-->.sql文件
pl/sql导出表数据:
Tools-->Export Tables-->选择表,选择SQL Inserts-->.sql文件