What are triggers and stored procedures in oracle

In Oracle, a stored procedure is a set of SQL statements that are used to complete specific functions and are stored in the database. The stored procedure can be used repeatedly to reduce the workload of developers; while triggers are provided by the database to programmers and data One way for analysts to ensure data integrity is through special stored procedures related to table events.

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

What is a trigger in oracle

Trigger is provided by the database to programmers and data analysts to ensure data integrity A method that is a special stored procedure related to table events. Triggered by events.

A trigger is a SQL statement that is implicitly executed when a certain event is triggered. The trigger cannot receive parameters. Oracle triggers trigger insert, update, delete operations on the database or similar operations on views in the Oracle database, as well as some system events, such as the closing or opening of the database.

What are the classifications of triggers?

(1), Data Manipulation Language (DML) trigger: a trigger created on the table and triggered by DML time;

(2), instead of ) Trigger: Created on the view, used to replace adding, modifying and deleting operations on the view.

(3) Data definition language (DDL) trigger: defined on the schema, the triggering event is the creation and modification of database objects.

(4) Database system trigger: It is defined on the entire database, and the trigger time is the operation of the database, such as the startup and shutdown of the database.

What are the components of a trigger?

(1) Triggered events: that is, under what circumstances the trigger is triggered, such as: insert, update, delete.

(2) Trigger time: that is, whether the trigger is before the trigger event (before) or after the trigger event (after), and also the order of the triggered event and the trigger body.

(3) The trigger itself: the operation to be performed by the trigger when the event is triggered, for example: pl/sql block.

(4) Trigger frequency: Indicates the number of times the action defined in the trigger is executed. That is, statement-level triggers and row-level triggers. A statement-level trigger means that the trigger is executed only once when an event occurs. As for row-level triggers, when an event occurs, the trigger will be executed separately for each row of data affected by the operation.

What is a stored procedure in oracle

Stored Procedure (Stored Procedure) is a group of procedures in large database systems that are used to complete specific functions. The set of SQL statements is stored in the database. After being compiled for the first time, it does not need to be compiled again when called again. The user calls the stored procedure by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters).

To put it simply, it is a SQL statement that does one thing specifically.

Stored procedures can be used repeatedly, reducing developer workload.

The stored procedure in the Oracle database is procedure.

Why write stored procedures

1. High efficiency

After the stored procedure is compiled once, it will be stored in the database and will be saved every time it is called Execute directly. If we want to save ordinary SQL statements to other places (such as Notepad), they must be analyzed and compiled before execution. So I think stored procedures are more efficient.

2. Reduce network traffic

The compiled stored procedure will be placed in the database. When we call it remotely, we will not transmit a large number of string type SQL statements.

3. High reusability

Stored procedures are often written for a specific function. When this specific function needs to be completed, the stored procedure can be called again.

4. High maintainability

When there are small changes in functional requirements, it is easier to modify the previous stored procedures and takes less effort.

5. High security

A stored procedure that completes a specific function can generally only be used by specific users and has usage identity restrictions, making it safer.

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of What are triggers and stored procedures in oracle. For more information, please follow other related articles on the PHP Chinese website!