PL/SQL 注意 02

1. Exception

  种类 : Predefine Oracle Server                       ( Implicitly raised ) , 只有20个

             NonPredefine Oracle Server                ( Implicitly raised )  除了以上20个,其他server提供的exception

             User-defined                                           ( Explicitly raised ) 

  Predefine

  语法 : EXCEPTION

                      WHEN   exception1 OR exception 2 THEN

                                   STATEMENT 1 ;

                      WHEN OTHERS THEN

                                   STATEMENT 2 ;

  当程序抛出异常时 , 程序不会象设想一样 , 返回到某段程序... 直接退出程序 .

  when an exception is raised , normal execution of your PL/SQL block or subprogram stops and control transfers to its exception-handling

  part . when these exception statements complete execution of the block or subprogram , control does not return to where the exception was

  raised , In other words , you can not resume processing where you left off .

  例如 : EXCEPTION

                           WHEN   NO_DATE_FOUND THEN

                                          STATEMENT1 ;

                           WHEN   TOO_MANY_ROWS THEN

                                          STATEMENT2;

                           WHEN OTHERS THEN

                                          STATEMENT3;

             END ;

 Nonpredefined   

 DECLARE

        e_emps_remaining           EXCEPTION

        PRAGMA  EXCEPTION_INIT ( e_emps_remaining, -2292 ) ;      -->( 分配系统异常号码 error number  -2292 )

 EXCEPTION

       WHEN e_emps_remaining THEN

                     STATEMENT1;

 END ;

 Functions for Trapping Exceptions  

  SQLCODE : returns the numeric value for the error code . ( 0 no exception encountered 1 User-defined exception100 No_Data_found exception负数 其他问题 )

  SQLERRM : returns the message associated with the error number

  例如 :

  DECLARE

         v_error_code           NUMBER ;

         v_error_message  VARCHAR2(255) ;

  BEGIN

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

  EXCEPTION

        WHEN OTHERS THEN                                   -->因为这是用的 others , 所有的异常全部 handle , 但是具体是什么异常呢 , 就需要用到这两个函数

               v_error_code := SQLCODE ;

               v_error_message  := SQLERRM ;

  END ;

  User - Defined  

  DECLARE

      e_invalid_department       EXCEPTION ;

  BEGIN

      UPDATE departments

      SET         department_name = 'asdf'

      WHERE  department_id = 'asdf'

      IF SQL%NOTFOUND THEN

          RAISE e_invalid_department s;

      END IF ;

      COMMIT ;

 EXCEPTION

      WHEN e_invalid_department THEN

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

  END ;

  Propagating an Exception in a subblock

  BEGIN

    SELECT .....

    UPDATE .....

    IF SQL%NOTFOUND THEN

        RAISE e_no_rows ;                        -->发现异常,交由下面的异常管理程序处理,不是立刻处理

    END IF ;

 END ;

 END LOOP;

 EXCEPTION

     WHEN e_integerity THEN ....

     WHEN e_no_rows THEN

 END ;

 When the exception propagates to an enclosing block , the remaining executable actions in that block are bypassed .

 One advantage of this behavior is that you can enclose statements that require their own exclusive error handling in their own block , while

   leaving more general exception handling to the enclosing block .

RAISE_APPLICATION_ERROR

  RAISE_APPLICATION_ERROR( error_number , message, [ TRUE | FALSE] );       -->返回error code 和error message ( 象 server 一样 )

  例如 :  IF SQL%NOTFOUND THEN 

                       RAISE_APPLICATION_ERROR ( -20202, ' This is not a valid manager ' ) 

              END IF

2. Create Procedure

  Every PL/SQL construct is composed of one or more blocks . These blocks can be entirely separate or nested within one another .

  Subprogram :

     - A procedure that performs an action

     - A function that computes a value

  Block structure

  DECLARE

  BEGIN

  EXCEPTION

  END;

  END and other PL/SQL statements do require semicolons ( 需要分号 ; )

  语法 :

  CREATE [ OR REPLACE ] PROCEDURE procedure_name

  [ ( parameter1 [ mode1] datatype1,                           --> datatype ( 可以是 %TYPE , %ROWTYPE )

    ( parameter2 [ mode2 ] datatype2, ... ) ]                   --> mode ( 参数类型 , IN OUT , IN OUT )

  IS | AS

  PL/SQL Block ;

  好习惯 : 形式参数前 + p_ , 例如 p_name

  参数的 mode :

  in :   --> ( 只是将参数传给 procedure ) 默认类型

  out :  <-- ( 只是返回对应参数 , 并不将参数传给 procedure )

  in out : <--> ( 既传进参数 , 又传出参数 )

  execute procedure ( argument ) ;

  例如 :

  CREATE OR REPLACE PROCEDURE query_emp (

       p_id                   IN                employees.employee_id%TYPE,

       p_name            OUT            employees.lastname%TYPE,

       p_salary            OUT            employees.salary%TYPE,

       p_comm           OUT            employees.commission_pct%TYPE )

  IS

  BEGIN

       SELECT last_name, salary, comission_pct

       INTO p_name, p_salary, p_comm

       FROM employees

       WHERE employee_id = p_id ; 

  END query_emp ;

  /                                       --> 这个 / 很有用 , sqlplus 中 / 这个是运行的意思 , 当创建存储过程时 , 需要 / 运行, 才能创建成功 .

  环境中调用以上 procedure :   

  VARIABLE g_name  VARCHAR2(25)

  VARIABLE g_sal       NUMBER

  VARIABLE g_comm NUMBER

  EXECUTE query_emp( 171, :g_name, :g_sal, :g_comm )          --> 必须使用变量接收

  PRINT g_name g_sal g_comm

  DROP procedure procedure_name ( 删除 procedure )

  EXCEPTION 写在 BEGIN 与 END ; 中间 , 那么， 如果这个 BLOCL ( 从 begin 到 end ) 出现问题 , 由它的 EXCEPTION 捕获异常

  异常捕获 与 出现异常但是没有捕获的区别 : if the exception is handled , the block terminates , and control goes to the calling program .

                                                                               Any data manipulation language ( DML ) statements issued before the exception was raisedremain as part of the transaction.

                                                                               if the exception is unhandled , the block terminates , and control goes to the exception section , unhandled , all the DML

                                                                               statements are rolled back along with any changes to any host variables .

3.Function ( 很像存储过程 )

  语法 :

  CREATE OR REPLACE FUNCTION function_name

  [ ( parameter1 [ mode1 ] datatype1 , parameter2 [ mode2 ] datatype2 , ... )

  ]

  RETURN datatype                  --> The return datatype must not include a size specification .

  IS | AS

  PL/SQL BLOCK ;

  show errors ; ( 查看compile时出现错误 )

  例子 :

  CREATE OR REPLACE FUNCTION get_sal

   ( p_id IN employees.employee_id%TYPE)

   RETURN NUMBER

   IS

   v_salary employees.salary%TYPE := 0 ;

   BEGIN

      SELECT salary

      INTO   v_salary

      FROM employees

      WHERE employee_id = p_id ;

      RETURN  v_salary ;                           --> RETURN语句要放在 excute section 中间

    END get_sal ;

     /                                                          --> 同 procedure 一样 , 是用来运行的

    Invoke a function as part of a PL/SQL expression , because the function will return a value to the calling environment .

    Function Execution

    A function may accept one or many parameters , but must return a single value . 

    avoid using the OUT and IN OUT modes with function . ( 只能用 IN )

    EXECUTE :g_salary ( 变量 ) := get_sal( 117 )

    PRINT g_salary

    SELECT employee_id , tax( salary )          --> 像普通函数那样使用, 个人定义的函数

    FROM employees

    Location to Call User-Defined Functions

    ^ select list of a select command

    ^ condition of the where and having clauses

    ^ connect by , start with,  order by, and group by clause

    ^ values clause of the insert command

    ^ set clause of the update command

  SELECT employee_id, tax( salary )

  FROM employees

  WHERE tax( salary ) > ( SELECT MAX( tax( salary) )

                                           FROM employees WHERE department_id = 30 )

  ORDER BY tax ( salary ) DESC ;

  规则 : ( SQL 标准 )

  ^ When called from a SELECT statement or a parallelized UPDATE or DELETE statement , the function can not  modify any database tables

  ^ When called from an UPDATE or DELETE statement , the function cannot query or modify any database tables modified by that statement .

  ^ When called from a SELECT , INSERT , UPDATE, DELETE statement , the function cannot execute SQL transaction control statement ,

     such as COMMIT , session control statement ( such as SET ROLE ) , or system control statement ( such as ALTER SYSTEM ) Also ,it

     cannot execute DDL statement ( such as CREATE) because they are followed by an automatic commit .

  ^ The function cannot call another subprogram that breaks one of the above restrictions .

删除 : DROP FUNCTION function_name

CREATE OR REPLACE 比较 ( Versus ) DROP and CREATE

create or replace ( 先删除function, 再创建function,所有的权限会被保存, 而drop and create , 也是先删除再创建, 但是权限也被收回

                                 比如分配给其他用户的执行权限 )

 函数只能返回一个 single value .