Oracle数据库基本操作（四） —— PLSQL编程

　　Procedure Language 实际上是Oracle对SQL语言的能力扩展,让SQL语言拥有了if条件判断,for循环等处理。

一、PLSQL基本语法

DECLARE 
    -- 声明部分
    变量名 变量类型 := 初始值 
    变量名 emp.sal % TYPE  -- 引用类型的变量
          emp % rowtype -- 记录型变量           
BEGIN
    -- 业务逻辑
END ;

1、变量的声明与使用

-- 已知数据类型的赋值声明 
DECLARE 
    i NUMBER := 100 ;
BEGIN
    -- 输出语句相当于 System.out.print();
    dbms_output.put_line('Hello World!' || i) ;
END ;

-- 未知数据类型的类型声明
-- 输出7369的工资

DECLARE 
    vsal emp.sal % TYPE ;
BEGIN
    -- 给变量赋值
    SELECT sal INTO vsal FROM emp WHERE empno = 7369 ; 
    dbms_output.put_line(vsal) ;
END ;

-- 记录型变量声明与赋值
-- 输出7369的所有信息
DECLARE 
    vrow emp % rowtype ;
BEGIN    
    SELECT * INTO vrow FROM emp WHERE empno = 7369 ;
    dbms_output.put_line(vrow.empno||'   '|| vrow.ename);
END ;

2、if条件判断语法与使用

-- 根据不同年龄输出信息
DECLARE
    -- 由客户端输入     
    age number := &aaa;
BEGIN
    IF age <= 18 THEN
        dbms_output.put_line('未成年人');
    ELSIF age > 18 AND age <= 24 THEN
        dbms_output.put_line('年轻人');
    ELSIF age > 24 AND age < 48 THEN
        dbms_output.put_line('中年人');
    ELSE 
        dbms_output.put_line('老年人');
    END IF;
END;

3、三种循环

/*
   三种循环
   for 变量名 in 起始值..结束值  loop
     
   end loop; 
  ----------------------------------
   while 条件 loop
     
   end loop;
  -----------------------------------    
   loop
     exit when 退出的条件
     循环体
   end loop;
*/

-- for 循环
-- 输出1-10
DECLARE 

BEGIN
    FOR i IN 1..10 LOOP
        dbms_output.put_line(i);
    END LOOP;
END;
-- 输出10-1
DECLARE 

BEGIN
    FOR i IN REVERSE 1..10 LOOP
        dbms_output.put_line(i);
    END LOOP;
END;

-- while 循环
DECLARE 
    i NUMBER := 1;
BEGIN
    WHILE i <= 10 loop
        dbms_output.put_line(i);
        i := i+1;
    END LOOP;
END;

-- 简单循环
DECLARE 
    i NUMBER := 1;
BEGIN 
    LOOP
        EXIT WHEN i > 10;
        dbms_output.put_line(i);
        i := i+1;
    END LOOP;
END;

二、游标

1、游标概述　　

1.1 游标: (光标/指针) 是对查询结果集的封装, 相当于是jdbc中的ResultSet

1.2 语法:

-- 声明游标
    CURSOR 游标名 IS 查询语句;
    CURSOR 游标名(参数名 参数类型) IS 查询语句 WHERE 列名 = 参数名;

1.3 开发步骤:

1.打开游标 open 游标名

2.从游标中提取数据:

fetch 游标名 into 变量

　　   游标名%notfound 没有数据

　　　游标名%found 找到数据

3.关闭游标 close 游标名

2、使用示例：

-- 无参 
-- 输出所有员工的信息
DECLARE
   -- 声明游标
  CURSOR vemps IS SELECT * FROM emp;
   -- 声明变量
  vrow emp % rowtype;
BEGIN
   --1. 打开游标
   open vemps;
   --2. 提取数据
   LOOP
       FETCH vemps INTO vrow;
        -- 判断是否有数据
       EXIT WHEN vemps % notfound;
        -- 打印数据
       dbms_output.put_line('姓名:'||vrow.ename||'  工资:'||vrow.sal);
   END LOOP;
   
   -- 关闭游标
   CLOSE vemps;
END;
---------------------------------------------------------------- 
-- for 变量游标        
DECLARE
   -- 声明游标
   CURSOR vemps IS SELECT * FROM emp;
   -- 声明记录型变量
   vrow emp % rowtype;
BEGIN
   -- 循环遍历游标
   FOR vrow IN vemps
   LOOP
        dbms_output.put_line('姓名:'||vrow.ename||' 工资:'||vrow.sal);
   END LOOP;
END;

-- ===============================================================
-- 有参
-- 输出指定部门的员工信息
DECLARE
   -- 声明游标
   CURSOR vemps(vdeptno NUMBER) IS SELECT * FROM emp WHERE deptno = vdeptno;
   -- 声明记录型变量
   vrow emp % rowtype;
BEGIN
   -- 1. 打开游标
   OPEN vemps(20);
   -- 2.循环遍历游标
   LOOP
    FETCH vemps into vrow;
    EXIT when vemps % notfound;
      -- 打印数据
        dbms_output.put_line('姓名:'||vrow.ename||' 工资:'||vrow.sal);
   END LOOP;
   -- 3. 关闭游标
   CLOSE vemps;
END;

三、例外

1、例外概述

      例外 (意外): 相当于是java异常

　　语法: 

declare 
    声明部分 
begin 
    业务逻辑
exception 
    处理例外 
    when 例外1 then 

    when 例外2 then 

    when others then 

end;

　　常见的系统的例外:

• • zero_divide : 除零例外
  • value_error : 类型转换
  • no_data_found : 没有找到数据例外
  • too_many_rows : 查询出多行记录,但是赋值给了单行变量

2、例外使用示例

DECLARE
    i NUMBER;
    vrow emp % rowtype;
BEGIN
--     i := 5/0;
--     i := 'aaa';
--     select * into vrow from emp where empno = 1234566;
    select * into vrow from emp;
EXCEPTION
    WHEN too_many_rows THEN
        dbms_output.put_line('查询出多行记录,但是赋值给了单行变量');
    WHEN no_data_found THEN
        dbms_output.put_line('发生了没有找到数据例外');
    WHEN value_error THEN
        dbms_output.put_line('发生类型转换的例外');
    WHEN zero_divide THEN
        dbms_output.put_line('发生除零的例外');
    WHEN others THEN
        dbms_output.put_line('发生未知的例外');
END;

3、自定义例外

　　语法：

DECLARE 
    -- 声明例外
    例外名称 EXCEPTION ;
BEGIN
    -- 抛出例外
    raise 例外名称 ; 
EXCEPTION 
    -- 捕获例外
    WHEN 例外名称 THEN
    ....
END ;

　　使用示例：

-- 查询指定编号的员工,若没有找到,则抛出自定义例外
DECLARE 
    -- 声明游标
    CURSOR vemps IS SELECT * FROM emp WHERE empno = 1234 ; 
    -- 记录型变量
    vrow vemps % rowtype ; 
    -- 定义例外
    no_emp_found EXCEPTION ; 
BEGIN 
    --1.打开游标
    OPEN vemps ;
    --2.提取记录 
    FETCH vemps INTO vrow ;
    -- 判断是否有数据 
    IF vemps % notfound THEN 
        -- 抛出例外
        raise no_emp_found ; 
    END IF ; 
    -- 关闭游标
    CLOSE vemps ; 
EXCEPTION 
    WHEN no_emp_found THEN 
        dbms_output.put_line('没有找到对应的员工') ; 
END ;

 四、存储过程

1、概述　　

　　存储过程: 实际上是将一段已经编译好的PLSQL代码片断,封装在数据库中。

　　作用:

1. 提高执行效率

2. 提高代码复用性

　　语法:

create [or replace] procedure 过程名称[(参数1 in|out 参数类型,参数2 in|out 参数类型)]
is | as
　　-- 声明　　　　
begin
 　 -- 业务
end;

2、使用示例

　　

-- 给指定员工涨薪,并打印涨薪前和涨薪后的工资
-- 员工编号 : 输入参数
-- 涨多少 : 输入参数
/*
   1. 查询当前工
   2. 打印涨薪前工资
   3. 涨工资
   4. 打印涨薪后的工资
   5. 提交数据
*/
create or replace procedure proc_updatesal(vempno in number,vcount in number)
is
   -- 声明变量记录当前工资
   vsal number;    
begin
   --1. 查询当前工资
   select sal into vsal from emp where empno=vempno;
   --2. 打印涨薪前工资
   dbms_output.put_line('涨薪前:'||vsal);
   --3. 涨工资
   update emp set sal=vsal+vcount where empno=vempno;
   -- 4. 打印涨薪后的工资
   dbms_output.put_line('涨薪后:'||(vsal+vcount));
   --5. 提交数据
   commit;
end;

-- 调用存储过程
-- 方式1:
call proc_update_sal(7369,100);

-- 方式2:
declare

begin
   proc_updatesal(7369,100);
end;

s
-- 获取指定编号员工的年薪
/*
   编号: in  输入
   年薪: out 输出
*/
create or replace procedure proc_getyearsal(vempno in number,vyearsal out number)
is
       
begin
  select sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno;
end;

-- plsql代码片断中调用
declare
   yearsal number;
begin
   proc_getyearsal(7369,yearsal);
   dbms_output.put_line(yearsal);
end;


-- 封装存储过程,输出的是游标类型, 所有员工
/*
   sys_refcursor : 系统引用游标
*/
create or replace procedure proc_getemps(vemps out sys_refcursor)
is

begin
    -- 打开游标, 谁调用谁关闭
    open vemps for select * from emp;   
end;

declare
  vemps sys_refcursor;
  vrow emp%rowtype;
begin
  -- 调用存储过程
  proc_getemps(vemps);
  
  loop
     fetch vemps into vrow; 
     exit when vemps%notfound;  
     dbms_output.put_line(vrow.ename);
  end loop;
  -- 关闭游标
  close vemps;
end;

五、存储函数

1、存储函数概述

　　存储函数: 实际上是将一段已经编译好的PLSQL代码片断,封装在数据库中。

　　作用:

1. 提高执行效率

2. 提高代码复用性

　　语法: 

create [or replace] function 函数名称(参数1 in|out 参数类型) return 返回类型 
is 

begin 

end;

　　存储过程和存储函数:

1. 函数有返回值,过程没有返回值

2. 函数可以直接在SQL语句中使用,过程不可以

3. 函数能实现的功能,过程能实现

4. 过程能实现的功能,函数也能实现

5. 函数和过程本质上没有区别 通常情况下,我们自己开发封装的是存储过程

2、使用示例

-- 存储函数:获取年薪
create or replace function func_getyearsal(vempno number) return number
is
  vyearsal number;
begin
  select sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno;
  return vyearsal;
end;

-- 调用
declare
   yearsal number;
begin
   yearsal := func_getyearsal(7369);
   dbms_output.put_line(yearsal);
end;

select emp.*,func_getyearsal(emp.empno) from emp;

 六、触发器

　　1、数据库触发器是一个与表相关的、存储的PL/SQL程序。每当一个特定的数据操作语句（insert，update，delete）在指定的表上发出是，Oracle自动地执行触发器中定义的语句序列。

　　2、作用：

• • 监听表中的数据变化；
  • 对表中的数据进行校验

　　3、语法：

CREATE [OR REPLACE] TRIGGER 触发器名称 
{BEFORE | AFTER}
{INSERT | UPDATE | DELETE} 
ON 表名 
[ FOR EACH ROW [WHEN(条件)]]
DECLARE
    ....
BEGIN
    PLSQL块
END 触发器名;

　　4、触发器的类型

• 行级触发器：一条SQL语句，影响了多少行记录，触发器就会执行多少次；
  • 两个内置对象：
    • :new 新的记录
    • :old 旧的记录
• 语句级触发器：一条SQL语句，无论影响了多少行记录，都只触发一次；

　　5、使用示例

-- 若用户向表中插入数据之后, 打印一句话
create or replace trigger tri_test1
after
insert 
on emp
declare

begin
   dbms_output.put_line('有人插入了....');
end;

insert into emp(empno,ename) values(9527,'华安');
-- 执行一条更新工资的语句

-- 周二老板不在,不能办理员工入职(不能向员工表中插入数据)
-- 触发器
-- before insert
-- 判断今天是否是周二
select trim(to_char(sysdate,'day')) from dual;

create or replace trigger tri_checkday
before
insert
on emp
declare
   vday varchar2(20);
begin
   -- 查询当前周几
   select trim(to_char(sysdate,'day')) into vday from dual;
   -- 判断是否为周二,若为周二,则需要中断插入操作
   if vday = 'tuesday' then
     --                   -20000 - -20999
     raise_application_error(-20001,'周二老板不在,不能插入');
   end if;
end;

insert into emp(empno,ename) values(9527,'华安');

select * from emp;

-- 语句级触发器
create trigger tri_test3
before
update
on emp
declare

begin
  dbms_output.put_line('语句级触发器'); 
end;

-- 行级触发器
create or replace trigger tri_test4
before
update
on emp
for each row
declare

begin
  dbms_output.put_line('行级触发器,旧的工资:'||:old.sal||'  新的工资:'||:new.sal); 
end;

update emp set sal=sal+100;

-- 6个月 ---> 人事 加薪 ---> 加10块钱 ---> 老板签字
-- 校验员工薪资 调整后的工资一定要 大于 薪资调整前的工资
-- 触发器:  before update on emp
-- 行级触发器
create or replace trigger tri_checksal
before
update
on emp
for each row
declare

begin
  -- 调整后的工资 <= 薪资调整前的工资 ,则中断更新操作
  -- :new.sal    <= :old.sal
  if :new.sal <= :old.sal then
     raise_application_error(-20002,'坑爹的,降薪啦!');
  end if;
end;

update emp set sal=sal-100;

/*
     使用触发器模拟类似auto_increment功能
     当用户插入的时候,若为sid为null,则给sid赋值一个编号
*/
create table stu(
     sid number primary key,
     name varchar2(20)
);

-- 创建一个序列
create sequence seq_stu;

-- 触发器: before insert on stu
-- 行级触发器
create or replace trigger tri_auto
before
insert 
on stu
for each row
declare

begin
   -- 从序列中查询一个数字出来,赋值给sid
   select seq_stu.nextval into :new.sid from dual;
end;

-- 同样一张表,有时候自己指定id, 有时候需要数据库自动生成id
insert into stu values(null,'zs');
insert into stu values(4,'zs');
select * from stu;