The PL / SQL single line function and group function detail 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, bonus, need to calculate the total compensation Column Name EMP_ID SALARY BONUSKEY TYPE PK NULLS / UNIQUE NN, U NNFK TABLE DATATYPE 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 following example: 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 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 Select Chr (65), CHR (122), CHR (223) from Empchr65 ChR122 CHR223A Z B
CONCAT (,) C1, C2 are strings, and the function connects C2 to the rear of C1. If C1 is NULL, the C2 will return. If C2 is null, return C1, if C1, C2 is null, then return NULL . He and the operator || return result The same Select Concat ('slobo', 'svoboda') username from dualusernameslobo syobodainitcap () 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 all 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 need to find the character, return 0, if i is negative, then the search will be made from right to left, but the calculation of the position is 1.Select Instr ('mississippi "from left to right, I and J default values. , '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]) is the same as the INSTR () function, but he returns bytes, for single-byte instrb () 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 () is the same as Length (), returns bytes.
Lower () Returns the lowercase character of C, often appears in the WHERE String Select Lower (ColorName) Like '% White%' ColorNamewinterwhite
LPAD (, [,]) C1, C2 are both strings, 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 (, [,]) replenizes the length i in the right side of C1, can repeat multiple times, if i is less than the length of C1, then only returns to i so long C1 characters, other will be Intercept. The default value of C2 is single spacer, and others are similar to LPAD
RTRIM (,) remove the rightmost character in C1, so that the last character is not in C2, if there is no C2, then C1 will not change. REPLACE (, [,]) C1, C2, C3 are all strings, and the function is replaced with C3 after C2 in C1. SELECT Replace ('Uptown', 'Up', 'Down') from DualReplacedowntown
Stbstr (, [,]) C1 is a string, i, j is an integer, starting back the length of J b string from C1, and 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 (, [,]) is substantially the same as SubStr, but i, J is based on byte.
Soundex () Return to C1 pronunciation Similar words Select Soundex ('Dawes') Dawes Soundex ('Daws') Daws, Soundex ('Dawson') from Dualdawes Dawsond200 D200 D250
Translate (,) the same characters in C1 with C2 in C3 in C3, SELECT TRANSLATE ('fulble', 'uf', 'ar') Test from DualTextRamble
TRIM ([[]] from C3) puts 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 Units 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 () anti-mystery function returns to the number of -1 to 1. n Represents radians Select Acos (-1) Pi, ACOS (1) Zero from dualpi ZERO3.14159265 0
Asin () arctoctive function, return -1 to 1, n represents the radia
Atan () anteriorctimeter, returning N of the annectal cut value, n represents the curvature.
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 hypothoemark of dicing, 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, n must be greater than 0
Log (,) Return to N1 N2 logarithm
MOD () returns N1 divided by N2,
Power (,) returns N1 N2
Round (,) returns the value of N1 of the N2 bit of the decimal point, the default value of N2 is 0, which is the closer integer of the decimal point. If the N2 is rounded to the corresponding position on the left side of the decimal point, N2 must It is an integer. Select Round (12345, -2), ROM DUALROUND (12345, -2) Round (12345.54321, 2) 12300 12345.54 Sign () If n is negative, return -1, if n is positive, return 1, if n = 0 returns 0.
SiN () returns n positive mystery, n is an arc.
SINH () Returns the hyperbolic nature value of N, n is an arc.
SQRT () returns the square root of N, n is an arc
Tan () returns the nose 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, N2 default setting to 0, when N2 is set to the default setting, the N1 cut is an integer, if the N2 is negative, it is on the left side of the decimal point. The corresponding bit is on.
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 D1 and D2 date is the same date, or make the month's last day, 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 (,) The first day of the condition given by DOW after DOW, DOW specifies one day in the week, and the returned time component is the same as the time components 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 ([,]) rounds the date D to the format specified by the FMT, FMT is a string.
The Syadate function has no parameters, returns the current date and time.
Trunc ([,]) returns the date D. single-line conversion function specified by the FMT
The single-row conversion function is used to operate a multi-numerical type and convert between data types.
ChartorWID () c makes 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 is 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 to a 16-based data type.
The RowidTochar () function converts the ROWID data type to a CHAR data type. TO_CHAR ([[[,) X is a DATA or NUMBER data type, the function converts X into the char data type of the FMT specified format, if x is the date nlsparm = nls_date_language controls the language used to return the month and the language used. 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 the string, and the FMT represents a string of special formats. 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, and the function converts the burning of C to a multi-byte character.
TO_NUMBER ([, [,) C represents the string, the 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 () transforms the string C 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 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, M1 is a matching expression, X and M1 comparison, if M1 is equal to X, then returns R1, otherwise, X and M2, push M3, M4 , M5 .... until there is a return result.
Dump (, [, [, [,]]]) X is an expression or character, FMT represents 8 enrollment, 10, 16, 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 to the largest expression, each expression is implicitly converted to the first expression of the data type, if the first expression is anywhere in the string data type One, then the result of returning is the varchar2 data type, and the comparison of the simultaneous use is a comparison of non-filled spaces.
Least () exp_list is an expression that returns the smallest expression, each expression is implied in the data type of the first expression, if the first expression is anywhere in a string data type One, the result of returning is a VARCHAR2 data type, and the comparison of use is a comparison of non-filled spaces.
UID This function does not have a parameter, returns an integer that is uniquely launched by the current database user.
User returns the username of the current user
Userenv () Based on OPT Returns containing current session information. Opt options are:
In the ISDBA session, the sysdba foot response, returns True SessionID Returns the audit session mark ENTRYID Returns the available audit items INSTANCE After the session is connected, the instance marker is returned. 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 queries, the default setting is all, * indicates that all rows are returned.
Max ([{DistINCT | All}] returns the maximum value of the selection list item, 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 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}]) Returns the standard deviation of the list of selectors, 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 group data Now you already know that the SELECT statement and the ORDER BY clause uses the main function, and the group function can only be used in two substrings, and the group function cannot be used in the WHERE substring, such as below. 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
Database in this statement doesn't know what SUM () is what we need to indicate the database to the line packet, then limit the output of the line after the group, the correct method is to use the HAVING statement: select sales_clerk, sun (sales_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 following example: Select Deptno, Greatest (Count (Distinct Job) 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
