PLSQL编程，游标，存储过程，触发器

PLSQL

procedure Language 过程语言，oracle对sql的一个扩展，让我们能够像在java中一样写if else条件，还可以编写循环逻辑 for while

语法：
declare
-- 声明变量
变量名 变量类型；
begin
-- 业务逻辑
end；

• 普通型的变量

/*
-- 变量名 变量类型 ：=初始值；
declare
i varchar2(10):='张三';
begin
dbms_output.put_line(i);
-- 相当于java中的syso
end;
*/

• 引用型的变量

/*
-- 查询7369的工资，并打印出来
declare
vsal emp.sal%type;
begin
-- 将查询出的结果赋值给vsal
select sal into vsal from emp where empno=7369;
dbms_output.put_line(vsal);
-- 相当于java中的syso
end;
*/

• 声明记录型的变量

/*
-- 查询7369的员工信息，并打印出来
declare
vrow emp%rowtype;
begin
-- 将查询出的结果赋值给vsal
select * into vrow from emp where empno=7369;
dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
-- 相当于java中的syso
end;

• pl条件判断

游标（光标）

• 游标是用来操作查询结果集，相当于是jdbc中的ResultSet

• 语法：cursor 游标名 is 查询结果集
• 开发步骤：
  1.声明游标
  2.打开游标 open 游标名
  3.从游标中取数据 fetch 游标名 into 变量
  游标名%found：找到数据
  游标名%notfound：没有找到数据
  4.关闭游标 close 游标名

• 无参数的游标

输出所有员工表中所有的员工姓名和工资

/*
游标：所有员工
声明一个变量，用来记录一行数据 %rowtype
*/
declare
-- 游标
cursor vrows is select * from emp;
-- 声明变量，记录一行数据
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;

• 带参数的游标

• 语法：cursor 游标名 [(参数名，参数类型)] is 查询结果集

-- 输出指定部门下的员工姓名和工资
/*
结果集：指定部门的所有员工
声明一个变量，用来记录一行数据 %rowtype
*/
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;
--3.关闭游标
close vrows;
end;

存储过程

实际上是封装在服务器上的一段plsql代码片段，是已经编译好了的代码
客户端调用存储过程，执行效率就会非常高

• 语法:create [or replace] procedure 存储过程的名称（参数名 in|out 参数类型）is|as
  -- 声明部分
  begin
  -- 业务逻辑
  end;

• 输入参数和输出参数

User user = new User();
public void getUser(int userId,user){
//userId是输入参数，user是输出参数
    user.setName();
}

例：给指定员工涨工资

/*
参数：员工编号 in，涨多少 in
声明一个变量：存储涨工资前的工资
查询出当前工资是多少
打印涨薪前的工资
更新工资
打印涨薪后的工资
*/
create or replace procedure pro_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 pro_updatesal(7788,10);
-- 方式2：
declare
begin
   pro_updatesal(7788,-10);
end;

触发器

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

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

-- 新员工入职之后，输出一句话，欢迎您
create or replace trigger tri_test1
after 
insert
on emp
declare
begin
dbms_output.put_line('欢迎您');
end;
-- 测试：
insert into emp(empno,ename)values(1111,'zhangsan')

-- 数据校验，星期六不能办理新员工入职
-- 在插入数据之前，判断当前日期是否是周六，如果是周六，就不能插入
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(1111.'zhangsan')

• 行级触发器

-- 更新所有员工的工资，输出一句话
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+100;

:old 代表旧的记录，更新前的记录
:new 代表新的记录，更新后的记录

-- 判断员工涨工资后的工资一定要大于涨工资前的工资
/*
触发器：befor
旧的工资>新的工资，抛出异常，不让它执行成功
*/
create or replace trigger tri_test4
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+100;

update emp set sal=sal-100;