-- virtual columns

ALTER TABLE employees 
ADD column_y DATE
GENERATED ALWAYS AS
(CAST( AS DATE));

ALTER TABLE employees 
ADD column_y NUMBER(9,2)
GENERATED ALWAYS AS
(CAST( AS NUMBER(9,2)));

ALTER TABLE employees 
ADD column_y VARCHAR2(50)
GENERATED ALWAYS AS
(CAST( AS VARCHAR2(50)));

SAGE@sw11g> @desc employees
 Name                           Null?    Type
 ------------------------------ -------- ---------------
 EMPLOYEE_ID                    NOT NULL NUMBER(6)
 FIRST_NAME                              VARCHAR2(20)
 LAST_NAME                      NOT NULL VARCHAR2(25)
 EMAIL                          NOT NULL VARCHAR2(50)
 PHONE_NUMBER                            VARCHAR2(20)
 HIRE_DATE                      NOT NULL DATE
 JOB_ID                         NOT NULL VARCHAR2(10)
 SALARY                                  NUMBER(8,2)
 COMMISSION_PCT                          NUMBER(2,2)
 MANAGER_ID                              NUMBER(6)
 DEPARTMENT_ID                           NUMBER(4)

--*** Date fun
ALTER TABLE employees 
ADD days_since_hire NUMBER
GENERATED ALWAYS AS
(CAST(SYSDATE-hire_date AS NUMBER));

-- ORA-54002: only pure functions can be specified in a virtual column expression

CREATE OR REPLACE FUNCTION date_diff(p_date DATE)
RETURN NUMBER AS
BEGIN
 RETURN(TRUNC(SYSDATE)-p_date);
END;
/

ALTER TABLE employees 
ADD days_since_hire NUMBER
GENERATED ALWAYS AS
(CAST(date_diff(hire_date) AS NUMBER));

-- ORA-30553: The function is not deterministic

CREATE OR REPLACE FUNCTION date_diff(p_date DATE)
RETURN NUMBER DETERMINISTIC AS
BEGIN
 RETURN(TRUNC(SYSDATE)-p_date);
END;
/

ALTER TABLE employees 
ADD days_since_hire NUMBER
GENERATED ALWAYS AS
(CAST(date_diff(hire_date) AS NUMBER));

SELECT SYSDATE, days_since_hire, hire_date
FROM employees 
WHERE employee_id = 195;

SYSDATE             DAYS_SINCE_HIRE
------------------- ---------------
13-05-2009 20:58:44            3710

1 row selected.

ALTER TABLE employees DROP COLUMN days_since_hire;

--*** Calculated column (a)
ALTER TABLE employees 
ADD income
GENERATED ALWAYS AS
( CAST((salary + NVL(salary*commission_pct,0)) AS NUMBER(9,2)));

ALTER TABLE employees 
ADD CONSTRAINT income_max 
CHECK (income < 25000);

insert into employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
values(99,'Scott','Wesley','scott@sagecomputing.com.au','0431858449',sysdate-100,'IT_PROG',24000,0.25,100,100);

-- ORA-12899: value too large for column "HR8"."EMPLOYEES"."EMAIL" (actual: 26, maximum: 25)

ALTER TABLE employees DROP CONSTRAINT income_max ;

--*** Calculated column (b)
ALTER TABLE employees 
ADD formatted_name
GENERATED ALWAYS AS
( CAST(UPPER(last_name)||', '||INITCAP(first_name) AS VARCHAR2(46))) VIRTUAL;

SELECT first_name, last_name, formatted_name
FROM employees SAMPLE(10)
;

ALTER TABLE employees 
ADD surname
GENERATED ALWAYS AS
(CAST(REGEXP_REPLACE(formatted_name,'(^\w*) (\w*)','\1') AS VARCHAR2(25)));

-- ORA-54012: virtual column is referenced in a column expression

--*** Partition

CREATE OR REPLACE FUNCTION income_grade(p_income employees.income%TYPE) 
RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
  RETURN CASE 
    WHEN p_income <= 1000  THEN 'H'
    WHEN p_income <= 5000  THEN 'G'
    WHEN p_income <= 6000  THEN 'F'
    WHEN p_income <= 7000  THEN 'E'
    WHEN p_income <= 8000  THEN 'D'
    WHEN p_income <= 9000  THEN 'C'
    WHEN p_income <= 10000 THEN 'B'
    WHEN p_income <= 20000 THEN 'A'
                           ELSE 'X'
    END;
END income_grade;
/

ALTER TABLE employees 
ADD income_grade
GENERATED ALWAYS AS
( CAST(income_grade(income) AS VARCHAR2(1)));

-- ORA-54012: virtual column is referenced in a column expression

ALTER TABLE employees 
ADD income_grade
GENERATED ALWAYS AS
( CAST(income_grade(salary + NVL(salary*commission_pct,0)) AS VARCHAR2(1)));

-- ORA-54012: virtual column is referenced in a column expression

ALTER TABLE employees 
ADD income_grade2
GENERATED ALWAYS AS
( CAST(income_grade(salary + NVL(salary*commission_pct,0)) AS VARCHAR2(1)));

DROP FUNCTION income_grade;

ALTER TABLE employees DROP COLUMN income_grade2;

-- BE VERY VERY CAREFUL!!
-- ORA-00904: "HR"."INCOME_GRADE": invalid identifier

CREATE OR REPLACE FUNCTION income_grade(p NUMBER) RETURN NUMBER AS BEGIN RETURN 0; END;
/

ALTER TABLE employees DROP COLUMN income_grade2;

CREATE OR REPLACE FUNCTION fn_income_grade(p_income employees.income%TYPE) 
RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
  RETURN CASE 
    WHEN p_income <= 1000  THEN 'H'
    WHEN p_income <= 5000  THEN 'G'
    WHEN p_income <= 6000  THEN 'F'
    WHEN p_income <= 7000  THEN 'E'
    WHEN p_income <= 8000  THEN 'D'
    WHEN p_income <= 9000  THEN 'C'
    WHEN p_income <= 10000 THEN 'B'
    WHEN p_income <= 20000 THEN 'A'
                           ELSE 'X'
    END;
END fn_income_grade;
/

ALTER TABLE employees 
ADD income_grade
GENERATED ALWAYS AS
( CAST(fn_income_grade(1+salary + NVL(salary*commission_pct,0)) AS VARCHAR2(1)));

SELECT formatted_name, income, income_grade 
FROM employees SAMPLE(10) ORDER BY 3;

ALTER TABLE employees DROP COLUMN income_grade;
-- ORA-04020: deadlock detected while trying to lock object HR.EMPLOYEES

CREATE OR REPLACE FUNCTION fn_income_grade(p_income NUMBER) 
RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
  RETURN CASE 
    WHEN p_income <= 1000  THEN 'H'
    WHEN p_income <= 5000  THEN 'G'
    WHEN p_income <= 6000  THEN 'F'
    WHEN p_income <= 7000  THEN 'E'
    WHEN p_income <= 8000  THEN 'D'
    WHEN p_income <= 9000  THEN 'C'
    WHEN p_income <= 10000 THEN 'B'
    WHEN p_income <= 20000 THEN 'A'
                           ELSE 'X'
    END;
END fn_income_grade;
/

CREATE TABLE employees_redefine (
 employee_id                    NUMBER(6)    NOT NULL 
,first_name                     VARCHAR2(20)          
,last_name                      VARCHAR2(25) NOT NULL 
,email                          VARCHAR2(50) NOT NULL 
,phone_number                   VARCHAR2(20)          
,hire_date                      DATE         NOT NULL 
,job_id                         VARCHAR2(10) NOT NULL 
,salary                         NUMBER(8,2)
,commission_pct                 NUMBER(2,2)
,manager_id                     NUMBER(6)
,department_id                  NUMBER(4)
,days_since_hire                NUMBER
,income                         NUMBER(9,2)
,formatted_name                 VARCHAR2(46)
,income_grade                   VARCHAR2(1)
)
PARTITION BY list (income_grade)
(
   PARTITION p_a VALUES ('A'),
   PARTITION p_b VALUES ('B'),
   PARTITION p_c VALUES ('C'),
   PARTITION p_d VALUES ('D'),
   PARTITION p_e VALUES ('E'),
   PARTITION p_f VALUES ('F'),
   PARTITION p_g VALUES ('G'),
   PARTITION p_h VALUES ('H'),
   PARTITION p_x VALUES ('X')
)
/

-- Thanks Connor McDonald for these...
begin
  dbms_redefinition.start_redef_table
   ('HR8','EMPLOYEES','EMPLOYEES_REDEFINE'
   ,options_flag => dbms_redefinition.cons_use_rowid);
end;
/

begin
  dbms_redefinition.finish_redef_table
   ('HR8','EMPLOYEES','EMPLOYEES_REDEFINE');
end;
/

SELECT formatted_name, income, income_grade 
FROM   employees 
PARTITION (p_a)
ORDER BY 2;


--*** Referential Integrity
ALTER TABLE employees 
ADD user_id VARCHAR2(25)
GENERATED ALWAYS AS
(CAST(SUBSTR(email, 1, INSTR(email, '@')-1) AS VARCHAR2(25)));

SELECT employee_id, email, user_id FROM employees WHERE ROWNUM = 1;

UPDATE employees SET email = LOWER(email)||'@sagecomputing.com.au';

SELECT employee_id, email, user_id FROM employees WHERE ROWNUM = 1;

CREATE TABLE logons AS SELECT user_id FROM employees;
ALTER TABLE logons MODIFY user_id NOT NULL;
ALTER TABLE logons ADD PRIMARY KEY  (user_id);

ALTER TABLE employees 
ADD CONSTRAINT email_fk
FOREIGN KEY (user_id)
REFERENCES logons (user_id)
/
 
UPDATE employees SET email = 'xyz@com' WHERE employee_id = 201;

-- ORA-02291: integrity constraint (HR.EMAIL_FK) violated - parent key not found

UPDATE employees SET email = 'xyz' WHERE employee_id = 201;

-- happy (because user_id is now null, no "@" to be found

UPDATE employees SET email = 'xyz' WHERE employee_id = 205 RETURNING user_id INTO :c;

print :c
shiggins -- wrong! Not post change. This should be null.

DECLARE
  l_user_id VARCHAR2(20);
  l_email  VARCHAR2(50);
BEGIN
  UPDATE employees 
  SET    email = 'xyz' 
  WHERE  employee_id = 205 
  RETURNING user_id, email
  INTO l_user_id, l_email;
  dbms_output.put_line('l_user_id:'||l_user_id);
  dbms_output.put_line('l_email:'||l_email);
END;
/

l_user_id:shiggins -- not post change
l_email:xyz -- post change

INSERT INTO logons VALUES ('xyz');

UPDATE logons SET user_id = 'abc' WHERE user_id = 'xyz' RETURNING user_id INTO :c1;

print :c1

-- inconsistent. The virtual column value delivered via RETURNING clause can't be trusted