Oracle今日复习，业务练习

-- todo 1 存储函数

-- 创建存储函数 fn_getAddressByID，根据 地址ID 查询 地址名称
-- fn_getAddressByID(12) ==> 顺义
-- todo 1.1 获取地址名称的sql
select name from T_ADDRESS where ID=1;
-- todo 1.2 创建具有参数的存储函数 并且返回值类型为字符串类型(注意!!这里只写类型不写大小)
--  fn_getAddressByID(addr_id number) return varchar2
create or replace function fn_getAddressByID(addr_id number)
return varchar2
is
    v_name varchar2(100);
begin
    -- 根据参数 获取名字
    select name into v_name from T_ADDRESS where ID=addr_id;
    -- 必须有返回值
    return v_name;
end;

-- todo 1.3 获取业主id 业主姓名 业主地址名称
-- 使用存储函数
select fn_getAddressByID( 12) from dual;

-- todo 1.4 练习
-- todo 要求:
-- 创建函数 fn_num_ou()
-- 具有参数 num ==> 整数类型
-- 返回值 ret ==> 字符串类型
-- 功能: 如果参数num为偶数 返回值ret结果为 是
--      如果参数num为偶数 返回值ret结果为 不是

create or replace function fn_num_ou(num number)
return varchar2
is
    ret varchar2(30);
begin
    if mod(num,2) = 0 then
        ret := '是';
        return ret;
    else
        ret := '不是';
        return ret;
    end if;
end;

select fn_num_ou(11) from dual;

-- todo 2 存储过程(传入参数)
-- todo 需求: 使用存储过程 完成 对t_owners表的数据插入

-- todo 2.1 准备工作(t_owners有主键)  创建序列 seq_owners 起始值100 生成主键值
create sequence seq_owners start with 100;
-- todo 2.2 创建传入参数的存储过程 pro_owners_add 添加业主信息 create procedure
--  注意!!!:参数只能写类型不能写大小
create or replace procedure pro_owners_add(
    v_NAME varchar2,
    v_ADDRESSID number,
    v_HOUSENUMBER varchar2,
    v_WATERMETER varchar2,
    v_OWNERTYPEID number
)
is
begin
-- todo 2.3 向t_owners插入数据
    insert into C##WATERUSER.T_OWNERS
    values (seq_owners.nextval, v_NAME, v_ADDRESSID, v_HOUSENUMBER, v_WATERMETER, sysdate, v_OWNERTYPEID);
-- todo 2.4 提交数据
    commit;
end;
-- todo 2.5 调用存储过程 添加业主信息 call 存储过程(... ...);
call pro_owners_add('蜘蛛精',1,'66','66',1);

-- todo 2.6 练习
-- 创建删除指定id的pro_owners_del存储过程
-- 要求 t_owners表 传入参数
create or replace procedure pro_owners_del(v_id in number)
is
begin
    delete from T_OWNERS where ID=v_id;
    commit;
end;

call pro_owners_del(100);

-- todo 3 存储过程(传出参数)
-- todo 需求:创建带传出参数pro_owners_add_2存储过程：添加业主信息
--  要求: 传出参数 v_id number类型 out 传出参数, 当添加完一条数据后

--  注意!!!: 参数只写类型不写大小
create or replace procedure pro_owners_add_2(
    v_NAME varchar2,
    v_ADDRESSID number,
    v_HOUSENUMBER varchar2,
    v_WATERMETER varchar2,
    v_OWNERTYPEID number,
    v_id out number
)
is
    -- 声明变量
begin
-- todo 3.1 调用序列下一个值 赋值给 v_id
    select seq_owners.nextval into v_id from dual;
-- todo 3.2 插入 insert into
    insert into C##WATERUSER.T_OWNERS
    values (v_id, v_NAME, v_ADDRESSID, v_HOUSENUMBER, v_WATERMETER, sysdate, v_OWNERTYPEID);
-- todo 3.3 提交
    commit;
end;
-- todo 3.4 使用
-- 注意: 这里通过plsql使用 begin end的形式
declare
    v_my_id number;
begin
    pro_owners_add_2('盘丝洞',1,'77','77',1,v_my_id);
    DBMS_OUTPUT.PUT_LINE('插入数据的id为:'||v_my_id);
end;

-- todo 3.5 练习
-- 创建删除指定id的pro_owners_del_2存储过程
-- 要求
-- 1. t_owners表 传入参数v_id in, 传出参数v_name out 字符窜类型
-- 2. 调用pro_owners_del_2时 获取 删除的数据名称name 并显示出来
create or replace procedure pro_owners_del_2(v_id in number,v_name out varchar2)
is
begin
    -- 获取v_name 删除的人的名字
    select name into v_name from T_OWNERS where id=v_id;
    -- 删除数据
    delete from T_OWNERS where ID=v_id;
    commit ;
end;

-- 调用
declare
    v_my_name varchar2(30);
begin
    pro_owners_del_2(101,v_my_name);
    DBMS_OUTPUT.PUT_LINE('删除的人的名字为:'||v_my_name);
end;

-- todo 案例练习
-- todo 需求1: 通过plsql完成阶梯计费
-- 要求: 计算业主类型为1 用水量为12吨 的用户需要付多少水费(T_PRICETABLE)
declare
-- 1. 声明变量 v_owner_type 并赋值为 1
--            v_usernum2 并赋值为 12
--            v_money 并赋值为0
    v_owner_type number := 1;
    v_usernum2 number := 12;
    v_money number(5,2) := 0;
-- 2. 创建具有参数v_type的游标 cur_pricetable 用来获取t_pricetable中所有数据
    cursor cur_pricetable(v_type number) is
    select * from T_PRICETABLE where OWNERTYPEID=v_type
    order by MINNUM;
begin
-- 3. <for loop end loop>方式 使用游标 cur_pricetable
    for i in cur_pricetable(v_owner_type)
    loop
           if v_usernum2 <= i.MAXNUM or i.MAXNUM is null then
               -- 证明就可以退出循环了
                v_money := v_money + (v_usernum2-i.MINNUM) * i.PRICE;
                exit;
           else
               -- 不满足条件
                v_money := v_money + (i.MAXNUM-i.MINNUM) * i.PRICE;
           end if;
    end loop;
-- 4. 输出金额
    DBMS_OUTPUT.PUT_LINE('金额:'||v_money);
end;

-- todo 需求2: 根据需求1创建存储函数 fn_calmoney(v_owner_type业主类型,v_usernum2用水量)
-- todo 注意: !!!返回值,参数只有类型<没有大小>!!!
create or replace function fn_calmoney(v_owner_type number,v_usernum2 number)
return number
is
-- 1. 声明变量 v_money 并赋值为0
    v_money number(5,2) := 0;
-- 2. 创建具有参数v_type的游标 cur_pricetable 用来获取t_pricetable中所有数据
    cursor cur_pricetable(v_type number) is
    select * from T_PRICETABLE where OWNERTYPEID=v_type
    order by MINNUM;
begin
-- 3. <for loop end loop>方式 使用游标 cur_pricetable
    for i in cur_pricetable(v_owner_type)
    loop
           if v_usernum2 <= i.MAXNUM or i.MAXNUM is null then
               -- 证明就可以退出循环了
                v_money := v_money + (v_usernum2-i.MINNUM) * i.PRICE;
                exit;
           else
               -- 不满足条件
                v_money := v_money + (i.MAXNUM-i.MINNUM) * i.PRICE;
           end if;
    end loop;
-- 4. 返回值
    return v_money;
end;

select fn_calmoney(1,20) from dual;

-- todo 需求3: 用存储过程pro_owner_account完成需求 ==> 在新增用户的同时新增账目记录
-- 问题:
-- 1. 两个表的id需要序列
drop sequence seq_owners;
drop sequence seq_account;
create sequence seq_owners start with 1000;
create sequence seq_account start with 1000;
-- 2. 向t_owners表插入数据的时候 插入的时间为 时间类型 ==> t_account需要year month
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'mm') from dual;
-- 3. 向t_owners表插入数据的时候 插入的是addressid(地址id) ==> t_account需要areaid(地区id)
select distinct AREAID from T_ADDRESS inner join T_OWNERS
on T_OWNERS.ADDRESSID = T_ADDRESS.ID where ADDRESSID=3;

-- todo 1 创建序列
-- todo 2 创建存储过程pro_owner_account
create or replace procedure pro_owner_account(
    -- 用来给t_owners表添加数据的
    v_NAME varchar2,
    v_ADDRESSID number,
    v_HOUSENUMBER varchar2,
    v_WATERMETER varchar2,
    v_OWNERTYPEID number
)
is
-- todo 3 声明变量 v_year v_month v_area_id v_owner_id
    -- 这些声明的变量用来给 t_acconut表插入数据
    v_year char(4);
    v_month char(2);
    v_area_id number;
    v_owner_id number;
begin
-- todo 4 给变量v_year v_month赋值
    select to_char(sysdate,'yyyy') into v_year from dual;
    select to_char(sysdate,'mm') into v_month from dual;
-- todo 5 给变量v_area_id赋值
    select distinct AREAID into v_area_id from T_ADDRESS inner join T_OWNERS
    on T_OWNERS.ADDRESSID = T_ADDRESS.ID where ADDRESSID=v_ADDRESSID;
-- todo 6 通过序列保存t_owners的id值
    select seq_owners.nextval into v_owner_id from dual;
-- todo 7 向t_owners表中插入数据
    insert into T_OWNERS
    values(
        v_owner_id,
           v_NAME,
           v_ADDRESSID,
           v_HOUSENUMBER,
           v_WATERMETER,
           sysdate,
           v_OWNERTYPEID
    );
-- todo 8 向t_account表中插入数据
    insert into C##WATERUSER.T_ACCOUNT(ID, OWNERUUID, OWNERTYPE, AREAID, YEAR, MONTH, NUM0, NUM1, USENUM)
    values (seq_account.nextval,v_owner_id,v_OWNERTYPEID,v_area_id,v_year,v_month,0,0,0);
-- todo 9 提交
    commit ;
end;
-- todo 10 调用
call pro_owner_account('火焰山',1,'666','666',2);