Oracle基础6-CSDN博客

本文链接：https://blog.csdn.net/Ashenlex/article/details/138587606

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
addr_name varchar2(30);
begin
select name into addr_name from T_ADDRESS where id=addr_id;
return addr_name;
end;
-- todo 1.3 获取业主id 业主姓名业主地址名称
-- sql的方式(不使用存储函数)
select
T_OWNERS.id,
T_OWNERS.NAME,
T_ADDRESS.NAME
from
T_OWNERS inner join T_ADDRESS
on T_OWNERS.ADDRESSID=T_ADDRESS.id;
-- 使用存储函数
select T_OWNERS.id,T_OWNERS.name,fn_getAddressByID(T_OWNERS.ADDRESSID)
from T_OWNERS;

-- 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('123') from dual;

2 存储过程(传入参数)
-- todo 需求: 使用存储过程完成对t_owners表的数据插入
-- todo 2.1 准备工作(t_owners有主键) 创建序列 seq_owners 起始值100 生成主键值
create sequence seq_owners start with 100 increment by 1;
-- todo 2.2 创建传入参数的存储过程 pro_owners_add 添加业主信息 create procedure
-- 注意!!!:参数只能写类型不能写大小
create or replace procedure pro_owners_add(
-- 参数: v_name in, v_addressid in, v_housenumber in, v_watermeter in, v_ownertypeid in
v_name varchar2,
v_addressid number,
v_housenumber varchar2,
v_watermeter varchar2,
-- ADDDATE 为插入时间不需要用户设定直接指定为插入数据时间即可
v_ownertypeid number
)
is
begin
-- todo 2.3 向t_owners插入数据
insert into 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('盘丝洞01',66,'66','66',66);
-- todo 2.6 练习
-- 创建删除指定id的pro_owners_del存储过程
-- 要求 t_owners表传入参数
create or replace procedure pro_owners_del(v_id in number)
is
begin
-- 根据v_id删除数据
delete from T_OWNERS where id=v_id;
-- 提交数据
commit;
end;
--
call PRO_OWNERS_DEL(100);

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 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_id number;
begin
pro_owners_add_2(
'盘丝洞02',
77,
'77',
'77',
77,
v_id
);
DBMS_OUTPUT.PUT_LINE('新插入的数据id为:'||v_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 number, v_name out varchar2)
is
begin
select name into v_name from T_OWNERS where id=v_id;
delete from T_OWNERS where id=v_id;
commit;
end;

declare
v_name varchar2(30);
begin
pro_owners_del_2(103,v_name);
DBMS_OUTPUT.PUT_LINE(v_name);
end;

-- 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 T_PRICETABLE.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;
end if;
v_money := v_money + (i.MAXNUM - i.MINNUM) * i.PRICE;
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 T_PRICETABLE.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;
end if;
v_money := v_money + (i.MAXNUM - i.MINNUM) * i.PRICE;
end loop;
-- 4. 输出金额
return v_money;
end;
-- 5. 调用函数 select 函数名()
select fn_calmoney(1,12) from dual;

-- todo 需求3: 用存储过程pro_owner_account完成需求 ==> 在新增用户的同时新增账目记录
-- 问题:
-- 1. 两个表的id需要序列
-- 2. 向t_owners表插入数据的时候插入的时间为时间类型 ==> t_account需要year month
-- 3. 向t_owners表插入数据的时候插入的是addressid(地址id) ==> t_account需要areaid(地区id)

-- todo 1 创建序列
create sequence SEQ_OWNERS start with 1000 increment by 100;
create sequence SEQ_ACCOUNT start with 1000 increment by 100;
-- todo 2 创建存储过程pro_owner_account
create or replace procedure pro_owner_account(
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
v_year char(4);
v_month char(2);
v_area_id number;
v_owner_id number;
begin
-- todo 4 给变量v_year v_month赋值
v_year := to_char(sysdate,'yyyy');
v_month := to_char(sysdate,'mm');
-- todo 5 给变量v_area_id赋值
select distinct AREAID into v_area_id from
T_OWNERS inner join T_ADDRESS
on T_OWNERS.ADDRESSID=T_ADDRESS.ID where T_OWNERS.ADDRESSID=1;
-- 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 T_ACCOUNT
values(SEQ_ACCOUNT.nextval,v_owner_id,v_OWNERTYPEID,v_area_id,v_year,v_month,0,0,0,null,null,null,null,null,null);
-- todo 9 提交
commit;
end;