Oracle数据库之存储过程（九）_oracle存储过程能查询数据吗-优快云博客

本文链接：https://blog.youkuaiyun.com/pursue_mony/article/details/138847405

本文详细介绍了Oracle存储过程的定义、优点、与函数和触发器的区别，以及如何创建和执行存储过程。通过示例展示了如何在PL/SQL中创建和调用存储过程，并提到了查询存储过程的方法，包括查询当前用户、所有可访问和数据库中的存储过程，以及查看源代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

概述

Oracle存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。以下是关于Oracle存储过程的详细概述：

定义：
- 存储过程是由流控制和SQL语句书写的过程，经编译和优化后存储在数据库服务器中。
- 在Oracle中，若干个有联系的过程可以组合在一起构成程序包。
优点：
- 提高性能：由于存储过程在数据库服务器上执行，可以减少网络流量和数据传输时间。此外，由于编译一次并多次执行，因此还可以提高应用程序的响应速度。
- 保护数据：通过存储过程来操作数据库可以防止SQL注入攻击和误操作等安全问题。
- 简化代码：将常见任务封装到单个代码块中，并将其命名为存储过程可以简化应用程序中的重复代码。
- 维护便利：如果需要更改某些业务逻辑或查询条件，则只需要更新一个存储过程即可。
与函数和触发器的区别：
- 函数：只能返回一个值，而存储过程可以返回多个值。函数通常作为表达式使用，而存储过程通常被调用以完成某些任务。函数不能修改数据库状态，而存储过程可以。
- 触发器：触发器不能显式地调用，而存储过程可以。触发器的执行是隐式的，而存储过程是显式的。触发器只能在表级别上定义，而存储过程可以在数据库级别上定义。
创建：
- 在Oracle中，可以使用CREATE PROCEDURE语句来创建存储过程。
- 示例：CREATE OR REPLACE PROCEDURE procedure_name [(parameter1 datatype1 [,parameter2 datatype2]...)] IS ... BEGIN ... END [procedure_name];
执行：
- 使用EXECUTE或EXEC语句来执行存储过程，例如：EXECUTE procedure_name; 或 EXEC procedure_name;
变量和控制结构：
- 在存储过程中，可以使用变量和控制结构来实现更复杂的业务逻辑。

存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

创建存储过程

在 Oracle 数据库中，创建存储过程需要使用 CREATE OR REPLACE PROCEDURE 语句。以下是一个创建存储过程的基本示例，该过程接收一个参数，并在数据库中执行一些操作：

CREATE OR REPLACE PROCEDURE my_procedure(p_input_param IN NUMBER) IS  
BEGIN  
    -- 这里是存储过程的主体  
    -- 可以使用 p_input_param 参数进行操作  
  
    -- 例如，我们假设有一个名为 my_table 的表，它有一个名为 my_column 的列  
    -- 我们可以使用输入参数 p_input_param 来插入一条新记录  
    INSERT INTO my_table (my_column) VALUES (p_input_param);  
  
    -- 或者我们可以查询一些数据并处理它  
    -- 这里只是一个示例，没有实际输出或处理  
    FOR rec IN (SELECT * FROM my_table WHERE some_column = p_input_param) LOOP  
        -- 在这里可以对 rec 进行处理，比如输出到 DBMS_OUTPUT  
        DBMS_OUTPUT.PUT_LINE('Found a record with value: ' || rec.my_column);  
    END LOOP;  
  
    -- 存储过程完成，没有返回值（存储过程不返回结果集，但可以修改数据）  
EXCEPTION  
    WHEN OTHERS THEN  
        -- 异常处理部分  
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);  
        -- 在这里可以根据需要执行其他操作，比如回滚事务等  
END my_procedure;  
/

注意：

IN 关键字表示这是一个输入参数。你也可以使用 OUT 或 IN OUT 来定义输出参数或输入输出参数。
存储过程体中的 BEGIN ... END; 块是必须的，它包含了存储过程的主体。
EXCEPTION 部分是可选的，用于处理存储过程中可能发生的异常。
当你运行此 SQL 语句后，存储过程 my_procedure 就会被创建或替换（如果已存在）。
使用 DBMS_OUTPUT.PUT_LINE 可以将信息输出到客户端（如果已启用 SET SERVEROUTPUT ON）。
如果你的存储过程需要修改数据，确保在过程开始时设置适当的事务控制逻辑（如 SET TRANSACTION 语句或使用 SAVEPOINT）。

执行存储过程

在 Oracle 数据库中，执行存储过程（Stored Procedure）通常不需要使用特定的 SQL 语句来“执行”它，而是直接通过调用存储过程的名字和传递必要的参数来执行。但是，为了在一个 SQL 环境中（比如 SQL*Plus、SQL Developer、PL/SQL 开发者工具等）调用存储过程，你通常会使用 BEGIN ... END; 块或者 EXECUTE（或 EXEC）命令。

以下是几种执行存储过程的方法：

使用 `BEGIN ... END;` 块

当你想要在一个匿名块中调用存储过程时，你可以这样做：

BEGIN  
    my_procedure(123); -- 假设 my_procedure 是一个接受一个参数的存储过程  
END;  
/

注意斜杠 / 用于表示匿名块的结束，并告诉 SQL 工具执行该块。

使用 `EXECUTE` 或 `EXEC` 命令

在 SQL*Plus 或某些其他工具中，你可以使用 EXECUTE 或 EXEC 缩写来调用存储过程：

EXECUTE my_procedure(123); -- 或者 EXEC my_procedure(123);

在 PL/SQL 块中

如果你在 PL/SQL 代码块中，你可以直接调用存储过程，而不需要 BEGIN ... END; 或 EXECUTE：

DECLARE  
    -- 这里可以声明变量  
BEGIN  
    -- 在这里调用存储过程  
    my_procedure(123);  
      
    -- 其他代码...  
END;  
/

在应用程序中

如果你在 Oracle 应用程序或外部程序中（如 Java、C#、Python 等）调用存储过程，你将使用相应的数据库连接库或 API 来执行存储过程。例如，在 Java 中，你可能使用 JDBC 的 CallableStatement 来调用存储过程。

注意事项

确保你有足够的权限来执行存储过程。
如果存储过程有输出参数或返回值，你需要在调用时适当地处理它们。
如果在存储过程中发生了异常，并且你没有在存储过程中捕获和处理它们，那么调用存储过程的代码块也可能会抛出异常。确保你的调用代码能够适当地处理这些异常。
在某些情况下，你可能需要设置 DBMS_OUTPUT.ENABLE（在 SQL*Plus 中）来查看由存储过程中的 DBMS_OUTPUT.PUT_LINE 语句产生的输出。但是，请注意，DBMS_OUTPUT 主要用于调试目的，并不总是适用于生产环境。

查询存储过程

在 Oracle 数据库中，要查询已创建的存储过程（Stored Procedure），你可以查询数据字典视图，如 USER_PROCEDURES、ALL_PROCEDURES 或 DBA_PROCEDURES。这些视图包含了关于数据库中存储过程、函数、包等的信息。

查询当前用户下的存储过程

如果你只想查询当前用户下的存储过程，可以使用 USER_PROCEDURES 视图：

SELECT PROCEDURE_NAME, PROCEDURE_TYPE, EXECUTABLE, STATUS  
FROM USER_PROCEDURES  
WHERE PROCEDURE_TYPE = 'PROCEDURE'; -- 过滤出存储过程（排除函数等）

查询所有可访问的存储过程

如果你想查询你有权限访问的所有存储过程（包括其他用户的），可以使用 ALL_PROCEDURES 视图：

SELECT OWNER, PROCEDURE_NAME, PROCEDURE_TYPE, EXECUTABLE, STATUS  
FROM ALL_PROCEDURES  
WHERE PROCEDURE_TYPE = 'PROCEDURE'; -- 过滤出存储过程（排除函数等）

查询数据库中的所有存储过程（DBA 权限）

如果你有 DBA 权限，你可以查询 DBA_PROCEDURES 视图来获取数据库中所有的存储过程信息：

SELECT OWNER, PROCEDURE_NAME, PROCEDURE_TYPE, EXECUTABLE, STATUS  
FROM DBA_PROCEDURES  
WHERE PROCEDURE_TYPE = 'PROCEDURE'; -- 过滤出存储过程（排除函数等）

查询存储过程的源代码（如果有权限）

如果你还有权限查看存储过程的源代码（比如你是存储过程的拥有者或者你有足够的权限），你可以查询 USER_SOURCE、ALL_SOURCE 或 DBA_SOURCE 视图来获取源代码。但是，这些视图包含了所有 PL/SQL 对象的源代码，包括存储过程、函数、包等，因此你需要通过其他列（如 NAME、TYPE、LINE）来定位存储过程的源代码。

例如，要查询当前用户下一个名为 my_procedure 的存储过程的源代码，你可以使用：

SELECT TEXT  
FROM USER_SOURCE  
WHERE NAME = 'MY_PROCEDURE'  
ORDER BY LINE;

请注意，Oracle 中的名称通常是大写的，但查询时不区分大小写（除非在创建时使用了双引号）。

此外，Oracle 还提供了数据字典视图 DBMS_METADATA.GET_DDL 来获取对象的 DDL（数据定义语言）语句，包括存储过程的创建语句。例如：

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'MY_PROCEDURE')   
FROM DUAL;

这将返回创建 MY_PROCEDURE 存储过程的完整 DDL 语句。同样，你需要有足够的权限来执行这个查询。