How to use one-line functions in oracle

This article mainly introduces oracle how to use one-line function, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

One-line function

Transform only one row and return one result per row

One-line functions are divided into characters, numeric values, dates, conversions, and generality

Character function: case control function, character control function

Case control functions: lower, upper, initcap

Character control function: concat,substr,length,instr,lpad | rpad,trim,replace

Lower,upper,initcap

Select lower ('SQL') from dual;-- result sqlselect upper (' sql') from dual;-- result SQLselect initcap ('SQL COurs') from dual;-- result Sql Cours initials are capitalized

Concat,substr,length,instr,lapd | rpd,trim, replace

Select concat ('hello','world') from dual; / / result helloworld select substr (' HelloWorld',1,4) from dual; / / result Hell takes four characters from the first character select length ('hellowrld') from dual; / / result 9 to calculate the character length * / select instr (' Helloword','w') from dual; / / result 6 the position of W for the first time select lpad (salary, 10 minutes) from employees / / result & 2600 left fill & select rpad (salary, 10 recording') from employees; / / result 2600 & left fill & select trim ('H' from 'HHllWoHldHH') from dual; / / result llWoHld goes from beginning to end without going to the middle

Digitally controlled round,trunc,mod

Select round (45.36954) from dual; / / 45.3695 rounding select trunc (45.36954) from dual; / / 45.369 truncated select mod (1600300) from dual; / / 100

Date control date can only add or subtract months_betwwen,add_months,next_day,last_day

The number of days between two dates minus the return date.

You can divide the number by 24 to add or subtract the number of days from the date

-- query the employees in the company whose entry time is the last two days of each month, select last_name,to_char (hire_date,'yyyy-mm-dd') hdate from employeeswhere hire_date=last_day (hire_date)-1 hire_date-query the employees who have been employed for more than 5 years in 2005, select last_name,to_char (hire_date,'yyyy-mm-dd') from employeeswhere to_date ('2005-12-31' 'yyyy-mm-dd')-hire_date > = 5 Today of next month (add 1 month to system time) select add_months (sysdate,1) from dual -date select next_day (sysdate,2) from dual two days later

Convert to_char,to_date,to_number

-- implicit conversion select'12 conversion 3 from dual; / / char automatically converted to number plus minus select sysdate + 2 from dual; / / number automatically converted to date-- explicit conversion select last_name, to_char (hire_date,'yyyy-mm-dd') hire_date from employees;select to_char (12345678.123) from dual; / / 12345678.12select to_char (12345678.123) from dual; / / 12345678.12select to_char (12345678.123) from dual / / 012345678.12 those who do not have filled select to_char (12345678.123) from dual; / / $12345678.12' as the local currency select to_number ('$12345678.12) from dual; / / 12345678.12select to_number ('12345678.12) from dual; / / 12345678.12

General functions these functions are suitable for any data type, as well as for null values

Nvl (expr1,edpr2), nvl2 (expr1,expr2,expr3), nullif (expr1,expr2), coalesce (expr1... Exprn)

Nvl converts null values to a known value that can be used for dates, characters, numbers

Ask the company employee's annual salary (including commission_pct) commisson_pct column to have a blank value select last_name,salary*12* (1+nvl (commission_pct,0)) "nianxin" from employees;-- output last_name,department_id, when department_id is null, display'no department 'select last_name, nvl (to_char (department_id,'9999'),' no department') Dep from employees Select last_name, nvl (to_char (department_id),'no department') Dep from employees; / / abbreviation-"No department" in NVL is the char type to explicitly convert department_id to NUMBER to make the data types in ()

Nvl2 (expr1,expr2,expr3) returns expr2 for null when expr1 is not null, and expr3 for null

-- query the employee's bonus rate. If it is empty, return 0.01.If it is not empty, return the actual bonus rate + 0.015select last_name,commission_pct, nvl2 (commission_pct,commission_pct + 0.015, 0.01) from employees

Nullif (expr1,expr2) two expressions equal return NULL unequal return expression 1 expr1

Select first_name,length (first_name) "expr1", last_name,length (last_name) "expr2", nullif (length (first_name), length (last_name)) resultfrom employees

Case expression

CASEexprWHENcomparison_expr1THENreturn_expr1

[WHEN comparison_expr2 THEN return_expr2

WHENcomparison_exprnTHENreturn_exprn

ELSEelse_expr]

END

-- query the information of the employees whose department number is 10pc20,30. If the department number is-10, print 1.1x of their salary.-- Department No. 20, print 1.2x of their salary, and Department 30print 1.3x select last_name,department_id of their salary. Case department_id when 10 then salary * 1.1 when 20 then salary * 1.2 else salary * 1.3 end new_salaryfrom employeeswhere department_id in-- the addition above shows other people's wages select last_name,department_id Case department_id when 10 then salary * 1.1when 20 then salary * 1.2when 30 then salary * 1.3else salary end new_salaryfrom employees

Decode

DECODE (col | expression, search2, result1

[, search3, result2,...,]

[, default])-replace when with parentheses. Then

-- the same question as above: select last_name,department_id,decode (department_id,10,salary * 1.1,20penny * 1.2, salary * 1.3) new_salaryfrom employeeswhere department_id In (10meme20jin30)-- plus other employees' salary select last_name,department_id Decode (department_id,10,salary * 1.1,20 salary * 1.2,30) new_salaryfrom employees

Thank you for reading this article carefully. I hope the article "how to use one-line functions in oracle" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!