Oracle
一、DQL语句
(1)基础语句
select [TOP|DISTINCT] [选择列表]|[*] --第五步
from 数据源 --第一步
[where 查询条件] --第二步
[group by 分组条件] --第三步
[having 过滤条件] --第四步
[order by 排序条件 asc|desc nulls first|last]; --第六步
(2)简单查询
select * from table_name
(3)别名查询
select t.name from table_name t
(4)去重查询
select distinct t.id from table_name t
(5)条件查询
条件运算符:>、>=、<、<=、=、<=>、!=、<>
逻辑运算符:and、or、not
模糊运算符:
like:%任意多个字符、_任意单个字符、如果有特殊字符,需要使用escape转义
between and
not between and
in
is null
is not null
(6)多表查询
- 内连接
- 隐式内连接:
select * from emp e1, dept d1 where e1.deptno = d1.deptno;
- 显示内连接:
select * from emp e1 inner join dept d1 on e1.deptno = d1.deptno;
- 隐式内连接:
- 外连接
- 隐式左外连接:
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;
- 隐式左外连接:
- 交叉连接
- 隐式交叉连接:
select * from emp, dept;
- 显示交叉连接:
select * from emp e1 cross join dept d1;
- 隐式交叉连接:
(7)联合查询
- 并集查询:将两个查询结果进行合并
/*
union : 它会去除重复的,并且排序
union all : 不会去除重复的,不会排序
*/
--工资大于1500或者20号部门下的员工
select * from emp where sal > 1500
union
select * from emp where deptno = 20;
--工资大于1500或者20号部门下的员工
select * from emp where sal > 1500
union all
select * from emp where deptno = 20;
- 交集运算:找两个查询结果的交集
--工资大于1500并且20号部门下的员工
select * from emp where sal > 1500
intersect
select * from emp where deptno = 20;
- 差集运算:找两个查询结果的差集
--1981年入职员工(不包括总裁和经理)
select * from emp where to_char(hiredate,'yyyy') = '1981'
minus
select * from emp where job = 'PRESIDENT' or job = 'MANAGER';
(8)子查询
-
单行子查询:>、>=、<、<=、!=、<>、=、<=>
-
多行子查询:in、not in、any、some、all、exits
- in
--查询所有经理的信息 select * from emp where empno in (select mgr from emp where mgr is not null);
- not in
--查询不是经理的信息 select * from emp where empno not in (select mgr from emp where mgr is not null);
- any
--查询出比10号部门任意一个员工薪资高的员工信息 select * from emp where sal > any (select sal from emp where deptno = 10);
- some
--查询出比10号部门任意一个员工薪资高的员工信息 select * from emp where sal > some (select sal from emp where deptno = 10);
- all
--查询出比20号部门所有员工薪资高的员工信息 select * from emp where sal > all (select sal from emp where deptno = 20);
- exits
--查询有员工的部门的信息 select * from dept d1 where exists (select * from emp e1 where e1.deptno = d1.deptno);
二、DCL语句
(1)创建表空间
create tablespace mytest ---mytest 表空间名称
datafile 'd:/mytest.dbf' ---'d:/mytest.dbf' 文件路径
size 100m ---100m 初始化大小
autoextend on
next 10m; ---10m 自动扩展大小
(2)删除表空间
drop tablespace mytest ---mytest 表空间名称
(3)创建用户
create user zhangsan ---zhangsan 用户名
identified by 123456 ---123456 密码
default tablespace mytest; ---mytest 表空间名称
(4)删除用户
/* CASCADE 的意思是将用户的数据库数据一并删除,并没有删除相应的表空间!*/
drop user zhangsan CASCADE; ---zhangsan 用户名
(5)用户授权
- 语法
grant 系统权限列表 to 用户名;
或者
grant 实体权限列表 on 表名称 to 用户名;
- 权限列表
- 系统权限分类:(系统权限只能由DBA用户授出)
- DBA:拥有全部特权,是系统最高权限,只有DBA才可以创建数据库结构。
- RESOURCE:拥有Resource权限的用户只可以创建实体,不可以创建数据库结构。
- CONNECT:拥有Connect权限的用户只可以登录Oracle,不可以创建实体,不可以创建数据库结构。
- 实体权限分类:select、update、insert、alter、index、delete、all
- 系统权限分类:(系统权限只能由DBA用户授出)
(6)取消授权
revoke 系统权限列表 from 用户名;
或者
revoke 实体权限列表 on 表名称 from 用户名
(7)修改密码
alter user zhangsan identified by "123456";
三、DDL
(1)常见数据类型
-
字符串类型
- CHAR:定长字符串,它会用空格填充来达到其最大长度,最多可以存储2000字节的信息
- NCHAR:这是一个包含UNICODE格式数据的定长字符串,最多可以存储2000字节的信息
- VARCHAR2:变长字符串,它不会用空格填充来达到其最大长度,最多可以存储4000字节的信息
- NVARCHAR2:这是一个包含UNICODE格式数据的变长字符串,最多可以存储4000字节的信息
-
数值类型
- NUMBER:NUMBER(p,s)是最常见的数字类型,关于NUMBER的有效位§和精确位(s)遵循以下规则:
- p:是有效数据总位数,取值范围为【1-38】,默认值是38
- s:表示精确到多少位,取值范围为【-84-127】,默认值是0
- INTEGER:INTEGER是NUMBER的子类型,它等同于NUMBER(38,0),用来存储整数
- FLOAT:Float(n)是NUMBER的子类型,数 n 指示位的精度,n值的范围可以从1到126
- NUMBER:NUMBER(p,s)是最常见的数字类型,关于NUMBER的有效位§和精确位(s)遵循以下规则:
-
日期类型
- DATE:DATE是最常用的日期数据类型,它可以存储日期和时间信息,虽然可以用字符或数字类型表示日期和时间信息,但是日期数据类型具有特殊关联的属性。Oracle 为每个日期值存储以下信息: 世纪、 年、 月、 日期、 小时、 分钟和秒,一般占用7个字节的存储空间
- TIMESTAMP:这是一个7字节或12字节的定宽日期/时间数据类型。它与DATE数据类型不同,因为TIMESTAMP可以包含小数秒,带小数秒的TIMESTAMP在小数点右边最多可以保留9位
(2)创建表:与Mysq类似
create table users(
id number,
username varchar2(20),
password varchar2(20)
);
(3)复制表
create table emp_copy as
select * from emp;
(4)删除表
方式一:drop table 表名;
方式二:truncate table 表名;
(5)修改表
添加一列
格式:alter table 表名 add 列名 列的类型;
演示:alter table users add phone varchar2(11);
修改列名
格式:alter table 表名 rename column 旧列名 to 新列名;
演示:alter table users rename column phone to mobile;
修改类型
格式:alter table 表名 modify 列名 列的类型;
演示:alter table users modify mobile char(11);
删除一列
格式:alter table 表名 drop column 列名;
演示:alter table users drop column mobile;
修改表名
格式:rename 旧表名 to 新表名;
演示:rename users to myusers;
(6)表约束
CREATE TABLE 表名(
列名 列的类型 primary key,--主键约束
列名 列的类型 not null,--非空约束
列名 列的类型 unique,--唯一约束
列名 列的类型 check(列名 in (检查列表)),--检查约束
constraint 约束名 foreign key(字段名) references 主表(被引用列)--外键约束
) ;
(7)修改
- 主键约束
添加
alter table product add constraint PK_PRODUCT_PID primary key(pid);
删除
alter table product drop constraint PK_PRODUCT_PID;
或者
alter table product drop primary key;
- 非空约束
添加
alter table product modify pname not null;
删除
alter table product modify pname null;
- 唯一约束
添加
alter table product add constraint UK_PRODUCT_PIMG unique(pimg);
删除
alter table product drop constraint UK_PRODUCT_PIMG;
或者
alter table product drop unique(pimg);
- 检查约束
添加
alter table product add constraint CK_PRODUCT_PFLAG check(pflag in ('上架','下架'));
删除
alter table product drop constraint CK_PRODUCT_PFLAG;
- 外键约束
添加
alter table product add constraint FK_PRODUCT_ID foreign key(cid) references category(cid);
删除
alter table product drop constraint FK_PRODUCT_ID;
四、DML
(1)插入语句
insert into 表名(列名1,列名2,...) values(值1,值2,...);
commit;
(2)修改语句
update 表名 set 列名1=值1,列名2=值2,... where 查询条件;
commit;
(3)删除语句
delete from 表名 where 查询条件;
commit;
五、常用函数
(1)字符函数
函数 | 说明 |
---|---|
ASCII(X) | 返回字符X的ASCII码 |
CONCAT(X,Y) | 连接字符串X和Y |
INSTR(X,STR[,START][,N) | 从X中查找str,可以指定从start开始,也可以指定从n开始 |
LENGTH(X) | 返回X的长度 |
LOWER(X) | X转换成小写 |
UPPER(X) | X转换成大写 |
LTRIM(X[,TRIM_STR]) | 把X的左边截去trim_str字符串,缺省截去空格 |
RTRIM(X[,TRIM_STR]) | 把X的右边截去trim_str字符串,缺省截去空格 |
TRIM([TRIM_STR FROM]X) | 把X的两边截去trim_str字符串,缺省截去空格 |
REPLACE(X,old,new) | 在X中查找old,并替换成new |
SUBSTR(X,start[,length]) | 返回X的字串,从start处开始,截取length个字符,缺省length,默认到结尾 |
(2)数字函数
函数 | 说明 |
---|---|
ABS(X) | X的绝对值 |
ACOS(X) | X的反余弦 |
COS(X) | 余弦 |
CEIL(X) | 大于或等于X的最小值 |
FLOOR(X) | 小于或等于X的最大值 |
LOG(X,Y) | 小于或等于X的最大值 |
MOD(X,Y) | X除以Y的余数 |
POWER(X,Y) | X的Y次幂 |
ROUND(X,Y) | X在第Y位四舍五入 |
SQRT(X) | X的平方根 |
TRUNC(X,Y) | X在第Y位截断 |
(3)日期函数
函数 | 说明 |
---|---|
ADD_MONTHS(d,n) | 在某一个日期 d 上,加上指定的月数 n,返回计算后的新日期。d 表示日期,n 表示要加的月数。 |
LAST_DAY(d) | 返回指定日期当月的最后一天。 |
ROUND(d[,fmt]) | 返回一个以 fmt 为格式的四舍五入日期值, d 是日期, fmt 是格式模型。默认 fmt 为 DDD,即月中的某一天。 |
EXTRACT(fmt FROM d) | 提取日期中的特定部分。fmt 为:YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配,也可以与 TIMESTAMP 类型匹配;但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。 |
(4)转换函数
函数 | 说明 |
---|---|
TO_CHAR(SYSDATE,‘YYYY"年"MM"月"DD"日" HH24:MI:SS’) | 把日期和数字转换为制定格式的字符串。 |
TO_DATE(X,[,fmt]) | 把一个字符串以fmt格式转换成一个日期类型 |
TO_NUMBER(X,[,fmt]) | 把一个字符串以fmt格式转换为一个数字 |
六、进阶
(1)序列
- 含义
序列是Oracle数据库中特有的,使用序列可以生成类似于 auto_increment 这种ID自动增长 1,2,3,4,5… 的效果
- 语法
create sequence 序列名称
start with 从几开始
increment by 每次增长多少
[maxvalue 最大值] | nomaxvalue
[minvalue 最小值] | nominvalue
cycle | nocycle --是否自动循环
[cache 缓存数量] | nocache;
- 演示
--创建序列
create sequence auto_increment_seq
start with 1
increment by 1
nomaxvalue
minvalue 1
nocycle
cache 10000;
--调用序列
select auto_increment_seq.nextval from dual;
select auto_increment_seq.currval from dual;
(2)PLSQL编程
- 格式
declare
--声明变量
begin
--业务逻辑
end;
- 变量
declare
--声明变量
-- 格式一:变量名 变量类型;
-- 格式二:变量名 变量类型 := 初始值;
-- 格式三:变量名 变量类型 := &文本框名;
-- 格式四:变量名 表名.字段名%type;
-- 格式五:变量名 表名%rowtype;
vnum number;
vage number := 28;
vabc number := &abc;--输入一个数值,从一个文本框输入
vsal emp.sal%type; --引用型的变量,代表emp.sal的类型
vrow emp%rowtype; --记录型的变量,代表emp一行的类型
begin
--业务逻辑
dbms_output.put_line(vnum); --输出一个未赋值的变量
dbms_output.put_line(vage); --输出一个已赋值的变量
dbms_output.put_line(vabc); --输出一个文本框输入的变量
select sal into vsal from emp where empno = 7654; --将查询到的sal内容存入vsal并输出
dbms_output.put_line(vsal);
select * into vrow from emp where empno = 7654; --将查询到的一行内容存入vrow并输出
dbms_output.put_line(vrow.sal);
dbms_output.put_line(123); --输出一个整数
dbms_output.put_line(123.456); --输出一个小数
dbms_output.put_line('Hello,World'); --输出一个字符串
dbms_output.put_line('Hello'||',World'); --输出一个拼接的字符串,||拼接符Oracle特有
dbms_output.put_line(concat('Hello',',World')); --输出一个拼接的字符串,concat函数比较通用
end;
- if判断
if 条件1 then
else 条件2 then
else
end if;
案例:
declare
age number := &age;
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;
- while循环
while 条件 loop
end loop;
案例:
--输出1~10
declare
i number := 1;
begin
while i <= 10 loop
dbms_output.put_line(i);
i := i + 1;
end loop;
end;
- for循环
for 变量 in [reverse] 起始值 .. 结束值 loop
end loop;
案例:
--输出1~10
declare
begin
for i in reverse 1 .. 10 loop
dbms_output.put_line(i);
end loop;
end;
- loop循环
loop
exit when 条件
end loop;
案例:
--输出1~10
declare
i number :=1
begin
loop
exit when i > 10;
dbms_output.put_line(i);
i := i + 1;
end loop;
end;
- 意外
- 意外是程序运行的过程发生的异常,相当于是Java中的异常
declare
--声明变量
begin
--业务逻辑
exception
--处理异常
when 异常1 then
...
when 异常2 then
...
when others then
...处理其他异常
end;
案例1,内置系统异常:
declare
vi number;
vrow emp%rotype
begin
--以下四行对应四个异常
vi := 8/0
vi := 'aaa'
select * into vrow from emp where empno = 1234567;
select * into vrow from emp
exception
when zero_divide then
dbms_output.put_line('发生除数为零异常');
when value_error then
dbms_output.put_line('发生类型异常转换');
when no_data_found then
dbms_output.put_line('没有找到数据异常');
when too_many_rows then
dbms_output.put_line('查询出多行记录,但是赋值给了%rowtype一行数据变量');
when other then
dbms_output.put_line('发生了其他异常' || sqlrrm)
end;
案例2,抛出系统异常:
--查询指定编号的员工,如果没有找到,则抛出系统异常
declare
--1.声明一个变量 %rowtype
vrow emp%rowtype;
begin
--查询员工信息,保存起来
select * into vrow from emp where empno = 8000;
--判断是否触发异常的条件
if vrow.sal is null then
--抛出系统异常
raise_application_error(-20001,'员工工资为空');
end if;
exception
when others then
dbms_output.put_line('输出了其它的异常' || sqlerrm);
end;
案例3,抛出自定义异常:
--查询指定编号的员工,如果没有找到,则抛出自定义异常
declare
--1.声明一个变量 %rowtype
vrow emp%rowtype;
--2.声明一个自定义的异常
no_emp exception;
begin
--查询员工信息,保存起来
select * into vrow from emp where empno = 8000;
--判断是否触发异常的条件
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;
(3)索引
- 含义
索引相当于是一本书的目录,能够提高我们的查询效率
- 语法
--创建索引
create [UNIQUE] | [BITMAP] index 索引名 on 表名(列名1,列名2,...);
--修改索引
----重命名索引
alter index 索引名称 rename to 新的名称;
----合并索引
alter index 索引名称 coalesce;
----重建索引
alter index 索引名称 rebuild;
----修改某列
先删除,再创建
--删除索引
drop index 索引名称
案例:
--重命名索引
alter index INX_CATEGORY_CNAME rename to INX_CATEGORY_CNAME_NEW;
--合并索引
alter index INX_CATEGORY_CNAME_NEW coalesce;
--重建索引
alter index INX_CATEGORY_CNAME_NEW rebuild;
--修改某列
先删除,在创建
--删除索引
drop index INX_CATEGORY_CNAME;
(4)视图
- 含义
视图是对查询结果的一个封装,视图里面所有的数据,都是来自于它查询的那张表,视图本身不存储任何数据,但是可以修改原数据,但是不建议这样使用
- 语法
--创建视图
create view 视图名称
as 查询语句
[with read only];
--修改视图
create or replace view 视图名称
as 查询语句
[with read only];
--删除视图
drop view 视图名称;
案例:
--创建视图
create view view_emp as
select ename,job,mgr from emp;
--修改视图
create or replace view view_emp as
select ename,job,mgr,deptno from emp;
--删除视图
drop view view_emp;
(5)同义词
- 含义
同义词就是别名的意思和视图的功能类似,就是一种映射关系
- 语法
--创建同义词
create [public] synonym 同义词名称 for 对象的名称;
--修改同义词
create or replace [public] synonym 同义词名称 for 对象的名称;
--删除同义词
drop [public] synonym 同义词名称;
案例:
--创建同义词
--创建
create synonym syno_emp for emp;
--调用
select * from syno_emp;
--修改同义词
--创建
create or replace synonym syno_emp_update for emp;
--调用
select * from syno_emp_update;
--删除同义词
drop synonym syno_emp_update;
(6)游标
- 含义
游标是用来操作查询结果集,相当于是JDBC中ResultSet,它可以对查询的结果一行一行的获取
- 语法
--第一步:定义游标
--第一种:普通游标
cursor 游标名[(参数 参数类型)] is 查询语句;
--第二种:系统引用游标
游标名 sys_refcursor;
--第二步:打开游标
--第一种:普通游标
open 游标名[(参数 参数类型)];
--第二种:系统引用游标
open 游标名 for 查询语句;
--第三步:获取一行
fetch 游标名 into 变量;
--第四步:关闭游标
close 游标名;
案例:
--普通游标使用
--输出指定部门下的员工姓名和工资
declare
--1.声明游标
cursor vrows(dno number) is select * from emp where deptno = dno;
--声明变量
vrow emp%rowtype;
begin
--2.打开游标
open vrows(10);
--3.循环遍历
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line('姓名:' || vrow.ename || ' 工资: ' || vrow.sal);
end loop;
--4.关闭游标
close vrows;
end;
--系统引用游标使用
--输出员工表中所有的员工姓名和工资
declare
--1.声明系统引用游标
vrows sys_refcursor;
--声明变量
vrow emp%rowtype;
begin
--2.打开游标
open vrows for select * from emp;
--3.循环遍历
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line('姓名:' || vrow.ename || ' 工资: ' || vrow.sal);
end loop;
--4.关闭游标
close vrows;
end;
--使用for循环输出
--输出员工表中所有的员工姓名和工资
declare
cursor vrows is select * from emp;
begin
--自动定义变量vrow,自动打开游标,自动关闭游标
for vrow in vrows loop
dbms_output.put_line('姓名:' || vrow.ename || ' 工资: ' || vrow.sal || '工作:' || vrow.job);
end loop;
end;
(7)存储过程
- 含义
存储过程实际上是封装在服务器上一段PLSQL代码片断,它已经编译好了,如果客户端调用存储过程,执行效率就会非常高效
- 语法
--创建存储过程
create procedure 存储过程名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...)
is|as
--声明部分
begin
--业务逻辑
end;
--修改存储过程
create [or replace] procedure 存储过程名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...)
is|as
--声明部分
begin
--业务逻辑
end;
--删除存储过程
drop procedure 存储过程名称;
--调用存储过程
--方式一:
call 存储过程名称(...);
--方式二:
declare
begin
存储过程名称(...);
end;
案例:
--创建存储过程
--给指定员工涨薪并打印涨薪前和涨薪后的工资
create procedure proc_update_sal(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;
--给员工编号为7521的员工涨工资10元
call proc_update_sal(7521, 10);
--修改存储过程
--给指定员工涨薪并打印涨薪前和涨薪后的工资
create or replace procedure proc_update_sal(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;
--给员工编号为7521的员工涨工资10元
call proc_update_sal(7521, 10);
--删除存储过程
drop procedure proc_update_sal;
(8)函数
- 含义
函数实际上是封装在服务器上一段PLSQL代码片断,它已经编译好了,如果客户端调用存储过程,执行效率就会非常高效,它跟存储过程没有什么本质区别,存储过程能做的函数也能做,只不过函数有返回值
- 语法
--创建函数
create function 函数名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...) return 返回的参数类型
is|as
--声明部分
begin
--业务逻辑
end;
--修改函数
create [or replace] function 函数名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...) return 返回的参数类型
is|as
--声明部分
begin
--业务逻辑
end;
--删除函数
drop function 函数名称;
--调用函数
--方式一:
select 函数名称(...) from dual;
--方式二:
declare
变量名 变量类型;
begin
变量名 = 函数名称(...);
end;
案例:
--创建函数
--查询指定员工的年薪
/*
参数 : 员工的编号
返回 : 员工的年薪
*/
create 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;
--查询员工编号为7788的年薪
declare
vsal number;
begin
vsal := func_getsal(7788);
dbms_output.put_line(vsal);
end;
--修改函数
--查询指定员工的年薪
/*
参数 : 员工的编号
返回 : 员工的年薪
*/
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;
--查询员工编号为7788的年薪
declare
vsal number;
begin
vsal := func_getsal(7788);
dbms_output.put_line(vsal);
end;
--删除函数
drop function func_getsal;
(9)触发器
- 含义
当用户执行了 insert | update | delete 这些操作之后,可以触发一系列其它的动作、业务逻辑,使用触发器可以协助应用在数据库端确保数据的完整性、日志记录 、数据校验等操作。使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在Oracle触发器不仅支持行级触发,还支持语句级触发
- 分类
触发器类型 | NEW和OLD的使用 |
---|---|
INSERT 型触发器 | NEW 表示将要或者已经新增的数据 |
UPDATE 型触发器 | OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据 |
DELETE 型触发器 | OLD 表示将要或者已经删除的数据 |
- 语法
--创建触发器
create trigger 触发器名称
before|after
insert|update|delete
on 表名称
[for each row]--行级触发器
declare
--声明部分
begin
--业务逻辑
end;
--修改触发器
create [or replace] trigger 触发器名称
before|after
insert|update|delete
on 表名称
[for each row]--行级触发器
declare
--声明部分
begin
--业务逻辑
end;
--删除触发器
drop trigger 触发器名称;
案例:
--INSERT型触发器
--新员工入职之后,输出一句话: 欢迎加入我们
create or replace trigger tri_emp_insert
after
insert
on emp
declare
begin
dbms_output.put_line('欢迎加入我们');
end;
--插入数据就可以自动触发触发器
insert into emp(empno, ename) values(9527, '马哈哈');
--UPDATE型触发器
--判断员工涨工资后的工资一定要大于涨工资前的工资
create or replace trigger tri_emp_update_sal
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;
select * from emp;
--DELETE型触发器
--老员工离职之后,输出一句话: 有员工离职了
create or replace trigger tri_emp_delete
after
delete
on emp
declare
begin
dbms_output.put_line('有员工离职了');
end;
--删除数据就可以自动触发触发器
delete from emp where empno = 9527;
--删除触发器
drop trigger tri_emp_insert;
drop trigger tri_emp_update_sal;
drop trigger tri_emp_delete;
(10)数据备份与恢复
--注意:以下操作为cmd命令行操作
--全部导出
exp 管理员帐号/密码 file='d:\beifen.dmp' full=y
--全部导入
imp 管理员帐号/密码 file='d:\beifen.dmp' full=y
--按用户导出
exp 管理员帐号/密码 file='d:\beifen.dmp' owner=帐号
--按用户导入
imp 管理员帐号/密码 file='d:\beifen.dmp' fromuser=帐号
(11)补充
- Merge into用法
格式:
merge into 目标表 a
using 源表 b
on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)
when matched then update set a.字段=b.字段 --目标表别称a和源表别称b都不要省略
when not matched then insert (a.字段1,a.字段2……)values(b.字段1,b.字段2……) --目标表别称a可省略,源表别称b不可省略