Oracle SQL BASICS (3)-index/trigger/view operations, oraclesql
This document only lists simple operation statements for indexes, triggers, and views.
1. Index
A. Create
Create index idx_name on fdh_client_info (name); -- Common index (Single Column index)
Create unique index uni_idx_id on fdh_client (id); -- unique index
Create index union_idx_name_addr on fdh_client (name, address); -- Union index
B. query Indexes
Select * from user_indexes;
Select * from all_indexes;
C. Re-Indexing
Alter index idx_name rebuild online; -- the table is not locked when the index is rebuilt.
Alter index idx_name rebuild tablespace tablespace_name; -- specify the tablespace stored in the index during reconstruction
D. Release useless space in the Index
Alter index idx_name deallocate unused;
E. Sort index fragments
Alter index idx_name coalesce;
F. delete an index
Drop index idx_name;
2. triggers
The following statements learn from MOOC (http://www.imooc.com/learn/414)
A. Create a statement-Level Trigger (no for each row)
Perform Security Check: do not insert employee information during non-work hours
Create or replace trigger security_empbefore insert on empbegin if to_char (sysdate, 'day') in ('satur', 'sunday') or to_number (to_char (sysdate, 'hh24 ')) not between 9 and 17 then -- from to raise_application_error (-20001, 'new employee cannot be inserted during non-working hours '); end if; end;
B. Create a row-Level Trigger
Data check: the post-increment salary cannot be less than the pre-increment salary (the pseudo-record variables: old and: new indicate the record before and after the update respectively)
Create or replace trigger check_salarybefore updateon 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 who earn more than 6000 salaries and audit employee information)
-- Create salary audit table create table audit_emp_sal (empno number (4, 0), ename varchar2 (10), newsal number (7,2), 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. View
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: this view is created only when the base table exists in ORACLE:
Alias: the alias defined for the columns generated by the view;
Subquery: a complete SELECT statement that defines aliases;
With check option: the inserted or modified data rows must meet the view-defined constraints;
With read only: No DML operations can be performed on this view.
B. 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;