----------PL/SQL 程序设计简介-----------

1.1   SQL与PL/SQL

1.1.1   什么是PL/SQL？

        PL/SQL是 Procedure Language & Structured Query Language 的缩写。PL/SQL是对SQL语言存储过程语言的扩展。从ORACLE6以后，ORACLE的RDBMS附带了PL/SQL。它现在已经成为一种过程处理语言，简称PL/SQL。目前的PL/SQL包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多产品（如C语言，JAVA语言等）工具中的独立引擎。可以将这两部分称为：数据库PL/SQL和工具PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。工具PL/SQL另外还增加了用于支持工具（如ORACLE Forms）的句法，如：在窗体上设置按钮等。

1.2  PL/SQL的优点或特征

1.2.1   有利于客户/服务器环境应用的运行

      对于客户/服务器环境来说，真正的瓶颈是网络上。无论网络多快，只要客户端与服务器进行大量的数据交换。应用运行的效率自然就回受到影响。如果使用PL/SQL进行编程，将这种具有大量数据处理的应用放在服务器端来执行。自然就省去了数据在网上的传输时间。

1.2.2   适合于客户环境

      PL/SQL由于分为数据库PL/SQL部分和工具PL/SQL。对于客户端来说，PL/SQL可以嵌套到相应的工具中，客户端程序可以执行本地包含PL/SQL部分，也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。

1.2.3   过程化

         PL/SQL是Oracle在标准SQL上的过程性扩展，不仅允许在PL/SQL程序内嵌入SQL语句，而且允许使用各种类型的条件分支语句和循环语句，可以多个应用程序之间共享其解决方案。 

1.2.4    模块化

       PL/SQL程序结构是一种描述性很强、界限分明的块结构、嵌套块结构，被分成单独的过程、函数、触发器，且可以把它们组合为程序包，提高程序的模块化能力。

1.2.5   运行错误的可处理性

       使用PL/SQL提供的异常处理（EXCEPTION），开发人员可集中处理各种ORACLE错误和PL/SQL错误，或处理系统错误与自定义错误，以增强应用程序的健壮性。

1.2.6   提供大量的内置程序包

       ORACLE提供了大量的内置程序包。通过这些程序包能够实现DBS的一些低层操作、高级功能，不论对DBA还是应用开发人员都具有重要作用。

当然还有其它的一些优点如：更好的性能、可移植性和兼容性、可维护性、易用性与快速性等。

1.3  PL/SQL 可用的SQL语句

       PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成。在PL/SQL中可以使用的SQL语句有：INSERT，UPDATE，DELETE，SELECT INTO，COMMIT，ROLLBACK，SAVEPOINT。

提示：在 PL/SQL中只能用 SQL语句中的 DML 部分，不能用 DDL 部分，如果要在PL/SQL中使用DDL(如CREATE  table  等)的话，只能以动态的方式来使用。

l ORACLE 的 PL/SQL 组件在对 PL/SQL 程序进行解释时，同时对在其所使用的表名、列名及数据类型进行检查。

l PL/SQL 可以在SQL* PLUS 中使用。

l PL/SQL 可以在高级语言中使用。

l PL/SQL可以在ORACLE的开发工具中使用(如：SQL Developer或Procedure Builder等)。

l 其它开发工具也可以调用PL/SQL编写的过程和函数，如Power Builder 等都可以调用服务器端的PL/SQL过程。

1.4  运行PL/SQL程序

       PL/SQL程序的运行是通过ORACLE中的一个引擎来进行的。这个引擎可能在ORACLE的服务器端，也可能在 ORACLE 应用开发的客户端。引擎执行PL/SQL中的过程性语句，然后将SQL语句发送给数据库服务器来执行。再将结果返回给执行端。

************************************************

---------PL/SQL块结构和组成元素--------

2.1   PL/SQL块

   PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。

   PL/SQL块的结构如下： 

其中：执行部分不能省略。 

PL/SQL块可以分为三类： 

　　1.        无名块或匿名块（anonymous）：动态构造，只能执行一次，可调用其它程序，但不能被其它程序调用。

　　2.        命名块（named）：是带有名称的匿名块，这个名称就是标签。

　　3.        子程序（subprogram）：存储在数据库中的存储过程、函数等。当在数据库上建立好后可以在其它程序中调用它们。

　　4.        触发器（Trigger）：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。

　　5.        程序包（package）：存储在数据库中的一组子程序、变量定义。在包中的子程序可以被其它程序包或子程序调用。但如果声明的是局部子程序，则只能在定义该局部子程序的块中调用该局部子程序。

2.2   PL/SQL结构

l       PL/SQL块中可以包含子块；

l       子块可以位于 PL/SQL中的任何部分；

l       子块也即PL/SQL中的一条命令；

2.3   标识符

PL/SQL程序设计中的标识符定义与SQL 的标识符定义的要求相同。要求和限制有：

l       标识符名不能超过30字符；

l       第一个字符必须为字母；

l       不分大小写；

l       不能用’-‘(减号);

l       不能是SQL保留字。

提示:  一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果. 

例如：下面的例子将会删除所有的纪录，而不是’EricHu’的记录；

    变量命名在PL/SQL中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个系统的文档在规范上达到要求。下面是建议的命名方法：

 2.4   PL/SQL 变量类型

   在前面的介绍中，有系统的数据类型，也可以自定义数据类型。

2.4.1   变量类型

 在ORACLE8i中可以使用的变量类型有：

在使用RETURNING 子句是应注意以下几点限制：

   1．不能与DML语句和远程对象一起使用；

　2．不能检索LONG 类型信息；

　3．当通过视图向基表中插入数据时，只能与单基表视图一起使用。

2.4.2   复合类型

  ORACLE 在 PL/SQL 中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型---记录和表。

2.4.2.1   记录类型

    记录类型类似于C语言中的结构数据类型，它把逻辑相关的、分离的、基本数据类型的变量组成一个整体存储起来，它必须包括至少一个标量型或RECORD 数据类型的成员，称作PL/SQL RECORD 的域(FIELD)，其作用是存放互不相同但逻辑相关的信息。在使用记录数据类型变量时，需要先在声明部分先定义记录的组成、记录的变量，然后在执行部分引用该记录变量本身或其中的成员。

定义记录类型语法如下:

例子：

可以用 SELECT语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相配即可。

2.4.2.2  数组类型

    数据是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标，它取决于成员在数组中的位置。在PL/SQL中，数组数据类型是VARRAY。

定义VARRY数据类型语法如下：

TYPE varray_name IS VARRAY(size) OF element_type [NOT NULL];

        varray_name是VARRAY数据类型的名称，size是下整数，表示可容纳的成员的最大数量，每个成员的数据类型是element_type。默认成员可以取空值，否则需要使用NOT NULL加以限制。对于VARRAY数据类型来说，必须经过三个步骤，分别是：定义、声明、初始化。

例子：

2.4.2.3  使用%TYPE

定义一个变量，其数据类型与已经定义的某个数据变量(尤其是表的某一列)的数据类型相一致，这时可以使用%TYPE。

　　使用%TYPE特性的优点在于：

　　l         所引用的数据库列的数据类型可以不必知道；

　　l         所引用的数据库列的数据类型可以实时改变，容易保持一致，也不用修改PL/SQL程序。

2.4.3  使用%ROWTYPE

PL/SQL 提供%ROWTYPE操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。

　　使用%ROWTYPE特性的优点在于：

　　l         所引用的数据库中列的个数和数据类型可以不必知道；

　　l         所引用的数据库中列的个数和数据类型可以实时改变，容易保持一致，也不用修改PL/SQL程序。

2.4.4  LOB类型

ORACLE提供了LOB (Large OBject)类型，用于存储大的数据对象的类型。ORACLE目前主要支持BFILE, BLOB, CLOB 及 NCLOB 类型。 

BFILE (Movie)

    存放大的二进制数据对象，这些数据文件不放在数据库里，而是放在操作系统的某个目录里，数据库的表里只存放文件的目录。 

BLOB(Photo)

    存储大的二进制数据类型。变量存储大的二进制对象的位置。大二进制对象的大小<=4GB。 

CLOB(Book)

    存储大的字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小<=4GB。 

NCLOB

    存储大的NCHAR字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小<=4GB。

2.4.5  BIND变量

    绑定变量是在主机环境中定义的变量。在PL/SQL 程序中可以使用绑定变量作为他们将要使用的其它变量。为了在PL/SQL 环境中声明绑定变量，使用命令VARIABLE。例如：

可以通过SQL*Plus命令中的PRINT 显示绑定变量的值。例如：

例子：

VARIABLE result NUMBER;

2.4.6  PL/SQL 表（TABLE）

定义记录表（或索引表）数据类型。它与记录类型相似，但它是对记录类型的扩展。它可以处理多行记录，类似于高级中的二维数组，使得可以在PL/SQL中模仿数据库中的表。 

定义记录表类型的语法如下：

关键字INDEX BY表示创建一个主键索引，以便引用记录表变量中的特定行。 

例1：按一维数组使用记录表

例2：按二维数组使用记录表

2.5   运算符和表达式(数据定义)

2.5.1  关系运算符

2.5.2  一般运算符

2.5.3  逻辑运算符

 2.6   变量赋值

在PL/SQL编程中，变量赋值是一个值得注意的地方，它的语法如下：

　　variable 是一个PL/SQL变量, expression 是一个PL/SQL 表达式.

2.6.1  字符及数字运算特点

  空值加数字仍是空值：NULL + < 数字> = NULL 

　空值加（连接）字符，结果为字符：NULL || <字符串> = < 字符串>

2.6.2  BOOLEAN 赋值

布尔值只有TRUE, FALSE及 NULL 三个值。如：

2.6.3  数据库赋值

数据库赋值是通过 SELECT语句来完成的，每次执行 SELECT语句就赋值一次，一般要求被赋值的变量与SELECT中的列名要一一对应。如：

提示：不能将SELECT语句中的列赋值给布尔变量。

2.6.4  可转换的类型赋值

l       CHAR 转换为 NUMBER：

使用 TO_NUMBER 函数来完成字符到数字的转换，如：

l       NUMBER 转换为CHAR：

    使用 TO_CHAR函数可以实现数字到字符的转换，如：

l       字符转换为日期：

使用 TO_DATE函数可以实现  字符到日期的转换，如：

l       日期转换为字符

使用 TO_CHAR函数可以实现日期到字符的转换，如：

2.7   变量作用范围及可见性

变量的作用域是指变量的有效作用范围，与其它高级语言类似，PL/SQL的变量作用范围特点是：

l       变量的作用范围是在你所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束。

l       一个变量（标识）只能在你所引用的块内是可见的。

l       当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为它可能不用了）。

l       在子块中重新定义该变量后，它的作用仅在该块内。

2.8   注释

在PL/SQL里，可以使用两种符号来写注释，即：

l       使用双 ‘-‘ ( 减号) 加注释

PL/SQL允许用 – 来写注释，它的作用范围是只能在一行有效。如：   

l         使用 /*   */  来加一行或多行注释，如：

提示：被解释后存放在数据库中的 PL/SQL 程序，一般系统自动将程序头部的注释去掉。只有在 PROCEDURE 之后的注释才被保留；另外程序中的空行也自动被去掉。

2.9   简单例子

2.9.1  简单数据插入例子：

2.9.2  简单数据删除例子：

************************************************

---------PL/SQL流程控制语句------------

介绍PL/SQL的流程控制语句, 包括如下三类:

             控制语句: IF 语句

l  循环语句: LOOP语句, EXIT语句

l  顺序语句: GOTO语句, NULL语句

3.1  条件语句

        IF <布尔表达式> THEN

          PL/SQL 和 SQL语句

        END IF;

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

        IF <布尔表达式> THEN

          PL/SQL 和 SQL语句

        ELSE

          其它语句

        END IF;

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

        IF <布尔表达式> THEN

          PL/SQL 和 SQL语句

        ELSIF < 其它布尔表达式> THEN

          其它语句

        ELSIF < 其它布尔表达式> THEN

          其它语句

        ELSE

          其它语句

        END IF;

提示: ELSIF 不能写成 ELSEIF

        例1：

        DECLARE

            v_empno  employees.employee_id%TYPE :=&empno;

            V_salary employees.salary%TYPE;

            V_comment VARCHAR2(35);

        BEGIN

           SELECT salary INTO v_salary FROM employees 

           WHERE employee_id = v_empno;

           IF v_salary < 1500 THEN

               V_comment:= ‘太少了,加点吧~!‘;

           ELSIF v_salary <3000 THEN

              V_comment:= ‘多了点,少点吧~!‘;

           ELSE

              V_comment:= ‘没有薪水~!‘;

           END IF;

           DBMS_OUTPUT.PUT_LINE(V_comment);

           exception

             when no_data_found then

                DBMS_OUTPUT.PUT_LINE(‘没有数据~!‘);

             when others then

                DBMS_OUTPUT.PUT_LINE(sqlcode || ‘---‘ || sqlerrm);        

        END;

        例2：

        DECLARE

           v_first_name  VARCHAR2(20);

           v_salary NUMBER(7,2);

        BEGIN

           SELECT first_name, salary INTO v_first_name, v_salary FROM employees

           WHERE employee_id = &emp_id;

           DBMS_OUTPUT.PUT_LINE(v_first_name||‘雇员的工资是‘||v_salary);

           IF v_salary < 10000 THEN

              DBMS_OUTPUT.PUT_LINE(‘工资低于10000‘);

           ELSE

              IF 10000 <= v_salary AND v_salary < 20000 THEN

                 DBMS_OUTPUT.PUT_LINE(‘工资在10000到20000之间‘);

              ELSE

                 DBMS_OUTPUT.PUT_LINE(‘工资高于20000‘);

              END IF;

           END IF;

        END;

3.2  CASE 表达式

        ---------格式一---------

        CASE 条件表达式

          WHEN 条件表达式结果1 THEN 

             语句段1

          WHEN 条件表达式结果2 THEN

             语句段2

          ......

          WHEN 条件表达式结果n THEN

             语句段n

          [ELSE 条件表达式结果]

        END;

        ---------格式二---------

        CASE 

          WHEN 条件表达式1 THEN

             语句段1

          WHEN 条件表达式2 THEN

             语句段2

          ......

          WHEN 条件表达式n THEN 

             语句段n

          [ELSE 语句段]

        END;

    例子：

    DECLARE

       v_first_name employees.first_name%TYPE;

       v_job_id employees.job_id%TYPE;

       v_salary employees.salary%TYPE;

       v_sal_raise NUMBER(3,2);

    BEGIN

       SELECT first_name,   job_id,   salary INTO

              v_first_name, v_job_id, v_salary

       FROM employees WHERE employee_id = &emp_id;

       CASE

          WHEN v_job_id = ‘PU_CLERK‘ THEN

             IF v_salary < 3000 THEN v_sal_raise := .08;

             ELSE v_sal_raise := .07;

             END IF;

          WHEN v_job_id = ‘SH_CLERK‘ THEN

             IF v_salary < 4000 THEN v_sal_raise := .06;

             ELSE v_sal_raise := .05;

             END IF;

          WHEN v_job_id = ‘ST_CLERK‘ THEN

             IF v_salary < 3500 THEN v_sal_raise := .04;

             ELSE v_sal_raise := .03;

             END IF;

          ELSE

             DBMS_OUTPUT.PUT_LINE(‘该岗位不涨工资: ‘||v_job_id);

       END CASE;

       DBMS_OUTPUT.PUT_LINE(v_first_name||‘的岗位是‘||v_job_id

                                        ||‘、的工资是‘||v_salary

                                        ||‘、工资涨幅是‘||v_sal_raise);

    END;

3.3  循环

 1.  简单循环

  LOOP
      要执行的语句;
      EXIT WHEN <条件语句> --条件满足，退出循环语句
  END LOOP;

    例子：

    DECLARE

        int NUMBER(2) :=0;

    BEGIN

       LOOP

          int := int + 1;

          DBMS_OUTPUT.PUT_LINE(‘int 的当前值为:‘||int);

          EXIT WHEN int =10;

       END LOOP;

    END;

2.  WHILE 循环

    WHILE <布尔表达式> LOOP
    要执行的语句;
  END LOOP;

  例子：

  DECLARE 
    x NUMBER :=1;
  BEGIN
     WHILE x<=10 LOOP
        DBMS_OUTPUT.PUT_LINE(‘X的当前值为:‘||x);
         x:= x+1;
     END LOOP;
  END;

3.  数字式循环

[<<循环标签>>]
FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP
  要执行的语句;
END LOOP [循环标签];

       每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1。跟在IN REVERSE 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。可以使用EXIT 退出循环。

例子1：

BEGIN
   FOR int  in 1..10 LOOP
       DBMS_OUTPUT.PUT_LINE(‘int 的当前值为: ‘||int);
   END LOOP;
END;

例子2:

DECLARE

   TYPE jobids_varray IS VARRAY(12) OF VARCHAR2(10); --定义一个VARRAY数据类型

   v_jobids JOBIDS_VARRAY; --声明一个具有JOBIDS_VARRAY数据类型的变量

   v_howmany NUMBER; --声明一个变量来保存雇员的数量

BEGIN

   --用某些job_id值初始化数组

   v_jobids := jobids_varray(‘FI_ACCOUNT‘, ‘FI_MGR‘, ‘ST_CLERK‘, ‘ST_MAN‘);

   --用FOR...LOOP...END LOOP循环使用每个数组成员的值

   FOR i IN v_jobids.FIRST..v_jobids.LAST LOOP

   --针对数组中的每个岗位，决定该岗位的雇员的数量

      SELECT count(*) INTO v_howmany FROM employees WHERE job_id = v_jobids(i);

      DBMS_OUTPUT.PUT_LINE ( ‘岗位‘||v_jobids(i)||

                       ‘总共有‘|| TO_CHAR(v_howmany) || ‘个雇员‘);

   END LOOP;

END;

例：在While循环中嵌套loop循环

/*求100至110之间的素数*/

DECLARE

   v_m NUMBER := 101;

   v_i NUMBER;

   v_n NUMBER := 0;

BEGIN

   WHILE v_m < 110 LOOP

      v_i := 2;

      LOOP

         IF mod(v_m, v_i) = 0 THEN

            v_i := 0;

            EXIT;

         END IF;

         v_i := v_i + 1;

         EXIT WHEN v_i > v_m - 1; 

      END LOOP;

      IF v_i > 0 THEN

         v_n := v_n + 1;

         DBMS_OUTPUT.PUT_LINE(‘第‘|| v_n || ‘个素数是‘ || v_m);

      END IF;

      v_m := v_m + 2;

   END LOOP;

END;

3.4  标号和GOTO 

PL/SQL中GOTO语句是无条件跳转到指定的标号去的意思。语法如下：

GOTO label;
......
<<label>>　/*标号是用<<　>>括起来的标识符 */

注意，在以下地方使用是不合法的，编译时会出错误。

u 跳转到非执行语句前面。

u 跳转到子块中。

u 跳转到循环语句中。

u 跳转到条件语句中。

u 从异常处理部分跳转到执行。

u 从条件语句的一部分跳转到另一部分。

例子：

DECLARE
   v_i NUMBER := 0;
   v_s NUMBER := 0;
BEGIN
   <<label_1>>
   v_i := v_i + 1;
   IF v_i <= 1000 THEN
      v_s := v_s + v_i;
      GOTO label_1;
   END IF;
   DBMS_OUTPUT.PUT_LINE(v_s);
END;

3.5  NULL 语句 

      在PL/SQL 程序中，NULL语句是一个可执行语句，可以用 null 语句来说明“不用做任何事情”的意思，相当于一个占位符或不执行任何操作的空语句，可以使某些语句变得有意义，提高程序的可读性，保证其他语句结构的完整性和正确性。如：

例子：

DECLARE
   v_emp_id employees.employee_id%TYPE;
   v_first_name employees.first_name%TYPE;
   v_salary employees.salary%TYPE;
   v_sal_raise NUMBER(3,2);
BEGIN
   v_emp_id := &emp_id;
   SELECT first_name, salary INTO v_first_name, v_salary
   FROM employees WHERE employee_id = v_emp_id;
   IF v_salary <= 3000 THEN
      v_sal_raise := .10;
      DBMS_OUTPUT.PUT_LINE(v_first_name||‘的工资是‘||v_salary
                                       ||‘、工资涨幅是‘||v_sal_raise);
   ELSE
      NULL;
   END IF;
END;

************************************************

----------把游标说透-----------

在 PL/SQL 程序中，对于处理多行记录的事务经常使用游标来实现。

4.1 游标概念

在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，ORACLE会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针，或是命名一个工作区（Work Area），或是一种结构化数据类型。它为应用等量齐观提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。

 在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。

对于不同的SQL语句，游标的使用情况不同：

4.1.1  处理显示游标

1 . 显式游标处理

显式游标处理需四个 PL/SQL步骤:

l 定义/声明游标：就是定义一个游标名，以及与其相对应的SELECT 语句。

格式：

    CURSOR cursor_name[(parameter[, parameter]…)] 
           [RETURN datatype]
    IS 
        select_statement;

游标参数只能为输入参数，其格式为： 

parameter_name [IN] datatype [{:= | DEFAULT} expression]

在指定数据类型时，不能使用长度约束。如NUMBER(4),CHAR(10) 等都是错误的。

[RETURN datatype]是可选的，表示游标返回数据的数据。如果选择，则应该严格与select_statement中的选择列表在次序和数据类型上匹配。一般是记录数据类型或带“%ROWTYPE”的数据

l 打开游标：就是执行游标所对应的SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有FOR UPDATE选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。

格式：

OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)]; 

在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示法。PL/SQL 程序不能用OPEN 语句重复打开一个游标。

l 提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中。 

格式： 

FETCH cursor_name INTO {variable_list | record_variable };

执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。当检索到最后一行数据时，如果再次执行FETCH语句，将操作失败，并将游标属性%NOTFOUND置为TRUE。所以每次执行完FETCH语句后，检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行，以便确定是否给对应的变量赋了值。

l 对该记录进行处理；

l 继续处理，直到活动集合中没有记录；

l 关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH 语句取其中数据。关闭后的游标可以使用OPEN 语句重新打开。

格式：

      CLOSE cursor_name; 

注：定义的游标不能有INTO 子句。

例子. 查询前10名员工的信息。

DECLARE
   CURSOR c_cursor 
   IS SELECT first_name || last_name, Salary 
   FROM EMPLOYEES 
   WHERE rownum<11;   
   v_ename  EMPLOYEES.first_name%TYPE;
   v_sal    EMPLOYEES.Salary%TYPE;   
BEGIN
  OPEN c_cursor;
  FETCH c_cursor INTO v_ename, v_sal;
  WHILE c_cursor%FOUND LOOP
     DBMS_OUTPUT.PUT_LINE(v_ename||‘---‘||to_char(v_sal) );
     FETCH c_cursor INTO v_ename, v_sal;
  END LOOP;
  CLOSE c_cursor;
END;

2.游标属性

 Cursor_name%FOUND     布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；

 Cursor_name%NOTFOUND   布尔型属性，与%FOUND相反；

 Cursor_name%ISOPEN     布尔型属性，当游标已打开时返回 TRUE；

 Cursor_name%ROWCOUNT   数字型属性，返回已从游标中读取的记录数。

例1：没有参数且没有返回值的游标。

DECLARE
   v_f_name employees.first_name%TYPE;
   v_j_id   employees.job_id%TYPE;
   CURSOR c1       --声明游标,没有参数没有返回值
   IS
      SELECT first_name, job_id FROM employees 
      WHERE department_id = 20;
BEGIN
   OPEN c1;        --打开游标
   LOOP
      FETCH c1 INTO v_f_name, v_j_id;    --提取游标
      IF c1%FOUND THEN
         DBMS_OUTPUT.PUT_LINE(v_f_name||‘的岗位是‘||v_j_id);
      ELSE
         DBMS_OUTPUT.PUT_LINE(‘已经处理完结果集了‘);
         EXIT;
      END IF;
   END LOOP;
   CLOSE c1;   --关闭游标
END;

例2：有参数且没有返回值的游标。

DECLARE
   v_f_name employees.first_name%TYPE;
   v_h_date employees.hire_date%TYPE;
   CURSOR c2(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数没有返回值
   IS
      SELECT first_name, hire_date FROM employees
      WHERE department_id = dept_id AND job_id = j_id;
BEGIN
   OPEN c2(90, ‘AD_VP‘);  --打开游标,传递参数值
   LOOP
      FETCH c2 INTO v_f_name, v_h_date;    --提取游标
      IF c2%FOUND THEN
         DBMS_OUTPUT.PUT_LINE(v_f_name||‘的雇佣日期是‘||v_h_date);
      ELSE
         DBMS_OUTPUT.PUT_LINE(‘已经处理完结果集了‘);
         EXIT;
      END IF;
   END LOOP;
   CLOSE c2;   --关闭游标
END;

例3：有参数且有返回值的游标。

DECLARE
   TYPE emp_record_type IS RECORD(
        f_name   employees.first_name%TYPE,
        h_date   employees.hire_date%TYPE);
   v_emp_record EMP_RECORD_TYPE;

   CURSOR c3(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数有返回值
          RETURN EMP_RECORD_TYPE
   IS
      SELECT first_name, hire_date FROM employees
      WHERE department_id = dept_id AND job_id = j_id;
BEGIN
   OPEN c3(j_id => ‘AD_VP‘, dept_id => 90);  --打开游标,传递参数值
   LOOP
      FETCH c3 INTO v_emp_record;    --提取游标
      IF c3%FOUND THEN
         DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||‘的雇佣日期是‘
                            ||v_emp_record.h_date);
      ELSE
         DBMS_OUTPUT.PUT_LINE(‘已经处理完结果集了‘);
         EXIT;
      END IF;
   END LOOP;
   CLOSE c3;   --关闭游标
END;

3. 游标的FOR循环

    PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。

格式：

  FOR index_variable IN cursor_name[(value[, value]…)] LOOP
    -- 游标数据处理代码
  END LOOP;

其中：

index_variable为游标FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标FOR 循环语句中的索引变量来访问这些列数据。

注：不要在程序中对游标进行人工操作；不要在程序中定义用于控制FOR循环的记录。

例子：当所声明的游标带有参数时，通过游标FOR 循环语句为游标传递参数。

DECLARE
  CURSOR c_cursor(dept_no NUMBER DEFAULT 10) 
  IS
    SELECT department_name, location_id FROM departments WHERE department_id <= dept_no;
BEGIN
    DBMS_OUTPUT.PUT_LINE(‘当dept_no参数值为30：‘);
    FOR c1_rec IN c_cursor(30) LOOP        DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||‘---‘||c1_rec.location_id);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(CHR(10)||‘使用默认的dept_no参数值10：‘);
    FOR c1_rec IN c_cursor LOOP        DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||‘---‘||c1_rec.location_id);
    END LOOP;
END;

4.1.2 处理隐式游标

显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL 语句所包含的数据。

格式调用为： SQL%

注：INSERT, UPDATE, DELETE, SELECT 语句中不必明确定义游标。

隐式游标属性

4.1.3  关于 NO_DATA_FOUND 和 %NOTFOUND 的区别

SELECT … INTO 语句触发 NO_DATA_FOUND；

当一个显式游标的WHERE子句未找到时触发%NOTFOUND；

当UPDATE或DELETE 语句的WHERE 子句未找到时触发 SQL%NOTFOUND；在提取循环中要用 %NOTFOUND 或%FOUND 来确定循环的退出条件，不要用 NO_DATA_FOUND

4.1.4  使用游标更新和删除数据

       游标修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。这时，要求游标查询语句中必须使用FOR UPDATE选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列。

为了对正在处理(查询)的行不被另外的用户改动，ORACLE 提供一个 FOR UPDATE 子句来对所选择的行进行锁住。该需求迫使ORACLE锁定游标结果集合的行，可以防止其他事务处理更新或删除相同的行，直到您的事务处理提交或回退为止。

语法：

SELECT column_list FROM table_list FOR UPDATE [OF column[, column]…] [NOWAIT]

如果另一个会话已对活动集中的行加了锁，那么SELECT FOR UPDATE操作一直等待到其它的会话释放这些锁后才继续自己的操作，对于这种情况，当加上NOWAIT子句时，如果这些行真的被另一个会话锁定，则OPEN立即返回并给出：

ORA-0054 ：resource busy  and  acquire with nowait specified.

如果使用 FOR UPDATE 声明游标，则可在DELETE和UPDATE 语句中使用

WHERE CURRENT OF cursor_name子句，修改或删除游标结果集合当前行对应的数据库表中的数据行。

4.2 游标变量

       与游标一样，游标变量也是一个指向多行查询结果集合中当前数据行的指针。但与游标不同的是，游标变量是动态的，而游标是静态的。游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域（但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连），只要这些查询语句的返回类型兼容即可。

4.2.1  声明游标变量

游标变量为一个指针，它属于参照类型，所以在声明游标变量类型之前必须先定义游标变量类型。在PL/SQL中，可以在块、子程序和包的声明区域内定义游标变量类型。

语法格式为：

TYPE ref_type_name IS REF CURSOR
 [ RETURN return_type];

其中：ref_type_name为新定义的游标变量类型名称；

  return_type 为游标变量的返回值类型，它必须为记录变量。

在定义游标变量类型时，可以采用强类型定义和弱类型定义两种。强类型定义必须指定游标变量的返回值类型，而弱类型定义则不说明返回值类型。

声明一个游标变量的两个步骤：

步骤一：定义一个REF CURSOU数据类型，如：

    TYPE ref_cursor_type IS REF CURSOR;

步骤二：声明一个该数据类型的游标变量，如：

    cv_ref REF_CURSOR_TYPE;

谢谢各位博友的支持！

未经博主同意不得随意转载。

如需转载请注明出处：http://www.cnblogs.com/ZRJ-boke/p/6602465.html

ORACLE PL/SQL编程总结（一）