Oracle synonyms sequence View

Excerpt: Oracle --- synonym, sequence, View

 

/* ===================================================== ======================================= */
/* Synonym: product_define */
/* ===================================================== ======================================= */
Create synonym product_define for pc_prod_main

Introduce the synonym, sequence and view in Oracle

Synonym

Create synonym for schema. Object

Hide the Object Name and owner:
Select count (*) from HR. employees;
Create synonym EMP for HR. Employees; -- by default, it belongs to the Donny user and is the private object of Donny.
Select count (*) from EMP;

Provides location transparency for remote objects in distributed databases:
When accessing other databases, you must first establish a database connection:
Create database link test_link connection name. You can use this name to directly connect to the database.) connect to username (username used to connect to the remote database) identified by pass password) using "orabase"; service name, that is, the service name configured in the local net manager to connect to the remote database) connect
Select count (*) from HR. Employees @ test_link;
Create synonym link_emp for HR. Employees @ test_link;
Select count (*) from link_emp;

Provide public access to objects:
Create public synonym pub_emp for HR. employees;
Pub_emp is a public user and can be accessed by all database users.

Synonym type
-Private EMP is actually Donny. EMP
-Public pub_emp all users can directly access

When a public object and a private object have the same name (because the data is different from the user, it is acceptable), the private object takes precedence. (Similar to local variables)
Desc dba_synonyms/user_synonyms/all_synonyms data dictionary, plural
Tab public Synonym
Create a private tab to view the results.

Delete synonym:
Drop synonym Donny. EMP;
Drop public synonym pub_emp;

 

 

 

Sequence:
Create sequence Donny. seq -- also belongs to a user. The following parameters can be omitted and the default value is used.
Increment by 1 -- specifies the interval between sequences, positive and negative integers. The default value is 1, which is ascending and the negative value is descending.
Start with 1 -- the first serial number. The default value is minvalue.
Nomaxvalue -- set the maximum value, which indicates the 27 power of 10 by default. Maxvalue 10
Nominvalue -- set the minimum value, which indicates the 26 power of-10 by default. Minvalue 1
Nocycle -- or cycle; indicates whether to start from scratch after the sequence reaches the maximum or minimum (descending ).
Cache 10; -- the default cache is 20. The number of serial numbers allocated in advance is stored in the memory to increase the speed.

Access sequence:
Oracle provides two pseudo columns for the sequence as its attributes.
Nextval: a new sequence value obtained based on increment. Each execution will get a new value.
Currval: current value, current value, obtained value.

Select seq. nextval from dual;
Select seq. currval from dual;

Sequence:
Insert into T values (seq. nextval );

Modify sequence:
Alter sequence seq ..... Specify parameters again
The start with cannot be modified, unless the reconstruction is deleted.

Delete sequence:
Drop sequence seq;

Data Dictionary:
Desc dba_sequences/user _... /All ....

View:
Create view <view_name> as <query>

Create view mytable
As
Select first_name | ',' | last_name
From HR. employees;

: How to use a view as a security mechanism
1. DESC evaluate the knowledge of HR. employees as a company employee information database table.
2. Goal: to enable each employee to access the name, email, and phone_number of all employees in the company to facilitate communication.
3. solution:
A) are all employees authorized to access the HR. Employees table? Salary
B) Create a view that only contains the appropriate fields, and grant all employees the permission to access this view, not the table permission.
4. Alter user HR account unlock;
Conn HR/hr
Create view company_phone_book
Select first_name | ',' | last_name name, email, phone_number
From employees;

Grant select on company_phone_book to public;

Desc company_phone_book comparison Column Length

Select * From company_phone_book;

Complexity of hiding data by name

Data Dictionary:
Dba_views
Text Field, long

Select text from dba_views where view_name = upper ('Company _ phone_book ')

Change the view definition:
New Requirement: add the employee ID (employee_id) to the existing view)
Create view company_phone_book
Select employee_id emp_id,
First_name | ',' | last_name name, email, phone_number
From employees;
An error is reported;
If reconstruction is deleted, what are the disadvantages? Deletes all associated authorizations. Create or replace view retains the original authorization.
Create or replace view company_phone_book
Select employee_id emp_id,
First_name | ',' | last_name name, email, phone_number
From employees;

Desc company_phone_book
Drop view company_phone_book

Add constraints to the View:
Create view yearly_hire_totals
Select to_char (hire_date, 'yyyy') Year,
Count (*) Total
From HR. Employees
Group by to_char (hire_date, 'yyyy ')
Order by to_char (hire_date, 'yyyy ');

Join View:
Desc HR. emp_details_view

Sets long 5000
Select text from dba_views where view_name = upper ('emp_details_view ')

With read only

Verify view validity:
Some changes to the basic table may result in invalid views:
1) Change the column name in the view or delete the column
2) Delete the basic table or view for creating a view

[Experiment] invalidate the view, recompile it, and make it valid:
1) basic table: Create Table Base (ID number, data varchar2 (200 ));
Insert into base values (1, 'abc'); Commit;
2) view: Create view view_ B
Select ID view_id, data view_data from T;

Select * From view_ B;
3) update basic table: alter table base modify (ID number, data varchar2 (255 ));
Alter table base add (data2 varchar2 (100 ));

4) invalid view: Select object_name, status from dba_objects where object_name = upper ('view _ B ')
5) Make the view valid: select from the view, and Oracle automatically compiles the view
Select * From view_ B;
6) manual compilation: Alter view view_ B compile;

Force option:
Force Oracle to accept invalid view definitions:
1) For example, A is responsible for creating the basic table during the development process, and B is responsible for creating the view. In this way, B can create and compile the view into the database without relying on the work progress of.
2) or B needs to be created in the User table view. However, B does not have the select permission on the User table yet. You can create the view first and use it after authorization.
Create view invalid_view
Select * From table_not_exist;
Create Force view invalid_view
Select * From table_not_exist;

Update and delete a view:
Similar to company_phone_book.
You can use dba_updatable_columns to view the columns that can be updated;
Desc HR. company_phone_book
Select * From dba_updatable_columns where table_name = upper ('Company _ phone_book ')

Update email and name
Update HR. company_phone_book
Set Name = 'chen, Donny'
Where emp_id = 100.

1. Use the instead of trigger to update the View:
Create trigger update_name_company_phone_book
Instead
Update on HR. company_phone_book
Begin
Update HR. Employees
Set employee_id =: New. emp_id,
First_name = substr (: New. Name, instr (: New. Name, ',') + 2 ),
Last_name = substr (: New. Name, 1, instr (: New. Name, ',')-1 ),
Phone_number =: New. phone_number,
Email =: New. Email
Where employee_id =: Old. emp_id;
End;

2. constraints with check option:
Purpose: prevent update of data that cannot be accessed through the view.

Test:
1) create a view. Only employees with department_id = 10 can be seen.
Create view department_10
Select * from HR. Employees where department_id = 10
With check Option
2) Select: Select employee_id, first_name, last_name from department_10;
3) view updatable columns:
Select * From dba_updatable_columns
Where table_name = upper ('department _ 10 ')
4) Try to move this person to department 20
Update department_10
Set department_id = 20
Where employee_id = 200
Error !!
This view limits that we can only access the data of Department = 10. We need to modify the data of Department = 20 through the view, which is forbidden.

[Test] about the first five
1) who is the top five employees of the company?
Select last_name, hire_date
From HR. Employees
Order by hire_date;
2) What if I only want to retrieve the first five pieces of data?
Select last_name, hire_date
From HR. Employees
Where rownum <6
Order by hire_date;
The result is incorrect. The first five data records are obtained and then sorted.
3) Select last_name, hire_date
From (select last_name, hire_date
From HR. Employees
Order by hire_date)
Where rownum <6