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;

2、导出（备份）和导入（还原）

2.1 需要准备虚拟目录：

--创建一个虚拟目录:映射到指定的物理路径上 create directory dir331 as 'd:/oracledata'; --授予u331用户操作dir331的权限：read,write grant read,write on directory dir331 to u331;

2.2 导出：expdp

(数据泵程序)

注意：在cmd窗口执行

expdp u331/123456 schemas=u331 dumpfile=u331.dmp logfile=u331.log directory=dir331 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、使用新创建的用户登录创建好的表空间，创建一个数据表并录入一些数据供测试

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;

如果忘掉管理员密码：

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、触发器

学习内容

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;

普通显式游标

示例：

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;

带变量的游标

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;

使用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;

游标类型的变量

​ 说明：将游标当作数据类型，来声明变量；比如：存储过程，函数，将查询的多条结果存入到游标中，返回供别的过程使用；

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;

隐式游标

begin delete from emp where empno=621; if SQL%NOTFOUND then dbms_output.put_line('数据不存在'); else dbms_output.put_line('删除成功'); end if; end;

游标测试

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;

测试：

begin test_cur5; end;

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、不允许删除员工的数据

create or replace trigger tg_test1 before delete on emp begin raise_application_error(-20001,'不允许删除员工表的数据'); end;

测试：

delete from emp where empno=66;

触发器中的内置的对象：

逻辑表：

: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;

触发器中的：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;

准备一个员工表相同结构的的历史记录表；

-- 复制表结构 create table emp_his as select * from emp where 1=2; --复制表 create table emp_his2 as select * from emp;

例子：删除员工数据时，将删除的数据插入到历史表中

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;

替代触发器只能作用于视图上面：

什么是视图（view):虚拟表，本质上是一个查询命令；

1、授予该用户创建视图的权限：

grant create view to scott;

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 ;

视图的作用：简化查询操作，不允许：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、模拟自增列

create or replace trigger tg_test6 before insert on emp for each row begin select 序列.nextval into :new.empno from dual; end;

Java中jdbc调用oracle存储过程：

获取驱动包：

D:\app\MrLang\product\11.1.0\db_1\jdbc\lib

maven中注册jar包:

mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.1.0.6.0 -Dpackaging=jar -Dfile=驱动包路径

引入依赖项：

<dependencies> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc6</artifactId> <version>11.1.0.6.0</version> </dependency> </dependencies>

测试调用过程：

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(); } } } } }

总结

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(); } } } } }

# 总结 tea_year 认证博客专家 大司徒 产品总监 微软MVP！ORACLE认证高级工程师！主要研究方向为大数据、人工智能、JAVA、.Net、数据库 、前端开发、产品研发，曾经服务过中铝、中烟等大型上市国企IT部门，软件企业联合创始人，喜欢软件研发管理、技术营销！