10.序列和游标

一、序列的概念

1.      序列是用于生成唯一、连续整数的对象，创建序列时可以指定是升序还是降序的

2.      序列是一种数据库对象，可以用于生成唯一的整数

3.      序列常用来生成自增的主键

二、创建序列的语法

create sequence 序列名

start with 开始数字

increment by 间隔

maxvalue 最大值

minvalue 最小值

cycle|nocycle

cache 缓冲区大小|nocache

三、序列的两个伪列

nextval:序列的下一个整数

currval:序列的当前值

注意：第一次不能使用currval伪列

              回滚操作不会使序列回滚

四、序列的更改

alter sequence 序列名

五、查看当前用户下有哪些序列

select * fromuser_sequences;

六、删除序列

drop sequence 序列名;

七、游标的概念

1.      为了处理SQL语句，oracle将在内存中分配一个区域，这个就是上下文区cursor

2.      上下文区域包含了该处理所需的信息，比如已经处理完的行数，指向被分析语句的指针，查询语句返回的数据行集

3.      游标的类型：隐式游标、显示游标和REF游标

八、隐式游标

1.      隐式游标是oracle自动定义的，用于DML语句和select...into..语句

2.      隐式游标的名称SQL

3.      通过隐式游标的属性，可以获得DML语句的结果

4.      当执行DML语句后，结果会保存在游标的属性中:

SQL%FOUND

SQL%NOTFOUND

SQL%ROWCOUNT

SQL%ISOPEN

九、隐式游标的使用例子

1.sql%count的使用

begin

  delete from emp2;

  dbms_output.put_line('删除了'||SQL%ROWCOUNT||'条数据');

end;

2.sql%found的使用

 

begin

  update emp set sal=sal-500 where empno=7369;

  if sql%found then

    dbms_output.put_line('更新成功');

  else

    dbms_output.put_line('没有更新任何数据');

  end if;

end;

十、显示游标

1.      显示游标是由程序员定义和命名的游标，用于多行查询语句

2.      上面所讲的隐式游标属性显示游标也有，不同之处在于游标属性的前缀是游标名而不是SQL

十一、      显示游标和隐式游标的区别

隐式游标	显式游标
PL/SQL维护，当执行查询时自动打开和关闭	在程序中显式定义、打开、关闭，游标有一个名字
游标属性前缀是SQL	游标属性的前缀是游标名
属性％ISOPEN总是为FALSE	％ISOPEN根据游标的状态确定值
SELECT语句带有INTO子句，只有一行数据被处理	可以处理多行数据，在程序中设置循环，取出每一行数据。

 

十二、      显示游标的使用步骤

1.      声明游标

cursor 游标名 is 查询语句

2.      打开游标

在使用之前应该打开游标

open 游标名;

3.      从游标提取数据

从游标得到一行数据使用fetch命令。每一次提取数据后，游标都指向结果集的下一行

fetch 游标名 into 变量1,变量2

 

如果有多行数据，可以使用循环来提取数据

loop

        fetch 游标名 into 变量1,变量2;

        exit when 游标%notfound;

end loop;

4.      关闭游标

close 游标名;

十三、      演示游标的使用,查询员工的编号，姓名和薪水

declare

  cursor mycur is select empno,ename,sal fromemp;

  myempno emp.empno%type;

  myename emp.ename%type;

  mysal emp.sal%type;

begin

  open mycur;

  loop

    fetch mycur into myempno,myename,mysal;

    exit when mycur%notfound;---注意退出的位置

    dbms_output.put_line('编号:'||myempno||'姓名:'||myename||'薪水'||mysal);

  end loop;

  close mycur;

end;

上面的代码使用了myempno.myename,mysal三个变量来接受游标的返回值，也可以这么做

定义游标类型的变量来接收数据

 

 

declare

  cursor mycur is select empno,ename,sal fromemp;

  my mycur%rowtype;-----注意这里的变化

begin

  open mycur;

  loop

    fetch mycur into my;

    exit when mycur%notfound;

    dbms_output.put_line('编号:'||my.empno||'姓名:'||my.ename||'薪水'||my.sal);

  end loop;

  close mycur;

end;

十四、      循环游标

1.      这种使用游标的方式比较简单。

2.      用于游标for循环的游标按照正常的声明方式声明，但在使用时不需要显示的打开，关闭，取数据，测试数据的存在，定义存放数据的变量

3.      语法格式

for 变量 in 游标或者查询语句

loop

        语句

end loop;

十五、      使用循环游标来简化前面的代码

declare

  cursor mycur is select empno,ename,sal fromemp order by sal desc;

begin

  for my in mycur loop

    dbms_output.put_line('编号:'||my.empno||'姓名:'||my.ename||'薪水:'||my.sal);

  end loop;

end;

上面的代码已经够简洁了，但是还有更简洁的，可以把声明部分去掉

begin

  for my in (select empno,ename,sal from emporder by sal) loop

    dbms_output.put_line('编号:'||my.empno||'姓名:'||my.ename||'薪水:'||my.sal);

  end loop;

end;

十六、      使用游标生成部门员工报表

       begin

        for myd in (select * from dept) loop

          dbms_output.put_line(myd.deptno||'      '||myd.dname||'      '||myd.loc);

          dbms_output.put_line('------------------------------------------');

          for mye in (select empno,ename,sal from emp where deptno = myd.deptno)loop

            dbms_output.put_line(mye.empno||'      '||mye.ename||'      '||mye.sal);

          end loop;

         endloop;

       end;

得到的结果如下面所示:

10       ACCOUNTING      NEW YORK

------------------------------------------

7782       CLARK      2450

7839       KING     5000

7934       MILLER      1300

20       RESEARCH      DALLAS

------------------------------------------

7369       SMITH      800

7566       JONES      2975

7788       SCOTT      3000

7876       ADAMS      1100

7902       FORD     3000

30       SALES      CHICAGO

------------------------------------------

7499       ALLEN      1600

7521       WARD     1250

7654       MARTIN      1250

7698       BLAKE      2850

7844       TURNER      1500

7900       JAMES      950

40       OPERATIONS      BOSTON

------------------------------------------

十七、      带参数的游标

1.      游标只能接受传递的值，而不能返回值

2.      参数只定义类型，不定义大小

十八、      使用带参数的游标实现上面的功能

declare

    cursor mye(mydeptno dept.deptno%type) isselect empno,ename,sal from emp where deptno = mydeptno;

begin

  for myd in (select * from dept) loop

    dbms_output.put_line(myd.deptno||'       '||myd.dname||'      '||myd.loc);

    dbms_output.put_line('------------------------------------------');

    for varmye in mye(myd.deptno) loop

      dbms_output.put_line(varmye.empno||'       '||varmye.ename||'      '||varmye.sal);

    end loop;

  end loop;

end;

得到的结果还是上面的表格

十九、      使用游标来更新数据

       DECLARE

          CURSOR c_emp IS

              SELECTempno,ename,sal FROM emp ORDER BY sal DESC FOR UPDATE;

          v_increase NUMBER := 0;

          v_new_sal  NUMBER;

       BEGIN

          FOR r_emp IN c_emp LOOP

              v_new_sal := r_emp.sal + v_increase;

              UPDATE emp SET sal = v_new_sal WHERE CURRENT OF c_emp;

              DBMS_OUTPUT.PUT_LINE(r_emp.empno||'     '||r_emp.ename||'old salary='||r_emp.sal||'     new salary= '||v_new_sal);

              v_increase:=v_increase+100;

              END LOOP;

       END;

二十、      REF游标

在用户运行时动态的决定执行何种查询