Oracle编程

一、PL/SQL

（一）什么是 PL/SQL

PL/SQL（Procedure Language/SQL）是 Oracle 对 sql 语言的过程化扩展，指
在 SQL命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有
过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起
来，使得 PLSQL面向过程但比过程语言简单、高效、灵活和实用。

基本语法结构

[declare
   --声明变量
]
begin
  --代码逻辑
[exception
   --异常处理
   ]
 end;

（二）变量

声明变量的语法：

变量名 类型(长度);

变量赋值的语法:

变量名:=变量值

变量的声明
 

需求：
声明变量水费单价、水费字数、吨数、金额。
对水费单价、字数、进行赋值 。吨数根据水费字数换算，规则为水费字数除以
1000，并且四舍五入，保留两位小数。计算金额，金额=单价*吨数。
输出单价 、数量和金额

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:=8012;--字数
  --字数换算为吨数
  v_usenum2:=round(v_usenum/1000,2);
  --计算金额
  v_money:=round(v_price*v_usenum2,2);
  dbms_output.put_line('单价:'||v_price||'吨数
  :'||v_usenum2||'金额:'||v_money);
  end;

Select into方式 赋值

语法结构：

select 列名 into 变量名 from 表名 where 条件

注意：结果必须是一条记录 ，有多条记录和没有记录都会报错

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:=3.45;
  --变量赋值
  select usenum,num0,num1 into v_usenum,v_num0,v_num1
  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_price||'吨数:'||v_usenum2
  ||'金额:'||v_money||'上月字数:'||v_num0||'本月字数:'||v_num1);
  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:=3.45;
  select usenum,num0,num1 into v_usenum,v_num0,v_num1 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_price||'吨数:'||v_usenum2||
  '金额:'||v_money||'上月字数:'||v_num0||'本月字数:'||v_num1);
  end;

 %ROWTYPE   记录型  ，上例中的例子可以用下面的代码代替

作用:  标识某个表的行记录类型

declare
  v_price number(10,2);--单价
  v_account t_account%rowtype;--记录型
  v_usenum2 number(10,2);--使用吨数
  v_money number(10,2);--水费金额
begin
  --对单价进行赋值
  v_price:=3.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_price||'吨数:'||v_usenum2||'金额:'||
  v_money||'上月字数:'||v_account.num0||'本月字数:'||v_account.num1);
end;

（四）异常

在运行程序时出现的错误叫做异常
发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分
异常有两种类型：

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

预定义异常

Oracle预定义异常 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;

（五）条件判断

基本语法 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 ，根据使用水费的量来计算阶梯水费。 

declare
  v_price1 number(10,2);--不足5吨的单价
  v_price2 number(10,2);--超过5吨不足10吨单价
  v_price3 number(10,2);--超过10吨的单价
  v_account t_account%rowtype;--记录型
  v_usenum2 number(10,2);--使用吨数
  v_money number(10,2);--水费金额
begin
  --对单价进行赋值
  v_price1:=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_price1*v_usenum2;
  elsif v_usenum2>5 and v_usenum2<=10 then --第二个阶梯
    v_money:=v_price2*(v_usenum2-5)+v_price1*5;
  else --第三个阶梯
    v_money:=v_price1*5+v_price2*5+v_price3*(v_account.usenum-10);
  end if; 
  
  dbms_output.put_line('吨数：' 
  ||v_usenum2||'金额：'||v_money||'上月字数：
'||v_account.num0||'本月字数'||v_account.num1);
exception
  when no_data_found then
    dbms_output.put_line('没有找到数据');
  when too_many_rows then
    dbms_output.put_line('返回的数据有多行');
end;

（六）循环

1．无条件循环  
 语法结构

loop 

  循环语句 

end loop;

--范例：输出从1开始的100个数
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;

2、条件循环

语法结构

while 条件

loop

end loop;

--范例：输出从1开始的100个数
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;

3、for循环

基本语法

for 变量 in 起始值..终止值
loop
 
end loop;

--范例：输出从1开始的100个数 
begin
  for v_num in 1..100
  loop
    dbms_output.put_line(v_num);
  end loop;
end;