一、子程序
子程序是已命名的PL/SQL块，它们存储在数据库中，可以Wie它们指定参数，可以从任何数据库客户端和应用程序中调用它们。子程序包括存储过程和函数。
子程序包括：
1、声明部分：声明部分包括类型、游标、常量、变量、异常和嵌套子程序的声明。这些项都是局部的，在退出后就不复存在。
2、可执行部分：可执行部分包括赋值、控制执行过程以及操纵ORacle数据的语句。
3、异常处理部分： 异常处理部分包括异常处理程序，负责处理执行存储过程中出现的异常。
子程序的有点：
1、模块化：通过子程序，可以将程序分解为可管理的、明确的逻辑模块。
2、可重用性：子程序在创建并执行后，就可以再任意数目的应用程序中使用。
3、可维护性：子程序可以简化维护操作，因为如果一个子程序受到影响，则只需修改该子程序的定义。
4、安全性：用户可以设置权限，使得访问数据的唯一方式就是通过用户提供的存储过程和函数。不仅可以让数据更安全，而且可以保证它的正确性。
二、存储过程
存储过程是执行某些操作的子程序，是执行特定任务的模块。从根本上讲，存储过程就是明明的PLSQL块，它可以被赋予参数，存储在数据库中，然后由一个应用程序或其他PLSQL程序调用。
1、创建存储过程:
语法：
CREATE [OR REPLACE] PROCEDURE procedure_name
[(paraameter_list)]
{IS/AS}
[local_declarations]
BEGIN
executable_statements;
[EXCEPTION]
[exception_handlers]
END [procedure_name]
说明：
procedure_name：为存储过程的名字；
paraameter_list：参数列表，参数中可以使用in和out表示输入和输出参数。可选
AS表示其他变量声明，IS表示显示游标声明
local_declarations：局部声明，可选
executable_statements：可执行语句
exception_handlers：异常处理语句，可选
OR REPLACE：可选。如果不包含，创建存储过程如果存在会报错，包含存在会替换。
例：添加员工信息
--添加员工信息 create or replace procedure add_emp( eno number, ename varchar2, job varchar2, mgr NUMBER, salary number, hiredate DATE, com NUMBER, dno number) is BEGIN dbms_output.put_line('添加员工信息'); INSERT INTO emp VALUES(eno,ename,job,mgr,hiredate,salary,com,dno); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('添加员工失败'); end add_emp;
注意：
存储过程中只声明类型，不指定长度。
AS后的变量声明以;结束。
2、调用存储过程：
语法：
exec[ute] procedure_name (parameters_list);
说明：
execute：执行命令，可以缩写为exec。
procedure_name：存储过程的名称。
parameters_list：存储过程的参数列表。c
调用存储过程有两种方式，命令行方式和PL/SQL方式。
1）命令行方式：打开命令行直接输入存储过程名称进行调用。
EXEC ADD_EMP(8888,'zhangsan','clerk',7902,2000,SYSDATE,500,30);
2）PLSQL方式：必须在pl/sql块中调用存储过程，不使用EXEC关键字，直接存储过程名称即可。
BEGIN --PL/SQL方式，不需要使用exec add_emp(8989,'LISI','clerk',7902,1000,SYSDATE,NULL,30); END;
参数的传递方式：
1）按位置传递：
按照参数的书序，依次写入参数内容。调用的参数顺序和定义的参数顺序必须一一对应。
EXEC ADD_EMP(8888,'zhangsan','clerk',7902,2000,SYSDATE,500,30);
2）按名称传递：
按名称调用时按名称对应，名称对应的关系最重要，次序不重要。
EXEC add_emp(ENO => 8989,ENAME => 'LISI',JOB => 'clerk',MGR => 7902,SALARY => 1000,HIREDATE => SYSDATE,COM => NULL,DNO => 30);
3）混合方式传递：
同时使用位置传递和参数传递，采用这种方式必须将位置参数放在名称参数的前面，只要第一个采用了名称传递法，后面所有的参数必须使用名称传递法。
EXEC add_emp(8989,'LISI',JOB => 'clerk',MGR => 7902,SALARY => 1000,HIREDATE => SYSDATE,COM => NULL,DNO => 30);
3、存储过程的参数模式：
存储过程参数有三种模式：IN、OUT和IN OUT，即输入、输出、输入/输出。
定义存储过程参数语法：
parameter_name [IN|OUT|IN OUT] dateType [{:= | default} expression]
注意：
1）参数IN模式是默认模式。如果未指定参数模式，则默认为IN。对于OUT和IN OUT参数，必须明确指定。
2）可以再参数列表中为IN参数指定默认值，OUT和IN OUT不可用。
带OUT参数的存储过程：
例：根据empno查询员工信息返回。
CREATE OR REPLACE PROCEDURE QUERY_EMP( V_EMPNO IN EMP.EMPNO%TYPE, --输入参数 V_ENAME OUT EMP.ENAME%TYPE, --输出参数 V_SAL OUT EMP.SAL%TYPE) IS --输出参数 BEGIN SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE empno = v_empno; dbms_output.put_line('数据已查到'); END QUERY_EMP;
调用带输出参数的存储过程，首先定义两个变量保存输出参数返回的内容。
DECLARE v_name emp.ename%TYPE; v_sal emp.sal%TYPE; BEGIN query_emp(7788,v_name,v_sal); dbms_output.put_line('name:'||v_name||' sal:'||v_sal); END;
带IN OUT参数的存储过程：
数据交换;
CREATE OR REPLACE PROCEDURE swap( v_num1 IN OUT NUMBER, v_num2 IN OUT NUMBER ) AS v_temp NUMBER; BEGIN v_temp := v_num1; v_num1 := v_num2; v_num2 := v_temp; END;
--调用 DECLARE v_n1 NUMBER := 10; v_n2 NUMBER := 20; BEGIN swap(v_n1,v_n2); dbms_output.put_line(v_n1||' '||v_n2); END;
4、存储过程的访问权限
存储过程创建后，只有创建该存储过程的用户和管理员才能有权执行。其他用户如果要调用该存储过程，需要得到存储过程的EXECUTE权限。
例：
--将swap的执行权限授予user1 GRANT EXECUTE ON swap TO user1; --将swap的执行权限授予user1，并且user1可以对其他用户进行授权。 GRANT EXECUTE ON swap TO user1 WITH GRANT OPTION; --撤销user1用户的执行swap权限。 REVOKE EXECUTE ON swap FROM user1
5、删除存储过程
DROP procedure swap;
三、总结：
优点：
1、存储过程增强了PL-SQL的功能和灵活性。
2、存储过程保证了数据的完整性和安全性。
3、提高了sql语句的性能。
4、降低了网络的通讯量。
缺点：
1、移植问题，不同的数据库存储过程语法不同。
2、重新编译问题。更改有依赖的存储过程也会重新编译。
3、需求变更的问题。