从Oracle自定义函数和存储过程案例学习PL/SQL的使用

发布于：2024-07-03 ⋅ 阅读:(12) ⋅ 点赞:(0)

一、什么是PL/SQL


PL/SQL（Procedural Language/Structured Query Language） 是Oracle数据库对SQL的扩展，它在SQL的基础上增加了过程化编程语言的元素，如变量、条件语句、循环语句、异常处理等。这使得PL/SQL不仅可以用于查询数据，还可以用于控制数据以及实现复杂的业务逻辑。

二、Oracle自定义函数案例分析

案例一

/*综述：大小值校验
    入参：checkcolumn：校验列列名    lowvalue：小值    highvalue:大值  isinclude:是否包含边界 1是 0否
    输出： 布尔值，判断通过与否 0不通过 1通过
    判断内容：1、判断校验列列名checkcolumn是否为数值型，如为非数值型-->0不通过；
                    A.判断小值lowvalue<校验列列名checkcolumn<大值highvalue-->1通过；
                    B.当小值为空，大值不为空时，判断校验列列名checkcolumn<大值highvalue-->1通过；
                    C.当大值为空，小值不为空时，判断小值lowvalue<校验列列名checkcolumn-->1通过；
                  
*/

-- 具体实现

CREATE OR REPLACE function LKTEST.SizeValueVerify(
checkcolumn IN varchar2, 
lowvalue IN numeric,
highvalue IN numeric
)

RETURN integer 

IS
    v_checkcolumn numeric;
BEGIN
    if checkcolumn is null then
        return null;
    end if;
    
    v_checkcolumn := to_number(checkcolumn);


    
    if lowvalue is not null and highvalue is not null and v_checkcolumn>lowvalue and v_checkcolumn<highvalue then
        return 1;
    elsif lowvalue is null and highvalue is not null and v_checkcolumn<highvalue then
        return 1;
    elsif lowvalue is not null and highvalue is null and v_checkcolumn>lowvalue then
        return 1;
    else
        return 0;
    end if ;
   
    --异常捕获
    EXCEPTION
    WHEN OTHERS THEN
      RETURN 0;

END;

逐步分析

函数的定义部分
CREATE OR REPLACE function LKTEST.SizeValueVerify(  
checkcolumn IN varchar2,  
lowvalue IN numeric,  
highvalue IN numeric  
)  
  
RETURN integer
  • CREATE OR REPLACE FUNCTION LKTEST.SizeValueVerify: 创建一个名为SizeValueVerify的函数在LKTEST模式下，如果该函数已经存在，则替换它。
  • 输入参数有三个：
    • checkcolumn IN varchar2: 一个字符串类型的输入参数，表示要校验的列名（但这里实际上应该是列的值，因为列名在运行时是未知的）。
    • lowvalue IN numeric: 一个数值类型的输入参数，表示校验的低值。
    • highvalue IN numeric: 一个数值类型的输入参数，表示校验的高值。
  • 函数返回一个整数类型的值。
函数的变量声明部分
 
IS  
    v_checkcolumn numeric;
  • v_checkcolumn numeric;: 声明了一个名为v_checkcolumn的数值型变量，用于存储转换后的checkcolumn值。
函数的主体部分
if checkcolumn is null then  
    return null;  
end if;

如果checkcolumn（实际上是列的值）为null，则函数返回null。

v_checkcolumn := to_number(checkcolumn);

尝试将checkcolumn（字符串）转换为数值并存储在v_checkcolumn中。

if lowvalue is not null and highvalue is not null and v_checkcolumn>lowvalue and v_checkcolumn<highvalue then  
    return 1;  
...  
end if;

这是主要的判断逻辑：

  • 如果lowvalue和highvalue都不为空，并且v_checkcolumn的值在这两个值之间（不包括边界），则返回1。
  • 接下来的两个elsif子句处理了当其中一个边界值为null的情况。
--异常捕获  
EXCEPTION  
    WHEN OTHERS THEN  
      RETURN 0;

这是异常处理部分。如果函数中的任何代码抛出了异常（例如，当to_number(checkcolumn)失败时），则捕获该异常并返回0。

根据案例总结PL/SQL语法

  • IS关键字之后的部分是函数的声明部分，其中你可以声明局部变量、常量、游标等。然后，BEGIN和END之间的部分是函数体，其中包含了函数的具体逻辑。简而言之，IS（或AS）在PL/SQL函数和过程中用于分隔参数列表和函数体。这是Oracle PL/SQL的语法要求之一。
  • 在PL/SQL中，IF 语句下面可以跟随任意数量的语句，这些语句被包含在 BEGIN ... END; 块中（如果有多于一个语句的话）。如果没有 BEGIN ... END; 块，则 IF 语句只能直接跟随一个单独的语句。
  • PL/SQL中BEGIN ... END; 块相当于就是Java中的"{" 和 "}"。
  • end if  也相当于Java中的"}"，代表语句块的结束。
  • ":="是PL/SQL中的赋值符号，等价于Java中的"="；PL/SQL中的"="可以理解为Java中的"=="。
  • 函数中是先对函数进行了声明，然后声明了变量。再开始在BEGIN ... END;块中写执行过程，其中BEGIN ... END;块还可以嵌套使用BEGIN ... END;块。

  • WHEN OTHERS THEN是一个通用的异常处理器，它会捕获在BEGIN和END之间发生的任何未被其他特定异常处理器捕获的异常。当发生这样的异常时，代码将执行RETURN 0;，这意味着函数将返回一个值0，而不是继续执行或抛出异常到调用者。

    使用WHEN OTHERS THEN需要谨慎，因为它会捕获所有的异常，这可能会隐藏一些你不希望忽略的潜在问题。通常，最好编写特定的异常处理器来处理你期望可能会发生的异常，并只使用WHEN OTHERS THEN作为最后的手段来捕获并处理那些你未明确处理的异常。

PL/SQL中变量的声明方式

在PL/SQL中，IS（或AS）和DECLARE在函数和过程的上下文中具有不同的用途和位置。

  • 在函数和过程中：
    • 函数和过程的定义通常以CREATE OR REPLACE FUNCTION或CREATE OR REPLACE PROCEDURE开始。
    • 在参数列表和返回类型之后，你会看到IS（或AS），这标志着函数或过程的参数和返回类型的结束，以及函数体或过程体的开始。
    • 在IS（或AS）之后和BEGIN之前，你可以直接声明局部变量、常量、游标等，而不需要使用DECLARE关键字。
CREATE OR REPLACE FUNCTION my_function (param1 IN NUMBER) RETURN NUMBER IS  
    v_my_variable NUMBER;  -- 直接声明变量，不需要DECLARE  
BEGIN  
    ...  
END my_function;  
/
  • 在匿名块中：
    • 匿名块是直接在SQL*Plus、SQL Developer或其他工具中执行的PL/SQL代码块。
    • 在匿名块中，如果你想声明局部变量、常量或游标，你需要使用DECLARE关键字。
    • DECLARE部分位于BEGIN之前。
DECLARE  
    v_my_variable NUMBER;  -- 使用DECLARE声明变量  
BEGIN  
    ...  
END;  
/
  • 总结：
    • 在函数和过程中，你不需要使用DECLARE来声明局部变量，而是直接在IS（或AS）之后声明它们。
    • 在匿名块中，你需要使用DECLARE来声明局部变量。

PL/SQL中异常处理方式

  • 预定义异常

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

DECLARE  
    v_count NUMBER;  
BEGIN  
    SELECT COUNT(*) INTO v_count FROM employees WHERE employee_id = 100;  
    IF v_count = 0 THEN  
        RAISE NO_DATA_FOUND; -- 实际上，上面的SELECT语句在找不到行时会自动触发NO_DATA_FOUND异常  
    END IF;  
EXCEPTION  
    WHEN NO_DATA_FOUND THEN  
        DBMS_OUTPUT.PUT_LINE('没有找到员工ID为100的员工。');  
END;

注意：在上面的示例中，我们实际上不需要手动触发NO_DATA_FOUND异常，因为当SELECT INTO语句没有找到任何行时，Oracle会自动触发它。

  • 用户自定义异常

你可以定义自己的异常，并在代码中显式地触发它们。

DECLARE  
    invalid_id EXCEPTION;  
    v_employee_id NUMBER := -1;  
BEGIN  
    IF v_employee_id < 0 THEN  
        RAISE invalid_id;  
    END IF;  
EXCEPTION  
    WHEN invalid_id THEN  
        DBMS_OUTPUT.PUT_LINE('员工ID无效。');  
END;
  • 使用PRAGMA EXCEPTION_INIT为用户定义异常关联错误号

你可以使用PRAGMA EXCEPTION_INIT为用户定义的异常关联一个Oracle错误号。

DECLARE  
    my_custom_exception EXCEPTION;  
    PRAGMA EXCEPTION_INIT(my_custom_exception, -20001); -- 关联自定义异常与错误号-20001  
BEGIN  
    -- 假设这里有一些可能触发错误号-20001的代码  
    -- ...  
    RAISE_APPLICATION_ERROR(-20001, '这是一个自定义错误消息'); -- 触发错误号-20001  
EXCEPTION  
    WHEN my_custom_exception THEN  
        DBMS_OUTPUT.PUT_LINE('捕获到自定义异常：' || SQLERRM);  
END;
  • 使用WHEN OTHERS THEN捕获所有其他异常

WHEN OTHERS THEN子句可以捕获所有未被前面WHEN子句捕获的异常。

BEGIN  
    -- 这里有一些可能引发异常的代码  
    -- ...  
EXCEPTION  
    WHEN NO_DATA_FOUND THEN  
        DBMS_OUTPUT.PUT_LINE('没有找到数据。');  
    WHEN OTHERS THEN  
        DBMS_OUTPUT.PUT_LINE('发生了一个未知异常：' || SQLERRM);  
END;
  • 在异常处理中回滚事务

如果在事务中发生异常，并且你希望撤销该事务中所有未提交的更改，可以使用ROLLBACK语句。

DECLARE  
    PRAGMA AUTONOMOUS_TRANSACTION; -- 如果需要的话，声明为自治事务  
BEGIN  
    -- 开始一个事务  
    -- ...（一些DML操作）  
      
    -- 假设这里发生了异常  
    RAISE_APPLICATION_ERROR(-20002, '事务中的错误');  
  
EXCEPTION  
    WHEN OTHERS THEN  
        ROLLBACK; -- 回滚事务  
        DBMS_OUTPUT.PUT_LINE('事务已回滚：' || SQLERRM);  
END;

 三、Oracle存储过程案例分析

案例一

假设我们有一个名为employees的表，其结构如下：

CREATE TABLE employees (  
    employee_id NUMBER PRIMARY KEY,  
    first_name VARCHAR2(50),  
    last_name VARCHAR2(50),  
    email VARCHAR2(100),  
    phone_number VARCHAR2(20)  
);

现在，我们可以创建一个存储过程来查询这个表：

CREATE OR REPLACE PROCEDURE sp_get_employee_details(  
    p_employee_id IN NUMBER,  
    p_first_name OUT VARCHAR2,  
    p_last_name OUT VARCHAR2,  
    p_email OUT VARCHAR2,  
    p_phone_number OUT VARCHAR2  
) AS  
BEGIN  
    SELECT first_name, last_name, email, phone_number  
    INTO p_first_name, p_last_name, p_email, p_phone_number  
    FROM employees  
    WHERE employee_id = p_employee_id;  
  
    -- 如果员工不存在，可以抛出一个异常或者设置一些错误标志，但这里为了简单起见，我们不会这样做  
    -- 你可以在调用这个存储过程的PL/SQL块中处理NO_DATA_FOUND异常  
EXCEPTION  
    WHEN NO_DATA_FOUND THEN  
        -- 你可以在这里处理员工不存在的情况  
        DBMS_OUTPUT.PUT_LINE('No employee found with the given ID.');  
        -- 或者你可以抛出一个自定义的异常  
        -- RAISE_APPLICATION_ERROR(-20001, 'No employee found with the given ID.');  
END sp_get_employee_details;  
/

 

注意：

  1. 我们使用了IN、OUT参数来定义输入和输出参数。在这个例子中，p_employee_id是输入参数，而其他四个参数是输出参数。
  2. 我们使用SELECT ... INTO ...语句从employees表中检索数据，并将结果存储在输出参数中。
  3. 如果在表中找不到与给定ID匹配的员工，SELECT ... INTO ...语句将引发NO_DATA_FOUND异常。在这个例子中，我们只是简单地使用DBMS_OUTPUT.PUT_LINE打印一条消息，但你可以根据你的需求选择其他处理方式。

要调用这个存储过程并处理结果，你可以使用以下PL/SQL块：

DECLARE  
    v_first_name VARCHAR2(50);  
    v_last_name VARCHAR2(50);  
    v_email VARCHAR2(100);  
    v_phone_number VARCHAR2(20);  
BEGIN  
    sp_get_employee_details(123, v_first_name, v_last_name, v_email, v_phone_number);  
    -- 这里你可以使用检索到的员工详细信息  
    DBMS_OUTPUT.PUT_LINE('First Name: ' || v_first_name);  
    DBMS_OUTPUT.PUT_LINE('Last Name: ' || v_last_name);  
    DBMS_OUTPUT.PUT_LINE('Email: ' || v_email);  
    DBMS_OUTPUT.PUT_LINE('Phone Number: ' || v_phone_number);  
EXCEPTION  
    WHEN OTHERS THEN  
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);  
END;  
/

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布