Quick query of common functions in Oracle databases

Welcome to the Oracle community forum, and interact with 2 million technical staff to enter the function is a program with zero or multiple parameters and a return value. In SQL, Oracle has a series of built-in functions, which can be called SQL or PLSQL statements. functions are mainly divided into two categories: single-row functions and group functions. This article will discuss how to use single-row functions and

Welcome to the Oracle community forum and interact with 2 million technicians> A function is a program with zero or multiple parameters and a return value. Oracle has a series of built-in functions in SQL, which can be called SQL or PL/SQL statements. functions are mainly divided into two categories: single-row functions and group functions. This article will discuss how to use single-row functions and

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

A function is a program with zero or multiple parameters and a return value. Oracle has a series of built-in functions in SQL, which can be called SQL or PL/SQL statements. functions are mainly divided into two categories:

Single Row function and group Function

This article will discuss how to use single-row functions and use rules.

Single Row functions in SQL

SQL and PL/SQL contain many types of functions, including character, number, date, conversion, and hybrid functions. Therefore, these functions can be collectively called single-row functions. These functions can be used in clauses such as SELECT, WHERE, and order by. For example, the following example contains single-row functions such as TO_CHAR, UPPER, and SOUNDEX.

SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere 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. During the certification exam, pay special attention to using these functions in SELECT statements, so our attention is also concentrated in the SELECT statement.

NULL and single-row Functions

It is very difficult to understand NULL, and even a very experienced person is still confused about it. NULL indicates an unknown data or a NULL value. Any operand of the arithmetic operator is NULL, and the result is a NULL value. This rule is also applicable to many functions, only CONCAT, DECODE, DUMP, NVL, and REPLACE can return non-NULL values when the NULL parameter is called. Among these functions, NVL is the most important because it can directly process NULL values. NVL has two parameters: NVL (x1, x2), x1, and x2 expressions, if x1 is null, return X2; otherwise, return x1.

Let's take a look at the emp data table. It includes two items: 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

It is not simply to add up the salary and bonus. If a row is null, the result will be null. For example:

update empset salary=(salary+bonus)*1.1

In this statement, both the employee's salary and bonus will be updated to a new value, but if there is no bonus, that is, salary + null, the wrong conclusion will be drawn, in this case, the nvl function is used to exclude the impact of null values.

The correct statement is:

update empset salary=(salary+nvl(bonus,0)*1.1

Single-line string functions

A single-line string function is used to manipulate string data. Most of them have one or more parameters, and most of them return strings.

ASCII ()

C1 is a string and returns the ASCII code of the first letter of c1. 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. The function returns the characters in decimal format.

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

CONCAT (,)

C1 and c2 are strings. The function connects c2 to the end of c1. If c1 is null, c2. if c2 is null, c1 is returned. If c1 and c2 are both null, returns null. He and operator | the returned result is the same

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

INITCAP ()

C1 is a string. The function returns the first letter of each word in upper case and other letters in lower case. Words are limited by spaces, control characters, and punctuation marks.

select INITCAP('veni,vedi,vici') Ceasar from dualCeasarVeni,Vedi,Vici

INSTR (, [, <I> [,])

C1 and c2 are strings, and I and j are integers. The function returns the position where c2 appears for the nth occurrence of c1, and searches for the position starting from the nth occurrence of c1. If no expected character is found, 0 is returned. If I is a negative number, the search proceeds from right to left, but the position is calculated from left to right, the default values of I and j are 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

Limit B (, [, I [, j])

Like the INSTR () function, it only returns bytes. For a single byte, bytes B () equals to INSTR ()

LENGTH ()

C1 is a string and returns the length of c1. If c1 is null, null is returned.

select LENGTH('Ipso Facto') ergo from dualergo10

[1] [2] [3] [4] [5] [6]