08oraclePLSQL基础知识

1、PL/SQL

1.1、什么是 PL/SQL

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

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

1.2、变量

声明变量的语法：

变量名 类型（长度）；

变量赋值的语法：

变量名:=变量值

变量的声明
需求：
声明变量水费单价、水费字数、吨数、金额。
对水费单价、字数、进行赋值 。吨数根据水费字数换算，规则为水费字数除以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;

1.3、属性类型

1.3.1、%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;
 --v_usenum:=8090;
 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;

1.3.2、%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--将查询到的一行赋值给v_account
 where year='2012' and month='01' and owneruuid=1;
 --使用吨数
 v_usenum2:= round(v_account.usenum/1000,2);--通过v_account.出来每一列的值
 --计算金额
 v_money:=v_price*v_usenum2;
 DBMS_OUTPUT.put_line('单价：'||v_price||'吨数：'
 ||v_usenum2||'金额：'||v_money||'上月字数：
'||v_account.num0||'本月字数'||v_account.num1);
end;

1.4、异常

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

1.4.1、异常有两种类型：

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

1.4.1.1、预定义异常

Oracle 预定义异常 21 个

命名的系统异常	产生原因
ACCESS_INTO_NULL	未定义对象
CASE_NOT_FOUND	CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时
COLLECTION_IS_NULL	集合元素未初始化
CURSER_ALREADY_OPEN	游标已经打开
DUP_VAL_ON_INDEX	唯一索引对应的列上有重复的值
INVALID_CURSOR	在不合法的游标上进行操作
INVALID_NUMBER	内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND	使用 select into 未返回行
TOO_MANY_ROWS	执行 select into 时，结果集超过一行
ZERO_DIVIDE	除数为 0
SUBSCRIPT_BEYOND_COUNT	元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT	使用嵌套表或 VARRAY 时，将下标指定为负数
VALUE_ERROR	赋值时，变量长度不足以容纳实际数据
LOGIN_DENIED	PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ON	PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
PROGRAM_ERROR	PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包
ROWTYPE_MISMATCH	宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL	使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR	运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID	无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE	Oracle 在等待资源时超时

语法结构：

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.5、条件判断

基本语法 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_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;

1.6、循环

1.6.1、无条件循环

语法结构

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 ;

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

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

1.7、游标

1.7.1、什么是游标

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

1.7.2、语法结构及示例

在声明区声明游标，语法如下：

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 ;

1.7.3、带参数的游标

我们的查询语句的条件值有可能是在运行时才能决定的，比如性业主类型，可能是运行时才可以决定，那如何实现呢？我们接下来学习带参数的游标，修改
上述案例

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 ;

1.7.4、 for 循环提取游标值

我们每次提取游标，需要打开游标 关闭游标 循环游标 提取游标 控制循环的退出等等，好麻烦！有没有更简单的写法呢？有！用 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 ;