Oracle(四)-PL/SQL语法

人生里看似偶然却又必经的告别，无约而至，无人可免。

许嵩-<如约而至>

---

一、PL/SQL

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

--声明变量
变量名 类型(长度);
--变量赋值
变量名:=变量值

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

--变量的用法--
set serveroutput on;--开启输出
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 条件
--开启输出
set serveroutput on
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 引用型: 引用某表某列的字段类型
--开启输出
set serveroutput on
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 记录型: 标识某个表的行记录类型.
--开启输出
set serveroutput on
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;

A、异常

--异常
--预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
--用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发
--语法:
exception
when 异常类型 then
异常处理逻辑
--变量的用法--
--上面的案例后面(end;之前)加
exception
when NO_DATA_FOUND then
    dbms_output.put_line('未找到数据，请核实');
when TOO_MANY_ROWS then
    dbms_output.put_line('查询条件有误，返回多条信息，请核实');
end;

B、条件判断

--语法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 ，根据使用水费的量来计算阶梯水费。
--开启输出
set serveroutput on
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_price1*5 + v_price2*(v_usenum2-5);
  else --第三个阶梯
        v_money:=v_price1*5+v_price2*5+v_price3*(v_usenum2-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;

C、循环

--无条件循环
loop
    循环语句
end loop;
--案例:输出从1开始的100个数
declare 
  v_num number:=1;
begin
  loop
    dbms_output.put_line(v_num);
    v_num:=v_num+1;
    exit when v_num>100;
  end loop;
end;

--条件循环
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;  

--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;

D.游标

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

--语法
cursor 游标名称 is SQL 语句;
open 游标名称
loop
fetch 游标名称 into 变量
exit when 游标名称%notfound
end loop;
close 游标名称
--打印业主类型为 1 的价格表
declare
  v_pricetable t_pricetable%rowtype;--价格行对象
  cursor cur_pricetable is select * from t_pricetable where ownertypeid=1;--定义游标
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;--关闭游标
end;    

--带参数的游标
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
cursor cur_pricetable(v_ownertypeid number) is select *
from T_PRICETABLE where ownertypeid=v_ownertypeid;--定义游
标
begin
for v_pricetable in cur_pricetable(3)
loop
dbms_output.put_line('价格:'||v_pricetable.price ||'吨
位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum );
end loop;
end ;