How to create and call Oracle stored procedures

In the Oracle database, a stored procedure is a precompiled program unit that can be created and stored in the database for subsequent calls. Stored procedures can receive input parameters and return output parameters, and can have conditional statements and loop structures, similar to functions or subroutines in programming languages.

When using Oracle stored procedures, we need to create the stored procedures first and then call them. The following will introduce how to create and call Oracle stored procedures.

Creating a stored procedure

1. Creating a stored procedure requires creating a PL/SQL block code in the Oracle database. Stored procedures can be created using Oracle SQL development tools or the command line.
   The following is a simple stored procedure example:

CREATE OR REPLACE PROCEDURE example_proc(
  p_input1 IN VARCHAR2,
  p_input2 IN NUMBER,
  p_output OUT NUMBER)
AS
BEGIN
  -- 存储过程代码
  p_output := p_input1 || p_input2;
END;

In this example stored procedure, we declare three parameters: two input parameters (p_input1 and p_input2), one output parameter ( p_output). In the code block of the stored procedure, we concatenate the values ​​of the input parameters and assign them to the output parameters.

2. After creating the stored procedure, execute the stored procedure by calling it. Stored procedures can be called using Oracle SQL development tools or the command line.

Calling stored procedures

1. Calling stored procedures through Oracle SQL Developer

You can use Oracle SQL Developer to call stored procedures. The following are the steps on how to call a stored procedure in Oracle SQL Developer:

• Open the Oracle SQL Developer tool
• Connect to the Oracle database
• Enter the following in the SQL window Sample code:

DECLARE
  p_input1 VARCHAR2(10) := 'Hello';
  p_input2 NUMBER := 1;
  p_output NUMBER;
BEGIN
  example_proc(p_input1, p_input2, p_output);
  dbms_output.put_line('Output value is ' || p_output);
END;

In this code block, we first declare three variables, corresponding to the three parameters of the stored procedure. Next, the stored procedure is executed and the values ​​of the output parameters are output.

• Click the Execute button (or press the F5 key) to execute this code block.

2. Calling stored procedures through SQL*Plus

You can use the SQLPlus command line tool to call stored procedures. The following are the steps on how to call a stored procedure in SQLPlus:

• Open the command line interface
• Connect to the Oracle database
• Enter the following sample code:

VARIABLE output_num NUMBER;
EXECUTE example_proc('Hello', 1, :output_num);
PRINT output_num;

In this code block, we declare an output parameter variable (output_num) using VARIABLE. Next, use the EXECUTE command to execute the stored procedure and store the value of the output parameter in the output_num variable. Finally, use the PRINT command to print the value of the output parameter.

Summary

Through this article, we introduced the basic working principles of Oracle stored procedures and how to create and call them. In practical applications, stored procedures can help us reduce duplicate code and improve database processing performance.

The above is the detailed content of How to create and call Oracle stored procedures. For more information, please follow other related articles on the PHP Chinese website!