Netsuite Formula > Oracle Function List quick check (PL/SQL single-line functions and group functions). txt

PL/SQL single-line functions and group functions
A function is a program that has 0 or more parameters and has a return value. Oracle has built a series of functions in SQL, which can be called SQL or PL/SQL statements, and functions are divided into two main categories:
Single-line function

Group functions

This article discusses how to take advantage of single-line functions and usage rules.

Single-line functions in SQL

There are many types of functions, including characters, numbers, dates, transformations, and blends, that are used to process single-line data, so these can be collectively referred to as single-line functions. These functions can be used in clauses such as Select,where, ORDER by, etc., such as the following example, which includes a single-line function such as To_char,upper,soundex.
SELECT Ename,to_char (hiredate, ' day,dd-mon-yyyy ') from Empwhere UPPER (ename) as ' al% ' ORDER by SOUNDEX (ename)

A single-line function can also be used in other statements, such as the SET clause of the update, the insert's values clause, the WHERE clause of the delet, and the certification exam takes special care to use these functions in the SELECT statement, so our attention is also focused on the SELECT statement.

Null and single-line functions

It is difficult to begin with the understanding of NULL, even if a very experienced person is still confused. A null value represents an unknown number or a null value, and any one operand of the arithmetic operator is a null value, and the result is a null value, which is also suitable for many functions, only CONCAT,DECODE,DUMP,NVL, Replace can return a non-null value when a null parameter is called. The NVL function is most important in these, because he can handle null values directly, NVL has two parameters: NVL (x1,x2), X1 and X2 expressions, X1 is returned when X2 is NULL, otherwise returns X1.

Let's take a look at the EMP data sheet, which contains two of the salary, the bonus, and 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 row is a null value then 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, 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 Empset salary= (SALARY+NVL (bonus,0) *1.1
Single-line String functions

Single-line string functions are used to manipulate string data, most of which have one or more parameters, most of which return a string

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 empbig_a big_z65 122

CHR () [Nchar_cs]
I is a number, and the function returns the character in decimal notation
Select Chr, Chr (122), Chr (223) from empCHR65 CHR122 chr223a z B

CONCAT (,)
C1,C2 are strings, the function connects C2 to the back of C1, and if C1 is NULL, it returns C2. If C2 is null, C1 is returned, and NULL is returned if C1, C2 are null. He and the operator | | Returns the same result
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 other letters in lowercase. Words are restricted by spaces, control characters, punctuation marks.
Select Initcap (' Veni,vedi,vici ') Ceasar from Dualceasarveni,vedi,vici

INSTR (, [, [,]])
C1,C2 are all strings and i,j are integers. The function returns the position of C2 in C1, where the search begins with the first character of the C1. When no characters are found to return 0, if I is negative, the search will proceed from right to left, but the position is calculated from left to right, and the default value for 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])
As with the InStr () function, it is just that he returns a byte, for a single byte InStrB () equals InStr ()

LENGTH ()
C1 is a string that returns the length of the C1, and if C1 is NULL, a null value is returned.
Select LENGTH (' Ipso facto ') ergo from Dualergo10

LENGTHB ()
As with length (), returns the byte.

Lower ()
lowercase characters that return C, often appearing in the Where substring
Select LOWER (colorname) from Itemdetail WHERE LOWER (colorname) like '%white% ' colornamewinterwhite

Lpad (, [,])
C1,C2 are all strings, and I is an integer. On the left side of the C1 with the C2 string to complement the length I, can be repeated, if I is less than the length of C1, then only return I so long C1 character, the others will be truncated. The default value for C2 is 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 the C1 so that its first character is not in C2, and if there is no C2, then C1 will not change.
Select LTRIM (' Mississippi ', ' Mis ') from Dualltrppi

Rpad (, [,])
On the right side of C1 with the C2 string to complement the length I, can be repeated many times, if I is less than the length of C1, then only return I so 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 from the C1 so that its first character is not in 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 ', ' off ') from Dualreplacedowntown

Stbstr (, [,])
C1 is a string, I,j is an integer, and a substring of length j is returned starting from the first bit of the C1, if J is empty, until the end of the string.
Select SUBSTR (' Message ', 1,4) from dualsubsmess

SUBSTRB (, [,])
is roughly the same as substr, except that i,j is calculated in bytes.

SOUNDEX ()
Return words similar to C1 pronunciation
Select SOUNDEX (' Dawes ') Dawes SOUNDEX (' Daws ') Daws, SOUNDEX (' Dawson ') from Dualdawes daws DawsonD200 D200 D250

TRANSLATE (,,)
Replace the same characters in C1 with the C2 in C3
Select TRANSLATE (' Fumble ', ' uf ', ' ar ') test from dualtextramble

TRIM ([[]] from C3)
Remove the first, last, or both of the C3 strings.
Select Trim (' Space padded ') trim from dual trimspace padded

UPPER ()
Returns the uppercase of the C1, often appearing in the Where substring
Select name from dual where UPPER (name) like ' ki% ' nameking
Single-line numeric functions

Single-line numeric functions manipulate numeric data to perform mathematical and arithmetic operations. All functions have numeric parameters and return numeric values. Operation of all trigonometric functions

ABS ()
Returns the absolute value of n

ACOS ()
Inverse-remainder function, which returns the number from 1 to 1. n = radians
Select ACOS ( -1) Pi,acos (1) ZERO from Dualpi ZERO3.14159265 0

ASIN ()
Anyway, the Xuan function returns 1 to 1,n to represent radians.

ATAN ()
The inverse tangent function returns the inverse tangent of N, which represents radians.

Ceil ()
Returns the smallest integer greater than or equal to N.

COS ()
Returns the remaining value of N, n radians

COSH ()
Returns the hyperbolic remainder value of n, which 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 largest integer less than or equal to N.

LN ()
Returns the natural logarithm of n, n must be greater than 0

LOG (,)
Returns the logarithm of the bottom n2 of the N1

MOD ()
Returns the remainder of N1 divided by N2,

POWER (,)
Returns the N2 of N1

ROUND (,)
Returns the N1 value of the N2 bit to the right of the decimal point, N2 the default value of 0, this returns the nearest integer to the decimal point, and if N2 is negative, rounds to the corresponding bit to the left of the decimal point, and 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 negative, returns 1, if n is a positive number, returns 1 if N=0 returns 0.

SIN ()
Returns the positive value of N, and N is the radian.

SINH ()
Returns the hyperbolic positive value of n, and N is the radian.

SQRT ()
Returns the square root of N, where N is radians

TAN ()
Returns the tangent of N, N radians

TANH ()
Returns the hyperbolic tangent of N, N radians

TRUNC (,)
Returns the value of N1 that is truncated to N2 decimal place, N2 the default setting of 0, and when N2 is the default setting n1 truncate to an integer, and if N2 is negative, it is truncated at the corresponding bit to the left of the decimal point.

Single-line Date function

The single-line date function operates data data types, most of which have parameters of the data type, and most return the values of the data type.

Add_months (, )
Returns the result of a date D plus I months later. I can make any integer. If I is a decimal, then the database will be implicitly converted to an integer, which will intercept the part after 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 D1 and D2 is the same, or if both make the last day of the month, then 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 zone tz1 is D. TZ1 and TZ2 strings.

Next_day (,)
Returns the first day of the condition given by Dow after the date D, Dow uses the language given in the current session to specify the day of the week, and 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 ([,])
The date D is rounded in the format specified by FMT, and the FMT is a string.

Syadate
The function has no parameters and returns the current date and time.

TRUNC ([,])
Returns the date d of the unit specified by the FMT.
Single-line conversion function

A single-line conversion function is used to manipulate multiple data types and convert between data types.

Chartorwid ()
C causes a string that functions to convert C to the RWID data type.
SELECT test_id from Test_case where Rowid=chartorwid (' aaaa0saacaaaaliaaa ')

CONVERT (, [,])
The C-tailed string, Dset, Sset is two character sets, and the function converts the string C from the Sset character set to the Dset character set, and the default setting for Sset is the database's character set.

Hextoraw ()
X is a 16-based string, and the function converts the 16-binary 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-binary data type.

Rowidtochar ()
The ROWID function converts the data type to a char data type.

To_char ([[,)
X is a data or number datatype, and the function converts x to the char data type specified in FMT, if X is the date nlsparm=nls_date_language controls the language used for the returned month and day. If X is a numeric nlsparm=nls_numeric_characters used to specify the separator for the decimal and the kilobits, as well as the currency symbol.
nls_numeric_characters = "DG", nls_currency= "string"

To_date ([, [,)
C represents a string, and FMT represents a special-format string. Returns the language used by the c,nlsparm shown in the FMT format. The function converts the string C to the date data type.

To_multi_byte ()
C represents a string in which the function converts the load truncation character of C into multibyte characters.

To_number ([, [,)
C represents a string, FMT represents a specially formatted 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 multibyte characters in string C to equivalent single-byte characters. This function is used only if the database character set contains both single-byte and multibyte characters

Other single-line functions

Bfilename (
,)
Dir is a directory-type object, file is a filename. The function returns an empty BFILE position value indicator, which is used to initialize the bfile variable or the bfile column.

DECODE (,, [,,, [])
X is an expression, M1 is a match expression, X is compared to M1, if M1 equals X, then R1 is returned, otherwise, X is compared with M2, and so on m3,m4,m5 .... Until there is a return result.

DUMP (, [, [, [,]]])
X is an expression or a character, and the FMT represents 8 binary, 10 binary, 16 binary, or one-word characters. The function returns a value that contains the VARCHAR2 type for the internal representation information of X. If N1,N2 is specified, then the byte length N2 from N1 will be returned.

Empty_blob ()
The function has no arguments, and the function returns an empty BLOB position indicator. function is used to initialize a BLOB variable or BLOB column.

Empty_clob ()
The function has no arguments, and the function returns an empty CLOB position indicator. The function is used to initialize a CLOB variable or CLOB column.

Greatest ()
Exp_list is a column of expressions that returns the largest of the expressions, each of which is implicitly converted to the data type of the first expression, and if the first expression is any of the string data types, the returned result is the VARCHAR2 data type. Comparisons that are used at the same time are non-padded space types.

LEAST ()
Exp_list is a column of expressions 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 types, the result is the VARCHAR2 data type. Comparisons that are used at the same time are non-padded space types.

Uid
The function has no parameters and returns an integer that uniquely identifies the current database user.

USER
Returns the user name of the current user

USERENV ()
The opt-based return contains the current session information. Optional values for opt are:

ISDBA session SYSDBA-color response, return 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 the parallel server is running and there are multiple instances.
LANGUAGE returns the character set of the language, geography, and database settings.
LANG returns the ISO abbreviation for the language name.
TERMINAL returns the identifier of the operating system for the terminal or computer used by the current session.
Vsize ()
X is an expression. Returns the number of bytes represented inside 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 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 requirements and behavior between the group function and the single-line function.

Group (multi-line) functions

Oracle provides a rich set of group-based, multi-line functions compared to a single-line function. These functions can be used in the HAVING clause of select or select, and are often used with group by when used with select substrings.

AVG ([{disyinct| All}])
Returns the average of a numeric value. 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, with the default setting of all,*, which means 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 datatype, returns a date, and if x is the numeric data type, returns a number. Note that distinct and all do not work, and the maximum value should be the same as these two settings.

MIN ([{distinct| All}])
Returns the minimum value for the selection list item.

STDDEV ([{distinct| All}])
Returns the standard deviation of the list item for the selected person, the so-called standard deviation is the square root of the variance.

SUM ([{distinct| All}])
Returns the sum of the values of the selected list item.

VARIANCE ([{distinct| All}])
Returns the statistical variance of the selection list item.

grouping data with GROUP by

As the topic implies, the set of functions is to manipulate data that has been grouped together, and we tell the database how to group or classify the data with group by, and when we use groups in the SELECT clause of the SELECT statement, we must place the grouping or the very series in the GROUP BY clause. If the group by is not specifically handled, the default classification is to set the entire result to one 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 to classify, and if we want to sort the results by zip_codes, we can use the order BY statement, and the ORDER BY clause can work with columns 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

Restricting grouped data with a HAVING clause

Now that you know that the main function is used in the SELECT and ORDER BY clauses of the query, the group function can only be used in two substrings, and the group function cannot be used in the 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

The database does not know what sum () is in this statement, and when we need to instruct the database to group rows and then limit the output of the grouped rows, 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 (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 procedure. But the function's precedence is only based on the position, the function follows the principle from the inside out, from left to right. Nesting techniques are generally used for applications such as decode that can be used in logical judgment statements if .... Then ... else's function.

Netsuite Formula > Oracle Function List quick check (PL/SQL single-line functions and group functions). txt