Oracle practice-view and index of PL/SQL Basics

View and index of basic PL/SQL

PL/SQL: a programming Language combined with a Structured Language (Structured Query Language). It is an extension of SQL and supports multiple data types, such as large objects and Collection types, you can use control statements such as conditions and loops to create stored procedures, packages, and triggers, and add program logic to SQL statement execution, tightly integrated with Oracle servers and Oracle tools, it is portable, flexible, and secure.

Bytes -----------------------------------------------------------------------------------------------------

View:

/*

View: data from one or more tables is displayed in a specified manner.

It can be regarded as "virtual table" or "Storage query"

The table on which the view is created is the base table.

Advantages: 1. provides another level of security;

2. Hide the complexity of data;

3. Simplify Your SQL statements;

4. Isolate base table changes

5. by renaming columns, you can access data from another perspective.

*/

-- Component view, keyword view

Create or replace view emp_view -- you must have the permission to create a view as select ename, sal, dname from emp, dept where emp. deptno = dept. deptno;

-- Query from view

select * from emp_view;

-- Delete View

drop view emp_view;

/*

The connection View query is similar to the multi-Table connection query, because the view can be used as a virtual table, but there are some restrictions:

1. Only one underlying base table can be modified.

2. If the modification violates the constraints of the base table, the view cannot be updated.

3. If a view contains join operators, DISTINCT keywords, set operators, Aggregate functions, or group by clauses, the view cannot be updated.

4. If a view contains a pseudo column or expression, the view cannot be updated.

*/

Bytes -----------------------------------------------------------------------------------------------------------------

Index:

/*

Index: an index is an optional table-related structure.

Advantages of index creation: 1. Reduce disk I/O

2. the query statement execution capability can be improved.

Note: creating unnecessary indexes will result in data writing performance overhead and a waste of disk space.

*/

-- 1. Create a Standard Index

Create index emp_index on emp (ename); -- note that you cannot use or replacealter index emp_index rebuild; -- restructured the index drop index emp_index; -- delete the index

-- Create a unique index

create unique index dept_uniqueIndex on dept(dname);drop unique index dept_uniqueIndex;