Oracle 游标 静态游标

[size=medium][/size]SQL> set serveroutput on;
SQL> remark 游标：一个特定结果集中的行指针，它指向确定的记录，通过移动游标可以操作不同的行数据
SQL> remark 游标的类型：静态游标  引用游标
SQL> remark 静态游标分为：隐式游标  显示游标
SQL> remark 隐式游标：系统进行查询和修改操作的时候自动调用的
SQL> remark 显示游标：开发人员显示的定义游标的查询语句
SQL> remark 游标的属性 4  个:
SQL> remark %isopen %found %notfound %rowcount 对应的关系分别是：
SQL> remark %isopen :判断游标是否被打开  true/false;
SQL> remark %found ：判断游标的行是否有效 与 %nofound 结果是相反的
SQL> remark %rowcount 返回当前位置为止游标读取的记录行数。 值为整数
SQL> remark ....显示游标-普通游标--.................
SQL> declare
  2  cursor curemp is select empno ,ename from emp;
  3  theempno emp.empno%type;
  4  theename emp.ename%type;
  5  begin
  6  open curemp;
  7  loop
  8  fetch curemp into theempno,theename;
  9  exit when curemp%nofound;
10  dbms_output.put_line('theempno'||theempno);
11  end loop;
12  close curemp;
13  end;
14  /
exit when curemp%nofound;
                 *
ERROR at line 9:
ORA-06550: line 9, column 19:
PLS-00208: identifier 'NOFOUND' is not a legal cursor attribute
ORA-06550: line 9, column 2:
PL/SQL: Statement ignored


SQL> edit;
Wrote file afiedt.buf

  1  declare
  2  cursor curemp is select empno ,ename from emp where deptno=30;
  3  theempno emp.empno%type;
  4  theename emp.ename%type;
  5  begin
  6  open curemp;
  7  loop
  8  fetch curemp into theempno,theename;
  9  exit when curemp%notfound;
10  dbms_output.put_line('theempno'||theempno);
11  end loop;
12  close curemp;
13* end;
SQL> /
theempno7499                                                                   
theempno7521                                                                   
theempno7654                                                                   
theempno7698                                                                   
theempno7844                                                                   
theempno7900                                                                   

PL/SQL procedure successfully completed.

SQL> remark 使用fetch从游标中获取数据给参数赋值的时候要与查询语句匹配，如果查询语句式select * from....那么需要定一个rowtype的变量
SQL> declare
  2  emprow emp%rowtype;
  3  cursor empcur is select * from emp where deptno=30;
  4  begin
  5  open empcur;
  6  loop
  7  fetch empcur into emprow;
  8  exit when empcur%notfound;
  9  dbms_output.put_line('编号：'||emprow.empno);
10  end loop;
11  close empcur;
12  end;
13  /
编号：7499                                                                     
编号：7521                                                                     
编号：7654                                                                     
编号：7698                                                                     
编号：7844                                                                     
编号：7900                                                                     

PL/SQL procedure successfully completed.

SQL> remark　RowType 类型，能获取一整行的结果集；
SQL> remark 使用显示游标的时候应该注意事项：
SQL> remark 显示游标一定关联一个查询的结果集，并且在游标定义的时候就更这个结果集绑定到了一起；
SQL> remark ............隐式游标...................
SQL> remark 对于隐式游标而言SQL%ISOPEN总是false，这是因为隐式游标在DML语句执行的时候打开，结束时就立刻关闭了。例如：
SQL> begin
  2  update emp set sal=sal*1.1 where deptno=30;
  3  DBMS_OUTPUT.PUT_LINE('被修改记录的条数为：'||SQL%ROWCOUNT);
  4  END;
  5  /
被修改记录的条数为：6                                                          

PL/SQL procedure successfully completed.

SQL> REMARK ...............................................................
SQL> REMARK ............带参数的游标.....................................
SQL> REMARK 在ORACLE中设定的参数只能在定义游标的查询中使用。
SQL> EDIT
Wrote file afiedt.buf

  1  DECLARE
  2  THEEMPNO EMP.DEPTNO%TYPE;
  3  EMPROW EMP%ROWTYPE;
  4  CURSOR EMPCUR(DELDEPTNO EMP.DEPTNO%TYPE) IS SELECT * FROM EMP WHERE DEPTNO=DELDEPTNO;
  5  BEGIN
  6  THEEMPNO:=&员工编号;
  7  OPEN EMPCUR(THEEMPNO);
  8  DBMS_OUTPUT.PUT_LINE('游标的记录行数--'||EMPCUR%ROWCOUNT);
  9  LOOP
10  FETCH EMPCUR INTO EMPROW;
11  EXIT WHEN EMPCUR%NOTFOUND;
12  DBMS_OUTPUT.PUT_LINE('编号：'||EMPROW.EMPNO||'员工姓名：'||EMPROW.ENAME);
13  END LOOP;
14  DBMS_OUTPUT.PUT_LINE('游标的记录条数为：'||EMPCUR%ROWCOUNT);
15  CLOSE EMPCUR;
16* END;
SQL> /
Enter value for 员工编号: 20
old   6: THEEMPNO:=&员工编号;
new   6: THEEMPNO:=20;
游标的记录行数--0                                                              
编号：7369员工姓名：SMITH                                                      
编号：7566员工姓名：JONES                                                      
编号：7788员工姓名：SCOTT                                                      
编号：7876员工姓名：ADAMS                                                      
编号：7902员工姓名：FORD                                                       
游标的记录条数为：5                                                            

PL/SQL procedure successfully completed.

SQL> REMARK 循环游标
SQL> REMARK 操作游标的过程：打开  循环取游标数据，操作数据 关闭游标的动作
SQL> REMARK 使用循环游标可以省去这些烦琐
SQL> DECLARE
  2  CURSOR EMPCUR IS SELECT * FROM EMP WHERE DEPTNO=30;
  3  BEGIN
  4  FOR EMPROW IN EMPCUR
  5  LOOP
  6  DBMS_OUTPUT.PUT_LINE('编号：'||EMPROW.EMPNO||'姓名：'||EMPROW.ENAME);
  7  END LOOP;
  8  END;
  9  /
编号：7499姓名：ALLEN                                                          
编号：7521姓名：WARD                                                           
编号：7654姓名：MARTIN                                                         
编号：7698姓名：BLAKE                                                          
编号：7844姓名：TURNER                                                         
编号：7900姓名：JAMES                                                          

PL/SQL procedure successfully completed.

SQL> REMARK FOR 查询变量 IN 游标变量；
SQL> REMARK 查询变量的使用不需要声明
SQL> REMARK 查询变量是一个行对象，其结构与查询语句查询列表一致。
SQL> REMARK 获取的方法：查询变量.列名；
SQL> REMARK 查询变量只能取数据，不能给查询变量赋值；
SQL> REMARK ..........................................................
SQL> REMARK 0........................................................
SQL> REMARK 利用游标修改数据
SQL> REMARK 使用游标修改数据是：逐条获取，逐条处理；利用游标进行数据查询，需要在打开游标的时候就进行数据的锁定；
SQL> REMARK 锁定数据的语法：
SQL> REMARK 游标定义语法：CURSOR 游标名称 IS 查询语句 FOR UPDATE;
SQL> REMARK 对当前游标进行删除和修改的语法：
SQL> REMARK UPDATE 语句 WHERE CURRENT OF 游标名称；
SQL> REMARK DELETE 语句 WHERE CURRENT OF 游标名称；
SQL> DECLARE
  2  CURSOR EMPCUR IS SELECT * FROM EMP FOR UPDATE;
  3  BEGIN
  4  FOR EMPROW IN EMPCUR
  5  LOOP
  6  IF EMPROW.DEPTNO=20 THEN
  7  DBMS_OUTPUT.PUT_LINE('被删除的记录编号为：'||EMPROW.EMPNO);
  8  DELETE FROM EMP WHERE CURRENT OF EMPCUR;
  9  END IF;
10  END LOOP;
11  END;
12  /
被删除的记录编号为：7369                                                       
被删除的记录编号为：7566                                                       
被删除的记录编号为：7788                                                       
被删除的记录编号为：7876                                                       
被删除的记录编号为：7902                                                       

PL/SQL procedure successfully completed.

SQL> SPOOL OFF;
[color=olive][/color]