oracle3

 一.EXCEPTION
 1 SQLCODE
 2 SQLERRM
 
Exception Name             Oracle        Description
                           Error
                          
ACCESS_INTO_NULL           ORA-          Attempted to assign values to the attributes of
                           06530         an uninitialized object
CASE_NOT_FOUND             ORA-          None of the choices in the WHEN clauses of a
                           06592         CASE statement is selected, and there is no
                                         ELSE clause.
COLLECTION_IS_NULL         ORA-          Attempted to apply collection methods other
                           06531         than EXISTS to an uninitialized nested table
                                         or varray
CURSOR_ALREADY_OPEN        ORA-          Attempted to open an already open cursor
                           06511
DUP_VAL_ON_INDEX           ORA-          Attempted to insert a duplicate value
                           00001
INVALID_CURSOR             ORA-          Illegal cursor operation occurred
                           01001
INVALID_NUMBER             ORA-          Conversion of character string to number fails
                           01722
LOGIN_DENIED               ORA-          Logging on to Oracle with an invalid
                           01017         username or password
NO_DATA_FOUND              ORA-          Single row SELECT returned no data
                           01403
NOT_LOGGED_ON              ORA-          PL/SQL program issues a database call
                           01012         without being connected to Oracle
PROGRAM_ERROR              ORA-          PL/SQL has an internal problem
                           06501
ROWTYPE_MISMATCH           ORA-          Host cursor variable and PL/SQL cursor
                           06504         variable involved in an assignment have
                                         incompatible return types
STORAGE_ERROR                ORA-        PL/SQL ran out of memory or memory is
                             06500       corrupted.
SUBSCRIPT_BEYOND_COUNT       ORA-        Referenced a nested table or varray element
                             06533       using an index number larger than the number
                                         of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT      ORA-        Referenced a nested table or varray element
                             06532       using an index number that is outside the legal
                                         range (–1 for example)
SYS_INVALID_ROWID            ORA-         The conversion of a character string into a
                             01410       universal ROWID fails because the character
                                         string does not represent a valid ROWID.
TIMEOUT_ON_RESOURCE          ORA-        Time-out occurred while Oracle is waiting for
                             00051       a resource.
TOO_MANY_ROWS                ORA-        Single-row SELECT returned more than one row.
                             01422  
VALUE_ERROR                  ORA-        Arithmetic, conversion, truncation, or size-
                             06502       constraint error occurred.
ZERO_DIVIDE                  ORA-01476   Attempted to divide by zero
                                  

===
eg:
 declare
     v_id   emp.emp_id%type;
     v_name emp.emp_name%type;
     v_error_id     number;
     v_error_msg    varchar(128);
     v_error        exception;
     v_number       number;
 begin
     select count(CREATE PROCEDURE query_employee
(p_id IN employees.employee_id%TYPE,
 p_name OUT employees.last_name%TYPE,
 p_salary OUT employees.salary%TYPE,
 p_comm OUT
   employees.commission_pct%TYPE)
AUTHID CURRENT_USER
IS
BEGIN
  SELECT last_name, salary,
         commission_pct
   INTO p_name, p_salary, p_comm
   FROM employees
   WHERE employee_id=p_id;
END query_employee;
*) into v_number from emp where emp.emp_id=9;
     if v_number=0 then
     raise  v_error;
     end if;
     commit;
exception
     when v_error then
       rollback;
       v_error_id :=SQLCODE;
       v_error_msg :=SQLERRM;
       dbms_output.put_line('count=0 ');
       insert into error_table values(v_error_id,v_error_msg);
     when NO_DATA_FOUND  then
       rollback;
       v_error_id :=SQLCODE;
       v_error_msg :=SQLERRM;
       dbms_output.put_line('no data found');
       insert into error_table values(v_error_id,v_error_msg);
     when others then
       rollback; rollback;
       v_error_id :=SQLCODE;
       v_error_msg :=SQLERRM;
       dbms_output.put_line(' other exception');
       insert into error_table values(v_error_id,v_error_msg);
end;
==================================================================
二. PROCEDURE

创建
 create [or replace ] procedure
 is
 //不用写declare
    .....
    begin
      .....
    exception
      ....
    end;
查找 PROCEDURE
select object_name from user_objects where object_type="PROCEDURE"
执行
 execute Procedure_name;
 or :
  begin
     Procedure_name;
  end;
修改
   desc user_source;
   select text from user_source where name='procedure_name';//名字要大写，数据库中保存为大写

eg:
  1  create procedure p_procedure_exception
  2  is
  3     v_id   emp.emp_id%type;
  4       v_name emp.emp_name%type;
  5       v_error_id     number;
  6       v_error_msg    varchar(128);
  7       v_error        exception;
  8       v_number       number;
  9   begin
 10       select count(*) into v_number from emp where emp.emp_id=9;
 11       if v_number=0 then
 12       raise  v_error;
 13       end if;
 14       commit;
 15  exception
 16       when v_error then
 17         rollback;
 18         v_error_id :=SQLCODE;
 19         v_error_msg :=SQLERRM;
 20         dbms_output.put_line('count=0 ');
 21         insert into error_table values(v_error_id,v_error_msg);
 22       when NO_DATA_FOUND  then
 23         rollback;
 24         v_error_id :=SQLCODE;
 25         v_error_msg :=SQLERRM;
 26         dbms_output.put_line('no data found');
 27         insert into error_table values(v_error_id,v_error_msg);
 28       when others then
 29         rollback; rollback;
 30         v_error_id :=SQLCODE;
 31         v_error_msg :=SQLERRM;
 32         dbms_output.put_line(' other exception');
 33         insert into error_table values(v_error_id,v_error_msg);
 34* end;
SQL> /
Procedure created.
SQL> execute p_procedure_exception;
PL/SQL procedure successfully completed.
SQL>
==================================================
三. function  P.289~
eg1:
SQL> create or replace function get_salary (
  2     v_id in  number)
  3     return number
  4   is
  5   v_salary emp.emp_salary%type;
  6   begin
  7      select emp_salary into v_salary
  8               from emp where emp_id=v_id;
  9      return v_salary;
 10* end get_salary;
SQL> /

Function created.
SQL>select emp_name,get_salary(emp_id) from emp where emp_id=2

EMP_NAME
--------------------------------------------------------------------------------
GET_SALARY(EMP_ID)
------------------
香港
              2000
 eg2:
===================================================
优点：
  在数据库里面操作，提高了效率
缺点：
  不易移植
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
四。 privileges  权限
 eg:
  两个用户下都有一张employee 表

  当某个用户调用这个procedure
CREATE PROCEDURE query_employee
(p_id IN employees.employee_id%TYPE,
 p_name OUT employees.last_name%TYPE,
 p_salary OUT employees.salary%TYPE,
 p_comm OUT
   employees.commission_pct%TYPE)
AUTHID CURRENT_USER // 如果有这句，就执行当前用户自己的 employees 表
IS
BEGIN
  SELECT last_name, salary,
         commission_pct
   INTO p_name, p_salary, p_comm
   FROM employees
   WHERE employee_id=p_id;
END query_employee;
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
五
  1.user_objects
  2.List All Procedures and Functions
    SELECT object_name, object_type
    FROM user_objects
    WHERE object_type in (’PROCEDURE’,
    ’FUNCTION’)ORDER BY object_name;
  3.USER_SOURCE Data Dictionary View
    //查看过程或函数的代码
    SELECT text
    FROM user_source
    WHERE name = ’QUERY_EMPLOYEE’
    ORDER BY line;

=====================================================
六。package

只有在package
select * from user_procedures;
只显示放在package中的procedure
创建包：
1.
package头信息
create or replace package my_package is
   .......
   function name(v_var type) return type;
end my_package;
   )

package body 信息
create or replace package body my_package is
   ...//实现上面头信息中声明的东西
   function name(v_var type) return type
    is
      begin
         ......
    end name;
end my_package;
)
eg:
 create or replace package my_package is
   function get_tax(v_salary number) return number;
 end my_package;
 create or replace package body my_package is
   function get_tax(v_salary number)
       return number
   is
       begin
       return v_salary*.2;
   end get_tax;
 end my_package;
执行函数脚本：
var sal number;
execute :sal :=my_package.get_tax(100);
print sal;
执行结果：
anonymous block completed
sal
--
20