Oracle function Info list（Oracle函数列表速查）
PL/SQL single line function and group function detailed explanation
A function is a program that has zero or more arguments and has a return value. In SQL, Oracle constructs a series of functions that can be called SQL or PL/SQL statements, and functions are divided into two broad categories:
Single-Row Functions
Group function
This article will discuss how to use single function and use rules.
Single function in SQL
SQL and PL/SQL come with many types of functions, such as characters, numbers, dates, conversions, and mixed functions. They are used to process single line data, so these can be collectively referred to as one-way functions. These functions can be used in SELECT, WHERE, ORDER, BY, etc., for example, the following example contains the TO_CHAR, UPPER, SOUNDEX and other one-way functions.
SELECT, ename, TO_CHAR (hiredate,'day, DD-Mon-YYYY'), FROM, empWhere, UPPER (ename), Like,'AL%'ORDER, BY, SOUNDEX (ename)
The one-way function can also be used in other statements, such as the SET update clause, the VALUES clause INSERT, WHERE clause
DELET, certification exam pay special attention to the use of these functions in the SELECT statement, so our attention is also focused on the SELECT statement.
NULL and one-way functions
It's hard to start on how to understand NULL, and even an experienced person is baffled. NULL represents the value of an unknown data or a null value, any operand arithmetic operators for the NULL value, the results are all provided with a NULL value, this rule is suitable for many functions, only CONCAT, DECODE, DUMP, NVL, REPLACE can call the NULL parameter returns a non NULL value. In these, the NVL function is most important because he can process NULL values directly; NVL has two parameters: NVL (x1, x2), X1 and X2 all expressions, returns X2 when X1 is null, or returns x1.
Let's take a look at the EMP data sheet, which contains two items of salary and bonus, and the total compensation should be calculated
Column, name, emp_id, salary, bonuskey, type, nulls/unique, NN, u, nnfk, table,, datatype, number, number, numberlength, PK, 11.2, two
Instead of simply adding salaries and bonuses, if a line is a null value, the result will be null, such as the following example:
Update empset salary= (salary+bonus) *1.1
This statement, employee salaries and bonuses will be updated to a new value, but if there is no bonus, salary + null, then it will draw the wrong conclusion, this time it is necessary to use the NVL function to eliminate the effect of null value.
So the correct statement is:
Update empset salary= (salary+nvl (bonus, 0) *1.1
Single string function
Single 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 the C1, and his 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 numeric, function that returns the decimal representation of the characters
Select, CHR (65), CHR (122), CHR (223), FROM, empCHR65, CHR122, CHR223A,, Z, B
CONCAT (())
C1, C2 are string, function to connect C2 to the back of C1, if C1 is null, will return c2., if C2 is null, then return C1, if C1 and C2 are all null, then return null. He and || operators return the same result
Select, concat ('slobo,,'Svoboda') username, from, dualusernameslobo, Syoboda
INITCAP ()
C1 is a string. The function will capitalize the first letter of each word and return the other letter in small case.
The word is limited by spaces, control characters, and punctuation.
oracle trunc函数的使用方法Select, INITCAP ('veni, Vedi, vici'), Ceasar, from, dualCeasarVeni, Vedi, Vici
(INSTR, [[, < I >, edit)
C1, C2 are strings, I, J are integers. The function returns the position where C2 appears in C1 at J, and searches from the first I character of c1. When no character is found, 0 is returned. If I is negative, then the search proceeds from right to left, but the location is computed from left to right, and 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
INSTRB ([, i[, j])
Just like the INSTR () function, except that he returns the byte, and for the single byte INSTRB () equal to INSTR ()
LENGTH ()
C1 is a string that returns the length of the C1. If C1 is null, then the null value is returned.
Select, LENGTH ('Ipso, Facto'), ergo, from, dualergo10
LENGTHb ()
Like LENGTH (), returns the byte.
Lower ()
Returns the lower case character of C, often in the where substring
Select, LOWER (colorname), from, itemdetail, WHERE, LOWER (colorname), LIKE,'%white%'COLORNAMEWinterwhite
LPAD (< < I > []])
C1, C2 are strings, and I is integers. The length of I with the C2 string up to the left side of the C1, which can be repeated,

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。