PL / SQL single line function and group function detailed
A function is a program with zero or more parameters and has a return value. In SQL, Oracle built a set of functions, which can be called SQL or PL / SQL statements, and the function is mainly divided into two categories:
Single line function
Group function
This article will discuss how to use a single line function and use rules.
Single line function in SQL
SQL and PL / SQL bring a lot of types of functions, characters, numbers, date, conversions, and hybrids and other functions for processing single line data, so they can be collectively referred to as a single line function. These functions can be used in SELECT, WHERE, ORDER BY, etc., for example, in the following examples contain single line functions such as TO_CHAR, UPPER, SOUNDEX.
SELECT ENAME, TO_CHAR (HIREDATE, 'DAY, DD-MON-YYYY') from Empwhere Upper (ENAME) LIKE 'Al%' Order by Soundex (ENAME)
The single line function can also be used in other statements, such as Update's set clause, INSERT's VALUES clause, DELET's WHERE clause, special attention of the certification exam, use these functions in the SELECT statement, so our attention is also concentrated in the SELECT statement in.
NULL and single line functions
It is very difficult to start on NULL, even if a very experienced person is still confused. NULL value represents an unknown data or an empty value, any of the operands of the arithmetic operator is a null value, and the result is a null value. This rule is also suitable for many functions, only Concat, Decode, Dump, NVL, Replace is called A non-NULL value can be returned when NULL parameters. The most important thing when these NVL functions is because he can directly process NULL values, NVL has two parameters: NVL (x1, x2), x1, and x2 all-form expression, return x2 when X1 is NULL, otherwise returns x1 .
Let's take a look at the EMP data table contains two salary, bonuses, and requires the total compensation.
COLUMN NAME EMP_ID SALY BONUSKEY TYPE PK NULLS / UNIQUE NN, U NNFK TABLE DATATYPE NUMBER NUMBER NUMBERLENGTH 11.2 11.2
It is not simple to add salary and bonus. If a row is a null value, the result will be NULL, such as the example below:
Update Empset Salry = (Salary Bonus) * 1.1
In this statement, employees' wages and bonuses will be updated to a new value, but if there is no bonus, it is Salary Null, then the error conclusion will be obtained. At this time, the NVL function will be used to exclude the impact of NULL values. .
So the correct statement is:
Update Empset Salry = (Salary NVL (Bonus, 0) * 1.1
Single line string function
Single string functions are used to operate string data, most of them have one or more parameters, most of which return strings
ASCII ()
C1 is a string, returns a C1 first letter ASCII code, his inverse function is CHR ()
SELECT ASCII ('a') BIG_A, ASCII ('Z') BIG_Z FROM EMPBIG_A BIG_Z65 122
CHR () [nchar_cs]
i is a number, the function returns the character of decimal representation
SELECT CHR (65), CHR (122), CHR (223) from Empchr65 ChR122 CHR223A Z B
Concat (,)
All of C1, C2 are strings, and the function connects C2 to C1, if C1 is null, will return C2. If C2 is NULL, return C1, if C1, C2 is null, then return null. He and the operator || Return to the same Select Concat ('Slobo', 'Svoboda') UserName from DualUsernameslobo Syoboda
INITCAP ()
C1 is a string. The function returns the first letter of each word to other letters lowercase. Words are limited by spaces, control characters, punctuation.
Select Initcap ('Veni, Vedi, Vici') Ceasar from Dualceasarveni, Vedi, Vici
INSTR (, [, [,]])
C1, C2 are both strings, i, j is an integer. The function returns the position where C2 appears at the J2 of C1, and the search begins with the i-th character from C1. When there is no desired character, return 0, if i is a negative number, the search will be performed from right to left, but the calculation of the position is from left to right, I and J of 1.
Select INSTR ('Mississippi', 'I', 3, 3) from Dualinstr ('mississippi', 'i', 3, 3) 11Select Instr ('mississippi', 'i', - 2, 3) from Dualinstr (' Mississippi ',' I ', 3, 3) 2
INSTRB (, [, i [, j])
Like the INSTR () function, just he returns bytes, for single-byte instib () equal to INSTR ()
Length ()
C1 is a string, returns the length of C1, if C1 is null, then the NULL value will be returned.
SELECT Length ('IPSo Facto') Ergo from Dualergo10
Lengthb ()
Like Length (), return bytes.
Lower ()
Returns the lowercase character of C, often appears in the WHERE string
SELECT LOWER (ColorName) from itemdetail where lower (colorname) Like '% White%' ColorNamewinterwhite
LPAD (, [,])
C1, C2 are both a string, i is an integer. In the left side of C1, the C2 string is added to the length I, which can repeat multiple times, if i is less than the length of C1, then only returns i so long C1 characters, and the other will be cut off. The default value of C2 is single spacer, see RPAD.
SELECT LPAD (Answer, 7, '') Padded, Answer Unpadded from Question; Padded Unpadded Yes Yesno Nomaybe Maybe
Ltrim (,)
Remove the leftmost character in C1, so that the first character is not in C2, if there is no C2, then C1 will not change.
SELECT LTRIM ('mississippi', 'mis') from dualltrppi
RPAD (, [,])
In the right side of C1, the C2 string is added to the length i, which can repeat multiple times, if i is less than the length of C1, then only returns i so long C1 characters, and others will be cut. The default value of C2 is single spacer, and others are similar to LPAD
RTRIM (,)
Remove the rightmost characters in C1, so that the last character is not in C2, if there is no C2, then C1 will not change. REPLACE (, [,])
Both C1, C2, and C3 are strings, and the function is replaced with C3 to return after C2 in C1.
SELECT Replace ('Uptown', 'Up', 'Down') from DualReplacedowntown
Stbstr (, [,])
C1 is a string, i, j is an integer, start returning a sub-string of j on the I, if j is empty, then the tail of the string is until the tail of the string.
SELECT SUBSTR ('Message', 1, 4) from Dualsubsmess
Substrb (, [,])
The same is substantially the same as SUBSTR, but i, J is based on byte.
Soundex ()
Return the word similar to the C1 pronunciation
SELECT SOUNDEX ('Dawes') Dawes Soundex ('Daws') Daws, Soundex ('Dawson') from Dualdawes Daws Dawsond200 D200 D250
Translate (,,)
Replace the same character as C2 in C2 in C3
SELECT TRANSLATE ('Fumble', 'Uf', 'AR') Test from DualTextRamble
Trim ([[]] from C3)
Delete the first, last one, or all of the C3 string.
SELECT TRIM ('Space Padded') Trim from Dual Trimspace Padded
Upper ()
Returns the uppercase of C1, often appears in the WHERE string
Select Name from Dual Where Upper (Name) Like 'Ki%' Nameking
Single line digital function
Single line digital functions operate digital data, perform mathematics and arithmetic operations. All functions have numeric parameters and returns a digital value. All the operands and values of all triangle functions are radians rather than angles, and Oracle does not provide built-in radians and angles of conversion functions.
ABS ()
Returns the absolute value of N
ACOS ()
The anti-mystery function returns to the number of -1 to 1. n represents an arc
SELECT ACOS (-1) Pi, ACOS (1) Zero from DualPi ZERO3.14159265 0
Asin ()
Any normal mystery function, returning -1 to 1, n represents the radia
Atan ()
Anytute cleaning function, returning N of the anterior correction value, n represents the radians.
CEIL ()
Returns the minimum integer greater than or equal to N.
COS ()
Returns the Yu Xuan value of N, n is an arc
Cosh ()
Returns the hypotonics of n, n is a number.
SELECT COSH (<1.4>) from Dualcosh (1.4) 2.15089847
Exp ()
Returns the n power of E, E = 2.71828183.
Floor ()
Returns the maximum integer that is less than or equal to N.
Ln ()
Returns Natural logarithm of N, n must be greater than 0
Log (,)
Returns the logarithm of N1
MOD ()
Returns N1 divided by the remainder of N2,
Power (,)
Returns N2 of N1
Round (,)
Returns the value of N1 of the N2-bit N2 bit of the decimal point, the default value of N2 is 0, which is the most close to the decimal point. If the N2 is rounded to the corresponding position on the left side of the decimal point, N2 must be an integer.
Select Round (12345, -2), Round (12345.54321, 2) from Dualround (12345, -2) Round (12345.54321, 2) 12300 12345.54 Sign ()
If n is a negative number, return -1, if n is a positive number, returns 1, if n = 0 returns 0.
Sin ()
Returns the nirange value of N, n is an arc.
Sinh ()
Returns the hyperbolic nature value of n, n is the curvature.
SQRT ()
Returns the square root of N, n is an arc
Tan ()
Returns the positive cut value of N, n is an arc
Tanh ()
Returns the hyperbolic value of N, n is an arc
Trunc (,)
Returns the value of N1 of the N2-bit decimal, the N2 default setting is 0, when N2 is an integer, the N1 cut is an integer, if the N2 is negative, and the corresponding position on the left side of the decimal point is on the left side of the decimal point. .
Single line date function
The single-line date function operation DATA data type, most of which have the parameters of the DATA data type, and most of the returns are also the value of the DATA data type.
Add_months (, )
Returns the date D plus the result of the month. i can make any integer. If i is a decimal, then the database will be implicitly converted into an integer, which will cut the part behind the decimal point.
Last_day ()
Function returns the last day of the month containing the date D
Months_between (,)
Returns the number of months between D1 and D2, if the date of D1 and D2 is the same, or the last day of the month, then an integer will be returned, otherwise the result will contain a score.
New_time (,,)
D1 is a date data type. When the date and time in the time zone TZ1 is D, the date and time in the time zone TZ2 is returned. TZ1 and TZ2 strings.
Next_day (,)
On the first day of the condition given by DOW after the date D, DOW specifies one day in the previous session, and the returned time component is the same as the time component of D.
Select next_day ('01-Jan-2000 ',' Monday ') "1st Monday", Next_DAY ('01-Nov-2004', 'Tuesday') 7 "2nd Tuesday") from Dual; 1st Monday 2nd Tuesday03-Jan -2000 09-NOV-2004
Round ([,])
Return the date D to the format specified by the FMT, FMT is a string.
Syadate
The function does not have a parameter, returns the current date and time.
Trunc ([,])
Returns the date d of the unit specified by the FMT.
Single line conversion function
The single-row conversion function is used to operate a multi-numerical type and convert between data types.
ChartorWid ()
c Make a string, the function converts C to the RWID data type.
Select Test_id from test_case where rowid = chartorwid ('aaaa0saacaaaaliaaaa)
Convert (, [,])
C tail strings, DSET, SSET are two character sets, and the function converts the string C by the SSET character set to the DSET character set, and the SSET's default setting is a character set of the database.
HEXTORAW ()
X is a 16-based string, and the function converts 16-entered X to the RAW data type.
Rawtohex ()
X is the RAW data type string, and the function converts the RAW data class into a 16-based data type.
Rowidtochar ()
The function converts the ROWID data type to a CHAR data type.
TO_CHAR ([[[,)
X is a DATA or NUMBER data type, and the function converts X to the CHAR data type of FMT specified format, if X is the date nlsparm = nls_date_language controls the language returned and the language used in the dial. If x is digital NLSPARM = NLS_NUMERIC_CHARACTERS to specify a separate and minibitial separator, as well as currency symbols. NLS_NUMERIC_CHARACTERS = "DG", NLS_CURRENCY = "String"
TO_DATE ([, [,)
c represents a string, and the FMT represents a string of special format. Returns the language that is displayed in the FMT format to represent the language used by NLSPARM. The function converts the string C into a DATE data type.
TO_MULTI_BYTE ()
c Represents a string, the function converts the burning of C to multiple byte characters.
TO_NUMBER ([, [,)
c represents a string, FMT represents a string of a special format, and the function return value is displayed in the format specified by the FMT. NLSPARM represents the language, the function will return the number of C.
TO_SINGLE_BYTE ()
Convert the string C to the multi-byte character to the equivalent single-byte character. This function is only used when the database character set contains both single bytes and multi-byte characters.
Other single line functions
BfileName
,
Dir is an object of a Directory type, file is a document name. The function returns an empty BFile location value indicator, and the function is used to initialize the BFile variable or a bfile column.
Decode (, ", [,, [])
X is an expression.
Dump (, [, [,]]])
X is an expression or character, and the FMT represents 8 credits, 10 encompasses, 16 envelopes, or single characters. The function returns a value of a VARCHAR2 type containing the internal representation of the X. If N1 is specified, N2 then the byte of the length from N1 is N2 will be returned.
EMPTY_BLOB ()
This function does not have a parameter, and the function returns an empty BLOB position indicator. The function is used to initialize a BLOB variable or a blob column.
EMPTY_CLOB ()
This function does not have a parameter, and the function returns an empty Clob position indicator. The function is used to initialize a CLOB variable or a Clob column.
Greatest ()
Exp_List is an expression that returns the largest expression, each expression is implicitly converted to the data type of the first expression, if the first expression is any one in the string data type, then The result of the returned is the VARCHAR2 data type, and the comparison of the use is a comparison of non-filled spaces.
Least ()
Exp_List is an expression that returns to the smallest expression, each expression is implied, the first expression of the first expression, if the first expression is any of the string data type, will The result of the returned is the VARCHAR2 data type, and the comparison of the use is a comparison of non-filled spaces.
UID
This function does not have a parameter, and returns an integer that is uniquely launched by the current database user.
User
Returns the username of the current user
Userenv ()
The OPT returns to contain current session information. Opt options are:
Sysdba feet in the ISDBA session, returns true
SessionID Returns the audit session marker
EntryID Returns the available audit item marker
After instance returns an instance marker after the session is connected. This value is only used to run the Parallel server and have multiple instances.
Language returns the character set for language, region, database settings.
LANG returns an ISO abbreviation for language name.
Terminal returns the logger of the operating system for the terminal or computer used by the current session. VSIZE ()
X is an expression. Returns the number of bytes indicated inside the X.
Group functions in SQL
The group function is also called a set function, returns a single result based on multiple rows, and the exact number of rows cannot be determined unless the query is executed and all results are included. Unlike the single line function, all the rows are known in parsing. Since this difference makes a group function and a single line function has little difference in requirements and behavior.
Group (multi-line) function
Oracle provides a rich group-based, multi-line functions compared to the single line function. These functions can be used in the Having clause of SELECT or SELECT, often used with Group By when used for SELECT substrings.
AVG ([{DISYINCT | All}])
Returns the average value of the value. The default is set to ALL.
SELECT AVG (SAL), AVG (ALL SAL), AVG (Distinct Sal from Scott.empavg (SAL) AVG (All Sal) AVG (Distinct Sal 1877.94118 1877.94118 1916.071413
Count ({* | DISTINCT | All})
Returns the number of striking inquiry, the default setting is all, * indicates that all rows are returned.
Max ([{DISTINCT | All}])
Returns the maximum value of the selection list, if x is a string data type, he returns a varchar2 data type, if x is a DATA data type, return a date, if x is the Numeric data type, return a number. Note that DistINCT and ALL do not work, the maximum value is the same as both settings.
MIN ([{Distinct | All}])
Returns the minimum value of the selection list item.
STDDEV ([{Distinct | all}])
Returning the standard deviation of the list item of the selector, the so-called standard difference is the square root of the variance.
SUM ([{Distinct | all}])
Returns the sum of the values of the selection list item.
Variance ([{DISTINCT | All}])
Returns the statistical variance of the selection list item.
Group By to data group
As the title implies, the group function is to operate the data that has been divided into groups. We tell the database how to give the data group or classify how Group By, when we use the group function in the SELECT clause of the SELECT statement, we must be grouping Or a very few columns are placed in the group BY clause, if there is no special processing with Group By, then the default classification is set to a class.
Select Stat, Counter (*) zip_count from zip_codes group by state; st zip_count-- --------- AK 360Al 1212ar 1309az 768ca 3982
In this example, we use the state field classification; if we want to sort the results, you can use the Order by statement, the Order BY clause can use the column or group function.
Select Stat, Counter (*) zip_count from zip_codes group by state order by count (*) desc; st count (*) - -------- NY 4312PA 4297TX 4123CA 3982
Use a Having clause to limit packet data
Now you already know that the SELECT statement and the ORDER BY clause uses the main function, the group function can only be used in two substrings, and the group function cannot be used in the WHERE substring, such as 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 This statement does not know what, when we need to indicate database pairs, then restrict group When the output of the line is output, the correct method is to use the HAVING statement:
SELECT SALES_Clerk, Sun (Sale_Amount) from gross_sales where sales_dept = 'outside' group by sales_clerkhaving sum (sales_amount)> 10000;
Nested function
The function can be nested. The output of a function can be an input to another function. The operand has a can inherit. But the priority of the function is only based on the location, the function follows the principle of left to right, by the principle of left to right. Nesting techniques are generally used in functions such as Decode to be used for logic judgment statements if .......
Nested functions can include nested single rows in group functions, or group functions in a single line function or group function. For example, the example below:
Select Deptno, Greatest (Count (Distinct Mgr) CNT (Distinct Mgr) CNT (Distinct Job) Jobs, Count (Distinct Mgr) mgrsfrom EmpGroup by DePTNO; DePTNO CNT JOBS MGRS ------ --- - ---- 10 4 4 220 4 3 430 3 3 2
