java常用數(shù)據(jù)庫oracle---深入理解oracle的實際運用
一、Oracle概念
oracle數(shù)據(jù)可系統(tǒng)是美國oracle（甲骨文）公司提供的以分布式數(shù)據(jù)庫為核心的一組軟件產(chǎn)品，是目前最流行的客戶/服務(wù)器或B/S體系結(jié)構(gòu)的數(shù)據(jù)庫之一，oracle數(shù)據(jù)庫時目前世界上使用最為廣泛的數(shù)據(jù)庫管理系統(tǒng)，作為一個通用的數(shù)據(jù)庫系統(tǒng)，它具有完善的數(shù)據(jù)庫管理功能，是關(guān)系型數(shù)據(jù)庫，比mysql更為龐大，在現(xiàn)行的關(guān)系型數(shù)據(jù)庫中排名第一（oracle、mysql、SqlServer），時間是最為精確的。
二、Oracle的安裝
安裝教程
三、Oracle數(shù)據(jù)庫的體系結(jié)構(gòu)
1、數(shù)據(jù)庫database
Oracle數(shù)據(jù)庫是數(shù)據(jù)的物理儲存，這就包括（數(shù)據(jù)文件ORA或者DBF、控制文件、聯(lián)機日志、參數(shù)文件）。其實Oracle數(shù)據(jù)庫的概念和其他數(shù)據(jù)庫不一樣，這里的數(shù)據(jù)庫時一個操作系統(tǒng)只有一個庫，可以看做Oracle就只有一個大數(shù)據(jù)庫。
2、實例
一個數(shù)據(jù)庫可以有n個實例，有一系列的后臺進程和內(nèi)存結(jié)構(gòu)組成。
3、數(shù)據(jù)文件dbf
是數(shù)據(jù)庫的物理儲存單位，數(shù)據(jù)庫的數(shù)據(jù)是儲存在表空間中的，真正是在某一個或者多個數(shù)據(jù)文件中，而一個表空間可以由一個或多個數(shù)據(jù)文件組成，一個數(shù)據(jù)文件只能屬于一個表空間，一旦數(shù)據(jù)文件被加入到某個表空間后，就不能刪除這個文件，如果要刪除某個數(shù)據(jù)文件，只能刪除其所屬于的表空間才行。
4、表空間
表空間是Oracle對物理數(shù)據(jù)庫上相關(guān)數(shù)據(jù)文件的邏輯映射，一個數(shù)據(jù)庫在邏輯上被劃分成一到若干個表空間，每個表空間包含了在邏輯上相關(guān)聯(lián)的一組結(jié)構(gòu)，每個數(shù)據(jù)庫至少有一個表空間（稱之為system表空間）
每個表空間由同一磁盤上的一個或多個文件組成，這些文件叫數(shù)據(jù)文件，一個數(shù)據(jù)文件只能屬于一個表空間。
5、用戶
用戶是在實例下建立的，不同實例中可以建相同名字的用戶，表的數(shù)據(jù)是由用戶放入某一個表空間的，而這個表龍劍會隨機把這些表數(shù)據(jù)進行管理和存放的，但是表不是由表空間去查詢的，而是由用戶去查詢。
6、SCOTT和HR用戶
是Oracle幫我們建好的測試賬戶，Scott賬戶常用，里面有emp、dept表等。
四、Oracle和MYSQL的差別
1、新建項目的方式
MYSQL : 創(chuàng)建一個數(shù)據(jù)庫,創(chuàng)建相應(yīng)的表
2、Oracle是多用戶的, MYSQL是多數(shù)據(jù)庫的
3、Oracle安全級別要高,MYSQL開源免費
五、基本查詢
1、SQL概念
結(jié)構(gòu)化查詢語言
2、SQL分類
DDL : 數(shù)據(jù)定義語言 create alter drop truncate
DML : 數(shù)據(jù)操縱語言 insert update delete
DCL : 數(shù)據(jù)控制語言 安全 授權(quán) grant revoke
DQL : 數(shù)據(jù)查詢語言 select from子句 where子句
在oracle中將DQL放入DML中，多了TCL：事務(wù)控制語言 commit、rollback
3、Oracle中的數(shù)據(jù)字典
所謂數(shù)據(jù)字典就是編寫sql語句，全選可以一次性執(zhí)行不報錯
/* 1.刪除數(shù)據(jù)對象 */
-- 刪除數(shù)據(jù)表
drop table t_reply;
drop table t_user;
-- 刪除序列
drop sequence sq_user;
drop sequence sq_reply;
/* 2.創(chuàng)建數(shù)據(jù)對象 */
-- 創(chuàng)建數(shù)據(jù)表
create table t_user(
u_id number(4) primary key,
u_userName varchar2(40) unique not null,
u_password varchar2(50) not null,
u_gender number(1) check(u_gender in (0,1,2)),
u_age number(3) check(u_age between 12 and 120),
u_registeTime date not null
);
create table t_reply(
r_id number(10) primary key,
r_content varchar2(500) not null,
r_userId number(4) not null,
foreign key(r_userId) references t_user(u_id)
);
-- 創(chuàng)建序列
create sequence sq_user start with 1001;
create sequence sq_reply start with 10000001;
/* 3.添加測試數(shù)據(jù) /
-- 處理全新的數(shù)據(jù)表和全新的序列在使用時取不到初始值的問題
insert into t_user values
(1, '1', '1', 1, 25, sysdate);
insert into t_reply values
(1, '1', 1);
delete from t_reply;
delete from t_user;
commit;
/insert into t_user values
(1000, 'owner', '9999', 1, 25, sysdate);
insert into t_reply values
(10000000, '人員召集...', 1000);*/
-- 添加數(shù)據(jù)
insert into t_user values
(sq_user.nextval, 'admin', '9999', 1, 25, sysdate);
insert into t_reply values
(sq_reply.nextval, '系統(tǒng)1.0上線...', sq_user.currval);
/* 4.提交 */
commit;
/* 5.查詢語句 /
/
select * from t_user;
select * from t_reply;
*/
3、查詢語句的結(jié)構(gòu)
select [列名] [*] from 表名 [where 條件] [group by 分組條件] [having 過濾] [order by 排序]
4、偽表dual
dual : oracle中的虛表 ,偽表, 主要是用來補齊語法結(jié)構(gòu)，
比如：select 1+1 from dual;
注意：直接寫一個常量比寫 * 要高效
select count(1) from emp;
select count(*) from emp;
5、別名查詢
使用as 關(guān)鍵字, 可以省略，別名中不能有特殊字符或者關(guān)鍵字, 如果有就加雙引號(比如空格）
elect ename 姓名, sal 工資 from emp;
select ename "姓 名", sal 工資 from emp;
6、去除重復(fù)數(shù)據(jù) distinct
加在select和列名后面，多列去除重復(fù): 每一列都一樣才能夠算作是重復(fù)
--單列去除重復(fù)
select distinct job from emp;
--多列去除重復(fù)的
select distinct job,deptno from emp;
7、查詢中四則運算
需要用到偽表dual
select 1+1 from dual;
--查詢員工年薪 = 月薪* 12
select sal*12 from emp;
在Oracle 中 ,雙引號主要是別名的時候使用, 單引號是使用的值, 是字符
8、空值處理函數(shù)
注意: null值 , 代表不確定的 不可預(yù)知的內(nèi)容 , 不可以做四則運算
1）mysql的空值處理
? ifnull(參數(shù)1, 參數(shù)2):
? 判斷參數(shù)1是否為null,
? 不為null, 則函數(shù)的結(jié)果取參數(shù)1的值
? 為null, 則取參數(shù)2的值
? if(參數(shù)1, 參數(shù)2, 參數(shù)3)
? 判斷參數(shù)1是否為null,
? 為null, 則取參數(shù)3的值
? 不為null, 則取參數(shù)2的值
? 注意if函數(shù)只能處理數(shù)值字段的空值
SELECT e.ename 姓名,
e.sal 底薪,
e.comm 獎金,
(e.sal+e.comm) 實發(fā),
IFNULL(e.sal+e.comm, e.sal) 實發(fā)1,
IF(e.comm, e.sal+e.comm, e.sal) 實發(fā)2
FROM emp e;
2）oracle的空值處理
nvl（參數(shù)1，參數(shù)2）：如果1為null，則返回2，不為null就為1
nvl2（參數(shù)1, 參數(shù)2, 參數(shù)3）1為null，則返回,3，不為null，則返回2
decode(需要判斷的字段,
? 常量值1, 結(jié)果值1,
? 常量值2, 結(jié)果值2,
? …
? 常量值n, 結(jié)果值n,
? 默認結(jié)果值
? )
select e.ename 姓名,
e.sal 底薪,
e.comm 獎金,
nvl(e.sal+e.comm, e.sal) 實發(fā)1,
nvl2(e.comm, e.sal+e.comm, e.sal) 實發(fā)2,
decode(e.comm, null, e.sal, e.sal+e.comm) 實發(fā)3
from emp e;
9、字符串拼接
Oracle 特有的連接符: || 拼接
concat(str1,str2) 函數(shù), 在mysql和Oracle中都有
--查詢員工姓名 : 姓名:SCOTT
select ename from emp;
--使用拼接符
select '姓名:' || ename from emp;
--使用函數(shù)拼接
select concat('姓名:',ename) from emp;
六、條件查詢
條件查詢就是where后面的寫法
1、關(guān)系運算符和排序
= = < <= != <>
注意：不等于既可以用！=也可以用<>但是<>效率更高
2、邏輯運算符
and or not
3、其它運算符
like 模糊查詢
in(set) 在某個集合內(nèi)
between..and.. 在某個區(qū)間內(nèi)
is null 判斷為空
is not null 判斷不為空
4、模糊查詢: like
% 匹配多個字符
_ 匹配單個字符
如果有特殊字符, 需要使用escape轉(zhuǎn)義
--查詢員工姓名中,包含%的員工信息
select * from emp where ename like '%%%' escape '';
select * from emp where ename like '%#%%' escape '#';
5、排序order by
升序: asc ascend
降序: desc descend
排序注意null問題 : nulls first | last指定null值顯示的位置
同時排列多列, 用逗號隔開
--查詢員工信息,按照獎金由高到低排序
select * from emp order by comm desc nulls last;
--查詢部門編號和按照工資 按照部門升序排序, 工資降序排序
select deptno, sal from emp order by deptno asc, sal desc;
分析函數(shù)：用來處理排名并列問題
rank——排名可并列，之后序號不會順眼（如2個第二，下一個是第三名）
dense_rank——排名可并列，之后序號會順眼（如2個第二，下一個是第四名）
row_number——不可并列排名
over——添加分組條件
partition—by——分組
6、分組排序
select 分組的條件，分組之后的條件 from 表名 group by 分組的條件 having 條件過濾
在分組查詢語句的檢索內(nèi)容中, 只能出現(xiàn)分組字段和聚合函數(shù)
sql的編寫順序：
? select,from,where,group by,having,order by
sql的執(zhí)行順序：
? from,where,group by,having,select,order by…
where和having的區(qū)別：
where后面不能跟聚合函數(shù)，可以接單行函數(shù)
having是在group by之后執(zhí)行，可以接聚合函數(shù)
--分組統(tǒng)計所有部門的平均工資，找出平均工資大于1500的部門
select deptno,avg(sal) from emp group by deptno having avg(sal)>1500;
--報錯,給分組之后的條件取別名的時候出錯，因為sql語句執(zhí)行的順序問題
select deptno,avg(sal),bb from emp group by deptno having bb>1500;
七、函數(shù)
函數(shù): 必須要有返回值
1、單行函數(shù)
對某一行中的某個值進行處理
1）數(shù)值函數(shù)
取整
select ceil(45.926) from dual; --46 向上取整
select floor(45.926) from dual; --45 向下取整
四舍五入
round（a，b）
b如果是正數(shù)代表取到小數(shù)點后b位數(shù)為止
b如果是負數(shù)代表取到十位為止
elect round(45.926,2) from dual; --45.93
select round(45.926,1) from dual; -- 45.9
select round(45.926,0) from dual; --46
select round(45.926,-1) from dual; --50
select round(45.926,-2) from dual; --0
select round(65.926,-2) from dual; --100
截斷（去尾法）
select trunc(45.926,2) from dual; --45.92
select trunc(45.926,1) from dual; -- 45.9
select trunc(45.926,0) from dual; --45
select trunc(45.926,-1) from dual; --40
select trunc(45.926,-2) from dual; --0
select trunc(65.926,-2) from dual; --0
求余
select mod(9,3) from dual; --0
select mod(9,4) from dual; --1
2）字符函數(shù)
substr(str1,起始索引,長度)
--注意: 起始索引不管寫 0 還是 1 都是從第一個字符開始截取
select substr('abcdefg',0,3) from dual; --abc
select substr('abcdefg',1,3) from dual; --abc
select substr('abcdefg',2,3) from dual; --bcd
--獲取字符串長度 24 28
select length('abcdefg') from dual;
--去除字符左右兩邊的空格
select trim(' hello ') from dual;
--替換字符串
Select replace('hello','l','a') from dual;
3） 日期函數(shù)
-查詢今天的日期（查詢的是服務(wù)器上的日期）
select sysdate from dual;
--查詢3個月后的今天的日期
select add_months(sysdate,3) from dual;
--查詢3天后的日期
select sysdate + 3 from dual;
--查詢員工入職的天數(shù)
select sysdate - hiredate from emp;
select ceil(sysdate - hiredate) from emp;
--查詢員工入職的周數(shù)
select (sysdate - hiredate)/7 from emp;
--查詢員工入職的月數(shù)
select months_between(sysdate,hiredate) from emp;
--查詢員工入職的年份
select months_between(sysdate,hiredate)/12 from emp;
4） 轉(zhuǎn)換函數(shù)
數(shù)值轉(zhuǎn)字符
select to_char(sal,'$9,999.99') from emp;
select to_char(sal,'L9,999.99') from emp;
/*
to_char(1210.73, '9999.9') 返回 '1210.7'
to_char(1210.73, '9,999.99') 返回 '1,210.73'
to_char(1210.73, '$9,999.00') 返回 '$1,210.73'
to_char(21, '000099') 返回 '000021'
to_char(852,'xxxx') 返回' 354'
*/
日期轉(zhuǎn)字符 to_char() （不區(qū)分大小寫）
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') from dual; --默認的是24小時制度
--只想要年
select to_char(sysdate,'yyyy') from dual; --2017
--只想要日
select to_char(sysdate,'d') from dual; --2 代表一個星期中第幾天（美國周天是一個星期的第一天）
select to_char(sysdate,'dd') from dual; --10 代表一個月中的第幾天
select to_char(sysdate,'ddd') from dual; --100 代表一年中的第幾天
select to_char(sysdate,'day') from dual; --monday（星期的英文）
select to_char(sysdate,'dy') from dual; --mon 星期的簡寫
字符轉(zhuǎn)日期
select to_date('2017-04-10','yyyy-mm-dd') from dual;
--查詢1981年 -- 1985年入職的員工信息
select * from emp where hiredate between to_date('1981','yyyy') and to_date('1985','yyyy');
5） 通用函數(shù)
nvl(參數(shù)1,參數(shù)2) 如果參數(shù)1 = null 就返回參數(shù)2
nvl2(參數(shù)1,參數(shù)2,參數(shù)3) 如果參數(shù)1 = null ,就返回參數(shù)3, 否則返回參數(shù)2
nullif(參數(shù)1,參數(shù)2) 如果參數(shù)1 = 參數(shù)2 那么就返回 null , 否則返回參數(shù)1
coalesce: 返回第一個不為null的值
select nvl2(null,5,6) from dual; --6;
select nvl2(1,5,6) from dual; --5;
select nullif(5,6) from dual; --5
select nullif(6,6) from dual; --null
select coalesce(null,null,3,5,6) from dual; --3
2、多行函數(shù)（聚合函數(shù)）
對某一列的所有行進行處理
max() min count sum avg
注意：直接忽略空值
例子：
--統(tǒng)計員工的平均獎金 550 錯誤 2200/14 =，因為空值產(chǎn)生的影響
select avg(comm) from emp;
--統(tǒng)計員工的平均獎金 157.
select sum(comm)/count(1) from emp;
select ceil(sum(comm)/count(1)) from emp;
3、條件表達式
作用：查詢的時候用來替換列中所有數(shù)據(jù)的值，區(qū)別于取別名，取別名只是單純的給子彈取別名
/*
條件表達式：
case 字段
when 值1 then 值
when 值2 then 值
else
默認值
end "別名"
case、when通用的寫法，mysql和oracle中都可以用
oracle特有的寫法：decode(字段,if1,then1,if2,then2,else1)
*/
--給表中姓名取一個中文名
select
case ename
when 'SMITH' then '劉備小二'
when 'ALLEN' then '薩達'
else
'路人甲'
end "中文名"
from emp;
--oracle 特有寫法
select decode(ename,'SMITH','劉備小二','ALLEN','薩達','路人甲') from emp;
八、多表查詢
1、笛卡爾積
實際上是兩張表的乘積,但是在實際開發(fā)中沒有太大意義，格式: select * from 表1,表2
select * from emp;
select * from dept;
select * from emp, dept;
select * from emp e1, dept d1 where e1.deptno = d1.deptno;
2、內(nèi)連接
隱式內(nèi)聯(lián)接:
? 等值內(nèi)聯(lián)接: where e1.deptno = d1.deptno;
? 不等值內(nèi)聯(lián)接: where e1.deptno <> d1.deptno;
? 自聯(lián)接: 自己連接自己
顯式內(nèi)聯(lián)接:
? select * from 表1 inner join 表2 on 連接條件
? inner 關(guān)鍵字可以省略
--查詢員工編號,員工姓名,經(jīng)理的編號,經(jīng)理的姓名
select e1.empno,e1.ename,e1.mgr,m1.ename
from emp e1, emp m1 where e1.mgr= m1.empno;
--查詢員工編號,員工姓名,員工的部門名稱,經(jīng)理的編號,經(jīng)理的姓名
select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename
from emp e1, emp m1,dept d1 where e1.mgr= m1.empno and e1.deptno = d1.deptno;
--查詢員工編號,員工姓名,員工的部門名稱,經(jīng)理的編號,經(jīng)理的姓名,經(jīng)理的部門名稱
select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename,d2.dname
from emp e1, emp m1,dept d1,dept d2
where
e1.mgr= m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
;
--查詢員工編號,員工姓名,員工的部門名稱,員工的工資等級,經(jīng)理的編號,經(jīng)理的姓名,經(jīng)理的部門名稱
select e1.empno,e1.ename,d1.dname,s1.grade,e1.mgr,m1.ename,d2.dname
from emp e1, emp m1,dept d1,dept d2,salgrade s1
where
e1.mgr= m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal
;
--查詢員工編號,員工姓名,員工的部門名稱,員工的工資等級,經(jīng)理的編號,經(jīng)理的姓名,經(jīng)理的部門名稱,經(jīng)理的工資等級
select e1.empno,e1.ename,d1.dname,s1.grade,e1.mgr,m1.ename,d2.dname,s2.grade
from emp e1, emp m1,dept d1,dept d2,salgrade s1,salgrade s2
where
e1.mgr= m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal
and m1.sal between s2.losal and s2.hisal
;
--查詢員工編號,員工姓名,員工的部門名稱,員工的工資等級,經(jīng)理的編號,經(jīng)理的姓名,經(jīng)理的部門名稱,經(jīng)理的工資等級
--將工資等級 1,2,3,4 顯示成 中文的 一級 二級 三級...
select e1.empno,
e1.ename,
d1.dname,
case s1.grade
when 1 then '一級'--為所有列更換成你想要的信息
when 2 then '二級'
when 3 then '三級'
when 4 then '四級'
else
'五級'
end "等級",--取別名
e1.mgr,
m1.ename,
d2.dname,
decode(s2.grade,1,'一級',2,'二級',3,'三級',4,'四級','五級') "等級"
from emp e1, emp m1,dept d1,dept d2,salgrade s1,salgrade s2
where
e1.mgr= m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal
and m1.sal between s2.losal and s2.hisal
;
--查詢員工姓名和員工部門所處的位置
select e1.ename,d1.loc from emp e1,dept d1 where e1.deptno = d1.deptno;
select * from emp e1 inner join dept d1 on e1.deptno = d1.deptno;
2、外連接
外連接: (標準,通用寫法)
? 左外連接: left outer join 左表中所有的記錄,如果右表沒有對 應(yīng)記錄,就顯示空
? 右外連接: right outer join 右表中的所有記錄,如果左表沒有對應(yīng)記錄,就顯示空
? outer 關(guān)鍵字可以省略
Oracle中的外連接: (+) 實際上是如果沒有對應(yīng)的記錄就加上空值
? select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);
select * from emp e1 left outer join dept d1 on e1.deptno = d1.deptno;
insert into emp(empno,ename) values(9527,'HUAAN');
select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);
select * from emp e1 right outer join dept d1 on e1.deptno = d1.deptno;
select * from emp e1,dept d1 where e1.deptno(+) = d1.deptno;
九、子查詢
查詢語句中嵌套查詢語句; 用來解決復(fù)雜的查詢語句
1、單行子查詢
= = < <= <> !=
--查詢最高工資的員工信息
--1.查詢出最高工資 --5000
select max(sal) from emp;
--2. 工資等于最高工資
select * from emp where sal = (select max(sal) from emp);
--查詢出比雇員7654的工資高,同時和7788從事相同工作的員工信息
--1.雇員7654的工資 1250
select sal from emp where empno = 7654;
--2.7788從事的工作 ANALYST
select job from emp where empno = 7788;
--3.兩個條件合并
select * from emp where sal > 1250 and job = 'ANALYST';
select * from emp where sal > (select sal from emp where empno = 7654) and job = (select job from emp where empno = 7788);
--查詢每個部門最低工資的員工信息和他所在的部門信息
--1.查詢每個部門的最低工資,分組統(tǒng)計
select deptno,min(sal) minsal from emp group by deptno;
--2.員工工資等于他所處部門的最低工資
select *
from emp e1,
(select deptno,min(sal) minsal from emp group by deptno) t1 --將查詢到的結(jié)果作為一張表進行兩表連查
where e1.deptno = t1.deptno and e1.sal = t1.minsal;
--3.查詢部門相關(guān)信息
select *
from emp e1,
(select deptno,min(sal) minsal from emp group by deptno) t1,
dept d1
where e1.deptno = t1.deptno and e1.sal = t1.minsal and e1.deptno = d1.deptno;
2、多行子查詢
in not in >any >all exists not exists
通常情況下, 數(shù)據(jù)庫中不要出現(xiàn)null 最好的做法加上Not null
? null值并不代表不占空間, char(100) null 100個字符
--查詢不是領(lǐng)導(dǎo)的信息
select * from emp where empno not in (select mgr from emp);
select * from emp where empno <>all(select mgr from emp);
--正確的寫法
select * from emp where empno not in (select mgr from emp where mgr is not null);
3、exists(查詢語句)
存在的意思,判斷一張表里面的記錄是否存在與另外一張表中，作布爾值來處理:， 當查詢語句有結(jié)果的時候, 就是返回true，否則返回的是false，數(shù)據(jù)量比較大的時候是非常高效的
--查詢有員工的部門的信息
select * from dept d1 where exists(select * from emp e1 where e1.deptno = d1.deptno );
1
2
十、分頁
Oracle的分頁查詢需要使用偽列輔助
偽列 : 在Oracle的結(jié)果集中默認帶有的可以使用的
? 不出現(xiàn)在結(jié)果集列中的內(nèi)容.
? rownum : 偽列序號, 從1開始
? rowid : 偽列地址
SQL執(zhí)行順序
? from … where …group by…h(huán)aving … select…rownum…order by
默認都是從1開始。
rownum按照員工id排序后再分頁
select t.*
from (
select rownum rn,e.*
from (
select *
from emp
order by empno
) e
) t
where t.rn between 5 and 9;
rowid去除表中重復(fù)數(shù)據(jù)
create table p(
name varchar2(10)
);
insert into p values('黃偉福');
insert into p values('趙洪');
insert into p values('楊華');
delete from p where
select rowid,p.* from p;
select distinct * from p;
delete from p p1 where rowid > (select min(rowid) from p p2 where p1.name = p2.name);
十一、集合運算
所有的查詢結(jié)果可能不是來自同一張表, 將查詢結(jié)果進行合并，并去除重復(fù)項
1、并集
union : 去除重復(fù)的,并且排序
union all : 不會去除重復(fù)的
select * from emp where sal > 1500
union
select * from emp where deptno = 20;
select * from emp where sal > 1500
union all
select * from emp where deptno = 20;
2、交集運算: intersect
--工資大于1500,并且20號部門下的員工
select * from emp where sal > 1500;
select * from emp where deptno = 20;
select * from emp where sal > 1500
intersect
select * from emp where deptno = 20;
3、差集運算
兩個結(jié)果相減
--1981年入職員工(不包括總裁和經(jīng)理)
--1981年入職員工
select * from emp where to_char(hiredate,'yyyy')='1981';
--總裁和經(jīng)理
select * from emp where job = 'PRESIDENT' or job = 'MANAGER';
select * from emp where to_char(hiredate,'yyyy')='1981'
minus
select * from emp where job = 'PRESIDENT' or job = 'MANAGER';
4、集合運算中的注意事項
1.列的類型要一致
2.按照順序?qū)?br>
3.列的數(shù)量要一致,如果不足,用空值填充
select ename,sal from emp where sal > 1500
union
select ename,sal from emp where deptno = 20;
--列的類型不匹配
select ename,sal from emp where sal > 1500
union
select sal,ename from emp where deptno = 20;
--列的數(shù)量不匹配
select ename,sal,deptno from emp where sal > 1500
union
select ename,sal from emp where deptno = 20;
select ename,sal,deptno from emp where sal > 1500
union
select ename,sal,null from emp where deptno = 20;
select ename,sal,deptno from emp where sal > 1500
union
select ename,sal,66 from emp where deptno = 20;
select * from emp;
select * from dept;
十二、DDL語句管理表
1、表空間
邏輯單位, 通常我們新建一個項目,就會去新建表空間,在表空間中創(chuàng)建用戶來創(chuàng)建表
創(chuàng)建表空間
語法:
? create tablespace 表空間的名稱
? datafile ‘文件的路徑(服務(wù)器上)’
? size 大小
? autoextend on 自動擴展
? next 每次擴展的大小
--創(chuàng)建表空間-
create tablespace canghe
datafile 'C:\oracle\canghe.dbf'
size 100m
autoextend on
next 10m;
刪除表空間
--刪除表空間
drop tablespace canghe;
2、創(chuàng)建用戶
create user 用戶名
identified by 密碼
default tablespace 表空間的名稱
create user canghe
identified by canghe
default tablespace canghe;
授權(quán)
--授予 dba的角色
grant dba to canghe;
select * from scott.emp;
3、創(chuàng)建表
create table 表名(
? 列名 列的類型 [列的約束],
? 列名 列的類型 [列的約束]
? );
列的類型:
varchar ,在Oracle中,目前是支持的, 但是不保證以后還支持
varchar2(長度) 可變字符長度 varchar2(10) hello 占5個字符
char(長度) 固定長度字符 char(10) hello 占10個字符,用空格填充
number(總長度,小數(shù)長度) 數(shù)字類型 --小數(shù)長度不能大于等于總長度
date 年月日時分秒 2017/4/13 9:43:49
timestamp 時間戳, 比date類型更加精確 13-APR-17 09.44.08.272000 AM +08:00
LONG/CLOB : 存放一本小說
BLOB : 存放電影 java 存進去, 再讀取出來
使用子查詢的方式創(chuàng)建表
create table 表名 as 查詢語句;
注意: 只會復(fù)制表結(jié)構(gòu)和表中的數(shù)據(jù),不會復(fù)制列的約束
如果查詢語句有結(jié)果, 就是復(fù)制 表結(jié)構(gòu)和數(shù)據(jù)
如果查詢語句沒有結(jié)果, 就是復(fù)制 表結(jié)構(gòu)
create table test1(
name1 varchar2(10),
name2 char(10),
age number(4,2)
);
create table 表名 as 查詢語句; 復(fù)制表
create table emp as select * from scott.emp;
4、修改表
添加咧
alter table 表名 add 列
修改列
修改列類型：modify
修改列名：rename
刪除咧
alter table 表名 drop column 列名;
修改列名
alter table 表名 rename column 列名;
重命名表
rename 舊表名 to 新表名;
5、刪除表
drop table 表名
6、表的五大約束
主鍵約束
primary key
非空約束
not null
唯一約束
unique
外鍵約束
添加外鍵約束：
alter table 表名 add foreign key（添加約束的字段） references 引用的表名 （引用的字段）
插入：先主表、再從表
刪除：先從表，再主表
強制刪除表（不建議）：
drop table 表名 cascade constraint;
級聯(lián)刪除:
添加外鍵約束,使用級聯(lián)約束 ,在刪除的時候,使用級聯(lián)刪除
----添加外鍵約束,使用級聯(lián)約束 ,在刪除的時候,使用級聯(lián)刪除
alter table product add foreign key(cno) references category(cid) on delete cascade;
insert into category values(2,'電腦辦公');
insert into product values(11,'外星人',2);
--級聯(lián)刪除 : 首先去從表中找有沒有 關(guān)聯(lián)數(shù)據(jù), 如果在從表中找到關(guān)聯(lián)數(shù)據(jù),先刪除從表中關(guān)聯(lián)數(shù)據(jù),然后再刪除表中的數(shù)據(jù)
delete from category where cid = 2;
檢查約束
check( 列名 in (‘值1’,‘值2’,‘值3’))
十三、DML管理表數(shù)據(jù)
1、插入數(shù)據(jù)
指定列名插入
insert into 表名 values(所有列的值都要對應(yīng)寫上)
不指定列名插入
insert into 表名(列1,列2) values(值1,值2);
使用子查詢插入
insert into 表名 查詢語句
2、修改數(shù)據(jù)
update 表名 set 列名 = 列的值 [where 條件]
3、刪除數(shù)據(jù)
delete from 表名 [where 條件]
delete和truncate 區(qū)別
delete: truncate:
DML DDL
逐條刪除 先刪除表再創(chuàng)建表
支持事務(wù)操作 不支持事務(wù)操作,
執(zhí)行效率要高
十四、其他數(shù)據(jù)庫對象
1、事務(wù)
事務(wù): 就是一系列的操作,要么都成功,要么都失敗
? 四大特性: 原子性,隔離性,持久性,一致性
?
如果不考慮隔離級別: 臟讀,虛讀,不可重復(fù)讀
MYSQL隔離級別: READ UNCOMMITTED , READ COMMITTED, REPEATABLE READ, SERIALIAZABLE
ORACLE隔離級別: READ COMMITTED SERIALIZABLE READ ONLY
默認隔離級別: READ COMMITTED
提交 : commit
事務(wù)的保存點/回滾點: savepoint 保存點的名稱
回滾: rollback
2、視圖
視圖: 是對查詢結(jié)果的一個封裝
? 視圖里面所有的數(shù)據(jù),都是來自于它查詢的那張表,視圖本身不存儲任何數(shù)據(jù)
? 1.能夠封裝復(fù)雜的查詢結(jié)果
? 2.屏蔽表中的細節(jié)
? 語法:
? create [or replace] view 視圖的名稱 as 查詢語句 [ with read only]
? 注意: 通常不要通過視圖去修改,視圖創(chuàng)建的時候,通常要加上with read only
--創(chuàng)建一個視圖
create or replace view view_test1 as select ename,job,mgr from emp;
--通過視圖修改數(shù)據(jù)
update view_test1 set ename='SMITH2' where ename = 'SMITH';
--創(chuàng)建一個只讀視圖
create or replace view view_test2 as select ename,job,mgr from emp with read only;
update view_test2 set ename='SMITH3' where ename = 'SMITH2';
-同義詞的概念（就是為視圖取的一個別名）
create synonym dept for view_test3;
3、序列
oracle中用來控制自增長的
語法:
create sequence 序列的名稱
start with 從幾開始
increment by 每次增長多少
maxvalue 最大值 | nomaxvalue
minvalue 最小值 | nominvalue
cycle | nocycle 是否循環(huán) 1,2,3,1,2,3
cache 緩存的數(shù)量3 | nocache 1,2,3,4,5,6
如何從序列獲取值
currval : 當前值
nextval : 下一個值
注意: currval 需要在調(diào)用nextval之后才能使用
永不回頭,往下取數(shù)據(jù), 無論發(fā)生異常, 回滾
--序列用的最多的一種寫法
create sequence seq_test2;
select seq_test2.nextval from dual;
4、索引
索引:相當于是一本書的目錄,能夠提高我們的查詢效率
如果某一列,你經(jīng)常用來作為查詢條件,那么就有必要創(chuàng)建索引,數(shù)據(jù)量比較的情況
語法:
create index 索引的名稱 on 表名(列)
注意:主鍵約束自帶主鍵索引, 唯一約束自帶唯一索引
索引原理: btree balance Tree 平衡二叉樹
如果某列作為查詢條件的時候,可以提高查詢效率,但是修改的時候,會變慢
索引創(chuàng)建好之后,過了一段,DBA都會去做重構(gòu)索引
SQL調(diào)優(yōu):
1.查看執(zhí)行計劃F5
2. 分析里面的cost 和 影響行數(shù), 想辦法降低
十五、PLSQL編程
1、概述
procedure Language 過程語言 Oracle對SQL的一個擴展
? 讓我們能夠像在java中一樣寫 if else else if 條件, 還可以編寫循環(huán)邏輯 for while
2、基本語法
declare
--聲明變量
變量名 變量類型;
變量名 變量類型 := 初始值;
vsal emp.sal%type; --引用型的變量
vrow emp%rowtype; --聲明記錄型變量
begin
--業(yè)務(wù)邏輯,比如輸出一句話：
dbms_output.put_line()相當于java中 syso
end;
declare
i varchar2(10) := '張三';
begin
dbms_output.put_line(i);
end;
3、變量
%type --引用型變量
%rowtype --記錄型變量
4、PL條件判斷
if then
elsif then
else
end if;
declare
age number := &aaa;
begin
if age < 18 then
dbms_output.put_line('小屁孩');
elsif age>=18 and age <=24 then
dbms_output.put_line('年輕人');
elsif age>24 and age < 40 then
dbms_output.put_line('老司機');
else
dbms_output.put_line('老年人');
end if;
end;
5、循環(huán)操作
while 循環(huán)
while 條件 loop
end loop;
for循環(huán)
for 變量 in [reverse] 起始值..結(jié)束值 loop
end loop;
loop循環(huán)
loop
exit when 條件
end loop;
--輸出1~10
declare
i number :=1;
begin
while i<=10 loop
dbms_output.put_line(i);
i := i+1;
end loop;
end;
--輸出1~10
declare
begin
for i in reverse 1..10 loop
dbms_output.put_line(i);
end loop;
end;
--輸出1~10
declare
i number :=1;
begin
loop
exit when i>10;
dbms_output.put_line(i);
i := i+1;
end loop;
end;
/*
輸出 m
x : [-m,m]
y : [-m,m]
輸出所有滿足條件的 : abs(y)+abs(x) <=m
m取值
/
--使用PLSQL輸出菱形
declare
m number := 10;
begin
for x in -m..m loop
for y in -m..m loop
if abs(y) + abs(x) <= m then
dbms_output.put('');
else
dbms_output.put(' ');
end if;
end loop;
dbms_output.new_line();
end loop;
end;
--使用PLSQL輸出三角形,只要是三個角
declare
m number := 10;
begin
for x in reverse -m..m loop
for y in -m..m loop
if abs(y) + abs(x) <= m and x>=0 then
dbms_output.put('*');
else
dbms_output.put(' ');
end if;
end loop;
dbms_output.new_line();
end loop;
end;
十六、游標
用來操作查詢的結(jié)果集.相當于JDBC中的ResultSe
語法: cursor 游標名[(參數(shù)名 參數(shù)類型)] is 查詢結(jié)果集
開發(fā)步驟:
1. 聲明游標
2. 打開游標 open 游標名
3. 從游標中取數(shù)據(jù) fetch 游標名 into 變量
游標名%found :找到數(shù)據(jù)
游標名%notfound : 沒有找到數(shù)據(jù)
4. 關(guān)閉游標 close 游標名
系統(tǒng)引用游標
1. 聲明游標 : 游標名 sys_refcursor
2. 打開游標: open 游標名 for 結(jié)果集
3. 從游標中取數(shù)據(jù)
4. 關(guān)閉游標
for循環(huán)遍歷游標:
不需要聲明額外變量
不需要打開游標
不需要關(guān)閉游標
1、不帶參數(shù)游標
/
--輸出員工表中所有的員工姓名和工資(不帶參數(shù)游標)
/
游標:所有員工
聲明一個變量,用來記錄一行數(shù)據(jù) %rowtype
*/
declare
--游標
cursor vrows is select * from emp;
--s聲明變量,記錄一行數(shù)據(jù)
vrow emp%rowtype;
begin
--1.打開游標
open vrows;
--2.從游標提取數(shù)據(jù)
--循環(huán)取數(shù)據(jù)
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line('姓名:'||vrow.ename ||' 工資: ' || vrow.sal);
end loop;
--3.關(guān)閉游標
close vrows;
end;
2、帶參數(shù)的游標
--輸出指定部門下的員工姓名和工資
/*
游標: 指定部門的所有員工
聲明一個變量記錄一行數(shù)據(jù)
*/
declare
--聲明游標
cursor vrows(dno number) is select * from emp where deptno = dno;
--聲明變量
vrow emp%rowtype;
begin
--1.打開游標 , 指定10號部門
open vrows(10);
--2. 循環(huán)遍歷,取數(shù)據(jù)
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line('姓名:'||vrow.ename ||' 工資: ' || vrow.sal);
end loop;
close vrows;
end;
3、系統(tǒng)引用游標
--輸出員工表中所有的員工姓名和工資
declare
--聲明系統(tǒng)引用游標
vrows sys_refcursor;
--聲明一個變量
vrow emp%rowtype;
begin
--1.打開游標
open vrows for select * from emp;
--2.取數(shù)據(jù)
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line('姓名:'||vrow.ename ||' 工資: ' || vrow.sal);
end loop;
close vrows;
end;
4、-使用for循環(huán)遍歷游標
--按照員工工作給所有員工漲工資,總裁漲1000,經(jīng)理漲800,其他人漲400
/*
游標 : 所有員工
聲明一個記錄一行數(shù)據(jù)
*/
declare
--聲明游標
cursor vrows is select * from emp;
--聲明一個變量
vrow emp%rowtype;
begin
--1.打開游標
open vrows;
--2.循環(huán)取數(shù)據(jù)
loop
--取數(shù)據(jù)
fetch vrows into vrow;
--退出條件
exit when vrows%notfound;
--根據(jù)不同的職位,漲工資 總裁漲1000,經(jīng)理漲800,其他人漲400
if vrow.job = 'PRESIDENT' then
update emp set sal = sal + 1000 where empno = vrow.empno;
elsif vrow.job = 'MANAGER' then
update emp set sal = sal + 800 where empno = vrow.empno;
else
update emp set sal = sal + 400 where empno = vrow.empno;
end if;
end loop;
--3.關(guān)閉游標
close vrows;
--4.提交事務(wù)
commit;
end;
十七、例外
例外:(意外)程序運行的過程發(fā)生異常,相當于是JAVA中的異常
declare
--聲明變量
begin
--業(yè)務(wù)邏輯
exception
--處理異常
when 異常1 then
...
when 異常2 then
...
when others then
...處理其它異常
end;
zero_divide : 除零異常
value_error : 類型轉(zhuǎn)換異常
too_many_rows : 查詢出多行記錄,但是賦值給了rowtype記錄一行數(shù)據(jù)變量
no_data_found : 沒有找到數(shù)據(jù)
自定義異常:
異常名 exception;
raise 異常名
--查詢指定編號的員工,如果沒有找到,則拋出自定義的異常
/*
--錯誤的演示
1.聲明一個變量 %rowtype
2.查詢員工信息,保存起來
3.判斷員工信息是否為空
4. 如果是 則拋出異常
*/
declare
-- 1.聲明一個變量 %rowtype
vrow emp%rowtype;
--2 .聲明一個自定義的異常
no_emp exception;
begin
--查詢員工信息,保存起來
select * into vrow from emp where empno = 8888; --拋出異常
if vrow.sal is null then
raise no_emp; --拋出自定義的異常
end if;
exception
when no_emp then
dbms_output.put_line('輸出了自定義的異常');
when others then
dbms_output.put_line('輸出了其它異常'||sqlerrm);
end;
--查詢指定編號的員工,如果沒有找到,則拋出自定義的異常
/*
游標來判斷
%found %notfound
聲明一個游標
聲明一個變量,記錄數(shù)據(jù)
從游標中取記錄
如果有,則不管它
如果沒有就拋出自定義的異常
*/
declare
--聲明游標
cursor vrows is select * from emp where empno=8888;
--聲明一個記錄型變量
vrow emp%rowtype;
--聲明一個自定義異常
no_emp exception;
begin
--1.打開游標
open vrows;
--2.取數(shù)據(jù)
fetch vrows into vrow;
--3.判斷游標是否有數(shù)據(jù)
if vrows%notfound then
raise no_emp;
end if;
close vrows;
exception
when no_emp then
dbms_output.put_line('發(fā)生了自定義的異常');
end;
十八、存儲過程
存儲過程: 實際上是封裝在服務(wù)器上一段PLSQL代碼片斷,已經(jīng)編譯好了的代碼
1.客戶端取調(diào)用存儲過程,執(zhí)行效率就會非常高效
語法:
create [or replace] procedure 存儲過程的名稱(參數(shù)名 in|out 參數(shù)類型,參數(shù)名 in|out 參數(shù)類型)
is | as
--聲明部分
begin
--業(yè)務(wù)邏輯
end;
/
--給指定員工漲薪,并打印漲薪前和漲薪后的工資
/
參數(shù) : in 員工編號
參數(shù) : in 漲多少
聲明一個變量 : 存儲漲工資前的工資
查詢出當前是多少
打印漲薪前的工資
更新工資
打印漲薪后的工資
*/
create or replace procedure proc_updatesal(vempno in number,vnum in number)
is
--聲明變量.記錄當前工資
vsal number;
begin
--查詢當前的工資
select sal into vsal from emp where empno = vempno;
--輸出漲薪前的工資
dbms_output.put_line('漲薪前:'||vsal);
--更新工資
update emp set sal = vsal + vnum where empno = vempno;
--輸出漲薪后的工資
dbms_output.put_line('漲薪后:'||(vsal+vnum));
--提交
commit;
end;
--調(diào)用方式1
call proc_updatesal(7788,10);
--調(diào)用方式2 用的最多的方式
declare
begin
proc_updatesal(7788,-100);
end;
十九、存儲函數(shù)
1、基本語法和例子
存儲函數(shù): 實際上是一段封裝是Oracle服務(wù)器中的一段PLSQL代碼片斷,它是已經(jīng)編譯好了的代碼片段
?
語法:
create [or replace] function 存儲函數(shù)的名稱(參數(shù)名 in|out 參數(shù)類型,參數(shù)名 in|out 參數(shù)類型) return 參數(shù)類型
is | as
begin
end;
存儲過程和函數(shù)的區(qū)別:
1.它們本質(zhì)上沒有區(qū)別
2.函數(shù)存在的意義是給過程調(diào)用 存儲過程里面調(diào)用存儲函數(shù)
3.函數(shù)可以在sql語句里面直接調(diào)用
4.存儲過程能實現(xiàn)的,存儲函數(shù)也能實現(xiàn),存儲函數(shù)能實現(xiàn)的,過程也能實現(xiàn)
默認是 in
/
--查詢指定員工的年薪
/
參數(shù) : 員工的編號
返回 : 年薪
/
create or replace function func_getsal(vempno number) return number
is
--聲明變量.保存年薪
vtotalsal number;
begin
select sal12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;
return vtotalsal;
end;
--調(diào)用存儲函數(shù)
declare
vsal number;
begin
vsal := func_getsal(7788);
dbms_output.put_line(vsal);
end;
2、java調(diào)用存儲過程、存儲函數(shù)
1）基本套路
/*
JAVA調(diào)用存儲過程
JDBC的開發(fā)步驟:
1.導(dǎo)入驅(qū)動包
2.注冊驅(qū)動
3.獲取連接
4.獲取執(zhí)行SQL的statement
5.封裝參數(shù)
6.執(zhí)行SQL
7.獲取結(jié)果
8.釋放資源
*/
/*
封裝一個存儲過程 : 輸出所有表中的記錄
輸出類型 : 游標
*/
create or replace procedure proc_getemps(vrows out sys_refcursor)
is
begin
--1.打開游標, 給游標賦值
open vrows for select * from emp;
end;
2）調(diào)用存儲過程
/**
* 查詢指定員工的年薪，用存儲過程實現(xiàn)
* create or replace procedure proc_gettotalsal(vempno in number,vtotalsal out number)
is
begin
select sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;
end;
* @throws Exception
*/
public void test1() throws Exception {
//注冊驅(qū)動
Class.forName("oracle.jdbc.driver.OracleDriver");
//獲取連接
String url="jdbc:oracle:thin:@106.13.43.205:1521:orcl";
String user="canghe";
String password="canghe";
Connection conn = DriverManager.getConnection(url, user, password);
//獲取執(zhí)行對象
String sql="{call proc_gettotalsal(?,?)}";
CallableStatement state = conn.prepareCall(sql);
//為問號賦值
state.setInt(1, 7654);//設(shè)置員工編號
state.registerOutParameter(2, OracleTypes.NUMBER);
//執(zhí)行statement
state.execute();
//獲取結(jié)果
int totle = state.getInt(2);
//輸出結(jié)果
System.out.println(totle);
//釋放資源
state.close();
conn.close();
}
3）調(diào)用存儲函數(shù)
//調(diào)用存儲函數(shù)
/*
create or replace function func_getsal(vempno number) return number
is
--聲明變量.保存年薪
vtotalsal number;
begin
select sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;
return vtotalsal;
end;
*/
public void test2() throws Exception{
//注冊驅(qū)動
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.獲取連接
String url = "jdbc:oracle:thin:@106.13.43.205:1521:orcl";
String username = "canghe";
String password = "canghe";
Connection conn = DriverManager.getConnection(url, username,password);
//3.獲取執(zhí)行SQL的statement
String sql = " {?= call func_getsal(?)}";
CallableStatement state = conn.prepareCall(sql);
//4.封裝參數(shù)
//注冊返回類型參數(shù)
state.registerOutParameter(1, OracleTypes.NUMBER);
//設(shè)置第二個參數(shù)
state.setInt(2, 7788);
//5.執(zhí)行SQL
state.execute();
//6.獲取結(jié)果
int totalsal = state.getInt(1);
System.out.println("年薪 : ====" +totalsal);
//7.釋放資源
state.close();
conn.close();
}
3）通過游標輸出結(jié)果集
/**
* 通過游標輸出結(jié)果集
* @throws Exception
*/
public void test3() throws Exception{
//注冊驅(qū)動
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.獲取連接
String url = "jdbc:oracle:thin:@106.13.43.205:1521:orcl";
String username = "canghe";
String password = "canghe";
Connection conn = DriverManager.getConnection(url, username,password);
//3.獲取執(zhí)行SQL的statement
String sql = "{call proc_getemps(?)}";
CallableStatement call = conn.prepareCall(sql);
//接口 --- > 對象 -->實現(xiàn)類的名稱
System.out.println(call.getClass().getName());
OracleCallableStatement oracleCall = (OracleCallableStatement)call;
//4.注冊輸出類型的參數(shù)
call.registerOutParameter(1, OracleTypes.CURSOR);
//5.執(zhí)行SQL
call.execute();
//6.獲取執(zhí)行的結(jié)果
ResultSet resultSet = oracleCall.getCursor(1);
while(resultSet.next()){
int empno = resultSet.getInt("empno");
String name = resultSet.getString("ename");
System.out.println(empno +" ==== "+name);
}
//7.釋放資源
resultSet.close();
call.close();
conn.close();
}
二十、觸發(fā)器
觸發(fā)器: 當用戶執(zhí)行了 insert | update | delete 這些操作之后, 可以觸發(fā)一系列其它的動作/業(yè)務(wù)邏輯
作用 :
在動作執(zhí)行之前或者之后,觸發(fā)業(yè)務(wù)處理邏輯
插入數(shù)據(jù),做一些校驗
語法:
create [or replace] trigger 觸發(fā)器的名稱
before | after
insert | update | delete
on 表名
[for each row]
declare
begin
end;
觸發(fā)器的分類:
語句級觸發(fā)器: 不管影響多少行, 都只會執(zhí)行一次
行級觸發(fā)器: 影響多少行,就觸發(fā)多少次
:old 代表舊的記錄, 更新前的記錄
:new 代表的是新的記錄
--新員工入職之后,輸出一句話: 歡迎加入黑馬程序員
create or replace trigger tri_test1
after
insert
on emp
declare
begin
dbms_output.put_line('歡迎加入黑馬程序員');
end;
insert into emp(empno,ename) values(9527,'HUAAN');
--數(shù)據(jù)校驗, 星期六老板不在, 不能辦理新員工入職
--在插入數(shù)據(jù)之前
--判斷當前日期是否是周六
--如果是周六,就不能插入
create or replace trigger tri_test2
before
insert
on emp
declare
--聲明變量
vday varchar2(10);
begin
--查詢當前
select trim(to_char(sysdate,'day')) into vday from dual;
--判斷當前日期:
if vday = 'saturday' then
dbms_output.put_line('老板不在,不能辦理入職');
--拋出系統(tǒng)異常
raise_application_error(-20001,'老板不在,不能辦理入職');
end if;
end;
insert into emp(empno,ename) values(9528,'HUAAN2');
--更新所有的工資 輸出一句話
create or replace trigger tri_test3
after
update
on emp
for each row
declare
begin
dbms_output.put_line('更新了數(shù)據(jù)');
end;
update emp set sal = sal+10;
--判斷員工漲工資后的工資一定要大于漲工資前的工資
/*
200 --> 100
觸發(fā)器 : before
舊的工資
新的工資
如果舊的工資大于新的工資 , 拋出異常,不讓它執(zhí)行成功
觸發(fā)器中不能提交事務(wù),也不能回滾事務(wù)
*/
create or replace trigger tri_updatesal
before
update
on emp
for each row
declare
begin
if :old.sal > :new.sal then
raise_application_error(-20002,'舊的工資不能大于新的工資');
end if;
end;
update emp set sal = sal + 10;
select * from emp;
update emp set sal = sal - 100;
/*
模擬mysql中ID的自增屬性 auto_increment
insert into person(null,'張三');
觸發(fā)器:
pid=1 insert pid=1
序列 : create sequence seq_person_pid;
*/
create table person(
pid number primary key,
pname varchar2(20)
);
insert into person values(null,'張三');
create sequence seq_person_pid;
--觸發(fā)器
create or replace trigger tri_add_person_pid
before
insert
on person
for each row
declare
begin
dbms_output.put_line(:new.pname);
--給新記錄 pid 賦值
select seq_person_pid.nextval into :new.pid from dual;
end;
insert into person values(null,'張三');
select * from person;
————————————————
原文鏈接：https://blog.csdn.net/qq_43270074/article/details/85727108
浙公網(wǎng)安備 33010602011771號