Here are findings regarding Oracle 11g, here are with
complete examples.
1)
Seudo Column in Tables - apply any
complex formula here
in this example: update Sale_category column based on value
of sales_amt directly
without user interaction, its like a DEFAULT value on column
---------------------------------------------------------------------------------
create table hr.sales
(
sales_id number,
cust_id
number,
sales_amt number,
sale_category varchar2(6)
generated always as
(
case
when
sales_amt <= 10000
then 'LOW'
when
sales_amt > 10000
and sales_amt <= 100000
then case
when cust_id < 101 then 'LOW'
when cust_id between 101 and 200 then 'MEDIUM'
else 'MEDIUM'
end
when
sales_amt > 100000
and sales_amt <= 1000000
then case
when cust_id < 101 then 'MEDIUM'
when cust_id between 101 and 200 then 'HIGH'
else 'ULTRA'
end
else
'ULTRA'
end
) virtual
);
insert into hr.sales (sales_id, cust_id, sales_amt) values
(1,1,100);
insert into hr.sales (sales_id, cust_id, sales_amt) values
(2,101,100001);
insert into hr.sales (sales_id, cust_id, sales_amt) values
(3,102,1500);
insert into hr.sales (sales_id, cust_id, sales_amt) values
(4,102,10000);
insert into hr.sales (sales_id, cust_id, sales_amt) values
(5,101,100000);
insert into hr.sales (sales_id, cust_id, sales_amt) values
(5,101,1000000);
insert into hr.sales (sales_id, cust_id, sales_amt) values
(6,102,10000000);
select * from hr.sales;
SALES_ID CUST_ID SALES_AMT
SALE_C
---------- ---------- ---------- ------
1
1 100 LOW
2 101 100001
HIGH
3
102 1500 LOW
4 102
10000 LOW
5 101 100000
MEDIUM
5 101 1000000 HIGH
6 102 10000000 ULTRA
7 rows selected.
drop table hr.sales;
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
2)
Using a Constraint to Enforce a Phone
Number Format with Regular Expression
Regular expressions are useful for enforcing constraints—for
example, to ensure that phone numbers are entered into the
database in a standard format. Example 3-1 creates a
contacts table and adds a CHECK constraint to the p_number column to
enforce the below format model:
(XXX) XXX-XXXXXXX
(092) 333-1234567
CREATE TABLE hr.contacts (
cust_name VARCHAR2(30),
cust_mobile VARCHAR2(30)
CONSTRAINT c_contacts_pnf
CHECK (REGEXP_LIKE (cust_mobile, '^\(\d{3}\)
\d{3}-\d{7}$'))
);
test entering wrong format in cust_mobile column
------------------------------------------------
HR.@.xe > insert into
hr.contacts values ('My Customer 1','123456789');
insert into hr.contacts values ('My Customer 1','123456789')
*
ERROR at line 1:
ORA-02290: check constraint (HR.C_CONTACTS_PNF) violated
HR.@.xe > insert into
hr.contacts values ('My Customer 1','(092) 333-123456');
insert into hr.contacts values ('My Customer 1','(092)
333-123456')
*
ERROR at line 1:
ORA-02290: check constraint (HR.C_CONTACTS_PNF) violated
now make a correct entry ...
----------------------------
1* insert into hr.contacts values ('My Customer
1','(092) 333-1234567')
1 row created.
commit;
