oracle 存储过程学习

### Oracle 存储过程与函数详解 #### 6.1 引言 Oracle数据库中的存储过程和函数是PL/SQL编程的重要组成部分。它们是数据库中存储的命名块，可以通过调用其名称来执行预定义的任务。这些命名块有助于实现业务逻辑、企业规则，并允许在多个地方重用代码。本文档将详细介绍如何创建、使用和管理存储过程和函数。 #### 6.2 创建函数 函数是一种特殊的PL/SQL块，它接收输入参数并返回一个值。创建函数的基本语法如下： ```sql CREATE [OR REPLACE] FUNCTION function_name (arg1 [{IN | OUT | INOUT}] type1 [DEFAULT value1], arg2 [{IN | OUT | INOUT}] type2 [DEFAULT value2], ... argn [{IN | OUT | INOUT}] typen [DEFAULT valuen]) [AUTHID DEFINER | CURRENT_USER] RETURN return_type IS | AS <类型.变量的声明部分> BEGIN <执行部分> RETURN expression EXCEPTION <异常处理部分> END function_name; ``` - **IN**: 只能用于传入值，不能修改。 - **OUT**: 用于传出值，传入时可为`NULL`。 - **INOUT**: 既可以传入也可以传出值。 - **RETURN**: 指定函数返回的数据类型。 **示例**：创建一个函数来计算某个部门的工资总额。 ```sql CREATE OR REPLACE FUNCTION get_salary( Dept_no NUMBER, Emp_count OUT NUMBER) RETURN NUMBER IS v_sum NUMBER; BEGIN SELECT SUM(SALARY), COUNT(*) INTO v_sum, Emp_count FROM EMPLOYEES WHERE DEPARTMENT_ID = Dept_no; RETURN v_sum; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE || '---' || SQLERRM); END get_salary; ``` **函数调用**： - **位置表示法**：按照参数顺序传递。 - **名称表示法**：指定参数名。 **示例**：调用上面创建的`get_salary`函数。 ```sql DECLARE v_num NUMBER; v_sum NUMBER; BEGIN v_sum := get_salary(10, v_num); DBMS_OUTPUT.PUT_LINE('部门号为:10的工资总和：' || v_sum || '，人数为：' || v_num); END; ``` #### 6.3 存储过程 存储过程是PL/SQL块，它可以包含复杂的逻辑，并且可以接受多个输入参数，但不返回值。存储过程通常用于执行数据库操作。 ##### 6.3.1 创建过程 创建存储过程的基本语法如下： ```sql CREATE [OR REPLACE] PROCEDURE procedure_name (arg1 [{IN | OUT | INOUT}] type1 [DEFAULT value1], arg2 [{IN | OUT | INOUT}] type2 [DEFAULT value2], ... argn [{IN | OUT | INOUT}] typen [DEFAULT valuen]) IS | AS <类型.变量的声明部分> BEGIN <执行部分> EXCEPTION <异常处理部分> END procedure_name; ``` **示例**：创建一个存储过程来更新员工的工资。 ```sql CREATE OR REPLACE PROCEDURE update_salary( Emp_id IN NUMBER, New_salary IN NUMBER) IS BEGIN UPDATE EMPLOYEES SET SALARY = New_salary WHERE EMPLOYEE_ID = Emp_id; COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE || '---' || SQLERRM); END update_salary; ``` ##### 6.3.2 调用存储过程 存储过程可以通过简单地使用其名称来调用。 **示例**：调用`update_salary`存储过程。 ```sql BEGIN update_salary(100, 5000); END; ``` ##### 6.3.3 AUTHID `AUTHID`关键字用于指定函数或过程运行的身份，可以是`DEFINER`或`CURRENT_USER`。 - **DEFINER**: 使用定义者的权限运行。 - **CURRENT_USER**: 使用当前用户的权限运行。 ##### 6.3.4 PRAGMA AUTONOMOUS_TRANSACTION `PRAGMA AUTONOMOUS_TRANSACTION`允许存储过程中执行非事务性操作，例如查询数据库快照。 **示例**：使用`PRAGMA AUTONOMOUS_TRANSACTION`。 ```sql CREATE OR REPLACE PROCEDURE check_data IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN -- 执行非事务性操作 END check_data; ``` ##### 6.3.5 开发存储过程步骤 1. **设计**: 确定功能需求。 2. **编写**: 编写存储过程代码。 3. **测试**: 测试存储过程的功能。 4. **优化**: 优化性能问题。 5. **部署**: 部署到生产环境。 ##### 6.3.6 删除过程和函数 使用`DROP`语句来删除存储过程或函数。 ```sql DROP FUNCTION function_name; DROP PROCEDURE procedure_name; ``` ##### 6.3.7 过程与函数的比较 - **函数**: 返回一个值；可以有输入参数。 - **存储过程**: 不返回值；可以有输入和输出参数。 - **函数**: 可以嵌套在其他函数或过程中。 - **存储过程**: 可以包含更复杂的逻辑和事务控制。 ### 结论 存储过程和函数是Oracle数据库中非常强大的工具，它们可以帮助提高代码的复用性和可维护性，同时减少网络流量。熟练掌握这两种工具的创建和使用方法对于Oracle开发者来说至关重要。