4.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;
|
输出结果
Tips:输出方式:查看
在下方会出现DBMS输出
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 ownerid=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.属性类型
%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 ownerid=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 ownerid=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; |
1.4.异常
在运行程序时出现的错误叫做异常
发生异常后,语句将停止执行,控制权转移到 PL/SQL 块的异常处理部分
异常有两种类型:
预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常,自定义的异常通过 RAISE 语句显式引发
预定义异常
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 应用程序在没有连接 racle 数据库的情况下访问数据 |
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 ownerid=1 and year='2012' and month='00'; --字数换算为吨数 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 ownerid=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.无条件循环
语法结构
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 ; |
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; |