Oracle基础5

1. 变量的基本用法(根据提示 计算水费)
declare
-- 声明变量水费单价 v_price、水费字数 v_usenum、吨数 v_usenum2、金额 v_money。
    v_price number(5,2);
    v_usenum number;
    v_usenum2 number(5,2);
    v_money number(5,2);
begin
-- 对水费单价=2.24、字数=8012、进行赋值
    v_price := 2.24;
    v_usenum := 8012;
-- 吨数根据水费字数换算，规则为水费字数除以1000，并且四舍五入，保留两位小数。
    v_usenum2 := round(v_usenum / 1000,2);
-- 计算金额，金额=单价*吨数。
    v_money := v_price * v_usenum2;
-- 输出单价 、吨数和金额。
-- DBMS_OUTPUT.PUT_LINE('')
    DBMS_OUTPUT.PUT_LINE('单价:'||v_price||'吨数:'||v_usenum2||'金额'||v_money);
end;
-- todo 1.1 练习
-- 题目: 小王在超市购买
-- 苹果 5斤 6元/斤
-- 香蕉 2斤 10元/斤

-- 请以plsql完成 完整显示如下
-- 苹果-5斤-6元/斤
-- 香蕉-2斤-10元/斤
-- 总金额: 50元
declare
    name1 varchar2(30);
    name2 varchar2(30);
    weight1 number;
    weight2 number;
    price1 number;
    price2 number;
    money number;
begin
    name1 := '苹果';
    name2 := '香蕉';

    weight1 := 5;
    weight2 := 2;

    price1 := 6;
    price2 := 10;

    money := weight1*price1 + weight2*price2;

    DBMS_OUTPUT.PUT_LINE('苹果-'||weight1||'斤-'||price1||'元/斤');
    DBMS_OUTPUT.PUT_LINE('香蕉-'||weight2||'斤-'||price2||'元/斤');
    DBMS_OUTPUT.PUT_LINE('总金额:'||money||'元');
end;

2.select into 方式 赋值
declare
--声明变量: v_price单价,v_usenum水费字数,v_num0上月字数,v_num1本月字数
--        v_usenum2 使用吨数, v_money 水费金额
    v_price number(5,2);
    v_usenum number;
    v_num0 number;
    v_num1 number;
    v_usenum2 number(5,2);
    v_money number(5,2);
begin
--单价赋值=3.45
    v_price := 3.45;
--使用 select 表字段 into 变量 from 表(注意只能有一行数据)
--给 水费字数, 上月字数, 本月字数 赋值
    select usenum,num0,num1 into v_usenum,v_num0,v_num1
    from T_ACCOUNT where OWNERUUID=1 and YEAR=2012 and MONTH='01';
--获取使用水量
    v_usenum := v_num1 - v_num0;
--求吨数
    v_usenum2 := round(v_usenum/1000,2);
--计算应付金额 = 吨数 * 单价
    v_money :=  v_usenum2 * v_price;
--输出 单价 吨数 应付金额 上月字数 本月字数
    DBMS_OUTPUT.PUT_LINE('xx'||v_price||'xx'||v_usenum2||'xx'||v_money);
end;
-- todo 2.1 练习
-- 从t_area表中获取获取区域数量并显示
-- 1. my_count存储区域数量
-- 2. 最终展示效果如下
--    区域数量: 6
declare
    my_count number;
begin
    select count(NAME) into my_count from T_AREA;
    DBMS_OUTPUT.PUT_LINE('区域数量:'||my_count);
end;

3.引用变量 改造上面的代码
declare
    -- todo 1 声明变量: v_usenum 水费字数,v_num0 上月字数,v_num1 本月字数,
    --  v_price 单价,v_usenum2 使用吨数,v_money 水费金额
    v_price number(10,2);
    v_usenum2 number(10,2);
    v_money number(10,2);
    v_usenum t_account.usenum%type;
    v_num0 t_account.num0%type;
    v_num1 t_account.num1%type;
begin
    -- todo 2 单价赋值=3.45
    v_price := 3.45;
    -- todo 3 使用 select into 给 水费字数, 上月字数, 本月字数 赋值
    select NUM0,NUM1,USENUM into v_num0,v_num1,v_usenum
    from T_ACCOUNT
    where MONTH='01' and OWNERUUID=1 and YEAR='2012';
    -- todo 4 求吨数
    v_usenum2 := round(v_usenum/1000,2);
    -- todo 5 计算应付金额 = 吨数 * 单价
    v_money := v_usenum2 * v_price;
    -- todo 6 输出 单价 吨数 应付金额 上月字数 本月字数
    DBMS_OUTPUT.PUT_LINE('单价:'||v_price||', 吨数:'||v_usenum2||', 应付金额:'||v_money);
end;

4. 目标: 引用变量 改造上面的代码
declare
    -- todo 1 声明变量: v_price 单价, v_usenum 水费字数,  v_num0 上月字数, v_num1  本月字数, v_usenum2 使用吨数, v_money 水费金额
    v_price number(10,2);
    v_usenum2 number(10,2);
    v_money number(10,2);
    v_account T_ACCOUNT%rowtype;
begin
    -- todo 2 单价赋值=3.45
    v_price := 3.45;
    -- todo 3 使用 select into 给 水费字数, 上月字数, 本月字数 赋值
    select * into v_account
    from T_ACCOUNT
    where MONTH='01' and OWNERUUID=1 and YEAR='2012';
    -- todo 4 求吨数
    v_usenum2 := round(v_account.USENUM/1000,2);
    -- todo 5 计算应付金额 = 吨数 * 单价
    v_money := v_usenum2 * v_price;
    -- todo 6 输出 单价 吨数 应付金额 上月字数 本月字数
    DBMS_OUTPUT.PUT_LINE('单价:'||v_price||', 吨数:'||v_usenum2||', 应付金额:'||v_money);
end;
 

5 异常处理
-- 异常处理的语法
-- exception
--     when 错误类型 then
--         处理异常;

declare
    -- 声明变量: v_price 单价, v_account 行变量, v_usenum2 使用吨数, v_money 水费金额
    v_price number(10, 2);
    v_account t_account%rowtype;
    v_usenum2 number(10, 2);
    v_money number(10, 2);
begin
    -- 单价赋值=3.45
    v_price := 3.45;
    -- 给行变量赋值
    select * into v_account from T_ACCOUNT
    -- 正常
    where OWNERUUID=1 and YEAR=2012 and month='01';
    -- 模拟异常1: 数据多了
    -- where YEAR=2012;
    -- 模拟异常2: 数据没找到
    --  where OWNERUUID=1000;
    -- 求吨数
    v_usenum2 := round(v_account.USENUM/1000,2);
    -- 计算应付金额 = 吨数 * 单价
    v_money := v_price * v_usenum2;
    -- 输出 单价 吨数 应付金额 上月字数 本月字数
    DBMS_OUTPUT.PUT_LINE('单价: ' || v_price || ', 吨数: ' || v_usenum2 || ', 应付金额: ' || v_money);

    -- 处理异常 exception
    exception
    -- 处理 未找到数据异常 no_data_found when then
        when no_data_found then
            DBMS_OUTPUT.PUT_LINE('未找到数据!!!');
    -- 处理 查询条件有误, 返回多条数据异常 too_many_rows
        when too_many_rows then
            DBMS_OUTPUT.PUT_LINE('数据太多了!!!');
    -- 其他异常 others
    --  sqlcode错误代码
    --  sqlerrm错误信息
        when others then
            DBMS_OUTPUT.PUT_LINE('错误类型:'||sqlcode||sqlerrm);

end;

6. 目标: 使用判断if计算阶梯水费
-- todo 业务: 设置三个等级的水费
--  5 吨以下           2.45  元/吨,
--  5 吨到 10 吨部分   3.45  元/吨,
--  超过 10 吨部分     4.45  元/吨,
--  根据使用水费的量来计算阶梯水费。

declare
    -- todo 1 声明变量:
    -- v_price1 <5吨单价,
    v_price1 number(10,2);
    -- v_price2 [5,10)吨单价,
    v_price2 number(10,2);
    -- v_price3 >=10吨单价,
    v_price3 number(10,2);
    -- v_account 行变量,
    v_account t_account%rowtype;
    -- v_usenum2 使用吨数,
    v_usenum2 number(10,2);
    -- v_money 水费金额
    v_money number(10,2);
begin
    -- 单价赋值: <5吨单价=2.45,  [5,10)吨单价=3.45, >=10吨单价=4.45
    v_price1 := 2.45;
    v_price2 := 3.45;
    v_price3 := 4.45;
    -- 使用 select into 给 行变量 赋值
    select * into v_account from t_account
    where owneruuid=1 and year='2012' and month='01';
    -- 求吨数
    v_usenum2 := round(v_account.usenum/1000,2);
    -- 计算阶梯水费
    if v_usenum2 < 5 then
        v_money := v_usenum2 * v_price1;
    elsif v_usenum2 >= 5 and v_usenum2 < 10 then
        v_money := (5 * v_price1) + (v_usenum2-5) * v_price2;
    else
        v_money := (5 * v_price1) + (5*v_price2) + (v_usenum2-10) * v_price3;
    end if;
    -- 输出 吨数 应付金额
    DBMS_OUTPUT.PUT_LINE('吨数:'||v_usenum2||',金额:'||v_money);
end;
 

7. 循环
-- 语法:
-- loop
-- 业务逻辑
-- end loop;

-- 使用 loop 输出 1 ~ 100
declare
-- 定义变量 v_num
    v_num number;
begin
-- 赋值 v_num等于1
    v_num := 1;
-- 编写 loop 循环
    loop
-- 进入循环, 输出 v_num变量的值
        DBMS_OUTPUT.PUT_LINE('数字:'||v_num);
-- 变量 v_num 加 1
        v_num := v_num + 1;
-- 当 exit when v_num>100时退出
        exit when v_num>100;
    end loop;

end;

8.while循环
-- 使用 while 输出 1 ~ 100
declare
    -- todo 1 初始化变量 v_num等于1
    v_num number := 1;
begin
    -- todo 2 编写 while 循环, 指定继续执行条件 v_num<=100
    while v_num<=100 loop
        -- todo 2.1 进入循环, 输出 v_num变量的值
        DBMS_OUTPUT.PUT_LINE('数值为:'||v_num);
        -- todo 2.2 变量 v_num 加 1
        v_num := v_num + 1;
        end loop;
end; 

9. 

for循环
-- 语法:
-- for 变量  in 起始值..终止值
-- loop
--   业务逻辑
-- end loop;

-- todo 9.2 练习
-- 使用 for 输出 1 ~ 100
declare
begin
    for num in 1..100 loop
        DBMS_OUTPUT.PUT_LINE('数值为:'||num);
    end loop;
end;
-- todo 9.1 练习
-- 使用 for 输出 1 ~ 100之间的偶数
declare
begin
    for num in 1..100 loop
        if mod(num,2) = 0 then
            DBMS_OUTPUT.PUT_LINE('数值为:'||num);
            end if;
        end loop;
end;

 

10.不带参数的游标
-- 创建游标语法:
-- cursor  游标名称  is   SQL语句;
-- 使用游标语法:
-- open 游标名称
-- loop
--   业务逻辑
-- fetch 游标名称  into 变量
-- exit   when   游标名称%notfound
--   业务逻辑
-- end loop;
-- close 游标名称

-- todo 需求：打印业主类型为 1 的价格表 显示如下
-- 价格: 2.45, 吨位: 0--5
-- 价格: 3.45, 吨位: 5--10
-- 价格: 4.45, 吨位: 10--
declare
-- todo 1 声明行变量 v_pricetable
    v_pricetable T_PRICETABLE%rowtype;
-- todo 2 声明游标 cur_pricetable 保存业主类型为 1 的价格表
    cursor cur_pricetable is
        select * from T_PRICETABLE where OWNERTYPEID=1;
begin
-- todo 3 打开游标
    open cur_pricetable;
-- todo 4 loop 循环
    loop
-- todo 5 fetch 游标 into 变量 提取游标到变量
        fetch cur_pricetable into v_pricetable;
-- todo 6 输出 价格: XX, 吨位: YY-ZZ
        DBMS_OUTPUT.PUT_LINE(
            '价格:'||v_pricetable.PRICE||', 吨位:'||v_pricetable.MINNUM||'--'||v_pricetable.MAXNUM
            );
-- todo 7 当游标到最后一行下面退出循环 exit when 游标%notfound;
        exit when cur_pricetable%notfound;
    end loop;
-- todo 8 关闭游标
    close cur_pricetable;
end;

11. 带参数的游标
-- todo 需求：打印业主类型为 (参数) 的价格表 显示如下
-- 价格: 2.45, 吨位: 0--5
-- 价格: 3.45, 吨位: 5--10
-- 价格: 4.45, 吨位: 10--

declare
-- todo 1 声明行变量 v_pricetable
    v_pricetable T_PRICETABLE%rowtype;
-- todo 2 声明有参数的游标 cur_pricetable(变量名 类型) 保存业主类型为 1 的价格表 <cursor 游标名称 is SQL语句;>
    cursor cur_pricetable(v_type number) is
    select * from T_PRICETABLE where OWNERTYPEID=v_type;
begin
-- todo 3 打开游标(传入参数)
    open cur_pricetable(1);
-- todo 4 loop 循环
    loop
-- todo 5 fetch 游标 into 变量 提取游标到变量
        fetch cur_pricetable into v_pricetable;
-- todo 7 当游标到最后一行下面退出循环
--  exit when 游标%notfound;
        exit when cur_pricetable%notfound;
-- todo 6 输出 价格: XX, 吨位: YY-ZZ
        DBMS_OUTPUT.PUT_LINE(
            '价格:'||v_pricetable.PRICE||', 吨位:'||v_pricetable.MINNUM||'--'||v_pricetable.MAXNUM
            );
    end loop;
-- todo 8 关闭游标
    close cur_pricetable;
end;

12.游标的简化使用
-- todo 需求：使用for 打印根据参数值显示指定业主类型 的 价格表
declare
-- todo 1 声明带参数游标 cur_pricetable 根据参数值获取指定类型的价格表
    cursor cur_pricetable(v_type number) is
    select * from T_PRICETABLE where OWNERTYPEID=v_type;
begin
-- todo 2 使用 for 循环遍历
    for i in cur_pricetable(1) loop
-- todo 2.1 打印 价格: XX, 吨位: YY-ZZ
        DBMS_OUTPUT.PUT_LINE('价格:'||i.PRICE||', 吨位:'||i.MINNUM||'--'||i.MAXNUM);
        end loop;
end;