Oracle第四天
2017-04-07 本文已影响32人
随手点灯
role.png
] args) {
try (Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
OracleCallableStatement prepareCall = (OracleCallableStatement) conn
.prepareCall("{call pack_emp2.proc_get_emp(?,?)}");) {
prepareCall.setInt(1, 10);
// 如果有输出参数需要进行住注册
prepareCall.registerOutParameter(2, OracleTypes.CURSOR);
// 然后执行
prepareCall.execute();
// 然后获取到输出参数
ResultSet rs = prepareCall.getCursor(2);
if (rs != null) {
while (rs.next()) {
String ename = rs.getString("ename");
double sal = rs.getDouble("sal");
String job = rs.getString("job");
System.out.println(ename + "的职位是:" + job + " 工资是:" + sal);
}
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
1.3 删除包
- 删除包体:drop package body 包名;
- 删除包:drop package 包名;
2 触发器
2.1 概念
- 触发器就是一个可以自动执行的PLSQL块.当数据库或者是数据库对象的状态发生改变的时候,Oracle数据库就会自动点燃某一个触发器,然后执行该触发器中的PLSQL块.
2.2 常见的触发器
- 系统触发器
- 打开或者关闭数据库,可以触发的事件
- DDL触发器
- 表的定义语言触发
- DML触发器
- 对数据库表的增删改操作创造触发器
- 系统和DDL触发器创建的,我们接触的是DML触发器.
2.3 创建DML触发器
-
一个触发器由三部分组成
- 触发时间
- 触发事件
- 触发动作(PLSQL块)
-
语法格式:
create or replace tigger 触发器 before|after -- 触发的时间:是在DML操作之前还是之后触发 insert|update|delete [of column_name] on table_name -- 触发的事件:可以针对某一个表的某一列的某操作触发 [for each row] begin -- 触发的动作 end;- 如果指定了for each row参数,该触发器就是一个行级触发器,如果没有指定该参数的话,该触发器就是一个列级的触发器;如果是行级触发器,每影响一行数据就会点燃一次触发器;如果是列级触发器,无论影响多少数据都只会点燃一次触发器.
-
示例:
-- 创建触发器 create or replace trigger tri_add_emp before -- 触发的时间 insert on emp for each row begin -- 触发动作 dbms_output.put_line('触发器被点燃了....'); end; insert into emp (empno,ename) values (2003,'狗狗狗');
2.4 应用的场景
-
场景一:执行DML操作之前对数据进行校验:
-
需求:老板给员工涨工资,但是要求涨后的工资必须要大于涨之前的工资.
-
:old 引用DML操作前的那一行记录
-
:new 引用DML操作后的那一行记录
create or replace trigger tri_add_sal
before
update of sal on emp
for each row
begin
-- 如果涨后工资小于涨之前的工资
if :new.sal <= :old.sal then
-- 阻止DML语句的执行(抛出异常)
raise_application_error(-20001,'哎,工资越来越少了');
else
dbms_output.put_line('谢谢老板!');
end if;
end;
-
-
场景二:实现主键列的自增长
-- 实现主键列的自增长 create or replace trigger tri_generate_gk before insert on emp for each row begin --给新插入的数据的empno列设置一个序列值 select seq_emp_empno.nextval into :new.empno from dual; end; insert into emp (ename) values ('狗哥' || seq_emp_empno.nextval); -
场景三:对删除的数据进行备份
-- 创建emp的备份表 create table emp_bak as select * from emp where 1=2; -- 需求:当我们删除emp表的数据,删除的数据会自动保存在emp_bak表中 create or replace trigger tri_backup_emp before delete on emp for each row begin insert into emp_bak (empno,ename) values(:old.empno,:old.ename); end; delete from emp where sal is null;
2.5 禁用或者是启用触发器
-
禁用或者是启用触发器
alter trigger 触发器名字 disable|enable-- 禁用触发器 alter trigger tri_delete_emp disable; -- 启用触发器 alter trigger tri_delete_emp enable; -
禁用或者是启用表中所有的触发器
alter table 表名 disable|enable all triggers
-- 禁用emp中所有的触发器
alter table emp disable all triggers;
-- 启用emp中所有的触发器
alter table emp disable all triggers;
- 删除触发器
drop trigger 触发器名字
2.6 总结
- 表
- 视图: 1)限制某些列的访问 2)简化select语句
- 同义词: 1)简化对象访问 2)提高数据安全性
- 索引: 1)提高表查询效率;会降低增删改的效率
- 序列:实现主键的自增长,只能够保证唯一,但是不能够保证序列值是连续的.
- 存储过程:命名了的PLSQL块
- 存储函数:有返回值的存储过程
- 程序包:1)对数据库对象更好的管理 2)更有利于对PLSQL模块化的开发
- 触发器:会自动执行的PLSQL块
3. 数据字典
3.1 概念
- 数据字典是由一些系统表和视图组成,安装Oracle数据库的时候,Oracle数据库就已经自动创建了一些系统表.这些系统表就是用来记录系统里面的一些信息.如果需要查询系统的信息,就需要查询这些系统表.
- 但是这些系统表是不允许直接访问的.因此,Oracle就为这些系统表创建了对应的视图,如果需要访问系统表的数据,就可以通过这些系统表的视图进行访问.
- 使用
select * from dict可以获取到所有的视图,这些视图大多是以USER,ALL,DBA开头
sjzd.png
- 以`USER`开头的视图,那么这些视图是用来保存当前用户的信息,只允许当前用户访问
- 以`ALL`开头的视图,那么这些视图是用来保存所有用户的信息,允许所有的用户访问
- 以`DBA`开头的视图,那么这些视图是用来保存系统相关的信息,只允许管理员访问.
3.2 常用的数据字典
| 视图 | 作用 |
|---|---|
| dba_data_files | 查询所有表空间以及数据文件 |
| dba_users | 查询所有用户的详细信息 |
| all_users | 查询所有用户的信息 |
| dba_sys_privs | 查询用户的系统权限 |
| dba_tab_privs | 查询用户的对象权限 |
4. 角色
4.1 概念
- 角色就是一些权限的集合,如果用户具有了某一个角色,那么这个用户就具有了该角色的所有的权限.
4.2 创建角色
- 第一步创建角色
create role 角色名 - 第二步给角色授予权限:
grant 权限 to 角色 - 第三步把角色授予给用户:
grant 角色 to 用户
grant.png
4.2 Oracle数据库提供的角色
- CONNECT:具有create session权限.
- RESOURCE:具有了create xxx的权限
- 查询一个角色的所有的权限
role.png
- 注意:如果某个用户有某个对象的createXXX的权限,那么就有权对该对象进行任何的操作
- 查询一个用户的角色
dba_role_privs
- 回收角色
revoke 角色名 from 用户名
revoke.png
5. 闪回
5.1 概念
- 闪回技术可以把之前的数据恢复回来.
5.2 闪回技术分类
- 闪回查询
- 闪回表
- 闪回删除
5.3 闪回查询
-
闪回查询就是允许用户查询某个表在过去某一个时间点或者SCN状态.
- SCN:系统改变号.它是Oracle数据库里面的一个计数器,当用户去操作数据库的时候,Oracle数据库就会给该计数器自动+1,计数器就是代表了某一个时间点.
-
执行闪回查询的方式一:(推荐)
-
语法
select * from 表名 as of timestamp 时间点--闪回查询 select * from emp_bak as of timestamp sysdate - interval '15' minute ;
-
-
执行闪回查询方式二:
-
语法
select * from 表名 as of scn 系统改变号-- 获取当前时间的scn select timestamp_to_scn(systimestamp) from dual; -- 根据scn获取到指定的时间 select scn_to_timestamp(1568877) from dual;
-
-
闪回查询的应用:
insert into emp_flash ( --闪回查询 select * from emp_flash as of timestamp sysdate - interval '25' minute where deptno = 10 );
5.4 闪回表
-
用户可以把某一张表的数据恢复到某一个时间点或者是某个scn的状态.
-
闪回表和闪回查询的区别:
- 闪回查询可以把某个表的某些数据恢复到某个时间点
- 闪回表只能把整张表的所有数据恢复到某个时间点
-
执行闪回表(方式一)
-
语法:
flashback table 表名 to timestamp 时间点 -
注意:如果需要执行闪回表操作,必须要启动行移动功能,如果没有启动,就会出错.
-
启动方式:alter table 表名 enable row movement;
-
示例:
-- 启动该表的行移动功能 alter table emp_bak enable row movement; -- 闪回表 flashback table emp_bak to timestamp timestamp '2017-4-6 15:00:00' ; -
注意:但是并不是所有的数据都可以通过闪回操作表操作恢复回来,为什么呢?因为用户删除数据的时候,这些被删除的数据是保存在撤销表空间里面,但是撤销表空间里面是有一个保留时间的.默认的保留时间是900秒(15分钟左右).如果超过了900秒.
-
如何修改?
-
flash.png
5.5 闪回删除
-
闪回删除允许用户把删除的表从回收站中恢复过来.
-
语法:
flashback table to before drop [rename to new_table_name] -
如果指定了rename参数,那么执行闪回删除的时候,Oracle数据库就会对该表进行重命名.
-- 删除表 drop table emp_bak; -- 执行闪回删除 flashback table emp_bak to before drop -
注意.闪回删除只能对回收站里面的表进行闪回,如果删除表的时候指定了purge参数.那么就不能够使用闪回删除把表恢复过来.
-
清空回收站
-- 清空回收站 purge recyclebin;
6. 数据库的备份与还原
-
数据库的备份
- 由于是oracle的命令,需要在cmd中输入
exp 用户名/密码@数据库名 file='备份路径' [tables=(表...)]- 注意:备份文件要以.dmp结尾
-
数据库恢复
- 在cmd命令输入命令
imp 用户名/密码@数据库名 file='备份文件的路径' tabels=(表...)
7.分析函数
7.1 概念
- 分析函数:和分组函数的功能非常的相似,它同样可以对表的某一列进行分组.它还可以对分组后的数据进行排序和编号.
7.2 常用的分析函数
- rank():如果排序字段值相等,那么该行记录的编号就会初夏你重复,并且编号是不重复的.
- dense_rank():如果排序字段的值相等,那么该行记录编号就会出现重复,但是编号是连续的
- row_number(): 即使排序字段值是相等的,该行记录编号也不会出现重复,编号也是连续的
8.总结
8.1 第一天
- 数据库安装
- 数据库服务
- 连接数据库
- sqlplus命令
- ddl
- dml
- 运算符
8.2 第二天
- 函数
- 表空间
- 用户管理
- 视图
- 同义词
- 索引
- 序列
8.3 第三天
- PLSQL块
- 存储函数
- 存储过程
8.4 第四天
- 程序包
- 触发器
- 数据字典
- 闪回
- 角色
- 备份和还原
