Oracle之游标

游标是用来操作PL/SQL中查询出来的结果集的
我们直接上例子

declare
  --声明游标，可以带参数，也可以不带
  cursor vrows(dno number) is select * from emp where deptno=dno;
  --声明游标中数据类型，这里是PL/SQL记录型变量，用于表示一行数据
  vrow emp%rowtype;
begin
  --打开游标
  open vrows(30);
  --遍历游标，使用fetch into取数据
  loop
    fetch vrows into vrow;
    --判断是否还有数据
    exit when vrows%notfound;
    --输出
    dbms_output.put_line(vrow.ename||' '||vrow.sal);
  end loop;
  --关闭游标
  close vrows;
end;

以下是建表语句

  CREATE TABLE "SCOTT"."EMP" 
   (	"EMPNO" NUMBER(4,0), 
	"ENAME" VARCHAR2(10), 
	"JOB" VARCHAR2(9), 
	"MGR" NUMBER(4,0), 
	"HIREDATE" DATE, 
	"SAL" NUMBER(7,2), 
	"COMM" NUMBER(7,2), 
	"DEPTNO" NUMBER(2,0), 
	 CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE, 
	 CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
	  REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
 

表中数据

展示结果：

ALLEN 1600
WARD 1250
MARTIN 1250
BLAKE 2850
TURNER 1500
JAMES 950