Detailed explanation of PL/SQL single-row functions and group functions A function is a program that has zero or more parameters and a return value. Oracle has a series of built-in functions in SQL,
These functions can be called SQL or PL/SQL statements. Functions are mainly divided into two categories:
Single Row Function Group Functions This article discusses how to utilize single row functions and the rules for their use.
Single row function in SQL
SQL and PL/SQL come with many types of functions, including character, numeric, date, conversion, and mixed functions for processing single rows of data.
Therefore these can be collectively referred to as single-row functions. These functions can be used in SELECT, WHERE, ORDER BY and other clauses,
For example, the following example contains single-line functions such as TO_CHAR, UPPER, and SOUNDEX.
SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')
FROM emp
Where UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename)
Single-row functions can also be used in other statements, such as the SET clause of update, the VALUES clause of INSERT, and the WHERE clause of DELET.
The certification exam pays special attention to the use of these functions in the SELECT statement, so our attention is also focused on the SELECT statement.
NULL and Single-Row Functions Understanding NULL can be difficult at first, and even a very experienced person can still be confused by it.
The NULL value represents an unknown data or a null value. Any operand of an arithmetic operator is a NULL value, and the result is a NULL value.
This rule also applies to many functions. Only CONCAT, DECODE, DUMP, NVL, and REPLACE can return non-NULL values when called with NULL parameters.
Among these, the NVL function is the most important because it can handle NULL values directly.
NVL has two parameters: NVL(x1,x2), both x1 and x2 are expressions. When x1 is null, X2 is returned, otherwise x1 is returned.
Let's take a look at the emp data table. It contains salary and bonus. We need to calculate the total compensation.
column name
emp_id salary bonuskey type
pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2
Instead of simply adding up the salary and bonus, if a certain row has a null value, the result will be null, such as the following example:
update empset salary=(salary+bonus)*1.1
In this statement, the employee's salary and bonus will be updated to a new value, but if there is no bonus,
That is, salary + null, then a wrong conclusion will be drawn. At this time, the nvl function must be used to eliminate the influence of null values.
So the correct statement is:
update empset salary=(salary+nvl(bonus,0)*1.1
Single-line string functions Single-line string functions are used to manipulate string data. Most of them have one or more parameters, and most of them return strings.
ASCII()
c1 is a string, returns the ASCII code of the first letter of c1, and its inverse function is CHR()
SELECT ASCII('A') BIG_A,ASCII('z') BIG_z
FROM empBIG_A BIG_z65 122
CHR(<i>)[NCHAR_CS]
i is a number and the function returns the decimal representation of the character
select CHR(65),CHR(122),CHR(223)
FROM empCHR65 CHR122 CHR223A z B
CONCAT(,)
Both c1 and c2 are strings. The function connects c2 to the back of c1. If c1 is null, c2 will be returned. If c2 is null, c1 will be returned.
If both c1 and c2 are null, return null. It returns the same result as operator ||
select concat('slobo ','Svoboda') username
from dualusernameslobo Syoboda
INITCAP()
c1 is a string. The function returns the first letter of each word in uppercase and the other letters in lowercase. Words are composed of spaces, control characters,
Punctuation restrictions.
select INITCAP('veni,vedi,vici') Ceasar
from dualCeasarVeni,Vedi,Vici
INSTR(,[,＜i＞[,]])
c1 and c2 are both strings, i and j are integers. The function returns the position of the j-th occurrence of c2 in c1, and the search starts from the i-th character of c1.
When the required character is not found, 0 is returned. If i is a negative number, the search will be performed from right to left, but the position is still calculated from left to right.
The default value for i and j is 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])
The same as the INSTR() function, except that it returns bytes. For single-byte INSTRB() is equal to INSTR()
LENGTH()
c1 is a string, and the length of c1 is returned. If c1 is null, a null value will be returned.
select LENGTH('Ipso Facto') ergo
from dualergo10
LENGTHb()
Like LENGTH(), returns bytes.
lower()
Returns the lowercase character of c, which often appears in the where substring.
select LOWER(colorname)
from itemdetail
WHERE LOWER(colorname) LIKE '%white%'COLORNAMEWinterwhite
LPAD(,＜i＞[,])
c1 and c2 are both strings, and i is an integer. Use c2 string to complement the length i on the left side of c1, which can be repeated multiple times. If i is less than the length of c1,
Then only c1 characters as long as i will be returned, and the others will be truncated. The default value of c2 is a single space, 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(,＜i＞[,])
Use the c2 string to complement the length i on the right side of c1, which can be repeated multiple times. If i is less than the length of c1, then only c1 characters as long as i will be returned.
Others will be truncated. The default value of c2 is a single space, and the 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, and c3 are all strings. The function replaces c2 that appears in c1 with c3 and returns.
select REPLACE('uptown','up','down')
from dualREPLACEdowntown
STBSTR(,＜i＞[,])
c1 is a string, i and j are integers, and a substring of length j is returned starting from the i-th position of c1, or until the end of the string if j is empty.
select SUBSTR('Message',1,4)
from dualSUBSMess
SUBSTRB(,<i>[,])
It is roughly the same as SUBSTR, except that I and J are calculated in bytes.
SOUNDEX()
Returns words that sound similar to c1
select SOUNDEX('dawes') Dawes SOUNDEX('daws') Daws, SOUNDEX('dawson')
from dualDawes Daws DawsonD200 D200 D250
TRANSLATE(,,)
Replace the same characters in c1 as c2 with c3
select TRANSLATE('fumble','uf','ar') test from dualTEXTramble
TRIM([[]] from c3)
Delete the first, last, or both in the c3 string.
select TRIM(' space padded ') trim from dual TRIMspace padded
UPPER()
Returns the uppercase version of c1, which often appears in the where substring.
select name from dual where UPPER(name) LIKE 'KI%'NAMEKING
Single-row numeric functions Single-row numeric functions operate on numeric data, performing mathematical and arithmetic operations. All functions take numeric parameters and return numeric values.
The operands and values of all trigonometric functions are radians rather than angles. Oracle does not provide a built-in conversion function for radians and angles.
ABS()
Returns the absolute value of n
ACOS()
The inverse cofactor function returns a number between -1 and 1. n represents radians
select ACOS(-1) pi,ACOS(1) ZERO
FROM dualPI ZERO3.14159265 0
ASIN()
Anyway, the mysterious function returns -1 to 1, n represents radians
ATAN()
Arctangent function, returns the arctangent value of n, where n represents radians.
CEIL()
Returns the smallest integer greater than or equal to n.
COS()
Returns the co-value of n, where n is radians
COSH()
Returns the hyperbolic cofactor of n, where n is a number.
select COSH(<1.4>)
FROM dualCOSH(1.4)2.15089847
EXP()
Returns the nth 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 n1 to base n2
MOD()
Returns the remainder of n1 divided by n2,
POWER(,)
Returns n1 raised to the n2 power
ROUND(,)
Returns the value of n1 rounded to n2 places to the right of the decimal point. The default value of n2 is 0. This time, the nearest integer to the decimal point is rounded.
If n2 is a negative number, it is rounded to the corresponding digit to the left 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, -1 is returned, if n is a positive number, 1 is returned, and if n=0, 0 is returned.
SIN()
Returns the positive value of n, where n is radians.
SINH()
Returns the hyperbolic positive value of n, where n is radians.
SQRT()
Returns the square root of n, where n is radians
TAN()
Returns the tangent of n, where n is radians
TANH()
Returns the hyperbolic tangent of n, where n is radians
TRUNC(,)
Returns the value of n1 truncated to n2 decimal places. The default setting of n2 is 0. When n2 is the default setting, n1 will be truncated to an integer.
If n2 is a negative value, it is truncated at the corresponding position to the left of the decimal point.
Single row date function
The single-line date function operates on the DATA data type, and most of them have parameters of the DATA data type.
Most of the values returned are also DATA data type values.
ADD_MONTHS(,＜i＞)
Returns the result of date d plus i months. i can be any integer. If i is a decimal,
Then the database will implicitly convert it into an integer and will truncate the part after the decimal point.
LAST_DAY()
Function returns the last day of the month containing date d
MONTHS_BETWEEN(,)
Returns the number of months between d1 and d2, if the dates of d1 and d2 are the same, or both are the last day of the month,
Then an integer will be returned, otherwise the returned result will contain a fraction.
NEW_TIME(,,)
d1 is a date data type. When the date and time in time zone tz1 is d, return the date and time in time zone tz2.
tz1 and tz2 are strings.
NEXT_DAY(,)
Returns the first day following date d given by dow, which specifies the day of the week using the language given in the current session,
The time component returned 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([,])
Round date d according to the format specified by fmt, which is a string.
SYADATE
The function takes no parameters and returns the current date and time.
TRUNC([,])
Returns the date d in the units specified by fmt.
Single-row conversion function Single-row conversion function is used to operate multiple data types and convert between data types.
CHARTORWID()
c makes a string, and the function converts c to the RWID data type.
SELECT test_id
from test_case
where rowid=CHARTORWID('AAAA0SAACAAAALiAAA')
CONVERT(,[,])
c tail string, dset and sset are two character sets. The function converts the string c from the sset character set to the dset character set.
The default setting of sset is the character set of the database.
HEXTORAW()
x is a hexadecimal string, and the function converts the hexadecimal x into a RAW data type.
RAWTOHEX()
x is a RAW data type string, and the function converts the RAW data type into a hexadecimal data type.
ROWIDTOCHAR()
Function converts ROWID data type to CHAR data type.
TO_CHAR([[,)
x is a data or number data type. The function converts x into a char data type in the format specified by fmt.
If x is a date nlsparm= NLS_DATE_LANGUAGE controls the language used for the month and day returned.
If x is a number nlsparm=NLS_NUMERIC_CHARACTERS is used to specify decimal and thousand separators, as well as currency symbols.
NLS_NUMERIC_CHARACTERS="dg", NLS_CURRENCY="string"
TO_DATE([,[,)
c represents a string, and fmt represents a string in a special format. Returns c displayed in fmt format, and nlsparm indicates the language used.
The function converts the string c into date data type.
TO_MULTI_BYTE()
c represents a string, and the function converts the truncation character of c into a multi-byte character.
TO_NUMBER([,[,)
c represents a string, fmt represents a string in a special format, and the function return value is displayed in the format specified by fmt.
nlsparm represents the language, and the function will return the number represented by c.
TO_SINGLE_BYTE()
Convert multi-byte characters in string c into equivalent single-byte characters.
This function is used only when the database character set contains both single-byte and multi-byte characters
Other single-line functions
BFILENAME(,)
dir is an object of directory type, and file is a file name. The function returns an empty BFILE position value indicator,
Function is used to initialize BFILE variables or BFILE columns.
DECODE(,,[,,,[])
x is an expression, m1 is a matching expression, x is compared with m1, if m1 is equal to x, then r1 is returned, otherwise, x is compared with m2,
And so on m3, m4, m5.... until the result is returned.
DUMP(,[,[,[,]]])
x is an expression or character, and fmt represents octal, decimal, hexadecimal, or a single character.
The function returns a value of type VARCHAR2 that contains information about the internal representation of x.
If n1,n2 are specified then bytes of length n2 starting from n1 will be returned.
EMPTY_BLOB()
This function has no parameters and returns an empty BLOB position indicator. Function used to initialize a BLOB variable or BLOB column.
EMPTY_CLOB()
This function has no parameters and returns an empty CLOB position indicator. Function is used to initialize a CLOB variable or CLOB column.
GREATEST()
exp_list is a list of expressions, returning the largest expression. Each expression is implicitly converted to the data type of the first expression.
If the first expression is of any of the string data types, then the returned result is of the varchar2 data type,
The comparison used at the same time is a non-space-filled type of comparison.
LEAST()
exp_list is a list of expressions, returning the smallest expression among them. Each expression is implicitly converted to the data type of the first expression.
If the first expression is of any of the string data types, the result returned is of the varchar2 data type,
The comparison used at the same time is a non-space-filled type of comparison.
UID
This function has no parameters and returns an integer that uniquely identifies the current database user.
USER
Returns the username of the current user
USERENV()
Based on opt return contains current session information. Optional values for opt are:
The SYSDBA role responds in the ISDBA session and returns TRUE.
SESSIONID returns the audit session identifier
ENTRYID returns available audit entry identifiers
INSTANCE Returns the instance identifier after the session is connected.
This value is only used if you are running a Parallel server and have multiple instances.
LANGUAGE returns the character set of language, region, and database settings.
LANG returns the ISO abbreviation of the language name.
TERMINAL Returns the operating system identifier for the terminal or computer used by the current session.
VSIZE()
x is an expression. Returns the number of bytes internally represented by x.
Group functions in SQL are also called aggregate functions. They return a single result based on multiple rows. The exact number of rows cannot be determined.
Unless the query is executed and all results are included. Unlike single-row functions, all rows are known at parse time.
Because of this difference, group functions have slightly different requirements and behavior than single-row functions.
Group (multi-row) functions Compared with single-row functions, Oracle provides a rich set of group-based, multi-row functions.
These functions can be used in select or the having clause of select, and are often used with GROUP BY when used to select substrings.
AVG([{DISYINCT|ALL}])
Returns the average of numeric values. The default setting 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 rows in the query. The default setting is ALL, * means returning all rows.
MAX([{DISTINCT|ALL}])
Returns the maximum value of the selection list item. If x is a string data type, it returns a VARCHAR2 data type.
If X is a DATA data type, returns a date, if X is a numeric data type, returns a number.
Note that distinct and all have no effect, the maximum value should be the same for both settings.
MIN([{DISTINCT|ALL}])
Returns the minimum value of a selection list item.
STDDEV([{DISTINCT|ALL}])
Returns the standard deviation of the selector's list items, where the standard deviation is the square root of the variance.
SUM([{DISTINCT|ALL}])
Returns the sum of the numeric values of the select list items.
VARIANCE([{DISTINCT|ALL}])
Returns the statistical variance of a select list item.
Use GROUP BY to group data. As the title suggests, the group function operates on data that has been grouped.
We tell the database how to group or classify data using GROUP BY. When we use the group function in the SELECT clause of the SELECT statement,
We must place grouped or non-constant columns in the GROUP BY clause. If group by is not used for special processing,
Then the default classification is to set the entire result into one category.
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 to classify; if we want to sort the results according to zip_codes, we can use the ORDER BY statement,
The ORDER BY clause can use column or group functions.
select stat,counter(*) zip_count
from zip_codes
GROUP BY state ORDER BY COUNT(*) DESC;
ST COUNT(*)----------NY 4312PA 4297TX 4123CA 3982
Limit grouped data with HAVING clause
Now that you know how to use the main function in the SELECT statement and ORDER BY clause of a query, the group function can only be used on two substrings.
Group functions cannot be used in WHERE substrings. For example, the following query is wrong:
mistake
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. When we need to instruct the database to group rows and then limit the output of the grouped rows,
The correct way 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 operands have an inheritable execution process.
But the priority of functions is only based on position, and functions follow the principle from inside to outside and from left to right.
Nesting technology is generally used for functions like DECODE that can be used in logical judgment statements IF...THEN...ELSE.