《Oracle Database 11g SQL 开发指南》学习笔记——第11章_PL/SQL编程简介

11.1、块结构
    [DECLARE
       declaration_statements
    ]
    BEGIN
       executable_statements
    [EXCEPTION
       exception_handing _statements
    ]
    END;
    /
 每一条语句都由'；'结尾，PL/SQL块由‘/’结尾。  SET SERVEROUTPUT ON命令打开服务器输出。
 还可以用%TYPE关键字来定义变量类型，它告诉PL/SQL使用与表中列相同的类型。
11.3、在PL/SQL中，IF、THEN、 ELSE、ELSEIF、ENDIF等关键字来执行条件逻辑，还可以嵌套使用。
11.4、循环。
   简单循环、while循环、for循环。
  11.4.1、简单循环：直到显式结束循环之前一直运行。
    LOOP
      ststement
    END LOOP;   要结束可以使用exit或exit  when语句。
    如：v_count :=0;    loop  v_count := v_count+1;   
               exit when v_count= 5;    end   loop;
   在Oracle 11g中也可以使用CONTINUE 或CONTINUE WHEN 语句结束当前迭代；
   如：v_count :=0;    loop  v_count := v_count+1;   
     if v_count = 3 then  continue;   end  if ;
     或者将此行换为：CIONTINUE WHEN v_count = 3;
     exit when v_count= 5;    end   loop;
  11.4.2、WHILE 循环：在某个条件出现之前一直运行。
    如：v_count := 0;  WHILE v_count<6  
      LOOP   v_count := v_count+1;  END LOOP;
  11.4.3、FOR循环：预先确定次数。
   如：FOR  c   IN   [ REVERSE ]   1..5 
      LOOP
        dbms_output.put_line(c);
      END LOOP;
11.5、游标：可以使用游标（cursor）获取查询返回的记录。
步骤如下：1、声明一些变量，用于保存记录的列值。（注意使用%TYPE，类型要兼容）
               2、声明游标并指定查询。（放在声明中：CURSOR cursor_name  IS select_statement;）
           3、打开游标。（在执行中：OPEN cursor_name）
           4、每次从游标中获取一条记录，并将列值存储在第一步声明的变量中，然后对这些变量执行操作
           FETCH  cursor_name  INTO  variable[, variable···];
           游标可能包含很多条记录，因此要使用循环，为确定循环是否结束，可以使用布尔变量coursor_name%NOTFOUND.
           5、关闭游标。CLOSE  cursor_name;
         例如：SET SERVEROUTPUT ON
            DECLARE
                          v_product_id  products.product_id%TYPE;
                          v_name   products.product_name%TYPE;
                          v_price   products.product.price%TYPE;
            CURSOR  v_product_cursor  IS
               SELECT  product_id   name  price  FROM propucts  ORDER BY product_id;
                BEGIN
                  OPEN  v_product_cursor;
                  LOOP
                        FETCH v_product_cursor
                        INTO   v_product_id, v_name,  v_price
                        EXIT  WHEN  v_product_cursor%NOTFOUND;
                  END  LOOP;
                END;
  11.5.7、游标与FOR循环
    利用for循环可以访问游标中的记录。当使用for循环时，可以不显式的打开和关闭游标——for循环会自动执行这些操作。
    FOR record_name  IN cursor_name  LOOP    statement1; statement2;···  ;   END LOOP;不需要事先声明循环的记录型变量。
    隐含open, fetch, 和close。
   重写上例：SET SERVEROUTPUT ON
            DECLARE
            CURSOR  v_product_cursor  IS
               SELECT  product_id   name  price  FROM propucts   ORDER BY product_id;
                BEGIN
                  FOR  v_product   IN    v_product_cursor  LOOP
                       DBMS.OUTPUT.PUT_LINE( v_product.product_id ||  v_product.name  ||  v_product.price )
                  END  LOOP;
                END;
  11.5.8、OPEN-FOR语句
    DECLARE
      TYPE  t_product_cursor  IS  REF CURSOR  RETURN  products%ROWTYPE;声明一个 REF CURSOR类型
      v_product_cursor  t_product_cursor;
      v_product   products%ROWTYPE;
      
    BEGIN
      OPEN   v_product_cursor  FOR  SELECT  *  FROM   products   WHERE  product_id < 5;
     LOOP
      FETCH v_product_cursor  INTO  v_product;
      EXIT  WHEN  v_product_cursor%NOTFOUND;
      DBMS.OUTPUT.PUT_LINE( v_product.product_id ||  v_product.name  ||  v_product.price );
     END  LOOP;
      CLOSE  v_product_cursor;
    END;
    REF CURSOR是一个指向游标的指针，这样声明的t_product_cursor结合OPEN FOR 语句就可以重用。  
  11.5.9、无约束游标
    前面的游标都有具体的返回类型，称为约束游标；其返回类型必须与游标运行的查询中的列相匹配。无约束油表没有返回类型，因此可以运行任何查询。
    DECLARE
      TYPE  t_cursor  IS  REF CURSOR ;声明一个 REF CURSOR类型，没有返回类型。
      v_cursor  t_cursor;
      
      v_product   products%ROWTYPE;
      v_customer  customers%ROWTYPE;     
    BEGIN
      OPEN   v_cursor  FOR  SELECT  *  FROM   products   WHERE  product_id < 5;
     LOOP
      FETCH v_cursor  INTO  v_product;
      EXIT  WHEN  v_cursor%NOTFOUND;
      DBMS.OUTPUT.PUT_LINE( v_product.product_id ||  v_product.name  ||  v_product.price );
     END  LOOP;
      OPEN   v_cursor  FOR  SELECT  *  FROM   customers   WHERE  customer_id < 3;
     LOOP
      FETCH v_cursor  INTO  v_customer;
      EXIT  WHEN  v_cursor%NOTFOUND;
      DBMS.OUTPUT.PUT_LINE( v_customer.customer_id ||  v_customer.name );
     END  LOOP;
      CLOSE  v_cursor;
    END;
  
11.6、异常。
  系统预定义的异常：
  ACCESS_INTO_NULL(ORA-06530):给未初始化对象赋值。             
  CASE_NOT_FOUND(ORA-06592):case中未找到匹配的when子句，也没有默认的else。
  COLLECTION_IS_NULL(ORA-06531):试图将除exists之外的集合方法应用到一个未初始化的嵌套表或变长数组上。或企图给他们赋值。
  CURSOR_ALREADY_OPEN(ORA-06511):试图打开已经打开的游标。
  DUP_VAL_ON_INDEX(ORA-00001):试图向有唯一索引约束的列中插入重复值。
  INVALID_CURSOR(ORA-01001):程序试图进行非法的游标操作。
  INVALID_NUMBER(ORA-01722):试图将字符串转换成数字数字时失败。
  LOGIN_DENIED(ORA-01017):试图用非法的用户名和密码连接数据库。
  NO_DATA_FOUNC(ORA-01403):SELECT INTO 语句没有返回任何记录，或试图访问嵌套表中已经删除的元素，或者试图在表索引中访问未初始化的元素。
  NOT_LOGGED_ON(ORA-01012):试图在连接数据库之前访问数据库中的数据。
  PROGRAM_ERROR(ORA-06501):PL/SQL内部错误。
  ROWTYPE_MISMATCH(ORA-06504):赋值中的宿主游标变量和PL/SQL游标变量返回类型不兼容。
  SELF_IS_NULL(ORA-30625):试图在空对象中的调用MEMBER方法。
  STORAGE_ERROR(ORA-06500):PL/SQL内存用尽，或内存出现问题。
  SUBSCRIPT_BEYOND_COUNT(ORA-06533):试图通过大于集合中元素个数的索引值引用嵌套表或变长数组元素。
  SUBSCRIPT_OUTSIDE_LIMIT(ORA-06532):试图通过合法范围之外的索引值（如-1）引用嵌套表或变长数组元素。
  SYS_INVALID_ROWID(ORA-01410):将字符串转换成通用记录号rowid的操作失败，原因是该字符串并非合法的rowid。
  TIMEOUT_ON_RESOURCE(ORA-00051):当数据库等待某项资源时发生超时。
  TOO_MANAY_ROWS(ORA-01422):SELECT INTO 语句返回记录数多于一条。
  VALUE_ERROR(ORA-06502):发生算数、转换、截或大小约束错误。
  ZERO_DIVIDE(ORA-01476):试图用0除某个数字。
 
  
11.7、过程
  11.7.1、创建存储过程
    CREATE [ OR REPLACE ] PROCEDURE  procedure_name [(parameter_name [IN | OUT | IN OUT ] type [,··· ]) ]
    {IS | AS }
    BEGIN
      procedure_body
    END;
  11.7.2、调用过程，update_products_price( id ,p_factor)
    位置表示法：CALL  update_products_price( 1,1.5);
    11g新增：命名表示法：CALL  update_products_price( id=>1 ,p_factor=>1.5);
    两种方式合起来是混合表示法。
  11.7.3、获取有关过程的信息
    从all_procedures中可以获得有关要访问的所有过程信息。
  11.7.4、删除过程：DROP  PROCEDURE  update_products_price;
  11.7.5、查看过程中的错误：SHOW  ERRORS
        
11.8、函数
  11.8.1、创建函数：CREATE [ OR REPLACE ] FUNCTION
           function_name [(parameter_name [IN | OUT | IN OUT ] type [,··· ]) ]
           RETURN type
           {IS | AS }
           BEGIN
            function_body
           END  function_name;
    例如：创建一个计算圆面积的函数：
      CREATE  FUNCTION  circle_area (p_radius IN  NUMBER )
      RETUN NUMBRE
      AS  v_pi  number :=3.1415926;
        v_area number;
      BEGIN
        v_area := v_pi*POWER(p_radius,2);
        RETURN v_area;
      END  circle_area;
  11.8.2、调用函数：可以像调用其他数据库内置函数那样调用自定义的函数；
     select circle_area(2) from dual;
     oracle11g中可以使用命名表示法和混合表示法：select circle_area(p_radius =>2) from dual;
  11.8.3、获取有关函数的信息
    可以从user_procedures视图中获得有关函数的信息。
  11.8.4、删除函数：DROP  FUNCTION  circle_area;
  
11.9、包。
  包包含两部分：规范和包体；包的规范列出可用的过程、函数、类型、对象。所有数据库用户都可以访问这些条目，
  因此称为公有项目。包体中任何没有在规范中列出的项目对于包体都是私有的对象。
  11.9.1、创建包规范
     CREATE  [ OR REPLACE ]  PACKAGE  package_name
     { IS | AS }
      package_specification
     END  package_name;
     例如：CREATE  PACKAGE  product_package AS
         TYPE t_ref_cursor  IS  REF CURSOR;
         FUNCTION  get_products_ref_cursor  RETURN  t_ref_cursor;
         PROCEDURE  update_product_price ( p_id  IN  products.product_id%TYPE, p_factor  IN number);
        END   product_package;
  11.9.2、创建包体
     CREATE  [ OR REPLACE ]  PACKAGE  BODY  package_name
     { IS | AS }
      package_body
     END  package_name;
    下面例子为product_package创建包体：
     CREATE  PACKAGE  BODY  product_package  AS 
      FUNCTION get_products_ref_cursor
      RETURN  t_ref_cursor      IS
       v_products_ref_cursor     t_ref_cursor;
      BEGIN
       OPEN  v_products_ref_cursor FOR
        select  product_id, name,price
        from  products;
       RETURN  v_products_ref_cursor;
      END  get_products_ref_cursor;
      
      PROCEDURE  update_product_price(
       p_id  IN  products.product_id%TYPE,
       p_factor  IN number
      ) AS
       v_product_count  INTEGER;
      BEGIN
       SELECT  COUNT(*)
       INTO  v_product_count
       FROM products
       WHERE  prouct_id = p_id;
       
       IF      v_product_count = 1 THEN
         UPDATE products
         SET  price = price * p_factor
         WHERE   product_id = p_id;
         COMMIT;
       END  IF;
      EXCEPTION 
       WHEN  OTHERS  THEN
            ROLLBACK;
      END  update_product_price;
     END  product_package;
     /
  11.9.3、调用包中的函数和过程
   select product_package.get_products_ref_cursor  from   dual;
   CALL  product_package.update_product_price(3 , 1.25);
  11.9.4、获取包中的函数和过程信息
   从user_procedures中检索object_name为product_package的所有信息。
  11.9.5、删除包：DROP  PACKAGE  product_package;
     
11.10、触发器：当特定的SQL DML 语句在特定的数据库上运行时，由数据库自动运行的过程。
  11.10.1、运行时机：可在DML语句运行前或者后被激活。分为行级触发器和语句级触发器。
     当UPDATE语句在某个列上激活行级触发器时，这个触发器可以同时访问此列的原值和新值。也可以通过使用触发器条件来限制行级触发器的激活。
  11.10.2、创建触发器
     CREATE [ OR REPLACE ] TRIGGER trigger_name
     {BEFOR |AFTER | INSTEAD OF | FOR } trigger_event
     ON table_name
     [ FOR EACH ROW ]
     [ { FORWARD | REVERSE } CROSSEDITION ]
     [ { FOLLOWS | PRECEDES } schema.other_trigger } ]
     [ {ENABLE | DISABLE } ]  
     [ WHEN trigger_condition ] ]
     BEGIN
       trigger_body
     END  trigger_name;    
   其中[ { FORWARD | REVERSE } CROSSEDITION ]是11g新增的特性，一般用于数据库管理员和应用程序管理员
   FORWARD 默认设置，当联机应用程序正在增加补丁或升级时对数据库进行某种改变，触发器被激活。REVERSE是在那之后。
   { FOLLOWS | PRECEDES } schema.other_trigger }  Oracle 11g新增，规定一个触发器在schema.other_trigger 中规定的另一个触发器激活之后或者之前激活。
   [ {ENABLE | DISABLE } ] Oracle 11g新增，指出创建时启用还是禁用此触发器。
  11.10.3、获取有关触发器信息
    从user_triggers视图可以获得触发器信息。从all_triggers中可以获得要访问的所有触发器信息。
    SQL*PLUS 中SET LONG命令可以查看触发器的所有代码。
  11.10.4、禁用和启用触发器   ALTER  TRIGGER  trigger_name  DISABLE/ENABLE;
  11.10.5、删除触发器:DROP TRIGGER trigger_name;

11.11 Oracle Database 11g新增的PL/SQL特性
  11.11.1、SIMPLE_INTEGER 类型
    它是BINARY_INTEGER的子类型，存储范围与BINARY_INTEGER相同但是不能存储null值。使用此类型时算术溢出被截断，因此溢出时不会产生错误
  11.11.2、在PL/SQL中使用序列（可以使用nextval和currval伪列）
    CREATE  SEQUENCE  s_product_id;