Oracle---. Oracle Functions

Numeric type functions:

Absolute:

ABS (x) "function" returns the absolute value of x "parameter" x, numeric Expression "return" number "example"  Select ABS (+), ABS ( -100) from dual;
-----------------------------------------------------------------------

Positive negative value

Sign (x) "function" returns the positive negative value of X "parameter" x, numeric Expression "return" number, if positive value returns 1, negative value returns -1,0 returns 0 "example" Select sign (+  ), sign ( -100), sign (0) from dual;

-----------------------------------------------------------------------

Ceil (x) "function" returns the smallest integer value greater than or equal to X "parameter" x, numeric Expression "return" number "example"  Select Ceil (3.1), Ceil (2.8+1.3), ceil (0) from dual; return 4,5,0

-----------------------------------------------------------------------

Floor (x) "function" returns the maximum integer value less than or equal to X "parameter" x, numeric Expression "return" number "example"  Select Floor (3.1), Floor (2.8+1.3), floor (0) from dual; return 4,5,0

-----------------------------------------------------------------------

Power (x, y) "function" returns X's y power "parameter" x, y numeric Expression "return" number "example"  Select Power (2.5,2), Power (1.5,0), Power (20,-1) from dual; return: 6.25, 1,0.05

-----------------------------------------------------------------------

"Close" exp (y) returns the Y power of E. (e is a mathematical constant) "relationship" Z=power (x, y), then Y=1/log (z,x) (condition z,x>0)

Exp (y) "function" returns the Y power of E (E is a mathematical constant) "parameter" y, the numeric expression "returns" the number "example"  Select exp (3), exp (0), exp ( -3) from dual; return: 20.0855369,1, 0.049787068 "near" power (x, y) returns the Y power of E. Instead, ln (y) returns the natural logarithm of e as the base.

-----------------------------------------------------------------------

Log (x, y) "function" returns the logarithmic "parameter" x, y, numeric expression of the bottom of the number, "condition" x, y must be greater than 0 "return" number "example"  Select Power (4,2), log (16,2), 1/log (16,4) from Dual; return: 16,0.25,2  Select Power (6.5,3), log (274.625,3), 1/log (Power (6.5,3), 6.5) from dual; return:  274.625,   0.195642521   ,           3 "near" ln (y) returns the logarithm of the base y of E. (e is a mathematical constant) "relationship" Z=power (x, y), then Y=1/log (z,x)   (condition z,x>0)

-----------------------------------------------------------------------

MoD (x, y) "function" returns x divided by y remainder "parameter" x, y, numeric expression "return" number "example"  Select mod (23,8), mod (24,8) from dual; return: 7,0

-----------------------------------------------------------------------

Round (X[,y]) "function" returns the rounded value "parameter" x, y, numeric expression, if Y is not an integer intercept y integer part, if y>0 is rounded to y decimal place, if Y is less than 0 rounded to the decimal point to the left y bit. "Returns" Number "Example"  select Round (5555.6666,2.1), round (5555.6666,-2.6), round (5555.6666) from dual; return:   5555.67     ,    5600    ,    5556 "Close" trunc (X[,y]) returns the truncated value, using the same round (X[,y]), just not rounding

-----------------------------------------------------------------------

Trunc (X[,y]) "function" returns x by precision y intercept the value "parameter" x, y, numeric expression, if Y is not an integer intercept y integer part, if y>0 intercept to y decimal place, if Y is less than 0 intercept to the decimal point to the left Y, decimal before the other data 0 is indicated. "Returns" Number "Example"  select Trunc (5555.66666,2.1), trunc (5555.66666,-2.6), Trunc (5555.033333) from  dual; return: 5555.66                    5500               5555 "Close" round (X[,y]) returns the truncated value, using the same trunc (X[,y]), just to do rounding

-----------------------------------------------------------------------

sqrt (x) "function" returns the square root of x "parameter" x-numeric Expression "return" number "example"  select sqrt (8), sqrt (ten) from dual; return:, 3.16227766

Sin (x) "function" returns the sinusoidal value of a number "example" Select Sin (1.57079) from dual; Returns:  1

-----------------------------------------------------------------------

"Function" returns the cosine of a given number

-----------------------------------------------------------------------

Return:-1 COSH (x)










"Example" SELECT ASIN (0.5) from dual;





"Example" Select Atan (1) from dual; Returns: 0.78539816

Date function:

-----------------------------------------------------------------------

Sysdate "function": Returns the current date. "Parameters": no parameters, no parentheses "return": Date "Example" select Sysdate  Hz from dual; return: 2008-11-5

Add_months (D1,N1) "function": Returns the new date based on the date D1 plus N1 months. "Parameters": D1, Date type, n1 numeric "return": Date "Example" Select Sysdate,add_months (sysdate,3)  Hz from dual; return: 2008-11-5,2009-2-5

-----------------------------------------------------------------------

Last_day (D1) "function": Returns the date D1 the last day of the month. "Parameters": D1, Date Type "return": Date "Example" Select Sysdate,last_day (sysdate)  Hz from dual; return: 2008-11-5,2008-11-30

-----------------------------------------------------------------------

Months_between (D1,D2) "function": Returns the number of months between the date D1 to the D2 of the date. "Parameter": d1,d2 Date Type "return": number if D1>D2, returns positive if d1<d2, returns negative numbers "Example" Select Sysdate,months_between (Sysdate,to_date (' 2006-01-01 ', ' yyyy-mm-dd '), Months_between (sysdate,to_date (' 2016-01-01 ', ' Yyyy-mm-dd ')) from dual; return: 2008-11-5, 34.16,-85.84

-----------------------------------------------------------------------

New_time (DT1,C1,C2) "function": Gives time DT1 in C1 time zone corresponding C2 time zone date and time "parameter": dt1,d2 Date Type "return": DateTime "parameter": c1,c2 corresponding time zone and its abbreviated     Atlantic Standard Time: AST or ADT     Alaska _ Hawaii Time: HST or HDT     UK Daylight Time: BST or BDT     United States mountainous times: MST or MDT     US Central Time zone: CST or CDT     Newland Standard Time: NST     EST or EDT     Pacific Standard Time: PST or PDT Greenwich Mean time:     GMT     Yukou Standard Time: Yst or Ydt "Example" select To_char (sysdate, ' yyyy.mm.dd Hh24:mi:ss ') Bj_time,to_char (New_time (sysdate, ' PDT ', ' GMT '), ' yyyy.mm.dd hh24:mi:ss ') los_angles from dual; return: Bj_time             los_angles--------------------------------------2008.11.05 20:11:58 2008.11.06 03:11:58 "Example" select Sysdate bj_ Time,new_time (sysdate, ' PDT ', ' GMT ') los_angles from dual; return: Bj_time             los_angles------------------------------- -------2008-11-05 20:11:58 2008-11-06 03:11:58

-----------------------------------------------------------------------

Round (D1[,C1]) "function": gives the date D1 the first day of the period (parameter C1) after rounding the date (similar to the value rounding) "parameter": D1 date Type, C1 as character (parameter), C1 default is J (that is, last 0 point date) "Parameter table" : C1 corresponding parameter table: Last 0 Point date: Cancel parameter C1 or J recent Sunday: day or dy or D date of month: month or Mon or mm or RM last quarter date: Q Latest beginning Date: Syear or year or yyyy or yyy or yy or y (multiple y means precision)  recent century at the beginning date: CC or SCC "return": Date "Example" Select Sysdate date, round (sysdate) Last 0 point date, round (sysdate, ' Day ') last Sunday, round (sysdate, ' Month ') Recent early month, round (sysdate, ' Q ') the latest quarter date, round (sysdate, ' year ') recent beginning date from dual;

Trunc (D1[,C1]) "function": Returns the date D1 the first day of the period (parameter C1) Date "parameter": D1 date Type, C1 as character (parameter), C1 default is J (that is, the current date) "Parameter table": C1 corresponding parameter table: the last 0 point date: Cancel parameter C1 or J recent Sunday: day or Dy or D (Weekly order: Days, one, two, three, four, five, six) date of month: month or Mon or mm or RM recent season date: Q Latest beginning Date: Syear or year or yyyy or yyy or yy or Y ( Multiple y for Precision)  Recent century Date: CC or SCC "return": Date "Example" Select Sysdate date, trunc (sysdate) today's date, trunc (sysdate, ' Day ') this week Sunday, Trunc ( Sysdate, ' month ') earlier this month, trunc (sysdate, ' Q ') at the beginning of the season, Trunc (Sysdate, ' year ') the beginning of the date from dual;

-----------------------------------------------------------------------

Next_day (D1[,C1]) "function": return date D1 in next week, Day of the week (parameter C1) Date "parameter": D1 date Type, C1 as character (parameter), C1 default is J (i.e. current date) "Parameter table": C1 counterpart: Monday, Tuesday, Wednesday ... Sunday "return": Date "Example" Select Sysdate then date, Next_day (sysdate, ' Monday ') next week Monday, Next_day (sysdate, ' Tuesday ') next week Tuesday, Next_day (Sysdate, ' Wednesday ') Next Wednesday, Next_day (sysdate, ' Thursday ') next week Thursday, Next_day (sysdate, ' Friday ') next week Friday, Next_day (sysdate, ' Saturday ') next week Saturday, Next_day ( Sysdate, ' Sunday ') next Sunday from dual;

-----------------------------------------------------------------------

Extract (c1 from D1) "function": Date/Time D1, parameter (c1) value "parameter": D1 date Type (date)/datetime type (timestamp), C1 for character type (parameter) "Parameter table": C1 corresponding parameter table See example "return": Character " Example "Select Extract (Hour from timestamp ' 2001-2-16 2:38:40 ') hours, extract (minute from timestamp ' 2001-2-16 2:38:40 ') minutes, ex Tract (second from timestamp ' 2001-2-16 2:38:40 ') seconds, extract (Day from timestamp ' 2001-2-16 2:38:40 '), Extract (MONTH fro M timestamp ' 2001-2-16 2:38:40 ') month, extract (from timestamp ' 2001-2-16 2:38:40 ') years from Dual;select extract (year F Rom Date ' 2001-2-16 ') from dual; Select Sysdate Current date, extract (hour from timestamp timestamp sysdate) hours, extract (day from sysdate) days, extract (MONTH from SYSD ATE) month, extract (year from sysdate) years from dual;

-----------------------------------------------------------------------

Localtimestamp "function": Returns the date and time "parameter" in the session: No arguments, no parentheses "return": Date "Example" select Localtimestamp from Dual; return: 1 April-November-08 12.35.37.453000 Morning

-----------------------------------------------------------------------

Current_timestamp "function": Returns the current date "parameter" in the current session timezone with the timestamp with time zone data type: No arguments, no parentheses "return": Date "Example" select Current_timestamp from dual; return: 1 April-November-08 12.37.34.609000 am +08:00

-----------------------------------------------------------------------

Current_date "function": Returns the current date "parameter" in the current session time zone: No arguments, no parentheses "return": Date "Example" select Current_date from dual; return: 2008-11-14

-----------------------------------------------------------------------

Dbtimezone "function": Returns the time zone "parameter": no parameters, no parentheses "return": Character type "Example" select Dbtimezone from dual;

-----------------------------------------------------------------------

Sessiontimezone "function": Returns the session time zone "parameter": no arguments, no parentheses "return": Character type "Example" select Dbtimezone,sessiontimezone from dual; return: +00:00   + 08:00

-----------------------------------------------------------------------

INTERVAL C1 Set1 "function": variable datetime value "parameter": C1 is a numeric string or datetime string, Set1 is a date parameter "parameter table": Set1 Specific Reference example "return": Numeric value of datetime format, number of preceding + Number is borrowed in days or days in smaller units, such as 1 for 1 days, 1/24 for 1 hours, and 1/24/60 for 1 minutes "example" Selecttrunc (sysdate) + (interval ' 1 ' second),--plus 1 seconds (1/24/60/60 ) trunc (sysdate) + (interval ' 1 ' minute),--plus 1 minutes (1/24/60) trunc (sysdate) + (interval ' 1 ' hour),--Add 1 hours (1/24) trunc (sysdate) + (INTERVAL ' 1 ' Day),  --plus 1 days (1) trunc (sysdate) + (INTERVAL ' 1 ' MONTH),--plus January trunc (sysdate) + (INTERVAL ' 1 ' year),-- Plus 1 years trunc (sysdate) + (interval ' 01:02:03 ' hour to second),--plus specify hours to seconds trunc (sysdate) + (interval ' 01:02 ' minute to second),-- Plus specify minutes to seconds trunc (sysdate) + (interval ' 01:02 ' hour to minute),--plus specify hours to minutes trunc (sysdate) + (interval ' 2 01:02 ' day to minute)--add finger Fixed days to minutes from dual;

Oracle---. Oracle Functions