oracle 游标操作

显示游标

1.操作过程

打开游标就是执行定义的 SELECT 语句，将查询结果转入内存，游标指针指向结果首部，提取游标用 FETCH，FETCH 语句执行一次，游标指针向后一行，直到结束，关闭游标就释放了占用的内存区

2.for in 遍历

DECLARE
CURSOR minSal(sal NUMBER) IS SELECT e_name,e_sal FROM emp2 WHERE e_sal>sal;
BEGIN 
  FOR emd IN minSal(100)
  LOOP
     EXIT WHEN minSal%ROWCOUNT>3; -- 取结果集的前3个
     --EXIT WHEN minSal%NOTFOUND; -- 取到结果集为空过
     dbms_output.put_line(emd.e_name || ' 工资 ' || emd.e_sal);
  END LOOP;
END;
​

3.fetch 遍历

DECLARE
CURSOR minSal(sal NUMBER) IS SELECT e_name,e_sal FROM emp2 WHERE e_sal>sal;
v_name emp2.e_name%TYPE;
v_sal emp2.e_sal%TYPE;
BEGIN 
  OPEN minSal(100);
  LOOP
    EXIT WHEN minSal%NOTFOUND;
    FETCH minSal INTO v_name,v_sal;
    dbms_output.put_line(v_name || ' 工资 ' || v_sal);
  END LOOP;
  CLOSE minSal;
END;
​

DECLARE
CURSOR minSal(sal NUMBER) IS SELECT e_name,e_sal FROM emp2 WHERE e_sal>sal;
TYPE emp_record IS RECORD( -- 此处用record（记录类型），其他的同上
  v_name emp2.e_name%TYPE,
  v_sal emp2.e_sal%TYPE
);
emp_record_ emp_record;
BEGIN 
  OPEN minSal(100);
  LOOP
    EXIT WHEN minSal%NOTFOUND;
    FETCH minSal INTO emp_record_;
    dbms_output.put_line(emp_record_.v_name || ' 工资 ' || emp_record_.v_sal);
  END LOOP;
  CLOSE minSal;
END;
​

隐式游标

用 select 进行操作默认使用了隐式游标，每个隐式游标必须有一个 into 子句且这个 select 语句只返回一行数据，如果返回多行数据，使用显示游标处理，不然报错 exact fetch returns more than requested number of rows

DECLARE
v_name emp2.e_name%TYPE;
v_sal emp2.e_sal%TYPE;
BEGIN
  SELECT e_name,e_sal INTO v_name,v_sal FROM emp2 where e_id=2;
  dbms_output.put_line(v_name||'工资'||v_sal);
END;
​

游标变量

DECLARE
TYPE cur_ref IS REF CURSOR; 
cur cur_ref; -- 定义一个游标变量
v_name emp2.e_name%TYPE;
v_sal  emp2.e_sal% TYPE;
BEGIN
  OPEN cur FOR SELECT e_name,e_sal FROM emp2;
  LOOP
    EXIT WHEN cur%NOTFOUND;
    FETCH cur INTO v_name,v_sal;
    dbms_output.put_line(v_name || ' 工资 ' || v_sal);
  END LOOP;
  CLOSE cur;
END;
​