--游标
游标是一种能从多条记录的结果集中每次提取一条记录的机制
可以充当指针 能遍历结果集中所有的行 但是每次只能指向一行
游标提供了在逐行的基础上操作表中数据的方法
游标的结果集是由查询语句定义
--游标的分类
静态游标:显式游标:普通的
带参数的
隐式游标
动态游标:强类型
弱类型
动态游标 --重点
--静态和动态游标的区别:
静态游标的结果集是固定的 中间不能改变
动态的结果集是可以改变的 每次打开都可以更换结果集
--显式游标和隐式游标的区别:
显式游标的结果集在declare部分声明 中途不能改变
隐式游标的结果集不用声明 且游标无需打开关闭 这些由系统完成
--静态--显式--普通的
声明一个显式游标
语法:
declare
cursor 游标名 is select 语句;
--打印dept表的数据
declare
cursor cur_1 is select * from dept;
v_1 dept%rowtype;
begin
open cur_1;--打开游标
fetch cur_1 into v_1;--提取记录给变量
dbms_output.put_line(v_1.dname||' '||v_1.deptno||' '||v_1.loc);
fetch cur_1 into v_1;--fetch一次打一行
dbms_output.put_line(v_1.dname||' '||v_1.deptno||' '||v_1.loc);
fetch cur_1 into v_1;--表内容,每列空格隔开
dbms_output.put_line(v_1.dname||' '||v_1.deptno||' '||v_1.loc);
fetch cur_1 into v_1;--游标命名cur_
dbms_output.put_line(v_1.dname||' '||v_1.deptno||' '||v_1.loc);
fetch cur_1 into v_1;--不够行会一直打印最后一行
dbms_output.put_line(v_1.dname||' '||v_1.deptno||' '||v_1.loc);
close cur_1;--关闭游标
end;
游标的四个属性:
cur_name%isopen 判断游标是否打开(对,错)--布尔型
cur_name%found 判断游标是否有值(对,错)--布尔型
cur_name%notfound 判断游标是否没值(对,错)--布尔型
cur_name%rowcount 返回游标指针指过的函数 --数值型
游标的循环:
普通循环+cur_name%notfound
while + cur_name%found
游标+ for 循环
--打印dept表的数据
declare
cursor cur_1 is select * from dept;
v_1 dept%rowtype;
begin
open cur_1;--打开游标
fetch cur_1 into v_1;--第一次提取
while cur_1%found
loop
dbms_output.put_line(v_1.dname||' '||v_1.deptno||' '||v_1.loc);
fetch cur_1 into v_1;
end loop;
close cur_1;
end;
普通循环的步骤:begin
open 游标->loop->fetch 提取->退出条件%notfound->打印->end loop->close
while 循环步骤:
open 游标->fetch 提取->while+执行条件%found->loop->打印->提取->end loop->close
--静态--显示--带参
declare
cursor cur_2(v_d number,v_j varchar2) is
select * from emp where deptno=v_d and job=v_j;
v_e emp%rowtype;
begin
open cur_2(20,'MANAGER');--打开游标的时候赋参
loop
fetch cur_2 into v_e;
exit when cur_2%notfound;
dbms_output.put_line(v_e.ename||' '||v_e.deptno||' '||v_e.job);
end loop;
close cur_2;
-------------------------------
open cur_2(20,'ANALYST');--重新打开游标的时候赋参
loop
fetch cur_2 into v_e;
exit when cur_2%notfound;
dbms_output.put_line(v_e.ename||' '||v_e.deptno||' '||v_e.job);
end loop;
close cur_2;
-------------------------------
open cur_2(30,'MANAGER');--重新打开游标的时候赋参
loop
fetch cur_2 into v_e;
exit when cur_2%notfound;
dbms_output.put_line(v_e.ename||' '||v_e.deptno||' '||v_e.job);
end loop;
close cur_2;
end;
--打印工作是MANAGER的姓名和工作
--工作是SALESMAN的姓名和佣金
--工作是CLERK的姓名和入职日期
declare
cursor cur_3(v_j varchar2) is
select * from emp where job=v_j;--游标内赋参条件和类型
v_e emp%rowtype;
begin
open cur_3('MANAGER');--打开游标赋参
loop--循环
fetch cur_3 into v_e;--提取
exit when cur_3%notfound;--退出
dbms_output.put_line(v_e.ename||' '||v_e.job);--打印表内对应列
end loop;--结束循环
close cur_3;
-------------------------------
open cur_3('SALESMAN');--重新打开赋参
loop
fetch cur_3 into v_e;
exit when cur_3%notfound;
dbms_output.put_line(v_e.ename||' '||v_e.comm);
end loop;
close cur_3;
-------------------------------
open cur_3('CLERK');--重新打开赋参
loop
fetch cur_3 into v_e;
exit when cur_3%notfound;
dbms_output.put_line(v_e.ename||' '||v_e.hiredate);
end loop;
close cur_3;
end;
--静态--隐式游标
不用声明 不同打开 不用赋值 不用关闭
常见方式有 delete update insert select...into..赋值
隐式游标的四个属性:
sql%isopen 判断游标是否打开 永远返回错
sql%found 判断指针是否有值 如果最近一次有值返回对 否则返回错
sql%notfound 判断指针是否没值 最近一次没有 返回对
sql%rowcount 返回指针指过的行数 返回最近一次的记录数
--创建emp1数据同emp;
--drop table emp1;
create table emp1 as select * from emp;
--
declare
v_e emp%rowtype;
begin
---------更新10部门数据-------------------
update emp1 set ename=initcap(ename) where deptno=10;
if sql%found then
dbms_output.put_line('真的更新了');
elsif sql%notfound then
dbms_output.put_line('没有更新');
end if;
dbms_output.put_line('更新了'||sql%rowcount||'行');
----------20部门删除-------------------
delete from emp1 where deptno=20;
if sql%found then
dbms_output.put_line('真的删除了');
elsif sql%notfound then
dbms_output.put_line('没有删除');
end if;
dbms_output.put_line('删除了'||sql%rowcount||'条');
-----------插入数据----------------------
insert into emp1 select * from emp;
if sql%found then
dbms_output.put_line('真的插入了');
elsif sql%notfound then
dbms_output.put_line('没有插入');
end if;
dbms_output.put_line('插入了'||sql%rowcount||'条');
-----------赋值------------------------
select * into v_e from emp where ename='SMITH';
if sql%found then
dbms_output.put_line('真的赋值了');
elsif sql%notfound then
dbms_output.put_line('没有赋值');
end if;
dbms_output.put_line('赋值了'||sql%rowcount||'行');
----------------------------------
if sql%isopen then
dbms_output.put_line('打开了');
else dbms_output.put_line('没有打开');
end if;
end;
--强类型和弱类型的区别
强类型有return open时for后面的结果集必须和return后面的保持一致
弱类型没有return open时for后面的结果集比较自由
--动态--动态
--打印今年所有的周末 每个人的名字和他上级的名字
declare
cur_1 sys_refcursor;--声明一个动态游标
v_m date;
v_1 varchar(20);
v_2 varchar(20);
begin
--今年所有周末
open cur_1 for
select * from (
select trunc(sysdate,'yyyy')-1+level ll
from dual
connect by level <=365)
where to_char(ll,'dy') in ('星期六','星期日');
loop
fetch cur_1 into v_m;
exit when cur_1%notfound;
dbms_output.put_line(v_m);
end loop;
close cur_1;
----每个人的名字和他上级的名字-------------
open cur_1 for
select a.ename,b.ename
from emp a
join emp b
on a.mgr=b.empno;
loop
fetch cur_1 into v_1,v_2;
exit when cur_1%notfound;
dbms_output.put_line(v_1||'经理是'||v_2);
end loop;
close cur_1;
end;
--游标+for循环
1.带游标名的
声明一个显式游标 不用打开 不用赋值 不用关闭
declare
cursor cur_1 is select * from dept;
begin
for i in cur_1
loop
dbms_output.put_line(i.dname||' '||i.deptno||' '||i.loc);
end loop;
end;
2.不带游标名的
不用声明 不用打开 不用赋值 不用关闭
begin
for i in (select * from dept)
loop
dbms_output.put_line(i.dname||' '||i.deptno||' '||i.loc);
end loop;
end;
--打印每个部门每种职位的人数
begin
for i in (select deptno,job,count(job) 人数 from emp group by deptno,job)
loop
dbms_output.put_line(i.deptno||' '||rpad(i.job,9,' ')||' '||i.人数);
end loop;
end;
异常:error
分类:预定义异常(系统中已经有名字(名字 错误代码 错误描述))
非预定义异常(系统已经有但没有名字 只有错误代码和描述)
自定义异常(自己定义的异常)
--预定义异常
违反了唯一键限制 dup_val_on_index
没有找到数据 no_data_found
返回多行 too_many_rows
视图用0做除数 zero_divide
其他异常 others
创建一张表用来存放异常信息
create table t_err(code number,errm varchar(500),err_date date);
错误代码
错误描述
declare
v_e varchar(200);
v_c number;
v_err varchar(200);
begin
select ename into v_e from emp where empno=&empno;
dbms_output.put_line(v_e);
exception
when others then dbms_output.put_line(sqlcode||sqlerrm);
v_c:=sqlcode;
v_err:=sqlerrm;
insert into t_err values(v_c,v_err,sysdate);
--在表中插入数据的时候需要借助变量 不能直接插入
--处理异常数据的时候要先打印后插入
end;
非预定义异常(了解)
declare
err exception;
v_c number;
v_e varchar(20);
pragma exception_init(err,-2291);--把异常代码是2291的起了一个名
begin
insert into emp(empno,deptno) values(1234,50);
exception
when err then
v_c:=sqlcode;
v_e:=sqlerrm;
dbms_output.put_line(sqlcode||sqlerrm);
insert into t_err values(v_c,v_e,sysdate);
end;--底层架构了解一下,方便之后优化
数据库的锁:
读锁(共享锁)
写锁(排他锁)
死锁:多个用户互相占用资源 导致谁也不能完成操作
陷入了无穷无尽的等待
--自定义异常
--往emp_1插入一条数据 要求必须是7开头的并且是4位
declare
v_empno number:=&empno;
err exception;
begin
if v_empno not like '7___' then
raise err;--不满足业务需求 抛出异常
end if;
insert into emp_1(empno) values(v_empno);
exception--最后写
when err then
dbms_output.put_line('编号不合规');
raise_application_error(-20001,'员工编号不满足');
--带内容的弹框报错
--错误代码的取值范围-20000~-20999之间
--raise;--不带内容的弹框报错
end;
--创建一个emp_2表 格式同emp
--插入一条数据 要求员工编号7开头并且4位 工资大于300 工作不能为空
--如果不符合规范 异常信息插入t_err中 并且弹窗报错
create table emp_2 as select * from emp where 1=2;
declare
v_empno number:=&empno;
v_sal number:=&sal;
v_job varchar(20):='&job';
err_1 exception;
err_2 exception;
err_3 exception;
begin
if v_empno not like '7___' then raise err_1;
elsif v_sal<300 then raise err_2;
elsif v_job is null then raise err_3;
end if;
insert into emp_2(empno,sal,job) values(v_empno,v_sal,v_job);
exception
when err_1 then
dbms_output.put_line('编号不合规');
insert into t_err values(-20001,'编码错误',sysdate);
commit;--有这个才会继续运行下面的
raise_application_error(-20001,'编号不满足');
when err_2 then
dbms_output.put_line('工资不合规');
insert into t_err values(-20002,'工资不满足',sysdate);
commit;
raise_application_error(-20002,'工资不满足');
when err_3 then
dbms_output.put_line('工作不合规');
insert into t_err values(-20003,'工作不满足',sysdate);
commit;
raise_application_error(-20003,'工作不满足');
end;
注:异常处理时既要插入又要弹窗 必须先插入提交事务后再去弹窗
select * from t_err;
自定义函数
语法:
pl/sql 匿名块
有名块:自定义函数 function
存储过程 procedure
包 package
触发器 trigger
定时器
自定义函数
语法:
create [or replace] function 函数名[参数1 [in]out 类型,参数2..]
return 类型 is|as
[声明的变量]
begin
...
return 值|变量;
[exception]
end;
1.没有参数
--制作一个Π函数
create or replace function fun1 return number is
v_a number;
begin
v_a:=acos(-1);
return v_a;
end;
--
函数调用
select acos(-1) from dual;
2.有 in
--输入一个员工编号 返回工资
create or replace function fun2(v_empno number) return number is
v_s number;
begin
select sal into v_s from emp where empno=v_empno;
return v_s;
exception
when no_data_found then
return 0;
end;
select fun2(7788) from dual;
--输入一个部门编号和一个工作返回这个部门这种职位的人数 没有返回0
create or replace function fun3(v_deptno number,v_job varchar2) return number is
v_d number(5);
begin
select count(1) into v_d from emp
where deptno=v_deptno and job=v_job;
return v_d;
exception
when no_data_found then
return 0;
end;
select fun3(20,'CLERK') from dual;
--输入一个部门 返回这个部门所有人名用,隔开
create or replace function fun4(v_deptno number) return varchar is
v_de varchar(50);
begin
select WM_CONCAT(ename) into v_de from emp
where deptno=v_deptno
group by deptno;
return v_de;
exception
when no_data_found then
return '错误';
end;
select fun4(50) from dual;
--输入一个年份 返回平年还是闰年
create or replace function fun6(v_y number) return varchar2 is
v_a varchar(20);
begin
select case when to_char(to_date(v_y||'-12-31','yyyy-mm-dd'),'ddd')=366 then '闰年'
else '平年' end
into v_a from dual;
return v_a;
end;
select fun6(8) from dual;
--随机函数
dbms_random.value(小数,大数) 生成小数和大数之间的随机数
dbms_random.string('格式',长度) 生成给定长度的随机字符串
U 大写
L 小写
A 字母
X 大写字母和数字
P 可打印字符
select trunc(dbms_random.value(1,100)) 随机数字,
dbms_random.string('X',6)
from dual;