PL/sql

1.什么是PL/SQL?

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

 

基本语法结构

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

 

 

2.变量

-- 声明变量的语法：
变量名 类型（长度）；
i varchar2(20);


--变量赋值的语法
变量名 :=变量值
i :="煌煌"

 

举例使用:

--入门练习1;打印"welcome!!"
begin 
  dbms_output.put_line("welcome!!!");
  end;



--实例1;定义变量 i,并赋值 "你好",输出
--变量类型,一定要支持赋值的长度
declare 
 i varchar2(20);
begin 
   i :="你好";
   dbms_output.put_line("输出内容是:" || i);
end;   




--多个变量
declare
 sname varchar2(20):="huangsir";
 age int:=18;
 sex varchar2(20):="男";
 begin 
   dbms_output.put_line("姓名:" ||sname || ",年龄:" ||age || ",性别:" ||sex);
 end;

 

如何执行查看呢?

 

 

Select into方式 赋值

语法结构： into关键字是能用在过程中

select 列名1，列名2  into  变量名1，变量名2  from 表名 where 条件

举例:

--练习1;打印业主名称
declare 
  --声明变量,用于存放name的值
  v_name varchar2(50);

begin
  --执行查询语句,获得name的值,并赋值变量
  select  name into v_name from t_owners where id=11;
  --打印变量
dbms_output.put_line(v_name);
end;

 

3. 变量类型 %type

如果自定义类型大小,如果设置的太小会出现数字或值错误:字符缓存区太小的错误
所以可用%type自己引用表类型即可

语法:

-- 语法
变量名  表名.列名%type;
-- 实例
v_name towners.name%type;

 

案例:

--练习2;打印业主名称
declare 
  --声明变量,用于存放name的值
  v_name t_owners.name%type;    --使用%type  可以不用自定义数据类型,直接从表数据获取类型

begin
  --执行查询语句,获得name的值,并赋值变量
  select  name into v_name from t_owners where id=11;
  --打印变量
dbms_output.put_line(v_name);
end;

 

练习:

--练习3:  需求：计算台账表中编号为1的用户当月用水金额。
      --要求输出：单价、吨数、金额、上月用水量、本月用水量

declare
 v_price number; --单价
 v_num0 number;   --t_account.num0%type   上月累计用水量
 v_num1 number;   --t_account.num1%type   本月累计用水量
 v_usenum number;  --t_account.usenum%type    使用量
 v_money number;  --金额
 v_usenum2 number;  --吨数
 
begin
  v_price:=3.45; --单价赋值
  --查询数据;获得用水量,金额
 select num0,num1,usenum,money into v_num0,v_num1,v_usenum,v_money from t_account where id = 45;
    --吨数 19920 /1000 ->> 19.920 -->19.92
    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   记录型 

• 作用:  标识某个表的行记录类型
• 通过%rowtype获得某一行的所有类型,相当于一个对象,存放一条记录

声明变量 :

--语法
变量 表名%rowtype;
--实例
v_account t_account%rowtype;

 

赋值 :

-- 单列
select 列名,列名2,... into 变量.列名,变量.列名2,...  from 表名;
-- 所有
select * into 变量  from 表名;

 

示例：重写上个练习：使用%rowtype

declare 
 --单价
  v_price number;
  --声明行变量
  v_account t_account%rowtype;
begin 
   --单价赋值
   v_price :=3.45;
   --保存-行数
   select * into v_account from t_account where id=45;
   --打印
   dbms_output.put_line("单价"||v_price ||",金额"||v_account.money ||",上月用水量"
   ||v_account.num0 ||",本月用水量"||v_account.num1);
   end;

 

 

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 应用程序在没有连接 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 在等待资源时超时

 

 

语法:

-- 语法
declare
begin
	-- 正常代码
	-- 异常块
	exception
		when 异常类型 then
			处理
		when 异常类型 then
			处理
		....
end;
-- 常见异常类型：
no_date_found , 没有数据异常，查询结果为null
too_many_rows，太多行，查询结果大于1条

 

练习4：查询台账表，id为1所有信息，并打印部分 -- 异常情况--没有数据

-- 异常处理
-- 练习4：查询台账表，id为1所有信息，并打印部分
declare 
  v_account t_account%rowtype;
begin 
  --查询
  select * into v_account from t_account where id = 999;
  --打印
  dbms_output.put_line("上月用水量" || 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("查询结果大于1条");
end;

 

练习4：查询台账表，id为1所有信息，并打印部分 -- 异常情况--查询多条数据

declare 
  v_account t_account%rowtype;
begin 
  --查询
  select * into v_account from t_account ; --少了where id=45 ,数据有多条
  --打印
  dbms_output.put_line("上月用水量" || 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("查询结果大于1条");
end;

 

 

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 
   --定义3个水费价格
   v_price1 number;
    v_price2 number;
    v_price3 number;
   --查询台账记录
   v_account t_account%rowtype;
   --使用量(吨数)
   v_usenum2 number;
   --金额
   v_money number;
begin
    --确定水费价格
    v_price1:=2.45;
     v_price2:=3.45;
      v_price3:=4.45;
    --查询记录
    select  * into v_account from t_account where id=45;
    --计算使用量
     v_usenum2:=round(v_account.usenum/1000,2);
     
    
    --根据使用量,计算阶梯水费
    
    if v_usenum2<=5 then
    --小于5吨 ,  使用量*2.45
     v_money:=v_usenum2*2.45;
     elsif v_usenum2 <=10 then
    --小于10吨   5* 2.45 +(使用量-5) *3.45
      v_money:=5 *2.45 +(v_usenum2-5)*3.45;
    else
    --大于10吨   5*2.45 +5*3.45 +使用量-10) *4.45
    v_money:=5 *2.45+5*3.45+(v_usenum2-10)*4.45;
    end if;
    --打印水费
    dbms_output.put_line(v_money);
end;

 

 

6.循环

(1)无条件循环

语法:

--语法
loop
	-- 代码
	-- 结束循环
	exit;
end loop;

 

练习：输出从1开始的100个数

declare
 v_i int :=1;
begin
  loop
    dbms_output.put_line(v_i);
    --累加
    v_i:=v_i+1;
    -- 退出
   if v_i > 100 then
     exit;
     end if;
     end loop;
end;

 

 

(2)条件循环-while

语法:

--语法结构
while 条件
loop
  ...
end loop;

 

举例:求1-100和，并打印

declare
--计数器
 v_i int :=1;
 --求和变量
 v_sum int :=0;
begin 
  while v_i <=100
  loop
     --求和
     v_sum :=v_sum+v_i;
     
     --累加
     v_i:=v_i +1;
     end loop;
     --打印
     dbms_output.put_line(v_sum);
end;     

 

 

(3)for循环

语法:

for 变量  in 起始值..终止值
loop
  
end loop;

 

举例:

求1-100所有偶数的和,并打印

declare
--计数变量
v_i int;
 --求和变量
 v_sum int :=0;
begin
  for v_i in 1..100
    loop
      --mod()用于求余
     if  mod(v_i,2)= 0 then
      v_sum :=v_sum+v_i;
      end if;
    end loop;  
      dbms_output.put_line(v_sum); 
end;

 

 

 

练习前.需要知道的小知识:

declare
begin
  --一行输出
    dbms_output.put("123");
  --换行输出
  dbms_output.put_line("abc");

end;

 

练习: 99乘法表 :分步骤实现

--1.普通循环
declare 
 vi int;
begin
  --循环
  for vi in 1..9
    loop
      dbms_output.put_line(vi);
      end loop;
end;




--2.嵌套循环
declare 
 vi int;
 vj int;
begin 
  --嵌套循环
  for vi in 1..9
    loop
      --内部循环
     for vj in 1..9
       loop 
           dbms_output.put_line(vi || vj);
       end loop;
    
     end loop; 
end;      




---3.总

declare 
 v_i int;
 v_j int;
begin
  --嵌套循环
  for v_i in 1..9
    loop
      --内部循环
      for v_j in 1..v_i
        loop 
          dbms_output.put(v_j || "*" || v_i ||"=" ||(v_i*v_j) );
      dbms_output.put("   "); 
     end loop;
     dbms_output.put_line("");
     end loop;
end;

 

 

7.游标

(1)什么是游标?

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

• 游标就是将查询结果存放到缓存区，可以通过游标依次获得数据

 

 

语法

--在声明区声明游标，语法如下：
cursor 游标名称 is SQL语句;

 

 

declare
    --声明游标
    cursor 游标名称 is 查询语句;
begin
    open 游标名称;
    loop;
        -- 给游标指向当前行进行命名，方便获得当前行的数据
        fetch 游标名称 into 变量;
        -- 使用当前行数据
        -- 结束信息
        exit when 游标名称%notfound;
    end loop;
    close 游标名称;
end;

 

 

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

实现方式1:

--方式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);
 end loop;
   
   --关闭
   close cur_pricetable;
end;

 

 

实现方式2.for循环(简化)

declare
 --声明游标
 cursor cp is select * from t_pricetable where ownertypeid=1;
  --变量
  vp t_pricetable%rowtype;
begin
    --for遍历
     for vp in cp
       loop
         dbms_output.put_line(vp.price);
       end loop;  
end;

 

 

具有参数的游标

语法:

 --curso 游标名称(参数名称 类型) is  SQL语句..where id=参数名称;
 -- 在for vp in cp(想要传递的参数);

 

案例:

declare
 --声明游标
 cursor cp(v_ownertypeid number) is select * from t_pricetable where ownertypeid=v_ownertypeid;
  --变量
  vp t_pricetable%rowtype;
begin
    --for遍历
     for vp in cp(3)
       loop
         dbms_output.put_line(vp.price);
       end loop;  
end;

 

 

 

 

看完恭喜你,又知道了一点点!!!

你知道的越多,不知道的越多! 

~感谢志同道合的你阅读,  你的支持是我学习的最大动力 ! 加油 ,陌生人一起努力,共勉!!