SQL> --字符函数
SQL> --大小写转换函数
SQL> select upper('hello world'),lower('HELLO WORLD') from dual;
UPPER('HELL LOWER('HELL
----------- -----------
HELLO WORLD hello world
SQL> --initcap: 每个单词的首字母大写
SQL> select initcap('hello world!') from dual;
INITCAP('HEL
------------
Hello World!
SQL> --concat字符串连接函数
SQL> select concat('hello','wold!') from dual;
CONCAT('HE
----------
hellowold!
SQL> --substr: 子串
SQL> select substr(
2 'Hello World',4) from dual;
SUBSTR('
--------
lo World
SQL> select substr('hello world!',4,2) from dual;
SU
--
lo
SQL> --length和legnthb
SQL> select length('abc'),lengthb('abc') from dual;
LENGTH('ABC') LENGTHB('ABC')
------------- --------------
3 3
SQL> select length('北京'),lengthb('北京') from dual;
LENGTH('北京') LENGTHB('北京')
-------------- ---------------
2 4
SQL> --length:字符数; lengthb:字节数
SQL> --instr:在母串中返回子串的位置
SQL> select instr('hello world!','ll') from dual;
INSTR('HELLOWORLD!','LL')
-------------------------
3
SQL> --lpad:左填充; rpad: 右填充
SQL> select lpad('abc',10,'#') from dual;
LPAD('ABC'
----------
#######abc
SQL> select rpad('abc',10,'#') from dual;
RPAD('ABC'
----------
abc#######
SQL> --trim:从母串中，去掉首尾的某个字符
SQL> select trim('H' from 'Helloworld!') from dual;
TRIM('H'FR
----------
elloworld!
SQL> --数值函数
SQL> --round,trunc,mod
SQL> select round(45.926,2) 四舍五入, trunc(45.926,2) 截断, mod(1000,300) 求余 from dual;
四舍五入 截断 求余
---------- ---------- ----------
45.93 45.92 100
SQL> desc dual;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
SQL> --日期函数
SQL> select sydate from dual;
select sydate from dual
*
第 1 行出现错误:
ORA-00904: "SYDATE": 标识符无效
SQL> select sysdate from dual;
SYSDATE
--------------
11-6月 -11
SQL> --显示昨天，今天，明天
SQL> select sysdate-1 昨天, sysdate 今天,sysdate+1 明天 from dual;
昨天 今天 明天
-------------- -------------- --------------
10-6月 -11 11-6月 -11 12-6月 -11
SQL> set linesize 120
SQL> col sal for 9999
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
1234 Tom_123
已选择15行。
SQL> --查询员工到公司的时间长短，分别按星期，月，年显示
SQL> select ename,hiredate,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365
2 from emp;
ENAME HIREDATE 星期 月 (SYSDATE-HIREDATE)/365
---------- -------------- ---------- ---------- ----------------------
SMITH 17-12月-80 1590.515 371.120167 30.5030275
ALLEN 20-2月 -81 1581.22929 368.953501 30.3249453
WARD 22-2月 -81 1580.94357 368.886834 30.3194658
JONES 02-4月 -81 1575.37215 367.586834 30.2126165
MARTIN 28-9月 -81 1549.80072 361.620167 29.7222055
BLAKE 01-5月 -81 1571.22929 366.620167 30.1331644
CLARK 09-6月 -81 1565.65786 365.320167 30.0263151
SCOTT 13-7月 -87 1247.80072 291.153501 23.9304247
KING 17-11月-81 1542.65786 359.953501 29.5852192
TURNER 08-9月 -81 1552.65786 362.286834 29.7770001
ADAMS 13-7月 -87 1247.80072 291.153501 23.9304247
ENAME HIREDATE 星期 月 (SYSDATE-HIREDATE)/365
---------- -------------- ---------- ---------- ----------------------
JAMES 03-12月-81 1540.37215 359.420167 29.5413836
FORD 03-12月-81 1540.37215 359.420167 29.5413836
MILLER 23-1月 -82 1533.08643 357.720167 29.4016576
Tom_123
已选择15行。
SQL> ed
已写入 file afiedt.buf
1 select ename,hiredate,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年
2* from emp
SQL> /
ENAME HIREDATE 星期 月 年
---------- -------------- ---------- ---------- ----------
SMITH 17-12月-80 1590.51502 371.120171 30.5030278
ALLEN 20-2月 -81 1581.22931 368.953505 30.3249456
WARD 22-2月 -81 1580.94359 368.886838 30.3194661
JONES 02-4月 -81 1575.37216 367.586838 30.2126168
MARTIN 28-9月 -81 1549.80073 361.620171 29.7222059
BLAKE 01-5月 -81 1571.22931 366.620171 30.1331648
CLARK 09-6月 -81 1565.65788 365.320171 30.0263154
SCOTT 13-7月 -87 1247.80073 291.153505 23.930425
KING 17-11月-81 1542.65788 359.953505 29.5852196
TURNER 08-9月 -81 1552.65788 362.286838 29.7770004
ADAMS 13-7月 -87 1247.80073 291.153505 23.930425
ENAME HIREDATE 星期 月 年
---------- -------------- ---------- ---------- ----------
JAMES 03-12月-81 1540.37216 359.420171 29.5413839
FORD 03-12月-81 1540.37216 359.420171 29.5413839
MILLER 23-1月 -82 1533.08645 357.720171 29.4016579
Tom_123
已选择15行。
SQL> select months_between(sysdate,hiredate) 月 from emp;
月
----------
365.826038
363.729264
363.664748
362.309909
356.471199
361.342167
360.084102
286.95507
354.826038
357.116361
286.95507
月
----------
354.277651
354.277651
352.63249
已选择15行。
SQL> select months_between(hiredate,sysdate) 月 from emp;
月
----------
-365.82605
-363.72928
-363.66476
-362.30993
-356.47122
-361.34218
-360.08412
-286.95509
-354.82605
-357.11638
-286.95509
月
----------
-354.27767
-354.27767
-352.63251
已选择15行。
SQL> select sysdate 当前日期, add_months(sysdate,1)
2 from dual;
当前日期 ADD_MONTHS(SYS
-------------- --------------
11-6月 -11 11-7月 -11
SQL> select next_day(sysdate,'星期六') from dual;
NEXT_DAY(SYSDA
--------------
18-6月 -11
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDA
--------------
30-6月 -11
SQL> --对日期进行四舍五入和截断
SQL> select round(sysdate,'month'),round(sysdate,'year') from dual;
ROUND(SYSDATE, ROUND(SYSDATE,
-------------- --------------
01-6月 -11 01-1月 -11
SQL> select round(sysdate,'month'),round(sysdate,'year') from dual;
ROUND(SYSDATE, ROUND(SYSDATE,
-------------- --------------
01-6月 -11 01-1月 -11
SQL> ed
已写入 file afiedt.buf
1* select trunc(sysdate,'month'),trunc(sysdate,'year') from dual
SQL> /
TRUNC(SYSDATE, TRUNC(SYSDATE,
-------------- --------------
01-6月 -11 01-1月 -11
SQL> --复习上午的内容
SQL> select sysdate from dual;
SYSDATE
--------------
11-6月 -11
SQL> select * from v$nls_parameters;
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_LANGUAGE
SIMPLIFIED CHINESE
NLS_TERRITORY
CHINA
NLS_CURRENCY
￥
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_ISO_CURRENCY
CHINA
NLS_NUMERIC_CHARACTERS
.,
NLS_CALENDAR
GREGORIAN
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR
NLS_DATE_LANGUAGE
SIMPLIFIED CHINESE
NLS_CHARACTERSET
ZHS16GBK
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_SORT
BINARY
NLS_TIME_FORMAT
HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY
￥
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16
NLS_COMP
BINARY
NLS_LENGTH_SEMANTICS
BYTE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_NCHAR_CONV_EXCP
FALSE
已选择19行。
SQL> col parameter for a30
SQL> /
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY ￥
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY ￥
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
已选择19行。
SQL> -- 修改日期的格式: alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh:min:ss'
SQL> --转换函数:
SQL> --隐性转换： 前提是要转换的内容一定是可以转换的
SQL> --查询员工的工资，要求工资以货币代码的格式显示
SQL> select ename,sal,to_char(sal,'L9999') from emp;
ENAME SAL TO_CHAR(SAL,'L9
---------- ----- ---------------
SMITH 800 ￥800
ALLEN 1600 ￥1600
WARD 1250 ￥1250
JONES 2975 ￥2975
MARTIN 1250 ￥1250
BLAKE 2850 ￥2850
CLARK 2450 ￥2450
SCOTT 3000 ￥3000
KING 5000 ￥5000
TURNER 1500 ￥1500
ADAMS 1100 ￥1100
ENAME SAL TO_CHAR(SAL,'L9
---------- ----- ---------------
JAMES 950 ￥950
FORD 3000 ￥3000
MILLER 1300 ￥1300
Tom_123
已选择15行。
SQL> --进一步：显示千位符
SQL> select ename,sal,to_char(sal,'L9,999') from emp;
ENAME SAL TO_CHAR(SAL,'L9,
---------- ----- ----------------
SMITH 800 ￥800
ALLEN 1600 ￥1,600
WARD 1250 ￥1,250
JONES 2975 ￥2,975
MARTIN 1250 ￥1,250
BLAKE 2850 ￥2,850
CLARK 2450 ￥2,450
SCOTT 3000 ￥3,000
KING 5000 ￥5,000
TURNER 1500 ￥1,500
ADAMS 1100 ￥1,100
ENAME SAL TO_CHAR(SAL,'L9,
---------- ----- ----------------
JAMES 950 ￥950
FORD 3000 ￥3,000
MILLER 1300 ￥1,300
Tom_123
已选择15行。
SQL> select to_date('2011-06-11','yyyy-mm-dd') from dual;
TO_DATE('2011-
--------------
11-6月 -11
SQL> select to_char(sysdate,'yyyy-mm-dd hh:min:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh:min:ss') from dual
*
第 1 行出现错误:
ORA-01821: 日期格式无法识别
SQL> select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2011-06-11 02:51:36
SQL> host cls
SQL> --通用函数
SQL> --查询员工的年收入
SQL> select ename,sal,sal*12 年薪, sal*12+nvl(comm,0)
2 from emp;
ENAME SAL 年薪 SAL*12+NVL(COMM,0)
---------- ----- ---------- ------------------
SMITH 800 9600 9600
ALLEN 1600 19200 19500
WARD 1250 15000 15500
JONES 2975 35700 35700
MARTIN 1250 15000 16400
BLAKE 2850 34200 34200
CLARK 2450 29400 29400
SCOTT 3000 36000 36000
KING 5000 60000 60000
TURNER 1500 18000 18000
ADAMS 1100 13200 13200
ENAME SAL 年薪 SAL*12+NVL(COMM,0)
---------- ----- ---------- ------------------
JAMES 950 11400 11400
FORD 3000 36000 36000
MILLER 1300 15600 15600
Tom_123
已选择15行。
SQL> --nvl2(p1,p2,p3):当p1为null,返回p3;否则返回p2
SQL> select ename,sal,sal*12, sal*12+nvl2(comm,comm,0) 年收入
2 from emp;
ENAME SAL SAL*12 年收入
---------- ----- ---------- ----------
SMITH 800 9600 9600
ALLEN 1600 19200 19500
WARD 1250 15000 15500
JONES 2975 35700 35700
MARTIN 1250 15000 16400
BLAKE 2850 34200 34200
CLARK 2450 29400 29400
SCOTT 3000 36000 36000
KING 5000 60000 60000
TURNER 1500 18000 18000
ADAMS 1100 13200 13200
ENAME SAL SAL*12 年收入
---------- ----- ---------- ----------
JAMES 950 11400 11400
FORD 3000 36000 36000
MILLER 1300 15600 15600
Tom_123
已选择15行。
SQL> select nullif('abc','abc') from dual;
NUL
---
SQL> select nullif('abc','abc1') from dual;
NUL
---
abc
SQL> --测试coalesce
SQL> select coalesce(comm,sal) from emp;
COALESCE(COMM,SAL)
------------------
800
300
500
2975
1400
2850
2450
3000
5000
0
1100
COALESCE(COMM,SAL)
------------------
950
3000
1300
已选择15行。
SQL> --找到第一个不为null的值
SQL> --SQL中的条件判断
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
1234 Tom_123
已选择15行。
SQL> --为员工涨工资,一般职员涨100,销售200，经理300 分析员 400 总裁500
SQL> select ename,job,sal 当前薪水, case job when 'CLERK' then sal+100
2 when 'SALESMAN' then sal+200
3 when 'MANAGER' then sal+300
4 when 'ANALYST' then sal+400
5 when 'PRESIDENT' then sal+500
6 end 涨后的薪水
7 from emp;
ENAME JOB 当前薪水 涨后的薪水
---------- --------- ---------- ----------
SMITH CLERK 800 900
ALLEN SALESMAN 1600 1800
WARD SALESMAN 1250 1450
JONES MANAGER 2975 3275
MARTIN SALESMAN 1250 1450
BLAKE MANAGER 2850 3150
CLARK MANAGER 2450 2750
SCOTT ANALYST 3000 3400
KING PRESIDENT 5000 5500
TURNER SALESMAN 1500 1700
ADAMS CLERK 1100 1200
ENAME JOB 当前薪水 涨后的薪水
---------- --------- ---------- ----------
JAMES CLERK 950 1050
FORD ANALYST 3000 3400
MILLER CLERK 1300 1400
Tom_123
已选择15行。
SQL> --使用decode实现上面的功能
SQL> select ename,job,sal 当前薪水, decode(job,'CLERK',sal+100,
2 'SALESMAN',sal+200,
3 'MANAGER',sal+300,
4 'ANALYST',sal+400,
5 sal+500) 涨后薪水
6 from emp;
ENAME JOB 当前薪水 涨后薪水
---------- --------- ---------- ----------
SMITH CLERK 800 900
ALLEN SALESMAN 1600 1800
WARD SALESMAN 1250 1450
JONES MANAGER 2975 3275
MARTIN SALESMAN 1250 1450
BLAKE MANAGER 2850 3150
CLARK MANAGER 2450 2750
SCOTT ANALYST 3000 3400
KING PRESIDENT 5000 5500
TURNER SALESMAN 1500 1700
ADAMS CLERK 1100 1200
ENAME JOB 当前薪水 涨后薪水
---------- --------- ---------- ----------
JAMES CLERK 950 1050
FORD ANALYST 3000 3400
MILLER CLERK 1300 1400
Tom_123
已选择15行。
SQL> --PL/SQL:循环，判断 输出
SQL> spool off
![[Oracle]面试官：你举例几个内置函数，并且说说如何使用内置函数作正则匹配](https://ucc.alicdn.com/3mfxe2r26qbaq/developer-article1634560/20241106/3c985adba7754028bad691222250b227.jpeg?x-oss-process=image/resize,h_160,m_lfit)
