Methods for creating virtual columns and composite triggers for Oracle data _oracle

This article focuses on the Oracle database virtual columns and composite triggers related knowledge, including the creation of virtual columns and composite triggers, and gave the creation of examples, let us understand the process together.

One, virtual columns

Oralce's virtual columns address the many data information that was previously needed to use triggers or to compute statistics by code. Every time that you count other columns, the new columns are generated by adding new columns in the SELECT statement by means of statistical calculations, which are inefficient, and are prone to errors because of the lengthy and complex query SQL statements. Seriously reduces the development efficiency and the execution efficiency of the program. The introduction of Oralce virtual columns solves this problem.

There are also some problems with oralce virtual columns. You cannot use INSERT into talbe_name values (). When you add data to a table that contains virtual columns, you need to write the column names of the added table for the INSERT statement. The virtual column name cannot appear in the INSERT INTO table_name (LIST1,LIST2,... listend) column name, otherwise an error is prompted.

The example of 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 as (case when 
 
sales_amt <= 10000 Then ' low ' when 
 
sales_amt > 100 Sales_amt <= 100000 Then ' MEDIUM ' when 
 
sales_amt > 100000 and Sales_amt <= 1000000 ' high ' else ' The ULTRA ' End ' 
 
virtual//* The content of the dummy column value function * * 
 
); 

The virtual column is specified as "generated always as", which means that the column values are generated at run time, not as part of the table. This clause is followed by a method that evaluates the value in a detailed case statement. Finally, "virtual" is specified to strengthen the fact that this is a virtual column.

Second, composite trigger

Composite triggers are actually four different triggers that are defined as a whole. For example, the UPDATE composite trigger merges before statement, before row, after statement, and after row into a composite trigger. This is a single piece of code, so you can pass variables like any other single pl/sql code.

Here we refer to an example to help illustrate. Note: A table sales_log is created that is the same as the sales table.

Create or replace trigger tri_sales for update ' 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 the begin Dbms_output.put_line (' in before statement '); 
 
End Before statement; 
 
Before each row are 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 the 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 over each row; 
 
After statement are begin Dbms_output.put_line (' in after statement '); ForAll counter in 1..coll_sales_log.count () inserts 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; 
 /* Cannot use INSERT into Sales_log values (Coll_sales_log (counter)), add/* end tri_sales;

The above example contains 4 different triggering forms, which are executed independently.

Note that there are virtual columns in the Sales_log table that you cannot use INSERT into Sales_log values (Coll_sales_log (counter)), and that we need to indicate the names of the columns in the table when we add them directly. Otherwise, there will be an error.

About Oracle Data create virtual columns and composite triggers here, I hope this introduction can be harvested for you!