Seven common Oracle functions and seven oracle Functions

Seven common Oracle functions and seven oracle Functions

1. decode Function

Select id, NAME, DECODE (LEVER, 1, 'Professor ', 2, 'associate Professor', 3, 'mentor ', 'lecturer') POSITION, (select classnum from class t where t. NUM = S. NUM) num from teacher s where t. lever in (1, 2, 3 );

The usage logic of the decode function in the preceding SQL statement is as follows:

IF (LEVER = 1) then return 'Professor '(translation value 1) else if (LEVER = 2) then return 'associate Professor' (translation value 2) else if (LEVER = 3) then return 'mentor 'else return 'lecturer' (default)

The default value is optional.

2. NVL Function

SELECT ID,PERSONNAME,NVL(BOOKNUM,0) AS BOOKNUM,NVL(PERSONNUM,0) AS PERSONNUM FROM BOOKSTORE

The NVL () function of the preceding SQL statement can be understood as follows:

IF (BOOKNUM !=NULL) THEN           RETURN BOOKNUMELSE          RETURN 0

3. NVL2 Functions

SELECT ID,PERSONNAME,NVL2(BOOKNUM,BOOKNUM,0) AS BOOKNUM,NVL2(PERSONNUM,PERSONNUM,0) AS PERSONNUM FROM BOOKSTORE

The NVL2 () function of the preceding SQL statement can be understood as follows:

IF (BOOKNUM !=NULL) THEN           RETURN BOOKNAMEELSE IF(BOOKNUM == NULL)          RETURN 0

It is worth noting that if the BOOKNAME and 0 types are different, 0 will be converted to the BOOKNAME type. If the conversion fails, an error will be reported.

4. NULLIF Function

SELECT E.NAME,E.JOB_ID,J.JOB_ID,NULLIF(E.JOB_ID,J.JOB_ID) AS SAME FROM EMPLOYEES E,JOB_HISTORY J    WHERE E.EMPLOYEE_ID = J.EMPLOYEE_ID ORDER BY NAME;

The NULLIF () function of the preceding SQL statement can be understood as follows:

IF(E.JOB_ID == J.JOB_ID) THEN            RETURN NULLELSE           RETURN E.JOB_ID

5. SUM Function

SELECT ID ,CLASSNAME,SUM(NVL(RESULTS,0)) AS RESULTS,SUM(DECODE(TYPE,'50',0,1)) AS CLASSTYPE FROM CLASS

The SUM () function of the preceding SQL statement is mainly used to SUM the values returned by expressions in parentheses or the SUM of a field in the table.

6. SUBSTR Function

Select substr (CLASSNAME, 0) as classname, SUBSTR ("The weather is sunny today! ", 3, 2) from class;

The SUBSTR () function of the preceding SQL statement is mainly used to intercept a part of a string. For example, SUBSTR (CLASSNAME, 0) indicates to intercept a CLASSNAME string starting from 0th bytes, SUBSTR ("today's weather is sunny",) indicates that two bytes of "today's weather is sunny" are intercepted starting from the third byte, and the result is "Weather ".

7. TO_CHAR Function

SELECT S.ID,S.NAME,TO_CHAR(S.TIME,'yyyy-mm-dd hh24:mi:ss'),TO_CHAR(S.MONEY,'999') AS MONEY    FROM STUDENT S

The SUBSTR () function of the preceding SQL statement converts numeric or date fields into numeric fields to facilitate operations similar to fuzzy queries.