oracle之4

1.pl/Sql(一种数据库语言)
1.定义变量: 在declare中定义 : 变量名 变量类型(长度) (其中变量类型,可以用引用的方式获得: 表名.字段名%type )
2.代码编写区:begin 代码区 end; 
1.代码区 直接给变量赋值 变量:=值;
2.代码区 从表中查询之后赋值: 
1.单个变量赋值 : select 字段 into 变量 from ...  
(其中变量的类型是一个字段的类型,获取方式:a.直接给类型,或者 b.引用方式:表名.字段名%type )
2.整行赋值     : select  *  into  变量 from ...  
(其中变量的类型是表的行的类型,获取方式:表名%rowtype)
3.代码区 异常处理
1. exception when 异常名 then 异常处理方式
4.代码区 条件判断 :if语句
1. if 条件 then 业务逻辑 end if;
2. if 条件 then 业务逻辑  else 业务逻辑 end if;
3. if 条件 then 业务逻辑 elsif 条件 then  业务逻辑 elsif 条件 then 业务逻辑 else 业务逻辑 end if;
5.代码区 循环:
1.无条件循环---- loop循环:
loop 
循环体; 
if 退出条件 then 
exit; 
end if; 
end loop;
2.有条件循环
while 条件
loop
end loop;

3.for 循环
for循环类似于 java中的foreach循环

for 变量 in 起始值..终止值          |       for(变量:数据集){
loop             |         对变量进行操作;
业务逻辑;             |       } 
end loop;

也就是说,在PL/SQL语法中,它的for循环是自动对数据集的数据进行遍历,然后将每一次遍历元素后将值赋给变量,
因此我们就可以在loop循环中对变量进行直接操作

3.游标 :游标是系统为用户开设的一个数据缓冲区,存放 SQL 语句的执行结果。 我们
可以把游标理解为 PL/SQL 中的结果集。
1.定义方法: cursor 游标名称  is SQL语句
2.游标中数据的获取:
1.loop循环获取
open 游标名称;
loop
fetch 游标名称 into 变量;
exit when 游标名称%notfound;
end loop;
close 游标名称;
2.for循环获取:
for 变量 in 游标名称
loop

end loop;

3.带参数游标:
所谓带参数的游标其实就是在定义游标的时候,它的SQL语句带参数;
1.定义方法: cursor 游标名称(变量名 变量类型)  is SQL语句(语句中使用了 变量);




4.存储函数: 存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。
在函数中我们可以使用 P/SQL 进行逻辑的处理。一般用的查询比较多;
1.创建存储函数的方法:
create or replace function 函数名称
(参数名称 参数类型,参数名称 参数类型)
return 结果变量数据类型
is
变量声明部分;
begin
逻辑部分;
return 变量结果;
exception
异常处理部分;
end;



5.存储过程: 
1.什么是存储过程
存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。
应用程序可以调用存储过程，执行相应的逻辑存储过程与存储函数都可以封
装一定的业务逻辑并返回结果，存在区别如下：
1、 存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过
传出参数返回多个值。
2、 存储函数可以在 select 语句中直接使用，而存储过程不能。过程多数是
被应用程序所调用。
3、 存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务
代码通过传出参数,向调用端传出多个参数;不能在sql中直接使用,
一般在应用程序中调用;
2.对存储过程中的传出参数的个人理解: 这个传出参数类似于servlet中的response,
在servlet中,容器给我们创建了Request和response对象,然后传入servlet中,我们
从Request中获得数据,我们也可将结果放到response中,这个response就类似一个传出
参数,因为我们放在response中的数据,都会被容器获取,所以,无论是response还是
传出参数,其实都是调用者专门给被调用者指定的结果存放的空间;
3.创建或修改存储过程的语法如下:
create or replace procedure pro_owners_id
(参数名称 参数类型,参数名称 参数类型)
is
变量声明部分;
begin
逻辑部分;
exception 
异常处理部分;

end;

ps:参数列表中参数,只指定类型,不指定长度;(只有变量在定义时才指定长度)
参数列表中的三种模式:
IN 传入参数(默认);
OUT 传出参数,主要用于返回程序运行结果;(被调用者将结果放入,调用者从中拿出)
IN OUT 传入传出参数;

6.触发器
1.什么是触发器
数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的
数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发
器中定义的语句序列。
2.个人理解:
触发器就相当于是js中的监听器,在表上注册,当表发生变化时,就触发动作执行;

3.创建触发器的语法:
create or replace trigger 触发器名
before|after
[delete] [[or] insert] [[or] update [of 列名]]
on 表名
[for each row] [when 条件]
declare
begin
PLSQL块;
end;

4.触发器中的 '触发语句' 和 '伪记录变量' 的值
:old 持有旧行的引用,可以用来获取旧行的字段值,比如 ':old.name'

:new 持有新行的引用,可以用来获取旧行的字段值,比如 ':old.num'



ps:开启输出: set serveroutput on;
2.水费计算实例:
declare
v_account t_account%rowtype; --台账记录行类型
v_usenum number(10,2); --定义水表字数
v_money number(10,2) --定义水费
v_price1 number(10,2):=2.45; --5吨以下水费价格
v_price2 number(10,2):=3.45; --5吨-10吨的水费价格
v_price3 number(10,2):=4.45; --10吨以上水费价格
begin
-- 从数据库提取一行数据
select * into v_account from t_account where year='2012' and month='01' and owneruuid=1;
-- 从行数据中提取出水表字数
v_usenum:=round(v_account.usenum/1000,2);
--计算阶梯水费
if v_usenum<=5 then 
v_money:=v_usenum*v_price1;
elsif v_usenum<=10 then
v_money:=5*v_price1+(v_usenum-5)*v_price2;
else 
v_money:=5*v_price1+5*v_price2+(v_usenum-10)*v_price3;
end if;

-- 输出信息
DBMS_OUTPUT.put_line('字数:'||t_account.usenum||'金额:'||v_money);
end;



3.触发器案例:
1.前置触发器:
需求：当用户输入本月累计表数后，自动计算出本月使用数

create or replace trigger tri_account_updatenum1
before
update
on t_account
for each row
declare
begin
:new.usenum:= :new.num1-:new.num0;
end;

2.后置触发器:
需求：当用户修改了业主信息表的数据时记录修改前与修改后的值
--创建业主名称修改日志,用于存储业主更改前后名称;
create table t_owners_log 
(
updatetime date,
ownerid number,
oldname varchar2,
newname varchar2
);

创建后置触发器,记录业主更改前后信息
create or replace trigger tri_owners_update 
before
update
on t_owners
for each row
declare
begin
insert into t_owners_log values(sysdate,:old.id,:old.name,:new.name)

end;
4.综合案例:
1.需求：编写 PL/SQL ，用水吨数 12 吨，业主类型为 1，计算阶梯水费。
思路分析:
1.水费实行阶梯计算,我们可以先建立游标,来记录水费:
create or replace cursor cur_price is 
select * from t_pricetalbe where ownertypeid=1 order by minnum;
2.我们利用游标来计算水费
create or replace function fn_moneybyusenum(usenum number)
return number;
declare
v_money number(10,2):=0;
begin

for pricerow in cur_price
loop
if pricerow.maxum is null then
if v_usenum>=pricerow.minnum then
v_money:=v_money+(v_usenum-pricerow.minnum)*pricerow.price;
else return v_money;
else 
if v_usenum<=pricerow.maxnum then
if v_usenum>=pricerow.minnum then
v_money:=v_money+(v_usenum-pricerow.minnum)*pricerow.price;
else return v_money;
else 
v_money:=v_money+(pricerow.maxnum-pricerow.minnum)*pricerow.price;
end if;

end if;
end loop;

end;