oracle存储过程详解

1，存储过程的创建语法

    其中， [OR REPLACE] 是可选的，如果指定，则在存储过程已存在时替换它。 parameter 部分可以包含输入、输出或输入输出参数。 declaration_section 用于声明变量、常量、游标等。 executable_section 包含执行存储过程所需的SQL语句和其他操作。 exception_section 用于处理在存储过程执行期间可能发生的异常

2，存储过程带参数的创建语法

    在Oracle中，创建带参数的存储过程通常涉及到定义输入（IN）、输出（OUT）或输入输出（IN OUT）参数，并在存储过程的主体中使用这些参数。以下是一个简单的Oracle存储过程示例，它接受一个输入参数（一个数字），计算这个数字的平方，并通过一个输出参数返回结果

    存储过程定义完成后，您可以使用匿名块或应用程序代码来调用这个存储过程，并传入参数值以及一个变量来接收输出参数的值。以下是一个匿名块的示例，用于调用上面创建的 calculate_square 存储过程：

3，存储过程中变量的使用

 1）变量名是区分大小写的，因此 v_name和 V_NAME被视为两个不同的变量。

 2）当使用 %TYPE或 %ROWTYPE时，可以确保变量的数据类型与数据库中的表或列的数据类型保持一致。这有助于增强代码的可维护性和可读性。

 3）在处理可能返回多行数据的查询时，要小心使用 SELECT ... INTO ...语句，因为如果查询返回多行，将会引发异常。

3.1，变量的声明

    在存储过程的声明部分（通常在 BEGIN 之前），使用 DECLARE 关键字来声明变量。变量的声明应指定变量的名称、数据类型以及可能的初始值。例如：

3.2，变量的赋值

    在存储过程的主体中，可以使用赋值语句为变量赋值。Oracle使用 := 作为赋值操作符。例如：

    此外，还可以使用 SELECT ... INTO ... 语句将查询结果赋给变量。例如：

3.3，变量的使用

    在存储过程的逻辑中，可以像使用其他数据一样使用这些变量。例如，在条件语句、循环或其他SQL语句中使用它们。例如：

3.4，变量的作用域与生命周期

    在存储过程中声明的变量具有局部作用域，即它们只在存储过程的执行期间存在。一旦存储过程执行完毕，这些变量就会被销毁

4，存储过程if/case条件判断的语法

4.1，if条件判断语法

    在Oracle存储过程中， IF/ELSE  语法用于基于某个条件执行不同的代码块。以下是基本的  IF/ELSIF/ELSE  语法结构，它允许你基于一个或多个条件执行不同的操作

4.2，case条件判断语法

        在Oracle存储过程中，CASE语法通常用于根据一个或多个条件来执行不同的操作。这与在SQL查询中使用CASE表达式的方式类似，但也可以在PL/SQL代码块中使用

1）简单的case表达式

2）搜索的case表达式

3）在IF-THEN-ELSE结构中的case

4）在select语句中的case

        虽然这不是存储过程的一部分，但经常在存储过程中的SQL语句中使用CASE。例如，在查询结果中根据条件格式化输出

5，存储过程游标的定义与使用

    在Oracle中，游标（Cursor）是用于从SQL查询中检索行数据的数据库对象。在存储过程中，游标通常用于处理从SELECT语句返回的多行结果集。下面是一个关于如何在Oracle存储过程中定义和使用游标的示例：

5.1，定义游标

    首先，你需要在存储过程的声明部分定义一个游标。游标可以定义为强类型（与特定的查询关联）或弱类型（不与特定的查询关联）。以下是一个强类型游标的定义示例：

5.2，打开游标

    在存储过程的主体中，你需要打开游标以准备从关联的查询中检索数据

5.3，从游标中检索数据

你可以使用循环结构（如 LOOP ）来遍历游标中的每一行，并使用 FETCH 语句将数据从游标中检索到变量中

5.4，游标使用注意事项

• 游标的使用通常会增加存储过程的复杂性和执行时间，因此应谨慎使用，特别是在处理大量数据时。
• 在使用FETCH语句时，可以通过%NOTFOUND、%ROWCOUNT和%ISOPEN等属性来检查游标的状态。
• 尽量避免在存储过程中打开多个游标，因为这可能导致性能下降和资源管理问题。
• 如果可能的话，考虑使用集合操作或批量处理来替代游标，以提高性能

6，存储过程中for循环与while循环的使用

    在Oracle存储过程中，可以使用 FOR 循环和 WHILE 循环来处理重复的任务。以下是关于如何在Oracle存储过程中使用这两种循环的示例

6.1，使用for循环

    FOR循环通常用于遍历一个已知的范围或集合。在Oracle中，你可以使用FOR循环来遍历一个数字范围或者一个游标的结果集。

示例：遍历数字范围

示例：遍历游标结果集

6.2，使用while循环

    WHILE循环会在满足某个条件时重复执行一段代码。条件在每次循环迭代开始时进行检查。

示例：使用WHILE循环打印数字

7，存储过程中异常的处理逻辑

    在Oracle存储过程中，异常处理逻辑是确保程序在遇到错误或异常情况时能够优雅地处理并可能恢复的关键部分。Oracle提供了异常处理结构，允许你捕获和处理运行时错误。

• 异常处理的基本结构

在Oracle PL/SQL中，异常处理通常使用BEGIN ... EXCEPTION ... END;块来实现。以下是异常处理的基本结构：

• 预定义异常

Oracle预定义了一些异常，如NO_DATA_FOUND、TOO_MANY_ROWS、ZERO_DIVIDE等。这些异常在特定情况下自动被触发。

• 用户定义异常

除了预定义异常，你还可以定义自己的异常来处理特定的业务逻辑错误。定义用户定义异常需要使用DECLARE部分，并使用EXCEPTION来声明它

• 使用RAISE触发异常

你可以使用RAISE语句来显式触发一个异常，无论是预定义的还是用户定义的。

• SQLERRM和SQLCODE

在异常处理块中，你可以使用SQLERRM来获取关于当前错误的消息文本，使用SQLCODE来获取错误代码。这些在记录错误或向用户报告错误时非常有用。

示例：异常处理逻辑

下面是一个简单的示例，演示了如何在存储过程中使用异常处理逻辑：

在上面的示例中，如果查询没有找到任何行，将触发NO_DATA_FOUND异常。然后，我们显式地触发了一个自定义异常e_employee_not_found。在EXCEPTION块中，我们捕获并处理这些异常

8，存储过程的调试和调用方法

    调用存储过程通常是通过执行一个PL/SQL块或在应用程序（如Java、C#等）中使用数据库连接来完成的。以下是几种常见的调用方法：

8.1. 在SQL*Plus或SQL Developer中调用

    你可以直接在SQL*Plus或Oracle SQL Developer等工具中执行存储过程。例如：

或者，使用PL/SQL匿名块：

8.2. 在应用程序中调用

    在应用程序中，你通常会使用数据库连接和相应的API来调用存储过程。这取决于你使用的编程语言和数据库连接库。例如，在Java中，你可能会使用JDBC来调用存储过程：

8.3，调试存储过程

• （使用DBMS_OUTPUT.PUT_LINE） 在存储过程的代码中，你可以使用DBMS_OUTPUT.PUT_LINE来输出调试信息。这些信息可以在SQLPlus或SQL Developer的输出窗口中查看。确保在调用存储过程之前已经启用了输出（在SQLPlus中使用SET SERVEROUTPUT ON）
•  （使用Oracle SQL Developer的调试器）Oracle SQL Developer提供了一个强大的调试器，允许你逐步执行存储过程代码，查看变量值，设置断点等。以下是使用SQL Developer调试器的基本步骤：

                 1） 打开存储过程代码。

                 2）在你想要暂停执行的代码行上设置断点。

                 3）选择调试模式来执行存储过程。

                 4）使用调试器控件逐步执行代码，查看变量值等

• （使用日志表）另一种调试方法是使用日志表来记录存储过程执行过程中的关键信息。你可以在存储过程中插入日志记录，然后将这些记录写入一个专门的日志表中。之后，你可以查询这个表来分析存储过程的执行情况

9，oracle复杂存储过程示例

编写一个复杂的Oracle存储过程需要涉及多个方面，包括参数处理、逻辑判断、循环、异常处理以及可能的数据库交互。下面我将提供一个示例存储过程，该过程执行以下任务：

• 接受一个员工ID作为输入参数。
• 检查员工是否存在。
• 如果员工存在，则计算该员工的总薪水，并更新一个日志表以记录此次计算。
• 如果员工不存在，则抛出一个自定义异常。
• 在计算总薪水时，考虑员工的基本薪水、奖金和津贴。
• 使用循环来处理员工的多个薪资记录（假设一个员工可能有多个薪资条目）

在这个存储过程中

• 我们首先定义了一个游标c_salary_records来检索员工的薪资记录。
• 使用IF语句来检查员工是否存在，如果不存在则抛出e_employee_not_found异常。
• 如果员工存在，我们使用FOR循环遍历游标中的每条记录，并累加基本薪水、奖金和津贴来计算总薪水。
• 计算完总薪水后，我们将结果插入到salary_calculation_log日志表中。
• 在异常处理部分，我们捕获了自定义的e_employee_not_found异常以及其他任何可能发生的异常，并输出相应的错误消息