Oracle知识学习记录（二）
ORACLE 编程
1. PL/SQL
1.1什么是pl/sql
- 在sql语言处理中增加了过程处理语句
--语法：
[declare
--声明变量
]
begin
--逻辑代码
[exception
--异常处理
]
end;
1.2变量
声明变量的语法：
变量名 类型（长度）;
变量赋值的语法：
变量名:=变量值;
案例：
声明变量水费单价、水费字数、吨数、金额。
对水费单价、字数、进行赋值 。
吨数根据水费字数换算，规则为水费字数除以 1000，并且四舍五入，保留两位小数。
计算金额，金额=单价*吨数。
输出单价 、数量和金额。
declare
--声明变量
v_waterprice number(10,2);--水费单价
v_usenum number;--用水字数
v_usenum2 number(10,2);--用水吨数
v_money number(10,2);--金额
begin
--变量赋值
v_waterprice:=2.45;
v_usenum:=8012;
v_usenum2:=round(v_usenum/1000,2);
v_money:=round(v_waterprice*v_usenum2,2);
--输出
dbms_output.put_line('单价：'||v_waterprice
||' 数量：'||v_usenum2||' 金额：'||v_money);
end;
Select into 方式 赋值:
--语法：
select 列名1,列名2 into 变量名1，变量名2 from 表名 where 条件
注意:结果必须是一条记录 ，有多条记录和没有记录都会报错
--select into
declare
--声明变量
v_waterprice number(10,2);--水费单价
v_usenum number;--用水字数
v_usenum2 number(10,2);--用水吨数
v_money number(10,2);--金额
begin
--变量赋值
v_waterprice:=3.45;
--select into赋值
select t.usenum into v_usenum from t_account t where t.year='2012' and t.month='01' and t.owneruuid='1';
v_usenum2:=round(v_usenum/1000,2);
v_money:=round(v_waterprice*v_usenum2,2);
--输出
dbms_output.put_line('单价：'||v_waterprice
||' 数量：'||v_usenum2||' 金额：'||v_money);
end;
1.3属性类型
1. %TYPE 引用型 ： 引用某表某列的字段类型
--%type 引用型 (表名.字段名%type)
declare
--声明变量
v_waterprice number(10,2);--水费单价
v_usenum t_account.usenum%type;--用水字数
v_usenum2 number(10,2);--用水吨数
v_money number(10,2);--金额
begin
--变量赋值
v_waterprice:=3.45;
--select into赋值
select t.usenum into v_usenum from t_account t where t.year='2012' and t.month='01' and t.owneruuid='1';
v_usenum2:=round(v_usenum/1000,2);
v_money:=round(v_waterprice*v_usenum2,2);
--输出
dbms_output.put_line('单价：'||v_waterprice
||' 数量：'||v_usenum2||' 金额：'||v_money);
end;
2. %ROWTYPE 记录型 ： 标识某个表的行记录类型
--%rowtype 记录型 (表名%rowtype)
declare
--声明变量
v_waterprice number(10,2);--水费单价
v_account t_account%rowtype;--用水字数
v_usenum2 number(10,2);--用水吨数
v_money number(10,2);--金额
begin
--变量赋值
v_waterprice:=3.45;
--select into赋值
select * into v_account from t_account t where t.year='2012' and t.month='01' and t.owneruuid='1';
v_usenum2:=round(v_account.usenum/1000,2);
v_money:=round(v_waterprice*v_usenum2,2);
--输出
dbms_output.put_line('单价：'||v_waterprice
||' 数量：'||v_usenum2||' 金额：'||v_money);
end;
1.4异常
-
发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分
-
异常有两种类型 :预定义异常,用户定义异常
-
预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
NO_DATA_FOUND :使用 select into 未返回行
TOO_MANY_ROWS :执行 select into 时，结果集超过一行
-
用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的 异常通过 RAISE 语句显式引发
--语法：
exception
when 异常类型 then
异常处理逻辑
--异常
declare
--声明变量
v_waterprice number(10, 2); --水费单价
v_account t_account%rowtype; --用水字数
v_usenum2 number(10, 2); --用水吨数
v_money number(10, 2); --金额
begin
--变量赋值
v_waterprice := 3.45;
--select into赋值
select *
into v_account
from t_account t
where t.year = '2012'
and t.month = '01' and t.owneruuid='1';
v_usenum2 := round(v_account.usenum / 1000, 2);
v_money := round(v_waterprice * v_usenum2, 2);
--输出
dbms_output.put_line('单价：' || v_waterprice || ' 数量：' || v_usenum2 ||
' 金额：' || v_money);
exception
when too_many_rows then
dbms_output.put_line('返回了多行记录');
when no_data_found then
dbms_output.put_line('未查到记录');
end;
1.5条件判断
--语法1：
if 条件 then 逻辑
end if;
--语法2：
if 条件 then 逻辑
else
逻辑
end if;
--语法3：
if 条件 then 逻辑
elsif 条件 then 逻辑
else 逻辑
end if;
案例：
设置三个等级的水费 5 吨以下 2.45 元/吨 ，5 吨到 10 吨部分 3.45 元/吨 ， 超过 10 吨部分 4.45 ，根据使用水费的量来计算阶梯水费。
--条件判断：
--设置三个等级的水费 5 吨以下 2.45 元/吨 ，
--5 吨到 10 吨部分 3.45 元/吨 ，
--超过 10 吨部分 4.45 ，
--根据使用水费的量来计算阶梯水费。
declare
v_waterprice1 number(10,2);--单价
v_waterprice2 number(10,2);--单价
v_waterprice3 number(10,2);--单价
v_usenum t_account.usenum%type;
v_usenum2 number(10,2);--吨
v_money number(10,2);--金额
begin
v_waterprice1:=2.45;
v_waterprice2:=3.45;
v_waterprice3:=4.45;
select a.usenum into v_usenum from t_account a where a.year='2012' and a.month='02' and a.owneruuid='1';
v_usenum2:=round(v_usenum/1000,2);
if v_usenum2<=5 then
v_money:=round(v_usenum2*v_waterprice1,2);
elsif v_usenum2<=10 then
v_money:=round((5*v_waterprice1+(v_usenum2-5)*v_waterprice2),2);
else
v_money:=round((5*v_waterprice1+5*v_waterprice2+(v_usenum2-10)*v_waterprice3),2);
end if;
dbms_output.put_line('用水量：'||v_usenum2||' 金额：'||v_money);
end;
1.6 循环
1.6.1无条件循环
--语法：
loop
--循环语句
end loop;
--无条件循环
declare
v_num number;
begin
v_num:=1;
loop
exit when v_num>100;
dbms_output.put_line(v_num);
v_num:=v_num+1;
end loop;
end;
1.6.2有条件循环
--语法：
while 条件
loop
--循环语句
end loop;
--有条件循环
declare
v_num number:=1;
begin
while v_num<=100
loop
dbms_output.put_line(v_num);
v_num:=v_num+1;
end loop;
end ;
1.6.3for循环
--语法：
for 变量 in 起始值..终止值
loop
--循环语句
end loop;
--for循环
begin
for x in 1 .. 100
loop
dbms_output.put_line(x);
end loop;
end;
1.7游标
1.7.1什么是游标
- 游标是系统为用户开设的一个数据缓冲区,存放 SQL 语句的执行结果。
- 我们可以把游标理解为 PL/SQL 中的结果集
1.7.2游标语法结构及示例
-
在声明区声明游标,语法如下
cursor 游标名称 is sql语句; -
使用游标语法
open 游标名称;
loop
fetch 游标名称 into 变量;
exit when 游标名称%notfound;
end loop;
close 游标名称;
打印业主类型为 1 的价格表:
--游标
--打印业主类型为 1 的价格表
declare
v_pricetable t_pricetable%rowtype;--价格行对象
cursor cur_price is select * from t_pricetable a where a.ownertypeid='1';--定义游标
begin
open cur_price;--打开游标
loop
fetch cur_price into v_pricetable;--提取游标到变量
exit when cur_price%notfound;--当游标到最后一行下面退出循环
dbms_output.put_line('价格：'||v_pricetable.price
||' 用电量：' ||v_pricetable.minnum||'-'||v_pricetable.maxnum);
end loop;--结束循环
close cur_price;--关闭游标
end;
1.7.3带参数的游标
- 查询语句的条件值有可能是在运行时才能决定的，比如业主类型， 可能是运行时才可以决定
--带参游标
--打印业主类型为 1 的价格表
declare
v_pricetable t_pricetable%rowtype;--价格行对象
cursor cur_price(v_ownertype t_pricetable.ownertypeid%type) is
select * from t_pricetable a where a.ownertypeid=v_ownertype;--定义游标
begin
open cur_price(1);--打开游标
loop
fetch cur_price into v_pricetable;--提取游标到变量
exit when cur_price%notfound;--当游标到最后一行下面退出循环
dbms_output.put_line('价格：'||v_pricetable.price
||' 用电量：' ||v_pricetable.minnum||'-'||v_pricetable.maxnum);
end loop;--结束循环
close cur_price;--关闭游标
end;
1.7.4for循环提取游标值
for 循环提取游标值 :无需定义变量，无需写打开游标 、关闭游标 、 提取游标 、控制循环的退出
-- for 循环提取游标值
--打印业主类型为 1 的价格表
declare
cursor cur_price(v_ownertype t_pricetable.ownertypeid%type) is
select * from t_pricetable a where a.ownertypeid=v_ownertype;--定义游标
begin
for v_pricetable in cur_price(1)--for 变量名 in 游标名称（参数）
loop
dbms_output.put_line('价格：'||v_pricetable.price
||' 用电量：' ||v_pricetable.minnum||'-'||v_pricetable.maxnum);
end loop;--结束循环
end;
2.存储函数
- 存储函数又称自定义函数，可以接收一个或多个参数，返回一个结果，使用pl/sql写逻辑。
- 存储函数类似于Java里的方法。
存储函数：
create [or replace] function 函数名
（参数名 参数类型，参数名 参数类型...）
return 结果变量数据类型
is
--变量声明部分
begin
--逻辑部分
return 结果;
[exception]
end;
案例：
--存储函数
--需求： 创建存储函数，根据地址 ID 查询地址名称。
create or replace function fun_address
(v_addressid number)--参数只指定类型，不指定长度
return varchar2
is
v_name varchar2(30);
begin
select a.name into v_name from t_address a where a.id=v_addressid;
return v_name;
end ;
--测试存储函数
select fun_address(3) from dual
查询业主 ID，业主名称，业主地址，业主地址使用刚才我们创建的函数 来实现。
--查询业主 ID，业主名称，业主地址，业主地址使用刚才我们创建的函数来实现。
select a.id 业主id,a.name 业主名称,fun_address(a.addressid) 业主地址
from t_owners a ;
3.存储过程
3.1什么是存储过程
存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。
应用程序可以调用存储过程，执行相应的逻辑。
存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如 下：
1、存储函数中有返回值（return），且必须返回，且只有一个返回值；而存储过程没有返回值（return），可以通过传出参数返回多个值。
2、存储函数可以在select语句中直接使用，而存储过程不能。过程多数是被应用程序所调用。
3、存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。
--存储过程语法结构
create [or replace] procedure 存储过程名
(参数名 参数类型，...)--参数只指定类型，不指定长度
is|as
--声明变量
begin
--逻辑
[exception 异常处理]
end;
过程参数的三种模式：
-
IN 传入参数（默认）
-
OUT 传出参数 ，主要用于返回程序运行结果
-
IN OUT 传入传出参数
3.2创建不带传出参数的存储过程
--存储过程
--创建不带传出参数的存储过程：添加业主信息
--创建序列
create sequence seq_owners
start with 11;
--创建存储过程
create or replace procedure pro_owners_add
(v_name varchar2,
v_addressid number,
v_housenumber varchar2,
v_watermeter varchar2,
v_ownertypeid number)
is
begin
insert into t_owners
values(seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_ownertypeid);
commit;
end;
--PL/SQL 中调用存储过程方法一：
call pro_owners_add('马大哈',1,222,45353,1);
--PL/SQL 中调用存储过程方法二：
begin
pro_owners_add('马二哈',1,22552,45766,1);
end;
3.3创建带传出参数的存储过程
--需求：添加业主信息，传出参数为新增业主的 ID
create or replace procedure pro_owners_add2
(v_name varchar2,
v_addressid number,
v_housenumber varchar2,
v_watermeter varchar2,
v_ownertypeid number,
v_id out number)
is
begin
select seq_owners.nextval into v_id from dual;
insert into t_owners
values(v_id,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_ownertypeid);
end;
--测试
declare
v_id number(30);
begin
pro_owners_add2('张哈',1,4365676,669,1,v_id);
dbms_output.put_line('插入成功，ID：'||v_id);
end;
4.触发器
4.1什么是触发器
-
数据库触发器是一个和表有关的pl/sql程序。
-
每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句。
触发器分类 :
前置触发器（BEFORE）
后置触发器（AFTER）
创建触发器的语法:
create [or replace] trigger 触发器名
BEFORE | AFTER
[UPDATE][[OR] INSERT][[OR] DELETE [OF 列名]]
ON 表名
[FOR EACH ROW][when 条件]
declare
begin
end;
FOR EACH ROW 作用是标注此触发器是行级触发器，另外还有语句级触发器
4.2前置触发器案例
--前置触发器
--需求：当用户输入本月累计表数后，自动计算出本月使用数 。
create or replace trigger tri_account1
before
update of num1
on t_account
for each row
declare
begin
:new.usenum:=:new.num1-:new.num0;
end;
4.3后置触发器案例
--后置触发器
--需求：当用户修改了业主信息表的数据时记录修改前与修改后的值
--创建日志表
create table owners_log(
updatetime date,
owners_id number,
newname varchar2(30),
oldname varchar2(30)
);
--创建后置触发器
create or replace trigger tri_owners
after
update of name
on t_owners
for each row
declare
begin
insert into owners_log values(sysdate,:old.id,:new.name,:old.name);
end;
--测试
select * from owners_log;
update t_owners a set a.name='张丰' where a.id='17';
5.综合案例
5.1编写 PL/SQL ，用水吨数 12 吨，业主类型为 1，计算阶梯水费。
--编写 PL/SQL ，用水吨数 12 吨，业主类型为 1，计算阶梯水费。
declare
v_usenum number;
v_money number(10,2);
v_ownertypeid number;
cursor cur_price(ownertype varchar2)
is
select * from t_pricetable a where a.ownertypeid=ownertype order by a.minnum;
v_pricetable t_pricetable%rowtype;--可省略
begin
v_usenum:=12;
v_ownertypeid:=1;
v_money:=0;
for v_pricetable in cur_price(v_ownertypeid)
loop
if v_pricetable.maxnum is null or v_usenum <=v_pricetable.maxnum then
v_money:=v_money+v_pricetable.price*(v_usenum-v_pricetable.minnum);
exit;
else
v_money:=v_money+v_pricetable.price*(v_pricetable.maxnum-v_pricetable.minnum);
end if;
end loop;
dbms_output.put_line('总水费金额：'||v_money);
end;
--编写 PL/SQL ，用水吨数 12 吨，业主类型为 1，计算阶梯水费。
declare
v_usenum number;
v_money number(10,2);
v_ownertypeid number;
cursor cur_price(ownertype varchar2)
is
select * from t_pricetable a where a.ownertypeid=ownertype;
v_pricetable t_pricetable%rowtype;
begin
v_usenum:=12;
v_ownertypeid:=1;
v_money:=0;
open cur_price(v_ownertypeid);
loop
fetch cur_price into v_pricetable;
exit when cur_price%notfound;
if v_pricetable.maxnum is null or v_usenum <=v_pricetable.maxnum then
v_money:=v_money+v_pricetable.price*(v_usenum-v_pricetable.minnum);
else
v_money:=v_money+v_pricetable.price*(v_pricetable.maxnum-v_pricetable.minnum);
end if;
end loop;
close cur_price;
dbms_output.put_line('总水费金额：'||v_money);
end;
5.2存储函数综合案例：创建计算阶梯水费的函数，参数为业主类型、吨数。
--2.存储函数综合案例：创建计算阶梯水费的函数，参数为业主类型、吨数。
create or replace function fn_watermoney
(v_ownertype number,
v_usenum number)
return number
is
cursor cur_price(v_ownertype number) is
select * from t_pricetable a where a.ownertypeid=v_ownertype order by a.minnum;
v_pricetable t_pricetable%rowtype;
v_money number(10,2);
begin
v_money:=0;
for v_pricetable in cur_price(v_ownertype)
loop
if v_pricetable.maxnum is null or v_usenum<=v_pricetable.maxnum then
v_money:=v_money+v_pricetable.price*(v_usenum-v_pricetable.minnum);
exit;
else
v_money:=v_money+v_pricetable.price*(v_pricetable.maxnum-v_pricetable.minnum);
end if;
end loop;
return v_money;
end;
--测试
select fn_watermoney(1,12) from dual
5.3触发器综合案例：当用户输入本月累计数后，自动计算阶梯水费。
--3.触发器综合案例：当用户输入本月累计数后，自动计算阶梯水费。
create or replace trigger tri_waterprice
before
update of num1
on t_account
for each row
declare
v_usenum2 number(10,2);--吨数
begin
:new.usenum:=:new.num1-:new.num0;
v_usenum2:=round(:new.usenum/1000,2);
:new.money:=fn_watermoney(:new.ownertype,v_usenum2);
end;
5.4存储过程综合案例
需求：增加业主信息时，同时在账务表（account）增加一条记录，年份与月份 为当前日期的年月，初始值（num0）为 0,其它字段信息（区域）与 t_owners 表一致
--4.需求：增加业主信息时，同时在账务表（account）增加一条记录，年份与月份
--为当前日期的年月，初始值（num0）为 0,其它字段信息（区域）与 t_owners表一致
create or replace procedure pro_owners_account
(v_name varchar2,
v_addressid number,
v_housenumber varchar2,
v_watermeter varchar2,
v_ownertypeid number,
v_owneruuid out number)
is
v_num0 number;
v_year char(4);--注意类型
v_month char(2);
v_areaid number;
begin
select seq_owners.nextval into v_owneruuid from dual;
select ad.areaid into v_areaid from t_address ad where ad.id=v_addressid;
v_num0:=0;
v_year:=to_char(sysdate,'yyyy');
v_month:=to_char(sysdate,'mm');
insert into t_owners values(v_owneruuid,v_name,v_addressid,v_housenumber,
v_watermeter,sysdate,v_ownertypeid) ;
insert into t_account (id,owneruuid,ownertype,areaid,year,month,num0)
values(seq_account.nextval,v_owneruuid,v_ownertypeid,v_areaid,v_year,v_month,v_num0);
commit;
end;
--测试
declare
v_uuid number(10);
begin
pro_owners_account('李大',1,'52342','4556353',1,v_uuid);
end;
