PL/SQL学习笔记：存储过程和函数[转]

  PL/SQL中的存储过程和函数是用来完成特定功能的子程序。

1.创建存储过程
    存储过程的创建包括参数的定义与过程体的定义两部分。如下是存储过程的一个结构体。
   
    CREATE OR REPLACE PROCEDURE 过程名
    (
      ... --参数定义部分
    ) IS
      ... --局部变量定义部分
    BEGIN
      ... --可执行部分
    EXCEPTION
      ... --异常处理部分
    END 过程名;
   
    如下是一个存储过程的完成事例：
   
    CREATE OR REPLACE PROCEDURE get_happy
    (p_ename_in IN VARCHAR2)
    IS
      hiredate DATE;
    BEGIN
      hiredate := SYSDATE - 2;
      INSERT INTO employee
        (emp_name, hiredate)
      VALUES (p_ename_in, hiredate);
    EXCEPTION
      WHEN DUP_VAL_IN_INDEX
      THEN
        DBMS_OUTPUT.PUT_LINE
          ('Cannot insert.');
    END;
   
   
  1.1 存储过程的参数
   1.1.1存储过程的参数分为实参和形参两种。
    形参也叫形式参数，如上面代码的p_ename_in就是形参。
    如下的代码是对get_happy进行调用：
  
    DECLARE
      v_ename_in employee.emp_name%TYPE := 'Tom';
    BEGIN
      get_happy(v_ename_in);
    END;
   
    上面代码是对get_happy存储过程的调用。其中v_ename_in就是实际参数，也叫实参。
 
   1.1.2在参数的定义中，IN、OUT和IN OUT关键字代表参数的三种不同模式。
    a)IN: 代表输入的参数。即该参数是只读的，如果在传剑存储过程时没有指定参数的模式，则默认为IN。
    b)OUT: 代表输出的参数。即该参数是只写的，只能为其赋值。
    c)IN OUT: 代表既输出又输出的参数。即可读写。
    例如，下面是一个含有三种从那书模式的存储过程：
   
    CREATE OR REPLACE PROCEDURE UpdateAuthsSalary(
      p_Author_code IN OUT auths.author_code%TYPE,
      p_Salary IN NUMBER,
      p_Name OUT auths.name%TYPE
      ) IS
      v_Salary_temp NUMBER; --定义存储过程中的局部变量
    BEGIN
      SELECT salary INTO v_Salary_temp
        FROM AUTHS
        WHERE author_code = p_Author_code;
      --如果该作家的工资小于300元,则修改其中工资
      IF v_Salary_temp < 300 THEN
        UPDATE auths
          SET salary = p_Salary
          WHERE author_code = p_Author_code;
      END IF;
     
      SELECT name
        INTO p_Name
        FROM auths
        WHERE author_code = p_Author_code;
    END UpdateAuthsSalary;
  
    1.1.3参数的数据类型
      我们可以观察一下前面的存储过程，比如
     
      CREATE OR REPLACE PROCEDURE get_happy(
       p_ename_in IN VARCHAR2
       )IS
      
     以及
    
     CREATE OR REPLACE PROCEDURE UpdateAuthsSalary(
      p_Author_code IN OUT auths.author_code%TYPE,
      p_Salary IN NUMBER,
      p_Name OUT auths.name%TYPE
      ) IS
    
    我们会发现 get_happy的p_ename_in的类型为VARCHAR2，VARCHAR2没有指定长度。UpdateAuthsSalary的p_Salary的类型为NUMBER，NUMBER同样没有指定长度。那是因为:
    在定义一个存储过程参数时，不能指定CHAR类型和VARCHAR2类型形参的长度，也不能指定NUMBER形参的精度和标度。这些约束由实参来传递。
   
  1.1.4  参数的传值方式
    在调用存储过程时，有下面两种参数传值方式：
    a)位置表示法
    b)名称表示法
    比如就用调用上面的UpdateAuthsSalary存储过程为例，如下传值方式就是位置表示法。
   
  DECLARE
    v_Author_code VARCHAR2(6) := 'A00001';
    v_Salary IN NUMBER(5) := 350,
    v_Name VARCHAR2(6);
  BEGIN
    UpdateAuthsSalary(v_Author_code, v_Salary, v_Name);
  END;
  
  如下传值方式就是名称表示法。
  
  DECLARE
    v_Author_code VARCHAR2(6) := 'A00001';
    v_Salary IN NUMBER(5) := 350,
    v_Name VARCHAR2(6);
  BEGIN
    UpdateAuthsSalary(
      p_Author_code => v_Author_code,
      p_Salary => v_Salary,
      p_Name => v_Name);
  END;
  
  也可以混合使用，但是，当在调用存储过程中出现了第一个命名表示法的参数时，后面的参数也必须使用命名表示法传值。如下：
  
  DECLARE
    v_Author_code VARCHAR2(6) := 'A00001';
    v_Salary IN NUMBER(5) := 350,
    v_Name VARCHAR2(6);
  BEGIN
    UpdateAuthsSalary(v_Author_code,
      p_Name => v_Name,
      p_Salary => v_Salary);
  END;

2.创建函数

   如下是函数的结构体。
   
    CREATE OR REPLACE FUNCTION 函数名
    (
      ... --参数定义部分
    ) RETURN 返回类型 IS
      ... --局部变量定义部分
    BEGIN
      ... --可执行部分
    EXCEPTION
      ... --异常处理部分
    END 过程名;
   
  与存储过程一样，参数列表是可选的。在没有参数的情况下，函数的定义与调用都没有圆括号。但返回值类型是必须的，因为函数是作为表达式的一部分调用，必须返回一个值。如下是一个函数的完整的例子：
 
  CREATE OR REPLACE FUNCTION company_type (
   p_type_code_in IN VARCHAR2)
   RETURN VARCHAR2
 IS
   v_return_value VARCHAR2 (25) := NULL;
 BEGIN
    IF p_type_code_in = 'S'
    THEN
       v_return_value := 'SUBSIDIARY';
 
    ELSIF p_type_code_in = 'P'
    THEN
       v_return_value := 'PARTNER';
    END IF;
 
    RETURN v_return_value;
 END;

  可以利用下面的块来调用上面的函数：
  DECLARE
    v_type_code_in VARCHAR2(1);
    v_company_type VARCHAR2(50);
  BEGIN
    v_type_code_in := 'P';
    v_company_type := company_type(v_type_code_in);
    DBMS_OUTPUT.PUT_LINE(v_company_type);
  END;