oracle基本命令集合(2)--单行函数
来源:互联网 发布:dbc2000怪物数据库 编辑:程序博客网 时间:2024/05/20 08:01
SQL函数的两种类型:单行函数、多行函数
单行函数
一.特征和分类
1.单行函数的特征:
作用于查询中返回的每一行
每行返回一个结果
可能返回一个与参数不同类型的数据值
可能需要一个或多个参数
能够用在SELECT、WHERE和ORDER BY子句中;
可以嵌套
2.单行函数的分类:
2.1字符函数:接受字符输入,可以返回字符或者数字值
2.2数字函数:接受数字输入,返回数字值
2.3日期函数:对DATE数据类型的值进行运算 (除了MONTHS_BETWEEN函数返回一个数字,所有日期函数都返回一个DATE数据类型的值。)
2.4转换函数:从一个数据类型到另一个数据类型转换一个值
2.5通用函数:NVL,DECODE
二.使用
SQL> set linesize 140SQL> set pagesize 20SQL> --字符函数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 19-4月 -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 23-5月 -87 1100 20 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 已选择14行。SQL> --小写显示员工姓名SQL> select empno,lower(ename),job,sal from emp; EMPNO LOWER(ENAM JOB SAL ---------- ---------- --------- ---------- 7369 smith CLERK 800 7499 allen SALESMAN 1600 7521 ward SALESMAN 1250 7566 jones MANAGER 2975 7654 martin SALESMAN 1250 7698 blake MANAGER 2850 7782 clark MANAGER 2450 7788 scott ANALYST 3000 7839 king PRESIDENT 5000 7844 turner SALESMAN 1500 7876 adams CLERK 1100 7900 james CLERK 950 7902 ford ANALYST 3000 7934 miller CLERK 1300 已选择14行。SQL> select upper('Hello world');select upper('Hello world') *第 1 行出现错误: ORA-00923: 未找到要求的 FROM 关键字 SQL> select upper('Hello world') from emp;UPPER('HELL ----------- HELLO WORLD HELLO WORLD HELLO WORLD HELLO WORLD HELLO WORLD HELLO WORLD HELLO WORLD HELLO WORLD HELLO WORLD HELLO WORLD HELLO WORLD HELLO WORLD HELLO WORLD HELLO WORLD 已选择14行。SQL> --当执行的查询与任何表都没有关系是,可以使用dual同义词SQL> select upper('Hello world') from dual;UPPER('HELL ----------- HELLO WORLD SQL> select lower('Hello WORLD') 转小写,upper('Hello WORLD') 转大写,initcap('Hello worLD') 首字赚大写 from dual;转小写 转大写 首字赚大写 ----------- ----------- ----------- hello world HELLO WORLD Hello World SQL> select concat('aaa','BBB') 连接 from dual;连接 ------ aaaBBB SQL> select 'aaa' || 'BBB' 连接 from dual;连接 ------ aaaBBB SQL> select substr('abcdefghijk',4),substr('abcdefghijk',4,3) from dual;SUBSTR(' SUB -------- --- defghijk def SQL> --字符个数SQL> select length('hello') ,length('中国') from dual;LENGTH('HELLO') LENGTH('中国') --------------- -------------- 5 2 SQL> select length('中国') 字符数,lengthb('中国') 字节数 from dual; 字符数 字节数 ---------- ---------- 2 4 SQL> --查找字符出现的位置SQL> select instr('hello world','o'),instr('hello world','O'),instr('Hello world','o',6) from dual;INSTR('HELLOWORLD','O') INSTR('HELLOWORLD','O') INSTR('HELLOWORLD','O',6) ----------------------- ----------------------- ------------------------- 5 0 8 SQL> --左右填充SQL> select lpad('hello',10,'*'),rpad('hello',10,'*') from dual;LPAD('HELL RPAD('HELL ---------- ---------- *****hello hello***** SQL> --员工姓名显示为10位,右对齐SQL> select empno,lpad(ename,10,' '),job,sal from emp; EMPNO LPAD(ENAME,10,'') JOB SAL ---------- -------------------- --------- ---------- 7369 SMITH CLERK 800 7499 ALLEN SALESMAN 1600 7521 WARD SALESMAN 1250 7566 JONES MANAGER 2975 7654 MARTIN SALESMAN 1250 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7788 SCOTT ANALYST 3000 7839 KING PRESIDENT 5000 7844 TURNER SALESMAN 1500 7876 ADAMS CLERK 1100 7900 JAMES CLERK 950 7902 FORD ANALYST 3000 7934 MILLER CLERK 1300 已选择14行。SQL> --显示名字为4个字符的员工的信息SQL> select * from emp where length(ename) = 4; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7839 KING PRESIDENT 17-11月-81 5000 10 7902 FORD ANALYST 7566 03-12月-81 3000 20 SQL> select length(' aaa aaa ') from dual;LENGTH('AAAAAA') ---------------- 12 SQL> select length( trim(' aaa aaa ')) from dual;LENGTH(TRIM('AAAAAA')) ---------------------- 7 SQL> --删除字符串两边指定的字符SQL> SQL> SQL> SQL> select trim( 'h' from 'hhello hworldhhhh') from dual;TRIM('H'FRO ----------- ello hworld SQL> --替换SQL> select replace('hello world', 'o' ,'#') from dual;REPLACE('HE ----------- hell# w#rld SQL> --数字函数SQL> SQL> select round(45.926,2),round(45.926,1),round(45.926,0),round(45.926 -1) from dual;ROUND(45.926,2) ROUND(45.926,1) ROUND(45.926,0) ROUND(45.926-1) --------------- --------------- --------------- --------------- 45.93 45.9 46 45 SQL> select round(45.926,2),round(45.926,1),round(45.926,0),round(45.926, -1) from dual;ROUND(45.926,2) ROUND(45.926,1) ROUND(45.926,0) ROUND(45.926,-1) --------------- --------------- --------------- ---------------- 45.93 45.9 46 50 SQL> ed已写入 file afiedt.buf 1* select trunc(45.926,2),trunc(45.926,1),trunc(45.926,0),trunc(45.926, -1) from dualSQL> /TRUNC(45.926,2) TRUNC(45.926,1) TRUNC(45.926,0) TRUNC(45.926,-1) --------------- --------------- --------------- ---------------- 45.92 45.9 45 40 SQL> --求余SQL> select mod(17,5) from dual; MOD(17,5) ---------- 2 SQL> select sysdate 今天 from dual;今天 -------------- 12-5月 -17 SQL> insert into emp(empno,ename,job,sal,hiredate) values(8001,''李四','MANAGER',2500,'2015-10-15');ERROR:ORA-01756: 引号内的字符串没有正确结束 SQL> insert into emp(empno,ename,job,sal,hiredate) values(8001,'李四','MANAGER',2500,'2015-10-15');insert into emp(empno,ename,job,sal,hiredate) values(8001,'李四','MANAGER',2500,'2015-10-15') *第 1 行出现错误: ORA-01861: 文字与格式字符串不匹配 SQL> insert into emp(empno,ename,job,sal,hiredate) values(8001,'李四','MANAGER',2500,'15-10月 -15');已创建 1 行。SQL> --当前会话参数SQL> select * from v$nls_parameters ;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 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 PARAMETER VALUE ---------------------------------------------------------------- ---------------------------------------------------------------- NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE 已选择19行。SQL> --修改会话中的日期格式SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';会话已更改。SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 8001 李四 MANAGER 2015-10-15 2500 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7902 FORD ANALYST 7566 1981-12-03 3000 20 7934 MILLER CLERK 7782 1982-01-23 1300 10 已选择15行。SQL> insert into emp(empno,ename,job,sal,hiredate) values(8002,'李4','MANAGER',2500,'15-10月 -15');insert into emp(empno,ename,job,sal,hiredate) values(8002,'李4','MANAGER',2500,'15-10月 -15') *第 1 行出现错误: ORA-01861: 文字与格式字符串不匹配 SQL> insert into emp(empno,ename,job,sal,hiredate) values(8002,'李4','MANAGER',2500,'2015-10-16');已创建 1 行。SQL> select sysdate - 1 昨天,sysdate 今天,sysage + 1 明天 from dual;select sysdate - 1 昨天,sysdate 今天,sysage + 1 明天 from dual *第 1 行出现错误: ORA-00904: "SYSAGE": 标识符无效 SQL> select sysdate - 1 昨天,sysdate 今天,sysdate + 1 明天 from dual;昨天 今天 明天 ---------- ---------- ---------- 2017-05-11 2017-05-12 2017-05-13 SQL> --查询用员入职的天数,周数,月数,年数SQL> select sysdate - hiredate 天数,(sysdate - hiredate)/7 周数,(sysdate-hiredate)/30 月数,(sysdate-hiredate) /365 年数 from dual;select sysdate - hiredate 天数,(sysdate - hiredate)/7 周数,(sysdate-hiredate)/30 月数,(sysdate-hiredate) /365 年数 from dual *第 1 行出现错误: ORA-00904: "HIREDATE": 标识符无效 SQL> select sysdate - hiredate 天数,(sysdate - hiredate)/7 周数,(sysdate-hiredate)/30 月数,(sysdate-hiredate) /365 年数 from emp; 天数 周数 月数 年数 ---------- ---------- ---------- ---------- 575.41088 82.2015542 19.1803627 1.57646816 574.41088 82.0586971 19.1470293 1.57372844 13295.4109 1899.34441 443.180363 36.4257832 13230.4109 1890.0587 441.013696 36.247701 13228.4109 1889.77298 440.947029 36.2422216 13189.4109 1884.20155 439.647029 36.1353723 13010.4109 1858.63013 433.680363 35.6449613 13160.4109 1880.0587 438.680363 36.0559202 13121.4109 1874.48727 437.380363 35.9490709 10981.4109 1568.77298 366.047029 30.0860572 12960.4109 1851.48727 432.013696 35.507975 13030.4109 1861.48727 434.347029 35.6997558 10947.4109 1563.91584 364.913696 29.9929065 12944.4109 1849.20155 431.480363 35.4641394 12944.4109 1849.20155 431.480363 35.4641394 12893.4109 1841.91584 429.780363 35.3244134 已选择16行。SQL> select empno,ename,hiredate, sysdate - hiredate 天数,(sysdate - hiredate)/7 周数,(sysdate-hiredate)/30 月数,(sysdate-hiredate) /365 年数 from emp; EMPNO ENAME HIREDATE 天数 周数 月数 年数 ---------- ---------- ---------- ---------- ---------- ---------- ---------- 8001 李四 2015-10-15 575.4111 82.2015856 19.18037 1.57646877 8002 李4 2015-10-16 574.4111 82.0587285 19.1470367 1.57372904 7369 SMITH 1980-12-17 13295.4111 1899.34444 443.18037 36.4257838 7499 ALLEN 1981-02-20 13230.4111 1890.05873 441.013703 36.2477016 7521 WARD 1981-02-22 13228.4111 1889.77301 440.947037 36.2422222 7566 JONES 1981-04-02 13189.4111 1884.20159 439.647037 36.1353729 7654 MARTIN 1981-09-28 13010.4111 1858.63016 433.68037 35.6449619 7698 BLAKE 1981-05-01 13160.4111 1880.05873 438.68037 36.0559208 7782 CLARK 1981-06-09 13121.4111 1874.4873 437.38037 35.9490715 7788 SCOTT 1987-04-19 10981.4111 1568.77301 366.047037 30.0860578 7839 KING 1981-11-17 12960.4111 1851.4873 432.013703 35.5079756 7844 TURNER 1981-09-08 13030.4111 1861.4873 434.347037 35.6997564 7876 ADAMS 1987-05-23 10947.4111 1563.91587 364.913703 29.9929071 7900 JAMES 1981-12-03 12944.4111 1849.20159 431.48037 35.46414 7902 FORD 1981-12-03 12944.4111 1849.20159 431.48037 35.46414 7934 MILLER 1982-01-23 12893.4111 1841.91587 429.78037 35.324414 已选择16行。SQL> select empno,ename,hiredate, (sysdate - hiredate)/30 月数,month_between(sysdate,hiredate) from emp;select empno,ename,hiredate, (sysdate - hiredate)/30 月数,month_between(sysdate,hiredate) from emp *第 1 行出现错误: ORA-00904: "MONTH_BETWEEN": 标识符无效 SQL> select empno,ename,hiredate, (sysdate - hiredate)/30 月数,months_between(sysdate,hiredate) from emp; EMPNO ENAME HIREDATE 月数 MONTHS_BETWEEN(SYSDATE,HIREDATE) ---------- ---------- ---------- ---------- -------------------------------- 8001 李四 2015-10-15 19.180402 18.9165181 8002 李4 2015-10-16 19.1470687 18.88426 7369 SMITH 1980-12-17 443.180402 436.852002 7499 ALLEN 1981-02-20 441.013735 434.755228 7521 WARD 1981-02-22 440.947069 434.690712 7566 JONES 1981-04-02 439.647069 433.335873 7654 MARTIN 1981-09-28 433.680402 427.497163 7698 BLAKE 1981-05-01 438.680402 432.368131 7782 CLARK 1981-06-09 437.380402 431.110066 7788 SCOTT 1987-04-19 366.047069 360.787486 7839 KING 1981-11-17 432.013735 425.852002 7844 TURNER 1981-09-08 434.347069 428.142325 7876 ADAMS 1987-05-23 364.913735 359.658454 7900 JAMES 1981-12-03 431.480402 425.303615 7902 FORD 1981-12-03 431.480402 425.303615 7934 MILLER 1982-01-23 429.780402 423.658454 已选择16行。SQL> --10个后的日期SQL> select add_months(sysdate,10) from dual;ADD_MONTHS ---------- 2018-03-12 SQL> --本月的最后一天SQL> select last_day(sysdate) from dual;LAST_DAY(S ---------- 2017-05-31 SQL> --上月的最后一天SQL> select last_day(add_month(sysdate,-1)) from dual;select last_day(add_month(sysdate,-1)) from dual *第 1 行出现错误: ORA-00904: "ADD_MONTH": 标识符无效 SQL> select last_day(add_months(sysdate,-1)) from dual;LAST_DAY(A ---------- 2017-04-30 SQL> --下一个星期六的日期SQL> select next_day(sysdate,'星期六') from dual;NEXT_DAY(S ---------- 2017-05-20 SQL> select next_day(sysdate,'星期日') from dual;NEXT_DAY(S ---------- 2017-05-14 SQL> select round(sysdate,'MONTH') from dual;ROUND(SYSD ---------- 2017-05-01 SQL> select round(sysdate+3,'MONTH') from dual;ROUND(SYSD ---------- 2017-06-01 SQL> --员工的入职日期显示为 xxxx年xx月xx日SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 8001 李四 MANAGER 2015-10-15 2500 8002 李4 MANAGER 2015-10-16 2500 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7902 FORD ANALYST 7566 1981-12-03 3000 20 7934 MILLER CLERK 7782 1982-01-23 1300 10 已选择16行。SQL> select empno,ename,job,to_char(hiredate,'yyyy年mm月dd日'),to_char(sal,'L9,999.99') from emp;select empno,ename,job,to_char(hiredate,'yyyy年mm月dd日'),to_char(sal,'L9,999.99') from emp *第 1 行出现错误: ORA-01821: 日期格式无法识别 SQL> select empno,ename,job,to_char(hiredate,'yyyy-mm-dd'),to_char(sal,'L9,999.99') from emp; EMPNO ENAME JOB TO_CHAR(HI TO_CHAR(SAL,'L9,999 ---------- ---------- --------- ---------- ------------------- 8001 李四 MANAGER 2015-10-15 ¥2,500.00 8002 李4 MANAGER 2015-10-16 ¥2,500.00 7369 SMITH CLERK 1980-12-17 ¥800.00 7499 ALLEN SALESMAN 1981-02-20 ¥1,600.00 7521 WARD SALESMAN 1981-02-22 ¥1,250.00 7566 JONES MANAGER 1981-04-02 ¥2,975.00 7654 MARTIN SALESMAN 1981-09-28 ¥1,250.00 7698 BLAKE MANAGER 1981-05-01 ¥2,850.00 7782 CLARK MANAGER 1981-06-09 ¥2,450.00 7788 SCOTT ANALYST 1987-04-19 ¥3,000.00 7839 KING PRESIDENT 1981-11-17 ¥5,000.00 7844 TURNER SALESMAN 1981-09-08 ¥1,500.00 7876 ADAMS CLERK 1987-05-23 ¥1,100.00 7900 JAMES CLERK 1981-12-03 ¥950.00 7902 FORD ANALYST 1981-12-03 ¥3,000.00 7934 MILLER CLERK 1982-01-23 ¥1,300.00 已选择16行。SQL> select empno,ename,job,to_char(hiredate,'yyyyaaamm-dd'),to_char(sal,'L9,999.99') from emp;select empno,ename,job,to_char(hiredate,'yyyyaaamm-dd'),to_char(sal,'L9,999.99') from emp *第 1 行出现错误: ORA-01821: 日期格式无法识别 SQL> select empno,ename,job,to_char(hiredate,'yyyy/mm-dd'),to_char(sal,'L9,999.99') from emp; EMPNO ENAME JOB TO_CHAR(HI TO_CHAR(SAL,'L9,999 ---------- ---------- --------- ---------- ------------------- 8001 李四 MANAGER 2015/10-15 ¥2,500.00 8002 李4 MANAGER 2015/10-16 ¥2,500.00 7369 SMITH CLERK 1980/12-17 ¥800.00 7499 ALLEN SALESMAN 1981/02-20 ¥1,600.00 7521 WARD SALESMAN 1981/02-22 ¥1,250.00 7566 JONES MANAGER 1981/04-02 ¥2,975.00 7654 MARTIN SALESMAN 1981/09-28 ¥1,250.00 7698 BLAKE MANAGER 1981/05-01 ¥2,850.00 7782 CLARK MANAGER 1981/06-09 ¥2,450.00 7788 SCOTT ANALYST 1987/04-19 ¥3,000.00 7839 KING PRESIDENT 1981/11-17 ¥5,000.00 7844 TURNER SALESMAN 1981/09-08 ¥1,500.00 7876 ADAMS CLERK 1987/05-23 ¥1,100.00 7900 JAMES CLERK 1981/12-03 ¥950.00 7902 FORD ANALYST 1981/12-03 ¥3,000.00 7934 MILLER CLERK 1982/01-23 ¥1,300.00 已选择16行。SQL> --当前时间SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 当前时间 from dual;当前时间 ------------------- 2017-05-13 10:05:50 SQL> insert into emp(empno,ename,job,sal,hiredate) values(8003,'TOM','MANAGER',2800, '2010/10/13' );已创建 1 行。SQL> insert into emp(empno,ename,job,sal,hiredate) values(8004,'JACK','MANAGER',2800, '10/13/2016' );insert into emp(empno,ename,job,sal,hiredate) values(8004,'JACK','MANAGER',2800, '10/13/2016' ) *第 1 行出现错误: ORA-01843: 无效的月份 SQL> insert into emp(empno,ename,job,sal,hiredate) values(8004,'JACK','MANAGER',2800, to_date('10/13/2016','mm/dd/yyyy') );已创建 1 行。SQL> --显示员工有没有奖金SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 8001 李四 MANAGER 2015-10-15 2500 8002 李4 MANAGER 2015-10-16 2500 8003 TOM MANAGER 2010-10-13 2800 8004 JACK MANAGER 2016-10-13 2800 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7902 FORD ANALYST 7566 1981-12-03 3000 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7934 MILLER CLERK 7782 1982-01-23 1300 10 已选择18行。SQL> select empno,ename,job,sal,nvl2(comm,'有奖金','没奖金') from emp; EMPNO ENAME JOB SAL NVL2(C ---------- ---------- --------- ---------- ------ 8001 李四 MANAGER 2500 没奖金 8002 李4 MANAGER 2500 没奖金 8003 TOM MANAGER 2800 没奖金 8004 JACK MANAGER 2800 没奖金 7369 SMITH CLERK 800 没奖金 7499 ALLEN SALESMAN 1600 有奖金 7521 WARD SALESMAN 1250 有奖金 7566 JONES MANAGER 2975 没奖金 7654 MARTIN SALESMAN 1250 有奖金 7698 BLAKE MANAGER 2850 没奖金 7782 CLARK MANAGER 2450 没奖金 7788 SCOTT ANALYST 3000 没奖金 7839 KING PRESIDENT 5000 没奖金 7844 TURNER SALESMAN 1500 有奖金 7876 ADAMS CLERK 1100 没奖金 7900 JAMES CLERK 950 没奖金 7902 FORD ANALYST 3000 没奖金 EMPNO ENAME JOB SAL NVL2(C ---------- ---------- --------- ---------- ------ 7934 MILLER CLERK 1300 没奖金 已选择18行。SQL> select nullif('aaa','bbb') from dual;NUL --- aaa SQL> select nullif('aaa','aaa') from dual;NUL --- SQL> select coalesce(null,null,'aaa','bbb') from dual;COA --- aaa SQL> select distinct job from emp;JOB --------- CLERK SALESMAN PRESIDENT MANAGER ANALYST SQL> --将员工的工作岗位显示为中文SQL> SQL> SQL> select empno,ename,job, 2 case job 3 when 'CLERK' then '清洁工' 4 when 'SALESMAN' then '销售员' 5 when 'PRESIDENT' then '董事长' 6 when 'MANAGER' then '经理' 7 else '其它岗位' 8 end 岗位 9 from emp; EMPNO ENAME JOB 岗位 ---------- ---------- --------- -------- 8001 李四 MANAGER 经理 8002 李4 MANAGER 经理 8003 TOM MANAGER 经理 8004 JACK MANAGER 经理 7369 SMITH CLERK 清洁工 7499 ALLEN SALESMAN 销售员 7521 WARD SALESMAN 销售员 7566 JONES MANAGER 经理 7654 MARTIN SALESMAN 销售员 7698 BLAKE MANAGER 经理 7782 CLARK MANAGER 经理 7788 SCOTT ANALYST 其它岗位 7839 KING PRESIDENT 董事长 7844 TURNER SALESMAN 销售员 7876 ADAMS CLERK 清洁工 7900 JAMES CLERK 清洁工 7902 FORD ANALYST 其它岗位 EMPNO ENAME JOB 岗位 ---------- ---------- --------- -------- 7934 MILLER CLERK 清洁工 已选择18行。SQL> ANALYST 其它岗SP2-0734: 未知的命令开头 "ANALYST ..." - 忽略了剩余的行。SQL> select empno,ename,job, 2 case 3 when job = 'CLERK' then '清洁工' 4 when job = 'SALESMAN' then '销售员' 5 when job = 'PRESIDENT' then '董事长' 6 when job = 'MANAGER' then '经理' 7 else '其它岗位' 8 end 岗位 9 from emp; EMPNO ENAME JOB 岗位 ---------- ---------- --------- -------- 8001 李四 MANAGER 经理 8002 李4 MANAGER 经理 8003 TOM MANAGER 经理 8004 JACK MANAGER 经理 7369 SMITH CLERK 清洁工 7499 ALLEN SALESMAN 销售员 7521 WARD SALESMAN 销售员 7566 JONES MANAGER 经理 7654 MARTIN SALESMAN 销售员 7698 BLAKE MANAGER 经理 7782 CLARK MANAGER 经理 7788 SCOTT ANALYST 其它岗位 7839 KING PRESIDENT 董事长 7844 TURNER SALESMAN 销售员 7876 ADAMS CLERK 清洁工 7900 JAMES CLERK 清洁工 7902 FORD ANALYST 其它岗位 EMPNO ENAME JOB 岗位 ---------- ---------- --------- -------- 7934 MILLER CLERK 清洁工 已选择18行。SQL> select empno,ename,job,decode(job,'CLERK','清洁工','SALESMAN','销售员','PRESIDENT','董事长','其它岗位') from emp; EMPNO ENAME JOB DECODE(J ---------- ---------- --------- -------- 8001 李四 MANAGER 其它岗位 8002 李4 MANAGER 其它岗位 8003 TOM MANAGER 其它岗位 8004 JACK MANAGER 其它岗位 7369 SMITH CLERK 清洁工 7499 ALLEN SALESMAN 销售员 7521 WARD SALESMAN 销售员 7566 JONES MANAGER 其它岗位 7654 MARTIN SALESMAN 销售员 7698 BLAKE MANAGER 其它岗位 7782 CLARK MANAGER 其它岗位 7788 SCOTT ANALYST 其它岗位 7839 KING PRESIDENT 董事长 7844 TURNER SALESMAN 销售员 7876 ADAMS CLERK 清洁工 7900 JAMES CLERK 清洁工 7902 FORD ANALYST 其它岗位 EMPNO ENAME JOB DECODE(J ---------- ---------- --------- -------- 7934 MILLER CLERK 清洁工 已选择18行。SQL> spool off
1 0
- oracle基本命令集合(2)--单行函数
- Oracle学习(2)单行函数
- oracle单行函数(2)
- oracle单行函数
- oracle单行函数
- Oracle单行函数笔记
- oracle单行函数详解
- Oracle(三)-------------------单行函数
- oracle单行函数详解
- oracle单行函数详解
- Oracle单行函数
- oracle单行函数详解
- oracle单行函数
- oracle单行函数
- oracle 单行函数详解
- oracle 单行函数介绍
- Oracle单行函数
- Oracle之单行函数
- 解决Spring MVC @ResponseBody返回中文字符串乱码问题
- js,jq,css多方面实现简易Tab切换
- hanoi问题
- raspi-config command not found resolution
- 闭包(Closures)
- oracle基本命令集合(2)--单行函数
- super在对象的内部使用,代表父类对象
- 铁路修复计划(裸Kruskal+二分)
- USB无线网卡配置ICS失败,你可以连接到SoftAP,但可能无法使用internet服务
- JavaScrip 时间格式化 ----转载
- 中介者模式
- python 链表的归并排序(附数组的归并排序作为对照)
- SeekBar增加可触摸区域
- Ruby-基础语法