Basic knowledge of Oracle

Basic knowledge of Oracle

Table space and schema)

Objects in the same solution can be stored in different tablespaces.

The same tablespace can store objects in different solutions.

Ø each tablespace is composed of one or more data files.

The total size of each data file is the storage capacity of the tablespace.

The sum of all tablespaces in the database is the storage capacity of the database.

A database consists of several tablespaces, which are logical objects such as tables, indexes, views, and stored procedures. A table consists of various segments. Each segment is divided into several data blocks, and each partition is divided into several data blocks.

Oracle wildcard

%: Any string

_: Any single character

? : Any single character

#: Table 0 ~ Any number of 9

[]: Specifies any single character in a range, for example, [2-6]

 

Connection query: If a query requires operations on multiple tables, that is, connection query.

Subquery: select statement nesting

Oracle stored procedure:

Is a set of precompiled PL/SQL statements. These statements are stored in a name and processed as a unit. A stored procedure consists of parameters, programming statements, and return values. A stored procedure can have only one return value. It is usually used to indicate whether the result of calling the stored procedure is successful or failed.

Oracle provides three types of stored procedures: procedures, functions, and packages.

Trigger:

When a specified event occurs, the PL/SQL statement defined in the trigger is executed.

The trigger contains four main parts:

Ø trigger name

Ø trigger statement

Ø trigger Constraints

Ø trigger operation

Five Oracle triggers: Statement triggers, row triggers, insteaof triggers, user event triggers, and system event triggers

Cursor: It is an entity that maps the result set and creates a position on a single row in the result set. With a cursor, you can access any row of data in the result set. The cursor has an explicit cursor and an implicit cursor. The result set obtained by executing the SELECT statement isCursor result setThe pointer to a record in the result set isCursor Position.

The main functions of a cursor:

1. Allow locating specific rows in the result set

2. retrieve one or more rows from the current position of the result set

3. Supports data modification to the row at the current position in the resultset.

4. provides SQL statements for accessing data in the resultset using scripts, stored procedures, and triggers.