Oracle_day04-游标&系统异常&存储函数&触发器
/*
序列: ORACLE使用来模拟ID自动增长的
*/
create sequence seq_test4;
create table test2(
tid number primary key,
tname varchar2(10)
);
insert into test2 values(seq_test4.nextval,‘张三’);
select * from test2;
/*
PLSQL编程: 过程语言,编写一些复杂业务逻辑
输出星号:
abs(y) + abs(x) <= m
vsal emp.sal%type --引用型变量
row emp%rowtype --记录型变量
select sal into vsal from emp where empno=7788;
/
declare
m number := 3;
begin
for y in -m…m loop
for x 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;
/*
游标(光标): 是用来操作查询结果集,相当于是JDBC中ResultSet
语法: cursor 游标名[(参数名 参数类型)] is 查询结果集
开发步骤:
1. 声明游标
2. 打开游标 open 游标名
3. 从游标中取数据 fetch 游标名 into 变量
游标名%found :找到数据
游标名%notfound : 没有找到数据
4. 关闭游标 close 游标名
系统引用游标
1. 声明游标 : 游标名 sys_refcursor
2. 打开游标: open 游标名 for 结果集
3. 从游标中取数据
4. 关闭游标
for循环遍历游标:
不需要声明额外变量
不需要打开游标
不需要关闭游标
/
–输出员工表中所有的员工姓名和工资(不带参数游标)
/
游标:所有员工
声明一个变量,用来记录一行数据 %rowtype
*/
declare
–游标
cursor vrows is select * from emp;
–s声明变量,记录一行数据
vrow emp%rowtype;
begin
–1.打开游标
open vrows;
–2.从游标提取数据
–循环取数据
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line(‘姓名:’||vrow.ename ||’ 工资: ’ || vrow.sal);
end loop;
–3.关闭游标
close vrows;
end;
–输出指定部门下的员工姓名和工资
/*
游标: 指定部门的所有员工
声明一个变量记录一行数据
*/
declare
–声明游标
cursor vrows(dno number) is select * from emp where deptno = dno;
–声明变量
vrow emp%rowtype;
begin
–1.打开游标 , 指定10号部门
open vrows(10);
–2. 循环遍历,取数据
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line(‘姓名:’||vrow.ename ||’ 工资: ’ || vrow.sal);
end loop;
close vrows;
end;
–系统引用游标
–输出员工表中所有的员工姓名和工资
declare
–声明系统引用游标
vrows sys_refcursor;
–声明一个变量
vrow emp%rowtype;
begin
–1.打开游标
open vrows for select * from emp;
–2.取数据
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line(‘姓名:’||vrow.ename ||’ 工资: ’ || vrow.sal);
end loop;
close vrows;
end;
–扩展内容----使用for循环遍历游标
declare
–声明一个游标
cursor vrows is select * from emp;
begin
for vrow in vrows loop
dbms_output.put_line(‘姓名:’||vrow.ename ||’ 工资: ’ || vrow.sal || ‘工作:’|| vrow.job);
end loop;
end;
select * from emp;
–按照员工工作给所有员工涨工资,总裁涨1000,经理涨800,其他人涨400
/*
游标 : 所有员工
声明一个记录一行数据
*/
declare
–声明游标
cursor vrows is select * from emp;
–声明一个变量
vrow emp%rowtype;
begin
–1.打开游标
open vrows;
–2.循环取数据
loop
–取数据
fetch vrows into vrow;
–退出条件
exit when vrows%notfound;
–根据不同的职位,涨工资 总裁涨1000,经理涨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.关闭游标
close vrows;
–4.提交事务
commit;
end;
select * from emp;
/*
例外:(意外)程序运行的过程发生异常,相当于是JAVA中的异常
declare
–声明变量
begin
–业务逻辑
exception
–处理异常
when 异常1 then
…
when 异常2 then
…
when others then
…处理其它异常
end;
zero_divide : 除零异常
value_error : 类型转换异常
too_many_rows : 查询出多行记录,但是赋值给了rowtype记录一行数据变量
no_data_found : 没有找到数据
自定义异常:
异常名 exception;
raise 异常名
*/
declare
vi number;
vrow emp%rowtype;
begin
–vi := 8/0;
–vi := ‘aaa’;
–select * into vrow from emp;
select * into vrow from emp where empno=1234567;
exception
when zero_divide then
dbms_output.put_line(‘发生了除零异常’);
when value_error then
dbms_output.put_line(‘发生了类型转换异常’);
when too_many_rows then
dbms_output.put_line(’ 查询出多行记录,但是赋值给了rowtype记录一行数据变量’);
when no_data_found then
dbms_output.put_line(‘没有找到数据异常’);
when others then
dbms_output.put_line(‘发生了其它异常’ || sqlerrm);
end;
–查询指定编号的员工,如果没有找到,则抛出自定义的异常
/*
–错误的演示
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
声明一个游标
声明一个变量,记录数据
从游标中取记录
如果有,则不管它
如果没有就抛出自定义的异常
*/
declare
–声明游标
cursor vrows is select * from emp where empno=8888;
–声明一个记录型变量
vrow emp%rowtype;
–声明一个自定义异常
no_emp exception;
begin
–1.打开游标
open vrows;
–2.取数据
fetch vrows into vrow;
–3.判断游标是否有数据
if vrows%notfound then
raise no_emp;
end if;
close vrows;
exception
when no_emp then
dbms_output.put_line(‘发生了自定义的异常’);
end;
/*
存储过程: 实际上是封装在服务器上一段PLSQL代码片断,已经编译好了的代码
1.客户端取调用存储过程,执行效率就会非常高效
语法:
create [or replace] procedure 存储过程的名称(参数名 in|out 参数类型,参数名 in|out 参数类型)
is | as
–声明部分
begin
–业务逻辑
end;
/
–给指定员工涨薪,并打印涨薪前和涨薪后的工资
/
参数 : in 员工编号
参数 : 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;
–方式1
call proc_updatesal(7788,10);
–方式2 用的最多的方式
declare
begin
proc_updatesal(7788,-100);
end;
/*
存储函数: 实际上是一段封装是Oracle服务器中的一段PLSQL代码片断,它是已经编译好了的代码片段
语法:
create [or replace] function 存储函数的名称(参数名 in|out 参数类型,参数名 in|out 参数类型) return 参数类型
is | as
begin
end;
存储过程和函数的区别:
1.它们本质上没有区别
2.函数存在的意义是给过程调用 存储过程里面调用存储函数
3.函数可以在sql语句里面直接调用
4.存储过程能实现的,存储函数也能实现,存储函数能实现的,过程也能实现
默认是 in
/
–查询指定员工的年薪
/
参数 : 员工的编号
返回 : 年薪
/
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;
–调用存储函数
declare
vsal number;
begin
vsal := func_getsal(7788);
dbms_output.put_line(vsal);
end;
–查询员工的姓名,和他的年薪
select ename,func_getsal(empno) from emp;
–查询员工的姓名和部门的名称
–查询指定员工的年薪–存储过程来实现
–参数: 员工编号
–输出: 年薪
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;
declare
vtotal number;
begin
proc_gettotalsal(7788,vtotal);
dbms_output.put_line(‘年薪:’||vtotal);
end;
select * from emp where empno = 8888;
/*
JAVA调用存储过程
JDBC的开发步骤:
1.导入驱动包
2.注册驱动
3.获取连接
4.获取执行SQL的statement
5.封装参数
6.执行SQL
7.获取结果
8.释放资源
*/
/*
封装一个存储过程 : 输出所有表中的记录
输出类型 : 游标
*/
create or replace procedure proc_getemps(vrows out sys_refcursor)
is
begin
–1.打开游标, 给游标赋值
open vrows for select * from emp;
end;
/*
触发器: 当用户执行了 insert | update | delete 这些操作之后, 可以触发一系列其它的动作/业务逻辑
作用 :
在动作执行之前或者之后,触发业务处理逻辑
插入数据,做一些校验
语法:
create [or replace] trigger 触发器的名称
before | after
insert | update | delete
on 表名
[for each row]
declare
begin
end;
触发器的分类:
语句级触发器: 不管影响多少行, 都只会执行一次
行级触发器: 影响多少行,就触发多少次
: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’);
–数据校验, 星期六老板不在, 不能办理新员工入职
–在插入数据之前
–判断当前日期是否是周六
–如果是周六,就不能插入
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(‘老板不在,不能办理入职’);
–抛出系统异常
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(‘更新了数据’);
end;
update emp set sal = sal+10;
–判断员工涨工资后的工资一定要大于涨工资前的工资
/*
200 --> 100
触发器 : before
旧的工资
新的工资
如果旧的工资大于新的工资 , 抛出异常,不让它执行成功
触发器中不能提交事务,也不能回滚事务
*/
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,‘张三’);
触发器:
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;
–触发器
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;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import org.junit.Test;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
/*
1.导入驱动包
2.注册驱动
3.获取连接
4.获取执行SQL的statement
5.封装参数
6.执行SQL
7.获取结果
8.释放资源
*/
public class TestProcedure {
@Test
/*
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;
* */
public void test1() throws Exception{
//注册驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.获取连接
String url = "jdbc:oracle:thin:@192.168.80.100:1521:orcl";
String username = "dakang";
String password = "dakang";
Connection conn = DriverManager.getConnection(url, username, password);
//3.获取执行SQL的statement.这是一个固定格式 proc_gettotalsal是一个存储函数
String sql = "{call proc_gettotalsal(?,?)}";
CallableStatement state = conn.prepareCall(sql);
//设置输入参数
state.setInt(1, 7788);//设置员工编号
//注册输出参数类型
state.registerOutParameter(2, OracleTypes.NUMBER);
//4.执行statement
state.execute();
//5.获取执行结果
int totalsal = state.getInt(2);
//输出结果
System.out.println("工资:"+ totalsal);
//6.释放资源
state.close();
conn.close();
}
//调用存储函数
/*
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;
*/
@Test
public void test2() throws Exception{
//注册驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.获取连接
String url = "jdbc:oracle:thin:@192.168.80.100:1521:orcl";
String username = "dakang";
String password = "dakang";
Connection conn = DriverManager.getConnection(url, username,password);
//3.获取执行SQL的statement
String sql = " {?= call func_getsal(?)}";
CallableStatement state = conn.prepareCall(sql);
//4.封装参数
//注册返回类型参数
state.registerOutParameter(1, OracleTypes.NUMBER);
//设置第二个参数
state.setInt(2, 7788);
//5.执行SQL
state.execute();
//6.获取结果
int totalsal = state.getInt(1);
System.out.println("年薪 : ====" +totalsal);
//7.释放资源
state.close();
conn.close();
}
/*
create or replace procedure proc_getemps(vrows out sys_refcursor)
is
begin
--1.打开游标, 给游标赋值
open vrows for select * from emp;
end;
* */
@Test
public void test3() throws Exception{
//注册驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.获取连接
String url = "jdbc:oracle:thin:@192.168.80.100:1521:orcl";
String username = "dakang";
String password = "dakang";
Connection conn = DriverManager.getConnection(url, username,password);
//3.获取执行SQL的statement
String sql = "{call proc_getemps(?)}";
CallableStatement call = conn.prepareCall(sql);
//接口 --- > 对象 -->实现类的名称
System.out.println(call.getClass().getName());
OracleCallableStatement oracleCall = (OracleCallableStatement)call;
//4.注册输出类型的参数
call.registerOutParameter(1, OracleTypes.CURSOR);
//5.执行SQL
call.execute();
//6.获取执行的结果
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();
}
}- 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
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
浙公网安备 33010602011771号