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;
看完恭喜你,又知道了一点点!!!
你知道的越多,不知道的越多!
~感谢志同道合的你阅读, 你的支持是我学习的最大动力 ! 加油 ,陌生人一起努力,共勉!!
