oracle sql executes stored procedure

In Oracle database, a stored procedure is a predefined set of SQL statements that can be executed when needed. Unlike SQL statements written at any time, stored procedures are more efficient, can reduce network traffic during data access, and improve system performance.

In actual applications, we may need to perform some complex database operations, which can be achieved by calling stored procedures. Oracle database provides simple and clear syntax to create stored procedures. Users can customize information such as parameters and return values ​​according to needs, and can also set up process control statements, exception handling, transaction processing and other functions.

This article will use a simple example to introduce how to execute stored procedures in Oracle database.

1. Create stored procedures

In Oracle database, creating stored procedures requires the use of PL/SQL language, which is Oracle's specialized process for stored procedures, triggers and functions. The language in which it is written. The following is a basic syntax for creating a stored procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
IS
    [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END [procedure_name];

Description:

• CREATE [OR REPLACE]: Create a stored procedure.
• procedure_name: The name of the stored procedure that needs to be created.
• parameter_name: The parameter name of the stored procedure, which can be IN type, OUT type or IN OUT type. IN represents input parameters and OUT represents output parameters.
• type: Data type of parameter.
• declaration_section: Declare variables, cursors, constants, etc.
• executable_section: The actual execution part of the stored procedure.
• exception_section: exception handling code block.

The following is a simple example to create a stored procedure to query the information of a specified employee number:

CREATE OR REPLACE PROCEDURE get_employee_info (p_empno IN NUMBER)
IS
    v_ename employees.ename%TYPE;
    v_job employees.job%TYPE;
    v_salary employees.sal%TYPE;
BEGIN
    SELECT ename, job, sal INTO v_ename, v_job, v_salary FROM employees WHERE empno = p_empno;
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('Job: ' || v_job);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
END;

2. Execute the stored procedure

After creating a stored procedure, we can execute it in a variety of ways. The following are some alternative methods:

2.1 Use PL/SQL development tools to execute

You can use the PL/SQL development tools provided by Oracle to execute stored procedures. After opening the tool, connect to the database instance that needs to be executed, find the created stored procedure, right-click and select "Execute Procedure", enter the required parameters to execute the stored procedure.

2.2 Use SQL*Plus to execute

You can also use the command line tool SQL*Plus provided by Oracle to execute stored procedures. After connecting to the database instance that needs to be executed, run the following command to execute the stored procedure:

EXEC procedure_name (parameter_value);

For example, to execute the stored procedure created above, you can use the following command:

EXEC get_employee_info (100);

2.3 Using Java Program Execution

When the business logic of the database is relatively complex, we can call stored procedures through Java programs to implement more advanced business logic. The following is a simple Java program for calling the stored procedure created above:

import java.sql.*;

public class ExecuteProcedure {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String user = "username";
        String password = "password";
        try {
            Connection conn = DriverManager.getConnection(url, user, password);
            CallableStatement cst = conn.prepareCall("{call get_employee_info(?)}");
            cst.setInt(1, 100);
            cst.execute();
            conn.close();
        } catch(SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

The program first connects to the database and then uses Java's CallableStatement class to call the stored procedure. Before calling the stored procedure, you need to set the parameter values. When executing the stored procedure, the program will output relevant information about employee number 100.

Summary

The above is the basic introduction to executing stored procedures in Oracle database. Stored procedures are a powerful and efficient tool that can promote database performance optimization and improve development efficiency to a certain extent. At the same time, it also requires users to master the PL/SQL language in order to better utilize stored procedures for data processing.

The above is the detailed content of oracle sql executes stored procedure. For more information, please follow other related articles on the PHP Chinese website!