目录
1. pl/sql的语法
需求：
-- 声明变量水费单价、水费字数、吨数、金额。对水费单价、字数进行赋值。吨数根据水费字数换算，规则为水费字数 / 1000，并且四舍五入，保留两位小数。
-- 计算金额，金额 = 单价 * 吨数。输出单价、数量和金额
declare
v_price number(10,2); -- 水费单价
v_usenum number; -- 水费字数
v_usenum2 number(10,2); -- 吨数
v_money number(10,2); -- 金额
begin
v_price := 1.50;
v_usenum := 3000;
v_usenum2 := round(v_usenum/1000, 2);
v_money := v_price * v_usenum2;
dbms_output.put_line('单价:'||v_price);
dbms_output.put_line('数量:'||v_usenum2);
dbms_output.put_line('金额:'||v_money);
end;
-- select 列名 into 变量名
declare
v_price number(10,2); -- 水费单价
v_usenum number; -- 水费字数
v_usenum2 number(10,2); -- 吨数
v_money number(10,2); -- 金额
begin
v_price := 1.50;
-- v_usenum := 3000;
select usenum into v_usenum
from t_account
where year = '2022' and month = '04';
v_usenum2 := round(v_usenum/1000, 2);
v_money := v_price * v_usenum2;
dbms_output.put_line('水费字数:'||v_usenum||'金额:'||v_money);
end;
-- 属性类型： 引用变量（表名.列名%type）
declare
v_price number(10,2); -- 水费单价
v_usenum t_account.usenum%type; -- 水费字数
v_usenum2 number(10,2); -- 吨数
v_money number(10,2); -- 金额
begin
v_price := 1.50;
select usenum into v_usenum
from t_account
where year = '2022' and month = '04';
v_usenum2 := round(v_usenum/1000, 2);
v_money := v_price * v_usenum2;
dbms_output.put_line('水费字数:'||v_usenum||'金额:'||v_money);
end;
-- 属性类型： 记录性变量(表名%rowtype)
declare
v_price number(10,2); -- 水费单价
v_usenum2 number(10,2); -- 吨数
v_money number(10,2); -- 金额
v_account t_account%rowtype;
begin
v_price := 1.50;
select * into v_account from t_account
where year = '2022' and month = '04';
v_usenum2 := round(v_account.usenum /1000, 2);
v_money := v_price * v_usenum2;
dbms_output.put_line('水费字数:'||v_account.usenum||'金额:'||v_money);
end;
2. 异常
需求：
-- 声明变量水费单价、水费字数、吨数、金额。对水费单价、字数进行赋值。吨数根据水费字数换算，规则为水费字数 / 1000，并且四舍五入，保留两位小数。
-- 计算金额，金额 = 单价 * 吨数。输出单价、数量和金额
-- 预定义异常
declare
v_price number(10,2); -- 水费单价
v_usenum t_account.usenum%type; -- 水费字数
v_usenum2 number(10,2); -- 吨数
v_money number(10,2); -- 金额
begin
v_price := 1.50;
select usenum into v_usenum
from t_account
where year = '2022' and month = '04' and ownertype_id=10;
v_usenum2 := round(v_usenum/1000, 2);
v_money := v_price * v_usenum2;
dbms_output.put_line('水费字数:'||v_usenum||'金额:'||v_money);
exception
when no_data_found then
dbms_output.put_line('没有找到相关记录...');
end;
-- 自定义异常
3. 条件判断
需求：
-- 设置三个等级的水费5吨以下 2.45/吨 5吨到10吨3.45/吨 超过10吨4.45/吨，根据使用水费的量来计算阶梯水费
declare
v_price1 number(10,2); -- 5吨以下
v_price2 number(10,2); -- 5吨到10吨
v_price3 number(10,2); -- 超过10吨
v_usenum2 number(10,2); -- 吨数
v_money number(10,2); -- 金额
v_account t_account%rowtype;
begin
v_price1 := 2.45;
v_price2 := 3.45;
v_price3 := 4.45;
select * into v_account from t_account
where year = '2022' and month = '04';
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 := 5 * v_price1 + v_price2 * (v_usenum2 - 5);
else
v_money := 5 * v_price1 + 5 * v_price2 + (v_usenum2 - 10) * v_price3;
end if;
-- v_money := v_price * v_usenum2; -- 水费
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;
4. 循环
-- 1） 无条件循环
declare
v_num number;
begin
v_num := 1;
loop
dbms_output.put_line(v_num);
v_num := v_num + 1;
if v_num > 100 then
exit;
end if;
end loop;
end;
-- 有条件循环
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;
-- for 循环
declare
begin
for v_num in 1 .. 100
loop
dbms_output.put_line(v_num);
end loop;
end;
5. 游标
-- 游标：pl/sql查询的每一条数据记录就是输出结果集并存放在数据缓冲区，这个缓冲区类似JAVA中存放数据的集合容器List Map...
需求：
-- 使用游标：打印业主类型为1的价格表。
select * from t_price where ownertype_id = 1;
declare
cursor cur_price is
select * from t_price where ownertype_id = 1; -- 声明游标
v_price t_price%rowtype; -- 声明游标变量名
begin
open cur_price; -- 开启游标
loop
fetch cur_price into v_price; -- 提取游标赋值给游标变量
exit when cur_price%notfound; -- 退出游标
dbms_output.put_line('价格:'||v_price.price||' '||'吨位:'||v_price.minnum||'--'||v_price.maxnum);
end loop;
close cur_price; -- 关闭游标
end;
-- 带参数的游标
-- 使用游标：打印业主类型为1的价格表
declare
cursor cur_price(v_ownertype number) is -- 声明形参 v_ownertype
select * from t_price where ownertype_id = v_ownertype; -- 声明游标
v_price t_price%rowtype; -- 声明游标变量名
begin
open cur_price(2); -- 开启游标 将 1 赋值给 v_ownertype
loop
fetch cur_price into v_price; -- 提取游标赋值给游标变量
exit when cur_price%notfound; -- 退出游标
dbms_output.put_line('价格:'||v_price.price||' '||'吨位:'||v_price.minnum||'--'||v_price.maxnum);
end loop;
close cur_price; -- 关闭游标
end;
-- for循环：带参数的游标
-- 使用游标：打印业主类型为1的价格表
declare
cursor cur_price(v_ownertype number) is -- 声明形参 v_ownertype
select * from t_price where ownertype_id = v_ownertype; -- 声明游标
-- v_price t_price%rowtype; -- 声明游标变量名
begin
for v_price in cur_price(2)
loop
dbms_output.put_line('价格:'||v_price.price||' '||'吨位:'||v_price.minnum||'--'||v_price.maxnum);
end loop;
end;
6. 存储函数 & 存储过程
-- 存储函数：自定义函数通过一组特定功能的sql语句，返回一个数据结果集将这个数据结果集存储到数据库中，在通过指定的存储过程的名称pl/sql进行逻辑处理。
-- 需求：创建存储函数，根据地址id查询地址名称。
-- 创建存储函数
create or replace function fn_addr
(v_id number)
return varchar2
as
-- 声明变量
v_name varchar2(30);
begin
-- 查询地址表的地址名
select name into v_name from t_address where id = v_id;
return v_name;
end;
-- 调用存储函数
select fn_addr(3) from dual;
-- 查询业主id，业主名称，业主地址，业主地址使用存储函数来实现
select id, name, fn_addr(address_id) from t_owners;
-- 存储过程：就是通过PL/SQL处理的SQL语句封装一定的业务逻辑并返回对应的结果
-- 存储函数和存储过程对比：
-- 1.存储过程没有返回值，通过传出参数返回多个值 2.存储过程不能使用select语句多数是被应用程序所调用。3.存储过程一般都封装一段事务代码
需求：创建一个不带传出参数的存储过程：添加业主信息。
-- 添加业主序列
create sequence seq_owers start with 10;
-- 创建一个不带传出参数的存储过程
create or replace procedure pro_owers_add
(
v_name in varchar2, -- 业主名
v_address_id in number, -- 业主地址
v_housenumber in varchar2, -- 业主门牌号
v_watermeter in varchar2, -- 业主水表
v_ownertype_id in number -- 业主类型
)
is
begin
insert into t_owers
values (seq_owners.nextval, v_name, v_address_id, v_housenumber, v_watermeter, sysdate, v_ownertype_id);
commit;
end;
-- 调用不带传出参数的存储过程
call pro_owers_add('大梦', '34111', '776666', 1);
begin
pro_owers_add('小梦', '34555', '77888', 1);
end;
带传出参数的存储过程
-- 需求：添加业主信息，传出参数为新增业主的id
-- 增加业主信息存储过程
create or replace procedure pro_owers_add
(
v_name in varchar2, -- 业主名
v_address_id in number, -- 业主地址
v_housenumber in varchar2, -- 业主门牌号
v_watermeter in varchar2, -- 业主水表
v_ownertype_id in number, -- 业主类型
v_id out number
)
is
begin
-- 对传出参数赋值
select seq_owners.nextval into v_id from dual;
-- 新增业主记录
insert into t_owers
values (v_id, seq_owners.nextval, v_name, v_address_id, v_housenumber, v_watermeter, sysdate, v_ownertype_id);
commit;
end;
-- 调用传出参数的存储过程
declare
v_id number;
begin
pro_owers_add('二梦', '34555', '77888', 1, v_id);
dbms_output.put_line(v_id);
end;7. 触发器
-- 触发器：是一个与表相关联的、存储PL/SQL程序。每当一个特定的数据操作语句（增删改）在指定表发出时，oracle自动执行触发器中定义的语句序列
-- 触发器的应用：数据确认、实施复杂的安全性检查、做审计，跟踪表所做的数据操作、数据备份和同步
-- 分类：前置触发器和后置触发器 ； 行级触发器和语句触发器
-- 伪记录变量： :old 修改之前的数据 :new 修改之后的数据
-- 前置触发器需求：当用户输入本月累计数后，自动计算出本月使用数。
create or replace trigger tri_account_num1
before
update of num1
on t_account
for each row
declare
begin
-- 通过伪记录变量修改usenum的字段值
:new.usenum:=:new.num1-:new.num0;
end;
-- 验证前置触发器
update t_account set num1 = num1 + 500 where id = 9;
commit;
-- 后置触发器需求：当用户修改了业主信息表的数据时记录修改前和修改后的值
oracle学习阶段，如果有小伙伴怕准备资源，可自取：
链接：https://pan.baidu.com/s/1uvS8F-IXjwnCmOARg9urwA?pwd=hcff
提取码：hcff
2894
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
