Oracle补充知识点
1.nvl函数，去除空值。 nvl函数与coalesce函数。coalesce比nvl好用
nvl(字段名,0)
nvl
SQL> select ename,sal,comm,NVL(comm,0)+sal from emp;
ENAME SAL COMM NVL(COMM,0)+SAL
---------- ---------- ---------- ---------------
SMITH 800 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975 2975
MARTIN 1250 1400 2650
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
TURNER 1500 0 1500
ADAMS 1100 1100
ENAME SAL COMM NVL(COMM,0)+SAL
---------- ---------- ---------- ---------------
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
已选择14行。
select case when a is null then b
else a end from use;
select nvl(a,b) from use;
https://www.cnblogs.com/zrui-xyu/p/4819712.html
2. WM_CONCAT(字段名)
--部门内的人员
select deptno 部门号,WM_CONCAT(ename) from emp group by deptno
3.组函数
select a,b,c ,组函数()
from 表名
group by a,b,c(必须)
having 组函数() >/</>=
4.group by 语句的增强
select a,b,组函数() from tablename group by rollup(a,b);
5.行号 rownum 只能用</<=
6. 查询时的in any任意 all 所有
7.执行计划，查看耗费资源
explain plan for
select * from emp;
select * from table(dbms_xplan.display);
8.sum(decode())和count(case when)
9.translate函数
10.执行sql脚本
方法一：sql>start file_name
方法二：@ file_name
11.对当前的输入进行编辑
sql>edit
12.重新运行上一次运行的sql
sql>/
13.将显示的内容输出到指定文件
sql>spool file_name(路径+文件名 可以txt格式)
关闭spool输出
sql>spool off
14.显示表的结构
desc 表名
15.清屏 clea screen
16.在sqlplus编写情况下，使用col更改显示列的名字
col 列名 heading 显示的名字
col loc heading 地址
17.sqlplus下，改变列的显示长度。更改------个数
col 列名 for a10 10为即将显示的个数
18.设置列标题的对齐方式
19.清除设置 clear columns
20.打开set echo on 显示脚本语句
21.是否显示列标题
show heading
关闭 set heading off/on
22.设置一行可容纳的字符数
set line 200
23.设置页与页之间的分隔
set newpage 1/2/3
24.设置一页有多少行
set pagesize 20
25.显示每个sql语句花费的执行时间
show timing
26.
27.设置顶部/尾部标题
title/btitle + 名字
28.复制表
29.一次修改多个字段
30.授权/撤销
31.错误更改 c/=/:=/ 将 = 改为 :=
32.属性类型
33.嵌套命名
34.替代变量
SQL> set verify on
SQL> run
1 select empno,ename,sal,deptno from emp
2* where empno=&employee_num
输入 employee_num 的值: 7369
原值 2: where empno=&employee_num
新值 2: where empno=7369
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 800 20
35.verify 的开关
36.替代变量 字符串和日期类型 需要用单引号
SQL> run
1 select ename,deptno,sal*12 from emp
2* where job='&job'
输入 job 的值: ANALYST
原值 2: where job='&job'
新值 2: where job='ANALYST'
ENAME DEPTNO SAL*12
---------- ---------- ----------
SCOTT 20 36000
FORD 20 36000
--------------------------------------------------------
SQL> select empno,ename,job,&column_name from emp
2 where &condition
3 order by &order_name;
输入 column_name 的值: sal
原值 1: select empno,ename,job,&column_name from emp
新值 1: select empno,ename,job,sal from emp
输入 condition 的值: sal>3000
原值 2: where &condition
新值 2: where sal>3000
输入 order_name 的值: ename
原值 3: order by &order_name
新值 3: order by ename
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
-------------------------------------------------------------------
37.accept的用法
脚本
accept prompt '请输入部门号:' --加hide隐藏
select * from emp
where deptno=&dept_num;
SQL> @ H:\数据库\Oracle数据库PLSQL入门到精通****\plsql\第2部分PLSQL基础\第三讲：一般变量与替代变量\新建文本文档.txt
请输入部门号:20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
38.define/undefine的用法
SQL> define &name=ename
输入 name 的值: ;
SP2-0553: 非法的变量名 ";=ename"。
SQL> define name=ename
SQL> select &name,sal from emp;
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
ENAME SAL
---------- ----------
JAMES 950
FORD 3000
MILLER 1300
已选择14行。
SQL> undefine name
SQL> select &name,sal from emp;
输入 name 的值: ename
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
ENAME SAL
---------- ----------
JAMES 950
FORD 3000
MILLER 1300
已选择14行。
39.日期函数
40.
SQL> select Ltrim('xyzaaa','xyz') from dual;
LTR
---
aaa
SQL> select translate('jack','j','k') from dual;
TRAN
----
kack
SQL> select replace('jack and jue','j','Jiii') from dual;
REPLACE('JACKANDJU
------------------
Jiiiack and Jiiiue
41.侧边插入数据
SQL> select lpad('abcdef',10,'1') from dual;
LPAD('ABCD
----------
1111abcdef
SQL> select rpad('abcdef',10,'1') from dual;
RPAD('ABCD
----------
abcdef1111
42. 匹配信息删除trim(leading/ trailing /both '中' from '中秋中')
SQL> select trim(0 from 3330000) from dual;
TRI
---
333
SQL> select trim('中' from '中秋') from dual;
TR
--
秋
SQL> select trim(leading '中' from '中秋中') from dual;
TRIM
----
秋中
SQL> select trim(trailing '中' from '中秋中') from dual;
TRIM
----
中秋
SQL> select trim(both '中' from '中秋中') from dual;
TR
--
秋
43.decode函数的使用
SQL> run
1 select empno,ename,job,sal,decode(job,'clerk',sal*1.5,
2 'salesman',sal*2.0,
3 'manager',sal*3,
4* sal ) as "new" from emp
EMPNO ENAME JOB SAL new
---------- ---------- --------- ---------- ----------
7369 SMITH CLERK 800 800
7499 ALLEN SALESMAN 1600 1600
7521 WARD SALESMAN 1250 1250
7566 JONES MANAGER 2975 2975
7654 MARTIN SALESMAN 1250 1250
7698 BLAKE SALESMAN 2850 2850
7782 CLARK MANAGER 2450 2450
7788 SCOTT ANALYST 3000 3000
7839 KING PRESIDENT 5000 5000
7844 TURNER SALESMAN 1500 1500
7876 ADAMS CLERK 1100 1100
EMPNO ENAME JOB SAL new
---------- ---------- --------- ---------- ----------
7900 JAMES CLERK 950 950
7902 FORD ANALYST 3000 3000
7934 MILLER CLERK 1300 1300
已选择14行。
44.分析函数
row_number()
SQL> select ename,job,deptno,sal,row_number() over (order by sal desc)
2 as sal_rank
3 from emp;
ENAME JOB DEPTNO SAL SAL_RANK
---------- --------- ---------- ---------- ----------
KING PRESIDENT 10 5000 1
FORD ANALYST 20 3000 2
SCOTT ANALYST 20 3000 3
JONES MANAGER 20 2975 4
BLAKE SALESMAN 30 2850 5
CLARK MANAGER 10 2450 6
ALLEN SALESMAN 30 1600 7
TURNER SALESMAN 30 1500 8
MILLER CLERK 10 1300 9
WARD SALESMAN 30 1250 10
MARTIN SALESMAN 30 1250 11
ENAME JOB DEPTNO SAL SAL_RANK
---------- --------- ---------- ---------- ----------
ADAMS CLERK 20 1100 12
JAMES CLERK 30 950 13
SMITH CLERK 20 800 14
已选择14行。
---------------------------------------------------------------------------------------------------------------------
rank()
SQL> select deptno,ename,sal,comm, rank() over(partition by deptno order by sal desc,comm) rank
2 from emp;
DEPTNO ENAME SAL COMM RANK
---------- ---------- ---------- ---------- ----------
10 KING 5000 1
10 CLARK 2450 2
10 MILLER 1300 3
20 SCOTT 3000 1
20 FORD 3000 1
20 JONES 2975 3
20 ADAMS 1100 4
20 SMITH 800 5
30 BLAKE 2850 1
30 ALLEN 1600 300 2
30 TURNER 1500 0 3
DEPTNO ENAME SAL COMM RANK
---------- ---------- ---------- ---------- ----------
30 WARD 1250 500 4
30 MARTIN 1250 1400 5
30 JAMES 950 6
已选择14行。
---------------------------------------------------------------------------------------------------------------------
dense_rank()
SQL> select d.dname,e.ename,e.sal,dense_rank() over(partition by e.deptno order by e.sal desc)
2 as denrank
3 from emp e, dept d
4 where e.deptno=d.deptno;
DNAME ENAME SAL DENRANK
-------------- ---------- ---------- ----------
ACCOUNTING KING 5000 1
ACCOUNTING MILLER 1300 2
RESEARCH SCOTT 3000 1
RESEARCH FORD 3000 1
RESEARCH JONES 2975 2
RESEARCH CLARK 2450 3
RESEARCH ADAMS 1100 4
RESEARCH SMITH 800 5
SALES BLAKE 2850 1
SALES ALLEN 1600 2
SALES TURNER 1500 3
DNAME ENAME SAL DENRANK
-------------- ---------- ---------- ----------
SALES WARD 1250 4
SALES MARTIN 1250 4
SALES JAMES 950 5
已选择14行。
二.
2.1
块的类型
无名块 存储过程 存储函数
2.2.oracle预定义异常
NO_DATA_FOUND：执行查询无数据、引用一个末初使化的表、通过UTL_FILE包调用到尾的文件
TOO_MANY_ROWS：采用SELECT INTO语句，但返回的记录超过了1条
DUP_VAL_ON_INDEX:插入或者更新语句，与唯一索引相冲突
TIMEOUT_ON_RESOURCE: 等待资源超时
TRANSACTION_BACKED_OUT：远程交易的部份交易已经回滚
INVALID_CURSOR:引用一个不存在的游标，如FETCH或者是CLOSE在其OPEN之前等
NOT_LOGGED_ON:在登陆ORACLE之前执行调用错误
LOGIN_DENIED:登陆时用户名或者密码非法
ZERO_DIVIDE:0为除数
INVALID_NUMBER: 将字符串转换成数字，但是转换失败
STORAGE_ERROR:内存不足
PROGRAM_ERROR: 系统自身程序错误
VALUE_ERROR: 在执行转换、截断、非法转换数据到文本出错
CURSOR_ALREADY_OPEN:打开一个已经打开的游标
2.3用户定义异常
声明用户定义异常
Declare
my_error EXCEPTION;
触发用户定义异常
RAISE my_error
处理用户定义异常
RAISE_APPLICATION_ERROR使用
SQL> declare
2 emp_no emp.empno%type;
3 emp_comm emp.comm%type;
4 comm_exception exception;
5 begin
6 emp_no:=7369;
7 select nvl(comm,0) into emp_comm from emp
8 where empno=emp_no;
9 if emp_comm=0 then
10 raise comm_exception;
11 else
12 dbms_output.put_line('奖金为:' || emp_comm );
13 end if;
14 EXCEPTION
15 when comm_exception then
16 raise_application_error(-20001,'未指定奖金的值');
17 end;
18 /
2.4游标
显式游标：用CURSOR..IS标明
声明游标：CURSOR ..IS select..
打开游标：OPEN
结果集控制：FETCH..INTO..
关闭游标：CLOSE