oracle-PL/SQL

迷失的小鹿308
222 阅读6分钟

PL/SQL

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

基本语法结构

image.png

变量

声明变量的语法： 变量名 类型（长度）;

变量赋值的语法：

变量名:=变量值

变量的声明

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

输出 image.png

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 引用型

作用：引用某表某列的字段类型

--属性类型 （引用型  表名.列名%type）
declare
  v_price number(10,2);--单价
  v_usenum t_account.usenum%type;--水费字数
  v_usenum2 number(10,2);--吨数
  v_money number(10,2);--金额
  v_num0 t_account.num0%type;--上月水表数
  v_num1 t_account.num1%type;--本月水表数
begin
  v_price:=2.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_usenum||'金额：'||v_money);

end;

%ROWTYPE 记录型 ，上例中的例子可以用下面的代码代替 作用：标识某个表的行记录

--属性类型 （记录型  表名%rowtype  ）
declare
  v_price number(10,2);--单价
 
  v_usenum2 number(10,2);--吨数
  v_money number(10,2);--金额
  v_account t_account%rowtype;--台账行记录类型 
begin
  v_price:=2.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_account.usenum||'金额：'||v_money);

end;

异常

在运行程序时出现的错误叫做异常

发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分 异常有两种类型：

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

预定义异常

Oracle 预定义异常 21 个

命名的系统异常产生原因
ACCESS_INTO_NULL未定义对象
CASE_NOT_FOUNDCASE 中若未包含相应的 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_DENIEDPL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ONPL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
PROGRAM_ERRORPL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包
ROWTYPE_MISMATCH宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID无效的 ROWID 字符串
TIMEOUT_ON_RESOURCEOracle 在等待资源时超时

语法结构：

image.png

--异常处理1
declare
  v_price number(10,2);--单价
 
  v_usenum2 number(10,2);--吨数
  v_money number(10,2);--金额
  v_account t_account%rowtype;--台账行记录类型 
begin
  v_price:=2.45;--单价赋值
  --从数据库中提取
  select * into v_account from t_account 
  where year='2012' and month='01' and owneruuid=200;
  
  v_usenum2:=round( v_account.usenum/1000,2);--吨数
  v_money:=v_price*v_usenum2;--金额
  
  DBMS_OUTPUT.put_line('字数：'||v_account.usenum||'金额：'||v_money);

exception
  when no_data_found  then
    DBMS_OUTPUT.put_line('没有找到账务数据');
end;



--异常处理2
declare
  v_price number(10,2);--单价
 
  v_usenum2 number(10,2);--吨数
  v_money number(10,2);--金额
  v_account t_account%rowtype;--台账行记录类型 
begin
  v_price:=2.45;--单价赋值
  --从数据库中提取
  select * into v_account from t_account 
  where year='2012' and month='01' ;
  
  v_usenum2:=round( v_account.usenum/1000,2);--吨数
  v_money:=v_price*v_usenum2;--金额
  
  DBMS_OUTPUT.put_line('字数：'||v_account.usenum||'金额：'||v_money);

exception
  when no_data_found  then
    DBMS_OUTPUT.put_line('没有找到账务数据');
  when too_many_rows then
    DBMS_OUTPUT.put_line('返回多行账务数据');
end;

条件判断

基本语法 1

image.png

基本语法2

image.png

基本语法3

image.png

--条件判断
declare
  v_price1 number(10,2);--单价
  v_price2 number(10,2);--单价
  v_price3 number(10,2);--单价
 
  v_usenum2 number(10,2);--吨数
  v_money number(10,2);--金额
  v_account t_account%rowtype;--台账行记录类型 
begin
  v_price1:=2.45;--单价赋值（5吨以下）
  v_price2:=3.45;--单价赋值 （5-10吨）
  v_price3:=4.45;--单价赋值 （超过10吨）
  
  --从数据库中提取
  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;--金额
  
  --阶梯水费计算
  
  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_account.usenum||'金额：'||v_money);

exception
  when no_data_found  then
    DBMS_OUTPUT.put_line('没有找到账务数据');
  when too_many_rows then
    DBMS_OUTPUT.put_line('返回多行账务数据');
end;

循环

无条件循环

语法结构

image.png

--无条件循环:1 到100

declare
  v_num number;  
begin
  v_num:=1;
  loop
    dbms_output.put_line(v_num);
    v_num:=v_num+1;  
    
    exit when v_num>100;
    
  end loop;
end ;

条件循环

--有条件循环：1 到100
declare
  v_num number;
begin
  v_num:=1;
  
  while  v_num<=100
  loop
     dbms_output.put_line(v_num);
     v_num:=v_num+1;  
  
  end loop; 
end;

for循环

image.png

-- 输出1到100的数
begin
  
  for v_num in  1 .. 100  
  loop
    dbms_output.put_line(v_num);
  end loop;

end;

游标

什么是游标

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

image.png

语法结构及示例

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

cursor 游标名称 is SQL 语句;

使用游标语法

image.png

需求：打印业主类型为 1 的价格表

-- 游标  输出结果集
declare
  cursor cur_pricetable is select * from t_pricetable where ownertypeid=1;--声明游标
  v_pricetable t_pricetable%rowtype;
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;

输出结果如下：

image.png

带参数的游标

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

--带参数的游标
declare
  cursor cur_pricetable(v_type number) 
         is select * from t_pricetable where ownertypeid=v_type;--声明游标
  v_pricetable t_pricetable%rowtype;
begin
  
  open cur_pricetable(1);--打开游标时指定参数值
  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;

输出结果

image.png

for 循环提取游标值

我们每次提取游标，需要打开游标 关闭游标 循环游标 提取游标 控制循环的 退出等等，好麻烦！有没有更简单的写法呢？有！用 for 循环一切都那么简单， 上例的代码可以改造为下列形式

--for循环 带参数的游标
declare
  cursor cur_pricetable(v_type number) 
         is select * from t_pricetable where ownertypeid=v_type;--声明游标
  --v_pricetable t_pricetable%rowtype;
begin
  
  for v_pricetable in cur_pricetable(1)
  loop
    DBMS_OUTPUT.put_line('价格:'||v_pricetable.price||'吨数：'
          ||v_pricetable.minnum||'-'||v_pricetable.maxnum); 
  end loop;  
 
end;

输出结果如下：

image.png

avatar
文章
阅读
粉丝