Oracle入门之带参数的显式游标

带参数的显式游标

• 显示游标是使用cursor定义的。主要作用是为了从数据库读取多行多列的内容。
• 参数是一个变量，主要用于游标声明中，当游标打开时，参数值被传递给Oracle服务器，Oracle服务器使用它来决定要将哪些行检索到光标的活动集中。

参数游标的用法

声明：

cursor 游标名称(变量  类型) is 查询语句;

使用：

open 游标名称(变量值);

示例

1.创建一个员工表

-- 插入EMP 
drop table EMP;
create table EMP(empno NUMBER(4),ename VARCHAR2(10),job VARCHAR2(9),mgr NUMBER(4),hiredate DATE,sal NUMBER(7,2),comm NUMBER(7,2),deptno NUMBER(2));
comment on table EMP is '员工表';
comment on column EMP.empno is '员工编号';
comment on column EMP.ename is '员工姓名';
comment on column EMP.job is '工作';
comment on column EMP.mgr is '上级主管';
comment on column EMP.hiredate is '入职日期';
comment on column EMP.sal is '工资';
comment on column EMP.comm is '奖金/绩效';
comment on column EMP.deptno is '所在部门的编号';
INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,to_date('13-6-1987','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,to_date('13-6-1987', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); 

2.用带参数的游标输出部门号为30的员工信息

--方法一：通过loop循环遍历使用
declare
--声明：向游标传入一个部门号
cursor cc(dno number) is select * from emp where deptno=dno;
res emp%rowtype;
begin
  --打开
  open cc(30);
  loop
    --提取
    fetch cc into res;
    exit when cc%notfound;
    dbms_output.put_line(res.ename);     
  end loop;
  -- 关闭
  close cc;
end;




--方法二：通过for循环遍历使用
declare
--声明
cursor cc(dno number) is select * from emp where deptno=dno;
res emp%rowtype;
begin
--for循环默认打开和关闭
  for i in cc(30) loop
    dbms_output.put_line(i.ename);      
  end loop;
end;