Pl/sql single-line functions and Group functions

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

1, a single function in SQL

There are many types of functions in SQL and pl/sql, such as characters, numbers, dates, transformations, and hybrids, which are used to process single-line data, so these can be collectively referred to as Single-line functions. These functions can be used in select,where, order BY, and other clauses, such as the following example contains the To_char,upper,soundex and other single-line functions.

SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM emp Where UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename)

A single-line function can also be used in other statements, such as the SET clause of UPDATE, the values clause of INSERT, the WHERE clause of the delet, and the authentication exams pay special attention to using these functions in the SELECT statement, so our attention is focused on the SELECT statement.

NULL and Single-line functions

It is very difficult to begin with how to understand null, even a very experienced person is still puzzled by it. A null value indicates an unknown or null value, and any operand of an arithmetic operator is a null value, and the result is a null value, and the rule is suitable for many functions, only CONCAT,DECODE,DUMP,NVL, Replace can return a non-null value when a null parameter is invoked. The most important thing in these NVL functions is that he can handle null values directly, NVL has two parameters: Nvl (x1,x2), X1 and X2 are expressions, return x1 When X2 is null, or return x1.

Now let's take a look at the EMP datasheet that contains the salary, bonus two items, which 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 bonuses, if a row is a null value, the result will be null, as in the following example:

update emp set salary=(salary+bonus)*1.1

In this statement, the employee's wages and bonuses 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 emp set salary=(salary+nvl(bonus,0)*1.1

Single line string function

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

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, the function returns the decimal representation of the character

select CHR(65),CHR(122),CHR(223) FROM empCHR65 CHR122 CHR223A z B

CONCAT (,)

C1,C2 are strings, the function connects C2 to the C1, and returns C2 if C1 is null. If C2 is null, returns C1, or null if c1, c2 are null. He and operator | | Returned the same result

select concat('slobo ','Svoboda') username from dualusernameslobo Syoboda