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

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;