存储过程初级篇

本文旨在把自己学到的有关存储过程的知识和大家分享，并希望能够帮助正在被存储过程折磨的同学。

什么是存储过程

官方定义：
A procedure is a subprogram that performs a specific action

A PL/SQL subprogram is a named PL/SQL block that can be invoked repeatedly. If the subprogram has parameters, their values can differ for each invocation.

A subprogram is either a procedure or a function. Typically, you use a procedure to perform an action and a function to compute and return a value.

翻译：
存储过程是一个可以执行特定行为的子程序

PL/SQL子程序是一个可以被重复调用的拥有名称的PL/SQL块（PL/SQL程序都是以块为基本单位的）。如果这个子程序有参数，我们则可以在调用的时候赋予不同的参数

子程序可以是存储过程或函数。通常情况下，你可以使用存储过程来执行一个动作，使用函数来计算并返回一个值

总结：存储过程对于不同的领域的人有不同的理解方式。可以把它理解为一个指令集，它可以帮助我们完成一系列复杂的数据操作，也可以把它看做一个专门处理SQL的批处理工具，在需要的时候执行一些增删改查的操作。

为什么学习存储过程

相信在学习存储过程的你一定已经对普通SQL了如指掌了，简单了解了存储过程的概念之后，用普通的SQL与存储过程进行比较可以让我们在工作中做出正确的选择。两者区别很大，详细的对比可以问度娘，由于本文主讲存储过程，故罗列一些重要存储过程的优点如下：

• 降低网络的通讯量。如果只是执行简单的SQL语句的话存储过程和普通SQL没有太大差别，但随着时间的推移SQL量越来越大甚至达到上百行时，其优越性明显体现
• 提高执行效率。我们都知道SQL是先编译再执行的，而存储过程是预编译在服务器中的，当执行的时候跳过编译的环节效率自然会提高
• 可维护性高。更新存储过程通常比更新，测试，重新部署需要较少的时间和精力

总之存储过程是一个SQL提供的一个非常优秀的功能，在工作中我们或多或少都会用到，学会存储过程绝对会让你受益匪浅。

PL/SQL存储过程

基本语法

存储过程在不同的数据库语言中语法略有不同，本文针对ORACLE的PL/SQL，但其他数据库语言也是大同小异

上图是一个存储过程的模型，由此可知一个最简单的存储过程必须包含以下关键字CREATE、PROCEDURE、存储过程名称、IS/AS、PL/SQLB标准执行语句（BEGIN ... END;）。
通过不断的实践我们将会完全理解上图的含义。首先从简单的存储过程开始(其功能是向EMP表中添加一条数据)：

create        -- 存储过程头部区域开始 
or replace    --可选表示如果数据库中已经存在一条相同名称的存储过程就把它替换掉
procedure
proc_emp_create --存储过程名称 procedure_name
(
empno number, ename varchar2, job varchar2, mgr number, hiredate date, sal number, comm number, deptno number
) -- parameter_declaration 声明参数（注意不需要写长度），存储过程头部区域结束
as
--声明区域，不需要声明变量可以不写
begin -- PL/SQL标准执行语句
--执行区域
  insert into emp values(empno, ename, job, mgr, hiredate, sal, comm, deptno);
end;

当我们执行上面的存储过程之后这条存储过程就被编译到数据库中了，进入PLSQL Developer中的Procedures文件夹就可以看到我们刚才创建的存储过程了

既然存储过程已经写好并编译完成了，接下来就是使用我们创建的存储过程了。使用存储过程有两种方法：
第一种是直接在SQL窗口中，执行SQL语句。

begin
  proc_emp_create(7778,'Alexander','analyst',6789,sysdate,4321,'',20);
end;

第二种是在命令窗口中使用execute命令

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as scott
 
SQL> execute   proc_emp_create(7778,'Alexander','analyst',6789,sysdate,4321,'',20);
 
PL/SQL procedure successfully completed
 
SQL> 

无论使用哪一种方法，我们都会发现在EMP表中已经多了一条刚才插入的数据。也就是说，我们以后想要向EMP表中插入数据的话，就可以直接调用这条存储过程来执行插入操作。

从上面的例子可以看出，一个存储过程可以分成三个区域

1. 头部区域
   用于编写最基本的存储过程头部标记，定义是否要创建一个替代原有存储过程的存储过程；决定是否定义参数；定义参数的类型（in out inout）；定义执行权限（Schema）。
2. 声明区域
   用于声明变量（要定义长度）包括cursor；
3. 执行区域
   用于执行业务逻辑代码，可以使用条件语句（选择、判断、循环。。。）来进行一些业务逻辑CRUD的处理

继续实践

了解了存储过程的基本语法和用途之后，再通过一些简单的实例可以帮助我们更好的理解存储过程的语法和其含义。上面的例子简单的完成了对EMP表的添加功能，接下来将会使用存储过程对EMP进行删除、修改和查询功能，实现完整的CRUD
D
创建用于根据EMPNO删除EMP中一条数据的存储过程

create or replace procedure proc_emp_delete(deleteid number) as
begin 
  delete from emp where empno = deleteid;
end;

执行（两种方法任选其一即可）

begin
  proc_emp_delete(7778);
end;

执行完成之后，刚才我们在了解存储过程语法的那条数据就被删除了
U
接下来来写更新的存储过程，首先准备一条数据

begin 
proc_emp_create(7778,'Alexander','analyst',6789,sysdate,4321,'',20);
end;

执行之后刚才被我们删掉的数据就又重新插入到EMP表中，接下来写更新的存储过程

create or replace procedure
proc_emp_update 
(
p_empno number, p_ename varchar2, p_job varchar2, p_mgr number, p_hiredate date, p_sal number, p_comm number, p_deptno number
) 
as
begin
  update emp set ename = p_ename,job = p_job,mgr = p_mgr,hiredate=p_hiredate,sal = p_sal,comm=p_comm,deptno = p_deptno where empno=p_empno;
end;

需要注意的是参数不能和字段表的名称相同，接下来执行

begin
  proc_emp_update(7778,'Alexander','analyst','6789',sysdate,4321,3000,10);
end;

再次查询会发现Alexander的奖金多了3000块。
R
最后要做的就是查询了，对于多条数据的查询输出要使用cursor，留在下篇文章中讨论，我们先做一个最简单的单条数据查询

create or replace procedure proc_emp_read
as 
v_no number;--声明变量
begin 
  select count(empno) into v_no from emp;--在执行代码块里面查询一定要使用into赋值
  dbms_output.put_line(v_no);
end;

接下来执行

begin
  proc_emp_read;
end;

我的结果如下图所示：

至此，简单的CRUD实践就完成了，相信你已经对存储过程有了大概的理解并能写出简单存储过程了。当然这只是存储过程最基本的使用方法，其高级特性（cursor、schema、控制语句、事务等）将在下篇文章中讨论。