Oracle (iii) ------- single row Function

I always think that my Oracle notes are especially spam, but I can't tell you how to write them. I 'd like to thank you for your suggestion! There are two types of SQL functions: single-row function and multi-row function single-row function: the operation data object accepts the parameter and returns a result. Only one row is transformed. Each row returns a result. The data type can be converted. The nested parameter can be a column or a single-row value. The function can be divided into: character function: case-sensitive control functions: [lower, upper, initcap] SQL> select lower ('redy ') from dual; lower (' ------- red SQL> select upper ('red') from dual; upper ('------- redy SQL> select initcap ('red') from dual; initcap ------- red character control function [Concat, substr, length, instr, lpad | rpad, trim, replace] function result Concat ('hello', 'World') helloworldsubstr ('He Loworld',) hellolength ('helloworld') 10 instr ('helloworld', 'w') 6 (where W appears) lpad (salary, 10 ,'*') * *** 24000 rpad (salary, 10, '*') 24000 ***** trim ('H' from 'helloworld ') elloworld (excluding the first letter of H) number function round rounded to SQL> select round (45.3213, 2) from dual; round (45.3213, 2) ---------------- 45.32 trunc truncation SQL> select trunc (45.926, 2) from dual; trunc (45.926, 2) ------------- 45.92 mod evaluate the remaining SQL> select Mod (1600,300) fro M dual; Mod (1600,300) ------------- 100 Note: Dual is a 'pseudo table' that can be used to test functions and expressions. Date: Date data in Oracle actually contains two values: date and time. The default date format is DD-MON-RR. Function sysdate returns: mathematical operation of the date: l plus or minus a number on the date result is still the date. L returns the number of days of the difference between two dates. L The number can be divided by 24 to add or minus the hour in the day period. Number of months with different months_between dates SQL> selectmonths_between ('1-August-90', '2-August-90') from dual; months_between ('1-August-90 ', '2-September-90') ---------------------------------- 5.96774194add _ months add the number of months to the specified date SQL> selectadd_months (sysdate, 2) from dual; add_months (sys -------------- 13-5-12next_day specifies the next date SQL> selectnext_day (sysdate, 'tuesday') from dual; next_day (sysda -------------- 20-3 month-12last_day last date of this month SQL> Se Lectlast_day (sysdate) from dual; last_day (sysda -------------- 31-3-12 round date rounding SQL> selectround (sysdate) from dual; round (sysdate) -------------- 13-3 month-12 trunk date truncation SQL> selecttrunc (sysdate, 'month') from dual; trunc (sysdate, -------------- 01-3 month-12 Conversion Function: Invisible: oracle automatically completed conversion display: to_char function to date conversion format: l must be included in single quotes and case sensitive. L can contain any valid date format. L dates are separated by commas. SQL> select to_char (sysdate, 'yyyy-mm-dd') from dual; to_char (SY ---------- 2012-03-13 to_char function to convert numbers below are several formats that are frequently used in the to_char function: 9 ---------- number 0 ----------- 0 $ ----------- dollar character l --------- local currency symbol. -------- decimal point, -------- thousands of characters SQL> select to_char ('000000', '$2430') from dual; to_char ('24 ----------- $99,999.00 [SQL> selectto_char ('20140901 ', '$99,999.00') from dual; to_char ('12 ----------- ########### if the above situation occurs, It indicates that the first parameter is too long.] l use the to_number function to convert the character to a number: SQL> select to_number ('123', '123') from dual; to_number ('20140901', '20140901') --------------------- 123 l use the to_date function to convert characters to dates: SQL> select to_date ('2017-3-4 ', 'yyyy/MM/dd') from dual; to_date ('2017--------------- 04-3-92 General functions: These functions apply to any data type, but also to null values: l nvl (expr1, expr2) l converts a null value into a known value: l data types that can be used include date, character, and number. L function Syntax: l nvl (commission_pct, 0) l nvl (hire_date, '01-Jan-97 ') l nvl (job_id, 'no job yun ') l nvl2 (expr1, expr2, expr3) l nullif (expr1, expr2) l coalesce (expr1, expr2 ,..., exprn) SQL> select nvl2 (Comm, comm, 0) from EMP; nvl2 (Comm, comm, 0) ----------------- 0 300 500 0 1400 SQL> selectlength (first_name ), length (last_name), nullif (length (first_name), length (last_name) from employees; length (first_name) length (L Ast_name) ----------------------------------- nullif (length (first_name), length (last_name )) ---------------------------------------- 7 9 7 4 4 4 5 5 the advantage of coalesce function L coalesce compared with nvl is that coalesce can process multiple alternate values at the same time. L if the first expression is null, return the next expression and perform coalesce on other parameters. SQL> select coalesce (Comm, 10) from EMP; coalesce (Comm, 10) ----------------- 10 300 500 10 1400 conditional expressions use the if-then-else logic in SQL statements using two methods: Case expression SQL> selectlast_name, job_id, salary, 2 case job_id when 'It _ prdg 'Then 1.10 * salary 3 when 'st _ cler' then 1.15 * salary 4 when 'sa _ rep' Then 1.20 * salary 5 else salary end" revised_salary "6 from employees; decode function 1 select last_name, job_id, salary, 2 decode (job_id, 'it _ PRD G', 1.10 * salary, 3 'st _ cler', 1.15 * salary, 4 'sa _ rep ', 1.20 * salary, salary) 5 * The revised_salary fromemployees nested function can be nested with a single-row function. The execution sequence of nested functions is from inner to outer. SQL> selectlast_name, nvl (to_char (manager_id), 'no manager') from employees where manager_idis NULL;