1. 获取系统日期时间
select sysdate as now from dual2. 把日期格式转换为字符串格式
select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual 3. 把字符串日期格式转换为date格式
select to_date('2000-10-10 10:10:10','YYYY-MM-DD HH24:MI:SS') as inputdate from dual 4. 创建一个DateDiff函数
/**//** 创建一个DateDiff函数**/
/**//** 调用方式: select datediff( 'SS', to_date('1900-1-1 0:2:0','YYYY-MM-DD HH24:MI:SS'), to_date('2999-1-1 0:2:0','YYYY-MM-DD HH24:MI:SS') ) from dual **/
create or replace function datediff(p_what in varchar2, p_d1 in date, p_d2 in date) return number is
l_result number;
begin
select decode( upper(p_what), 'SS', (p_d2-p_d1)* 24 * 60 * 60
, 'MI', (p_d2-p_d1)* 24 * 60
, 'HH', (p_d2-p_d1)* 24
, 'DD', (p_d2-p_d1)
, 'MM', trunc(months_between(to_date(to_char(p_d2,'DD-MON-YYYY'), 'DD-MON-YYYY'), to_date(to_char(p_d1,'DD-MON-YYYY'), 'DD-MON-YYYY')))
, 'YY', trunc(months_between(to_date(to_char(p_d2,'DD-MON-YYYY'), 'DD-MON-YYYY'), to_date(to_char(p_d1,'DD-MON-YYYY'), 'DD-MON-YYYY')) / 12)
, NULL )
into l_result from dual;
return(l_result);
end datediff; 5.ORA-01658:无法为表空间SA中的段创建INITIAL区?
select file#,status,enabled,name from v$datafile;
alter tablespace users add datafile 'c:\data\a.dbf' size 1024m;
6.Oracle遍历树结构表的两种案例
--从叶子往上遍历
SELECT pkid, CATENAME,path,isdel
FROM tcategory
CONNECT BY pkid= PRIOR PARENTID START WITH pkid =10001224;
--从根往下遍历
SELECT pkid, CATENAME,path,isdel
FROM tcategory
CONNECT BY PRIOR pkid=PARENTID START WITH pkid =10001223; 7.循环
declare X integer :=0;
begin
WHILE X<=500 LOOP
X:=X+1;
insert into tcustomer(pkid,full_name,gender,grade_id,join_time)
values(X,'user'||X,1,1,sysdate);
END LOOP;
end;