Oracle就业课第六课之游标和触发器
oracle 游标和触发器
回顾
表空间
表空间:逻辑名词:表、视图、索引;拆分:段、区 、块(最小的存储单元)
物理文件的构成:
1、tcl文件
2、log文件
3、dbf文件(数据文件)
一个或者多个表空间----------->一个物理文件(dbf文件)
1、创建表空间和用户
1、创建表空间(切换成管理员)
2、创建用户 关联表空间
3、授权
示例:
--表空间: 数据表空间
create tablespace tbs_331
datafile 'd:/oracledata/tbs_331.dbf' --数据文件
size 50m; --初始大小
--临时表空间
create temporary tablespace tbs_331_tmp
tempfile 'd:/oracledata/tbs_331_tmp.dbf'
size 20m;
--创建用户
create user u331 identified by 123456
default tablespace tbs_331 --默认表空间
temporary tablespace tbs_331_tmp; --默认临时表空间
--授权
--内置的角色:
--connect: 连接,能够登陆系统,具备最基本的权限;游客
--resource: 资源;查看数据库对象,增删改查数据;正式的用户使用
--dba:管理员
grant connect,resource to u331;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
2、导出(备份)和导入(还原)
2.1 需要准备虚拟目录:
--创建一个虚拟目录:映射到指定的物理路径上
create directory dir331 as 'd:/oracledata';
--授予u331用户操作dir331的权限:read,write
grant read,write on directory dir331 to u331;
- 1
- 2
- 3
- 4
- 5
2.2 导出:expdp
(数据泵程序)
注意:在cmd窗口执行
expdp u331/123456 schemas=u331 dumpfile=u331.dmp logfile=u331.log directory=dir331
- 1
- u331/123456:用户名和密码
- schemas=u331:模式,默认情况下模式=用户;
- dumpfile=u331.dmp:导出的文件名
- logfile=u331.log:日志文件
- directory=dir331:虚拟目录:dir331;默认导出的数据文件都在虚拟目录对应的物理路径中;
导入:impdp
impdp u331/123456 schemas=u331 dumpfile=u331.dmp logfile=u331.log directory=dir331
- 1
实验:
1、使用新创建的用户登录创建好的表空间,创建一个数据表并录入一些数据供测试
2、导出
3、删除表
4、再次导入,查看数据是否还原
3、删除用户和表空间:
-- 删除用户
drop user u331 cascade; --级联删除
--删除表空间 (包括内容和数据文件)
drop tablespace tbs_331 including contents and datafiles;
drop tablespace tbs_331_tmp including contents and datafiles;
- 1
- 2
- 3
- 4
- 5
- 6
如果忘掉管理员密码:
sqlplus scott/tiger
SQL*Plus: Release 11.1.0.6.0 - Production on 星期一 9月 21 15:30:44 2020
Copyright (c) 1982, 2007, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn / as sysdba;
已连接。
SQL> alter user sys identified by sys;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
学习目标
1、游标
2、触发器
学习内容
1、游标
游标:cursor ;指针;
以往对数据表中数据进行的各种数据操作,本质上都是对列进行操作的。无法对每一行数据进行处理。
游标:将查询到的结果暂时的存放到上下文的空间中(内存区),可以对数据进行逐行定位操作。
游标的分类:
1、显式游标
有名字,定义游标,打开游标…
2、隐式游标
DML命令(insert,update,delete) 和select … into 命令
游标的属性
用法:显式游标名%属性名 或者:SQL%属性名
%NOTFOUND:如果没有读取到数据,返回true。否则返回false
%ROWCOUNT:实际读取到的行数
%ISOPEN:游标是否打开,true/false
%FOUND:游标是否读取到数据 ;读取到true;否则:false
显式游标
1、声明游标
2、打开游标
3、提取数据
4、关闭游标
declare
--声明游标
cursor c1 is 查询命令;
begin
--打开游标
open c1;
--提取数据
fetch c1 into 变量名;
--关闭游标
close c1;
end;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
普通显式游标
示例:
declare
--声明游标
cursor c1 is select ename,sal from emp where deptno=10;
--临时变量
vname emp.ename%type;
vsal emp.sal%type;
begin
--打开游标
open c1;
--逐行提取数据
loop
fetch c1 into vname,vsal;
-- 实际的业务...
if vsal>3000 and vsal<=10000 then
dbms_output.put_line('调薪:2000');
else
dbms_output.put_line('调薪:1000');
end if;
exit when c1%notfound;
end loop;
--关闭游标
close c1;
end;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
带变量的游标
declare
--声明游标
cursor c1(vno number) is select ename,sal from emp where deptno=vno;
--临时变量
vname emp.ename%type;
vsal emp.sal%type;
begin
--打开游标
open c1(10);
--逐行提取数据
loop
fetch c1 into vname,vsal;
dbms_output.put_line(vname||':工资:'||vsal);
-- 实际的业务...
if vsal>3000 and vsal<=10000 then
dbms_output.put_line('调薪:2000');
else
dbms_output.put_line('调薪:1000');
end if;
exit when c1%notfound;
end loop;
--关闭游标
close c1;
end;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
使用for循环简化游标
--emp_row:行数据
begin
for emp_row in (select ename,sal from emp where deptno=10) loop
dbms_output.put_line(emp_row.ename||emp_row.sal);
end loop;
end;
- 1
- 2
- 3
- 4
- 5
- 6
游标类型的变量
说明:将游标当作数据类型,来声明变量;比如:存储过程,函数,将查询的多条结果存入到游标中,返回供别的过程使用;
declare
--游标类型
type cur_type is ref cursor;
--使用该游标类型 声明一个变量
cur1 cur_type;
--行类型的变量用来存储每次读取到的数据
emp_row emp%rowtype;
begin
open cur1 for select * from emp;
loop
fetch cur1 into emp_row;
dbms_output.put_line(emp_row.ename||emp_row.sal);
exit when cur1%notfound;
end loop;
close cur1;
end;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
隐式游标
begin
delete from emp where empno=621;
if SQL%NOTFOUND then
dbms_output.put_line('数据不存在');
else
dbms_output.put_line('删除成功');
end if;
end;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
游标测试
create or replace procedure test_cur5
as
--声明变量
cursor c1 is select deptno,count(*) from emp group by deptno;
vno number(3);
vcount number(5);
begin
open c1;
loop
fetch c1 into vno,vcount;
dbms_output.put_line('部门:'||vno||'人数:'||vcount);
exit when c1%notfound;
end loop;
close c1;
end;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
测试:
begin
test_cur5;
end;
- 1
- 2
- 3
2、触发器
触发器:
隐式(自动)执行的存储过程;当执行:DDL(create,alter,drop)操作,DML(insert,update,delete)操作时,一系列的系统事件(实例加载,登录、登出)时会激活相应类型的触发器;
触发器分类:
1、DDL触发器
2、DML触发器
3、系统触发器
DML触发器
创建触发器:
create or replace trigger 名字 --trigger 触发器
before or after or instead of --执行时间 before:之前 after:之后 instead of :替代(替换)
insert or update or delete --激活触发器的操作(动作)
on 表名 或者 视图名 --触发器操控的数据库对象
for each row -- 行级触发器 :数据操作影响的每一行数据都会激活触发器
begin
--命令 如果出现错误:
rasie_application_error(); --触发器执行过程中一旦发生错误,会导致原先的数据操作:一并回滚
end;
-- 触发器中规定,最多不超过:32k的代码;如果代码过多可以封装成过程,在触发器中调用
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
例子:
1、不允许删除员工的数据
create or replace trigger tg_test1
before delete on emp
begin
raise_application_error(-20001,'不允许删除员工表的数据');
end;
- 1
- 2
- 3
- 4
- 5
测试:
delete from emp where empno=66;
- 1
触发器中的内置的对象:
逻辑表:
- :old 旧表:用来存放删除的数据;delete和update时会用到
- :new 新表:用来存放新的数据;insert和update时会用到
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oSAyVFAF-1601737304231)(6、Oracle游标和触发器.assets/image-20200923151129100.png)]
1、不允许降薪
create or replace trigger tg_test2
before update on emp
for each row --行级
begin
-- 获取原来的薪资 ,获取修改后的薪资
if :old.sal>:new.sal then
raise_application_error(-20002,'不允许降薪操作');
end if;
end;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
触发器中的:3个条件谓词:
- updating: 判断是否是修改操作
- deleting: 判断是否是删除操作
- inserting: 判断是否是录入操作
用来判断当前是那种数据操作激活了触发器;直接当作条件使用;
create or replace trigger tg_test3
after insert or update or delete
on emp
begin
if updating then
dbms_output.put_line('修改操作激活了触发器');
elsif inserting then
dbms_output.put_line('录入操作激活了触发器');
elsif deleting then
dbms_output.put_line('删除操作激活了触发器');
end if;
end;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
准备一个员工表相同结构的的历史记录表;
-- 复制表结构
create table emp_his as select * from emp where 1=2;
--复制表
create table emp_his2 as select * from emp;
- 1
- 2
- 3
- 4
例子:删除员工数据时,将删除的数据插入到历史表中
create or replace trigger tg_test4
before delete on emp for each row
begin
insert into emp_his(empno,ename,job,sal) values(:old.empno,:old.ename,:old.job,:old.sal);
end;
- 1
- 2
- 3
- 4
- 5
替代触发器只能作用于视图上面:
什么是视图(view):虚拟表,本质上是一个查询命令;
1、授予该用户创建视图的权限:
grant create view to scott;
- 1
2、创建视图
将一个复杂的查询命令存储起来,以供重复使用;
创建视图
create view v_selectemp as
select dt.deptno,dt.dname,count(e.empno) vcount from dept dt,emp e where dt.deptno=e.deptno
group by dt.deptno,dt.dname;
--测试
select * from v_selectemp where vcount>6 order by vcount ;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
视图的作用:简化查询操作,不允许:insert,update,delete
例子:
-- 测试录入
insert into v_selectemp(deptno,dname,vcount) values(30,'帅哥部',30);
create or replace trigger tg_test5
instead of insert on v_selectemp for each row --替换录入操作
begin
delete from emp where deptno=:new.deptno; --换成了删除操作
end;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
应用场景:
1、限制数据的修改规则
2、实现自动记录历史记录
3、模拟自增列
create or replace trigger tg_test6
before insert on emp for each row
begin
select 序列.nextval into :new.empno from dual;
end;
- 1
- 2
- 3
- 4
- 5
Java中jdbc调用oracle存储过程:
获取驱动包:
D:\app\MrLang\product\11.1.0\db_1\jdbc\lib
- 1
maven中注册jar包:
mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.1.0.6.0 -Dpackaging=jar -Dfile=驱动包路径
- 1
引入依赖项:
<dependencies>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.1.0.6.0</version>
</dependency>
</dependencies>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
测试调用过程:
public class Test1 {
static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
static final String USERNAME = "scott";
static final String PWD = "tiger";
public static void main(String[] args) {
Connection connection=null;
//操作过程
CallableStatement statement=null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PWD);
// 与编译命令:指定要调用的过程,
statement=connection.prepareCall("{call sp_zhuanzhang3(?,?,?,?)}");
// 绑定参数
// 输入参数:传参
statement.setInt(1,2);
statement.setInt(2,1000);
// 输出参数:只需要指定对应的数据类型;注册输出参数
statement.registerOutParameter(3,OracleTypes.NUMBER);
statement.registerOutParameter(4,OracleTypes.NUMBER);
// 执行命令
statement.execute();
// 执行后:可以获取到输出参数的值
double b1= statement.getDouble(3);
double b2=statement.getDouble(4);
System.out.println("转账后的余额:"+b1+":"+b2);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
总结
tStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
# 总结
- 1
- 2
- 3
- 4
- 5
文章来源: aaaedu.blog.csdn.net,作者:tea_year,版权归原作者所有,如需转载,请联系作者。
原文链接:aaaedu.blog.csdn.net/article/details/108914509
- 点赞
- 收藏
- 关注作者
评论(0)