Shulou(Shulou.com)05/31 Report--

The main content of this article is to explain "what is the method of TO_CHAR format conversion in the database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "what is the method of TO_CHAR format conversion in the database"!

My manager asked me to write a function that takes a positive integer and returns the corresponding ordinal (for example, 2-> 2nd, 145-> 145th), which is a string. This function only accepts integers between 1 and 50000.

Which of the following options implements the function plch_n_to_nth so that it meets the above requirements after the following blocks are executed:

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

I can see the following output:

1st8th256th25763rd

(A)

CREATE OR REPLACE FUNCTION plch_n_to_nth (n_in IN INTEGER) RETURN VARCHAR2ISBEGIN 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 (25763)); DBMS_OUTPUT.put_line (plch_n_to_nth (25763)); END ORA-01481: invalid number format model ORA-06512: in "YOGA.PLCH_N_TO_NTH", line 5ORA-06512: in line 2SQL >

(B)

CREATE OR REPLACE FUNCTION plch_n_to_nth (n_in IN INTEGER) RETURN VARCHAR2ISBEGIN RETURN LOWER (TO_CHAR (TO_DATE ('1-1 muri'| | 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 / 1st8th256thBEGIN 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); DBMS_OUTPUT.put_line (plch_n_to_nth (25763)); END;ORA-01830: date format picture ends before converting the entire input string ORA-06512: in "YOGA.PLCH_N_TO_NTH", line 5ORA-06512: in line 5SQL >

(C)

CREATE OR REPLACE FUNCTION plch_n_to_nth (n_in IN INTEGER) RETURN VARCHAR2ISBEGIN 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 / 1st8th256th25763rdPL/SQL procedure successfully completedSQL >

(D)

CREATE OR REPLACE FUNCTION plch_n_to_nth (n_in IN INTEGER) RETURN VARCHAR2IS 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 / 1st8th256th25763rdPL/SQL procedure successfully completedSQL > CD. (a) you cannot use the TH format in TO_CHAR (NUMBER), only in TO_CHAR (datetime). (B) almost right, this answer uses the th format in the year, but ORACLE does not support up to 50000 years, there will be the following error: ORA-01830: date format picture ends before converting entire input string (C), which correctly uses the th format of TO_CHAR (datetime), has 86400 seconds in a day, which is enough to use. (d) this custom function correctly implements the conversion rules. At this point, I believe you have a deeper understanding of "what is the method of TO_CHAR format conversion in the database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.