oracle stored procedure execution sql

In Oracle Database, a stored procedure is a set of predefined SQL statements and program logic that can be executed under specific input conditions. Stored procedures can be written based on business requirements and then stored in the database. Stored procedures are useful in a variety of business scenarios, such as batch data processing, logging, automated processes, etc. This article will mainly discuss how to write and execute SQL statements in Oracle stored procedures.

Writing Oracle stored procedures

Creating stored procedures

Creating stored procedures requires the use of the CREATE PROCEDURE statement. The specific syntax is as follows:

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
IS
[local_variable_declarations;]
BEGIN
    executable_statements
    [EXCEPTION
        exception_handler
        [exception_handler, ...]]
END [procedure_name];

Among them, procedure_name is the name of the stored procedure, parameter_name is the name of the input, output, or input-output parameter, type is the data type of the parameter, local_variable_declarations is local The declaration statement of the variable, executable_statements is the main part of the stored procedure, and exception_handler is the exception handler. Inside a stored procedure, you can use SQL statements to perform database operations.

Example

The following is an example of a simple stored procedure for inserting new records in the employee table:

CREATE OR REPLACE PROCEDURE add_employee (employee_id IN NUMBER, name IN VARCHAR2, salary IN NUMBER)
IS
BEGIN
    INSERT INTO employee (employee_id, name, salary)
    VALUES (employee_id, name, salary);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END add_employee;

This stored procedure has 3 input parameters, namely employee_id, name, salary. Within the body of the stored procedure, use the INSERT INTO statement to insert the input values ​​into the employee table. Finally, use the COMMIT statement to commit the changes. If the insert operation fails, use the ROLLBACK statement to roll back to the state at the beginning of the transaction and use the RAISE statement to rethrow the exception.

Calling a stored procedure

To call a stored procedure, you can use the CALL statement. The specific syntax is as follows:

CALL procedure_name;

When calling a stored procedure, you can pass input parameters. The following is a Example:

CALL add_employee(123, 'John', 5000);

This will call the add_employee stored procedure, passing the values ​​123, 'John' and 5000 to the input parameters employee_id, name and salary. The execution result will be a new record inserted into the employee table.

Execute SQL statements

Within the stored procedure, you can use SQL statements to perform various operations. Here are some examples of common SQL statements:

SELECT statement

The SELECT statement is used to retrieve records from a table. The following is the syntax of the SELECT statement:

SELECT column1, column2, ...
FROM table_name
[WHERE condition];

Among them, column1, column2, ... is the column name to be retrieved, table_name is the table name to be retrieved, [WHERE condition] is an optional search condition. For example, the following SELECT statement is used to retrieve all records in the employee table:

SELECT *
FROM employee;

INSERT INTO statement

The INSERT INTO statement is used to insert new records into the table. The following is the syntax of the INSERT INTO statement:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Among them, table_name is the name of the table to insert records, column1, column2, ... is the data to be inserted The columns, value1, value2, ... are the values ​​to be inserted. For example, the following INSERT INTO statement inserts new records into the employee table.

INSERT INTO employee (employee_id, name, salary)
VALUES (123, 'John', 5000);

UPDATE statement

The UPDATE statement is used to update records in a table. The following is the syntax of the UPDATE statement:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition];

Among them, table_name is the name of the table to be updated, column1 = value1, column2 = value2, ... is the name of the table to be updated. columns and corresponding values, WHERE condition is an optional update condition. For example, the following UPDATE statement updates the salary value to 6000 for the record name in the employee table to 6000:

UPDATE employee
SET salary = 6000
WHERE name = 'John';

DELETE statement

The DELETE statement is used to delete records from a table. The following is the syntax of the DELETE statement:

DELETE FROM table_name
[WHERE condition];

where table_name is the name of the table to delete records, WHERE condition is the optional deletion condition. For example, the following DELETE statement is used to delete all records in the employee table with a salary value less than 5000:

DELETE FROM employee
WHERE salary < 5000;

Conclusion

In Oracle database, Stored procedures are very useful tools for executing predefined SQL statements and program logic based on specific input conditions. Writing stored procedures is a complex and challenging task, but as long as they are written according to specifications, stored procedures can greatly simplify the tasks of database development and management. Executing SQL statements is the core part of stored procedures. It requires proficiency in various SQL statements and understanding how to embed them into stored procedures.

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