您好,登錄后才能下訂單哦!
本篇內(nèi)容主要講解“數(shù)據(jù)庫中TO_CHAR格式轉(zhuǎn)換的方法是什么”,感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷,實用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“數(shù)據(jù)庫中TO_CHAR格式轉(zhuǎn)換的方法是什么”吧!
我的經(jīng)理叫我寫一個函數(shù),這個函數(shù)接受一個正整數(shù)并返回相應(yīng)的序數(shù)(比如,2->2nd, 145->145th), 這個序數(shù)是一個字符串。這個函數(shù)只接受1和50,000之間的整數(shù)。
下列的哪些選項實現(xiàn)了函數(shù)plch_n_to_nth, 從而使得它滿足上述需求,在下列塊被執(zhí)行之后:
BEGIN DBMS_OUTPUT.put_line (plch_n_to_nth (1)); DBMS_OUTPUT.put_line (plch_n_to_nth (8)); DBMS_OUTPUT.put_line (plch_n_to_nth (256)); DBMS_OUTPUT.put_line (plch_n_to_nth (25763)); END; /
我能見到如下輸出:
1st 8th 256th 25763rd
(A)
CREATE OR REPLACE FUNCTION plch_n_to_nth (n_in IN INTEGER) RETURN VARCHAR2 IS BEGIN RETURN TO_CHAR (n_in, '9999th'); END; /
SQL> BEGIN 2 DBMS_OUTPUT.put_line (plch_n_to_nth (1)); 3 DBMS_OUTPUT.put_line (plch_n_to_nth (8)); 4 DBMS_OUTPUT.put_line (plch_n_to_nth (256)); 5 DBMS_OUTPUT.put_line (plch_n_to_nth (25763)); 6 END; 7 / BEGIN DBMS_OUTPUT.put_line (plch_n_to_nth (1)); DBMS_OUTPUT.put_line (plch_n_to_nth (8)); DBMS_OUTPUT.put_line (plch_n_to_nth (256)); DBMS_OUTPUT.put_line (plch_n_to_nth (25763)); END; ORA-01481: 無效的數(shù)字格式模型 ORA-06512: 在 "YOGA.PLCH_N_TO_NTH", line 5 ORA-06512: 在 line 2 SQL>
(B)
CREATE OR REPLACE FUNCTION plch_n_to_nth (n_in IN INTEGER) RETURN VARCHAR2 IS BEGIN RETURN LOWER ( TO_CHAR ( TO_DATE ('1-1-' || n_in, 'dd-mm-yyyy') , 'FMYYYYth')); END; /
SQL> BEGIN 2 DBMS_OUTPUT.put_line (plch_n_to_nth (1)); 3 DBMS_OUTPUT.put_line (plch_n_to_nth (8)); 4 DBMS_OUTPUT.put_line (plch_n_to_nth (256)); 5 DBMS_OUTPUT.put_line (plch_n_to_nth (25763)); 6 END; 7 / 1st 8th 256th BEGIN DBMS_OUTPUT.put_line (plch_n_to_nth (1)); DBMS_OUTPUT.put_line (plch_n_to_nth (8)); DBMS_OUTPUT.put_line (plch_n_to_nth (256)); DBMS_OUTPUT.put_line (plch_n_to_nth (25763)); END; ORA-01830: 日期格式圖片在轉(zhuǎn)換整個輸入字符串之前結(jié)束 ORA-06512: 在 "YOGA.PLCH_N_TO_NTH", line 5 ORA-06512: 在 line 5 SQL>
(C)
CREATE OR REPLACE FUNCTION plch_n_to_nth (n_in IN INTEGER) RETURN VARCHAR2 IS BEGIN RETURN LOWER ( TO_CHAR ( TO_DATE ('1-1-2011 ' || n_in , 'dd-mm-yyyy SSSSS') , 'FMSSSSSth')); END; /
SQL> BEGIN 2 DBMS_OUTPUT.put_line (plch_n_to_nth (1)); 3 DBMS_OUTPUT.put_line (plch_n_to_nth (8)); 4 DBMS_OUTPUT.put_line (plch_n_to_nth (256)); 5 DBMS_OUTPUT.put_line (plch_n_to_nth (25763)); 6 END; 7 / 1st 8th 256th 25763rd PL/SQL procedure successfully completed SQL>
(D)
CREATE OR REPLACE FUNCTION plch_n_to_nth ( n_in IN INTEGER) RETURN VARCHAR2 IS c_last_digit CONSTANT PLS_INTEGER := MOD (n_in, 10) ; c_tens_digit CONSTANT CHAR (1) := SUBSTR ('0' || TO_CHAR (n_in), -2, 1) ; BEGIN RETURN TO_CHAR (n_in) || CASE WHEN c_tens_digit = '1' THEN 'th' WHEN c_last_digit = 1 THEN 'st' WHEN c_last_digit = 2 THEN 'nd' WHEN c_last_digit = 3 THEN 'rd' ELSE 'th' END; END plch_n_to_nth; /
SQL> BEGIN 2 DBMS_OUTPUT.put_line (plch_n_to_nth (1)); 3 DBMS_OUTPUT.put_line (plch_n_to_nth (8)); 4 DBMS_OUTPUT.put_line (plch_n_to_nth (256)); 5 DBMS_OUTPUT.put_line (plch_n_to_nth (25763)); 6 END; 7 / 1st 8th 256th 25763rd PL/SQL procedure successfully completed SQL>
CD. (A)你不能在TO_CHAR(NUMBER)中使用TH格式,只能在TO_CHAR (datetime)中使用。 (B)差點就對了,這個答案在年份使用了th格式, 但ORACLE不支持高達(dá)50,000的年份,會出現(xiàn)如下錯誤: ORA-01830: date format picture ends before converting entire input string (C)這個正確使用了TO_CHAR (datetime)的th格式,一天中有86400秒,足夠使用。 (D)這個自定義函數(shù)正確實現(xiàn)了轉(zhuǎn)換規(guī)則。
到此,相信大家對“數(shù)據(jù)庫中TO_CHAR格式轉(zhuǎn)換的方法是什么”有了更深的了解,不妨來實際操作一番吧!這里是億速云網(wǎng)站,更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢,關(guān)注我們,繼續(xù)學(xué)習(xí)!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。