一、pl/sql块
在sql语句中增加过程处理语句，使sql语句具有过程处理能力
语法：
[declare
--变量声明
]
begin
--代码逻辑
[exception
--异常处理
]
end;
--变量
--声明变量
变量名 类型(长度)
--变量赋值语法
变量名:= 变量值
declare
v_price number(10,2);
v_usenum number;
v_usenum2 number(10,2);
v_money number(10,2);
begin
v_price:=2.45;
v_usenum:=9231;
v_usenum2:=round(v_usenum/1000,2);
v_money:=v_price*v_usenum2;
--打印
DBMS_OUTPUT.put_line('金额：'||v_money);
end;
--从数据库中查询然后赋值
select 列名 into 变量名 from 表名 where 条件
declare
v_price number(10,2);
v_usenum number;
v_usenum2 number(10,2);
v_money number(10,2);
v_num0 number;--上月水表数
v_num1 number;--本月水表数
begin
v_price:=2.45;
--v_usenum:=9231; 水表字数
select usenum into v_usenum from t_account
where year = '2012' and month = '01'and owneruuid=1;
v_usenum2:=round(v_usenum/1000,2);
v_money:=v_price*v_usenum2;
--打印
DBMS_OUTPUT.put_line('金额：'||v_money);
end;
--属性类型（引用型 表名.列名%type）
处理数据库表数据时的首选写法
完全不用改代码，自动适配新类型
select * from t_account
declare
v_price number(10,2);
v_usenum t_account.usenum%type;
v_usenum2 number(10,2);
v_money number(10,2);
v_num0 t_account.num0%type;--上月水表数
v_num1 t_account.num1%type;--本月水表数
begin
v_price:=2.45;
--从数据库提取
select usenum into v_usenum from t_account
where year = '2012' and month = '01'and owneruuid=1;
v_usenum2:=round(v_usenum/1000,2);
v_money:=v_price*v_usenum2;
--打印
DBMS_OUTPUT.put_line('金额：'||v_money);
end;
--数据类型（记录型 表名%rowtype）
它是存储一整行数据的记录型类型，是 %TYPE 的升级版
1. 查询表中一整行数据时（最常用）
不用单独声明 g_id、g_name、g_price 三个变量，一个变量搞定所有列。
2. 表结构修改时（自动适配）
如果给表加一列、修改价格精度，代码完全不用改，%ROWTYPE 自动同步新结构。
3. 插入 / 更新整行数据时
declare
v_price number(10,2);
v_usenum2 number(10,2);
v_money number(10,2);
v_account t_account%rowtype;--台账行记录类型
begin
v_price:=2.45;
--从数据库提取
select * into v_account from t_account
where year = '2012' and month = '01'and owneruuid=1;
v_usenum2:=round(v_account.usenum/1000,2);
v_money:=v_price*v_usenum2;
--打印
DBMS_OUTPUT.put_line('金额：'||v_money);
end;
二、异常
发生异常后，语句停止执行，控制权转移到pl/sql 异常处理部分，有两种异常
预定义异常：当pl/sql程序违法oracle规制或超过系统现在模式引发
自定义异常：在pl/sql块的声明部分定义异常，自定义的异常通过raise语句显示引发
exception
when 异常类型 then
异常处理逻辑
--条件判断
if 条件 then
业务逻辑
end if;
if条件 then
业务逻辑
else
业务逻辑
end if;
if 条件 then
业务逻辑
elsif 条件 then 业务逻辑
else
业务逻辑
end if;
设置三给等级的水费5吨以下2.45元，5-10吨3.45 超过10吨4.45
declare
v_price number(10,2);
v_price2 number(10,2);
v_price3 number(10,2);
v_usenum2 number(10,2);
v_money number(10,2);
v_account t_account%rowtype;--台账行记录类型
begin
v_price:=2.45;
v_price2:=3.45;
v_price3:=4.45;
--从数据库提取
select * into v_account from t_account
where year = '2012' and month = '01'and owneruuid=1;
v_usenum2:=round(v_account.usenum/1000,2);
--水费计算
if v_usenum2<=5 then
v_money:=v_price*v_usenum2;
elsif v_usenum2>5 and v_usenum2<=10 then
v_money := v_price2*(v_usenum2 - 5)+ v_price*5;
else
v_money := v_price*5+v_price2*5+v_price3*(v_usenum2-10);
end if;
--打印
DBMS_OUTPUT.put_line('金额：'||v_money);
end;
三、循环
1.无条件循环
loop
--循环语句
end loop;
输出从1到100个数
declare
v_num number:=1;
begin
loop
dbms_output.put_line(v_num);
v_num:=v_num+1;
if v_num>100 then
exit;
end if;
end loop;
end;
2.有条件循环
while 条件
loop
end loop;
输出从1到100个数
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;
3.for循环
declare
begin
for v_num in 1..100
loop
dbms_output.put_line(v_num);
end loop;
end;
四、游标
游标是系统给用户开设的一个数据缓冲区，存放sql语句执行的结果，可以把游标理解成pl/sql的一个结果集
语法：
--声明
cursor 游标名称 is sql语句
--使用游标语法
open 游标名称
loop
fetch 游标名称 into 变量
exit when 游标名称%notfound --代表游标走到底了
end loop;
close 游标名称
打印业主类型为1的价格表
select * from t_pricetable where ownertypeid = 1
--游标输出结果集
declare
cursor cur_pricetable is select * from t_pricetable where ownertypeid = 1; --声明变量
v_pricetable t_pricetable%rowtype;
begin
open cur_pricetable;--打开游标
loop
fetch cur_pricetable into v_pricetable;--提前数据
exit when cur_pricetable%notfound;--退出循环
DBMS_OUTPUT.PUT_LINE('价格'||v_pricetable.price);
end loop;
close cur_pricetable;
end;
--带参数的游标
declare
cursor cur_pricetable(v_ownertype) is select * from t_pricetable where ownertypeid = v_ownertype; --声明变量
v_pricetable t_pricetable%rowtype;
begin
open cur_pricetable(1);--打开游标,数字是ownertypeid对应类型，代表传进去的参数
loop
fetch cur_pricetable into v_pricetable;--提前数据
exit when cur_pricetable%notfound;--退出循环
DBMS_OUTPUT.PUT_LINE('价格'||v_pricetable.price);
end loop;
close cur_pricetable;
end;
--for循环带参数游标
declare
cursor cur_pricetable(v_ownertype) is select * from t_pricetable where ownertypeid = v_ownertype; --声明变量
v_pricetable t_pricetable%rowtype;
begin
for v_pricetable in cur_pricetable(1) --数字是ownertypeid对应类型，代表传进去的参数
loop
DBMS_OUTPUT.PUT_LINE('价格'||v_pricetable.price);
end loop;
end;
五、存储函数
存储函数又称自定义函数，可以接受一个或多个参数，返回一个结果，用pl/sql进行逻辑处理
语法：
create [or replace] function 函数名称
(参数名称 参数类型，参数名称 参数类型,...)
return 结果变量数据类型
IS
变量声明部分;
begin
逻辑部分
return 结果变量;
[exception
异常处理部分]
end;
--创建存储函数，根据地址id查询地址名称
create or replace function fn_getaddress1
(v_id number)
return varchar2
IS
v_name varchar2(30);
begin
select name into v_name from t_address where id =v_id; --将查询到name的结果存入变量v_name中
return v_name;
end;
--存储函数测试
select fn_getaddress1(3) from dual;
--应用
查询业主id,业主名称，业主地址，用存储函数就可以实现，不用多表关联
select * from t_owners
select * from t_address
select ow.id,ow.name,ad.name from t_owners ow,t_address ad where ow.addressid = ad.id --多表的
select id,name,fn_getaddress1(addressid)from t_owners;--存储函数的
六、存储过程
业务必须全部成功或全部失败
存储过程是命名的pl/sql块，存储于数据库中，是数据库对象的一种，应用程序可以调用存储过程，执行相应的逻辑
存储过程与存储函数的区别
1.存储函数中有返回值，必须返回，存储过程没有返回值，可以通过传出参数返回多个值
2.存储过程可以在select语句中直接使用，而存储过程不能，过程多数是被应用程序所调用
3.存储过程一般是封装一个查询结果，而存储过程一般可以封装一段事务代码。
create or replace procedure 存储过程名称
(参数名 类型,参数名 类型)
is|as
变量声明部分
begin
逻辑部分
[exception
异常处理部分
]
end;
--过程参数三种模式
in 传入参数(默认)
out 传出参数，主要用于返回程序运行结果
in out 传入传出参数
--创建不带传出参数的存储过程：添加业主信息，可以用序列
select * from owners
create sequence seq_ownersid start with 11;
create or replace procedure pro_insert
(v_name varchar2,v_addressid number,v_housenumber varchar2,v_waternumber varchar2,v_ownertypeid number )
is
begin
insert into t_owners values(seq_ownersid.nextval,v_name,v_addressid,v_housenumber,v_waternumber,sysdate,v_ownertypeid);
commit;
end;
调用存储过程
写法1：
call pro_insert ('测试','10','1-10','345',1)
写法2：
begin
pro_insert ('测试1','10','1-10','345',1);
end;
--创建带参数存储过程
create or replace procedure pro_insert1
(v_name varchar2,
v_addressid number,
v_housenumber varchar2,
v_waternumber varchar2,
v_ownertypeid number,
v_id out number
)
is
begin
--对传出参数赋值
select seq_account.nextval into v_id from dual;
insert into t_owners values(v_id,v_name,v_addressid,v_housenumber,v_waternumber,sysdate,v_ownertypeid);
commit;
end;
调用
declare
v_id number;
begin
pro_insert1 ('测试3','10','1-10','345',1,v_id);
dbms_output.put_line(v_id);
end;
七、触发器
一个与表关联的，存储的pl/sql程序，每当一个特定的数据操作语句在指定表上发出时，oracle自动执行触发器中定义的语句序列
可用于：
数据确认，实施复杂的安全性检查，做审计，跟踪表上所做的数据操作等，数据的备份和同步
分类
前置触发器（before）
后置触发器（after）
创建语法
create [or replace] trigger 触发器名称
before | after
[delete][[or]insert][[or]update [of 列名]]
on 表名
[for each row][where(条件)] -- for each row表明是行级触发器
declare
...
begin
plsql块
end;
--触发器中触发语句与伪记录变量的值（前置触发器可以改这个伪列的值，后置触发器不能修改只能使用）
分为行级触发器和语句触发器，语句触发器没有:old和:new
insert
:old 所有字段都是空(null)
:new 将要插入的数据
update
:old 更新以前该行的值
:new 更新后的值
delete
:old 删除以前该行的值
:new 所有字段都是空
--前置触发器
当用户输入本月累计表数后，自动计算出本月使用数
select * from t_account for update
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;
--后置触发器
当前用户修改了业主名称的时间时记录修改前与修改后的值
--创建记录表
create table t_owners_log(
updatetime date,
ownerid number,
oldnum varchar2(30),
newnum varchar2(30)
);
--创建触发器
create or replace trigger tri_after
after
update of name
on t_owners
for each row
declare
begin
--向日志表插入
insert into t_owners_log values(sysdate,:new.id,:old.name,:new.name);
end;
select * from t_owners for update
--当前窗口能看到日志表里有数据，但是别的窗口看不到，要commit后才能看到
select * from t_owners_log
commit;