15 PL-SQL

最新推荐文章于 2024-02-19 10:34:37 发布

crayon_ycj

最新推荐文章于 2024-02-19 10:34:37 发布

阅读量354

点赞数

分类专栏： Oracle教程文章标签： sql 数据库 oracle

本文链接：https://blog.csdn.net/miudashu/article/details/128132087

版权

Oracle教程专栏收录该内容

16 篇文章 0 订阅

订阅专栏

什么是 PL/SQL

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

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

变量

声明变量的语法:

变量名 类型(长度);

变量赋值的语法:

变量名:=变量值

变量的声明
需求:
声明变量水费单价、水费字数、吨数、金额。
对水费单价、字数、进行赋值。吨数根据水费字数换算，规则为水费字数除以 1000，并且四舍五入，保留两位小数。计算金额，金额=单价*吨数。
输出单价、数量和金额。

declare
v_price number(10, 2); --水费单价
v_usenum number; --水费字数
v_usenum2 number(10, 2); --吨数
v_money number(10, 2); --金额

begin
    v_price:=2.45; --水费单价
    v_usenum:=8012; --字数
    --字数换为吨数
    v_usenum2 := round(v_usenum / 2, 2);
    --计算金额
    v_money := round(v_price * v_usenum2, 2);
    dbms_output.put_line('单价：' || v_price || '吨数:' || v_usenum2 || '金额：' || v_money);
end;

Select into 方式赋值
语法结构:

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

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

属性类型

%TYPE 引用型
作用:引用某表某列的字段类型

declare
    v_price   number(10, 2) ;--     单价
    v_usenum  t_account.usenum%TYPE;--水费字数
    v_num0    t_account.num0%TYPE;--     上月字数
    v_num1    t_account.num1%TYPE;-- 本月字数
    v_usenum2 number(10, 2);--     使用吨数
    v_money   number(10, 2); --水费金额
begin
    --对单价进行赋值
    v_price := 3.45;
    select usenum, num0, num1
    into v_usenum, V_num0, v_num1
    from t_account
    where YEAR = '2012'
      and MONTH = '01'
      and OWNERUUID = 1;

    --使用吨数
    v_usenum2 := round(v_usenum / 1000, 2);

    --计算金额
    v_money := v_price * v_usenum2;
    DBMS_OUTPUT.put_line('单价:' || v_price || '吨数:' || v_usenum2 || '金额:' || v_money || '上月字数:' || v_num0 || '本月字数' ||
                         v_num1);
end;

%ROWTYPE 记录型，上例中的例子可以用下面的代码代替
作用: 标识某个表的行记录类型

declare
    v_price   number(10, 2) ;--     单价
--     v_usenum  t_account.usenum%TYPE;--水费字数
--     v_num0    t_account.num0%TYPE;--     上月字数
--     v_num1    t_account.num1%TYPE;-- 本月字数
    v_account t_account%ROWTYPE; --代替上面三个
    v_usenum2 number(10, 2);--     使用吨数
    v_money   number(10, 2); --水费金额
begin
    --对单价进行赋值
    v_price := 3.45;
    select *
    --into v_usenum, V_num0, v_num1
    into v_account --代替上面一行
    from t_account
    where YEAR = '2012'
      and MONTH = '01'
      and OWNERUUID = 1;

    --使用吨数
    v_usenum2 := round(v_account.usenum / 1000, 2);

    --计算金额
    v_money := v_price * v_usenum2;
    DBMS_OUTPUT.put_line('单价:' || v_price || '吨数:' || v_usenum2 || '金额:' || v_money || '上月字数:' || v_account.NUM0 || '本月字数' ||
                         v_account.NUM1);
end;

异常

在运行程序时出现的错误叫做异常发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分
异常有两种类型:
预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发

Oracle 预定义异常 21 个
用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发
语法结构:

exception
 when 异常类型 then
	异常处理逻辑

根据上例中的代码，添加异常处理部分

declare
    v_price   number(10, 2) ;--     单价
--     v_usenum  t_account.usenum%TYPE;--水费字数
--     v_num0    t_account.num0%TYPE;--     上月字数
--     v_num1    t_account.num1%TYPE;-- 本月字数
    v_account t_account%ROWTYPE; --代替上面三个
    v_usenum2 number(10, 2);--     使用吨数
    v_money   number(10, 2); --水费金额
begin
    --对单价进行赋值
    v_price := 3.45;
    select *
    --into v_usenum, V_num0, v_num1
    into v_account --代替上面一行
    from t_account
    where YEAR = '2012'
      and MONTH = '01'
      and OWNERUUID = 1;

    --使用吨数
    v_usenum2 := round(v_account.usenum / 1000, 2);

    --计算金额
    v_money := v_price * v_usenum2;
    DBMS_OUTPUT.put_line('单价:' || v_price || '吨数:' || v_usenum2 || '金额:' || v_money || '上月字数:' || v_account.NUM0 || '本月字数' ||
                         v_account.NUM1);

    exception
    when NO_DATA_FOUND then
        dbms_output.put_line('未找到数据,请核实');
    when TOO_MANY_ROWS then
        dbms_output.put_line('查询条件有误，返回多条信息,请核实');
end;

条件判断

基本语法 1

if 条件 then
 业务逻辑
end if;

基本语法 2

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

基本语法 3

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

需求:设置三个等级的水费 5 吨以下 2.45 元/吨 5 吨到 10 吨部分 3.45 元/吨，超过 10 吨部分 4.45 ，根据使用水费的量来计算阶梯水费。

--条件判断
declare
    v_price1  number(10, 2); --单价
    v_price2  number(10, 2);
    v_price3  number(10, 2);
    v_usenum2 number(10, 2); --吨数
    v_money   number(10, 2); --金额
    v_account t_account%rowtype;

begin
    v_price1 := 2.45; --5吨以下
    v_price2 := 3.45; --(5-10吨)
    v_price3 := 4.45;
    --10吨以上

    --从数据库中提取
    select * into v_account from t_account where year = '2012' and month = '01' and owneruuid = 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_account.USENUM || '金额：' || v_money);

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

循环

1.无条件循环
语法结构

loop
 --循环语句
end loop;

范例:输出从1开始的100个数

declare
    v_num number := 1;
begin
    loop
        DBMS_OUTPUT.PUT_LINE(v_num);
        v_num := v_num + 1;
        exit when v_num > 100;
    end loop;
end;

2、条件循环
语法结构

while 条件
loop
end loop;

范例:输出从1开始的100个数

--有条件循环：1 到100
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;

3、for循环
基本语法

for 变量 in 起始值..终止值
loop
end loop;

范例:输出从1开始的100个数

declare
    v_num number := 1;
begin
    for v_num in 1..100
    loop
            DBMS_OUTPUT.PUT_LINE(v_num);
    end loop;
end;

游标

游标是系统为用户开设的一个数据缓冲区,存放 SQL 语句的执行结果。我们
可以把游标理解为 PL/SQL 中的结果集。
语法结构及示例
在声明区声明游标，语法如下:

cursor 游标名称 is SQL 语句;

使用游标语法

open 游标名称
loop
	fetch 游标名称 into 变量
	exit when 游标名称%notfound
end loop;
close 游标名称

需求:打印业主类型为 1 的价格表

declare
    v_pricetable t_pricetable%rowtype; --价格行对象
    cursor cur_pricetable is select * from t_pricetable where ownertypeid=1; --定义游标
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;