Oracle's SQL function is divided into single line functions and multi-line functions. The single line function is only valid for single records, and multi-line functions are valid for multiple records.
The single line function includes characters, numbers, date, conversions, and normal functions.
Character function example: •
Full lowercase Lower ('SQL Course') SQL COURSE •
Full case UPPER ('SQL Course') SQL Course
• The first letters capital initcap ('sql course') SQL Course stitching concat ('good', 'string') Goodstring Base Substr ('String', 1, 3) STR Asks Length Length ('String') 6
Digital function example: Round - Rounter value for rounding TRUNC - truncation. Note that the number of times the truncation and rounding can be negative. Round (45.923, 0) 45.92 Round (45.923, - 1) 50 Trunc (45.923, 2) 45.92 Trunc (45.923) 45 Trunc (45.923, -1) 40
MOD - Returns the remainder of two numbers
MOD (1600, 300) - 100
Date function: • Oracle will save the date as seven bytes of seven bytes in the internal format - century, year, month, day, hour, minute, seconds. • The default display format in Oracle is: DD - MON - YY. • You can use the system variable sysdate to get the system time. You can use the virtual table DUAL when viewing sysdate.
Example: How many months of Months_between ('01-Sep-95 ',' 11-JAN-94 ') 1.9774194 is added to the date of the date of the two days Add_Months ('11-JAN-94', 6) '11 -jul-94 'What is the next week next to next_day ('01 -sep-95', 'friday') '08 -sep-95 'Some last day last_day ('01 -sep-95') ' 30-Sep-95 '
Take the date to the month Round ('25 -May-95 ',' Month ') 01-Jun-95 Round ('25 -MAY-95', 'Year') 01-JAN-95 cut the date to the month Trunc ( '25 -May-95 ',' Month ') 01-May-95 Trunc ('25 -May-95', 'Year') 01-JAN-95
Conversion function
TO_CHAR converts the digital or date to string to_number to convert a string to digital to_date to convert a string to a date in a conversion function using a format string to_Char (Date, 'FMT') format string in '', case sensitive However, it can be any effective date format format string: YYYY indicates the full four digit year year, said English spelling the year mm, two digit months, Month, represents the year of English full of DY, showing the three abbreviated weekdays expressing English full fight Week
Some special usage: Time format HH24: MI: SS AM 15:45:32 PM Add String DD "of" MONTH 12 of October in the format string DDSPTH FOURTEENTH
TO_CHAR (NUMBER, 'FMT') Use this function to convert the digital converted into a number 9 indicate that the number 0 is enforced to 0 $ set the dollar symbol L using the current character set currency symbol. Decimal point, thousand separator
You can use TO_NUMBER to convert a string to a number to use to_date to convert a string to a format date to_date ('10 September 1992 ',' DD Month YYYY ')
Use format string to_date (char [, 'fmt'])
Ordinary function null value processing function
NVL (EXPR1, EXPR2) If EXPR1 is NULL, returns EXPR2NVL2 (Expr1, Expr2, Expr3) Returns EXPR2 if EXPR1 is non-empty, returns expr3nullif (expr1, expr2) if expr1 = expr2, return null Otherwise returning EXPR1
Coalesce (expr1, expr2, ..., exprn) Returns the first non-empty expression in the list of expressions
Conditional expression, CASE expression or decode Condition logic Case expression Case EXPR WHEN COMPARISON_EXPR1 THEN RETURN_EXPR1 [WHEN COMPARISON_EXPR2 THEN RETURN_EXPR2 WHEN COMPARISON_EXPRN THEN RETURN_EXPRN ELSE ELSE_EXPR] END
Decode function decode (Col | Expression, Search1, Result1 [, Search2, Result2, ...,] [, Default])
Group function: AVG (Distinct | All | N) Count (Distinct | All | EXPR | *) MAX (Distinct | All | EXPR) STDDEV (Distinct | All | N) SUM (Distinct | All | n) Variance (Distinct | all | n)
