Oracle编程
序
任意语言的三个基本结构：顺序结构、分支结构、循环结构。任意语言都拥有变量和常量。
PL/SQL
PL/SQL的基本概念
PL/SQL（Procedure Language/SQL），是Oracle对Sql语言的过程化拓展，是一门语言，可以让SQL具备逻辑，具有过程处理能力。
基本语法结构
[declare --声明变量]
begin
[plsql代码]
[exception --异常处理]
end;
--常见的简单模式
begin
plsql
end
基本元素
变量
注意事项：
- 变量名 变量类型
- 赋值符号 :=
- declare后边声明变量，而变量的赋值在begin和end之间
- 内置存储函数（过程）：DBMS_OUTPUT.PUTLINE()
案例
--变量声明与赋值
declare --声明
v_price number(10,2);
v_usenum number;
v_usenum2 number(10,2);
v_money(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.put_line('金额：'||v_money);--内置存储函数，可以输出
end;
--第二种赋值方法
declare --声明
v_price number(10,2);
v_usenum number;
v_usenum2 number(10,2);
v_money(10,2);
v_num0 number;
v_num1 number;
begin
v_price:=2.45;
--语句：select 【数据库中的列名】 into 变量名称
select usenum into v_usenum from t_account where year='2012' and monyh='01' and owneruuid=1;
v_usenum:=9213;
v_usenum2:=round(v_usenum/1000,2);
v_money:=v_price*v_usenum2;
end
引用类型
语法格式：表名.列名%type
declare --声明
v_price number(10,2);
--可以直接查找到表中的值的类型
v_usenum t_account.usenum%type;
v_usenum2 number(10,2);
v_money(10,2);
v_num0 number;
v_num1 number;
begin
v_price:=2.45;
select usenum into v_usenum from t_account where year='2012' and monyh='01' and owneruuid=1;
v_usenum:=9213;
v_usenum2:=round(v_usenum/1000,2);
v_money:=v_price*v_usenum2;
end
记录类型
语法格式：变量名称 表名%rowtype
使用时语句：变量名称 . 列名
declare --声明
v_price number(10,2);
--可以直接查找到表中的值的类型
v_usenum t_account.usenum%type;
v_usenum2 number(10,2);
v_money(10,2);
v_num0 number;
v_num1 number;
begin
v_price:=2.45;
select usenum into v_usenum from t_account where year='2012' and monyh='01' and owneruuid=1;
v_usenum:=9213;
v_usenum2:=round(v_usenum/1000,2);
v_money:=v_price*v_usenum2;
end
异常
在运行程序时出现的错误叫做异常（也叫例外）
- 预定义异常
- 自定义异常
预定义异常共有21个
--语法格式
exception
when 异常类型 then
异常处理逻辑
案例
declare
v_price number(10,2);-- 水费单价
v_usenum T_ACCOUNT.USENUM%type; -- 水费字数
v_usenum2 number(10,3);-- 吨数
v_money number(10,2);-- 金额
begin
v_price:=2.45;-- 水费单价
select usenum into v_usenum from T_ACCOUNT where
owneruuid=1 and year='2012' and month='01';
-- 字数换算为吨数
v_usenum2:= round( v_usenum/1000,3);
-- 计算金额
v_money:=round(v_price*v_usenum2,2);
dbms_output.put_line('单价：'||v_price||'吨
数:'||v_usenum2||'金额：'||v_money);
exception
--只要查到这个异常就会处理
when NO_DATA_FOUND then
dbms_output.put_line('未找到数据，请核实');
when TOO_MANY_ROWS then
dbms_output.put_line('查询条件有误，返回多条信息，请核实');
end;
记忆两个
NO_DATA_FOUND：没有找到数据
TOO_MANY_ROWS：结果集超过一行
分支结构
条件判断
--sql中的条件判断语句
if 条件 then
end if;
if 条件 then
else
end if；
if 条件 then
elsif 条件 then
。。。
else
end if；
案例：
declare
v_price1 number();
v_price2 number();
v_price3 number();
v_usenum2 number();
v_money number();
v_account t_account%rowtype;
--阶梯水费计算
begin
if v_usenum2<=5 then
v_money:=v_price1*v_usenum2;
elsif v_usenum2>5 and v_usenum2<=10 then
v_money:=v_price1*5+ v_price2*(v_usenum2-5)
else
v_money:=v_price1*5+ v_price2*(v_usenum2-5)+v_price3*(v_usenum2-10)
循环
循环关键字：loop 循环语句 end loop;
--loop无条件循环
loop
循环语句
end loop;
--案例
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;
(或者：exit when v_num>100;)
end loop;
end;
--loop有条件循环
while 满足循环的条件
loop
循环语句
end loop;
--案例
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循环
for 局部变量 in 条件
loop
循环语句
end loop;
--案例
declare
v_num number;
begin
for v_num in 1..100
loop
dbms_output.put_line(v_num);
end loop;
end;
游标
游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。
游标的作用
速度快！2017年开发人员基本不用游标了，但是在今后的学习中还要涉及到。
申明游标语法
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||'吨位：'||v_pricetable.minnum||'到'||v_pricetable.maxnum);
end loop；
close cur_pricetable;
附：步骤
- PLSQL结构
- 声明游标
- 打开--关闭游标
- 循环取记录
带参数的游标
声明的时候指定参数的类型和名称，打开游标的时候需要传入指定类型的参数即可。
案例：
declare
v_pricetable T_PRICETABLE%rowtype;-- 价格行对象
cursor cur_pricetable(v_ownertypeid number) is select *from T_PRICETABLE where ownertypeid=v_ownertypeid;-- 定义游
标
begin
open cur_pricetable(2);-- 打开游标
loop
fetch cur_pricetable into v_pricetable;-- 提取游标到变量
exit when cur_pricetable%notfound;-- 当游标到最后一行下面退出循环
dbms_output.put_line('价格:'||v_pricetable.price ||'吨位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum );
end loop;
close cur_pricetable;-- 关闭游标
end ;
For循环游标[掌握使用]
declare
begin
for v_pricetable in cur_pricetable(3)
end
存储函数/存储过程
存储函数
Oracle中提供的使用PLSQL语言自定义的一些函数称之为存储函数
语法格式
CREATE [or replace] function 函数名称
（参数名称 参数类型，参数名称 参数类型，...）
return 结果变量数据类型
is
变量声明部分
begin
逻辑部分
return 结果变量
[Exception]
end；
案例
--通过ID查找小区
create or replace 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;
实际使用语句
select id,name,fn_getaddress(addressid) from t_owners
存储过程
概念
存储过程和存储函数差不多，和存储函数相比，它没有返回值，可以通过传出参数返回多个值；存储过程不能在select语句中直接使用，它多数是被应用程序调用。
存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：
- 存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值。
- 存储函数可以在 select 语句中直接使用，而存储过程不能。过程多数是被应用程序所调用。
- 存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。
语法格式
create [or replace] procedure procedure名称 (参数名 参数类型，...)
is[as]--都可以用
变量声明
begin
具体逻辑
[Exception]
end;
参数只指定类型，不指定长度
过程参数的三种模式：
IN 传入参数（默认）
OUT 传出参数 ，主要用于返回程序运行结果
IN OUT 传入传出参数
案例
--创建不带传出参数的存储过程：添加业主信息
--增加业主信息
create or replace procedure pro_owners_add()
使用
--调用不带参数的存储过程
--一
call 存储过程名(实际参数。。。);
--二
begin
存储过程的名称（实参）
end;
用JDBC调用
--带传出参数的存储过程
create or replace procedure pro_owners_add()
触发器
已经淘汰，现在都用消息队列
对特定表（增删改---生产者消费者思想---中介思想）
概念： 数据库触发器就是一段sql程序
触发器可用于
- 数据确认
- 实施复杂的安全性检查
- 做审计，跟踪表上所做的数据操作等
- 数据的备份和同步
触发器的分类
- 前置触发器（BEFORE）
- 后置触发器（AFTER）
面向切面的AOP思想
创建触发器的语法
create [or replace] trigger 触发器名
before|after
[delete][[or] insert][[or]update[of 列名,...]]
on 表名
[for each row][when(条件)]
declare
...
begin
PLSQL 块
end；
--for each row的作用是标准此触发器是行级触发器，不写的话是语句级触发器
