Oracle学习基础（四）总结_使用不带参数游标,显示工资大于的员工姓名和工资-优快云博客

本文介绍了Oracle数据库的基础知识，包括游标的声明、打开、关闭和遍历，系统引用游标的操作，异常处理的语法及常见异常类型，以及存储过程和存储函数的概念、创建语法和区别。此外，还提到了触发器的作用、分类和使用场景，以及如何在Java中调用存储过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

复习：

--使用Oracle来模拟ID的自增长

--创建序列
create sequence seq_test1;
--创建表
create table test2(
     tid number primary key,
     tname varchar2(10)
);

--tid自增长插入
insert into test2 values(seq_test1.nextval,'张山');

select * from test2;

--删除表
drop table test2;

/*
     PLSQL编程：过程语言，编写一些复杂业务逻辑。
     
     输出星号：
             abs(x)+abs(y)<=m
             
      vsal emp.sal%type;--引用型变量
      vrow emp%rowtype;--记录型变量
      
      select sal into vsal from emp where empno=7788;


*/

--使用abs（）函数输出菱形
declare
m number :=5;
begin
  for y in -m..m loop
    for x in -m..m loop
      if abs(x)+abs(y)<=m then
        dbms_output.put('*');
        else
          dbms_output.put(' ');
        end if;
      end loop;
      
      dbms_output.new_line();--此处必须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 crow is select * from scott.emp;
   vrow scott.emp%rowtype;
begin
  open crow;
  loop
    fetch crow into vrow;
  exit when  crow%notfound;
     dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
     
  end loop;
  close crow;
end;

--先创建一个此用户下emp表，方便使用。
create table emp as select * from scott.emp;
select * from emp;

--带参数游标的使用
----查询出指定员工的姓名和薪资
declare
   cursor crow2(cno number) is select * from emp where empno=cno;
   vrow emp%rowtype;
begin
   open crow2(7369);
   fetch crow2 into vrow;
   dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
   close crow2;
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;
  --3.关闭游标
  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);
  end loop;
end;
--按照员工工作给所有员工涨工资,总裁涨1000,经理涨800,其他人涨400
/*
    游标 : 所有员工
    声明一个记录一行数据   
*/
declare
  cursor vrows is select * from emp;
  vrow emp%rowtype;
begin
  open vrows;
  loop
    fetch vrows into vrow;
      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;       
  exit when vrows%notfound;
  
  end loop;
  close vrows;
  commit;
end;

例外：（意外）程序运行过程发生异常。
语法：
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:='xxx'; --类型转换异常
  --select * into vrow from emp; --too_many_rows 异常
  select * into vrow from emp where empno=1111; --没有找到数据的异常
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('没有找到数据！');
end;

--练习使用自定义异常

--错误演示：此处抛出的是no_data_found异常，并不会抛出no_emp这个自定义的异常。
--原因是执行' select * into vrow from emp where empno=8888;'时，已经抛出了异常，所以if抛出自定义异常是不执行的。
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 no_data_found then
   dbms_output.put_line('输出no_data_found异常');
end;

--正确演示：只能使用游标判断。
declare
   vrow emp%rowtype;
   no_emp exception;
   cursor crows(ndo number) is select * from emp where empno=ndo;
begin
   open crows(8888);
   fetch crows into vrow;
   if crows%notfound then
      raise no_emp;
   end if;
   close crows;
exception
 when no_emp then
 dbms_output.put_line('输出自定义异常');
 when no_data_found then
 dbms_output.put_line('输出no_data_found异常');
end;

存储过程：其实是封装在服务器上的一段PLSQL代码，已经编译好了。
1.客户端调用存储过程，执行效率非常高
语法：
create [or replace] procedure 存储过程的名称（参数 in|out 参数类型，参数名 in|out 参数类型）
is | as
--声明部分
begin
--业务逻辑
end;

--练习1：给指定员工涨薪，并打印涨薪前和涨薪后的工资。
/*
   参数： in 员工编号
   参数： in 涨多少
   声明一个变量：记录原工资
   查询当前是多少
   打印原工资
   修改涨薪
   打印出涨薪后的工资

*/
create or replace procedure pro_test1(eno in number,money in number)
is
  vrow emp%rowtype;
begin
  select * into vrow from emp where empno=eno;
  dbms_output.put_line('涨薪前：'||vrow.sal); 
  update emp set sal=sal+money where empno=eno;
  select * into vrow from emp where empno=eno;
  dbms_output.put_line('涨薪后：'||vrow.sal); 
  commit;
end;

--调用procedure的方式
--1.方式一：
  call pro_test1(7788,10);
--2.方式二：最常用
declare

begin
  pro_test1(7788,15);
end;

存储函数：封装在Oracle服务器上的一段PLSQL代码，它是编译好的代码

语法：
create [or repalce] function 存储函数的名称（参数名 in|out 参数类型,参数名 in|out 参数类型） return 参数类型
is|as

begin

end;

存储过程和存储函数的区别：
1.本质上没有什么区别
2.存储函数存在意义是给过程调用，一般存储过程里面调用存储函数。
3.存储函数可以在sql语句里面直接调用。
4.存储过程能实现，存储函数也能实现，同时存储函数能实现的，存储过程也能实现。
5.存储函数必须返回一个类型的数值。（return）

默认参数类型是 in(输入参数) 不是out(输出参数)


--根据员工id查询员工年薪（基础工资+奖金）
create or replace function getMoenyById(eno number) return number
is
 salyear number;
begin
  select sal*12+nvl(comm,0) into salyear from emp where empno=eno;
  return salyear;
end;

--调用存储函数:
declare
  vsal number;
begin
  vsal:=getMoenyById(7788);
  dbms_output.put_line(vsal);
end;

Java中调用存储过程的步骤：友情链接：https://www.cnblogs.com/huhx/p/JavaProcedure.html
JAVA调用存储过程：
JDBC的开发步骤：
1.导入驱动包
2.注册驱动
3.获取链接
4.获取执行SQL的Statement
5.封装数据
6.执行SQL
7.获取结果
8.释放资源

触发器：当用户执行了 insert| update| delete 这些操作之后，引发一系列的其他的动作/业务逻辑
作用：
在动作执行之前后者之后，触发一系列的业务逻辑。
插入数据时，做一些校验
语法：
create [or replace] trigger 触发器名称
before | after
insert | update |delete
on 表名
[for each row]
declare

begin

end;

触发器的分类：
语句级触发器：不管影响多少行，都只会执行一次。

行级触发器：影响多少行，就执行多少次。（for each row）
：old 代表旧的记录，更新前的记录
：new 代表新的记录

--新员工入职后，输出：欢迎加入
create or replace trigger tri_test1
after
insert
on emp
declare

begin
  dbms_output.put_line('欢迎加入！');
end;

insert into emp(ename) values('张三硕士');

--触发器的数据校验功能
--星期六老板不在, 不能办理新员工入职
--在插入数据之前
--判断当前日期是否是周六
--如果是周六,就不能插入
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;

触发器的 :old 和:new 两个对象的使用
判断员工涨工资后的工资一定要大于涨工资前的工资
200 --> 100
触发器 : before
旧的工资 :old
新的工资 :new
如果旧的工资大于新的工资 , 抛出异常,不让它执行成功

触发器中不能提交事务,也不能回滚事务

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;

模拟mysql中ID的自增属性 auto_increment
insert into person(null,'张三');

触发器:

pid=1 insert pid=1

序列 : create sequence seq_person_pid;

---让触发器在插入前取得序列的值，并插入新数据中。

create table stu1(
      sid number,
      sname varchar2(10)
);

create  sequence seq_test2;

create or replace trigger tri_rr
before
insert
on stu1
for each row
declare
 
begin
   dbms_output.put_line(:new.sname);
   select seq_test2.nextval into :new.sid from dual;
end;

insert into stu1 values(null,'张三ss');

select * from stu1;