Oracle基础部分四(PL/SQL、变量、赋值、异常处理、条件判断、循环、游标、存储函数、存储过程、触发器)

1 PL/SQL

1.1 概述

PL/SQL( Procedure Language/SQL)是Oracle 对sql语言的过程化扩展,指在SQL命令语言中增加了
过程处理语句(如分支、循环等),使SQL语言具有过程处理能力。把SQL 语言的数据操纵能力与过
程语言的数据处理能力结合起来,使得 PLSQL面向过程但比过程语言简单、高效、灵活和实用。

1.2 语法结构

[declare
   -- 声明变量
]
begin
  -- 代码逻辑
[exception
  -- 异常处理
] 
end;

2 变量

2.1 变量声明

2.1.1 语法

declare 变量名 类型(长度);

2.1.2 变量类型

变量的类型除了可以是Oracle常规的数据类型,如(char、varchar2、number),还可以进行引用型或记录型
引用型
语法:

表名.列明%type

示例:

declare
v_num0 t_account.num0%type; -- 上月水表数
v_num1 t_account.num1%type; -- 本月水表数

记录型
语法:

表名%rowtype

示例:

declare
v_account t_account%rowtype; --台账行记录类型

2.2 变量赋值

2.2.1 变量赋值语句

语法:

变量名 := 变量值

示例:

v_price := 2.45;

2.2.1 select into 赋值语句

语法:

select 列明 into 变量名 from 表名 where 条件

示例:

select usenum,num0,num1 into v_usenum,v_num0,v_num1 from t_account
  where year = '2012' and month = '01' and id = 1;

**注意:**结果必须是一条记录,有多条记录和没有记录都会报错

3 异常处理

3.1 概述

在设计或运行一个程序时,常出现这样或那样的错误。针对这类错误,采取相应的措施。这就是异常处理(EXCEPTION)
异常处理就是针对系统中发生的各种错误所采取的处理措施,也叫例外

3.2 异常分类

异常描述处理方向
系统预定义的异常在PL/SQL中经常出现的25个系统定义错误在PL/SQL中经常出现的25个系统定义错误
非预定义的系统异常任何其他(25个以外的)服务器产生的标准错误必须在说明部分定义,并且允许服务器隐式地触发它们
用户自定义的异常开发者认为是非正确的一个条件必须在说明部分定义,并且显示地触发它们

3.3 语法


BEGIN;
EXCEPTION
  WHEN 异常情况 1[ OR 异常情况 2] THEN
     …;
  WHEN 异常情况 3[ OR 异常情况 4] THEN
    …;
  WHEN OTHERS THEN
…;
END

3.4 示例

3.4.1 系统预定义的异常示例

declare
   v_price number(10,2); --单价

   v_usenum2 number(10,2); -- 吨数
   v_money number(10,2); -- 金额
   
   v_account t_account%rowtype; --台账行记录类型
begin
  v_price := 2.45; --单价赋值
  -- v_usenum := 9213; --水费字数
  select * into v_account from t_account
  where year = '2012' and month = '01' ;
  
  v_usenum2 := round(v_account.usenum/1000,2); --吨数
  v_money := v_price * v_usenum2; --金额
  
  DBMS_OUTPUT.PUT_LINE('水费字数:'|| v_account.usenum ||' 金额:' || v_money);

exception
  when too_many_rows then
    DBMS_OUTPUT.PUT_LINE('返回多行账单数据');
  when no_data_found then
    DBMS_OUTPUT.PUT_LINE('没有找到账单数据');
end;

3.4.2 非系统预定义的异常示例

要使用非预定义的Oracle异常,则用户必须首先进行定义。步骤如下:
(1)在PL/SQL块的declare定义部分定义异常名:
<exception_name> EXCEPTION;
(2)使用pragma exception_init语句,将其定义好的异常名与标准的Oracle错误编号联系起来:
pragma exception_init(<exception_name >, <handle_code>);
(3)在PL/SQL块异常的处理部分对异常情况做出相应的处理。

DECLARE
    Fk_delete  EXCEPTION;
    PRAGMA EXCEPTION_INIT (Fk_delete ,-2292);
BEGIN
    DELETE products WHERE productid=1;              
 EXCEPTION
WHEN Fk_delete   THEN
       dbms_output.put_line(‘在另一个表中引用了该记录’);
WHEN others  THEN
       dbms_output.put_line(‘其它错误’||sqlcode);
      END;

3.4.3 用户自定义的异常

步骤:
1、定义异常处理:declare 异常名 exception
2、触发异常:raise 异常名
3、处理异常:exception when 异常名1 then 异常处理语句段1; when 异常名2 then 异常处理语句段2

DECLARE
    prod_excep EXCEPTION;
BEGIN
   UPDATE products SET Discontinued=1WHERE unitprice<=3;
   IF sql%notfound THEN RAISE prod_excep;
   ELSE
     dbms_output.put_line(‘更新了’||sql%rowcount||’条记录’);
   END IF;
EXCEPTION
   WHEN prod_excep THEN
       dbms_output.put_line(‘未找到相应产品’);
END;  

4 条件判断

4.1 单分支

if 条件 then
  业务逻辑
end if;

4.2 两个条件分支

if 条件 then 
  业务逻辑
else
  业务逻辑
end if;

4.3 多条件分支

if 条件 then 
  业务逻辑
elsif 条件 then
  业务逻辑
else
  业务逻辑
end if;

4.4条件判断综合应用

declare
    -- 定义变量
    v_price1 number(10,2); -- 不超过5吨的价格
    v_price2 number(10,2); -- 不超过10吨的价格
    v_price3 number(10,2); -- 超过10吨的价格
    v_account t_account%rowtype; --台账行记录类型
    v_usenum2 number(10,2); -- 吨数
    v_money number(10,2); -- 金额
begin   
    -- 变量赋值
    v_price1 := 2.45;
    v_price2 := 3.45;
    v_price3 := 4.45;
    
    
    -- 业务逻辑
    select * into v_account from t_account
    where year = '2012' and month = '01' and id = 1;
    
    v_usenum2 := round(v_account.usenum/1000,2); --吨数
    
    -- 阶梯水费计算
    if v_usenum2 <= 5 then
       v_money := v_price1 * v_usenum2;
    elsif v_usenum2 >5 and v_usenum2 <= 10  then
       v_money := v_price1*5 + v_price2*(v_usenum2-5);
    else
       v_money := v_price1*5 + v_price2*5 + v_price3*(v_usenum2-10);
    end if;

    -- 输出展示
    DBMS_OUTPUT.put_line('水的吨数是:' || v_usenum2 || ' 水费金额:' || v_money);

exception
  when too_many_rows then
    DBMS_OUTPUT.PUT_LINE('返回多行账单数据');
  when no_data_found then
    DBMS_OUTPUT.PUT_LINE('没有找到账单数据');

end;

5 循环

5.1 无条件循环

语法:

loop
  循环语句
end loop

示例:

declare
   v_num number;
begin
   v_num := 1;
   
   -- 循环
   loop
     dbms_output.put_line(v_num);
     v_num := v_num + 1;
    /* if v_num > 100 then 
        exit;  -- 循环100次退出
     end if;*/
     exit when v_num > 100;  -- 与上面等效
   end loop;
  
end;

5.2 有条件循环

语法:

  while 条件
  loop
    循环语句
  end loop;

示例:

declare 
   v_num number;

begin
  v_num := 1;
  while v_num <=100
  loop
    dbms_output.put_line(v_num);
    v_num := v_num + 1;
  end loop;
end;

5.3 for 循环

语法:

for 循环名 in (循环体) 
loop 
执行循环内容;
end loop;

示例

begin
  for v_num in 1 .. 100
    loop
      dbms_output.put_line(v_num);
    end loop;
end;

6 游标

6.1 概述

游标是系统为用户开设的一个数据缓冲区存放SQL 语的执行结果,我们可以把游标理解为 PVSQL中的结果集。

6.2 语法

-- 声明区声明游标,语法如下:
   cursor 游标名称 is SQL语句;
   
   -- 使用游标
   open 游标名称  -- 打开游标
   
   loop -- 循环
     
     fetch 游标名称 into 变量  -- 取出游标赋值
     
     exit when 游标名称%notfound  --关闭游标
     
   end loop;
     
   close 游标名称

6.3 游标案例

6.3.1 不带参数,输出结果集

declare 
   cursor cur_pricetable is select * from t_pricetable where ownertypeid = 1;  -- 声明游标
   v_pricetable t_pricetable%rowtype;
begin
  open cur_pricetable;  -- 打开游标
  
       loop
         
         fetch cur_pricetable into v_pricetable;  -- 提取游标
         exit when cur_pricetable%notfound;  -- 退出循环游标
         DBMS_OUTPUT.PUT_LINE('价格:' || v_pricetable.price || ' 吨位:' ||
          v_pricetable.minnum || '-' || v_pricetable.maxnum);
       
       end loop;

  close cur_pricetable;  -- 关闭游标
end;

6.3.2 带参数,输出结果集

declare 
   cursor cur_pricetable(v_ownertype number) is select * from t_pricetable where ownertypeid = v_ownertype;  -- 声明游标
   v_pricetable t_pricetable%rowtype;
begin
  open cur_pricetable(2);  -- 打开游标
  
       loop
         
         fetch cur_pricetable into v_pricetable;  -- 提前游标
         exit when cur_pricetable%notfound;  -- 退出循环游标
         DBMS_OUTPUT.PUT_LINE('价格:' || v_pricetable.price || ' 吨位:' ||
         v_pricetable.minnum || '-' || v_pricetable.maxnum);
       
       end loop;

  close cur_pricetable;  -- 关闭游标
end;

6.3.2 for 循环 带参数的游标

declare 
   cursor cur_pricetable(v_ownertype number) is select * from t_pricetable where ownertypeid = v_ownertype;  -- 声明游标
   -- v_pricetable t_pricetable%rowtype;
begin
  for v_pricetable in cur_pricetable(2)  -- for循环自动打开和关闭游标,节省代码
    loop
      DBMS_OUTPUT.PUT_LINE('价格:' || v_pricetable.price || ' 吨位:' ||
          v_pricetable.minnum || '-' || v_pricetable.maxnum); 
    end loop;

end;

7 存储函数

7.1 概述

存储函数:又称为自定义函数。可以接收一个或多个参数,返回一个结果在函数中我们可以使用 P/SQL进行逻辑的处理

7.2 语法

   create [or replace] function 函数名
   (参数名称 参数类型,参数名称 参数类型,...)
   return 结果变量数据类型
   
   is
   
   declare
   -- 声明变量   

   begin
     逻辑部分;
     return 结果变量;
     [exception
        异常处理部分]

   end

7.3 示例

创建存储函数

create or replace function fn_getaddress
(v_id number)
return varchar2
is
   v_name varchar2(30);  
begin
  -- 查询地址表
  select name into v_name from t_address where id = v_id;
  return v_name;
end;

8 存储过程

8.1 概述

存储过程是被命名的 PL/SQL块,存储于数据库中,是数据库对象的一种。应用程序可以调用存储过程,执行相应的逻辑
存储过程与存储函数都可以封装一定的业务逻辑并返回结果,存在区别如
1、存储函数中有返回值,且必须返回;而存储过程没有返回值,可以通过传出参数返回多个值。
2、存储函数可以在 select 语中直接使用,而存储过程不能。过程多数是被应用程序所调用。
3、存储函数一般都是封装一个查询结果,而存储过程一般都封装一段事务代码

8.2 语法

create or replace procedure 存储过程名称
(参数名称 类型,参数名称 类型,...)
is | as
变量声明部分;
begin
  逻辑部分
  [exception
   异常处理部分]
end;

注意:
1、参数只指定类型,不指定长度;
2、过程参数的三种模式:
IN 传入参数(默认)
OUT 传出参数,主要用于返回程序运行结果
IN OUT 传入传出参数

8.3 示例

8.3.1 案例1 不带传出参数的存储过程

create sequence seq_owners1 start with 1;  -- 业主序列

create or replace procedure pro_owners_add 
(
v_name varchar2, --名称
v_addressid number, -- 地址编码
v_housenumber varchar2, -- 门牌号
v_watermeter varchar2, -- 水表号
v_ownertypeid number -- 业主类型
)
 
is

begin
  insert into t_owners values(seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_ownertypeid);
  commit;  -- 别忘记写提交了
end;

8.3.1 案例1 带传出参数存储过程

create or replace procedure pro_owners_add 
(
v_name varchar2, --名称
v_addressid number, -- 地址编码
v_housenumber varchar2, -- 门牌号
v_watermeter varchar2, -- 水表号
v_ownertypeid number, -- 业主类型
v_id out number -- 传出参数
)
 
is

begin
  -- 对传出参数赋值
  select seq_owners1.nextval into v_id from dual;
  -- 新增业主
  insert into t_owners values(v_id,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_ownertypeid);
  commit;
end;

8.4 存储过程的调用

8.4.1 不带参数存储过程的调用

调用方式1:

call pro_owners_add('马大哈',2,'22333','6677',1);

调用方式2:

begin
  pro_owners_add('马小哈',2,'22333','6678',1);
end;

8.4.1 带参数存储过程的调用

只能使用方式2:

declare
v_id number;

begin
  pro_owners_add('马二哈',2,'223323','664378',1,v_id);
  dbms_output.put_line(v_id);
end;

9 触发器

9.1 概述

  数据库触发器是一个与表相关联的、存储的 PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时,Oracle 自动地执行触发器中定义的语句序列
作用
– 数据确认
– 实施复杂的安全性检查
– 做审计,跟踪表上所做的数据操作等
– 数据的备份和同步

触发器的分类
– 前置触发器(BEFORE)
– 后置触发器(AFTER)

9.2 语法

CREATE [OR REPLACE] TRIGGER 触发器名
       BEFORE | AFTER
       [DELETE][[OR] INSERT][[OR] UPDATE [OF 列明]]
       ON 表名
       [FOR EACH ROW][WHEN (条件)]
declare

-- 变量声明

begin
       PLSQL 块
end; 

**解释:**在触发器语句与伪记录变量的值
触发语句 :old :new
Insert 所有字段都是空(nulI) 将要插入的数据
Update 更新以前该行的值 更新后的值
delete 删除以前该行的值 所有字段都是空(null)

9.3 示例

9.3.1 前置触发器案例

常用于提交数据前的触发器操作

create or replace trigger tri_account_num1
before  -- 前置触发器
update of num1 
on t_account
for each row

declare

begin
  -- 通过伪记录变量修改usenum 字段的值
  :new.usenum := :new.num1 - :new.num0;  -- 后置触发器无法修改:new.usenum值
end;

9.3.2 后置触发器案例

后置触发器常用于存储修改后的日志记录

-- 创建日志表,记录业主名称修改前和修改后的名称
create table t_owners_log
(
ownerid number,
oldname varchar2(30),
newname varchar2(30),
updatetime date
)

create or replace trigger tri_owners_log
after
update of name
on t_owners
for each row

declare

begin
  -- 向日志表插入日志就行
  insert into t_owners_log values(:new.id,:old.name,:new.name,sysdate);  -- 不许写commit
end;
阅读终点,创作起航,您可以撰写心得或摘录文章要点写篇博文。去创作
  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

slb190623

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值