12.单行函数
1.单行函数
--使用函数是为了操作数据
--将输入的变量处理，每行返回一个结果
--处理返回的每一行
--一行返回一个结果
--可以转化数据类型
--能嵌套使用
--传入的变量可以是列的值，也可以是表达式
2.字符函数
hr@ORCLPDB01 2023-02-19 11:11:44> select employee_id, last_name,salary,department_id
2 from employees
3 where last_name = 'higgins';
no rows selected
Elapsed: 00:00:00.00
hr@ORCLPDB01 2023-02-19 11:12:13> 3
3* where last_name = 'higgins'
hr@ORCLPDB01 2023-02-19 11:12:20> c/last_name/lower(last_name)
3* where lower(last_name) = 'higgins'
hr@ORCLPDB01 2023-02-19 11:12:40> ;
1 select employee_id, last_name,salary,department_id
2 from employees
3* where lower(last_name) = 'higgins'
hr@ORCLPDB01 2023-02-19 11:12:41> r
1 select employee_id, last_name,salary,department_id
2 from employees
3* where lower(last_name) = 'higgins'
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
205 Higgins 12008 110
Elapsed: 00:00:00.003.字符处理函数
hr@ORCLPDB01 2023-02-19 11:16:41> select employee_id,concat(first_name,last_name) name,
2 job_id, length(last_name),
3 instr(last_name, 'a') "Contains 'a' ?"
4 from employees
5 where substr(job_id,4) = 'REP'
6 ;
EMPLOYEE_ID NAME JOB_ID LENGTH(LAST_NAME) Contains 'a' ?
----------- -------------------------------------------------------------------------------- ---------- ----------------- --------------
150 PeterTucker SA_REP 6 0
151 DavidBernstein SA_REP 9 0
152 PeterHall SA_REP 4 2
153 ChristopherOlsen SA_REP 5 0
154 NanetteCambrault SA_REP 9 2
155 OliverTuvault SA_REP 7 4
156 JanetteKing SA_REP 4 0
157 PatrickSully SA_REP 5 0
158 AllanMcEwen SA_REP 6 0
159 LindseySmith SA_REP 5 0
160 LouiseDoran SA_REP 5 4
161 SarathSewall SA_REP 6 4
162 ClaraVishney SA_REP 7 0
163 DanielleGreene SA_REP 6 0
164 MatteaMarvins SA_REP 7 2
165 DavidLee SA_REP 3 0
166 SundarAnde SA_REP 4 0
167 AmitBanda SA_REP 5 2
168 LisaOzer SA_REP 4 0
169 HarrisonBloom SA_REP 5 0
170 TaylerFox SA_REP 3 0
171 WilliamSmith SA_REP 5 0
172 ElizabethBates SA_REP 5 2
173 SunditaKumar SA_REP 5 4
174 EllenAbel SA_REP 4 0
175 AlyssaHutton SA_REP 6 0
176 JonathonTaylor SA_REP 6 2
177 JackLivingston SA_REP 10 0
178 KimberelyGrant SA_REP 5 3
179 CharlesJohnson SA_REP 7 0
202 PatFay MK_REP 3 2
203 SusanMavris HR_REP 6 2
204 HermannBaer PR_REP 4 24.数字函数
--按照指定的小数位四舍五入
--按照指定的小数位截断数据
--两数相除，返回余数
hr@ORCLPDB01 2023-02-19 11:18:07> select round(45.923,2),round(45.923,0),round(45.923,-1) from dual;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- --------------- ----------------
45.92 46 50
Elapsed: 00:00:00.01
hr@ORCLPDB01 2023-02-19 11:24:29> select trunc(45.923,2),trunc(45.923,0),trunc(45.923,-1) from dual
TRUNC(45.923,2) TRUNC(45.923,0) TRUNC(45.923,-1)
--------------- --------------- ----------------
45.92 45 40
Elapsed: 00:00:00.00
hr@ORCLPDB01 2023-02-19 11:24:31> select last_name,salary,mod(salary,5000)
2 from employees
3 where job_id = 'SA_REP';
LAST_NAME SALARY MOD(SALARY,5000)
------------------------- ---------- ----------------
Tucker 10000 0
Bernstein 9500 4500
Hall 9000 4000
Olsen 8000 3000
Cambrault 7500 2500
Tuvault 7000 2000
King 10000 0
Sully 9500 4500
McEwen 9000 4000
Smith 8000 3000
Doran 7500 2500
Sewall 7000 2000
Vishney 10500 500
Greene 9500 4500
Marvins 7200 2200
Lee 6800 1800
Ande 6400 1400
Banda 6200 1200
Ozer 11500 1500
Bloom 10000 0
Fox 9600 4600
Smith 7400 2400
Bates 7300 2300
Kumar 6100 1100
Abel 11000 1000
Hutton 8800 3800
Taylor 8600 3600
Livingston 8400 3400
Grant 7000 2000
Johnson 6200 12005.日期
--Oracle数据库存储日期格式：世纪，年，月，日，时，分，秒
-- 默认显示：DD-MON-RR
-- 默认显示年的最后两个数字
--现有日期加上或减去一个数值，可以得到一个日期
--两个日期相减，可以得到两个日期间的天数
--将值除以24，可以得到小时数
hr@ORCLPDB01 2023-02-19 11:33:38> select last_name,(sysdate-hire_date)/7 as weeks
2 from employees
3 where department_id = 90;
LAST_NAME WEEKS
------------------------- ----------
King 1026.78317
Kochhar 908.640314
De Haan 1153.21174
Elapsed: 00:00:00.01
