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;SELECT COUNT(*) INTO V3 FROM EMP3 WHERE DEPTNO=V_DEPTNO;IF V3<V1 THEN INSERT INTO EMP1 SELECT * FROM EMP3 WHERE DEPTNO=V_DEPTNO;dbms_output.put_line('插入了'||sql%rowcount||'行');ELSIF v3>v1 THEN UPDATE emp3 SET ename=NULL,empno=null WHERE deptno=v_deptno;dbms_output.put_line('删了'||sql%rowcount||'人');ELSE UPDATE emp3 SET sal=POWER(sal,2) WHERE deptno=v_deptno;dbms_output.put_line(sql%rowcount||'人的工资增加了');END IF; 
EXCEPTION WHEN ERR THEN DBMS_output.put_line('请输入0-9的整数');END;
-- 调用：
CALL pro_3(2,10)
-- 验证：
SELECT * FROM emp3/*===============================================================================*/
【练习2】
--新建一张表emp2和emp数据相同。
--编写一个存储过程，输入一个数字和一个部门编号。
--要求数字是0-9的整数
--当该部门人数小于该数字将该部门员工信息插入到emp3中；
--当该部门人数大于该数字将该部门的员工姓名编号删除；
--当该部门人数等于该数字则该部门全部员工工资加666。
-- 创建
create or replace procedure pro4(v_num number, v_deptno number) as exception;
beginselect count(*) into v_count from emp where deptno = v_deptno;if v_num not in (0, 1, 2, 3, 4, 5, 6, 7, 8, 9) thenraise err;elsif v_count < v_num theninsert into emp3select * from emp2 where deptno = v_deptno;dbms_output.put_line(v_deptno||'部门人数' || v_count || '<' || v_num);elsif v_count > v_num thenupdate emp2 set ename = null, empno = null where deptno = v_deptno;dbms_output.put_line(v_deptno||'部门人数' || v_count || '>' || v_num);elseupdate emp2 set sal = sal + 666 where deptno = v_deptno;dbms_output.put_line(v_deptno||'部门人数' || v_count || '=' || v_num);end if;
exceptionwhen err thendbms_output.put_line('请输入0-9的整数');raise;
end;
-- 调用：
call pro4(3,10);

3. 有输出值 OUT

/*===============================================================================*/
【练习1】
--输入：姓名
--输出：如果工资比MILLER高，输出高，比MILLER低输出低，一样的话巧了
-- 创建
create or replace procedure pro3(v1 varchar2, v2 out varchar2) asv_3 number;v_4 number;
beginselect sal into v_sal_m from emp where ename = 'MILLER';select sal into v_sal_t from emp where ename = v1;if v_sal_t > v_sal_m thenv2:='高';elsif v_sal_t < v_sal_m thenv2:='低';else v2:='巧了';end if;
end;
/
-- 调用：
declarev2 varchar2(4);
beginpro3(&a, v2);dbms_output.put_line(v2);
end;
/
/*===============================================================================*/
【练习2】
--输入：姓名
--输出： 如果部门和MILLER一样，输出一样，不一样输出不一样
-- 创建
create or replace procedure pro4(v1 varchar2, v2 out varchar2) asv_deptno_m number;v_deptno   number;
beginselect deptno into v_deptno_m from emp where ename = 'MILLER';select deptno into v_deptno from emp where ename = v1;if v_deptno = v_deptno_m thenv2 := '一样';elsev2 := '不一样';end if;dbms_output.put_line(v2);
end;
/
-- 调用：
declarev2 varchar2(6);
beginpro4('&a', v2);
end;
/
/*===============================================================================*/
【练习3】存过名称不建议使用中文
--输入：姓名
--输出：如果部门和MILLER一样，输出一样，不一样输出不一样
-- 创建
create or replace procedure 判断是否和MILLER信息一样(v1 varchar2, v2 out varchar2) asv_d_m number;v_d_t number;
beginselect deptno into v_d_m from emp where ename = 'MILLER';select deptno into v_d_t from emp where ename = v1;if v_d_t = v_d_m thenv2 := '一样';elsev2 := 'MMP不一样,滚';end if;
end;
/
-- 调用：
declarev2 varchar2(20);
begin判断是否和MILLER信息一样(&a, v2);dbms_output.put_line(v2);
end;
/

4. 有输入输出值IN OUT

--输入两个名字，把两个人的名字的首字母大写并拼接
--输出更新后的名字
-- 创建
CREATE OR REPLACE PROCEDURE pro_5(v1 IN OUT VARCHAR2,v2 IN OUT VARCHAR2) AS
BEGIN v1:=INITCAP(v1);v2:=INITCAP(v2);
END;
-- 调用
DECLAREv1 VARCHAR2(20):='&A';v2 VARCHAR2(20):='&B';
BEGINpro_5(v1,v2);dbms_output.put_line(v1||v2);
END;

（1）编译：

（2）调用

（3）宏&输入名称

（4）查看输出结果

四、存储过程的调用总结

存过不同类型创建	调用方式
没有参数	call pro_name()/exec pro_name
只有输入值	call pro_name(参数)/exec pro_name(参数)
有输出值	pl/sql调用
有输入输出	pl/sql调用

五、存储过程中的DML操作

1. 基本DML示例

CREATE OR REPLACE PROCEDURE transfer_employee(p_emp_id IN NUMBER,p_new_dept_id IN NUMBER,p_salary_adjustment IN NUMBER DEFAULT 0
) ASv_old_dept_id NUMBER;v_new_dept_name VARCHAR2(100);
BEGIN-- 获取原部门IDSELECT department_id INTO v_old_dept_idFROM employeesWHERE employee_id = p_emp_id;-- 获取新部门名称SELECT department_name INTO v_new_dept_nameFROM departmentsWHERE department_id = p_new_dept_id;-- 更新员工记录UPDATE employeesSET department_id = p_new_dept_id,salary = salary + p_salary_adjustmentWHERE employee_id = p_emp_id;-- 记录调动历史INSERT INTO transfer_history (transfer_id, employee_id, from_dept_id, to_dept_id, transfer_date) VALUES (transfer_seq.NEXTVAL, p_emp_id,v_old_dept_id, p_new_dept_id,SYSDATE);COMMIT;DBMS_OUTPUT.PUT_LINE('成功将员工 ' || p_emp_id || ' 从部门 ' || v_old_dept_id || ' 调至 ' || v_new_dept_name);
EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('调动失败: ' || SQLERRM);ROLLBACK;
END transfer_employee;
/

2. 使用RETURNING子句

CREATE OR REPLACE PROCEDURE promote_employee(p_emp_id IN NUMBER,p_new_job_id IN VARCHAR2,p_salary_increase IN NUMBER,p_new_salary OUT NUMBER
) AS
BEGINUPDATE employeesSET job_id = p_new_job_id,salary = salary + p_salary_increaseWHERE employee_id = p_emp_idRETURNING salary INTO p_new_salary;COMMIT;
END promote_employee;
/

六、异常处理

1. 预定义异常

异常	触发条件
NO_DATA_FOUND	SELECT INTO未返回行
TOO_MANY_ROWS	SELECT INTO返回多行
DUP_VAL_ON_INDEX	违反唯一约束
INVALID_CURSOR	非法游标操作
ZERO_DIVIDE	除零错误
LOGIN_DENIED	无效的用户名/密码
PROGRAM_ERROR	PL/SQL内部错误

2. 自定义异常

CREATE OR REPLACE PROCEDURE process_order(p_order_id IN NUMBER,p_discount IN NUMBER DEFAULT 0
) ASe_invalid_discount EXCEPTION;PRAGMA EXCEPTION_INIT(e_invalid_discount, -20001);v_order_total NUMBER;
BEGIN-- 验证折扣率IF p_discount < 0 OR p_discount > 0.5 THENRAISE e_invalid_discount;END IF;-- 计算订单总额SELECT SUM(unit_price * quantity) * (1 - p_discount)INTO v_order_totalFROM order_itemsWHERE order_id = p_order_id;-- 更新订单总额UPDATE ordersSET order_total = v_order_totalWHERE order_id = p_order_id;COMMIT;
EXCEPTIONWHEN e_invalid_discount THENDBMS_OUTPUT.PUT_LINE('错误: 折扣率必须在0到0.5之间');ROLLBACK;WHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('错误: 未找到订单 ' || p_order_id);ROLLBACK;WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('错误代码: ' || SQLCODE);DBMS_OUTPUT.PUT_LINE('错误信息: ' || SQLERRM);ROLLBACK;
END process_order;
/

七、游标处理

1. 显式游标

CREATE OR REPLACE PROCEDURE generate_department_report(p_dept_id IN NUMBER DEFAULT NULL
) ASCURSOR dept_cur ISSELECT department_id, department_nameFROM departmentsWHERE department_id = NVL(p_dept_id, department_id);CURSOR emp_cur(p_dept NUMBER) ISSELECT employee_id, last_name, salaryFROM employeesWHERE department_id = p_deptORDER BY salary DESC;v_total_salary NUMBER;
BEGINFOR dept_rec IN dept_cur LOOPDBMS_OUTPUT.PUT_LINE('部门: ' || dept_rec.department_name);DBMS_OUTPUT.PUT_LINE('--------------------------------');v_total_salary := 0;FOR emp_rec IN emp_cur(dept_rec.department_id) LOOPDBMS_OUTPUT.PUT_LINE(RPAD(emp_rec.employee_id, 10) ||RPAD(emp_rec.last_name, 20) ||TO_CHAR(emp_rec.salary, '$999,999.00'));v_total_salary := v_total_salary + emp_rec.salary;END LOOP;DBMS_OUTPUT.PUT_LINE('--------------------------------');DBMS_OUTPUT.PUT_LINE('部门总薪资: ' || TO_CHAR(v_total_salary, '$999,999.00'));DBMS_OUTPUT.PUT_LINE(CHR(10));  -- 空行END LOOP;
END generate_department_report;
/

2. REF游标(动态游标)

CREATE OR REPLACE PROCEDURE get_employee_data(p_query_type IN VARCHAR2,p_result_set OUT SYS_REFCURSOR
) AS
BEGINIF p_query_type = 'HIGH_SALARY' THENOPEN p_result_set FORSELECT employee_id, last_name, salaryFROM employeesWHERE salary > 10000ORDER BY salary DESC;ELSIF p_query_type = 'BY_DEPARTMENT' THENOPEN p_result_set FORSELECT e.employee_id, e.last_name, d.department_nameFROM employees eJOIN departments d ON e.department_id = d.department_idORDER BY d.department_name, e.last_name;ELSEOPEN p_result_set FORSELECT employee_id, last_name, hire_dateFROM employeesORDER BY hire_date DESC;END IF;
END get_employee_data;
/

八、高级特性

1. 自治事务

允许在过程中创建独立的事务：

CREATE OR REPLACE PROCEDURE log_activity(p_action IN VARCHAR2,p_user IN VARCHAR2 DEFAULT USER
) ASPRAGMA AUTONOMOUS_TRANSACTION;
BEGININSERT INTO activity_log (log_id, action_name, user_name, log_time) VALUES (log_seq.NEXTVAL, p_action,p_user, SYSTIMESTAMP);COMMIT;
EXCEPTIONWHEN OTHERS THENROLLBACK;RAISE;
END log_activity;
/

2. 批量处理(FORALL)

CREATE OR REPLACE PROCEDURE update_multiple_salaries(p_emp_ids IN SYS.ODCINUMBERLIST,p_percent IN NUMBER
) AS
BEGINFORALL i IN 1..p_emp_ids.COUNTUPDATE employeesSET salary = salary * (1 + p_percent/100)WHERE employee_id = p_emp_ids(i);log_activity('批量更新薪资', USER);COMMIT;DBMS_OUTPUT.PUT_LINE('成功更新 ' || SQL%ROWCOUNT || ' 条记录');
EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('批量更新失败: ' || SQLERRM);ROLLBACK;
END update_multiple_salaries;
/

3. 条件编译

CREATE OR REPLACE PROCEDURE debug_procedure ASv_debug BOOLEAN := TRUE;
BEGIN$IF $$DEBUG $THENDBMS_OUTPUT.PUT_LINE('调试信息: 开始执行过程');$END-- 主要处理逻辑$IF $$DEBUG $THENDBMS_OUTPUT.PUT_LINE('调试信息: 过程执行完成');$END
END debug_procedure;
/-- 编译时设置条件
ALTER PROCEDURE debug_procedure COMPILE PLSQL_CCFLAGS = 'debug:true';

九、存储过程管理

1. 查看存储过程

-- 查看源代码
SELECT text FROM user_source 
WHERE name = 'UPDATE_EMPLOYEE_SALARY' 
AND type = 'PROCEDURE'
ORDER BY line;-- 查看依赖关系
SELECT * FROM user_dependencies
WHERE name = 'UPDATE_EMPLOYEE_SALARY';-- 查看参数信息
SELECT argument_name, data_type, in_out
FROM user_arguments
WHERE object_name = 'UPDATE_EMPLOYEE_SALARY';

右键查看

2. 重新编译

ALTER PROCEDURE procedure_name COMPILE;

右键重新编译

右键编辑--执行

3. 权限控制

-- 授予执行权限
GRANT EXECUTE ON procedure_name TO user_name;-- 创建公有同义词
CREATE PUBLIC SYNONYM proc_synonym FOR schema.procedure_name;

4. 删除存储过程

DROP PROCEDURE procedure_name;

十、最佳实践

1. 命名规范：使用动词+名词形式，如calculate_tax, generate_report

2. 参数设计：限制参数数量(通常不超过10个)，使用默认参数

3. 错误处理：捕获预期异常，记录错误日志

4. 事务管理：保持事务简短，避免长时间锁定

5. 性能优化：使用批量操作(FORALL, BULK COLLECT)

6. 文档注释：为过程添加清晰注释

7. 模块化：将复杂逻辑分解为多个小过程

8. 避免硬编码：使用参数和常量代替字面值

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

• 1.调用方式不一样。
• 2.DML操作一般不用函数。
• 3.函数有return返回值，存储过程没有。
• 4.如果返回值超过一个，一般用存储过程。
• 5.存储过程可以调用函数，函数不能调用存储过程。
• 6.存储过程用来实现某些操作或者业务，函数用来实现某种功能。

存储过程	函数
用于在数据库中完成特定的操作或者任务（如插入、删除等）	用于特定的数据（如选择）
程序头部声明用procedure	程序头部声明用function
程序头部声明不需描述返回类型	程序头部声明时要描述返回类型，而且PL/SQL块中至少要包括一个有效的return语句
可以使用in/out/in out三种模式的参数	可以使用in/out/in out三种模式的参数
可作为一个独立的PL/SQL语句来执行	不能独立执行，必须作为表达式的一部分调用
可以通过out/in out返回零个或多个值	通过return语句返回一个值，且改值要与声明部分一致，也可以是通过out类型的参数带出的变量
SQL语句(DML或SELECT)中不可调用存储过程	SQL语句(DML或SELECT)中可以调用函数

存储过程是Oracle数据库编程的核心组件，合理设计和使用的存储过程可以显著提高应用性能、安全性和可维护性。通过掌握上述技术，您可以构建高效、可靠的企业级数据库应用。