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
