ORACLE Section 3rd Single-line function

? Different types of functions in SQL? Use characters, numbers, dates, and data in a SELECT statementConversion Functions?using conditional Expressions
Two types of SQL functions

Single-line function:

? Manipulating Data Objects? Accept parameter Returns a result? Transform only one row? Each row returns a result? You can convert data types? can be nested? A parameter can be a column or a value

function_name [(arg1,arg2,... )]

Character type function:

Casing control functions

Casing Control Functions:

This type of function alters the case of characters.

LOWER

UPPER

Initcap

Explicit data-type conversions:

SELECT to_char (sysdate, 'yyyy-mm-ddhh:mi : ss') from dual;

To_char (date, ' Format_model ')

? usingto_datefunction converts a character to a number:

to_date (char[, 'format_model'])

to_date (' October 29, 2012 08:10:21 ', ' yyyy ' year "MM" month "DD" Day "Hh:mi:ss") From dual

? usingTo_numberfunction converts a character to a date:

to_number ( char [ , ' format _model '])

to_number ('￥1,234,567,890.00 ', ' l999,999,999,999.99 ') From dual

General functions:

These functions are suitable for any data type and also for null values :

? NVL (EXPR1, expr2)? NVL2 (Expr1, expr2, EXPR3)? Nullif (EXPR1, expr2)? COALESCE (EXPR1, expr2, ..., exprn)

converts a null value to a known value : (If commission_pct is empty, commission_pct isthe original value and is empty,commission_pct is expr2)

–NVL (commission_pct,0)

NVL2 (Expr1, Expr2, EXPR3): Expr1 Not for NULL , return EXPR2 ; for NULL , return EXPR3 .

SELECT last_name , Salary, NVL (commission_pct, 0),

(salary*12) + (SALARY*12*NVL (commission_pct, 0)) An_sal

from employees;

conditional expression:
–case-expression–decode function

Case expr when Comparison_expr1 then return_expr1

[ when Comparison_expr2 then RETURN_EXPR2

when comparison_exprn then RETURN_EXPRN

ELSE else_expr]

END

SELECT last_name, job_id, salary, case      job_id "  It_prog" then 1.10*salary "                St_clerk" then 1.15* Salary when                ' Sa_rep ' and then   1.20*salary       ELSE     salary END    "Revised_salary" from  employees;

Select Employee_id,last_name,department_id,case department_id when ten then salary*1.1 and then                                                              salary*1.2                                                              When and salary*1.3                                                              end "new salary" from Employeeswhere department_id in (10,20,30)

SELECT last_name , job_id , salary,

DECODE (job_id, ' It_prog ', 1.10*salary,

' St_clerk ', 1.15*salary,

' Sa_rep ', 1.20*salary,

Salary

Revised_salary

From employees;

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

ORACLE section 3rd single-line function