PL/SQL-procedure-function-package创建

PL/SQL有下列程序单元：过程,函数,包说明,包体 

存储过程是作为对象存在于oracle数据库的程序单元。oracle实现存储过程作为过程、函数和包。 
不能调用包，可以调用包中可见部分的过程和函数。包是封装同子程序一样持久数据的程序单元。 

参数模式有：IN 、OUT、IN OUT。 

一、过程 
PL/SQL过程是一个编译到oracle数据库模式的单机程序。过程可以接受参数。当编译一个过程时，CREATE PROCEDURE语句的过程标识符成为数 

据字典中的对象名。 
CREATE OR REPLACE PPROCEDURE procedure_name (optional parameters) IS | AS 
        declarative part 
BEGIN 
        program body 
    EXCEPTION 
        exception handler 
END procedure_name; 
   
Declarative Part   是声明变量的地方，比如： 
                   local_counter NUMBER := 0; 
                   也可以声明复合类型结构，如records和tables 
                   也可以声明异常，但必须在Exception部门进行处理。 
Subprogram Body    包含使用PL/SQL控制结构的逻辑算法实现。PL/SQL控制结构支持loops、if-then-else、case、和声明块结构。 
Exception handler 可选，类似于其他语言的try-catch模型，你可以对特定错误类型或一般异常编写处理器. 

应当以动词来命名一个过程。过程常常执行一些操作如更新数据库、写数据到一个文件或者发送一条消息。 
一个过程不必要有参数，如果没有参数创建过程是不需要括号。当调用一个过程时（过程没有参数）括号是可选的。 
例如： 
CREATE OR REPLACE PROCEDURE insert_temp IS 
BEGIN 
    INSERT INTO TEMP (n) VALUES (0); 
END insert_temp: 

sqlplus调用过程： 
SQL>execute insert_temp; 
SQL>execute insert_temp(); 
都是正确的。 

IS 或 AS关键字是一样的，都可以用。 
PROCEDURE insert_temp IS | AS 

END关键字后加过程名也是可选的，但建议加过程名。一个过程可能跨越几屏的长度，当滚屏时，有助于看到END子句知道没有跳到下一个包过 

程中。 

完整的过程结构如下： 
CREATE OR REPLACE PROCEDURE print_temp 
IS 
    v_average NUMBER; 
    v_sum     NUMBER; 
BEGIN 
    SELECT AVG(n), SUM(n) INTO v_average, v_sum 
    FROM TEMP; 

    dbms_output.put_line('Average:'||v_average); 
    dbms_output.put_line('Sum:'||v_sum); 
END print_temp; 

二、函数 
函数提供了获取对象状态和情形信息的手段,函数有返回值。 
CREATE OR REPLACE FUNCTION student_status(optional parameters) 
                               RETURN VARCHAR2 IS 
        declarative part 
BEGIN 
        program body 
        RETURN expression; 
EXCEPTION 
        exception handler code 
        that should include a RETURN 
END student_student_status; 

参数是可选的，但RETURN语句是必须的。FUNCTION必须有一个return 语句。 
下面的例子是返回一个DATE类型的函数： 
CREATE OR REPLACE FUNCTION tomorrow RETURN DATE 
IS 
    next_day DATE; 
BEGIN 
    next_day := SYSDATE + 1; 
    RETURN next_day; 
END tomorrow; 

函数中的return语句可以是一个表达式，上面函数可以直接返回一个表达式而不用本地变量： 
FUNCTION tomorrow RETURN DATE IS 
BEGIN 
    RETURN SYSDATE + 1; 
END tomorrow; 
函数于过程结果相似，只是函数返回值，而过程不返回值 

三、包说明 
1、语法和风格 
包说明基本语法： 
CREATE PACKAGE package_name IS 
    Type definitions for records, index-by tables, 
        varrays, nested tables 
    Constants 
    Exceptions 
    Global variable declarations 
    PROCEDURE procedure_name_1 (parameters & types); 
    PROCEDURE procedure_name_2 (parameter & types); 
    FUNCTION function_name_1 (parameters & types) RETURN type; 
END package_name; 

包说明中过程和函数没有顺序限制。 
包主体将包含包说明中每一个子程序的PL/SQL代码。包说明中的每一个子程序包体中必须有相应的子程序体。 
在包说明中的数据对象声明是全局的。因此，仅声明需要全局定义的对象。 
在包体内的过程语句，包括子程序名、参数名、参数模式、参数类型，必须匹配包说明中的过程语句。同样，函数也是一样。 
包主体模板如下： 
CREATE PACKAGE BODY package_name IS 
    PROCEDURE procedure_name_1 (parameters & types) 
    IS 
        local variables 
    BEGIN 
        body of code 
    END procedure_name_1; 
    PROCEDURE procedure_name_2 (parameter & types) 
    IS 
        local variables 
    BEGIN 
        body_of_code 
    END procedure_name_2; 
    FUNCTION function_name_1 (parameters & types) RETURN type 
    IS 
        local variables 
    BEGIN 
        body of code 
        RETURN statement; 
    END function_name_1; 
END package_name; 

2、示例： 

CREATE OR REPLACE PACKAGE students_pkg IS 
PROCEDURE add_student 
    (v_student_name   IN students.student_name%TYPE, 
     v_college_major IN students.college_major%TYPE, 
     v_status         IN students.status%TYPE, 
     v_state          IN students.state%TYPE DEFAULT NULL, 
     v_license_no     IN students.license_no%TYPE DEFAULT NULL); 

FUNCTION NO_OF_STUDENTS 
    (v_major IN major_lookup.major_desc%TYPE DEFAULT NULL, 
     v_status IN students.status%TYPE DEFAULT NULL) 
RETURN NUMBER; 
END students_pkg; 

四、包主体 
下面是上例的包主体实现： 
CREATE OR REPLACE PACKAGE BODY students_pkg IS 
PROCEDURE add_student 
    (v_student_name   IN students.student_name%TYPE, 
     v_college_major IN students.college_major%TYPE, 
     v_status         IN students.status%TYPE, 
     v_state          IN students.state%TYPE DEFAULT NULL, 
     v_license_no     IN students.license_no%TYPE DEFAULT NULL) 
IS 
BEGIN 
      INSERT INTO students VALUES 
        ('A'||students_pk_seq.NEXTVAL, 
          v_student_name, 
          v_college_major, 
          v_status, 
          v_state, 
          v_license_no); 
END add_student; 

FUNCTION NO_OF_STUDENTS 
    (v_major IN major_lookup.major_desc%TYPE DEFAULT NULL, 
     v_status IN students.status%TYPE DEFAULT NULL) 
RETURN NUMBER 
IS 
    ccount INTEGER; 
BEGIN 
      SELECT COUNT (*) INTO ccount 
      FROM   students, major_lookup 
      WHERE students.college_major = major_lookup.major 
      AND    major_lookup.major_desc = 
                   nvl(v_major,major_lookup.major_desc) 
      AND   students.status = nvl(v_status,students.status); 
    RETURN ccount; 
END NO_OF_STUDENTS; 
END students_pkg; 

开发包主体可能需要其他本地过程和函数，这些是隐藏的，叫私有（过程或函数）。 
常常情况下要把一个单独的过程放入一个新包或一个已存在包里面。 
PACKAGE temp_operations IS 
    PROCEDURE insert_temp; 
END temp_operations; 

PACKAGE BODY temp_operations IS 
    PROCEDURE insert_temp IS 
    BEGIN 
        INSERT INTO temp (n) VALUES (0); 
    END insert_temp; 
END temp_operations; 

使用下面方式调用： 
temp_operations.insert_temp; 
temp_operations.insert_temp(); 


五、参数和模式 
PL/SQL程序有3种模式：IN (default),IN OUT,OUT 
(1)、IN 模式参数是一个常量 
IN模式参数是一个常量必须被看作常量。下面的过程将不能编译成功以为第3行是一个IN模式变量 
PROCEDURE print_next_value(v_data IN INTEGER) IS 
BEGIN 
      v_data := v_data+1; -- compile error 
      dbms_output.put_line(v_data); 
END; 
常量可以用在表达式中，下面的用法是正确的： 
PROCEDURE print_next_value(v_data IN INTEGER) IS 
BEGIN 
     dbms_output.put_line(v_data+1); 
END; 

(2)、IN OUT模式 
IN OUT模式的变量既可以在赋值语句的左边，也可以在赋值语句的右边。 
PROCEDURE change_data(v_data IN OUT INTEGER) IS 
BEGIN 
      for i in 1..10 loop 
          v_data := v_data + 1; 
      end loop; 
END; 

(3)、OUT模式 
在下例中，第4行之前，v_data变量是一个null，在使用OUT模式变量前必须先给他赋一个值： 
PROCEDURE provide_data(v_data OUT INTEGER) 
IS 
BEGIN 
      v_data := 100; 
      FOR i IN 1..10 LOOP 
          v_data := v_data +1; 
      END LOOP; 
END; 

(4)、参数默认值 
过程或函数说明可以为IN或IN OUT参数定义一个初始默认值。下面两种方法都是正确的： 
PROCEDURE name 
    (argument mode datatype := a_default_value); 

PROCEDURE name 
    (argument mode datatype DEFAULT a_default_value); 

下面函数定义了以默认半径为1返回圆的面积： 

FUNCTION circle 
    (radius IN NUMBER := 1) RETURN NUMBER IS 
BEGIN 
    RETURN 3.14 * radius**2; 
END; 

FUNCTION circle 
    (radius IN NUMBER DEFAULT 1) RETURN NUMBER IS 
BEGIN 
    RETURN 3.14 * radius**2; 
END; 

(5)、%TYPE 
%TYPE 的意思是变量声明类型和数据库表的指定字段类型一致。 
variable_name table_name.column_name%TYPE; 

CREATE OR REPLACE PROCEDURE get_professor_salary 
   (v_prof_name IN professors.prof_name%TYPE, 
    v_salary    OUT professors.salary%TYPE);