oracle PL/SQL（procedure language/SQL）程序设计之函数+过程+包（转）

匿名PL/SQL块回顾

DECLARE (可选)     定义在PL/SQL块中要使用的对象 BEGIN (必须)     执行语句 EXCEPTION (可选)     错误处理语句 END; (必须) 匿名块（以DECLARE或BEGIN开始）每次使用时都要进行编译。 匿名块不存储在数据库中，并且它不能从其它的PL/SQL块中进行调用。

 

---

 

过程、函数、包和触发器：都是带名块。（请注重同匿名块的比较） 过程、函数、包和触发器：可以存储在数据库中，并且可以在需要的时候运行。 过程、函数、包和触发器：可以从其它PL/SQL块中进行调用。 子程序：过程、函数

子程序是带名的PL/SQL块，能够接受参数和被环境调用。在PL/SQL中两类子程序：过程和函数

子程序说明（header）决定 : PL/SQL子程序的类型是过程还是函数。 子程序的名称 参数列表 当子程序是函数时必须要有返回值(使用RETURN子句) 关键字IS或AS是必须的。 子程序体:是一个拥有声明、执行和异常处理部分的PL/SQL块。 声明部分介于IS|AS和BEGIN之间。在匿名块中必须使用DECLARE关键字显示的指出声明部分，而在子程序中没有DECLARE关键字。 执行部分介于BEGIN和END关键字之间，这部分必须存在。 异常处理部分介于EXCEPTION和 END之间是可选的部分

---

创建过程（语法）

Example  创建存储过程，输出系统的日期和时间 CREATE OR REPLACE PROCEDURE display_time IS BEGIN    dbms_output.put_line(systimestamp); END display_time; 使用三种方式调用上面创建的存储过程 方式一：使用sqlplus命令EXECUTE(简写EXEC) 调用     EXECUTE display_time; 方式二：使用sql命令CALL调用     CALL display_time( ); 方式三：在PL/SQL块中调用     BEGIN         display_time;     END; 参数和模式

形式参数(Formal parameters):在过程中声明的参数就是形式参数（p_FirstName, p_LastName, p_Major ）。

CREATE OR REPLACE PROCEDURE insert_emp  [形式参数] (no  emp.empno%TYPE,            name  emp.ename%TYPE, Job  emp.job%TYPE,                mgr  emp.mgr%TYPE, hiredate emp.hiredate%TYPE ,    salary emp.sal%TYPE , comm emp.comm%TYPE ,        deptno emp.deptno%TYPE ) IS BEGIN 　　INSERT INTO emp VALUES(no,name,job,mgr,hiredate,salary,comm,deptno); END; 调用上述储存过程[匿名块来调用储存过程（储存过程是带名块。）]

DECLARE v_no  emp.empno%TYPE:=10000, v_name  emp.ename%TYPE :=‘Jones’, v_job  emp.job%TYPE := 'SALESMAN', v_mgr  emp.mgr%TYPE :=7369, v_hiredate emp.hiredate%TYPE :=SYSDATE, v_salary emp.sal%TYPE := 800, v_comm emp.comm%TYPE :=NULL, v_deptno emp.deptno%TYPE :=10 BEGIN insert_emp(v_no, v_name, v_job, v_mgr, v_hiredate, v_salary, v_comm, v_deptno ); END;

参数和模式 过程可以通过参数和调用环境进行数据传递。 有三种传递模式—IN、OUT和IN OUT。如果没有为形式参数指定模式，则使用缺省模式IN。

对形式参数的约束：在过程声明中，限制CHAR和VARCHAR2参数的长度以及限制NUMBER参数的精度和/或刻度范围都是非法的。 CREATE OR REPLACE PROCEDURE ParameterLength (   p_Parameter1 IN OUT VARCHAR2(10),   p_Parameter2 IN OUT NUMBER(3,2)) AS BEGIN   p_Parameter1 := 'abcdefghijklm';   p_Parameter2 := 12.3; END ParameterLength; / 过程声明非法

---

参数和模式--参数的缺省值

CREATE OR REPLACE PROCEDURE add_dept   (p_name IN dept.dname%TYPE DEFAULT 'unknown',    p_loc IN dept.loc%TYPE  DEFAULT 'NEW YORK') IS BEGIN    INSERT INTO dept (deptno,dname, loc)    VALUES (dept_seq.NEXTVAL, p_name, p_loc); END add_dept; 执行：(用匿名块来执行 带名块)

SQL>BEGIN                add_dept;                add_dept ('TRAINING');                add_dept ( p_loc =>'BOSTON ', p_name => 'EDUCATION');                add_dept ( p_loc => 'CHICAGO') ;                END;          SQL>SELECT * FROM dept; 删除过程  DROP PROCEDURE raise_salary;

---

创建函数

CREATE [OR REPLACE] FUNCTION function_name [(argument1 [IN | OUT | IN OUT] data_type , argument2 [IN | OUT | IN OUT] data­_type,…)] RETURN data_type IS |AS    [declaration_section;] BEGIN    executable_section;    RETURN expression; [EXCEPTION    exception_handlers; RETURN expression;] END [function_name]; 相关说明

OR REPLACE关键字指出如果过程存在，首先删除它，然后用新版本创建。 Parameter、mode和datatype的含义与过程相同,但是应该尽量避免使用OUT和IN OUT参数模式。 return_datatype是函数返回的数值的类型，不能对数据类型的尺寸进行限定。 PL/SQL块以BEGIN开始或以局部变量声明开始，以END或END function_name结束。在PL/SQL块中，可以使用多个RETURN语句，但是必须保证至少有一条RETURN语句存在。函数每一次运行只有一个RETURN语句被执行。

举例：。。。。。。。。。。。。。。。。。。。。。。 CREATE OR REPLACE FUNCTION get_sal       (p_id IN emp.empno%TYPE) RETURN NUMBER IS       v_salary emp.sal%TYPE :=0; BEGIN      SELECT sal     INTO v_salary     FROM emp     WHERE empno = p_id;      RETURN v_salary; END get_sal;

执行 函数 注意：因为函数具有返回值，所以调用函数是作为一个表达式的一部分，而不能像调用过程那样作为一个独立的语句使用。 方式一：使用变量接收返回值 VAR salary NUMBER; EXEC :salary:=get_sal(7369); PRINT salary;方式二：在SQL语句中直接调用函数 SELECT get_sal(7369) FROM DUAL; 方式三：使用DBMS_OUTPUT调用函数 SET SERVEROUTPUT ON EXEC dbms_output.put_line('工资是:'|| get_sal(7369));
删除函数 DROP FUNCTION get_sal;

---

创建包

包是一个可以将相关对象存储在一起的PL/SQL结构，是Oracle数据库的一种模式对象。包有两个独立的部分—包的规范和主体，这两部分独立的存储在数据字典中。 包所包含的程序对象是过程、函数、变量、常数、游标和异常。

包比独立的过程和函数有更多的优点： 包使组织应用开发更有效。 包使授权更有效。 包允许修改包的对象而不需要重新编译从属的对象。 包允许ORACLE一次读入多个包对象到内存。 包可包含全局变量和游标，可为包中的全部过程和函数使用。 包允许重载过程和函数。

---

创建包

建立一个包，要执行两个步骤： （1）用CREATE PACKAGE命令建立包规范。在包规范中说明程序对象，这些对象称为公共对象。公共对象可以在包外引用，也可以在包中的其它对象引用。 （2）用CREATE PACKAGE BODY命令建立包体，在包体中说明和定义程序对象： 定义在包规范中说明的公共对象； 定义附加的包对象（私用对象），由于私用对象是在包体中说明，它不能在包外引用。 CREATE [OR REPLACE] PACKAGE package_name IS|AS     public type and item declarations     subprogram specifications END package_name; specification

OR REPLACE：如果包规范存在则删除并重新创建包的规范。 package_name：包的名字 public type and item declarations ：声明公共的变量、常量、游标、异常和数据类型。 subprogram specifications：声明PL/SQL子程序。 在包规范中声明的变量，默认的初始化值是NULL。

创建包的规范
CREATE OR REPLACE PACKAGE comm_package IS      g_comm NUMBER := 0; --initialized to 0      PROCEDURE reset_comm  (p_comm IN NUMBER); END comm_package; G_COMM 是一个全局变量，初始化值是0.10。 RESET_COMM 是一个公共的过程，它在包体中定义其执行代码。 创建包体 CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS        private type and item declarations        subprogram bodies END package_name; OR REPLACE：如果包体存在则删除并重新创建包体。 package_name：包的名字。 private type and item declarations：声明私有的变量、常量、游标、异常和数据类型。 subprogram bodies ：定义公有和私有的PL/SQL子程序。 创建包体

CREATE OR REPLACE PACKAGE BODY comm_package  IS    FUNCTION validate_comm (p_comm IN NUMBER)  RETURN BOOLEAN  （包中创建函数）    IS        v_max_comm NUMBER;    BEGIN        SELECT MAX(comm)  INTO v_max_comm  FROM emp;        IF p_comm > v_max_comm THEN RETURN(FALSE);        ELSE RETURN(TRUE);        END IF;    END validate_comm;    PROCEDURE reset_comm (p_comm IN NUMBER)   IS （包中创建储存过程）    BEGIN         IF validate_comm(p_comm)  THEN  g_comm:=p_comm; --reset global variable         ELSE  RAISE_APPLICATION_ERROR(-20210,'Invalid commission');         END IF;    END reset_comm; END comm_package; 执行

---

创建一个没有包体包 CREATE OR REPLACE PACKAGE global_consts IS      mile_2_kilo CONSTANT NUMBER := 1.6093;      kilo_2_mile CONSTANT NUMBER := 0.6214;      yard_2_meter CONSTANT NUMBER := 0.9144;      meter_2_yard CONSTANT NUMBER := 1.0936; END global_consts; 执行 SQL>EXECUTE DBMS_OUTPUT.PUT_LINE('20 miles = '||20*global_consts.mile_2_kilo|| ' km')

http://www.cnblogs.com/pacoson/p/3523467.html