匿名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')
