如何在单词中显示数值
问.在 Words 中显示数字值，输出应如下所示
Q. Display the number value in Words and output should look like this
SAL In_Words
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
而且，我仍然没有弄清楚，这个查询是如何解决上述输出的.
And, I'm still didn't figure out, how this query is the solution for the above output.
select sal, to_char(to_date(sal,'j'),'Jsp') in_words from emp
to_date 在这里做什么?有人对此查询有任何想法吗?
What to_date is doing here ? Anyone have any idea about this query ?
推荐答案
那么查询是如何工作的呢?原因如下:
So how the query works? Well here’s why:
select to_char(to_date(:number,'j'),'jsp') from dual;
如果你查看查询 to_date(:number,'j') 的最里面的部分，'j' 或 J 是儒略日期(公元前 4713 年 1 月 1 日)，基本上这个日期被用于天文研究.
If you look into the inner most part of the query to_date(:number,'j') the ‘j’ or J is the Julian Date (January 1, 4713 BC), basically this date is been used for astronomical studies.
所以 to_date(:number,'j') 它把 number 代表的数字假装是儒略日期，转换成日期.
So to_date(:number,'j') it take the number represented by number and pretend it is a julian date, convert into a date.
如果你将 3 传递给数字，那么它会将日期转换为公元前 4713 年 1 月 3 日，这意味着 3 被添加到儒略日期.
If you pass 3 to number, so it will convert date to 3rd Jan 4713 BC, it means 3 is added to the Julian date.
select to_char(to_date(3,'j'),'jsp') from dual;
现在 to_char(to_date(3,'j'),'jsp'), jsp = 现在;取那个 date(to_date(3,'j')) 并拼出它所代表的儒略数，输出是:
Now to_char(to_date(3,'j'),'jsp'), jsp = Now; take that date(to_date(3,'j')) and spell the julian number it represents, the output is:
TO_CH
-----
three
使用儒略日期有一个限制，它的范围是从 1 到 5373484.这就是为什么如果你把值放在 5373484 之后，它会抛出一个错误，如下所示:
There is a limitation while using Julian dates ,It ranges from 1 to 5373484. That’s why if you put the values after 5373484, it will throw you an error as shown below:
ORA-01854: julian date must be between 1 and 5373484
大家好，这个话题很有趣.我记得我在 2005 年学习 Oracle 时，一位讲师要求我编写一个 PL/SQL 代码来将数字转换为文字，这是整整两页的代码.
Hi everyone, it is interesting this topic. I remember when I was learning Oracle in 2005 one of the instructor required me to write a PL/SQL code to convert numbers in words, it was a whole two pages code to reach this.
这里有一些参考资料可以帮助我们理解儒略日，这就是我们在此操作中使用字母j"或J"的原因.
Here is some reference that could help us to understand the Julian day, that is why we use the letter 'j' or 'J' during this operation.
首先有一个网站有关于如何使用 Oracle SQL 查询将数字转换为单词"的示例和说明:
First there is a website that has the example and explanation about "How To Convert Number Into Words Using Oracle SQL Query":
http://viralpatel.net/blogs/convert-number-into-words-oracle-sql-query/
如果您想了解更多关于朱利安日"的信息，请访问:
Second if you want to know more about "Julian day" go to:
http://en.wikipedia.org/wiki/Julian_day
第三，如果您想了解更多关于谁在 1583 年提出儒略日数的信息，那就是Joseph Scaliger":
Third if you want to know more about who proposed the Julian day number in 1583, it was by "Joseph Scaliger":
http://en.wikipedia.org/wiki/Joseph_Justus_Scaliger
我继续重复这些网站的其他作者所做的事情是没有意义的，这就是为什么我刚刚发布了您可以访问它们的链接并阅读您需要了解的查询以了解此类查询的工作原理:
It is not make sence for me continue repiting what another author in these websites have made, that is why I just posted the link you can access them and read what you need to understand how query like this works:
SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;
//输出:244.7834万
//Output: two million four hundred forty-seven thousand eight hundred thirty-four
相关文章