学习PL/SQL之七：子程序

关于子程序：
   子程序是指被命名的PL/SQL块，这种块可以带有参数，可以在不同应用程序中多次调用。
   PL/SQL有两种类型的子程序：过程和函数。过程用于执行特定操作，而函数用于返回特定数据。
开发过程：PROCEDURE
  创建过程
  CREATE [OR REPLACE] PROCEDURE proc_name
   [ (arg_name [ { IN | OUT | IN OUT } ] TYPE,
    …
     arg_name [ { IN | OUT | IN OUT } ] TYPE) ]
   { IS | AS }
  procedure_body
  ……
  OR REPLACE：如果要创建的过程存在，则覆盖或者替换它。
    Procedure_body：是一个带有声明段、可执行段和异常处理段的PL/SQL语句块
  PROCEDURE中定义的要传的参数（ arg_name ）叫做形式参数，具体调用该PROCEDURE时（ proc_name (1,’Johnson’)）传进去的参数叫做实际参数。
    IN
      在调用过程的时候，实际参数的值被传递给该过程；在过程内部，形参是只可读的。
    OUT
      在调用过程时，任何的实参将被忽略(必须要在过程内部赋值)；在过程内部，形参是只可写的。过程结束时，控制会返回给控制环境，而形式参数的内容将赋给调用时的实际参数。
   IN OUT
      是IN与OUT的组合。在调用过程的时候，实参的值可以被传递给该过程；在过程内部，形参也可以被读出也可以被写入；过程结束时，控制会返回给控制环境，而形式参数的内容将赋给调用时的实际参数。
   参数的缺省模式是IN
   CREATE OR REPLACE PROCEDURE ModeTest(
  p_InParm IN NUMBER := 10 ,
  p_OutParm OUT NUMBER,
  p_InOutParm IN OUT NUMBER)
 IS
  v_LocalVar NUMBER;
 BEGIN
   v_LocalVar := p_InParm;   --legal
   p_InParm := 7;   --illegal
   p_OutParm := 7;  --legal
   v_LocalVar := p_OutParm;  --Possibly illegal
   v_LocalVar := p_InOutParm; --legal
   p_InOutParm := 5;  --legal
   …
  EXCEPTION
   WHEN OTHERS THEN
    …
 END ModeTest;
 模式为IN OUT或OUT参数对应的实际参数必须是变量，不能是常量或表　达式。因为必须要有一个位置（即传进去的那个变量）可以存储返回的                    值（见callMT.sql）
  模式为IN可以传常量也可以传变量
  形式参数不能声明约束（如:不能指定缓冲区的大小），但可使用%TYPE来进行约束
  在调用过程当中，实际参数在将值传递给过程时，也传递了对变量的约束。
   -- illegal
 CREATE OR REPLACE PROCEDURE ParameterLength (
    p_Parameter1 IN OUT VARCHAR2(20), --illegal with VARCHAR2(10)
    p_Parameter2 IN OUT NUMBER(3,1)) AS --illegal with NUMBER(3,1)
 BEGIN
    p_Parameter1 := 'abcdefghijklm';
    p_Parameter2 := 12.3;
 END ParameterLength;
 /
 
 -- Legal
 CREATE OR REPLACE PROCEDURE ParameterLength (
    p_Parameter1 IN OUT VARCHAR2,
    p_Parameter2 IN OUT NUMBER) AS
 BEGIN
    p_Parameter1 := 'abcdefghijklm';
    p_Parameter2 := 12.3;
    dbms_output.put_line('p_Parameter1 :'||p_Parameter1);
    dbms_output.put_line('p_Parameter2 :'||p_Parameter2);
 END ParameterLength;
 /
 
 DECLARE
    v_Variable1 VARCHAR2(40);
    v_Variable2 NUMBER(7,3);
 BEGIN
    ParameterLength(v_Variable1, v_Variable2);
 END;
 /
 
 DECLARE
    v_Variable1 VARCHAR2(10);--illegal,'abcdefghijklm'.lenth > 10
    v_Variable2 NUMBER(7,3);--legal
 BEGIN
    ParameterLength(v_Variable1, v_Variable2);
 END;
 /
指定实参的模式 
  位置标示法
    调用时添入所有参数，实参与形参按顺序一一对应
  名字标示法
     调用时给出形参名字(顺序不要求一一对应)，并给出实参
     ModeTest(p_InOutParameter  => v_var2,
      p_OutParameter => v_var1,
     p_InParameter => 10);
  两种方法可以混用
   混用时，第一个参数必须通过位置来指定
   名字标示法对于参数很多时，可提高程序的可读性
  
   CREATE OR REPLACE PROCEDURE ModeTest(
   p_In IN NUMBER,
   p_Out IN NUMBER,
   p_inout IN NUMBER)
  IS
   v_Local Number;
  BEGIN
   --DBMS_OUTPUT.Enable;
   v_Local := p_In;
   DBMS_OUTPUT.PUT_LINE( '1--> '||V_LOCAL|| ' 2--> '||P_OUT||' 3--> '||P_INOUT);
  END ModeTest;
  /
  
  DECLARE
   A NUMBER;
   B NUMBER;
   C NUMBER;
  BEGIN
   A := 5;
   B := 6;
   C := 7;
   DBMS_OUTPUT.PUT_LINE(' A = ' || A || ' ,B = ' || B || ' ,C = ' || C);
   ModeTest(A,B,C);
   DBMS_OUTPUT.PUT_LINE(' A = ' || A || ' ,B = ' || B || ' ,C = ' || C);
  END;
  /
  
  DECLARE
   A NUMBER;
   B NUMBER;
   C NUMBER;
  BEGIN
   A := 5;
   B := 6;
   C := 7;
   DBMS_OUTPUT.PUT_LINE(' A = ' || A || ' ,B = ' || B || ' ,C = ' || C);
   ModeTest(p_In => C,p_INOUT => B, p_Out => A);
   DBMS_OUTPUT.PUT_LINE(' A = ' || A || ' ,B = ' || B || ' ,C = ' || C);
  END;
  /
  
  DECLARE
   A NUMBER;
   B NUMBER;
   C NUMBER;
  BEGIN
   A := 5;
   B := 6;
   C := 7;
   DBMS_OUTPUT.PUT_LINE(' A = ' || A || ' ,B = ' || B || ' ,C = ' || C);
   ModeTest( C,p_INOUT => B, p_Out => A);
   DBMS_OUTPUT.PUT_LINE(' A = ' || A || ' ,B = ' || B || ' ,C = ' || C);
  END;
  /
  
  
  DECLARE
   A NUMBER;
   B NUMBER;
   C NUMBER;
  BEGIN
   A := 5;
   B := 6;
   C := 7;
   DBMS_OUTPUT.PUT_LINE(' A = ' || A || ' ,B = ' || B || ' ,C = ' || C);
   ModeTest( p_In => C,B,A);--illegal
   DBMS_OUTPUT.PUT_LINE(' A = ' || A || ' ,B = ' || B || ' ,C = ' || C);
  END;
  /
  
  
开发函数
  CREATE [OR REPLACE] FUNCTION func_name
  [ (arg_name [ { IN | OUT | IN OUT } ] TYPE,
   …
  (arg_name [ { IN | OUT | IN OUT } ] TYPE )]
  RETURN TYPE
 { IS | AS }
  Func_body
  在函数的主体内部，return语句用来将控制通过一个数值(或其他返回类型)返回给调用环境，且返回类型都不能加上约束。
RETURN <类型>;
  在一个函数主体中，可以使用多个返回语句，只有一个会被执行
  没有返回语句的函数将是一个错误
  CREATE OR REPLACE FUNCTION ClassInfo(
    p_Department classes.department%TYPE,
    p_Course classes.course%TYPE)
    RETURN VARCHAR2 IS
 
    v_CurrentStudents NUMBER;
    v_MaxStudents NUMBER;
    v_PercentFull NUMBER;
 BEGIN
    SELECT current_students,max_students
      INTO v_CurrentStudents, v_MaxStudents
      FROM classes
      WHERE department = p_Department
      AND course = p_Course;
 
      v_PercentFull := v_CurrentStudents /v_MaxStudents * 100;
 
      IF v_PercentFull = 100 THEN
         RETURN 'Full';
      ELSIF v_PercentFull > 80 THEN
         RETURN 'Some Room';
      ELSIF v_PercentFull > 60 THEN
         RETURN 'More Room';
      ELSIF v_PercentFull > 0 THEN
         RETURN 'Lots of Room';
      ELSE
         RETURN 'Empty';
      END IF;
 END ClassInfo;
  /
  函数可以通过OUT参数来返回多个数值
  函数可以接收缺省参数
函数调用限制
  因为函数必须要返回数据，所以只能作为表达式的一部分调用，另外，函数也可以在SQL语句的一下部分调用：
   1、SELECT 命令的选择列表；
   2、WHERE 和 HAVING 字句中
   3、CONNECT BY,START WITH,ORDER BY 以及GROUP BY字句中。 
   4、INSERT 命令的VALUES字句中
   5、UPDATE 命令的SET子句中
  注意：并不是所有的函数都可以在SQL语句中调用，在SQL语句中调用函数有一下一些限制：
    1、在SQL 语句中只能调用存储函数（服务器端），而不能调用客户端的函数；
    2、在SQL 语句中调用的函数只能带有输入参数（IN），而不能带有输出参数（OUT）和输入输出参数（IN OUT）
    3、在SQL语句中调用的函数只能使用SQL所支持的标准数据类型，而不能使用PL/SQL特有的数据类型（例如：BOOLEAN TABLE,VARRAY）
    4、在SQL语句中调用的函数不能包含INSERT UPDATE DELETE语句