pl/sql
目录
变量和常量
%type 列类型
%rowtype 行类型
record 记录类型
table 表类型
条件判断
if /else
case when
循环
loop循环
while循环
for循环
异常
事务处理
pl/sql是什么
sql是结构化查询语言。sql是不是一个编程语言？编程语言一般都能够声明变量，写条件判断，循环。sql不具备这些特征，所有sql不是一门编程语言。我们在实际的开发中，有这种需要，把sql语句和编程语言的特性结合起来。 oracle 就提供了pl/sql这种语言来解决这种问题。使用pl/sql有什么优点： 可以提高程序的运行效率，因为能够减少数据库的访问次数 可以对复杂的业务逻辑进行封装
pl/sql ，结构性查询语言，却具有编程语言的特性，可以写if else 实现复杂的业务逻辑
pl/sql匿名块的结构：
declare --变量声明部分 begin -- 业务逻辑处理部分 exception -- 异常处理部分 end;
注意：
1，pl/sql块 变量名，关键字不区分大小写。 2，变量声明部分是可选的。业务逻辑处理部分是必须的。异常处理部分是可选的。 3，end后面必须加 ''；" declare begin exception 后面都不要加； 一般写匿名块都是先写结构，再往结构里面填代码。
declare
-- 变量声明部分
v_ename varchar2(100);
begin
-- 业务逻辑执行部分
select ename into v_ename from emp where empno=&请输入员工编号;
-- 把员工姓名打印到控制台
dbms_output.put_line('员工姓名:'||v_ename);
exception
-- 异常处理部分
when no_data_found then
dbms_output.put_line('您输入的员工编号不存在');
end;
变量和常量
声明变量 ： 变量名 变量类型； 变量名一般建议以v_开头. 声明常量要加上constant关键字： 常量名 constant 常量类型；
变量类型
%type 列类型
声明变量的时候，该变量参考某个表的某个列的类型
declare
-- 变量声明部分 v_ename 类型参考emp表的ename的类型
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
-- 业务逻辑执行部分
select ename,sal into v_ename,v_sal from emp where empno=&请输入员工编号;
-- 把员工姓名打印到控制台
dbms_output.put_line('员工姓名:'||v_ename||',基本工资:'||v_sal);
exception
-- 异常处理部分
when no_data_found then
dbms_output.put_line('您输入的员工编号不存在');
end
%rowtype 行类型
参数某个表的类型。%rowtype中可以存储多个数据，具体可存多少个和表的字段数一样。
declare
-- 声明行类型变量
v_emp emp%rowtype;
begin
-- 业务逻辑执行部分
select * into v_emp from emp where empno=&请输入员工编号;
-- 把员工姓名打印到控制台
dbms_output.put_line('员工姓名:'||v_emp.ename||',基本工资:'||v_emp.sal||',奖金:'||v_emp.comm
||'雇佣日期:'||v_emp.hiredate);
exception
-- 异常处理部分
when no_data_found then
dbms_output.put_line('您输入的员工编号不存在');
end;
record 记录类型
record可以理解为可以自定义的行类型。 record类型在使用之前必须声明该类型
注意：只能存储一行数据
declare
--声明record类型
type emp_record_type is record(
ename emp.ename%type,
sal emp.sal%type,
total emp.sal%type
);
--声明record类型的变量
v_emp emp_record_type;
begin
--查询数据，并给变量赋值
select ename,sal,sal+nvl(comm,0)
into v_emp from emp where empno=&no;
dbms_output.put_line('姓名'||v_emp.ename||
',基本工资'||v_emp.sal
||',总工资'||v_emp.total);
end;
table 表类型
无论是行类型还是记录类型只能存储一行数据，如果要存储多行数据，需要用到table类型。table类型也需要先声 明类型，再声明该类型的变量 注：可以存储多行数据
declare
-- 声明table类型 is table of 后可以跟行类型，也可以是记录类型
type emp_table_type is table of emp%rowtype
-- 指定下标的增长方式为整数，每次增长1
index by binary_integer;
-- 声明table类型的变量
v_emp emp_table_type;
begin
-- 把编号为7369的员工的信息存储到table类型变量中
select * into v_emp(1) from emp where empno=7369;
-- 把编号为7499的员工的信息存储到table类型变量中
select * into v_emp(2) from emp where empno=7499;
--数据table类型变量中的数据
dbms_output.put_line('员工姓名:'||v_emp(1).ename||',基本工资:'||v_emp(1).sal);
dbms_output.put_line('员工姓名:'||v_emp(2).ename||',基本工资:'||v_emp(2).sal);
end;
条件判断
if /else
语法：
if 条件表达式 then
语句块；
elsif 条件表达式 then
语句块；
...
else
语句块；
end if;
查询：输入一个员工编号，给该员工涨奖金。策略是这样的： 如果原来员工没有奖金，则把基本工资的百分之10作为奖金，如果原来员工的奖金低于1000，把奖金提升到 1000，其他情况奖金提升百分之10
declare
-- 声明奖金的变量
v_comm emp.comm%type;
begin
-- 查询出员工的奖金
select comm into v_comm from emp where empno=&no;
-- 判断如果员工没有奖金，把基本工资的百分之10作为奖金
if v_comm is null then
update emp set comm=sal*0.1 where empno=&no;
--如果原先奖金低于1000，提升到1000
elsif v_comm<1000 then
update emp set comm=1000 where empno=&no;
-- 其他情况把奖金提升百分之10
else
update emp set comm=comm*1.1 where empno=&no;
end if;
case when
语法：
case 变量名
when 变量值1 then
语句块；
when 变量值2 then
语句块；
........
else:
语句块;
end case;
案例
declare
v_deptno emp.deptno%type:=&no;
begin
case v_deptno
when 10 then
dbms_output.put_line('技术部');
when 20 then
dbms_output.put_line('销售部');
when 30 then
dbms_output.put_line('公关部');
when 40 then
dbms_output.put_line('开发部');
else
dbms_output.put_line('输入有误');
end case;
end;
循环
loop循环
语法
loop
--条件判断部分
--判断循环条件，满足后跳出
if then
--跳出循环
exit;
end if;
--
--循环语句部分
end loop;
案例：100内的自然数
--100内的素数
declare
--声明循环变量
v_i number(8):=1;
begin
loop
--判断循环条件，满足后跳出
if v_i>100 then
--跳出循环
exit;
end if;
--输出100内的自然数
dbms_output.put_line(v_i);
--改变循环变量的值
end loop;
end;
遍历出表中数据
批量效率的读取游标数据 bulk collect into
declare
--声明table类型
type dept_table_type is table of dept%rowtype
index by binary_integer;
--声明table类型的变量，用来存储部门的所有数据
v_dept dept_table_type;
--声明循环变量
v_i number(8):=1;
begin
--查询所有部门表的内容，放到v_dept变量中
--bulk collect into
select * bulk collect into v_dept from dept;
--循环出v_dept变量中的值
loop
--判断循环条件
if v_i>v_dept.count then
exit;
end if;
--控制台输出
dbms_output.put_line(
'部门编号:'||v_dept(v_i).deptno
||'部门名称'||v_dept(v_i).dname
||'部门位置'||v_dept(v_i).loc);
--改变循环变量的值
v_i:=v_i+1;
end loop;
end;
while循环
语法
while --条件
loop
--循环语句
end loop;
遍历出表中数据
declare
--声明table类型
type dept_table_type is table of dept%rowtype
index by binary_integer;
--声明table类型的变量，用来存储部门的所有数据
v_dept dept_table_type;
--声明循环变量
v_i number(8):=1;
begin
--查询所有部门表的内容，放到v_dept变量中
--bulk collect
select * bulk collect into v_dept from dept;
--条件
while v_i<=v_dept.count
--循环
loop
--控制台输出
dbms_output.put_line(
'部门编号:'||v_dept(v_i).deptno
||'部门名称'||v_dept(v_i).dname
||'部门位置'||v_dept(v_i).loc);
--改变循环变量的值
v_i:=v_i+1;
end loop;
end;
for循环
注意：for循环，循环变量可以直接使用，不用声明。in 包含循环变量的开始值和结束值
语法
--for不需要声明循环变量
for 循环变量 in 循环变量的开始值..循环变量的结束值
loop
end loop;
100内的自然数 for
begin
--循环变量不需要声明
for v_i in 1..100
loop
dbms_output.put_line(v_i);
end loop;
end;
100内的素数 for
declare
v_i number(8);
v_j number(8):=1;
v_n number(8):=0;
begin
for v_i in 1..100
loop
while v_j<=v_i
loop
if mod(v_i,v_j)=0 then
v_n:=v_n+1;
end if;
v_j:=v_j+1;
end loop;
v_j:=0;
if v_n=2 then
dbms_output.put_line(v_i);
end if;
v_n:=0;
end loop;
end;
遍历出表中数据 for
declare
--声明table类型
type dept_table_type is table of dept%rowtype
index by binary_integer;
--声明table类型的变量，用来存储部门的所有数据
v_dept dept_table_type;
begin
--查询所有部门表的内容，放到v_dept变量中
--bulk collect
select * bulk collect into v_dept from dept;
--循环出v_dept变量中的值 for
for v_i in 1..v_dept.count
loop
--控制台输出
dbms_output.put_line(
'部门编号:'||v_dept(v_i).deptno
||'部门名称'||v_dept(v_i).dname
||'部门位置'||v_dept(v_i).loc);
end loop;
end;
异常
内置异常
no_data_found
处理
有异常
when no_data_found then
如何处理
除数为0的异常
zero_divide
行数过多
too_many_rows
出现了其他异常
others
案例
根据员工编号查询员工姓名
declare
v_ename emp.ename%type;
v_num number(8);
begin
--v_num:=1/0;
v_num:=123465671256511;
select ename into v_ename from emp;
dbms_output.put_line('员工姓名：'||v_ename);
exception
when no_data_found then
dbms_output.put_line('没有找到该员工');
when zero_divide then
dbms_output.put_line('除数不能为0');
when too_many_rows then
dbms_output.put_line('返回行数过多');
when others then
dbms_output.put_line('有错误');
end;
自定义异常
语法
declare
--声明异常变量
--变量名称 exception
begin
--抛出异常
raise 变量
exception
-- 处理异常
when 变量名称 then
业务处理
end;
案例
根据员工编号删除员工信息，如果删除的员工不存在，抛出异常，在控制台打印没有删除成功
declare
--声明异常变量
v_myex exception;
begin
--slq语句
delete from emp where empno=&编号;
--sql 隐含游标的名称 notfound 是游标的属性 没有找到数据
if sql%notfound then
--抛出异常
raise v_myex;
end if;
exception
--处理异常
when v_myex then
dbms_output.put_line('编号错误');
end;
事务处理
事务的acid特性
原子性
数据库增删改，要么成功，要么失败
一致性
必须符合业务逻辑。有增，就有减。
隔离性
多个数据库操作之间不能产生干扰。变动一个带动另一个不相关的是不行的。
持久性
数据应永久持续化的保存。
pl/sql的事务操作方式
在pl/sql中，可以定义事物的回顾点，可以回顾事物，也可以提交事物。 定义回滚点 savepoint 回滚点名称； 回滚事物
begin
--事务回滚点
savepoint a;
insert into dept values(1,'aaa','aaa');
insert into dept values(2,'www','aaa');
insert into dept values(3,'eee','aaa');
insert into dept values(4,'rrr','aaa');
--回滚事务
rollback to a;
--insert into dept values(4,'rrr','aaa');
--提交事务
--commit;
end;
事务死锁
两个事务执行的条件冲突时会出现死锁。
roacle中锁的概念
oracle中使用锁的机制来实现事物。oracle中分两种常见的锁 排他锁，共享锁。
增删改数据库记录的时候，就会在操作记录的行上加上排他锁。
查询的时候会在查询的记录上加上共享锁
