《存储过程的原理及实战应用》

一、存储过程概述

存储过程（Stored Procedure）是用PL/SQL语言编写的能完成特定功能的程序单元，编译后存储在数据库字典中。它由以下部分组成：

1. 声明部分（可选）
2. 可执行部分（必需）
3. 异常处理部分（可选）

二、存储过程的优点

1. 模块化：将复杂业务逻辑分解为可管理的模块
2. 可重用性：可以被多个应用程序调用
3. 可维护性：集中管理业务逻辑，简化维护
4. 安全性：通过权限控制保护数据安全
5. 性能优化：预编译执行，减少网络传输

三、存储过程的缺点

1. 调试相对困难
2. 移植性较差
3. 版本管理复杂
4. 过度使用可能导致数据库性能问题

四、存储过程适用场景

1. 复杂业务逻辑处理
2. 批量数据处理
3. 数据校验和转换
4. 定时任务处理
5. 需要高安全性的数据操作

五、存储过程语法

CREATE [OR REPLACE] PROCEDURE 
   <procedure_name> [(<parameter_list>)]
IS|AS 
   <local_variable_declaration>  -- 声明局部变量
BEGIN
   <executable_statements>       -- 执行体
[EXCEPTION
   <exception_handlers>]         -- 异常处理
END;

六、参数模式

1、IN模式（默认）

• 属于默认参数
• 输入模式的参数，用于接收参数，在子程序内部，不能进行修改。

示例：

--定义测试in模式的存储过程
CREATE OR REPLACE PROCEDURE pro(a in int,b in int)--参数的个数、类型可以自定义，但是【参数不允许指定长度】
AS
BEGIN
  dbms_output.put_line(a);
  dbms_output.put_line(b);
  --b:=11; --in模式参数不能为其赋值、补充：赋值是":="  而不是单个"="，单个"="是判断是否相等意思
END;

--通过语句块调用存储过程
BEGIN
  pro(10,20);
END;

输出：
10
20

2、OUT模式

• 输出模式的参数，用于输出值，会忽略传入的值。在子程序内部可以对其进行修改。
• 输出：子程序执行完毕后，out模式参数最终的值会赋值给调用时对应的<实参变量>。
• 注意：out模式参数的调用，必须通过变量

示例：

CREATE OR REPLACE PROCEDURE pro(c out int)
AS
BEGIN
  dbms_output.put_line(c);--c会忽略传入的值
  c:=30;--设定存储过程调用后的值
END;


DECLARE
var3 int :=100;--声明一个变量用于设定存储过程调用前的值
BEGIN
-- pro(100); --error,100对应过程中out模式的参数，out会输出结果给调用的实参，但是100不能作为赋值目标
dbms_output.put_line('存储过程调用前的值：'||var3);
pro(var3);--调用pro存储过程重新赋值；调用过程，如果过程形参是out模式，必须采用变量实参
dbms_output.put_line('存储过程调用后的值：'||var3);
END;

输出：
存储过程调用前的值：100
存储过程调用后的值：30

3、IN OUT

• 输入输出模式：能接收传入的实参值；在子程序内部可以修改； 可以输出（必须用实参变量调用）

示例：

--测试in out模式的存储过程
CREATE OR REPLACE PROCEDURE pro(d in out int)
AS
BEGIN
  dbms_output.put_line(d);
  d:=99;--in out模式参数的值可以修改
END;


DECLARE
age int :=40;--声明一个变量用于设定存储过程调用前的值
BEGIN
dbms_output.put_line('存储过程调用前的值：'||age);
pro(age);--调用pro存储过程重新赋值
dbms_output.put_line('存储过程调用后的值：'||age);
END;

存储过程调用前的值：40
存储过程调用后的值：99

4、不带参存储过程

create or replace procedure find_emp(emp_no number) 
as
  emp_name varchar2(20);
begin
  select ename into emp_name from emp where empno = emp_no;
  dbms_output.put_line('雇员姓名是:' || emp_name);
exception
  when no_data_found then
    dbms_output.put_line('雇员编号没有找到');
end find_emp;


begin
find_emp(7369);
end;

输出：雇员姓名是:SMITH

七、存储过程调试

1. 使用DBMS_OUTPUT.PUT_LINE输出调试信息
2. 使用异常处理捕获错误
3. 使用SQL*Plus或PL/SQL Developer等工具调试

八、性能优化建议

1. 避免大事务操作
2. 减少对大数据量表的重复访问
3. 谨慎使用游标
4. 合理使用临时表
5. 注意参数类型匹配
6. 控制DML操作的数据量
7. 使用批量操作代替逐行处理

九、实战示例

示例1：计算员工净收入

CREATE TABLE salary
( empno varchar2(10),
 workdays   NUMBER,
 salary     NUMBER
);
INSERT INTO salary VALUES ('E001', 21, 4000);
INSERT INTO salary VALUES ('E002', 19, 3000);
INSERT INTO salary VALUES ('E003', 20, 2500);
INSERT INTO salary VALUES ('E004', 18, 2000);
INSERT INTO salary VALUES ('E005', 15, 1800);
INSERT INTO salary VALUES ('E006',  7, 1500);
COMMIT;

create or replace procedure salary_proc
    (emp_no varchar2)
    is
      emp_sal number;
      netsal number;
    begin
     select salary into emp_sal from salary
     where empno=emp_no;
     netsal:=emp_sal-emp_sal*5/100;
    dbms_output.put_line('职员'||emp_no||'的净收入为'||netsal);
   end;
   
调用存储过程
begin 
  salary_proc('E001');
end;

输出：
职员E001的净收入为3800

示例2：批量插入数据

create table test(A int, B int);
create or replace procedure insert_proc
    ( start_num  in number,
      end_num  in number)
    as
     begin
      declare i  number;
         begin
            for i in start_num .. end_num loop
              insert into test values(i,i);
           end loop;
        end;
   end;
   
调用存储过程   
begin 
  insert_proc(1,10);
end;

查看输出
select * from test;
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9
10  10  10
ps：看到已插入10条数据

示例3：向学生表中插入数据

create or replace procedure insert_stud
    ( stu_no  学生表.学号%type,
      stu_name 学生表.姓名%type,
      stu_sex  学生表.性别%type,
      stu_age  学生表.年龄%type)
    as
     begin
       insert into 学生表(学号,姓名,性别,年龄)
       values(stu_no,stu_name,stu_sex,stu_age);
    end;
    
调用存储过程 
begin
  insert_stud('95007','陈红','女',21);
end;

查看输出
select * from 学生表;

示例4：更新学生成绩

insert into 成绩表 values('95001','1',80);
insert into 成绩表 values('95002','2',95);
commit;

create or replace procedure update_grade
    (stu_no  成绩表.学号%type,
     stu_cno 成绩表.课程号%type,
     stu_grade 成绩表.成绩%type)
    as
      begin
        update 成绩表
         set 成绩=stu_grade
         where 学号=stu_no and 课程号=stu_cno;
       if SQL%found then
         DBMS_output.put_line('该学生成绩已更新');
       else
        DBMS_output.put_line('该学生课程不存在');
       end if;
    end;

调用存储过程 
begin
  update_grade('95001','1',90);
end;

输出：
该学生成绩已更新

示例5：调整员工薪资并输出

select * from emp;
1 7369  SMITH CLERK 7902  2018-12-17  2200    20
2 7499  ALLEN SALESMAN  7698  2018-2-20 2000  1000  30
3 7521  WARD  SALESMAN  7698  2018-2-22 1450  1000  30
4 7566  JONES MANAGER 7839  2018-4-2  2975    20
5 7654  MARTIN  SALESMAN  7698  2018-9-28 1450  1000  30
6 7698  BLAKE SALESMAN  7839  2018-5-1  2850  1000  30
7 7782  CLARK MANAGER 7839  2018-6-9  2450    20
8 7788  SCOTT ANALYST 7566  1987-4-19 3000    20
9 7839  KING  PRESIDENT   2018-11-17  5000    10
10  7844  TURNER  SALESMAN  7698  2018-9-8  1700  1000  30
11  7876  ADAMS CLERK 7788  1987-5-23 1300    20
12  7900  JAMES CLERK 7698  2018-12-3 1150  1000  30
13  7902  FORD  ANALYST 7566  2018-12-3 3000    20
14  7934  MILLER  CLERK 7782  1982-1-23 1500    10
15  -10 Not found!  

create or replace procedure mytest_proc
  (value1 in number,value2 out number)
  is
  salary number;
  begin 
    select sal into salary from emp
     where empno=value1;
  if salary<2000 then
     value2:=salary+500;
     update emp
        set sal=value2
      where empno=value1;
  else
     value2:=salary;
  end if;
  end;

调用存储过程
declare
  v1 number:=7369;
  v2 number;
  begin
  mytest_proc(v1,v2);
  dbms_output.put_line('V2的值的为' || to_char(v2));
  end;
  
输出：
V2的值的为2200

十、权限管理

1. 授予执行权限

GRANT EXECUTE ON procedure_name TO user_name;

1. 删除存储过程

DROP PROCEDURE procedure_name;

十一、注意事项

1. 避免过度使用存储过程
2. 注意事务的合理划分
3. 做好异常处理
4. 定期优化和重构
5. 做好版本控制
6. 编写清晰的注释