Database objects related to Oracle PL/SQL programming

The following describes database objects such as tables, views, synonyms, and sequences.

1. Table.

Create, delete, and modify tables in Oracle in the same way as other databases. However, the database types of oracle databases are different from those of tables in other database management systems.

Create:

Create table Name (field Name field type );

Delete:

Drop table name;

Modify:

Update table name set field = modified value where condition;

Check the Data Types of database tables as needed. The common data types are as follows:

Char: stores non-unicode character data of a fixed length. The maximum length is bytes.

Varchar2: stores variable-length character data with a maximum length of 4,000 bytes.

Number: stores all numeric types, including floating point, positive, negative, and zero. Directly use number (instead of number (4). The value range is 10 ^-130 to 10 ^ 126-1.

Date: date and Time data stored in 01/01/4712 BC to 12/31/9999 a.m.

Ii. View.

1. a view is a virtual table generated from one or more tables using query statements. It can be considered that a VIEW is the result of a query statement, it is stored in the database in a table-like form. A view is a virtual table, that is, the data corresponding to the view does not actually exist in the database. The database only saves the view definition. (In the data dictionary ).

Basic syntax for creating a view:

Create or replace view name

As

SQL query statement;

For example:

Create or replace view stusummary

As

Select stu. stuid, stu. stuname from student stu;

After creating a view, we can directly use the created view for future queries (provided that the query in the view can meet your needs), as shown below:

Select stuid, stuname from stusummary;

Syntax:

Select * from view name;

Syntax for deleting a view:

Drop view name;

2. What is the purpose of a view )?

> Protect data security. Because the view can restrict the content retrieved from the table, rather than all the data stored in the table.

> Data Query and processing are simplified.

> Facilitates data exchange operations. We can define a view to put the dataset to be exchanged in the view, thus simplifying the data exchange operation.

> Simplify the management of data permissions.

3. Synonyms

When developing database applications, the common rule that should be followed is to avoid directly referencing tables, views, or other database objects. Otherwise, after DBA modifies and changes the data objects, for example, to change the table name and structure, you must update and recompile the application. Oracle uses synonym to solve this problem.

There are two synonyms:

> Public synonyms: public synonyms are owned by a special user group. All users in the database can use public synonyms.

Syntax for creating Synonyms:

Create public synonym name for database object name;

> Private synonym: it is owned by the user who created it or the schema. You can control whether other users have the permission to use their own private synonyms.

Syntax for creating Synonyms:

Create synonym name for database object name;

Delete synonym Syntax:

Drop synonym name;