Samuel Learning

Oracle 使用筆記

select?sysdate?as?now?from?dual

select?to_char(sysdate,?'YYYYMMDD?HH24:MI:SS')?from?dual

3.? 把字符串日期格式轉(zhuǎn)換為date格式

select?to_date('2000-10-10?10:10:10','YYYY-MM-DD?HH24:MI:SS')?as?inputdate?from?dual

4. 創(chuàng)建一個(gè)DateDiff函數(shù)

/**//**?創(chuàng)建一個(gè)DateDiff函數(shù)**/
/**//**?調(diào)用方式:?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中的段創(chuàng)建INITIAL區(qū)?

select?file#,status,enabled,name?from?v$datafile;

alter???tablespace???users???add???datafile???'c:\data\a.dbf'???size???1024m;?

6.Oracle遍歷樹結(jié)構(gòu)表的兩種案例

--從葉子往上遍歷
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.循環(huán)

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;

?

?

posted on 2005-10-14 12:18 MingIsMe 閱讀(259) 評(píng)論(0)  編輯  收藏 所屬分類: 10 Database