Today, I find a text, supplementation: 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: the single line function group function will discuss how to use a single line function and the rules of use. SQL's single-line functions SQL and PL / SQL come from many types of functions, characters, numbers, date, conversions, and hybrid, etc. are used to process 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) 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 special attention to the use of these functions in the SELECT statement, so our attention is also concentrated in the SELECT statement. NULL and single-line functions are very difficult in how to understand 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 is not simple to pay salary and bonus It can be, if a row is a null value, then the result will be null, such as the following example: Update Empset Salry = (Salary Bonus) * 1.1 This statement, employee's salary and bonus will be updated to a new one 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 Units String Function Units String Function Used to operate string data, most of them have one or more parameters, most of them Returns a string 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 CONCAT (,) C1, C2 String, the function connects C2 to the back of C1, if C1 is null, will return C2. If C2 is null, return C1, if both, C2 is null, then return null. He and operator || Return Results Same SELECT Concat ('SLOBO') UserName from DualUsernameslobo Syoboda Initcap () C1 is a string. The function returns the first letter of each word to the other letter lowercase. Word by space, control character, punctuation Symbol limit .Select initcap ('Veni, Vedi, Vici') Ceasar from Dualceasarveni, VEDI, Vici INSTR (, [, [,]]) C1, C2 is a string, i, j is an integer. Function returns C2 The position of the first appearance in C1, the search starts from the i-th character from C1. When there is no need to find the required character, return 0, if i is negative, the search will be performed from right to left, but the position calculation is still From left to right, the default value of I and J is 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]) is the same as the INSTR () function, just he returned Bytes, for single-byte instrb () equal to INSTR () length () c1 is a string, return The length of the C1 is returned 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 characters of C, often appears in the WHERE substring Select Lower (ColorName) Like '% White%' ColorNamewInterwhite LPAD (, [,]) C1, C2 is 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 removed the leftmost character in C1, so that the first character is not in C2, if there is no C2, So 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, others will be cut off. The default value of C2 is single spacer, and other RTRIMs like LPAD remove the rightmost characters in C1, so that the rear 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 from the first bits of C1 to return length as j Sub strings, if j is empty, then until the tail of the string. SELECT SUBSTR ('Message', 1, 4) from Dualsubsmess Substrb (, [,]) is substantially the same as SubStr, just i, J is based on byte. Soundex () Returns the word SoundEx ('Dawes') Dawes Soundex ('Daws') Daws Soundex ('Dawson') DAWS, SOUNDEX (,) DAWS DAWSOND 200 D200 D250 Translate (,) C1 is the same as C2 The characters replace SELECT Translate Test from DualTextRamble Trim ([[]] from C3) in C3 ([]] from C3). The first, last one, or all deleted in the C3 string. SELECT TRIM ('Space Padded') Trim from Dual Trimspace Padded Upper () Returns the uppercase of C1, often appears in WHERE String Select Name from Dual Where Upper (Name) Like 'Ki%' Nameking Units Digital Function Units Digital Function Operation 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 the N, returns the number of -1 to 1 between -1 to 1. n represents radians Select ACOS (-1) Pi, ACOS (1) Zero from dualpi zero3.14159265 0 asin () arctoction, return -1 to 1, n to represent radian Atan () anterior cleaning function, return N, an interchangeable 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 the two-dimensional 玄 玄 value of the arc COSH () returns N, n is the 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 equal to N. Ln () returns Natural logarithm of N, n must be greater than 0 log (,) returns the log number of N1 to the bottom N2 Returns N1 divided by N2, and POWER (,) returns N2 Round of N1 ( , Return to the value of N1 of the N2 bit of the N2-bit on the right side of the decimal point, the default value of N2 is 0, which will close the integer of 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 positive, return 1. If n = 0 returns 0. sin () returns n positive mystery value, 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 the positive cut value of the arc TAN (), n is the two-curved positive cut value returns N of the arc TANH (), and n is an arc TRUNC (,) returns the N1 of the N2-bit decimal. N1 The value, the N2 default setting is 0, when N2 is an integer of N1, if the N2 is negative, and the corresponding position on the left side of the decimal point is cut. Single line date function single line date function Action DATA data type, most of which have parameters of the DATA data type, most returned is 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 of the date D. Months_between (,) returns the number of months between D1 and D2, if D1 and D2 date is the same, or the last day of the month, then return An integer, 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 FMT, FMT is a string. The Syadate function has no parameters, returns the current date and time. Trunc ([,]) returns the date d of the unit specified by the FMT. The single line conversion function single-line conversion function is used to operate a multi-data type and converted 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 (, [,]) C tail string, DSET, SSET is two character sets, the function converts the string C converted to Dset character set, SSET The default setting is a character set for 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, the FMT represents a special format string. 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 uses other single line functions BFileName (,) Dir is a Directory type object only when the database character set contains single-byte and multi-byte characters, and.) Dir is an object of a Directory type. 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 current user's username Userenv () Based on the OPT to return to the current session information. OPT's optional value is: The Sysdba foot response in the ISDBA session, returns True SessionID Returns the audit session marker ENTRYD 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. The group function group function in SQL 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 group functions and single line functions have tiny differences in requirement and behavior. Group (multi-line) functions provide a rich group-based, multi-line functions compared to a 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 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 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. The group function that uses group By as the topic is implied is to operate the data that has been divided into groups. We tell the database how to group or classify how Group BY will group or classify, when we use group functions in the SELECT clause of the SELECT statement We must place it in the group BY clause for grouping or very few columns. If you don't have a special processing with Group By, then the default classification is set to a class.