流程控制，循环控制语句，游标

流程控制

emp %rowtype 行类型

emp.sal%type 和emp表中sal类型一样

record 记录集类型

条件控制语句

if 条件

declare
v_job emp.job%type
begin
select job into v_job from emp where empno=&empno;
if v_job='MANAGER' then
dbms_output.put_line('是经理');
else
dbms_output.put_line('不是经理');
end if;
end;

打开输出
set serveroutput on;

多重if语句-------if elsif  else
declare
 v_sal emp.sal%type;
 begin
 select sal into v_sal from emp where empno=&no;
 if v_sal<=1000 then
 dbms_output.put_line('蓝领');
 elsif v_sal<=2000 then
 dbms_output.put_line('白领');
 else
 dbms_output.put_line('金领');
 end if;
 end;

case 等值

declare
v_job emp.job%type;
begin
select job into v_job from emp where empno=&no;
case
v_job
when 'CLERK' then dbms_output.put_line('科员');
when 'SALEMAN' then dbms_output.put_line('销售员');
when 'MANAGER' then dbms_output.put_line('经理');
else dbms_output.put_line('不明职位');
end case;
end;

case 条件

declare
v_job emp.job%type;
begin
select job into v_job from emp where empno=&no;
case
when v_sal>=3000 then dbms_output.put_line('金领');
when v_sal>=3000 then dbms_output.put_line('白领');
else dbms_output.put_line('金领');
end case;
end;

循环控制语句

loop

end loop;循环结束

exit;当满足什么条件强制结束

put 输出

put_line 输出换行

declare
v_i number(5);
begin
v_i:=1;
loop
dbms_output.put_line(v_i);
v_i:=v_i+1;
exit when v_i>10;
end loop;

while loop

while 条件；满足什么条件继续执行

declare
v_i number(5);
begin
v_i:=1;
while v_i<=10 loop
dbms_output.put_line(v_i);
v_i:=v_i+1;
end loop;
dbms_output.put_line(' ');
end;

begin
for v_i in 1 .. 10
loop
dbms_output.put_line(v_i);
end loop;
dbms_output.put_line(' ');
end;

游标

针对增加、删除、修改

逐行返回查询结果

游标都能够使用的三个参数

%notfound

–如果FETCH语句失败，则该属性为"TRUE"，否则为"FALSE";

%found

–如果FETCH语句成功，则该属性为"TRUE"，否则为"FALSE";

%rowcount

–返回游标当前行的行数;

%isopen

–如果游标是开的则返回"TRUE"，否则为"FALSE";

隐式游标

sql

begin
update emp set sal=sal+100 where deptno=&deptno;
dbms_output.put_line(sql%rowcount||'个员工涨了工资');
end;

begin
update emp set sal=sal-100 where deptno=&deptno;
if sql%found then
dbms_output.put_line('员工降了工资');
else
dbms_output.put_line('员工没有降工资');
end if;
end;

显式游标

静态游标

定义

cursor <游标名> is <select 语句> [for update|for update of字段];

declare
cursor mycursor is select * from emp where deptno=&deptno;
v_emprow emp%rowtype;//从游标提取数据只能单行取，保存取出的单行数据
begin
open mycursor;//打开
//将数据保存到结果集
loop
fetch mycursor into v_emprow;//提取
//一次只能提取一行
exit when mycursor%notfound;
dbms(v_emprow.ename||' '||v_emprow.job);
end loop;
close mycursor;//关闭
end;

for循环提取游标，自动打开关闭

declare
cursor mysursor is select * from emp where deptno=&deptno;//指向select语句，没有数据
v_emprow emp%rowtype;
begin
for v_row in mycursor loop
dbms(v_emprow.ename||' '||v_emprow.job);
end loop;
end

带参数的游标，参数类型不定义长度

for循环不用打开关闭游标，所以for循环不能用带参数的游标

declare
cursor mycur(v_ename varchar2) is select * from emp where ename like '%'||v_ename||'%';
v_emprow emp%rowtype;
begin
open mycur('S');
loop
fetch mycur into v_emprow;//提取
exit when mycur%notfound;
dbms(v_emprow.ename||' '||v_emprow.job);
end loop;
close mycur;
end;

带锁的游标，在锁定期间，其他用户只能查看不能修改，打开游标加锁，关闭游标解锁

一般修改update需要锁

declare
cursor mycursor is select * from emp where deptno=10 for update;//加锁
begin
for v_row in mycursor loop
if v_row.job='MANAGER' then
update emp set sal=sal+200 where current of mycursor;//读取游标中的当前一行
else
update emp set sal=sal+150 where current of mycursor;
end if;
end loop;
end

动态游标

与静态游标相比，赋值sql语句的位置不同

弱类型游标

declare
type mycurtype is ref cursor;//定义动态游标
mycur mycurtype;
emprow emp%rowtype;
begin
open mycur for select * from emp where deptno=20;//open时才引入sql语句
loop
fetch mycur into emprow;
exit when mycur%notfound;
dbms_output.put_line(emprow.ename||' '||emprow.job);
end loop;
close mycur;
end;

限制返回数据结果的列，select语句不能随便写，加返回值类型

强类型

declare
type RecordTye is record
(
ename emp.ename%type,
  job emp.job%type,
  sal emp.sal%type
);--//加入限制的列，查询结果只能有这些数据类型
--//可以查不同表，但数据类型必须兼容
type mycurtype is ref cursor return RecordTye;
mycur mycurtype;
emprow RecordTye;
begin
if to_number(to_char(sysdate,'dd'))<=15 then
open mycur for select ename,job,sal from emp where deptno=20;--//open时才引入sql语句
else 
open mycur for select ename,job,sal from emp where deptno=10;
end if;
loop
fetch mycur into emprow;
exit when mycur%notfound;
dbms_output.put_line(emprow.ename||' '||emprow.job||' '||emprow.sal);
end loop;
close mycur;
end;