Oracle编程

1.PL/SQL

--基本语法结构
[declare
--声明变量
]
begin
--代码逻辑
[
--异常处理
]
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:=9213
v_usenum2:=round(v_usenum / 1000 ,2)
v_money := v_price * v_usenum2;
DBMS_OUTPUT.putline('account:' || v_money);

end;

–select 列名 into 变量名

declare
v_price Number（10,2）；--单价
v_usenum number;--水费字数
v_num0 number；--上月字数
v_num1 number；--本月字数
v_usenum2 number(10,2)--使用吨数
v_money number(10,2)；--水费金额
begin
v_price :=2.45
//从数据库提取
select usenum，num0，num1 into v_usenum，v_num0，v_num1 from T_CCOUNT
where year=‘2012’ And month=‘01’ And owneruuid = 1;

v_usenum2:=round(v_usenum / 1000 ,2)
v_money := v_price * v_usenum2;
DBMS_OUTPUT.putline('account:' || v_money);

end;

–属性类型（引用型 表名.列名%type）

declare
v_price Number（10,2）；--单价
v_usenum t_account.usenum%type;--水费字数
v_num0 t_account.num0%type；--上月字数
v_num1 t_account.num1%type；--本月字数
v_usenum2 number(10,2)--使用吨数
v_money number(10,2)；--水费金额
begin
v_price :=2.45
select usenum，num0，num1 into v_usenum，v_num0，v_num1 from T_CCOUNT
where year=‘2012’ And month=‘01’ And owneruuid = 1;

v_usenum2:=round(v_usenum / 1000 ,2)
v_money := v_price * v_usenum2;
DBMS_OUTPUT.putline('account:' || v_money);

end;

–属性类型（纪录性：代表某个表的某一行的类型 表名%type）

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_CCOUNT
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.putline('account:' || v_money);

end;

–异常
1.预定义异常：当PLSQL程序违反Oracle规则或超越系统限制时隐式引发。
2.用户定义异常：用户可以在PLSQL块的声明部分定义异常，自定的异常通过RAISE语句显式引发。

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_CCOUNT
where year=‘2012’ And month=‘01’ And owneruuid = 1000;
//报错：no data found


v_usenum2:=round(v_account.usenum / 1000 ,2)
v_money := v_price * v_usenum2;
DBMS_OUTPUT.putline('account:' || v_money);

exception
when NO_DATA_FOUND then DBMS_OUTPUT.putline('没有找到相应的数据')
when too_many_rows then DBMS_OUTPUT.putline('返回多行数据')

end;

–判断语句

if ... then 
elsif ... then
else
end if;

–循环语句

loop
--无条件循环
END loop；

eg：无条件循环输出1-100
declare 
v_num number := 1;
begin
loop
 DBMS_OUTPUT.putline(v_num);
 v_num:=v_num+1;
 exit when v_num > 100;
 end loop;
 end;

–条件循环

while 条件
loop
END loop；

eg：有条件循环输出1-100
declare 
v_num number;
begin
    v_num:=1;
    while v_num<=100
    loop
     DBMS_OUTPUT.putline(v_num);
     v_num:=v_num+1;
    end loop
end;

–for 循环

declare

begin
    for v_num 1..100
    loop
    DBMS_OUTPUT.putline(v_num);
    end loop;
end；

–游标语法
游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，我们可以把游标理解为PL/SQL中的结果集。

在声明区声明游标，语法如下:
cursor 游标名称 is SQL语句；
使用游标语法
Open 游标名称
loop
    fetch 游标名称 into 变量
    exit when 游标名称%notfound
END loop；
close 游标名称

eg：
declare
    cursor cur_pricetable is select * from t_pricetable where ownertypid=1;
begin
 open cur_pricetable;
     loop
         fetch cur_pricetable into v_pricetable
         exit when cur_pricetable%notfound;
         DBMS_OUTPUT.putline('price:' || v_pricetable);

    end loop;
end;

eg：带参数的游标
eg：
declare
    cursor cur_pricetable(v_ownertype number) is select * from t_pricetable where ownertypid=v_ownertype;
    v_pricetable t_pricetable%rowtype;
begin
 open cur_pricetable;
     loop
         fetch cur_pricetable into v_pricetable
         exit when cur_pricetable%notfound;
         DBMS_OUTPUT.putline('price:' || v_pricetable);

    end loop;
end;

eg:for循环带参数游标
declare 
cursor cur_pricetable(v_type number) is select * from t_pricetable where ownertypeid=v_type;
begin
for v_pricetable in cur_pricetable(1);
loop
 DBMS_OUTPUT.putline('price:' || v_pricetable);
 end loop;
 end;

2.存储函数

--存储函数：自定义函数，可以接受一个或者多个参数，返回一个结果。在函数中我们可以使用PLSQL进行逻辑处理。

--存储函数的语法结构
create [or replace] FUNCTION foo_name(参数名称 参数类型，参数名称 参数类型)

return 结果变量数据类型
IS
变量声明部分；
BEGIN
逻辑部分；
RETURN 结果变量；
[Exception 异常处理部分]
end；

eg：创建存储函数，根据地址ID查询地址名称。
create function fn_getaddress(v_id number)
return varchar2
is
v_name varchar2(30);
begin
    select name into v_name from t_address where id = v_id;
    return v_name;
end;

TEST:select fn_getaddress(3) from dual;

3.存储过程

存储过程是被命令的PLSQL块，存储与数据库中，是数据库对象的一种。
应用程序可以调用存储过程，执行相应的逻辑。
存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：
1.存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值。
2.存储函数可以在select语句中直接使用，而存储过程不能，过程过多时被应用程序多调用。
3.存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。

--存储过程语法结构
CREATE [OR REPLACE] PROCEDURE 存储过程名称
（参数名 类型，参数名 类型，参数名 类型）
IS|AS
变量声明部分；
BEGIN
逻辑部分
[EXCEPTION 异常处理部分]
END；
参数指定类型，不指定长度
过程参数的三种模式：
IN 传入参数（默认）
OUT 传出参数，主要用于返回程序运行结果
IN OUT 传入传出参数

--案例
1.创建不带传出参数的存储过程
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);
commit;
end;

调用不带传出参数的存储过程
call pro_owners_add（‘tommy’，2，‘23333’，‘67777’，1）；

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,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);
commit;
end;

--调用传出参数的存储过程
declare
v_id number;
begin
    pro_owners_add('tommy2',2,'2333','2333333',1,vid);
    DBMS_output.putline(v_id);
end；

4.触发器

触发器是一个与表相关联的，存储的PLSQL程序，每当一个特定的数据操作语句（insert，update，delete）在指定的表上发出时，Oracle自动的执行触发器中定义的语句序列。

触发器可用于：

 - 数据确认
 - 实施复杂的安全检查
 - 做审计，跟踪表上所有的数据操作等
 - 数据的备份和同步
 触发器分类：

 - 前置触发器
 - 后知触发器

 --创建触发器的语句
 CREATE [OR REPLACE] TRIGER 触发器名称
 BEFORE | AFTER
 [DELETE ] [(or) INSERT] [[OR] UPDATE [OR 列名]]
 ON 表名
 [FOR EACH ROW ][WHEN 条件]
 declare
 ...
 begin
 PLSQL 块
 End；
//FOR EACH ROW作用是标注此触发器是行级触发器， 语句触发器
在触发器中触发语句与未记录变量的值
Insert 所有字段都是空 将要插入的数据
Update 更新以前该行的值 更新后的值
delete 删除以前该行的值 所有字段都是空

--前置触发器
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_owner_log(
updatetime datee,
ownerid number,
oldname varchar2(30),
newname varchar2(30)
);
--创建后置触发器，自动记录业主更改前后日志
create trigger tri_owners_log
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;

--验证
update t_owners set name='tommy' where id=4;