Oracle PL/SQL operations (5) Other Technologies

1. data transmission between tables

1) ExploitationInsertTransmit data

 
Insert into test1 (select name2, age2 from Test2 );
 

From the operation above, we can see thatSelectAdd a batch to a tableAdd data, but note that the data type should be consistent, and the selected columns should be consistent.The syntax format of this statement is as follows:

 
Insert into table_name (select statement );
 

2) create a new table based on an existing table

Syntax:

 
Create Table new_table_name as select statement;
 

Complete these steps and compareTest1AndTest2OfTable Structure:

 
Create Table test3 as select * From Test2;
 

2. Rename the table

The syntax is as follows:

 
Rename old_table_name to new_table_name; perform the following operations: Rename test3 to tst3; rename Test2 to tst2; rename test1 to tst1;
 

 

3. Change the table structure

1) add columns

 
Alter table table_name add new_column_name datetype [not null];
 

 
Alter table tst1 add csrq date; alter table tst2 add DZ varchar2 (20); DESC tst1; DESC tst2;
 

2) Change the column data type.

 
Alter table table_name modify column_name new_datatype; alter table tst1 modify age char (3); DESC tst1;
 

3) Change the null value option

 
Alter table table_name modify column_name not null;
 

Complete these steps,Observe the changes in the Data Type of the table structure column:

 
Alter table tst1 modify name not null; DESC tst1; alter table tst1 modify name NULL; DESC tst1;
 

4. View

Concept: Define a query, store it in the Oracle database, and allow users to call it by name, just like a table (actually not a table ). From the user's point of view, the view looks like a table, and the data is retrieved from the table. In fact, the data is only displayed in this view. They actually come from one or more other data sources. One of the most common applications of a view is to connect data from two or more tables and provide them to users in a readable list. By specifying the record retrieval process, users do not have to understand how to connect tables, so that a large number of people can use the data.

 
Create or replace view view_name as select statement; Create view view_name as select statement;
 

Create a view to query the basic conditions, electives, and scores of students.

 
Create or replace view xyqk_view as select. XM name,. DH phone number, C. kcmc Course name, B. CJ score from xyqkb A, xycjb B, XKB c Where. xybh = B. xybh and C. kcbh = B. kcbh; select * From xyqk_view;
 

Delete View

 
Drop view view_name; for example, drop view xyqk_view;
 

OracleYou cannot change an existing view. The only way to change a view is to delete it.And then recreate it.

5. Top N Analysis

Display the conditions in the query results beforeNRow,Complete these steps:

 
Select XH, XM, DH from xyqkb where rownum <= 5
 

 

6. other database objects

1) Sequence (Sequence)

A sequence is a counter.

A) Create a sequence

Basic Syntax:Create SequenceSequence_name;

Syntax with optional parameters:

 
Create sequence sequence_name [increment by increment_quantity] [start with starting_value] [maxcalue highest_value] [minvalue lowest_value] [cycle];
 

 
Create sequence test_seq; select test_seq.nextval from dual;
 

B) Use sequence

Complete these steps to understand sequence usage:

Create Table ST (record_id number (6, 0), record_text varchar2 (10); insert into St values (test_seq.nextval, 'aaa'); insert into St values (test_seq.nextval, 'bbbbb'); insert into St values (test_seq.nextval, 'ccccs'); insert into St values (test_seq.nextval, 'ddddd'); select * from St;
 

C) Modify the existing Sequence

Alter SequenceSequence_name

[Option];

 
Alter sequence test_seq increment by 2; select region from dual; select test_seq.nextval from dual; insret into st (Region, 'eee'); insret into st (test_seq.nextval, 'ffffff '); insret into st (test_seq.nextval, 'gggg '); insret into st (test_seq.nextval, 'hhhh'); select * from St;
 

D) Delete Sequence

 
Drop sequence sequence_name; do the following: drop sequence test_seq;
 

2) Synonym

In layman's terms, synonyms areOracleOfObjectStart an alias. You can use this alias to accessThis object. You can also use synonyms to allow other users to access the object through synonyms.

A) Create a synonym

 
Create [public] synonym synonym_name for object_name; for example: Create synonym test_syn for test; Create public synonym test_syn2 for EMP;
 

Note: You must have the relevant permissions to create a synonym. You do not have the permission to create a synonym.You cannot create Synonyms at a specified time.

B) Delete Synonyms

 
Drop synonym synonym_name; for example, drop synonym test_syn;
 

3)OracleData Dictionary

Now you may realize that the Oracle database is composed of many different objects, including tables, columns, views, relationships, constraints, sequences, and so on. Oracle stores the information of these objects in its data dictionary to save them.

A data dictionary is a collection of tables and views. These tables and views are maintained by Oracle so that they always have the latest information about each object and user in the database.

A) query the data dictionary to obtain user and database information.

A complete list of data dictionary objects can be obtained by querying a view named dict.

B) use different data dictionary views

Data Dictionary views related to user information, such as user_tables, user_synonyms, user_indexes, and user_views.