plsql程序设计详解(二)

九：循环语句

一共有三种循环方式
oracle中没有自增++ ；
自身=自身+1即可

WHILE 条件 LOOP … END LOOP;
LOOP EXIT WHEN 条件 … END LOOP;
FOR I IN 1..5 (必须为连续区间)LOOP … ;END LOOP;

例1：

--while循环打印数字的1~10

set serveroutput on
declare

--定义循环变量

pnum number := 1;
begin
while pnum <=10 loop

 --循环体

dbms_output.put_line(pnum);

--使该变量+1

pnum := pnum+1;
end loop;
end;
/

例2：

--loop循环打印数字的1~10(推荐)
set srveroutput on 
declare
  --定义循环变量
  pnum number:=1;
  begin
  loop
    --退出条件
    exit when pnum>0;
    --打印该变量的值
     dbms_output.put_line(pnum); 
    --循环变量+1
    pnum:=pnum+1;
  end loop;
end;
/

例3：

--for循环打印数字1~10
set serveroutput on
declare
  --定义循环变量
  pnum number:=1;
  begin
  for pnum in 1..10 loop   --表示从1到10连续的区间
    --循环体
    dbms_output.put_line(pnum);
  end loop;
end;
/

三种循环，建议使用第二种循环，因为第二种在控制光标的时候会比较方便

十：光标

光标（游标）：就是一个结果集（Result Set）
不能把一个集合赋值给一个基本型变量

光标的语法：
cursor 光标名[(参数名 数据类型[,参数名 数据类型]…)] is select 语句；
例：cursor c1 select ename from emp;
打开光标：open c1;
注：就是执行select语句，将结果集给c1
关闭光标：close c1;
取出一行光标的值：fetch c1 into pename;(取一行到变量中)

fetch 的作用：1)把当前指针指向的记录返回
             2)将指针指向下一条记录

光标属性：%found %notfound 值有true与false
例:

--查询并打印员工的姓名薪水
set serveroutput on
DECLARE 
--定义游标
  cursor cemp is select ename, sal from emp;
  --使用引用型变量，为光标定义对应的变量
  pename emp.ename%type;
  psal emp.sal%type;
  begin
  --打开游标
  open cemp;
  loop
    fetch cemp into pename, psal;   --取一条记录
    exit when cemp%notfound;        --没有取到记录退出。
    dbms_output.put_line(pename || '的薪水是' || psal);
  end loop;
  --关闭游标
  close cemp;
end;
/

十一：练习

--给员工涨工资，总裁1000，经理800，其他400
set serveroutput on 
declare
  cursor cemp is select empno,ename,empjob,sal from emp;
  pempno emp.empno%type;
  pempjob emp.empjob%type;
  psal emp.sal%type;
  pename emp.ename%type;
  begin
  --把之前涨的工资回退回去
   rollback;
   open cemp;
    loop 
    --取出一个员工
      fetch cemp into pempno,pename,pempjob,psal;
      exit when cemp%notfound;
      --判断是什么职位
      if pempjob = 'PRESIDENT' then update emp  set sal=sal+1000 where empno=pempno; 
      elsif pempjob = 'MANAGER' then update emp set sal=sal+800  where empno=pempno; 
      else update emp set sal=sal+400 where empno=pempno;
      end if;

    end loop;
   close cemp;
   --对于Oracle默认的事务隔离级别是read committed
   --事务的ACID即原子性、一致性、隔离性、持久性
   commit;
   DBMS_OUTPUT.PUT_LINE('修改完成');
   open cemp;
    loop 
      fetch cemp into pempno,pename,pempjob,psal;
      exit when cemp%notfound;
      DBMS_OUTPUT.PUT_LINE(pename||'的薪水是'||psal);
    end loop;
  close cemp;
end;
/

十二：光标的属性与限制
1光标的属性
%found %notfound
%isopen : 判断光标是否打开
%rowcount : 影响的行数
2.光标数的限制:默认情况下,oracle数据库只允许在同一个会话中,打开300个光标

>--切换到管理员,查看数据库初始化设置   
>show user
>conn sys/password@127.0.0.1:1521/orcl as sysdba
>show parameter cursor

修改光标数的限制:
alter system set open_cursors=400 scope=both;
scope是范围的意思，它的取值有:both(两个同时更改),memory(只更改当前实例,不更改参数文件),spfile(只更改参数文件,不更改当前文件,数据库需要重启)

  if cemp%isopen then
  dbms_output.put_line('光标已经打开');
  end if;

  loop
   --取出一条记录 
  fetch cemp into pempno,pjob;
  exit when cemp%notfound;
   --打印rowcount的值
  dbms_output.put_line('rowcount'||cemp%rowcount);
  end loop;

十三：带参数的光标

（1）光标就是一个结果集（Result Set）
（2）光标的语法：
cursor 光标名[(参数名 数据类型[,参数名 数据类型]…)]
IS SELECT 语句；
cursor c1 is select ename from emp;

--查询某个部门中员工的姓名
set serveroutput on

declare
  --定义带参数的光标
  cursor cemp(dno number) is select ename from emp where     deptno=dno;
  begin
  open cemp(10);
  loop
    fetch cemp into penae;
    exit when cemp%notfound;
    dbms_output.put_line(pename);
  end loop;
  close cemp;
end;
/