-- 创建存储函数 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);