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; ?
?