Oracle数据库数据编程SQL＜3.5 PL/SQL 存储过程(Procedure)＞

存储过程(Stored Procedure)是 Oracle 数据库中一组预编译的 PL/SQL 语句集合，存储在数据库中并可通过名称调用执行。它们是企业级数据库应用开发的核心组件。

目录

一、存储过程基础

1. 存储过程特点

2. 创建基本语法 

3. 存储过程优点

4. 简单示例

二、没有参数的存储过程

1. 简单示例

三、有参数的存储过程

 1. 参数模式

2. 有输入值 IN

3. 有输出值 OUT

4. 有输入输出值IN OUT

（1）编译：

（2）调用​编辑

（3）宏&输入名称​编辑

（4）查看输出结果

四、存储过程的调用总结

五、存储过程中的DML操作

1. 基本DML示例

2. 使用RETURNING子句

六、异常处理

1. 预定义异常

2. 自定义异常

七、游标处理

1. 显式游标

2. REF游标(动态游标)

八、高级特性

1. 自治事务

2. 批量处理(FORALL)

3. 条件编译

九、存储过程管理

1. 查看存储过程

右键查看

2. 重新编译

右键重新编译

右键编辑--执行

3. 权限控制

4. 删除存储过程

十、最佳实践

十一、存储过程和函数的区别

---

一、存储过程基础

1. 存储过程特点

• 预编译执行：提高性能，减少解析开销

• 模块化设计：促进代码重用和维护

• 增强安全性：通过权限控制保护数据

• 减少网络流量：客户端只需调用过程名而非发送多句SQL

• 事务控制：可在过程中管理完整事务

2. 创建基本语法 

CREATE [OR REPLACE] PROCEDURE procedure_name[(parameter1 [IN|OUT|IN OUT] datatype [DEFAULT|:= value],parameter2 [IN|OUT|IN OUT] datatype [DEFAULT|:= value],...)]
[IS|AS][declaration_section]
BEGINexecutable_section
[EXCEPTIONexception_section]
END [procedure_name];
/create {or replace} procedure pro_name(v1 in/out/in out 类型)
as/is
{声明变量}
begin
要执行的语句;
end;
/（1）创建 creat or replace procedure
（2）声明变量
（3）开始 begin
（4）DML操作
（5）异常处理 exception
（6）结束 end

3. 存储过程优点

（1）存储过程只在创建时进行编译，以后每次执行都不需要重新编译，而一般的SQL语句每执行一次就编译一次，所以使用存储过程可以提高数据库的执行速度。

（2）当对数据库进行复杂操作时（比如对多个表进行查询、修改操作），可以将此复杂的事务处理结合一起使用这些操作。如果用SQL需要多次连接数据库，如果用存储过程，只需要连接一次数据库。

（3）存储过程可以重复使用，可以减少数据库开发人员的工作量。

4. 简单示例

CREATE OR REPLACE PROCEDURE update_employee_salary(       --创建--存过主题结构
---------------------------------------------------------------------------------p_emp_id IN employees.employee_id%TYPE,p_percent IN NUMBER DEFAULT 10
) ASv_old_salary employees.salary%TYPE;                   --声明变量--存过主题结构
---------------------------------------------------------------------------------
BEGIN                                                     --开始--存过主题结构
----------------------------------------------------------------------------------- 获取当前薪资SELECT salary INTO v_old_salaryFROM employeesWHERE employee_id = p_emp_id;-- 更新薪资UPDATE employeesSET salary = salary * (1 + p_percent/100)WHERE employee_id = p_emp_id;-- 输出结果DBMS_OUTPUT.PUT_LINE('员工ID ' || p_emp_id || ' 薪资从 ' || v_old_salary || ' 调整为 ' || (v_old_salary * (1 + p_percent/100)));COMMIT;                                               --DML操作--存过主题结构
---------------------------------------------------------------------------------
EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('错误: 未找到员工ID ' || p_emp_id);ROLLBACK;WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);ROLLBACK;                                         --异常处理--存过主题结构
---------------------------------------------------------------------------------
END update_employee_salary;                               --结束--存过主题结构
---------------------------------------------------------------------------------
/

二、没有参数的存储过程

1. 简单示例

--编写一个存储过程,将emp表中和编号7788相同部门的员工信息插入到
--emp3中，将工作为CLERK的工资加300后插入到emp4中。
-- 创建
create or replace procedure pro2 as   --创建--存过主题结构
begin                                 --开始--存过主题结构
--------------------------------------------------------------------DML操作insert into emp3select *from empwhere deptno = (select deptno from emp where empno = 7788);--DML操作insert into emp4select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal + 300,e.comm,e.deptnofrom emp ewhere job = 'CLERK';
------------------------------------------------------------------
end;                                   --结束--存过主题结构
/
-- 调用：
call pro2();
-- 查询、验证
select * from emp3;
select * from emp4;

三、有参数的存储过程

 1. 参数模式

模式	描述	示例
IN	只读参数(默认)	p_id IN NUMBER
OUT	只写参数，返回给调用者	p_result OUT VARCHAR2
IN OUT	可读写参数	p_counter IN OUT NUMBER

2. 有输入值 IN

--输入员工编号，输出姓名和薪资。
-- 创建
create or replace procedure pro1(v_empno number) asv_name varchar2(20);v_sal  emp.sal%type;
beginselect ename, sal into v_name, v_sal from emp where empno = v_empno;dbms_output.put_line(v_name || v_sal);
end;
-- 调用：
call pro1(7788);【调用方法】
在sql窗口  call pro_name(参数); --sql窗口括号不能省
在命令窗口 exec pro_name(参数);/*===============================================================================*/
【练习1】
--创建一张emp3数据同emp
--更改emp3的sal列的长度为number(20,2)
--编写一个存储过程
--输入一个数字和一个部门编号
--要求数字是0-9的整数（如果不是，抛出异常，并打印'请输入0-9的整数'）
--当部门人数小于该数字，将该部门的员工信息插入到emp1--显示插入了多少行
--当部门人数大于该数字,将该部门的员工姓名，编号删除--并显示删除了多少人
--当部门人数等于该数字，不该部门的全部员工工资变成原工资的二次方--并显示增加了多少人的工资
-- 准备：
CREAT TABLE EMP3 AS SELECT * FORM EMP;
ALTER TABLE EMP3 MODIFY SAL NUMBER(20,2);
-- 创建：
CREATE OR REPLACE PROCEDURE PRO_3(V1 NUMBER,V_DEPTNO NUMBER) AS
ERR EXCEPTION;
V3 NUMBER;
BEGINIF v1 NOT IN (0,1,2,3,4,5,6,7,8,9)THENRAISE ERR;END IF;