mysql存储过程游标中试用变量存储过程游标的用法

转载

footballboy 2023-12-04 19:21:08

文章标签 mysql存储过程游标中试用变量游标存储过程数据库结果集 文章分类 MySQL 数据库

最近在熟悉公司代码时遇到了一些SQL数据库存储过程的使用，在存储过程中遇到了游标cursor，之前对游标不怎么了解，现整理一些关于游标的知识共享。

1、关于游标：

游标：用来查询数据库，获取记录集合（结果集）的指针，我们所说的游标通常是指显式游标，因此从现在起没有特别指明的情况，我们所说的游标都是指显式游标。

2、游标作用：

就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

有以下几点：
（1）指定结果集中特定行的位置。
（2）基于当前的结果集位置检索一行或连续的几行。
（3）在结果集的当前位置修改行中的数据。
（4）对其他用户所做的数据更改定义不同的敏感性级别。
（5）可以以编程的方式访问数据库。

3、游标的生命周期：

游标FOR循环，在大多数的时候我们在设计程序的时候都遵循下面的步骤，也可以说是游标的生命周期：

　1、打开游标 open cs1;

　2、开始循环 while cs1%found loop | for column_name in .. LOOP

　3、从游标中取值 fetch .. into.. |

　4、检查那一行被返回

　5、处理

　6、关闭循环 end loop;

　7、释放游标 if cs1&isopen then close cs1;

4、一个完整游标实例：

选项：参数和返回类型

set serveroutput on 
 
 declare 
 
 cursor emp_cur ( p_deptid in number) is 
 
 select * from employees where department_id = p_deptid 
 
 l_emp employees%rowtype; 
 
 BEGIN 
 
  dbms_output.put_line('Getting employees from department 30') 
 
 open emp_cur(30) 
 
 loop 
 
 fetch emp_cur into l_emp; 
 
 exit when emp_cur%notfound; 
 
 dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is ') 
 
 dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name) 
 
 end loop 
 
 close emp_cur 
 
 dbms_output.put_line('Getting employees from department 90') 
 

 open emp_cur(90) 
 
 loop 
 
 fetch emp_cur into l_emp 
 
 exit when emp_cur%notfound 
 
 dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is ') 
 
 dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name) 
 
 end loop 
 
 close emp_cur 
 

 END

5、游标属性

游标的状态是通过属性来表示

%FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。

%NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。

%ROWCOUNT：当前时刻已经从游标中获取的记录数量

%ISOPEN：是否打开

相关代码如下

Declare 
 
  Cursor emps is 
 
  Select * from employees where rownum<6 order by 1; 
 
   
 
  Emp employees%rowtype; 
 
  Row number :=1; 
 
 Begin 
 
  Open emps; 
 
  Fetch emps into emp; 
 
   
 
  Loop 
 
   If emps%found then 
 
    Dbms_output.put_line('Looping over record '||row|| ' of ' || emps%rowcount); 
 
    Fetch emps into emp; 
 
    Row := row + 1; 
 
   Elsif emps%notfound then 
 
    Exit;  ---exit loop, not IF 
 
   End if; 
 
  End loop; 
 
   
 
  If emps%isopen then 
 
   Close emps; 
 
  End if; 
 
 End;

6、显式和隐式游标的区别：

尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。
REF CURSOR游标：

7、静态游标和REF 游标的区别：
（1）静态游标是静态定义，REF 游标是动态关联；
（2）使用REF 游标需REF 游标变量。
（3）REF 游标能做为参数进行传递，而静态游标是不可能的

8、动态游标，在运行的时候才能确定游标使用的查询。

A、声明REF 游标类型，确定REF 游标类型
（1）强类型REF游标：指定retrun type，REF 游标变量的类型必须和return type一致。
语法：Type REF游标名 IS Ref Cursor Return 结果集返回记录类型；
（2）弱类型REF游标：不指定return type，能和任何类型的CURSOR变量匹配，用于获取任何结果集。
语法：Type REF游标名 IS Ref Cursor；

B、声明Ref 游标类型变量
语法：变量名已声明Ref 游标类型；
C、打开REF游标，关联结果集
语法：Open Ref 游标类型变量 For 查询语句返回结果集；
D、获取记录，操作记录
语法：Fatch    REF游标名 InTo   临时记录类型变量或属性类型变量列表；
E、关闭游标，完全释放资源；
语法：Close   REF游标名；

分类：
强类型（限制）REF CURSOR，规定返回类型
弱类型（非限制）REF CURSOR，不规定返回类型，可以获取任何结果集。

TYPE ref_cursor_name IS REF CURSOR [RETURN return_type] 
 
 Declare 
 
 Type refcur_t is ref cursor  
 
 Type emp_refcur_t is ref cursor return employee%rowtype 
 
 Begin 
 
  Null 
 
 End

9、游标类型：

（1）静态游标：结果集已经确定（静态定义）的游标。分为隐式和显式游标。
A、隐式游标：所有DML语句为隐式游标，通过隐式游标属性可以获取SQL语句信息。当直接在代码中执行一条SQL语句时，只要该代码没有显式声明一个游标，PL/SQL就会产生一个隐式游标。如：插入操作：INSERT、更新操作：UPDATE、删除操作：DELETE、单行查询操作：SELECT...INTO... 。当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。
B、显式游标：用户显示声明的游标，即指定结果集。当查询返回结果超过一行时，就需要一个显式游标。
（2）REF游标（动态游标）：动态关联结果集的临时对象。即在运行的时候动态决定执行查询。实现在程序间传递结果集的功能，利用REF CURSOR也可以实现BULK SQL，从而提高SQL性能。
在变量声明部分定义的游标是静态的，不能在程序运行过程中修改。虽然可以通过参数传递来取得不同的数据，但还是有很大的局限性。通过采用动态游标，可以在程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型，然后声明一个游标变量，游标对应的查询语句可以在程序的执行过程中动态地说明。

（3）要在程序中使用游标，必须首先声明游标分类，游标分类如下

静态游标：分为显式游标和隐式游标。

REF游标：是一种引用类型，类似于指针。

显式游标： CURSOR 游标名 ( 参数 ) [返回值类型] IS Select 语句

10、REF 游标变量
是一种引用 REF游标类型的变量，指向动态关联的结果集。
特点：
（1）可与不同查询关联，可从不同结果集中取数；
（2）可作为过程或函数的参数传递。实现对游标的共享；
（3）可以使用静态游标所具有的全部功能；
（4）可以将一个游标变量内容（包括它的结果集）赋给另一个游标变量。

11、使用显示游标与遍历循环游标

A、使用显示游标

（1）声明游标：划分存储区域，注意此时并没有执行Select 语句。
（DELARE） CURSOR 游标名(参数列表) [返回值类型] IS Select 语句；

参数只定义数据类型，没有大小(所有Oracle中的形参只定义数据类型，不指定大小)。游标只能接受传递的值，而不能返回值。
参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。如：

（DELARE） CURSOR 游标名(参数名数据类型 DEFAULT 默认值，。。。) [返回值类型] IS Select 语句。

SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。

（2）打开游标：执行Select 语句，获得结果集存储到游标中，此时游标指向结果集头，而不是第一条记录。
Open 游标名( 参数列表)；

（3）获取记录：移动游标取一条记录
Fetch 游标名 InTo 临时记录或属性类型变量；

（4）关闭游标：将游标放入缓冲池中，没有完全释放资源。可重新打开。
Close 游标名；

显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

B、遍历循环游标
（1）For 循环游标
   循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。
     For 变量名 In 游标名
     Loop
      数据处理语句；
     End Loop；
（2）Loop循环游标
    Loop
     Fatch 游标名 InTo 临时记录或属性类型变量；
     Exit When 游标名%NotFound；
数据处理语句；
    End   Loop；
（3）Loop循环游标
    FETCH 游标名 INTO 临时记录或属性类型变量;
    WHILE 游标名%FOUND LOOP
      数据处理语句；
      FETCH 游标名临时记录或属性类型变量;
    END LOOP;

12、更新和删除显示游标中的记录
A、UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。要使用这个方法，在声明游标时必须使用FOR UPDATE子串，当对话使用FOR UPDATE子串打开一个游标时，所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，其他对象只能查询这些数据行，不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。
在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，直到数据行解锁。
B、使用更新或删除：
（1）声明更新或删除显示游标：
   Cursor 游标名 IS SELECT 语句   For Update [ Of 更新列列名]；
   Cursor 游标名 IS SELECT 语句   For Delete [ Of 更新列列名]；
（2）使用显示游标来更新或删除当前记录：
   Update 表名   SET   更新语句 Where   Current Of   游标名；
   Delete From 表名   Where   Current Of   游标名；

13、强类型举例：
declare --声明记录类型

type emp_job_rec is record(
   employee_id number,
   employee_name varchar2(50),
   job_title varchar2(30)
  );
  --声明REF CURSOR，返回值为该记录类型
  type emp_job_refcur_type is ref cursor
   return emp_job_rec;
  --定义REF CURSOR游标的变量
  emp_refcur emp_job_refcur_type;
  emp_job emp_job_rec;
 begin
  open emp_refcur for
   select e.employee_id,
     e.first_name || ' ' ||e.last_name "employee_name",
     j.job_title
   from employees e, jobs j
   where e.job_id = j.job_id and rownum < 11 order by 1;
  fetch emp_refcur into emp_job;
  while emp_refcur%found loop
   dbms_output.put_line(emp_job.employee_name || '''s job is ');
   dbms_output.put_line(emp_job.job_title);
   fetch emp_refcur into emp_job;
  end loop;
 end;

14、FOR循环及实例

游标FOR循环，在大多数的时候我们在设计程序的时候都遵循下面的步骤：

　1、打开游标

　2、开始循环

　3、从游标中取值

　4、检查那一行被返回

　5、处理

　6、关闭循环

　7、关闭游标

可以简单的把这一类代码称为游标用于循环。但还有一种循环与这种类型不相同，这就是FOR循环，用于FOR循环的游标按照正常的声明方式声明，它的优点在于不需要显式的打开、关闭、取数据，测试数据的存在、定义存放数据的变量等等。游标FOR 循环的语法如下：
　

FOR record_name IN 
 
 　　(corsor_name[(parameter[,parameter]...)] 
 
 　　| (query_difinition) 
 
 　　LOOP 
 
 　　statements 
 
 　　END LOOP;

　下面我们用for循环重写上面的例子：

DECALRE 
 
 　　CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno; 
 
 　　CURSOR c_emp (p_dept VARACHAR2) IS 
 
 　　SELECT ename,salary 
 
 　　FROM emp 
 
 　　WHERE deptno=p_dept 
 
 　　ORDER BY ename 
 
 　　v_tot_salary EMP.SALARY%TYPE; 
 
 　　BEGIN 
 
 　　FOR r_dept IN c_dept LOOP 
 
 　　DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname); 
 
 　　v_tot_salary:=0; 
 
 　　FOR r_emp IN c_emp(r_dept.deptno) LOOP 
 
 　　DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary); 
 
 　　v_tot_salary:=v_tot_salary+v_salary; 
 
 　　END LOOP; 
 
 　　DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary); 
 
 　　END LOOP; 
 
 　　END;

在游标FOR循环中可以定义查询，由于没有显式声明所以游标没有名字，记录名通过

DECALRE
 v_tot_salary EMP.SALARY%TYPE;
 　　BEGIN
 FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP
 　DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
 　v_tot_salary:=0;
 　FOR r_emp IN (SELECT ename,salary
 　　FROM emp
 　　WHERE deptno=p_dept
 　　ORDER BY ename) LOOP
 　DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);
 　v_tot_salary:=v_tot_salary+v_salary;
 　END LOOP;
 　DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
 END LOOP;
 　　END;

　游标中的子查询
　语法如下：

CURSOR C1 IS SELECT * FROM emp
 　　WHERE deptno NOT IN (SELECT deptno
 　　FROM dept
 　　WHERE dname!='ACCOUNTING');

　可以看出与SQL中的子查询没有什么区别。

CURSOR FOR LOOP，用于for loop 语句
1举例：

declare
 begin
  update departments set department_name=department_name;
  --where 1=2;
  
  dbms_output.put_line('update '|| sql%rowcount ||' records');

end;
2举例：

declare
 begin
  for my_dept_rec in ( select department_name, department_id from departments)
  loop
   dbms_output.put_line(my_dept_rec.department_id || ' : ' || my_dept_rec.department_name);
  end loop;
 end;

3举例：
单独select

declare
  l_empno emp.EMPLOYEE_ID%type;
 -- l_ename emp.ename%type;
 begin
  select EMPLOYEE_ID   
   into l_empno
  from emp;
  --where rownum =1;
  dbms_output.put_line(l_empno);
 end;

使用INTO获取值，只能返回一行。

15、游标的%TYPE属性
　在PL/SQL中可以将变量和常量声明为内建或用户定义的数据类型，以引用一个列名，
同时继承他的数据类型和大小。这种动态赋值方法是非常有用的，比如变量引用的列的数据类
型和大小改变了，如果使用了%TYPE,那么用户就不必修改代码，否则就必须修改代码。
例：
　　v_empno SCOTT.EMP.EMPNO%TYPE;
　　v_salary EMP.SALARY%TYPE;
　不但列名可以使用%TYPE,而且变量、游标、记录，或声明的常量都可以使用%TYPE。

DELCARE
 　　V_A NUMBER(5):=10;
 　　V_B V_A%TYPE:=15;
 　　V_C V_A%TYPE;
 　　BEGIN
 　　DBMS_OUTPUT.PUT_LINE
 　　('V_A='||V_A||'V_B='||V_B||'V_C='||V_C);
 　　END
 　　SQL>/
 　　V_A=10 V_B=15 V_C=
 　　PL/SQL procedure successfully completed.

16、其他DML语句
　其它操作数据的DML语句是:INSERT、UPDATE、DELETE和LOCK TABLE,这些语句在
PL/SQL中的语法与在SQL中的语法相同。我们在前面已经讨论过DML语句的使用这里就不再重
复了。在DML语句中可以使用任何在DECLARE部分声明的变量，如果是嵌套块，那么要注意变
　例：

CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number)
 AS
 　v_ename EMP.ENAME%TYPE;
 BEGIN
 　SELECT ename INTO v_ename
 　FROM emp
 　WHERE empno=p_empno;
 　INSERT INTO FORMER_EMP(EMPNO,ENAME)
 　VALUES (p_empno,v_ename);
 　DELETE FROM emp
 　WHERE empno=p_empno;
 　UPDATE former_emp
 　SET date_deleted=SYSDATE
 　WHERE empno=p_empno;
 　EXCEPTION
 　　WHEN NO_DATA_FOUND THEN
 　　DBMS_OUTPUT.PUT_LINE('Employee Number Not Found!');
 END

17、DML语句的结果
　当执行一条DML语句后，DML语句的结果保存在四个游标属性中，这些属性用于控制程
序流程或者了解程序的状态。当运行DML语句时，PL/SQL打开一个内建游标并处理结果，游标
是维护查询结果的内存中的一个区域，游标在运行DML语句时打开，完成后关闭。隐式游标只
使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT三个属性.SQL%FOUND,SQL%NOTFOUND是布尔值，
　SQL%FOUND和SQL%NOTFOUND
　在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在执行DML语句后，
　. TRUE :INSERT
　. TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE.
　. TRUE :SELECT INTO至少返回一行
　当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE。
　SQL%ROWCOUNT
　在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL,对于SELECT INTO语句，如果
执行成功，SQL%ROWCOUNT的值为1,如果没有成功，SQL%ROWCOUNT的值为0，同时产生一个异常
　SQL%ISOPEN
　SQL%ISOPEN是一个布尔值，如果游标打开，则为TRUE, 如果游标关闭，则为FALSE.对
于隐式游标而言SQL%ISOPEN总是FALSE，这是因为隐式游标在DML语句执行时打开，结束时就
　事务控制语句
　事务是一个工作的逻辑单元可以包括一个或多个DML语句，事物控制帮助用户保证数
据的一致性。如果事务控制逻辑单元中的任何一个DML语句失败，那么整个事务都将回滚，在
PL/SQL中用户可以明确地使用COMMIT、ROLLBACK、SAVEPOINT以及SET TRANSACTION语句。
　COMMIT语句终止事务，永久保存数据库的变化，同时释放所有LOCK,ROLLBACK终止现
行事务释放所有LOCK，但不保存数据库的任何变化,SAVEPOINT用于设置中间点，当事务调用过
多的数据库操作时，中间点是非常有用的，SET TRANSACTION用于设置事务属性。　

18、使用游标
　这里要做一个声明，我们所说的游标通常是指显式游标，因此从现在起没有特别指明
的情况，我们所说的游标都是指显式游标。要在程序中使用游标，必须首先声明游标。
　（1）声明游标
　语法：
　　CURSOR cursor_name IS select_statement;
　在PL/SQL中游标名是一个未声明变量，不能给游标名赋值或用于表达式中。
　例：
　

DELCARE
 　　CURSOR C_EMP IS SELECT empno,ename,salary
 　　FROM emp 　　WHERE salary>2000
 　　ORDER BY ename;
 　　........
 　　BEGIN

　在游标定义中SELECT语句中不一定非要表可以是视图，也可以从多个表或视图中选择
　打开游标
　使用游标中的值之前应该首先打开游标，打开游标初始化查询处理。打开游标的语法
　　OPEN cursor_name
　cursor_name是在声明部分定义的游标名。
　例：

OPEN C_EMP;

　（2）关闭游标
　语法：
　

CLOSE cursor_name

　例：

CLOSE C_EMP;

　（3）从游标提取数据
　从游标得到一行数据使用FETCH命令。每一次提取数据后，游标都指向结果集的下一
　　FETCH cursor_name INTO variable[,variable,...]
　对于SELECT定义的游标的每一列，FETCH变量列表都应该有一个变量与之相对应，变
　例：

SET SERVERIUTPUT ON
 　　DECLARE
 　　v_ename EMP.ENAME%TYPE;
 　　v_salary EMP.SALARY%TYPE;
 　　CURSOR c_emp IS SELECT ename,salary FROM emp;
 　　BEGIN
 　　OPEN c_emp;
 　　FETCH c_emp INTO v_ename,v_salary;
 　　DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename
 　　||'is'|| v_salary);
 　　FETCH c_emp INTO v_ename,v_salary;
 　　DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename
 　　||'is'|| v_salary);
 　　FETCH c_emp INTO v_ename,v_salary;
 　　DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename
 　　||'is'|| v_salary);
 　　CLOSE c_emp;
 　　END

　这段代码无疑是非常麻烦的，如果有多行返回结果，可以使用循环并用游标属性为结
束循环的条件，以这种方式提取数据，程序的可读性和简洁性都大为提高，下面我们使用循环

SET SERVERIUTPUT ON
 　　DECLARE
 　　v_ename EMP.ENAME%TYPE;
 　　v_salary EMP.SALARY%TYPE;
 　　CURSOR c_emp IS SELECT ename,salary FROM emp;
 　　BEGIN
 　　OPEN c_emp;
 　　LOOP
 　　FETCH c_emp INTO v_ename,v_salary;
 　　EXIT WHEN c_emp%NOTFOUND;
 　　DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename
 　　||'is'|| v_salary);
 　　END

　（4）记录变量
　定义一个记录变量使用TYPE命令和%ROWTYPE，关于%ROWsTYPE的更多信息请参阅相关
　记录变量用于从游标中提取数据行，当游标选择很多列的时候，那么使用记录比为每
　当在表上使用%ROWTYPE并将从游标中取出的值放入记录中时，如果要选择表中所有列
，那么在SELECT子句中使用*比将所有列名列出来要安全得多。
　例：

SET SERVERIUTPUT ON
 　　DECLARE
 　　R_emp EMP%ROWTYPE;
 　　CURSOR c_emp IS SELECT * FROM emp;
 　　BEGIN
 　　OPEN c_emp;
 　　LOOP
 　　FETCH c_emp INTO r_emp;
 　　EXIT WHEN c_emp%NOTFOUND;
 　　DBMS_OUT.PUT.PUT_LINE('Salary of Employee'||r_emp.ename||'is'|| r_emp.salary);
 　　END LOOP;
 　　CLOSE c_emp;
 　　END;

　（5）%ROWTYPE也可以用游标名来定义，这样的话就必须要首先声明游标：
　

SET SERVERIUTPUT ON
 　　DECLARE
 　　CURSOR c_emp IS SELECT ename,salary FROM emp;
 　　R_emp c_emp%ROWTYPE;
 　　BEGIN
 　　OPEN c_emp;
 　　LOOP
 　　FETCH c_emp INTO r_emp;
 　　EXIT WHEN c_emp%NOTFOUND;
 　　DBMS_OUT.PUT.PUT_LINE('Salary of Employee'||r_emp.ename||'is'|| r_emp.salary);
 　　END LOOP;
 　　CLOSE c_emp;
 　　END;

　（6）带参数的游标
　与存储过程和函数相似，可以将参数传递给游标并在查询中使用。这对于处理在某种
条件下打开游标的情况非常有用。它的语法如下：
　　CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
　定义参数的语法如下：
　　Parameter_name [IN] data_type[{:=|DEFAULT} value]
　与存储过程不同的是，游标只能接受传递的值，而不能返回值。参数只定义数据类型
　另外可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。游标
中定义的参数只是一个占位符，在别处引用该参数不一定可靠。
　在打开游标时给参数赋值，语法如下：

OPEN cursor_name[value[,value]....];

　参数值可以是文字或变量。
　例：
　

DECALRE
 　　CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno;
 　　CURSOR c_emp (p_dept VARACHAR2) IS
 　　SELECT ename,salary
 　　FROM emp
 　　WHERE deptno=p_dept
 　　ORDER BY ename
 　　r_dept DEPT%ROWTYPE;
 　　v_ename EMP.ENAME%TYPE;
 　　v_salary EMP.SALARY%TYPE;
 　　v_tot_salary EMP.SALARY%TYPE;
 　　BEGIN
 　　OPEN c_dept;
 　　LOOP
 　　FETCH c_dept INTO r_dept;
 　　EXIT WHEN c_dept%NOTFOUND;
 　　DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
 　　v_tot_salary:=0;
 　　OPEN c_emp(r_dept.deptno);
 　　LOOP
 　　FETCH c_emp INTO v_ename,v_salary;
 　　EXIT WHEN c_emp%NOTFOUND;
 　　DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);
 　　v_tot_salary:=v_tot_salary+v_salary;
 　　END LOOP;
 　　CLOSE c_emp;
 　　DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
 　　END LOOP;
 　　CLOSE c_dept;
 　　END;

　
　
　（7）游标中的更新和删除
　在PL/SQL中依然可以使用UPDATE和DELETE语句更新或删除数据行。显式游标只有在需
要获得多行数据的情况下使用。PL/SQL提供了仅仅使用游标就可以执行删除或更新记录的方法
　UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作
的表中取出的最近的数据。要使用这个方法，在声明游标时必须使用FOR UPDATE子串，当对话
使用FOR UPDATE子串打开一个游标时，所有返回集中的数据行都将处于行级（ROW-LEVEL)独占
式锁定，其他对象只能查询这些数据行，不能进行UPDATE、DELETE或SELECT...FOR UPDATE操
　语法：
　　FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..
　　[nowait]
　在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择
的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，
　在UPDATE和DELETE中使用WHERE CURRENT OF子串的语法如下：
　　

WHERE{CURRENT OF cursor_name|search_condition}

　例：

DELCARE
 　　CURSOR c1 IS SELECT empno,salary
 　　FROM emp
 　　WHERE comm IS NULL
 　　FOR UPDATE OF comm;
 　　v_comm NUMBER(10,2);
 　　BEGIN
 　　FOR r1 IN c1 LOOP
 　　IF r1.salary<500 THEN
 　　v_comm:=r1.salary*0.25; 　　ELSEIF r1.salary<1000 THEN
 　　v_comm:=r1.salary*0.20; 　　ELSEIF r1.salary<3000 THEN
 　　v_comm:=r1.salary*0.15;
 　　ELSE
 　　v_comm:=r1.salary*0.12;
 　　END IF;
 　　UPDATE emp;
 　　SET comm=v_comm
 　　WHERE CURRENT OF c1l;
 　　END LOOP;
 　　END

附[WHERE CURRENT OF ]:
where current of 子句只能在定义游标的时候使用了 for update语句才可以使用。
for v_sor in t_sor loop
update aa set value='3' where current of v_sor; --已限制了条件了，更新只是当前记录集
end loop;
上面的语句中 v_sor 是 t_sor中的一行数据，更新的时候应该用 current of t_sor吧，因为v_sor只是一个隐式游标，它本身不是通过 for update定义的，只是代表了循环中t_sor的当前记录。这样，你的update语句或者delete语句的作用范围就只在你循环的当前行的范围中了。
要注意的是，用for update定义的游标会让数据库对涉及的行加锁，别的会话如果要访问该游标中的行便会进入等待状态。你也可以明确指明要加锁的列，用 for update of "列名" 就可以了。
如果你的select for update选中的行已经被别的会话加锁的话,会话就需要等待解锁，如果别的会话一直不解锁，那么你的select就会一直等待下去，如果你不想等,只需在for update后面加上nowait就可以解决这个问题了,这样你的选择会立即返回。

文章相关内容出处 http://375940084.blog.51cto.com/2581965/1064924

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。