oracle数据库sql语句08 PL SQL程序结构 循环 游标

PL/SQL程序结构 








set serveroutput on


declare
 season int :=3;
 info varchar2(50);
begin
 season := &season;
 case season
 when 1 then
  info := season||'季度包含1,2,3月份';
 when 2 then
  info := season||'季度包含4,5,6月份';
 when 3 then
  info := season||'季度包含7,8,9月份';
 when 4 then
  info := season||'季度包含10,11,12月份';
 else
  info := '季度不合法';
 end case;
 dbms_output.put_line(info);
end;
/


循环结构


--for
declare
 sum_i int:=0;
 i int := 0;
begin
 for i in 1..100 loop
  sum_i:=sum_i+i;
 end loop;
 dbms_output.put_line(sum_i);
end;
/






--while
declare
 sum_i int:=0;
 i int := 0;
begin
 while i<=99 loop
 i:=i+1;
 sum_i:=sum_i+i;
 end loop;
 dbms_output.put_line(sum_i);
end;
/






--loop
declare
 sum_i int:=0;
 i int := 0;
begin
 loop
 i:=i+1;
 sum_i:=sum_i+i;
 exit when i >99;
 end loop;
 dbms_output.put_line(sum_i);
end;
/




--显式游标


--游标 while
declare
 cursor cur_emp is select * from emp where deptno=30;
 a emp%rowtype;
begin
 open cur_emp;
 fetch cur_emp into a;
 while cur_emp%found loop
dbms_output.put_line(a.ename);
fetch cur_emp into a;
 end loop;
 close cur_emp;
end;
/




--游标 loop
declare
 cursor cur_emp is select * from emp where deptno=30;
 a emp%rowtype;
begin
 open cur_emp;
 loop
 fetch cur_emp into a;
 exit when cur_emp%notfound;
 dbms_output.put_line(a.ename);
 end loop;
 close cur_emp;
end;
/


--游标 for 自动打开、读取、关闭 游标
declare
 cursor cur_emp is select * from emp where deptno=30;
begin
 for i in cur_emp loop
dbms_output.put_line(i.ename);
 end loop;
end;
/






--隐式游标


begin
update emp set sal=sal+100 where job='SALESMAN';
if sql%found then
dbms_output.put_line(sql%rowcount||'有员工跟新');
else
dbms_output.put_line('无员工跟新');
end if;
end;
/




declare
aa emp%rowtype;
begin
select * into aa from emp where job='SALESMAN';
if sql%found then
dbms_output.put_line(sql%rowcount||'有员工跟新');
else
dbms_output.put_line('无员工跟新');
end if;
end;
/




begin
for i in (select * from emp where deptno=30) loop
dbms_output.put_line(i.empno||'  '||i.ename);
end loop;
end;
/