function | An explanatory function is a program that has 0 or more parameters and has a return value. In SQL, Oracle builds a series of functions that can be called SQL or PL/SQL statements, and functions fall into two main categories:
Single line function
Group functions
This article discusses how to take advantage of a single-line function and use rules.
Single-line functions in SQL
There are many types of functions in SQL and pl/sql, such as characters, numbers, dates, transformations, and hybrids, which are used to process single-line data, so these can be collectively referred to as Single-line functions. These functions can be used in select,where, order BY, and other clauses, such as the following example contains the To_char,upper,soundex and other single-line functions.
SELECT Ename,to_char (hiredate, ' day,dd-mon-yyyy ')
From EMP
Where UPPER (ename) like ' al% '
Order by SOUNDEX (ename)
A single-line function can also be used in other statements, such as the SET clause of UPDATE, the values clause of INSERT, the WHERE clause of the delet, and the authentication exams pay special attention to using these functions in the SELECT statement, so our attention is focused on the SELECT statement.
NULL and Single-line functions
It is very difficult to begin with how to understand null, even a very experienced person is still puzzled by it. A null value indicates an unknown or null value, and any operand of an arithmetic operator is a null value, and the result is a null value, and the rule is suitable for many functions, only CONCAT,DECODE,DUMP,NVL, Replace can return a non-null value when a null parameter is invoked. The most important thing in these NVL functions is that he can handle null values directly, NVL has two parameters: Nvl (x1,x2), X1 and X2 are all expressions, return x1 When X2 is null, or return x1.
Now let's take a look at the EMP datasheet that contains the salary, bonus two items, which need to calculate the total compensation
Column Name emp_id salary bonus
Key Type PK
Nulls/unique Nn,u nn
FK table
DataType Number Number number
Length 11.2 11.2
Instead of simply adding up the salary and bonuses, if a row is a null value, the result will be null, as in the following example:
Update emp
Set salary= (Salary+bonus) *1.1
In this statement, the employee's wages and bonuses will be updated to a new value, but if there is no bonus, i.e. salary + NULL, then the wrong conclusion will be drawn, and the NVL function should be used to exclude the effect of the null value.
So the correct statement is:
Update emp
Set salary= (SALARY+NVL (bonus,0) *1.1
Single line string function
Single-line string functions are used to manipulate string data, most of them have one or more parameters, most of which return strings
ASCII ()
C1 is a string that returns the ASCII code of the first letter of C1, and his inverse function is Chr ()
SELECT ASCII (' A ') big_a,ascii (' z ') big_z from EMP
Big_a big_z
65 122
CHR (<i>) [Nchar_cs]
I is a number, the function returns the decimal representation of the character
Select CHR, CHR (122), CHR (223) from EMP
CHR65 CHR122 CHR223
A Z B
CONCAT (,)
C1,C2 are strings, the function connects C2 to the C1, and returns C2 if C1 is null. If C2 is null, returns C1, or null if c1, c2 are null. He and operator | | Returned the same result
Initcap ()
C1 is a string. The function returns the first letter of each word to uppercase in the other letter lowercase. Words are restricted by spaces, control characters, and punctuation marks.
Select Initcap (' Veni,vedi,vici ') Ceasar from dual
Ceasar
Veni,vedi,vici
INSTR (, [, <i> [,]])
C1,C2 are both strings and i,j as integers. The function returns the position of the C2 in the C1, and the search begins with the first character of C1. When you don't find the word you need characters return 0, if I is negative, then the search will go from right to left, but the position is calculated from left to right, and I and J have the default value of 1.
Select INSTR (' Mississippi ', ' I ', 3,3) from dual
INSTR (' Mississippi ', ' I ', 3, 3)
11
Select INSTR (' Mississippi ', ' I ', -2,3) from dual
INSTR (' Mississippi ', ' I ', 3, 3)
2
INSTRB (, [, I[,j])
As with the InStr () function, it is just that he returns a byte, and for a single byte InStrB () equals InStr ()
LENGTH ()
C1 is a string that returns the length of the C1 and, if C1 is null, returns a null value.
Select LENGTH (' Ipso Facto ') ergo from dual
Ergo
10
LENGTHB ()
Returns the byte, as in length ().
Lower ()
Returns lowercase characters for C, which often appear in the Where substring
Select LOWER (colorname) from Itemdetail WHERE LOWER (colorname) like '%white% '
ColorName
Winterwhite
Lpad (, <i> [,])
C1,C2 are all strings, I is an integer. On the left side of the C1, a C2 string is used to make up the length I, which can be repeated several times, if I is less than the length of the C1, then only return I so long C1 characters, others will be truncated. The default value for C2 is a single space, see Rpad.
Select Lpad (answer,7, ') Padded,answer unpadded from question;
Padded unpadded
Yes Yes
No no
Maybe Maybe
LTRIM (,)
Remove the leftmost character in the C1 so that the first character is not in the C2, and if there is no C2, then C1 will not change.
Select LTRIM (' Mississippi ', ' Mis ') from dual
LTR
Ppi
Rpad (, <i> [,])
On the right-hand side of the C1, a C2 string is used to make up the length I, which can be repeated several times, if I is less than the length of the C1, then only returns I that long C1 character, the others will be truncated. The default value for C2 is single space, others are similar to Lpad
RTRIM (,)
Remove the rightmost character in the C1 so that the first character is not in the C2, and if there is no C2, then C1 will not change.
REPLACE (, [,])
C1,C2,C3 are strings, and functions are returned with C3 instead of C2 appearing in C1.
Select REPLACE (' Uptown ', ' Up ', ' down ') from dual
REPLACE
Downtown
Stbstr (, <i> [,])
C1 is a string, I,j is an integer, and returns a substring of J at the beginning of the C1, if J is empty, until the tail of the string.
Select SUBSTR (' message ', 1,4) from dual
Subs
Mess
SUBSTRB (, <i> [,])
Roughly the same as substr, except that the i,j is calculated in bytes.
SOUNDEX ()
Return words similar to C1 pronunciation
D200 D200 D250
TRANSLATE (,,)
Replace the characters in the C1 with the C2 in C3
Select TRANSLATE (' Fumble ', ' uf ', ' ar ') test from dual
TEXT
Ramble
TRIM ([[]] from C3)
The first, last, or both of the C3 string will be deleted.
Select Trim (' Space padded ') trim from dual
TRIM
Space padded
UPPER ()
Returns the upper case of C1, which often appears in the Where substring
Select name from dual where UPPER (name) like ' ki% '
NAME
KING
Single-line numeric function
Single-line numeric functions manipulate numeric data and perform mathematical and arithmetic operations. All functions have numeric parameters and return numeric values. The operands and values of all trigonometric functions are radians instead of angles, and Oracle does not provide a built-in radian and angle conversion function.
ABS ()
Returns the absolute value of n
ACOS ()
Inverse of the function, return 1 to 1 of the number. n indicates radians
Select ACOS ( -1) Pi,acos (1) ZERO from dual
PI ZERO
3.14159265 0
ASIN ()
The inverse of the function, return-1 to 1,n represents radians
Atan ()
The inverse tangent function returns the tangent value of N, and N represents radians.
Ceil ()
Returns the smallest integer greater than or equal to N.
COS ()
Returns the remaining Xuan value of N, radians
COSH ()
Returns the hyperbolic residual value of n, and N is a number.
Select COSH (<1.4>) from dual
COSH (1.4)
2.15089847
EXP ()
Returns the n power of E, e=2.71828183.
FLOOR ()
Returns the largest integer less than or equal to N.
LN ()
Returns the natural logarithm of n, which must be greater than 0
LOG (,)
Returns the logarithm of a N1 base N2
MOD ()
Returns n1 divided by the remainder of the N2,
Power (,)
Returns the N2 of N1
ROUND (,)
Returns the N1 value of the N2 bit to the right of the decimal point, and the N2 defaults to 0, which rounds the nearest integer to the decimal point, if N2 is a negative number, to the corresponding bit to the left of the decimal point, and the N2 must be an integer.
Select ROUND (12345,-2), ROUND (12345.54321,2) from dual
ROUND (12345,-2) ROUND (12345.54321,2)
12300 12345.54
SIGN ()
If n is negative, returns-1, if n is positive, returns 1 if N=0 returns 0.
SIN ()
Returns the positive value of N, and N is radians.
SINH ()
Returns the hyperbolic positive value of n, and N is radians.
SQRT ()
Returns the square root of N, which is radians
TAN ()
Returns the tangent value of N, radians
TANH ()
Returns the hyperbolic tangent of N, and N is radians
TRUNC (,)
Returns the N1 value of the truncated to N2 decimal digits, n2 the default setting of 0, and when N2 is the default, the N1 is truncated to an integer, and if the N2 is negative, it is truncated to the corresponding bit to the left of the decimal point.
Single-line Date function
Single-line date functions manipulate data data types, most of which have data type parameters, and the vast majority of returns are values of data types.
Add_months (, <i>)
Returns the result of date D plus I months. I can make any integer. If I is a decimal, then the database converts the implicit form to an integer, which will intercept the part following the decimal point.
Last_day ()
function returns the last day of the month that contains the date D
Months_between (,)
Returns the number of months between D1 and D2, if the date of the D1 and D2 is the same, or if both are the last day of the month, an integer is returned, otherwise the result returned will contain a fraction.
New_time (,,)
D1 is a date data type that returns the date and time in the time zone tz2 when the date and time in the TZ1 is D. TZ1 and TZ2 strings.
Next_day (,)
Returns the first day of a condition given by Dow after date D, Dow uses the language given in the current session to specify a day of the week that returns the same time component as D.
ROUND ([,])
Rounds the date d in the format specified by FMT, FMT as a string.
Syadate
The function has no arguments and returns the current date and time.
TRUNC ([,])
Returns the date d of the unit specified by FMT.
Single-line conversion functions
Single-line conversion functions are used to manipulate multiple data types and to convert between data types.
Chartorwid ()
C makes a string that converts C to the Rwid data type.
SELECT test_id from Test_case where Rowid=chartorwid (' aaaa0saacaaaaliaaa ')
CONVERT (, [,])
C-tailed strings, Dset, Sset are two character sets, the function converts string C from the Sset character set to the Dset character set, and the Sset defaults to the database's character set.
Hextoraw ()
X is a 16-binary string that converts the 16-in-X to raw data type.
Rawtohex ()
X is the raw data type string, which converts the raw data class to a 16-binary data type.
Rowidtochar ()
function converts a ROWID data type to a char data type.
To_char ([[,)
X is a data or number datatype that converts x to the char data type in the FMT specified format, if x is the date Nlsparm=nls_date_language control the language in which the month and day of the return are used. If x is the numeric nlsparm=nls_numeric_characters used to specify the decimal and thousand separator characters, and the currency symbol.
nls_numeric_characters = "DG", nls_currency= "string"
To_date ([, [,]
C represents a string, FMT represents a special form of string. Returns the language used by the C,nlsparm represented in FMT format. function converts the string C to a date data type.
To_multi_byte ()
C represents a string that converts a burden-truncated character of C into multibyte characters.
To_number ([, [,]
C represents a string, FMT represents a special format string, and the function return value is displayed in the format specified by FMT. Nlsparm represents the language, and the function returns the number represented by C.
To_single_byte ()
Converts a multibyte character in string C to an equivalent single-byte character. This function is used only if the database character set contains both Single-byte and multibyte characters.
Other Single-line functions
Bfilename (
,)
Dir is an object of a directory type, file name. function returns an empty bfile positional value indicator, which is used to initialize bfile variables or bfile columns.
DECODE (,, [,,, [])
X is an expression, M1 is a matching expression, X is compared to M1, if M1 equals X, then return r1, otherwise, X and M2, and so on m3,m4,m5 .... Until there is return result.
DUMP (, [, [, [,]]]
X is an expression or character that FMT represents 8, 10, 16, or single characters. function returns a value that contains the VARCHAR2 type of internal representation information about X. If N1,N2 is specified, the byte with a length of N2 starting from N1 will be returned.
Empty_blob ()
The function has no arguments, and the function returns an empty BLOB position indicator. function to initialize a BLOB variable or BLOB column.
Empty_clob ()
The function has no arguments, and the function returns an empty CLOB position indicator. function to initialize a CLOB variable or CLOB column.
Greatest ()
Exp_list is a column expression that returns the largest expression, each of which implicitly converts the data type of the first expression, and if the first expression is any of the string data type, the result returned is the VARCHAR2 data type. Comparisons that are used at the same time are non padded space types.
Least ()
Exp_list is a column expression that returns the smallest expression in which each expression is implicitly converted to the data type of the first expression, and if the first expression is any of the string data type, the result returned is the VARCHAR2 data type. Comparisons that are used at the same time are non padded space types.
Uid
The function has no arguments and returns an integer that uniquely identifies the current database user.
USER
Returns the user name of the current user
USERENV ()
Returns the containing current session information based on OPT. Optional values for opt are:
Isdba a sysdba-foot-color response in a session, returns true
SESSIONID return Audit Session identifier
ENTRYID returns the available audit entry identifiers
INSTANCE returns the instance identifier after the session is connected. This value is used only when running the parallel server and with multiple instances.
LANGUAGE returns the character set for language, geography, and database settings.
LANG returns the ISO abbreviation for the language name.
TERMINAL returns the operating system identifier for the terminal or computer used for the current session.
Vsize ()
X is an expression. Returns the number of bytes represented within X.
Group functions in SQL
Group functions, also called aggregate functions, return a single result based on multiple rows, and the exact number of rows cannot be determined unless the query is executed and all the results are included. Unlike a single-line function, all rows are known at parse time. Because of this difference, there is a slight difference in the requirements and behavior between the group function and the Single-line function.
Group (multiline) function
Oracle provides a rich array-based, multiline function compared to a single-line function. These functions can be used in the HAVING clause of a select or select and are often used with group by when used for select substrings.
AVG ([{disyinct| All}])
Returns the average of a numeric value. The default setting is all.
COUNT ({*| Distinct| All})
Returns the number of rows in the query, the default setting is all,* to return all rows.
MAX ([{distinct| All}])
Returns the maximum value for a select list item, if x is a string data type, he returns a VARCHAR2 data type, if x is a data type, returns a date, if X is a numeric data type, returns a number. Note that distinct and all do not work, and the maximum value should be the same as the two settings.
MIN ([{distinct| All}])
Returns the minimum value for a select list item.
StdDev ([{distinct| All}])
Returns the standard deviation of the list item for the selected person, and the so-called standard deviation is the square root of the variance.
SUM ([{distinct| All}])
Returns the sum of the values for the select list item.
Variance ([{distinct| All}])
Returns the statistical variance of the select list item.
grouping data with GROUP by
As the topic implies, the group function is to manipulate the data that has been grouped, and we tell the database how to group or categorize the data by group by, and when we use group functions in the SELECT clause of the SELECT statement, we must place the grouping or the very series in the GROUP BY clause, If you do not specialize with GROUP BY, the default classification is to set the entire result to a class.
Select Stat,counter (*) Zip_count from Zip_codes GROUP by state;
ST Zip_count
-- ---------
AK 360
AL 1212
AR 1309
AZ 768
CA 3982
In this example, we classify the state field, and if we want to sort the results by zip_codes, we can use the order BY statement and the ORDER BY clause to work with the column or group function.
Select Stat,counter (*) Zip_count from Zip_codes GROUP by state ORDER by Count (*) DESC;
ST COUNT (*)
-- --------
NY 4312
PA 4297
TX 4123
CA 3982
Restricting grouped data with HAVING clause
Now you know that. Using the main function in a SELECT statement and an ORDER BY clause in a query, a group function can only be used in two substrings, and a group function cannot be used in a where substring, for example, the following query is wrong:
Error
SELECT Sales_clerk,sun (sale_amount) from Gross_sales WHERE sales_dept= ' OUTSIDE ' and SUM (Sale_amount) >10000 GROUP by Sales_clerk
In this statement, the database does not know what sum is, and when we need to instruct the database to group rows and then limit the output of the grouped rows, the correct approach is to use the having statement:
SELECT Sales_clerk,sun (Sale_amount)
From Gross_sales
WHERE sales_dept= ' OUTSIDE '
GROUP by Sales_clerk
Having SUM (Sale_amount) >10000;
Nested functions
Functions can be nested. The output of one function can be the input of another function. The operand has an inheritable execution process. But the precedence of the function is based only on the position, and the function follows the principle from the inside out to the left to the right. Nested techniques are generally used in such a way as decode can be used for logical judgment statements if .... THEN ... else's function.
Nested functions can include nested single-line functions in a group function, or a group function nested into a single line function or group function. For example, the following examples:
SELECT Deptno, Greatest (count (DISTINCT job), COUNT (DISTINCT Mgr) CNT,
COUNT (DISTINCT job) jobs,
COUNT (DISTINCT Mgr) Mgrs
From EMP
GROUP by Deptno;
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
