1.游标的使用。看到的一段解释很好的概念，如下：

    游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。 

游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。 

游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。在我们进行insert、update、delete和select   value into  variable 的操作中，使用的是隐式游标。

隐式游标的属性 返回值类型意义 ： 

SQL%ROWCOUNT    整型  代表DML语句成功执行的数据行数   

SQL%FOUND   布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功   

SQL%NOTFOUND    布尔型 与SQL%FOUND属性返回值相反   

SQL%ISOPEN  布尔型 DML执行过程中为真，结束后为假  

2.隐士游标：

create or replace procedure prc_example (epo in number) as

BEGIN  

        UPDATE emp SET sal=sal+100 WHERE empno=epo;   

         IF SQL%FOUND THEN    

        DBMS_OUTPUT.PUT_LINE('成功修改雇员工资！');   

        COMMIT;    

        ELSE  

        DBMS_OUTPUT.PUT_LINE('修改雇员工资失败！');   

         END IF;    

        END; 

declare

e_number number;

begin

e_number:=7788;

prc_example(e_number);

 end;

3.显示游标：

使用游标查询所有编号为10 的员工名

create or replace procedure prc_example is

begin

    declare

    cursor emp_sor  is select ename,sal from emp where deptno=10;  

    cname emp.ename%type;     

    csal emp.sal%type;

 begin

   open emp_sor;       

   loop        

   fetch emp_sor into cname,csal;  --取游标的值给变量。             

   dbms_output.put_line('ename:'||cname);        

   exit when emp_sor%notfound;        

  end loop;         

  close emp_sor;     

 end;

end;

 

最近在熟悉公司代码时遇到了一些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

 

 

用户变量一般以@开头，作用于全局范围
局部变量需用 declare 定义格式为 declare 变量名 数据类型 [default value];
mysql 数据类型有 int ,float,date,varchar(length)等
声明的顺序必须是 先声明变量，再声明游标、最后声明handler。
同一个存储过程中，一个游标的使用和两个游标的使用是一样的。
调用存储过程 call sp_name()；
查询某数据库中全部存储过程 ：
select name from mysql.proc where db='数据库名'；
或
select routine_name from information_schema.routines where routine_schema='数据库名'；
或
show procedure status where db='数据库名'；
查看单个存储过程： show create procedure 数据库.存储过程名；
删除存储过程 ：drop procedure 存储过程名
存储过程创建语句：
delimiter $$                                                          -- 定义语句结束标志为 $$, 默认结束标志是；
drop procedure if exists test.sp_example$$           -- 创建存储过程关键字
create procedure test.sp_example()                     -- 创建存储过程具体内容：
begin                                                                     -- 存储过程内容以begin开始，end 结束。
declare _inner_code int;                                       --  声明 局部变量 及变量类型
declare _writedate date;
declare _done int default 1;                                  -- 声明 局部变量 、变量类型  及 变量默认值
declare c_cursor cursor for select inner_code,writedate from test.example group by inner_code,writedate;
-- 声明游标名、游标所存储数据
-- 此处可继续申明第二个游标 ： declare a_cursor cursor for select  ... from ...;
declare continue handle for not found set _done=0; --  当出现 not found 的错误时 continue 并将变量_done的值设置为0
start transaction；
open c_cursor;                                                     -- 打开游标
fetch c_cursor into _inner_code,_writedate;
-- 获取当前游标指向的数据行赋值给变量_inner_code,_writedate,并将游标指向下一行
while _done do
功能语句块
fetch c_cursor into _inner_code,_writedate;
/* 获取当前游标指向的数据行赋值给变量_inner_code,_writedate,并将游标指向下一行，当游标已经指向最后一行时会造成游标溢出. mysql 中游标溢出时会引发mysql预定义的not found 错误，在上面定义了一个continue属性的操作handle，当出现not found 错误时 继续，并修改_done变量的值为0，使循环结束*/
end while ;
close c_cursor ;                                                  --  关闭游标
end $$
delimiter ;                                                           -- 将结束标志定义回；
游标嵌套
在mysql中同一个error事件只能定义一次，如果多定义的话在编译时会提示 duplicate handler declared in the same block.
每个begin end 块都是一个独立的scope 区域，嵌套的游标可用begin end 包裹。
drop procedure if exists nest_use;
create procedure nest_use()
begin
declare _n varchar(20);
declare done int default false;
declare cur cursor for select age from store group by age;
declare continue handler for not found set done =true;
open cur ;
read_loop:loop
fetch cur into _n;
if done then
leave read_loop;
end if ;
begin
declare c int ;
declare n varchar(20);
declare total int default 0;
declare done int default false;
declare cur cursor for select name ,count from store where name='iphone';
declare continue handler for not found set done=true;
set total=0;
open cur ;
iphone_loop:loop
fetch cur into n ,c ;
if done then
leave iphone_loop;
end if ;
set total =tatal + c;
end loop;
close cur;
select _n,n,total;
end;
begin
declare c int;
declare n varchar(20);
declare total int default 0;
declare done int default false;
declare cur cursor for select name,count from store where name = 'android';
declare continue HANDLER for not found set done = true;
set total = 0;
open cur;
android_loop:loop
fetch cur into n,c;
if done then
leave android_loop;
end if;
set total = total + c;
end loop;
close cur;
select _n,n,total;
end;
end loop;
close cur;
end ;

1. 存储过程
迄今为止，大多数SQL语句都是针对一个或多个表的单条语句。有一些操作会有一个完整的操作需要多条语句才能完成。
存储过程简单来说就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。
存储过程的优点：
通过把处理封装在容易使用的单元中，简化复杂的操作；由于不要求反复建立一系列处理步骤，保证了数据的完整性，防止错误的发生可能；
简化对变动的管理，安全性；提高性能；存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。
即简单、安全、高性能。
存储过程的缺点：
比较复杂，需要更高的技能；可能没有创建存储过程的安全访问权限，许多数据库管理员限制存储过程的创建权限。
执行存储过程：
MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。
CALL productpricing( @pricelow, @pricehigh, @priceaverage);
其中，执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。
存储过程可以显示结果，也可以不显示结果。
创建存储过程：
CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG(prod_price) AS priceaverage
FROM products;
END;
此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义。
如果存储过程接收参数，它们将在()中列举出来。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句。
在MySQL处理这段代码时，它创建一个新的存储过程productpricing，没有返回数据，因为这段代码并未调用存储过程，这里只是为以后使用而创建它。
由上面看出，存储过程中出现了多个;，如果使用mysql命令行，命令行的语句分隔符也是;，需要一种方法来避免出现错误。
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG(prod_price) AS priceaverage
FROM products;
END //
DELIMITER;
DELIMITER告诉了1命令行使用程序使用//作为新的语句结束分隔符。后一个DELIMITER后接 ; 表示恢复为原来的分隔符。
然后可以CALL这个存储过程：
CALL productpricing();
执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后需要有()符号，即使无参数也要有。
删除存储过程：
存储过程在创建之后，被保存在服务器上以供使用，直至被删除。删除命令从服务器中删除存储过程。
删除存储过程可用DROP PROCEDURE：
DROP PROCEDURE productpricing;
这条语句删除刚创建的存储过程。这是不用加上()。
查看所有的存储过程：
show procedure status;
查看存储过程结构：
show create procedure procname;
存储过程使用参数：
变量variable：内存中一个特定的位置，用来临时存储数据。
CREATE PROCEDURE productpricing(
OUT pl DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
SELECT MIN(prod_price)
INTO pl
FROM products;
SELECT MAX(prod_price)
INTO ph
FROM products;
SELECT AVG(prod_price)
INTO pa
FROM products;
END;
此存储过程接受3个参数。每个参数必须具有指定的类型。关键字OUT指出相应的参数用来从存储过程传出一个值(返回给调用者)。
MySQL支持IN(传递给存储过程)、OUT(从存储过程传出)和INOUT(对存储过程传入和传出)类型的参数。
存储过程的代码位于BEGIN和END语句内，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量中(通过INTO关键字)。
为了调用此修改的存储过程，必须指定3个变量名：
CALL productpricing(@pricelow, @pricehigh, @priceaverage);
由于此存储过程要求3个参数，因此必须正好传递3个参数，不多也不少。这个例子中是存储过程将保存结果的3个变量的名字。
在调用时，这条语句不显示任何数据，它返回以后可以显示(或在其它处理中使用)的变量。
SELECT @priceaverage;
SELECT @pricehigh, @pricelow, @priceaverage;
变量名：所有MySQL变量都必须以@开始。
CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT SUM(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO ototal;
END;
CALL ordertotal(20005, @total);
必须传递给这个存储过程两个参数。
使用智能存储：
-- Name: ordertotal
--
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
)COMMENT 'Obtain order total, optionally adding tax'
BEGIN
DECLARE total DECIMAL(8,2);
DECLARE taxrate INT DEFAULT 6;
SELECT SUM(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;
IF taxable THEN
SELECT total+(total/100*taxrate) INTO total;
END IF;
SELECT total INTO ototal;
END;
此存储过程：增加了注释(前面放置--)。用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型，它也支持可选的默认值，用DEFAULT跟着类型后面。
IF语句检查条件是否为真，如果为真怎么做，否则怎么做。
COMMENT关键字：其将在SHOW PROCEDURE STATUS的结果中显示。
IF语句：IF语句还支持ELSEIF和ELSE子句。
检查存储过程：
为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句：
SHOW CREATE PROCEDURE ordertotal;
获得了包括何时、由谁创建等详细信息的存储过程列表。
2. 使用游标
游标cursor：是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。
只能用于存储过程：MySQL游标只能用于存储过程(和函数)。
使用游标：
在能够使用游标前，必须声明(定义)它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
对于填有数据的游标，根据需要取出(检索)各行。
在结束游标使用时，必须关闭游标。
创建游标：
游标用DECLARE语句创建。DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其它子句。
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
END;
这个存储过程并没有做很多事情，DECLARE语句用来定义和命名游标，这里为ordernumbers。存储过程处理完成后，游标就消失(因为它局限于存储过程)。
打开和关闭游标：
游标用OPEN CURSOR语句来打开：
OPEN ordernumbers;
在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动。
CLOSE ordernumbers;
CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。
在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次声明，用OPEN语句打开它就可以了。
隐含关闭：如果不明确关闭游标，MySQL将会在到达END语句时自动关闭它。
使用游标数据：
在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据(所需的列)，检索出来的数据存储在什么地方。它还向前移动游标中的内部指针，使下一条FETCH语句检索下一行(不重复读取同一行)。
循环检索数据，从第一行到最后一行：
CREATE PROCEDURE processorders()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN ordernumbers;
REPEAT
FETCH ordernumbers INTO o;
UNTIL done END REPEAT;
CLOSE ordernumbers;
END;
REPEAT重复到条件为真(UNTIL done END REPEAT)，此处的done为BOOLEAN条件，当done为真时打破重复。
DECLARE CONTINUE HANDLER声明了done设为1时的条件。CONTINUE HANDLER是在条件出现时被执行的代码。
3. 触发器
MySQL语句在需要时被执行，存储过程也是如此。但是，有时会想要某条语句(或某些语句)在事件发生时自动执行。
触发器：是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于BEGIN和END语句之间的一组语句)：
DELETE；INSERT；UPDATE。
其它MySQL语句不支持触发器。
创建触发器：
在创建触发器时，需要给出4条信息：
唯一的触发器名；触发器关联的表；触发器应该响应的活动(DELETE、INSERT或UPDATE)；触发器何时执行(处理之前或之后)。
触发器名必须在每个表中唯一，但不是在每个数据库中唯一。这表示同一数据库中的两个表可具有相同名字的触发器。
触发器用CREATE TRIGGER语句创建：
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';
CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本Product added将对每个插入的行显示一次。
只有表才支持触发器，视图不支持(临时表也不支持)。
触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器(每条SELECT、UPDATE和DELETE的之前和之后)。单一触发器不能与多个事件或多个表关联，所以，如果需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。
触发器失败：如果BEFORE触发器失败，则MySQL将不执行请求的操作。如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器。
删除触发器：
DROP TRIGGER语句：
DROP TRIGGER newproduct;
触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，然后再重新创建。
使用触发器：
INSERT触发器：在INSERT语句执行之前或之后执行。
在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
在BEFORE INSERT触发器中，NEW中的值也可以被更新(允许更改被插入的值)；
对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;
此代码创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。FOR EACH ROW对于每一行，即每插入一行到表orders之后就执行SELECT NEW.order_num语句，这里的NEW指的是orders表。
DELETE触发器：
DELETE触发器在DELETE语句执行之前或之后执行。
在DELETE触发器代码内，可以引用一个名为OLD的虚拟表，访问被删除的行；
CREATE TRIGGER neworder AFTER DELETE ON orders
FOR EACH ROW SELECT OLD.order_num;
UPDATE触发器：
CREATE TRIGGER neworder AFTER UPDATE ON order FOR EACH ROW
BEGIN
...
END;