PL/SQL那些事

PL/SQL是oracle对标准数据库语言的扩展，oracle公司已经将PL/SQL整合到oracle服务器和其他工具中了，近几年中更多的开发人员和DBA开始使用PL/SQL，本文将讲述

PL/SQL基础语法，结构和组件、以及如何设计并执行一个PL/SQL程序。

            从版本6开始PL/SQL就被可靠的整合到oracle中了，一旦掌握了PL/SQL的优点以及其独有的数据管理的便利性，那么你很难想象oracle缺了PL/SQL的情形。PL/SQL不是一个独立的产品，他是一个整合到oracle服务器和Oracle工具中的技术，可以把PL/SQL看做oracle服务器内的一个引擎，sql语句执行者处理单个的Sql语句，PL/SQL引擎处理PL/SQL程序块。当PL/SQL程序块在PL/SQL引擎处理时，oralce服务器中的sql语句执行处理PL/SQL程序块中的SQL语句。

 

PL/SQL的优点：

           1. PL/SQL是一种高性能的基于事务处理的语言，能运行在任何oracle环境中，支持所有数据处理命令。通过使用PL/SQL程序单元处理SQL的数据定义和数据控制元素。

           2. PL/SQL支持所有SQL数据类型和所有SQL函数，同时支持所有oracle对象类型。

           3. PL/SQL块可以被命名和存储在oracle服务器中，同时也能被其他的PL/SQL程序或SQL命令调用，任何客户/服务器工具都能访问PL/SQL程序，具有很好的可重用性。

           4. 可以使用oracle数据工具管理存储在服务器中的PL/SQL程序的安全性。可以授权或撤销数据库其他用户访问PL/SQL程序的能力。

           5. PL/SQL代码可以使用任何ASCII文本编辑器编写，所以对任何oracle能够运行的操作系统都是非常便利的。

           6. 对于sql，oracle必须在同一时间处理每一条sql语句，在网络环境下这就意味着作为每一个独立的调用都必须被oracle服务器处理，这就占用了大量的服务器时间，同时导致网络拥挤。而PL/SQL是以整个语句块发送给服务器，这就降低了网络拥挤。

 

PL/SQL块结构：

            PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑快，一个PL/SQL程序包含了一个或者多个逻辑快，每个块都可以划分为三个部分。与其他语言相同，变量在使用之前必须声明，PL/SQL提供了独立的专门用于处理异常的部分，下面描述了PL/SQL块的不同部分：

      1. 声明部分(Declaration section)

          声明部分包含了变量和常量的数据类型和初始值。这个部分是由关键字DECLARE开始，如果不需要声明变量或常量，那么可以忽略这一部分；需要说明的是游标的声明也在这一部分。

      2. 执行部分(Executable section)

          执行部分是PL/SQL块中的指令部分，由关键字BEGIN开始，所有的可执行语句都放在这一部分，其他的PL/SQL块也可以放在这一部分。

      3. 异常处理部分(Exception section)

          这一部分是可选的，在这一部分中处理异常或者错误，对异常处理的详细讨论我们在后面进行。

 

PL/SQL块语法：

          [DECLARE]

           ---declaration statements

          BEGIN

          ---executable statements

          [EXCEPTION]

          ---exception statements

          END

         PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以是多行的，但分号表示该语句的结束。一行中可以有多条SQL语句，它们之间以分号分隔。每一个PL/SQL块由BEGIN或DECLARE开始，以END结束。注释由--标示。

 

PL/SQL块的命名和匿名

          PL/SQL程序块可以是一个命名的程序块也可以是一个匿名程序块。匿名程序块可以用在服务器端也可以用在客户端。

          命名程序块可以出现在其他PL/SQL程序块的声明部分，这方面比较明显的是子程序，子程序可以在执行部分引用，也可以在异常处理部分引用。

          PL/SQL程序块可被独立编译并存储在数据库中，任何与数据库相连接的应用程序都可以访问这些存储的PL/SQL程序块。oracle提供了四种类型的可存储的程序：

          1. 函数

          2. 过程

          3. 包

          4. 触发器

 

         函数：

              函数是命名了的、存储在数据库中的PL/SQL程序块。函数接受零个或多个输入参数，有一个返回值，返回值的数据类型在创建函数时定义。定义函数的语法如下：

              FUNCTION  name [{parameter[,parameter,...]}] RETURN  datatypes  IS

                    [local  declarations]

              BEGIN

                    execute  statement

              [EXCEPTION

                    exception  handlers]

              END [name]

         过程：

              存储过程是一个PL/SQL程序块，接受零个或多个参数作为输入(INPUT)或输出(OUTPUT)、或既作为输入由作为输出(INOUT)，与函数不同，存储过程没有返回值，存储过程不能由SQL语句直接使用，只能通过EXECUT命令或PL/SQL程序块内部调用，定义存储过程的语法如下：

             PROCEDURE  name [(parameter[,parameter,...])]  IS

                  [local  declarations]

             BEGIN

                  execute  statements

             [EXCEPTION

                  exception  handlers]

             END [name]

        包(package)：

              包其实就是被组合在一起的相关对象的集合，当包中任何函数或存储过程被调用，包就被加载入内存中，包中的任何函数或存储过程的子程序访问速度将大大加快。

           包由两部分组成：

              规范和包主体(body)，规范描述变量、常量、游标、和子程序，包体完全定义子程序和游标。

         触发器(trigger)：

           触发器与一个表或数据库事件联系在一起的，当一个触发器事件发生时，定义在表上的触发器被触发。

 

变量和常量：

      变量存放在内存中以获得值，能被PL/SQL块引用。你可以把变量想象成一个可储藏东西的容器，容器内的东西是可以改变的。

      声明变量：

            变量一般都在PL/SQL块的声明部分声明，PL/SQL是一种强壮的类型语言，这就是说在引用变量前必须首先声明，要在执行或异常处理部分使用变量，那么变量必须首先在声明部分进行声明。

            声明变量的语法：

                     variable_name [CONSTANT] datatype  [NOT NULL] [ := |DEFAULT  expression]

            注意：可以在声明变量的同时给变量强制性的加上 NOT NULL 约束条件，此时变量在初始化是必须赋值。

     给变量赋值：

             给变量赋值有两种方式：

                   1. 直接给变量赋值：

                         X := 200;

                         Y = Y + (X * 20);

                   2. 通过SQL语句 SELECT INTO 或 FETCH INTO 给变量赋值

                         select  sum(salary), sum(salary * 0.1) into total_salary, tatal_commission

                         from  employee

                         where  dept = 10;

      常量：

           常量与变量相似，但常量的值在程序内部不能改变，常量的值在定义时赋值，它的声明方式与变量相似，但必须包括关键字CONSTANT。常量和变量都可被定义为SQL和用户定义的数据类型。

           zero_value  CONSTANT  number := 0;

          上面语句定义了一个名为zero_value、数据类型是number、值为0的常量。

      标量(scalar)数据类型：

           标量(scalar)数据类型没有内部组件，它们大致可分为以下四类：

           1. number     2. character      3. date/time        4. boolean

           以下表1显示了数字数据类型，表2显示了字符数据类型，表3显示了日期和布尔数据类型

 

表1 scalar types:numric

表2字符数据类型

表3 date和boolean

 

       LOB数据类型：

              LOB(大对象，Large object) 数据类型用于存储类似图像，声音这样的大型数据对象，LOB数据对象可以是二进制数据也可以是字符数据，其最大长度不超过4G。LOB数据类型支持任意访问方式，LONG只支持顺序访问方式。LOB存储在一个单独的位置上，同时一个"LOB定位符"(LOB locator)存储在原始的表中，该定位符是一个指向实际数据的指针。在PL/SQL中操作LOB数据对象使用oracle提供的包DBMS_LOB。LOB数据类型可分为以下四类：

          1. BFILE            2. BLOG         3. CLOB           4. NCLOB

       操作符

           与其他程序设计语言相同，PL/SQL有一系列操作符。操作符分为下面几类：

1. 算数操作符        

2. 关系操作符(关系操作符主要用于条件判断语句或用于where子串中，关系操作符检查条件和结果是否为true或false)

3. 比较操作符    

4. 逻辑操作符

 

执行部分

       执行部分包含了所有的语句和表达式，执行部分以关键字BEGIN开始，以关键字EXCEPTION结束，如果EXCEPTION不存在，那么将以关键字END结束。分号分隔每一条语句，使用赋值操作符 := 或 select  into  或 fetch  into  给每个变量赋值，执行部分的错误将在异常处理部分解决，在执行部分中可以使用另一个PL/SQL程序块，这种程序块被称为嵌套块。

       所有的SQL数据操作语句都可以用于执行部分，PL/SQL块不能在屏幕上显示select语句的输出。select 语句必须包括一个Into子串或者是游标的一部分，执行部分使用的变量和常量必须首先在声明部分声明，执行部分必须至少包括一条可执行语句，NULL是一条合法的可执行语句，事物控制语句commit和rollback可以在执行部分使用，数据定义语言(data definition language)不能在执行部分中使用，DDL语句与execute  immediate 一起使用或者是DBMS_SQL调用。

 

执行一个PL/SQL块

          SQL*PLUS中匿名的PL/SQL块的执行是在PL/SQL块后输入“/”来执行，如下面的例子所示：

          命名的程序与匿名程序的执行不同，执行命名的程序块必须使用execute关键字：

       如果在另一个命名程序块或匿名程序块中执行这个程序，那么就不需要execute关键字。

 

控制结构：

       控制结构控制PL/SQL程序流程的代码行，PL/SQL支持条件控制和循环控制结构。

       语法和用途

         1. IF...THEN

               语法格式：

               IF  condition  THEN

                  statements 1;

                  statements 2;

                   ...

               END  IF

            IF语句判断条件condition是否为TRUE，如果是，则执行THEN后面的语句，如果condition为false或NULL则跳过THEN到END IF之间的语句，执行END IF后面的语句。

 

         2. IF...THEN...ELSE

               语法：

               IF  condition  THEN

                     statements 1;

                     statements 2;

                     ...

               ELSE

                     statements 1;

                     statements 2;

                     ...

               END  IF

           如果条件condition为TRUE，则执行THEN到ELSE之间的语句，否则执行ELSE到END IF之间的语句。

           IF 可以嵌套，可以在IF或IF...ELSE语句中使用IF或IF...ELSE语句。

 

        3.  IF...THEN...ELSE

          语法：

              IF  condition1  THEN

                   statement1;

              ELSE IF  condition2  THEN

                   statement2;

              ELSE IF  condition3  THEN

                   statement3;

              ELSE

                   statement4;

              END IF;

                   statement5;

             如果条件condition1为TRUE则执行statement1，然后执行statement5，否则判断condition2是否为TRUE，若为TRUE则执行statement2，然后执行statement5，对于condtion3也是相同的，如果condition1，condition2，condition3都不成立，那么将执行statement4，然后执行statement5。

 

循环控制：

        循环控制的基本形式是LOOP语句，LOOP和END LOOP之间的语句将无限次的执行。

        1. LOOP语句的语法如下：

            LOOP

                statements;

            END LOOP

         LOOP和END LOOP之间的语句无限次的执行显然是不行的，那么在使用LOOP语句时必须使用EXIT语句，强制循环结束，例如：

         此时Y的值是1010.

         2. EXIT  WHEN 语句将结束循环，如果条件为TRUE，则结束循环。

     

       3. WHILE...LOOP

            WHILE...LOOP有一个条件与循环相联系，如果条件为TRUE，则执行循环体内的语句，如果结果为FALSE，则结束循环。

 

       4.  FOR...LOOP

             语法：

              FOR  counter  IN [REVERSE]  start_range....end_range  LOOP

                      statements

              END LOOP;

             LOOP和WHILE循环的循环次数都是不确定的，FOR循环的循环次数是固定的，counter是一个隐式声明的变量，他的初始值是stat_range，第二个值是start_range + 1，直到end_range，如果start_range等于end_range，那么循环将执行一次。如果使用了REVERSE关键字，那么范围将是一个降序。

          如果要退出 for 循环可以使用 EXIT语句。

 

标签：

        用户可以使用标签使程序获得更好的可读性。程序块或循环都可以被标记。标签的形式是 <>

        1. 标记程序块

          [DECLARE]

              ....

          BEGIN

              ....

          [EXCEPTION]

              ....

          END label_name

 

        2. 标记循环

          <>

          LOOP

              .....

          <>

           LOOP

                 .....

           <>

           LOOP

               .....

           EXIT  outer_loop  WHEN  v_condition = 0;

           end  loop  innermost_loop;

            ..........

           END LOOP  inner_loop;

           END LOOP  outer_loop;

 

          5. GOTO语句

             语法：

              GOTO  LABEL;

              执行GOTO语句时，控制会立即转到由标签标记的语句。PL/SQL中对GOTO语句有一些限制，对于块、循环、IF语句而言，从外层跳转到内层是非法的。

         注意：NULL是一个合法的可执行语句。

 

嵌套：

       程序块的内部可以有另一个程序块这种情况称为嵌套。嵌套要注意的是变量，定义在最外部程序块中的变量可以在所有子块中使用，如果在子块中定义了与外部程序块变量相同的变量名，在执行子块时将使用子块中定义的变量。子块中定义的变量不能被父块引用。同样GOTO语句不能由父块跳转到子块中，反之则是合法的。

       《OUTER  BLOCK》

         DECLARE

             A_NUMBER  INTEGER;

             B_NUMBER  INTEGER;

         BEGIN

             --A_NUMBER  and  B_NUMBER  are  available  here

             <>

         DECLARE

              C_NUMBER  INTEGER

              B_NUMBER  NUMBER(20)

         BEGIN

              C_NUMBER := A_NUMBER;

              C_NUMBER := OUTER_BLOCK.B_NUMBER;

         END  SUB_BLOCK;

         END  OUT_BLOCK:

 

复合数据类型：

        PL/SQL有两种复合数据结构：记录和集合。记录由不同的域组成，集合由不同的元素组成。本文中我们将讨论记录和集合的类型、怎样定义和使用记录和集合。

        PL/SQL记录

            记录是PL/SQL的一种复合数据结构，scalar数据类型和其他数据类型只是简单的在包一级进行预定义，但复合数据类型在使用前必须被定义，记录之所以被称为复合数据类型是因为它由域这种由数据元素的逻辑组所组成。域可以是scalar数据类型或其他记录类型，它与C语言中的结构相似，记录也可以看成表中的数据行，域则相当于表中的列，在表和虚拟表(视图或查询)中非常容易定义和使用，行或记录中的每一列或域都可以被引用或单独赋值，也可以通过一个单独的语句引用记录所有的域。在存储过程或函数中记录也可能有参数。

        1.  创建记录：

              在PL/SQL中有两种定义方式 ：显式定义和隐式定义。一旦记录被定义后，声明或创建定义类型的记录变量，然后才是使用该变量。隐式声明是在基于表的结构或查询上使用%TYPE属性，隐式声明是一个更强有力的工具，这是因为这种数据变量是动态创建的。

        2. 显式定义记录：

              显式定义记录是在PL/SQL程序块中创建记录变量之前在声明部分定义。使用type命令定义记录，然后再创建该记录的变量。语法如下：

                  TYPE  record_type  IS  RECORD(field_definition_list);

                  field_definition_list是由逗号分隔的列表。

              域定义的语法如下：

                  field_name  data_type_and_size  [NOT NULL][{:=|DEFAULT} default_value]

                  域名必须服从与表或列的命名规则相同的命名规则。下面我们看一个例子：

              域定义时的%TYPE属性用于引用数据库中的表或视图的数据类型和大小，而在此之前程序不知道类型和大小。在上面的例子中记录域在编译时将被定义为与列SYMBOL相同的数据类型和大小，当代码中要使用来自数据库中的数据时，在变量或域定义中最好使用%TYPE来定义。

       3. 隐式定义记录：

            隐式定义记录中，我们不用描述记录的每一个域。这是因为我们不需要定义记录的结构，不需要使用TYPE语句，相反在声明记录变量时使用%ROWTYPE命令定义与数据库表，视图，游标有相同结构的记录，与TYPE命令相同的是它是一种定义获得数据库数据记录的好方法。

           有一些PL/SQL指令在使用隐式定义记录时没有使用%ROWTYPE属性，比如游标FOR循环或触发器中的 :old 和 :new 记录。

       4. 使用记录

            用户可以给记录赋值、将值传递给其他程序。记录作为一种复合数据结构意味着它有两个层次可用。用户可以引用整个记录，使用select into 或 fetch 转移所有域，也可以将整个记录传递给一个程序或将所有域的值赋给另一个记录。在更低的层次，用户可以处理记录内单独的域，用户可以给单独的域赋值或者在单独的域上运行布尔表达式，也可以将一个或更多的域传递给另一个程序。

       5. 引用记录

           记录由域组成，访问记录中的域使用点(.)符号。例子如下：

       6. 给记录赋值

           给记录或记录中的域赋值的方法有几种，可以使用select into 或 fetch给整个记录或单独的域赋值，可以将整个记录的值赋给其他记录，也可以通过给每个域赋值来得到记录，一下我们通过实例讲解每一种赋值方法。

           1) 使用 select  into

               使用select  into给记录赋值要将记录或域放在 into 子串中，into 子串中的变量与 select 中列的位置相对应。例子如下：

           2) 使用 fetch

                如果SQL语句返回多行数据或者希望使用带参数的游标，那么就要使用游标，这种情况下使用fetch语句代替instead into是一个更简单、更有效的方法，但是在安全性较高的包中fetch的语法如下：

            fetch  cursor_name  into  variable;

           我们改写上面的例子：

          使用赋值语句将整个记录复制给另一个记录是一项非常有用的技术，不过记录必须精确地被声明为相同的类型，不能是基于两个不同的type语句来获得相同的结构。例：

          --这两个记录看上去是一样的，但实际上是不一样的

             stock_one   stocks_quote_rec;

             stock_two   stocks_quote_rec;

           --这两个域有相同的数据类型和大小

           stock_also  stock_rec_too;  --与stock_quote_rec是不同的数据类型

          记录不能用于insert语句和将记录直接用于比较，下面两种情况是错误的：

               insert  into  stocks  values(stock_record);  和  if  stock_rec1  >  stock_rec2  then

          可以使用数据库对象，数据库对象可以使用order或map方法定义，允许oracle对复合数据类型进行比较。关于数据库对象的讨论已经超越了本文的范围，要详细了解数据库对象，可以查阅oracle手册。

 

PL/SQL集合

         集合与其他语言中的数组相似，在oracle7.3及以前的版本中只有一种集合称为PL/SQL表，这种类型的集合依然保留，就是索引(INDEX_BY)表，与记录相似，集合在定义的时候必须使用type语句，然后才是创建和使用这种类型的变量。

        PL/SQL有三种类型的 集合

                  1. index_by表          2. 嵌套表          3. varray

        这三种类型的集合之间有许多差异，包括数据绑定、稀疏性(sparsity)、数据库中的存储能力都不相同。绑定涉及到集合中元素数量的限制，varray集合中的元素的数量是有限，index_by和嵌套表则是没有限制的。稀疏性描述了集合的下标是否有间隔，index_by表总是稀疏的，如果元素被删除了嵌套表可以是稀疏的，但varray类型的集合则是紧密的，它的下标之间没有间隔。

         index_by表不能存储在数据库中，但嵌套表和varray可以被存储在数据库中。

         虽然这三种类型的集合由很多不同之处，但他们也由很多相似的地方：

             1) 都是一维的类似数组的结构

             2) 都有内建的方法

             3) 访问由点分隔

         1. index_by表集合的定义语法如下：

             type  type_name  is  table  of  element_type [not null]  index  by  binary_interget

             这里面重要的关键字是 index by binary_integer，没有这个关键字，那么集合将是一个嵌套表，element_type可以是任何合法的PL/SQL数据类型，包括：PLS/integer、signtype、和boolean。其他的集合类型对数据库的数据类型都有限制，但index_by表不能存储在数据库中，所以没有这些限制。

             一旦定义了index_by表，就可以像创建其他变量那样创建index_by表的变量：

          2. 嵌套表

                  嵌套表非常类似于 index_by表，创建的语法也非常相似。使用 type 语句，只是没有 index by binary_integer 子串。

                        type  type_name  is  table  of  element_type  [ not null ]

                  not  null 选项要求集合所有的元素都要有值，element_type可以是一个记录，但是这个记录只能使用标量数据类型字段以及只用于数据库的数据类型(不能是PLS_INTEGER，BOOLEAN或SIGNTYPE)。

                 嵌套表和varray都能作为列存储在数据库表中，所以集合自身而不是单个的元素可以为null，oracle称这种集合为null的为"自动设置为null(atomically  null)"以区别元素为null的情况。当集合为null时，即使不会产生异常，用户也不能引用集合中的元素。用户可以使用 is null 操作符检测集合是否为null。

                 存储在一个数据库中的嵌套表并不与表中的其他数据存放在同一个数据块中，它们实际上被存放在第二个表中。正如没有order by 子句 select 语句不能保证返回任何有顺序的数据，从数据库中取回的嵌套表也不保证元素的顺序。由于集合数据是离线存储的，对于大型集合嵌套表是一个不错的选择。

         3. varray

             varray或数据变量都有元素的限制。像其他集合一样 varray 定义仍然使用 type 语句，但关键字 varray 或 varrying array 告诉 oracle 这是一个varray集合。

                   type  type_name  is  [varray|varying array] (max_size)  of  element_type  [ not  null ]

             max_size是一个整数，用于标示varray集合拥有的最多元素数目。varray集合的元素数量可以低于max_size，但不能超过max_size。element_type是一维元素的数据类型，如果element_type是记录，那么这个记录只能使用标量数据字段(与嵌套标相似)。not null 子串表示集合中的每一个元素都必须有值。

             与嵌套表相似，varray能够自动为null，可以使用is null操作符进行检测，与嵌套表不同的是，当varray存储在数据库中时与表中的其他数据存放在同一个数据块中。正像列的排序保存在表的select *中一样元素的顺序保存在varray中。同样由于集合是在线存储的，varray很适合于小型集合。

 

使用集合：

       像记录一样，集合可以在两个层面上使用：

            1) 操作整个集合       2) 访问集合中的单个元素

        第一种情况使用集合名，第二种情况使用下标