7、游标

一、游标语法概念和案例

一.课前回顾
存储过程的用途:
(1)它相当于方法,所以可以通过java代码调用存储过程,
使得存储过程的执行时机是可控的!(想什么时候执行都可以)
(2)存储过程中还可以调用游标!
(3)存储过程中可以执行赋值,sql的增删改查(查一条)
1.存储过程(有参数)
create or replace procedure p_名字
  (v_变量名 in/out 数据类型)
is|as
  定义变量
begin
   执行内容
end;

2.存储过程(无参数)
create or replace procedure p_名字
is|as
  定义变量
begin
   执行内容
end;

3.单纯的调用存储过程
exec p_名字(实参|无参);

4.如果在plsql中调用存储过程,
p_名字(实参|无参);

5.异常
notfound → select ...into 的时候没有查到
others → 其他异常类型,必须放在异常列表的最后,
 相当于java异常中的exception

二.游标
用途:查询批量数据,放入一个游标(集合)
2.1 使用步骤
(1)定义游标(相当于定义了一个集合)
 cursor c_游标名 is 查询语句;

(2)打开游标
open c_游标名;

(3)提取游标内容
fetch c_游标名 into 变量1,变量2,...;

用循环去提取
while c_游标名%found  loop
   读取,打印,赋值
   fetch c_游标名 into 变量1,变量2,...;
end loop;

loop循环
loop
   fetch c_游标名 into 变量1,变量2,...;
   读取,打印,赋值
   exit when c_游标名%notfound;
end loop;

(4)关闭游标
close c_游标名;

(5)使用for 循环隐式打开游标,提取游标,关闭游标
(使用for循环不用显示的打开游标,提取游标,关闭游标)
--隐式的打开游标
for i in c_游标名
loop
    --隐式的提取游标内容
    读取,赋值,打印i.查询出的表字段

--隐式关闭游标
end loop;

(6)存储过程和游标结合的大概
create or replace procedure p_过程名(v_list out varchar2)
is|as
begin
   v_list:=c_游标名;
end;

2.2 使用游标提取emp表中的所有数据
方法1:使用while循环提取游标内容
set serverout on
declare
  --定义存储提取内容的变量
  v_ename emp.ename%type;
  v_sal emp.sal%type;
  
  --定义游标
  cursor c_emp is select ename,sal from emp;
begin
  --打开游标
  open c_emp;
  
  --提取游标内容
  fetch c_emp into v_ename,v_sal ;

  --while循环打印游标读取的内容
  while c_emp%found loop
     dbms_output.put_line(c_emp%rowcount||'--'||v_ename||'--'||v_sal);
     fetch c_emp into v_ename,v_sal ;  
  end loop;

  --关闭游标
  close c_emp;
end;
/

方法2:使用do-while循环提取游标内容
set serverout on
declare
  --定义存储提取内容的变量
  v_ename emp.ename%type;
  v_sal emp.sal%type;
  
  --定义游标
  cursor c_emp is select ename,sal from emp;
begin
  --打开游标
  open c_emp;
  
  --do-while循环打印游标读取的内容
  loop
     --提取游标内容
     fetch c_emp into v_ename,v_sal ;  
     dbms_output.put_line(c_emp%rowcount||'--'||v_ename||'--'||v_sal);
     exit when c_emp%notfound;
  end loop;

  --关闭游标
  close c_emp;
end;
/
    
方法2:使用for循环提取游标内容

set serverout on
declare
  cursor c_emp is select ename,sal from emp;
begin
  for i in c_emp
  loop
     dbms_output.put_line(c_emp%rowcount||'--'||i.ename||'--'||i.sal);
  end loop;
end;
/

2.3 查询所有的员工编号和工资,如果工资小于1200则在原来的基础上加50

--方法1:使用while循环
set serverout on
declare
  --定义游标提取存放数据的变量
  v_empno1 emp.empno%type;
  v_sal1 emp.sal%type;
  
  --定义游标
  cursor c_emp is select empno,sal from emp;
begin
  --打开游标
  open c_emp;
  
  --提取游标中的一行数据
  fetch c_emp into v_empno1,v_sal1;
  while c_emp%found 
  loop
     dbms_output.put_line(v_empno1||'--'||v_sal1);
     if v_sal1<=1200 then
        update emp set sal=v_sal1+50 where empno=v_empno1;
        dbms_output.put_line(v_empno1||'修改成功');
        select empno,sal into v_empno1,v_sal1 from emp
         where empno=v_empno1;
         dbms_output.put_line(v_empno1||'--'||v_sal1);
     end if;
     fetch c_emp into v_empno1,v_sal1;
  end loop;
  close c_emp;
end;
/



--方法2:使用do-while==loop循环
set serverout on
declare
  --定义游标提取存放数据的变量
  v_empno1 emp.empno%type;
  v_sal1 emp.sal%type;
  
  --定义游标
  cursor c_emp is select empno,sal from emp;
begin
  --打开游标
  open c_emp;
  
  fetch c_emp into v_empno1,v_sal1;
  loop
     
     dbms_output.put_line(v_empno1||'--'||v_sal1);
     if v_sal1<=1200 then
        update emp set sal=v_sal1+50 where empno=v_empno1;
        dbms_output.put_line(v_empno1||'修改成功');
        select empno,sal into v_empno1,v_sal1 from emp
         where empno=v_empno1;
         dbms_output.put_line(v_empno1||'--'||v_sal1);
     end if;
     fetch c_emp into v_empno1,v_sal1;
     exit when c_emp%notfound;

  end loop;
  close c_emp;
end;
/

--方法3:使用 for in循环
set serverout on
declare
  v_empno emp.empno%type;
  v_sal emp.sal%type;
  cursor c_emp is select empno,sal from emp;
begin
  for i in c_emp
  loop
    dbms_output.put_line(c_emp%rowcount||'--'||i.empno||'--'||i.sal);
    if i.sal<=1200 then
       update emp set sal=i.sal+50 where empno=i.empno;
       select empno,sal into v_empno,v_sal from emp where empno=i.empno ;

       dbms_output.put_line(i.empno||'数据已经修改');
       dbms_output.put_line(v_empno||'--'||v_sal);
    end if;
  end loop;
end; 
/
  
2.4 定义有参数无返回值的游标
--题目:查询员工的编号和工资
set serverout on
declare
  --定义有参数的游标
  cursor c_emp(v_empno number)
  is 
    select empno,sal from emp where empno=v_empno;
begin
  dbms_output.put_line('编号--工资');
  for i in c_emp(7369)
  loop
    dbms_output.put_line(i.empno||'--'||i.sal);
  end loop;
end;
/

2.5 定义有参数有返回值类型的游标
--do-while循环
set serverout on
declare
  --定义游标的返回值的列集
  TYPE emp_record_type 
  is RECORD(v_empno emp.empno%type,v_sal emp.sal%type );
  
  --定义游标的返回值类型
  v_emp_record_type EMP_RECORD_TYPE;

  --定义有参数有返回值类型的游标
  CURSOR c_emp(v_deptno emp.deptno%type)
    RETURN EMP_RECORD_TYPE
  IS
    select empno,sal from emp where deptno=v_deptno;

begin
  --打开游标
  open c_emp(20);
  dbms_output.put_line('编号--工资');
  loop
    --提取游标内容
    fetch c_emp into v_emp_record_type;
     dbms_output.put_line(v_emp_record_type.v_empno||'--'||v_emp_record_type.v_sal);
    exit when c_emp%notfound;
  end loop;
  close c_emp;
end;
/

--for循环
set serverout on
declare
  --定义游标的返回值的列集
  TYPE emp_record_type 
  is RECORD(v_empno emp.empno%type,v_sal emp.sal%type );

  --定义游标的返回值类型
  v_emp_record_type EMP_RECORD_TYPE;

  --定义有参数有返回值类型的游标
  CURSOR c_emp(v_deptno emp.deptno%type)
    RETURN EMP_RECORD_TYPE
  IS
    select empno,sal from emp where deptno=v_deptno;
begin
  dbms_output.put_line('编号--工资');
  for i in c_emp(20)
  loop
    dbms_output.put_line(i.v_empno||'--'||i.v_sal);
  end loop;
end;
/

二、显示游标、动态游标，动态sql

 --1.显示游标的使用
 --在dos窗口中写plsql块要输出内容必须写下面这句话
 set serverout on
 declare
   --定义两个变量姓名和工资
   name emp.ename%type;
   sal emp.sal%type;
   
   --定义游标
   cursor cursor_emp is
     select ename,sal from emp;
 begin
 
 --开启右边
   open cursor_emp;
   loop
   --提取游标内容
      fetch cursor_emp into name,sal;
      exit when cursor_emp%notfound;
      dbms_output.put_line('第'||cursor_emp%rowcount||'个雇员：'||name||sal);
   end loop;
   
   --关闭游标
   close cursor_emp;
 end;
 
 --dos窗口中写必须用次符号来结束
 /
 
 --输出结果：
第1个雇员：holly233.33
第2个雇员：管未433.33

--2.使用for in循环简化游标的读取自动结束
set serverout on
declare
   --定义游标
   cursor cursor_emp is
      select ename,sal from emp;
begin
  --for in循环游标，当游标中没有数据时自动停止并关闭游标
   for i in cursor_emp
   loop
        dbms_output.put_line('第'||cursor_emp%rowcount||'个雇员：'||i.ename||i.sal);
   end loop; 
end;
/

--3.动态有游标
set serverout on
declare
  --定义弱类型游标数据类型
  type cursor_type_scott is ref cursor;
  
  --定义一个游标变量
  cursor_scott cursor_type_scott;
  
  --定义两个循增量
  i emp%rowtype;
  j dept%rowtype;
  
begin
  --游标读取员工表
  --打开游标并将内容放入游标变量
  open cursor_scott for select * from emp where deptno=20;
  loop
     --提取游标变量里的内容
    fetch cursor_scott into i;
    
    --循环提取结束条件
    exit when cursor_scott%notfound;
    dbms_output.put_line(i.ename||'的雇佣日期是'||i.hiredate);
  end loop;
  
  --游标读取部门表
  open cursor_scott for select * from dept where deptno in(10,20);
  loop
    fetch cursor_scott into j;
    exit when cursor_scott%notfound;
    dbms_output.put_line(j.deptno||'表示'||j.dname);
  end loop;
  
  --关闭游标
  close cursor_scott;
end;
/

--输出结果为：
管未的雇佣日期是03-3月 -14
10表示ACCOUNTING
20表示RESEARCH


--4.动态sql创建表
--根据用户输入的表名及字段名等参数动态创建表t1
set serverout on
declare
   table_name varchar2(20);--表名
   field1 varchar2(20);--字段名
   datatype1 varchar2(20);-- 字段类型
   field2 varchar2(20);--字段名
   datatype2 varchar2(20);-- 字段类型
   str_sql varchar2(500); --存放sql语句的变量
   
begin
   table_name:='t1';
   field1:='id';
   datatype1:='number';
   field2:='name';
   datatype2:='varchar2(20)';
   str_sql:='create table '||table_name||'('||field1||'  '||datatype1||','||field2||' '||datatype2||')';
   
   --动态创建表
   execute immediate str_sql;
 
exception
  when others then
  dbms_output.put_line('操作失败！');

end;
/

运行结果：
PL/SQL 过程已成功完成。
 desc t1;
 名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
NAME                                               VARCHAR2(20)


--5.动态sql插入数据
set serverout on
declare
   id number;--输入序号
   name varchar2(20);  --输入姓名
   str_sql varchar2(500);  --存储sql语句
begin
   id:=1;
   name:='Tom';
   str_sql:='insert into t1 values(:1,:2)';
   --动态插入数据
   execute immediate str_sql using id,name;  
exception
   when others then
   dbms_output.put_line('操作失败！');
end;
/

select * from t1;

运行结果：
PL/SQL 过程已成功完成。
 select * from t1;

    ID NAME
------ ------------
     1 Tom
     

--6.动态sql查询总的数据条数
set serverout on
declare
   v_id number:=1;
   v_count number;
   str_sql varchar2(500);
begin
   str_sql:='select count(1) from t1 where id=:id';
   --动态查询sql语句
   execute immediate str_sql into v_count using v_id;
   dbms_output.put_line('总的数据条数为：'||v_count);
end;
/   

--7.通过动态游标和动态sql一起使用查询多行数据
set serverout on
declare  
  type emp_cur is ref cursor;
  my_emp_cur emp_cur;
  my_emp_rec emp%rowtype;
   
begin
  open my_emp_cur for 'select * from emp where deptno=:x' using 30;
  loop 
    fetch my_emp_cur into my_emp_rec;
    exit when my_emp_cur%notfound;
    dbms_output.put_line(my_emp_rec.ename||'→'||my_emp_rec.sal);
  end loop;
end;
/

运行结果：
ALLEN→1600
WARD→1250
MARTIN→1250
BLAKE→2850
TURNER→1500
JAMES→950

PL/SQL 过程已成功完成。


--8.使用动态sql的DBMS_SQL包根据用户输入的表名、字段名及字段类型见表
set serverout on
declare
   table_name2 varchar2(20); 
   field1 varchar2(20);
   datatype1 varchar2(20);
   field2 varchar2(20);
   datatype2 varchar2(20);
   v_cursor number;
   v_string varchar2(200);
   v_row number;
  
begin
   table_name2:='t2';
   field1:='id';
   datatype1:='number';
   field2:='name';
   datatype2:='varchar2(20)';
   v_cursor:=dbms_sql.open_cursor;
   v_string:='create table '||table_name2||'('||field1||' '||datatype1||','||field2||' '||datatype2||')';
   dbms_sql.parse(v_cursor,v_string,dbms_sql.native);
   v_row:=dbms_sql.execute(v_cursor);
   dbms_sql.close_cursor(v_cursor);
   dbms_output.put_line(v_row);
exception
   when others then
      dbms_sql.close_cursor(v_cursor);
   raise;   
end;
/

desc t2;
名称                                      是否为空? 类型
----------------------------------------- -------- -------------
ID                                                 NUMBER
NAME                                               VARCHAR2(20)


--9.使用动态sql的DBMS_SQL包将表中t1中id=1的名称改为marry
set serverout on
declare
   id number;
   name varchar2(20);
   v_cursor number;
   v_string varchar2(200);
   v_row number;
  
begin
   id:=1;
   name:='Marry';
   v_cursor:=dbms_sql.open_cursor;
   v_string:='update t1 set name=:p_name where id=:p_id';
   dbms_sql.parse(v_cursor,v_string,dbms_sql.native);
   dbms_sql.bind_variable(v_cursor,':p_name',name);
   dbms_sql.bind_variable(v_cursor,':p_id',id);
   v_row:=dbms_sql.execute(v_cursor);
   dbms_sql.close_cursor(v_cursor);
exception
   when others then
      dbms_sql.close_cursor(v_cursor);
   raise;   
end;
/

PL/SQL 过程已成功完成。
select * from t1;


        ID NAME
---------- ----------------
         1 Marry

三、隐式游标

一.隐式游标
调用游标时:SQL%

1.1 删除某个部门下的员工信息,如果该部门下没有员工,则删除部门表中的该部门信息

set serverout on
declare
  --动态输入删除的条件
  v_deptno emp.deptno%type:=&p_deptno;
  --定义接受受影响行数的变量
  v_count number;
begin
  delete from emp where deptno=v_deptno;
  --接受受影响的行数赋值给变量
  v_count:=SQL%rowcount;
  
--判断受影响的行数是否>0,如果为true就删除员工,否则删除部门信息
  if v_count>0 then
   dbms_output.put_line('部门编号为:'||v_deptno||'的员工信息已经被删除');
  else
    delete from dept where deptno=v_deptno; 
    if SQL%ROWCOUNT>0 then
    dbms_output.put_line('部门编号为:'||v_deptno||'的部门信息已经被删除');
    else 
     dbms_output.put_line('部门编号为:'||v_deptno||'数据没有找到');
   end if;
 end if;
end;
/

1.2 动态游标
--1.2.1 使用动态的弱类型游标实现查询员工信息和部门信息
set serverout on
declare
   --<1>定义弱类型游标
  TYPE TYPE_CURSOR_SCOTT IS REF CURSOR;

  --<2>定义游标变量
  c_scott TYPE_CURSOR_SCOTT;

  --<3>定义存储两个表数据的变量,相当于list
  e emp%rowtype; 
  d dept%rowtype;

  --<4>定义一个查询条件的变量
  v_deptno emp.deptno%type:=&p_deptno;

begin
  --游标读取员工信息表
  --打开游标并将内容放入游标变量
  open c_scott 
    for
    select * from emp where deptno=v_deptno;
    dbms_output.put_line('员工编号--员工工资');
   loop
     fetch c_scott into e;
     exit when c_scott%notfound;
     dbms_output.put_line(e.empno||'--'||e.sal);
   end loop;

  --打开游标并将内容放入游标变量
  open c_scott 
    for
    select * from dept;
   dbms_output.put_line('部门编号--部门名字');
   --循环读取游标内容
   loop
     fetch c_scott into d;
     exit when c_scott%notfound;
     dbms_output.put_line(d.deptno||'--'||d.dname);
   end loop;
 
--关闭游标
close c_scott;
end;
/


输入 p_deptno 的值:  10
原值   13:   v_deptno emp.deptno%type:=&p_deptno;
新值   13:   v_deptno emp.deptno%type:=10;
7782--2450
7839--5000
7934--1300
128--ccc
143--新部门11
144--啊啊啊
10--统计1部
20--RESEARCH
30--销售7部
40--OPERATIONS
41--测试部
42--产品研发部
49--d1
104--商业3部

1.3 使用动态sql创建表
set serverout on
declare
  --①.定义表名
  table_name varchar2(20);
  --②定义id列
  id_name varchar2(20);
  
  --③定义id列的数据类型
  idtype varchar2(20);
  
  --④定义name列
  name varchar2(20);
  
  --⑤定义name列的数据类型
  nametype varchar2(20);

  --⑥定义存放sql语句的变量
  strsql varchar2(500);
begin
  table_name:='t1';
  id_name:='id';
  idtype:='number';
  name:='name';
  nametype:='varchar2(20)';

  strsql:='create table '||table_name
||'('||id_name||' '||idtype||','||name||' '|nametype||')';
  dbms_output.put_line('sql语句:'||strsql);

  --执行动态sql语句
  execute immediate strsql;
exception
  when others then
    dbms_output.put_line('创建表失败!');
end;
/

--1.4.动态sql插入数据
set serverout on
declare
   id number;--输入序号
   name varchar2(20);  --输入姓名
   str_sql varchar2(500);  --存储sql语句
begin
   id:=1;
   name:='Tom';
   str_sql:='insert into t1 values(:1,:2)';
   --动态插入数据
   execute immediate str_sql using id,name;  
exception
   when others then
   dbms_output.put_line('操作失败！');
end;
/

select * from t1;

--1.5.动态sql查询总的数据条数(没有条件的)
set serverout on
declare
   v_count number;
   str_sql varchar2(500);
begin
   str_sql:='select count(1) from t1';
   --动态查询sql语句
   execute immediate str_sql into v_count;
   dbms_output.put_line('总的数据条数为：'||v_count);
end;
/ 

--1.6.动态sql查询总的数据条数(有条件的)
set serverout on
declare
   v_id number;
   v_count number;
   str_sql varchar2(500);
begin
   v_id:=1;
   str_sql:='select count(1) from t1 where id=:id';
   --动态查询sql语句
   execute immediate str_sql into v_count using v_id;
   dbms_output.put_line('总的数据条数为：'||v_count);
end;
/ 

--1.7 动态sql和动态游标结合查询多行数据
set serverout on
declare
  --定义游标类型
  TYPE c_type_emp IS REF CURSOR; 

  --定义游标类型变量
  c_emp c_type_emp;
 
  --定义存储where条件的变量
  v_deptno emp.deptno%type:=&p_deptno;

  --定义行数据类类型(表=集合)
  list emp%rowtype;
   
begin
  --打开游标,同时使用动态sql,给占位符赋值
  open c_emp
    for 'select * from emp where deptno=:deptno'
    using v_deptno;
  
  --循环提取游标内容
  loop
     --先提取一行数据
     fetch c_emp into list;
     exit  when c_emp%notfound ;

     dbms_output.put_line(list.ename||'-'||list.deptno);
  end loop;
  
  --关闭游标
  close c_emp;
end;
/

输入 p_deptno 的值:  30
原值    9:   v_deptno emp.deptno%type:=&p_deptno;
新值    9:   v_deptno emp.deptno%type:=30;
ALLEN-30
WARD-30
MARTIN-30
BLAKE-30
TURNER-30
JAMES-30