Oracle Common functions

The general To_char is such a to_char (Sysdate, ' yyyy-mm-dd hh:mi:ss am '), and there are several points of use for the book

To_char (sysdate, ' DD ') see what date it is today

To_char (sysdate, ' ww ') view this is the first few weeks of the month

To_char (sysdate, ' mm ') view this is the first few months of the year

To_char (sysdate, ' yyyy ') view year

Last_day (to_date (' 2007-02-01 ', ' yyyy-mm-dd ')) View the last day of one months, add_months (sysdate,10) View a number of months after today, Next_day (sysdate, ' Friday ') To give a date to see the latest day of the week, but this Friday if there is a problem in English, the 2nd parameter can be the number 1-7, respectively, Sunday to Saturday.

Here's a full one, check it when you need it.

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 built a series of functions, which can be called SQL or PL/SQL statements, functions are mainly divided into two categories: a single function; Group functions

This article discusses how to take advantage of a single-line function and use rules. Single-line functions 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 Empwhere 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 all 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 Empset 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 Empset salary= (SALARY+NVL (bonus,0) *1.1