PL/SQL triggers for Oracle databases, rownum, dynamic SQL, database views and indexes

The When clause describes the triggering constraint condition. When condition is a logical expression, it must contain a correlation name, not a query statement, or a PL/SQL function. The trigger constraint specified by the When clause can only be used in before and after row triggers, not in instead of row triggers and other types of triggers.

--Create a table that records action events

CREATE TABLE event_table (

Event VARCHAR2 (50),

Time DATE

);

--Create a trigger

CREATE OR REPLACE TRIGGER tr_startup

After STARTUP

On DATABASE

BEGIN

INSERT into Event_table (event, time)

VALUES (Ora_sysevent, sysdate);

END;

1. The rownum cannot be used with > (values greater than 1), >= (values greater than 1), = (values greater than 1), otherwise no result.

2. When using rownum, the query results are sorted and then evaluated rownum only if the order by field is the primary key, but when you sort the non-primary key fields (for example: name), the result may

It's confusing. The reason for this confusion is that Oracle takes the physical storage location (ROWID) order out of the records that meet the rownum condition, that is, the first 5 data in the physical location, and then sorts the data according to the order by field, rather than the first sort, and then the number of specific records we expect.

There are typically three different types of dynamic SQL methods that are executed:

1. Use the Execute IMMEDIATE statement.
   In addition to not being able to handle multiline query statements, other dynamic SQL includes DDL statements, DCL statements, and single-row select queries.
2. REF cursor dynamic cursor, using Open-for,fetch,close.
   To handle dynamic multi-row query operations, you must use the OPEN-FOR statement to open the cursor, use a FETCH statement to iterate through the data, and finally close the cursor with the close statement.
3. Use bulk bulk collect to execute dynamic SQL.
   By using bulk dynamic SQL statements, you can speed up the processing of SQL statements, which in turn improves the performance of PL/A.

1. Introduction to the view

A view is a logical table that is based on a table or multiple tables or views, which itself does not contain data that can be queried and modified by the data in the table.

2. Create a view

3. Advantages of the view

4. Index

In order to improve the speed of the query, when the user is not satisfied with the query speed and needs to adjust the performance of the database, it is preferred to establish an index.

PL/SQL triggers for Oracle databases, rownum, dynamic SQL, database views and indexes