Oracle SQL BASICS (3)-index/trigger/view operations

Oracle SQL BASICS (3)-index/trigger/view operations
1. index. create index idx_name on fdh_client_info (name); -- create unique index uni_idx_id on fdh_client (id); -- create index union_idx_name_addr on fdh_client (name, address); -- Union index B. query the index select * from user_indexes; select * from all_indexes; c. re-create index alter index idx_name rebuild online; -- when re-indexing, the alter index idx_name rebuild tablespace tablespace_name is not locked; -- specify the index storage tablespace d during re-indexing. release useless space in the index alter index idx_name deallocate unused; e. sort the index fragmentation alter index idx_name coalesce; f. delete index drop index idx_name; 2. trigger the following statement to learn from MOOC ( http://www.imooc.com/learn/414 ). Create a statement-Level trigger (no for each row) for security check: do not insert employee information in non-working hours create or replace trigger security_empbefore insert on empbegin if to_char (sysdate, 'day ') in ('saturday', 'sunday') or to_number (to_char (sysdate, 'hh24') not between 9 and 17 then -- raise_application_error (-20001, 'New employee cannot be inserted during non-working hours '); end if; end; B. create row-Level trigger data check: The salary after the upgrade cannot be less than before (the pseudo record variable: old and: new respectively indicate the record before and after the update) create or replace trigger check_salarybefore upd Ateon empfor each row -- row-Level Trigger begin if: new. sal <: old. sal then raise_application_error (-20002, 'salary after increase cannot be less than before increase. '|' salary after increase: '|: new. sal | 'salary before increase: '|: old. sal); end if; end; c. database Audit (employees are paid more than 6000 after the increase, audit employee information) -- create a salary audit table create table audit_emp_sal (empno number (4, 0), ename varchar2 (10 ), newsal number (6000), incdate date) -- create employee test table create table emp_2 as select * from emp; -- Database Audit: After the salary increases, the employee information is inserted into the audit table create Or replace trigger do_audit_emp_salafter updateon emp_2for each rowbegin if: new. sal> 6000 then insert into audit_emp_sal values (: new. empno,: new. ename,: new. sal, sysdate); end if; end; d. database backup and synchronization -- create table emp_back as select * from emp; -- backup and synchronization of databases (synchronous Backup Using triggers) create or replace trigger sync_emp_salafter updateon empfor each rowbegin update emp_back B set B. sal =: new. sal where B. empno =: New. empno; end; 3. the view itself does not contain data. It stores the query results of a select statement. The view is a logical table based on several tables or views. The table here is called a base table, you can use a view to query or modify the data of a base table. A view can be divided into a simple view and a complex view. A simple view obtains data from a single table and can perform DML operations without including functions and data groups. A. syntax: CREATE [or replace] [FORCE | NOFORCE] VIEW view_name [(alias [, alias]...)] AS subquery [with check option [CONSTRAINT constraint] [with read only] Note: FORCE: this view is automatically created no matter whether the base table exists in ORACLE. NOFORCE: only when the base table exists in ORACLE can this view be created: alias: the alias defined for the column generated by the view; subquery: a complete SELECT statement, which can be used to define aliases; with check option: the inserted or modified data rows must meet the constraints defined by the view. with read only: No DML operations can be performed on the view. example: create or replace view dept_statistics (name, minsal, maxsal, avgsal) as select d. dname, min (e. sal), max (e. sal), avg (e. sal) from emp e, dept d where e. deptno = d. deptno group by d. dname;