Oracle

declare
  num number;
begin
  select count(1) into num
    from user_tables
   where table_name = upper('test');

  if num > 0 then
    execute immediate 'drop table test';
  end if;
end;

select *
  from dba_tables
 where owner = upper('ysc')
   and table_name = upper('som_edi_temp_h');

create or replace function edi_div(p1 number, p2 number) 
  return number
is
  l_temp numberx;
begin
  return p1 / p2;
end;

select * from user_errors where name = 'EDI_DIV';

select *
  from all_tab_cols t
 where t.table_name = upper('table_name')
   and t.owner = 'APPS'

select listagg(t.column_name, ',') within group(order by t.segment_column_id)
  from all_tab_cols t
 where t.table_name = upper('table_name')
   and t.owner = 'APPS';

DBMS_METADATA.GET_DDL (
    object_type     IN VARCHAR2,                           --对象类型
    name            IN VARCHAR2,                           --对象名称
    schema          IN VARCHAR2 DEFAULT NULL,              --对象所在的Schema，默认为当前用户所在所Schema
    version         IN VARCHAR2 DEFAULT 'COMPATIBLE',      --对象原数据的版本
    model           IN VARCHAR2 DEFAULT 'ORACLE',          --原数据的类型默认为ORACLE
    transform       IN VARCHAR2 DEFAULT 'DDL')
    RETURN CLOB;                                           --对象的原数据默认以CLOB类型返回

select dbms_metadata.get_ddl('TABLE', 'ASO_BI_QOT_APRB_MV', 'APPS') from dual;
select dbms_metadata.get_ddl('SEQUENCE', 'RA_TRX_NUMBER_1000_S', 'APPS') from dual;
-- 获取所有sequence的定义
select dbms_metadata.get_ddl('SEQUENCE', u.object_name)
  from user_objects u
 where object_type='SEQUENCE';
 
--The output not include terminators. 
BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
select DBMS_METADATA.get_ddl('TABLE', 'EDI_T') as output from dual;

select DBMS_METADATA.get_dependent_ddl('INDEX','SOM_EDI_856_HEADER','YSC') as output from dual;

select DBMS_METADATA.get_xml('TABLE', 'EDI_T') as output from dual;

create table som_edi_861_header (
  header_id       number not null primary key,
  document_number varchar2(30),
  document_date   varchar2(8),
  received_date   varchar2(8),
);

alter table som_edi_861_header
  add constraint som_edi_861_header_pk primary key (header_id);

create index som_edi_861_header_n1 on som_edi_861_header(document_number);

RENAME TABLE SAMP.EMP_ACT TO EMPLOYEE_ACT

flashback table hr.regions_hist to before drop;

create table edi_emp(
  eno number,
  ename varchar2(1000),
  sal number
);

insert into edi_emp values (1, 'Babb', 2000);
insert into edi_emp values (2, 'Babb Chen', 9000);

alter table edi_emp read only;
insert into edi_emp values (3, 'Babbb Chen', 9000); --ORA-12081: update operation not allowed on table "APPS"."EDI_EMP"

alter table edi_emp read write;
insert into edi_emp values (3, 'Babbb Chen', 9000);

create or replace trigger edi_block_dml_on_emp before
 insert or update or delete on edi_emp
begin
  raise_application_error(-20001, 'DML Operation not allowed on EMP');
end;

insert into edi_emp values (4, 'Babbbb Chen', 9000); -- ORA-20001: DML Operation not allowed on EMP

select * from edi_emp; 

grant select on edi_emp to ysc;  

--use ysc login
select * from apps.edi_emp;

insert into apps.edi_emp values (5, 'Babbbbb Chen', 9000); --ORA-01031: insufficient privileges

create table edi_t(
  c number
);

insert into edi_t values (1);
insert into edi_t values (2);
insert into edi_t values (3);
insert into edi_t values (4);
insert into edi_t values (2);
insert into edi_t values (4);

alter table edi_t add constraint pk1 primary key (c); --ORA-02437: cannot validate (APPS.PK1) - primary key violated


create table edi_exceptions (
  row_id rowid,
  user_name varchar2(30),
  table_name varchar2(30),
  cons_name varchar2(30)
);

alter table edi_t add constraint pk1 primary key (c) exceptions into edi_exceptions; --ORA-02437: cannot validate (APPS.PK1) - primary key violated

select * from edi_exceptions;

select * 
  from edi_t 
 where rowid in (
    select row_id from edi_exceptions
  );

create table edi_emp1(
  empno number primary key,
  ename varchar2(30),
  doj date,
  sal number
);

insert into edi_emp1 values(1, 'Babb', sysdate, 1000);
insert into edi_emp1 values(2, 'Babb', sysdate, null);
insert into edi_emp1 values(3, 'Babb', null, 2000);
insert into edi_emp1 values(4, 'Babbbbbbbbbb Chen', sysdate, 4000);

select * from  edi_emp1;

create table edi_emp2(
  empno number primary key,
  ename varchar2(7),  --edi_emp1 is varchar2(30)
  doj date not null,  --edi_emp1 is nullable
  sal number not null --edi_emp1 is nullable
);

insert into edi_emp2 select * from edi_emp1; --ORA-01400: cannot insert NULL into ("APPS"."EDI_EMP2"."SAL")
select * from edi_emp2;  --0 rows selected

begin
  dbms_errlog.create_error_log(dml_table_name => 'edi_emp2'); --dml_table_name same as target table name
end;

select * from err$_edi_emp2; --0 rows selected

insert into edi_emp2 select * from edi_emp1
log errors into err$_edi_emp2 reject limit unlimited; 

select * from edi_emp2;

select * from err$_edi_emp2;

truncate table err$_edi_emp2;
truncate table edi_emp2;

--------------------------------------------------------
insert into edi_emp2 select * from edi_emp1
log errors into err$_edi_emp2('TEST INSERT') reject limit unlimited; -- TEST INSERT is ORA_ERR_TAG$

select * from err$_edi_emp2;
select * from edi_emp2;
--------------------------------------------------------
update edi_emp2 set sal = null
log errors into err$_edi_emp2('TEST UPDATE') reject limit unlimited;

select * from err$_edi_emp2;
select * from edi_emp2;
---------------------------------------------------------
create or replace trigger edi_row_before_del
before delete on edi_emp2 for each row 
declare
  lv_number number;
begin
  lv_number := 1 / 0;
end;

delete from edi_emp2
log errors into err$_edi_emp2('TEST DELETE') reject limit unlimited;

select * from err$_edi_emp2;
select * from edi_emp2;

drop table edi_emp1;
drop table edi_emp2;
drop table err$_edi_emp2;
drop trigger edi_row_before_del;

create table edi_test(
 a number,
 b number,
 c number
);

insert into edi_test(a, b, c) values (1, 2, 3);

alter table edi_test set unused(c);

select * from edi_test;

alter table edi_test drop unused columns;

create table edi_test(
 a number,
 b number,
 c as (a + b)
);

insert into edi_test(a, b) values (1, 2);
select * from edi_test;

create table edi_t (
  id number,
  name varchar2(100)
);

create or replace procedure alter_table_column(table_name  varchar2,
                                               column_name varchar2,
                                               column_type varchar2) is
begin
  execute immediate 'alter table ' || table_name || ' modify ' ||
                    column_name || ' ' || column_type;
end;

begin
  alter_table_column('edi_t', 'name', 'varchar2(30)');
end;

drop procedure alter_table_column;

declare
  l_tab_name varchar2(30) := 'edi_t';
  l_id       number := 1;
  l_name     varchar2(30) := 'Babb';
  l_sql_stmt varchar2(100);
begin
  l_sql_stmt := 'insert into ' || l_tab_name || ' values (:id, :name)';
  execute immediate l_sql_stmt
    using l_id, l_name;
end;

declare
  l_tab_name     varchar2(30) := 'edi_t';
  l_id           number := 1;
  l_name         varchar2(30) := 'Babb Chen';
  l_sql_stmt     varchar2(100);
  l_updated_name varchar2(30);
begin
  l_sql_stmt := 'update ' || l_tab_name ||
                ' set name = :1 where id = :2 returning name into :3';
  execute immediate l_sql_stmt
    using l_name, l_id
    returning into l_updated_name;
  dbms_output.put_line(l_updated_name);
end;

create or replace procedure edi_update_sal(p_empno number,
                                           p_sal   varchar2,
                                           p_name  out varchar2,
                                           p_job   out varchar2) is
  l_sql_stmt varchar2(100);
begin
  l_sql_stmt := 'update edi_t set sal = :1 where empno = :2 returning ename, job into :3, :4';
  execute immediate l_sql_stmt
    using p_sal, p_empno
    returning into p_name, p_job;
end;

declare
  l_sql_block varchar2(100);
  l_name      varchar2(30);
  l_job       varchar2(30);
begin
  l_sql_block := 'begin edi_update_sal(:empno,:sal,:name,:job); end;';
  execute immediate l_sql_block
    using in 7369, 9999, out l_name, out l_job;
  dbms_output.put_line(l_name || ':' || l_job);
end;

declare
  type ref_cursor is ref cursor;
  emp_rec_cur ref_cursor;
  emp_rec edi_t%rowtype;
  l_sql_stmt varchar2(100);
begin
  l_sql_stmt := 'select * from edi_t where deptno = :1';
  open emp_rec_cur for l_sql_stmt using 10;
  loop
    fetch emp_rec_cur into emp_rec;
    exit when emp_rec_cur%notfound;
    dbms_output.put_line(emp_rec.empno || '-' || emp_rec.ename);
  end loop;
  close emp_rec_cur;
end;

declare
begin
  execute immediate 'update edi_t set mgr = :1 where empno = :2'
    using null, 7369; --PLS-00457: expressions have to be of SQL types
end;

declare
begin
  execute immediate 'update edi_t set mgr = :1 where empno = :2'
    using '', 7369;
end;

create table edi_emp(
  emp_id number primary key,
  emp_name varchar2(100),
  mgr_id number references edi_emp(emp_id)
);

insert into edi_emp values (1, 'Babb', null);
insert into edi_emp values (2, 'Babbb', 1);
insert into edi_emp values (3, 'Babbc', 1);

insert into edi_emp values (4, 'Babb Chen', 5); --ORA-02291: integrity constraint (APPS.SYS_C001459559) violated - parent key not found

create table edi_t(
  n number
);

insert into edi_t 
select level
  from dual 
connect by level <= 10; 

select * from edi_t;

begin
  update edi_t set n = n + 10;
  
  dbms_output.put_line('Rows Effected: ' || sql%rowcount);
  rollback;
end;
--Rows Effected: 10

begin
  insert into edi_t values (100);
  
  dbms_output.put_line('Rows Effected: ' || sql%rowcount);
  rollback;
end;
--Rows Effected: 1

declare
  type l_number_list_type is table of number;
  l_number_list l_number_list_type := l_number_list_type();
begin
  select n 
    bulk collect into l_number_list
    from edi_t;
  
  dbms_output.put_line(sql%rowcount);
  dbms_output.put_line(l_number_list.count);
end;
--10
--10

declare 
  lv_count number;
begin
  update edi_t set n = n + 10
  returning count(1) into lv_count;
  
  dbms_output.put_line('Rows Effected: ' || lv_count);
  rollback;
  --Rows Effected: 10
end;

declare 
  lv_number number;
begin
  insert into edi_t values (100)
  returning count(1) into lv_number;  --PL/SQL: ORA-00934: group function is not allowed here
  
  dbms_output.put_line(lv_number);
  
  rollback;
end;

declare 
  lv_number number;
begin
  insert into edi_t values (100)
  returning n into lv_number;
  
  dbms_output.put_line(lv_number);
  rollback;
end;
--100

create table edi_t(name varchar2(30));

insert into edi_t values('scott');
insert into edi_t values('babb');

create or replace trigger edi_del_trg before
  delete on edi_t
  for each row
begin
  raise_application_error(-20001, 'Deletion not allowed');
end;

delete from edi_t;  --ORA-20001: Deletion not allowed
-- Can truncate
truncate table edi_t; 

drop table edi_t; 

create table edi_t(name varchar2(30));

insert into edi_t values('scott');
insert into edi_t values('babb');

create or replace trigger edi_truncate_trg before
  truncate on schema
begin
  if ora_dict_obj_name = 'EDI_T' and ora_dict_obj_type = 'TABLE' then
    raise_application_error(-20001, 'Truncate not allowed');
  end if;
end;

truncate table edi_t; --ORA-20001: Truncate not allowed
--Can delete
delete from edi_t; 


--drop table edi_t;
--drop trigger edi_truncate_trg;

create table edi_emp as select * from scott.emp;
create table edi_dept as select * from scott.dept;

create view edi_emp_dept_v 
as 
 select e.empno, e.ename, e.job, e.mgr,
        e.hiredate, e.sal, e.deptno, d.dname
   from edi_emp e, edi_dept d
  where e.deptno = d.deptno;

insert into edi_emp_dept_v(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO, DNAME)
values (1001, 'BABB', 'IT', 'JBN', sysdate, 1000, 60, 'IA');  --ORA-01779: cannot modify a column which maps to a non key-preserved table

create or replace trigger edi_emp_dept_v_trg 
  instead of insert on edi_emp_dept_v
declare
  l_check_exists number;
begin
  select count(*)
    into l_check_exists
    from edi_dept 
   where deptno = :new.deptno;
    
  if l_check_exists = 0 then
    insert into edi_dept(deptno, dname) values (:new.deptno, :new.dname);
  end if;
  
  select count(*)
    into l_check_exists
    from edi_emp
   where empno = :new.empno;
    
  if l_check_exists = 0 then
    insert into edi_emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) 
    values (:new.EMPNO, :new.ENAME, :new.JOB, :new.MGR, :new.HIREDATE, :new.SAL, :new.DEPTNO);
  end if;
end;

insert into edi_emp_dept_v(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO, DNAME)
values (1001, 'BABB', 'IT', 7839, sysdate, 1000, 60, 'IA');

select * from edi_dept;

select * from edi_emp;

create table edi_t(
  id number,
  name varchar2(30),
  course_id number,
  course_completed varchar2(1),
  course_complete_date date
);

alter table edi_t
add constraint c1
check (
  (nvl(course_completed, 'N') = 'N' and course_complete_date is null)
  or 
  (nvl(course_completed, 'N') = 'Y' and course_complete_date is not null)
);

insert into edi_t values (1, 'babb', 1001, 'Y', null); --ORA-02290: check constraint (APPS.C1) violated
insert into edi_t values (1, 'babb', 1001, null, sysdate); --ORA-02290: check constraint (APPS.C1) violated
insert into edi_t values (1, 'babb', 1001, null, null);
insert into edi_t values (2, 'aabb', 1002, 'Y', sysdate);

--drop table edi_t;

create table edi_t (
  id number,
  name varchar2(30),
  job varchar2(30)
);

insert into edi_t values (1, 'babb', 'IT');
insert into edi_t values (2, 'aabb', 'IT');
insert into edi_t values (3, 'babb chen', 'CEO');

create unique index i1 on 
  edi_t (
    case when job = 'CEO' then job
    else null end
  );
  
insert into edi_t values (4, 'BC', 'CEO');  --ORA-00001: unique constraint (APPS.I1) violated
insert into edi_t values (4, 'BC', 'IT');

--drop table edi_t;

create table edi_t(c1 number);

create or replace function edi_t_fn return number
as 
begin
  update edi_t set c1 = c1;
  return 1;
end;

select edi_t_fn from dual; --ORA-14551: cannot perform a DML operation inside a query 

create or replace function edi_t_fn return number
as 
  pragma autonomous_transaction;
begin
  update edi_t set c1 = c1;
  commit;
  return 1;
end;

select edi_t_fn from dual;

create or replace function edi_t_fn return number
as 
  pragma autonomous_transaction;
begin
  execute immediate 'create table edi_t1(id number)';
  execute immediate 'insert into edi_t1 values (1)';
  --execute immediate 'drop table edi_t1';
  
  commit;
  return 1;
end;
/
select edi_t_fn from dual;
select * from edi_t1;

--drop table edi_t;
--drop function edi_t_fn;

create table edi_t( 
  no number,
  name varchar2(30)
);
/

insert into edi_t
select level, chr(ascii('A') - 1 + level)
  from dual connect by level <= 5;
  
insert into edi_t values (1, 'AA');

select * from edi_t;

--The table has two rows with same NO 1
alter table edi_t add constraint no_pk primary key(no); --ORA-02437: cannot validate (APPS.NO_PK) - primary key violated

--No error for deferrable novalidate
alter table edi_t add constraint no_pk primary key(no) deferrable novalidate;

--Insert or update to the 'no' already exists in table will cause unique constraint error
insert into edi_t values(1, 'AAA');  --ORA-00001: unique constraint (APPS.NO_PK) violated
update edi_t set no = 2 where name = 'C'; --ORA-00001: unique constraint (APPS.NO_PK) violated

select u.uniqueness, u.* from user_indexes u where u.table_name = 'EDI_T';

SELECT * 
  FROM NLS_DATABASE_PARAMETERS 
 WHERE parameter='NLS_LENGTH_SEMANTICS';

update som_edi_so_import_temp esit
   set esit.ret_code          = 'E'
 where esit.ret_code is null
   and not exists (
       select 1
         from hr_operating_units hou
        where hou.name = esit.ou_name);

declare 
  l_return varchar2(1);
begin
  select * 
    into l_return
    from dual
   where 1 <> 1;
end; --ORA-01403: no data found


declare 
  l_return varchar2(1);
begin
  select *
    into l_return
    from dual
   where 1 <> 1;
exception
  when no_data_found then
       dbms_output.put_line('No Data Found');
end; --No Data Found


declare 
  l_return varchar2(1);
begin
  select count(*) 
    into l_return
    from dual
   where 1 <> 1;
end;

declare
  l_number number;
begin
  begin
    select *
      into l_number
      from (select 1 from dual union select 2 from dual);
  exception
    when others then
      dbms_output.put_line(sqlerrm);
  end;
  dbms_output.put_line(l_number);
end;
--ORA-01422: exact fetch returns more than requested number of rows
--1

INSERT INTO my_table VALUES ((SELECT 1 FROM dual), NULL);

SELECT substr((SELECT 'ABC' FROM dual), 1, 1) FROM dual;

INSERT INTO my_table VALUES (1, 'OWNER', DEFAULT, NULL);

UPDATE my_table SET column1 = DEFAULT;

insert into babb_temp(si) select 1 from dual where 1 <> 1;

insert into babb_test_over(name,class,score)
select 'a','3',45 from dual union all
select 'b','3',55 from dual union all
select 'c','2',74 from dual union all
select 'd','3',99 from dual

create table edi_t(
  c varchar2(10)
);

create table edi_t1 as select * from edi_t;
create table edi_t2 as select * from edi_t; 
create table edi_t3 as select * from edi_t; 

insert into edi_t values('a');
insert into edi_t values('b');
insert into edi_t values('c');

select * from edi_t;

insert all 
  into edi_t1 values(c)
  into edi_t2 values(c)
  into edi_t3 values(c)
select *
  from edi_t;
  
select * from edi_t1;
select * from edi_t2;
select * from edi_t3;

drop table edi_t;
drop table edi_t1;
drop table edi_t2;
drop table edi_t3;
----------------------------------------------

create table edi_e(
  ename varchar2(20),
  edeptno number
);

create table edi_e10 as select * from edi_e where 1 <> 1;
create table edi_e20 as select * from edi_e where 1 <> 1;

insert into edi_e values ('Babb', 10);
insert into edi_e values ('Julian', 10);
insert into edi_e values ('Rice', 10);
insert into edi_e values ('Yoyo', 20);
insert into edi_e values ('Owen', 20);

insert all 
  when edeptno = 10 then
    into edi_e10 values(ename, edeptno)
  when edeptno = 20 then
    into edi_e20 values(ename, edeptno)
select *
  from edi_e;

select * from edi_e10;
select * from edi_e20;

drop table edi_e;
drop table edi_e10;
drop table edi_e20;
------------------------------------------------

create table edi_e(
  ename varchar2(20),
  salary number
);

create table edi_e_low_sal as select * from edi_e where 1 <> 1;
create table edi_e_mid_sal as select * from edi_e where 1 <> 1;
create table edi_e_high_sal as select * from edi_e where 1 <> 1;


insert into edi_e values ('Babb', 1000);
insert into edi_e values ('Yoyo', 2000);
insert into edi_e values ('Owen', 3000);
insert into edi_e values ('Julian', 4000);
insert into edi_e values ('Rice', 5000);

/*insert all
  when salary <= 2000 then
    into edi_e_low_sal values (ename, salary)
  when salary > 2000 and salary <= 4000 then
    into edi_e_mid_sal values (ename, salary)
  else
    into edi_e_high_sal values (ename, salary)
select *
  from edi_e;*/


insert first --找到符合条件的就不再继续往下走
  when salary <= 2000 then
    into edi_e_low_sal values (ename, salary)
  when salary <= 4000 then
    into edi_e_mid_sal values (ename, salary)
  else
    into edi_e_high_sal values (ename, salary)
select *
  from edi_e;


select * from edi_e_low_sal;
select * from edi_e_mid_sal;
select * from edi_e_high_sal;

drop table edi_e;
drop table edi_e_low_sal;
drop table edi_e_mid_sal;
drop table edi_e_high_sal;

-------------------------------------------------------

create table edi_region_sales(
  prod_id varchar2(10),
  apac number,
  canada number,
  euro number,
  us number
);

insert into edi_region_sales values ('PROD1', 100, 200, 300, 400);
insert into edi_region_sales values ('PROD2', 500 , 600, 700, 800);

select * from edi_region_sales;

create table edi_region_sales_row_wise(
  prod_id varchar2(10),
  region_name varchar2(100),
  sales_numbers number
);

insert into edi_region_sales_row_wise
select * 
  from edi_region_sales
unpivot (column_value for column_name in (apac, canada, euro, us));


select * from edi_region_sales_row_wise;

insert all
  into edi_region_sales_row_wise values (prod_id, 'APAC', apac)
  into edi_region_sales_row_wise values (prod_id, 'CANADA', canada)
  into edi_region_sales_row_wise values (prod_id, 'EURO', euro)
  into edi_region_sales_row_wise values (prod_id, 'US', us)
select *
  from edi_region_sales;
  
drop table edi_region_sales;
drop table edi_region_sales_row_wise;

-- 创建日历表calendar
CREATE TABLE calendar(
  id             INTEGER NOT NULL PRIMARY KEY, -- 日历编号
  calendar_date  DATE NOT NULL UNIQUE, -- 日历日期
  calendar_year  INTEGER NOT NULL, -- 日历年
  calendar_month INTEGER NOT NULL, -- 日历月
  calendar_day   INTEGER NOT NULL, -- 日历日
  is_work_day    VARCHAR(1) DEFAULT 'Y' NOT NULL -- 是否工作日
);

-- 创建考勤记录表attendance
CREATE TABLE attendance(
  id         INTEGER NOT NULL PRIMARY KEY, -- 考勤记录编号
  check_date DATE NOT NULL, -- 考勤日期
  emp_id     INTEGER NOT NULL, -- 员工编号
  clock_in   TIMESTAMP, -- 上班打卡时间
  clock_out  TIMESTAMP, -- 下班打卡时间
  CONSTRAINT uk_attendance UNIQUE (check_date, emp_id)
);

-- ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
-- ALTER SESSION SET nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF';
INSERT INTO calendar VALUES (1,'2021-01-01',2021,1,1,'N');
INSERT INTO calendar VALUES (2,'2021-01-02',2021,1,2,'N');
INSERT INTO calendar VALUES (3,'2021-01-03',2021,1,3,'N');
INSERT INTO calendar VALUES (4,'2021-01-04',2021,1,4,'Y');
INSERT INTO calendar VALUES (5,'2021-01-05',2021,1,5,'Y');
INSERT INTO calendar VALUES (6,'2021-01-06',2021,1,6,'Y');

INSERT INTO attendance VALUES (1,'2021-01-04',1,'2021-01-04 08:34:02.374','2021-01-04 18:33:11.842');
INSERT INTO attendance VALUES (2,'2021-01-04',2,'2021-01-04 08:10:31.367','2021-01-04 19:11:59.19');
INSERT INTO attendance VALUES (3,'2021-01-04',3,'2021-01-04 08:54:08.807','2021-01-04 18:27:21.348');
INSERT INTO attendance VALUES (4,'2021-01-04',4,'2021-01-04 08:27:55.39','2021-01-04 18:10:16.862');
INSERT INTO attendance VALUES (5,'2021-01-04',5,'2021-01-04 08:39:34.557','2021-01-04 18:36:10.973');
INSERT INTO attendance VALUES (6,'2021-01-04',6,'2021-01-04 08:32:34.859','2021-01-04 19:29:04.401');

CASE SELECTOR
WHEN EXPRESSION 1 THEN STATEMENT 1;
WHEN EXPRESSION 2 THEN STATEMENT 2;
...
WHEN EXPRESSION N THEN STATEMENT N;
ELSE STATEMENT N+1;
END CASE;

if som_edi_temp_d_rec.customer_id = 'XXX' then
  case l_edi_shipping_method
      when 'S' then l_freight_terms_code := '10FCA';
      when 'A' then l_freight_terms_code := '10FCA';
  end case;
end if;

CASE
WHEN SEARCH CONDITION 1 THEN STATEMENT 1;
WHEN SEARCH CONDITION 2 THEN STATEMENT 2;
...
WHEN SEARCH CONDITION N THEN STATEMENT N;
ELSE STATEMENT N+1;
END CASE;

select name,score,
   case
     when score >= 90 then
      '优秀'
     when score between 70 and 89 then
      '良好'
     when score between 60 and 69 then
      '及格'
     when score < 60 then
      '不及格'
   end 等级
 from test;

select decode(1, 1, 'One', '2', 'Two', 'None'),
       decode(2, 1, 'One', '2', 'Two', 'None')
  from dual;

select case 1
         when 1 then
          'One'
         when '2' then --ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
          'Two' 
         else
          'None'
       end
  from dual;

create table edi_t as select * from scott.emp;
select * from edi_t;

create or replace trigger edi_comp_trigger
  for insert or update or delete on edi_t compound trigger
  
  g_var varchar2(50) := 'Demo for Compound Trigger';
  
  before statement is
    l_var varchar2(50) := 'Before Statement';
  begin
    dbms_output.put_line(l_var);
    if inserting then
      dbms_output.put_line('---Insert');
    elsif updating then
      dbms_output.put_line('---Update');
    elsif deleting then
      dbms_output.put_line('---Delete');
    end if;
  end before statement;
  
  before each row is 
    l_var varchar2(50) := 'Before Each Row';
  begin
    dbms_output.put_line(l_var);
  end before each row;
  
  after each row is 
    l_var varchar2(50) := 'After Each Row';
  begin
    dbms_output.put_line(l_var);
  end after each row;
  
  after statement is
    l_var varchar2(50) := 'After Statement';
  begin
    dbms_output.put_line(l_var);
  end after statement;
end;
/

update edi_t set sal = sal * 2 where deptno = 10;

/*
Before Statement
---Update
Before Each Row
After Each Row
Before Each Row
After Each Row
Before Each Row
After Each Row
After Statement
*/

create table edi_t_log(
  empno number,
  modified_date date,
  desc_of_change varchar2(100)
);

create or replace trigger edi_comp_trigger
  for update on edi_t compound trigger
  
  type l_log_tab_type is table of edi_t_log%rowtype;
  l_log_list l_log_tab_type := l_log_tab_type();
  
  before each row is 
    l_log_row edi_t_log%rowtype;
  begin
    l_log_row.empno := :old.empno;
    l_log_row.modified_date := sysdate;
    l_log_row.desc_of_change := 'OLD SAL = ' || :old.sal || ', NEW SAL = ' || :new.sal;
    
    l_log_list.extend();
    l_log_list(l_log_list.last()) := l_log_row;
  end before each row;
  
  after statement is
  begin
    forall i in 1..l_log_list.count
    insert into edi_t_log values l_log_list(i);
  end after statement;
end; 

update edi_t set sal = sal * 2 where deptno = 10;
select * from edi_t_log;

create or replace trigger edi_validate_trg
before update of sal on edi_t
for each row
declare
  l_ceo_sal number;
begin
  select sal  --select
    into l_ceo_sal 
    from edi_t 
   where job = 'PRESIDENT';
   
   if (:new.sal < l_ceo_sal and :old.job <> 'PRESIDENT') or (:old.job = 'PRESIDENT') then
     insert into edi_t_log values (:new.empno, sysdate, 'OLD SAL = ' || :old.sal || ', NEW SAL = ' || :new.sal);
   else
     insert into edi_t_log values (:new.empno, sysdate, 'Employee salary cannot be more than ' || l_ceo_sal);
   end if;
end;

update edi_t set sal = sal * 2 where empno = 7369; --updating
/*
ORA-04091: table APPS.EDI_T is mutating, trigger/function may not see it
ORA-06512: at "APPS.EDI_VALIDATE_TRG", line 4
ORA-04088: error during execution of trigger 'APPS.EDI_VALIDATE_TRG'
*/

create or replace trigger edi_validate_trg
for update on edi_t compound trigger
  l_ceo_sal number;
  
  before statement is
  begin
    select sal
      into l_ceo_sal 
      from edi_t 
     where job = 'PRESIDENT';
  end before statement;
  
  before each row is
  begin
   if (:new.sal < l_ceo_sal and :old.job <> 'PRESIDENT') or (:old.job = 'PRESIDENT') then
     insert into edi_t_log values (:new.empno, sysdate, 'OLD SAL = ' || :old.sal || ', NEW SAL = ' || :new.sal);
   else
     :new.sal := :old.sal;
     insert into edi_t_log values (:new.empno, sysdate, 'Employee salary cannot be more than ' || l_ceo_sal);
   end if;
  end before each row;
end;

update edi_t set sal = sal * 10 where empno = 7369; --updating

create table edi_t(
  id number
);
create sequence edi_t_seq; 

create or replace trigger edi_t_trg_a
before insert on edi_t for each row 
begin
  dbms_output.put_line('A-' || edi_t_seq.nextval);
end;
/

create or replace trigger edi_t_trg_b
before insert on edi_t for each row 
begin
  dbms_output.put_line('B-' || edi_t_seq.nextval);
end;
/

create or replace trigger edi_t_trg_c
before insert on edi_t for each row 
begin
  dbms_output.put_line('C-' || edi_t_seq.nextval);
end;
/

insert into edi_t values (1);

/*
C-1
B-2
A-3
*/


create or replace trigger edi_t_trg_a
before insert on edi_t for each row 
begin
  dbms_output.put_line('A-' || edi_t_seq.nextval);
end;
/

create or replace trigger edi_t_trg_b
before insert on edi_t for each row follows edi_t_trg_a
begin
  dbms_output.put_line('B-' || edi_t_seq.nextval);
end;
/

create or replace trigger edi_t_trg_c
before insert on edi_t for each row follows edi_t_trg_b
begin
  dbms_output.put_line('C-' || edi_t_seq.nextval);
end;
/

insert into edi_t values (1);

/*
A-4
B-5
C-6
*/

drop trigger edi_t_trg_c;
drop trigger edi_t_trg_b;
drop trigger edi_t_trg_a;

drop table edi_t;
drop sequence edi_t_seq;