How to create virtual columns and composite triggers for Oracle databases

This article mainly introducesOracle DatabaseOfVirtual ColumnAndCompound triggerRelated knowledge, including the creation of virtual columns and composite triggers, and examples of creation are provided. Let's take a look at this process.

I. Virtual Columns

Oralce's virtual column solves a lot of data information that can be generated only when triggers or code is used for calculation and statistics. In the past, when other columns were counted and new columns were generated, the new columns were added through Statistical Computation in the select statement, resulting in low execution efficiency, in addition, the query SQL statements become lengthy and complex and prone to errors. It seriously reduces the development efficiency and program execution efficiency. The introduction of Oralce virtual columns solves this problem.

There are also some problems with the virtual columns of Oralce. The insert into talbe_name values (). statement cannot be used. When adding data to a table containing virtual columns, the column name of the added table must be written in the insert statement. The insert into table_name (list1, list2,... listend) column name cannot contain virtual column names. Otherwise, an error is prompted.

The instance for creating a virtual column is as follows:

 
 

  
  
Create table sales
  
  
 
  
  
(
  
  
 
  
  
Sales_id number,
  
  
 
  
  
Cust_id number,
  
  
 
  
  
Sales_amt number,
  
  
 
  
  
Sale_category varchar2 (6)/* virtual column name and data type */
  
  
 
  
  
Generated always
  
  
 
  
  
(
  
  
 
  
  
Case when sales_amt <= 10000 then 'low'
  
  
 
  
  
When sales_amt> 10000 and sales_amt <= 100000 then 'medium'
  
  
 
  
  
When sales_amt> 100000 and sales_amt <= 1000000 then 'high' else' ULTRA 'end
  
  
 
  
  
) Virtual/* virtual column value function content */
  
  
 
  
  
);
 
 

The virtual column is specified as "generated always as", which means that the column value is generated at runtime rather than stored as part of the table. The clause is followed by a detailed CASE statement to calculate the value. Finally, the fact that "virtual" is specified to enhance this is a virtual column.

Ii. Compound triggers

Composite triggers are actually four different triggers defined as a whole. For example, the UPDATE composite trigger combines before statement, before row, after statement, and after row into a composite trigger. This is a single code snippet, so you can pass variables like any other single PL/SQL code.

Here is an example to help illustrate. Note: A sales_log table with the same sales table is created.

 
 

  
  
Create or replace trigger tri_sales
  
  
 
  
  
For update of sales_amt on SALES
  
  
 
  
  
Compound trigger
  
  
 
  
  
Type ty_sales_log is table of sales_log % rowtype
  
  
 
  
  
Index by pls_integer;
  
  
 
  
  
Coll_sales_log ty_sales_log;
  
  
 
  
  
Ctr pls_integer: = 0;
  
  
 
  
  
Before statement is
  
  
 
  
  
Begin
  
  
 
  
  
Dbms_output.put_line ('in before statement ');
  
  
 
  
  
End before statement;
  
  
 
  
  
Before each row is
  
  
 
  
  
Begin
  
  
 
  
  
Dbms_output.put_line ('in before each row ');
  
  
 
  
  
End before each row;
  
  
 
  
  
After each row is
  
  
 
  
  
Begin
  
  
 
  
  
Ctr: = ctr + 1;
  
  
 
  
  
Dbms_output.put_line ('in after each row. sales_amt '|: new. sales_amt );
  
  
 
  
  
Coll_sales_log (ctr). sales_id: =: old. sales_id;
  
  
 
  
  
Coll_sales_log (ctr). cust_id: =: old. cust_id;
  
  
 
  
  
Coll_sales_log (ctr). sales_amt: =: new. sales_amt;
  
  
 
  
  
End after each row;
  
  
 
  
  
After statement is
  
  
 
  
  
Begin
  
  
 
  
  
Dbms_output.put_line ('in after statement ');
  
  
 
  
  
Forall counter in 1 .. coll_sales_log.count ()
  
  
 
  
  
Insert into sales_log (sales_id, cust_id, sales_amt)
  
  
 
  
  
Values (coll_sales_log (counter). sales_id, coll_sales_log (counter). cust_id, coll_sales_log (counter). sales_amt );
  
  
 
  
  
End after statement;/* The Insert into sales_log values cannot be used.
  
  
 
  
  
(Coll_sales_log (counter); add */
  
  
 
  
  
End tri_sales;
 
 

The above example contains four different trigger forms, which are executed independently.

Note that:If a virtual Column exists in the sales_log table, Insert into sales_log values (coll_sales_log (counter) cannot be used. To add a virtual column directly, you must specify the column name in the table. Otherwise, an error occurs.

This article introduces the knowledge of Oracle Database Virtual columns and composite triggers. If you want to learn more about Oracle databases, please visit http://database.51cto.com/oracle/, and I believe you can bring them to us!