PLSQL LOOP,WHILE.FOR 循环，游标

最新推荐文章于 2023-09-25 11:52:24 发布

Tapyou

最新推荐文章于 2023-09-25 11:52:24 发布

阅读量1.3k

点赞数 4

分类专栏： ORACLE 文章标签： sql

本文链接：https://blog.csdn.net/Tapyou/article/details/120954817

版权

ORACLE 专栏收录该内容

21 篇文章 8 订阅

订阅专栏

循环 --注意自增
语法
loop
要执行的语句;
[exit when 退出条件;]
end loop;

--循环打印1-10
declare
v1 number:=1;
begin
loop
dbms_output.put_line(v1);
v1:=v1+1;
exit when v1=11;
end loop;
end;

--循环的三大组成部分:初始值,顺序,退出条件

--循环打印1-10
declare
v1 number:=0;
begin
loop
v1:=v1+1;
dbms_output.put_line(v1);
exit when v1=10;
end loop;
end;

--循环打印1-10
declare
v1 number:=0;
begin
loop
exit when v1=10;
v1:=v1+1;
dbms_output.put_line(v1);
end loop;
end;

--打印1+2+3..+10的和
declare
v1 number:=1;
v_sum number:=1;
begin
loop
dbms_output.put_line(v_sum);
v1:=v1+1;
v_sum:=v_sum+v1;
exit when v1=11;
end loop;
end;

v1 v_sum d
1 1 1
2 3 3
3 6 6
4 10 10
5 15 15
6 21 21
7 28 28
8 36 36
9 45 45
10 55 55
11 66

--不换行打印1+2+3..+10的和
declare
v1 number:=1;
v_sum number:=1;
begin
loop
dbms_output.put(v_sum||' ');
v1:=v1+1;
v_sum:=v_sum+v1;
exit when v1=11;
end loop;
dbms_output.put_line('');
end;

--注意:不换行打印不能单独使用否则为空,不换行打印的后面必须有一个换行打印

--用循环的方法打印"1+2+3+4+....+10=55"这个式子
declare
v1 number:=1;
v_sum number:=0;
begin
loop
dbms_output.put(v1||'+');
v_sum:=v_sum+v1;
v1:=v1+1;
exit when v1=10;
end loop;
dbms_output.put(v1||'='||(v_sum+v1));
dbms_output.put_line('');
end;

--用循环的方法打印"1*2*3*4*....*10=3628800"这个式子
declare
v1 number:=1;
v2 number:=1;
begin
loop
v2:=v2*v1;
dbms_output.put(v1||'×');
v1:=v1+1;
exit when v1=10;
end loop;
dbms_output.put_line(v1||'='||v2*v1);
end;

--用循环的方法打印"1.0001^1^2^3^...^9"的结果
declare
v1 number:=1;
v2 number:=1.0001;
begin
dbms_output.put('1.0001^');
loop
v2:=power(v2,v1);
dbms_output.put(v1||'^');
v1:=v1+1;
exit when v1=9;
end loop;
dbms_output.put_line(v1||'='||power(v2,v1));
end;

--循环不换行打印a-z
declare
v1 number:=ascii('a');
begin
loop
dbms_output.put(chr(v1));
v1:=v1+1;
exit when v1=ascii('z')+1;
end loop;
dbms_output.put_line('');
end;

--循环不换行打印a-z,五个一换行
declare
v1 number:=ascii('a');
v_count number:=0;
begin
loop
dbms_output.put(chr(v1));
v_count:=v_count+1;
v1:=v1+1;
if mod(v_count,5)=0 then
dbms_output.put_line('');
end if;
exit when v1=ascii('z')+1;
end loop;
dbms_output.put_line('');
end;

--循环打印A-Za-z,5个一换行
declare
v1 number:=ascii('a');
v2 number:=ascii('A');
v_count number:=0;
begin
loop
dbms_output.put(chr(v1));
v_count:=v_count+1;
v1:=v1+1;
if mod(v_count,5)=0 then
dbms_output.put_line('');
end if;
exit when v1=ascii('z')+1;
end loop;
loop
dbms_output.put(chr(v2));
v_count:=v_count+1;
v2:=v2+1;
if mod(v_count,5)=0 then
dbms_output.put_line('');
end if;
exit when v2=ascii('Z')+1;
end loop;
dbms_output.put_line('');
end;

--while循环 --有条件的循环 --注意自增
语法:
while 进入循环的条件 loop
要执行的语句;
[exit when 条件;] --中途退出的条件
end loop;

--循环打印1-10
declare
v1 number:=1;
begin
while v1<11 loop
dbms_output.put_line(v1);
v1:=v1+1;
end loop;
end;

--用循环的方法打印"1+2+3+4+....+10=55"这个式子
declare
v1 number:=1;
v_sum number:=0;
begin
while v1<10 loop
dbms_output.put(v1||'+');
v_sum:=v_sum+v1;
v1:=v1+1;
end loop;
dbms_output.put_line('10='||(v_sum+10));
end;

--for循环
语法:
for 变量名 in [reverse] 小值..大值 loop --reverse 取反
要执行的语句;
[exit when 条件;]
end loop;

--循环打印1-10
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;

--用循环的方法打印"1+2+3+4+....+10=55"这个式子
declare
v_sum number:=0;
begin
for i in 1..9 loop
dbms_output.put(i||'+');
v_sum:=v_sum+i;
end loop;
dbms_output.put_line('10='||(v_sum+10));
end;

--循环打印10-1
begin
for i in reverse 1..10 loop
dbms_output.put_line(i);
end loop;
end;

--打印如下正方形
* * * * *
* * * * *
* * * * *
* * * * *
* * * * *

begin
for k in 1..5 loop
for i in 1..5 loop
dbms_output.put('* ');
end loop;
dbms_output.put_line('');
end loop;
end;

--打印如下直角三角形
*
* *
* * *
* * * *
* * * * *

begin
for i in 1..5 loop
for k in 1..i loop
dbms_output.put('* ');
end loop;
dbms_output.put_line('');
end loop;
end;

--打印如下等腰三角形
* --4个空格+1个*
* * --3个空格+2个*
* * * --2个空格+3个*
* * * * --1个空格+4个*
* * * * * --0个空格+5个*
第n行 --(5-n)个空格+n个*

begin
for i in 1..5 loop
for j in 1..5-i loop
dbms_output.put(' ');
end loop;

for k in 1..i loop
dbms_output.put('* ');
end loop;
dbms_output.put_line('');
end loop;
end;

--打印如下等腰三角形
* * * * *
* * * *
* * *
* *
*

begin
for i in reverse 1..5 loop
for j in 1..5-i loop
dbms_output.put(' ');
end loop;

for k in 1..i loop
dbms_output.put('* ');
end loop;
dbms_output.put_line('');
end loop;
end;

--打印如下菱形
*
* *
* * *
* * * *
* * * * *
* * * *
* * *
* *
*

begin
for i in 1..4 loop
for j in 1..5-i loop
dbms_output.put(' ');
end loop;

for k in 1..i loop
dbms_output.put('* ');
end loop;
dbms_output.put_line('');
end loop;

for i in reverse 1..5 loop
for j in 1..5-i loop
dbms_output.put(' ');
end loop;

for k in 1..i loop
dbms_output.put('* ');
end loop;
dbms_output.put_line('');
end loop;
end;

--打印如下梯形
* * *
* * * *
* * * * *

begin
for i in 3..5 loop
for j in 1..5-i loop
dbms_output.put(' ');
end loop;

for k in 1..i loop
dbms_output.put('* ');
end loop;
dbms_output.put_line('');
end loop;
end;

--游标 cursor
游标是一种能从多条记录的结果集中每次提取一条记录的机制。
游标可以充当指针。游标能遍历结果中的所有行，但是一次只能指向一行。
游标提供了在逐行的基础上操作表中数据的方法。游标的结果集是由select
语句产生的。
--游标的分类
┌静态游标┌显式游标┌普通的显式游标(没有参数)
│ │ └带参数的显式游标
│ └隐式游标
└动态游标┌强类型游标
├弱类型游标
└动态游标

--静态游标-显示游标-普通的显示游标
declare
cursor cur_1 is select * from dept; --声明游标
v_dept dept%rowtype;
begin
open cur_1; --打开游标
fetch cur_1 into v_dept; --提取游标中的值到变量中
dbms_output.put_line(v_dept.deptno||' '||v_dept.dname||' '||v_dept.loc);
fetch cur_1 into v_dept;
dbms_output.put_line(v_dept.deptno||' '||v_dept.dname||' '||v_dept.loc);
fetch cur_1 into v_dept;
dbms_output.put_line(v_dept.deptno||' '||v_dept.dname||' '||v_dept.loc);
fetch cur_1 into v_dept;
dbms_output.put_line(v_dept.deptno||' '||v_dept.dname||' '||v_dept.loc);
fetch cur_1 into v_dept;
dbms_output.put_line(v_dept.deptno||' '||v_dept.dname||' '||v_dept.loc);

close cur_1; --关闭游标
end;

--打印10部门的员工信息
declare
cursor cur_1 is select * from emp where deptno=10;
v_emp emp%rowtype;
begin
open cur_1;
fetch cur_1 into v_emp;
dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||
v_emp.job||' '||v_emp.mgr||' '||
v_emp.hiredate||' '||
v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);
fetch cur_1 into v_emp;
dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||
v_emp.job||' '||v_emp.mgr||' '||
v_emp.hiredate||' '||
v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);
fetch cur_1 into v_emp;
dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||
v_emp.job||' '||v_emp.mgr||' '||
v_emp.hiredate||' '||
v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);

close cur_1;
end;

游标的属性
游标%isopen 判断游标是否打开,打开返回真,否则返回假.--布尔型
游标%found 判断游标的指针里是否有值,有返回真,否则返回假.--布尔型
游标%notfound 判断游标的指针里是否没值,没值返回真,否则返回假.--布尔型
游标%rowcount 游标的指针指了多少行.(最大是结果集的行数)--数值型

--游标循环
1.loop+%notfound

顺序:声明游标→声明变量→open→loop→fetch→exit when→打印→end loop→close

declare
cursor cur_1 is select * from emp;
v_emp emp%rowtype;
begin
open cur_1;
loop
fetch cur_1 into v_emp;
exit when cur_1%notfound;
dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||
v_emp.job||' '||v_emp.mgr||' '||
v_emp.hiredate||' '||
v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);

end loop;
close cur_1;
end;

2.while+%found

顺序:声明游标→声明变量→open→fetch→while→打印→fetch→close
declare
cursor cur_1 is select * from emp;
v_emp emp%rowtype;
begin
open cur_1;
fetch cur_1 into v_emp;
while cur_1%found loop
dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||
v_emp.job||' '||v_emp.mgr||' '||
v_emp.hiredate||' '||
v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);
fetch cur_1 into v_emp;
end loop;
close cur_1;
end;

--静态游标-显示游标-带参数的显示游标
declare
cursor cur_1(v1 number) is select * from emp where deptno=v1;
v_emp emp%rowtype;
begin
open cur_1(10);
loop
fetch cur_1 into v_emp;
exit when cur_1%notfound;
dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||
v_emp.job||' '||v_emp.mgr||' '||
v_emp.hiredate||' '||
v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);
end loop;
close cur_1;

open cur_1(20);
loop
fetch cur_1 into v_emp;
exit when cur_1%notfound;
dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||
v_emp.job||' '||v_emp.mgr||' '||
v_emp.hiredate||' '||
v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);
end loop;
close cur_1;
end;

--打印工作是MANAGER的姓名和工作,
--SALESMAN的姓名和佣金,
--CLERK的姓名和入职日期

declare
cursor cur_1(v_job varchar2) is select * from emp where job=v_job;
v_emp emp%rowtype;
begin
open cur_1('MANAGER');
loop
fetch cur_1 into v_emp;
exit when cur_1%notfound;
dbms_output.put_line(v_emp.ename||' '||v_emp.job);
end loop;
close cur_1;

open cur_1('SALESMAN');
loop
fetch cur_1 into v_emp;
exit when cur_1%notfound;
dbms_output.put_line(v_emp.ename||' '||v_emp.comm);
end loop;
close cur_1;

open cur_1('CLERK');
loop
fetch cur_1 into v_emp;
exit when cur_1%notfound;
dbms_output.put_line(v_emp.ename||' '||v_emp.hiredate);
end loop;
close cur_1;
end;

--静态游标-隐式游标
不用声明、不用打开、不用赋值、不用关闭。
这些都是oracle系统自动完成的。
常用的方式有delete/update/insert/select..into单行赋值。

隐式游标的四个属性
1.sql%isopen 判断游标是否打开,永远返回假。
2.sql%found 判断游标的指针里是否有值，如果最近一次有结果，返回真，否则假。
3.sql%notfound 判断游标的指针里是否没值，如果最近一次没结果，返回真，否则假。
4.sql%rowcount 游标的指针指了多少行，返回最近一次从游标读取的数据。

--update测试
begin
update emp_b set ename=lower(ename) where deptno=&a;
if sql%found then
dbms_output.put_line('更新了,更新了'||sql%rowcount||'行');
else
dbms_output.put_line('没更新');
end if;
end;
--insert测试
begin
insert into emp_b select * from emp where deptno=&a;
insert into emp_b select * from emp where deptno=&b;
if sql%found then
dbms_output.put_line('插入了,插入了'||sql%rowcount||'行');
else
dbms_output.put_line('没插入');
end if;
end;
--select into测试
declare
v1 number;
begin
select sal into v1 from emp where ename='SMITH';
if sql%found then
dbms_output.put_line('查找到'||sql%rowcount||'行数据');
end if;
end;

--动态游标
每次打开都是一个全新的结果集
CREATE TABLE emp_10 AS SELECT * FROM emp;
CREATE TABLE emp_20 AS SELECT * FROM emp;
CREATE TABLE emp_30 AS SELECT * FROM emp;

--动态游标-强类型
有return,每次打开都是可以换结果集但是不能换表结构
declare
type cur_1_ref is ref cursor return emp%rowtype;
cur_1 cur_1_ref;
v_emp emp%rowtype;
begin
open cur_1 for select * from emp where deptno=10;
loop
fetch cur_1 into v_emp;
exit when cur_1%notfound;
dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||
v_emp.job||' '||v_emp.mgr||' '||
v_emp.hiredate||' '||
v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);
end loop;
close cur_1;

open cur_1 for select * from emp where job='SALESMAN';
loop
fetch cur_1 into v_emp;
exit when cur_1%notfound;
dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||
v_emp.job||' '||v_emp.mgr||' '||
v_emp.hiredate||' '||
v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);
end loop;
close cur_1;
end;

--动态游标-弱类型
每次打开都是可以换结果集,没有return,可以换表结构
declare
type cur_1_ref is ref cursor;
cur_1 cur_1_ref;
v_emp emp%rowtype;
v_dept dept%rowtype;
begin
open cur_1 for select * from emp;
loop
fetch cur_1 into v_emp;
exit when cur_1%notfound;
dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||
v_emp.job||' '||v_emp.mgr||' '||
v_emp.hiredate||' '||
v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);
end loop;
close cur_1;

open cur_1 for select * from dept;
loop
fetch cur_1 into v_dept;
exit when cur_1%notfound;
dbms_output.put_line(v_dept.dname||' '||v_dept.loc||' '||v_dept.deptno);
end loop;
close cur_1;
end;

--动态游标-动态游标
declare
cur_1 sys_refcursor; --直接声明一个动态游标叫做cur_1
v_emp emp%rowtype;
v_dept dept%rowtype;
begin
open cur_1 for select * from emp;
loop
fetch cur_1 into v_emp;
exit when cur_1%notfound;
dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||
v_emp.job||' '||v_emp.mgr||' '||
v_emp.hiredate||' '||
v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);
end loop;
close cur_1;

open cur_1 for select * from dept;
loop
fetch cur_1 into v_dept;
exit when cur_1%notfound;
dbms_output.put_line(v_dept.dname||' '||v_dept.loc||' '||v_dept.deptno);
end loop;
close cur_1;
end;

--for 游标
begin
for i in (select * from emp)
loop
dbms_output.put_line(i.empno||' '||i.ename||' '||
i.job||' '||i.mgr||' '||
i.hiredate||' '||
i.sal||' '||i.comm||' '||i.deptno);
end loop;
end;

优点
1.使用游标可以执行多个不相关的操作
2.使用游标可以提高脚本的可读性
3.使用游标可以建立命令字符串，可以传送表名，或者把变量传送到参数中，以便建立可以执行的命令字符串
4.他还提供基于游标位置而对表中数据进行删除或更新
缺点
1.处理大数据量时，效率底下，占用内存大
2.游标使用时会对行加锁，可能会影响其他业务的正常进行