Oracle to_char () function has powerful functions

1. The Oracle to_char () function is powerful, but pay attention to the following items when formatting numeric data. If it is a decimal number, such as: 0.23, the data goes through to_char and then

 
 

  
  

<Strong onmouseover = "isShowAds = true; isShowAds2 =
True; ads. Move (this, "http://rad.17luntan.com/
UploadImage/17/633111011619218750 .gif ","
% U89C1 % u6240 % u672A % u89C1 % uFF01 % u62A2 % u8D2DLG
% U9510 % u6BD4 % u6DB2 % u6676 % uFF0C % u8D60 % u65E0 % u7EBF % u5927
% U793C % 21 "," 42887 "," show "," % u663E % u793A % 20 "," http % 3A //
210.192.100.35/11779028996160 http % 3A //
Www.lgemonitor.com.cn/mnt/index.html?3f11779028996160 ",
Event) "style =" FONT-WEIGHT: normal; CURSOR: hand; COLOR:
# 0000ff; TEXT-DECORATION: underline "onclick =
"Javascript: window. open
("Http://s0.17luntan.com/ClickPortal/WebClick.aspx? Id =
42887 & k = % u663E % u793A & siteid =
95d6d193-1fb9-4fc0-8708-b7ceb3276924 & url = http % 3A //
Wisebo.bokee.com/viewdiary.15801227.html&gourl=http%3A //
210.192.100.35/11779028996160 http % 3A //
Www.lgemonitor.com.cn/mnt/
Index.html % 3F11779028996160 & parm =
C794e0a4a49df5fda-ea88008fd215212b465427a50ddcdb &
Alliedsiteid = 148 ");" onmouseout = "isShowAds =
False; isShowAds2 = false ">
 
 

The display will change. 23. If you want to display the original 0.23, you must use To_char (the value to be formatted, '0. 999 ') Keep three decimal places and display them, but pay attention to them here. He rounded down the number of decimal places for you. Therefore, if you want to cut off decimals without rounding them, you should write a function to cut them down before normalization. To ensure that it is not rounded.

2. Oracle To_char (1.9999, 'fm90. 0999 ') when this function is normalized, 90.0999 indicates that 9 indicates that a number is displayed if no number is displayed, where there is 0, there will also be 0 to placeholder when there is no number. but this also has a major drawback, that is, if it is an integer, it will still display ". ", don't underestimate this point. Generally, this point is redundant if it is to be displayed on the page. it also caused us a lot of trouble. you need to write the function again to fix this small point.

3. A good solution is provided for Oracle inverted time of the date type. You can convert the date into a numeric type, and then To_char will display the type you need.

4. When using select into, be sure to use this method only when data is identified. if the query result is null, an error is returned. another case is that multiple pieces of data will also report an error. therefore, the cursor should be made as cheap as possible. this reduces the probability of errors.

5. Note that rownum does not support sorting, that is, if you want to use this to control the number of rows, you will find that it is not displayed in the order you specified ., this is a very difficult task. and if you use a statement like rownum = 2 to output the second line, it will not work.

However, Oracle's null judgment is abnormal to the extreme. if you say a variable aa = null, it cannot be determined. although aa is indeed empty. it cannot be determined even in the selection conditions. I don't know why, so I had to use the nvl () function to judge. in addition to the condition, aa is null can be used to determine.

Six. when writing a stored procedure, note that the parameter name cannot be the same as the database field name. otherwise, Oracle regards this parameter name as the field name, even if you use the table alias to distinguish it. so pay attention to this when starting the parameter name.

The above content is about the powerful function of Oracle to_char () function, but please pay attention to the introduction when formatting numeric data with it. I hope you will gain some benefits.