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;